MySQL索引

一. 存储引擎

1. 什么是存储引擎？

与其他数据库例如Oracle 和SQL Server等数据库中只有一种存储引擎不同的是，MySQL有一个被称为“Pluggable Storage Engine Architecture”(可替换存储引擎架构)的特性，也就意味着MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。MySQL数据库在实际的工作中其实分为了语句分析层和存储引擎层，其中语句分析层就主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能，而存储引擎层则主要负责接收来自语句分析层的分析结果，完成相应的数据输入输出和文件操作。简而言之，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

2. 存储引擎种类

接下来我们就介绍两种在实际开发中使用最多的两种引擎【MyISAM】和【InnoDB】。

3. MyISAM 引擎

这种引擎是MySQL最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：

• 静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为 数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。

• 动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。

• 压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

当然不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

• 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。

• 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。 例如我创建了一个【test】表，那么就会生成以下三个文件：

4. InnoDB引擎

InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 使用InnoDB是最理想的选择：

• 更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求

• 事务：InnoDB存储引擎是支持事务的标准MySQL存储引擎

• 自动灾难恢复：与其它存储引擎不同，InnoDB表能够自动从灾难中恢复

• 外键约束：MySQL支持外键的存储引擎只有InnoDB

• 支持自动增加列AUTO_INCREMENT属性

5. 总结

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

二. 索引结构(方法、算法)

在mysql中常用两种索引结构(算法)BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。

常用存储引擎对应的索引结构

mysql InnoDB存储引擎 是支持hash索引的，不过，我们必须启用，hash索引的创建由InnoDB存储引擎引擎自动优化创建，我们干预不了。

1.Hash

Hash索引的底层实现是由Hash表来实现的，非常适合以 key-value 的形式查询，也就是单个key 查询，或者说是等值查询。其结构如下所示：

从上面结构可以看出，Hash 索引可以比较方便的提供等值查询的场景,由于是一次定位数据，不像BTree索引需 要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。但是对于范围查询的话，就需要进行全表扫描了。

但为什么我们使用BTree比使用Hash多呢？主要Hash本身由于其特殊性，也带来了很多限制和弊端：

1. Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询。

2. 联合索引中，Hash索引不能利用部分索引键查询。 对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。

3. Hash索引无法避免数据的排序操作 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

4. Hash索引任何时候都不能避免表扫描 Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。

5. Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高 对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

2. B+Tree

B+Tree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，例如：* select * from user where name like ‘jack%‘; select * from user where name like ‘jac%k%‘; 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： select * from user where name like ‘%jack‘; select * from user where name like simply_name;

3. 扩展:B+/-Tree原理

B树和B+树 B树和B+树算是数据结构中出现频率十分高的模型了，在笔者之前的几篇博客，有对二叉查找树和二叉平衡树进行过讲解和代码分析，但是那些都是在程序中使用比较多的树，在数据库中，数据量相对较大，多路查找树显然更加适合数据库的应用场景，接下来我们就介绍这两类多路查找树，毕竟作为程序员，心里没点B树怎么能行呢？

B树：B树就是B-树，他有着如下的特性：

1、B树不同于二叉树，他们的一个节点可以存储多个关键字和多个子树指针，这也是B+树的特点；

2、一个m阶的B树要求除了根节点以外，所有的非叶子子节点必须要有[m/2,m]个子树；

3、根节点必须只能有两个子树，当然，如果只有根节点一个节点的情况存在；

4、B树是一个查找二叉树，这点和二叉查找树很像，他都是越靠前的子树越小，并且，同一个节点内，关键字按照大小排序；

5、B树的一个节点要求子树的个数等于关键字的个数+1；

好了，话不多说，看看B树的模型吧：

由于B树将所有的查找关键字都放在节点中，所以查找方式和二叉查找十分相像，比如说查找E:

先通过根节点找到了左子树，再顺序地遍历左子树，发现E在F和J的中间，于是查找叶子节点，顺序遍历关键字以后就可以返回E了，如果未能查到E，则表示没有找到。

B+树 人人都喜欢plus，B+树就是这么一个plus，后头所讲解的索引，就是用的B+树，我们先来看看他的特性吧：

1、B+树将所有的查找结果放在叶子节点中，这也就意味着查找B+树，就必须到叶子节点才能返回结果；

2、B+树每一个节点的关键字个数和子树指针个数相同；

3、B+树的非叶子节点的每一个关键字对应一个指针，而关键字则是子树的最大，或者最小值；

看看模型吧：

一个3阶的B+树

他的查找方式也是简单粗暴的，和B树十分像，只不过他会在叶子节点中找到目标，比如我们找兔：

第一步比马小，就会查找他的子树，第二部比龙小，就会查找他的子树，最后在叶子节点中的关键字命中目标。

那么MySql是如何利用这数据结构的呢？

三. 索引方式

Mysql数据库中的B+树索引可以分为聚集索引和非聚集索引（辅助索引）

聚集索引

聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引（这里不懂先放着，一会举例），每张表只能有一个聚集索引，聚集索引的叶子节点存储了整个行数据(即:一张表只能有一个聚集索引)。

解释：什么叫索引项的排序方式和表中数据记录排序方式一致呢？ 我们把一本字典看做是数据库的表，那么字典的拼音目录就是聚集索引，它按照A-Z排列。实际存储的字也是按A-Z排列的。这就是索引项的排序方式和表中数据记录排序方式一致。

对于Innodb，主键毫无疑问是一个聚集索引。但是当一个表没有主键，或者没有一个索引，Innodb会如何处理呢。请看如下规则:

• 如果一个主键被定义了，那么这个主键就是作为聚集索引。

• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。

• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

非聚集索引

非聚集索引：非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，还存储了一个指向改行数据的聚集索引建的书签。

四. 索引类型

MySQL目前主要有以下几种索引类型：

• 普通索引

• 唯一索引

• 主键索引

• 组合索引

• 全文索引

普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

（1）直接创建索引

CREATE INDEX index_name ON table(column(length))  

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

五. 索引原理以及结构

1. MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶子节点中存储数据行的物理地址(data域存放的是数据记录的物理地址)。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

2. InnoDB索引实现

Innodb使用的是B+树，他存在有一个主键索引(聚集索引)和辅助索引(非聚集索引)两种索引，主键索引是在生成主键时就有的索引，他的叶子节点中存放的就是数据行，所以又称之为聚集索引。

而另一类索引，辅助索引，就是我们人为新建的索引，他的叶子节点中存放的是主键，当我们通过辅助索引查找到主键之后，再通过查找的主键去查找主键索引(所以非聚集索引有二次查询的问题，即 回表 解决方式)。

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

A. 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

图10

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

B. 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

图11

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

为了更准确直观的了解 聚集索引与非聚集索引的 关系。我们看一个主键索引与普通索引的案例

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL COMMENT ‘主键ID‘,
  `age` int(10) DEFAULT NULL COMMENT ‘年龄‘,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
?
INSERT INTO `t_user` VALUES (‘100‘, ‘10‘), (‘200‘, ‘20‘), (‘300‘, ‘30‘), (‘500‘, ‘50‘), (‘600‘, ‘60‘);

上面表和数据的存储结构大致如下所示：

从上图可以看出，有 2 个索引结构：主键ID 索引和普通索引。主键索引的叶子节点存储的是行数据的内容（聚簇索引），普通索引的叶子节点存储的是主键的值（非聚簇索引/二级索引）。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

3.组合索引原理及存储结构

上面我们已经看了MyISAM和InnoDB索引的例子，但讲述的都是单列索引，联合索引对应的结构图是怎样的？

我们创建索引时，也会经常创建如 idx_name_age (name, age) 这样的索引结构。并且还知道 WHERE 条件中 name = ? AND age = ? 和 name = ? 都可以使用到这个联合索引。下面我们来看一下其结构，看一下为什么是可以做到这一点的。

疑惑：联合索引的结构是怎样的

比方说联合索引 (col1, col2,col3)，我知道在逻辑上是先按照col1进行排序再按照col2进行排序最后再按照col3进行排序。

其相当于创建了三个索引：（col1）、（col1,col2）、（col1,col2,col3）

因此如果是select * from table where col1 = 1 and col3 = 3的话，只有col1的索引部分能生效。

但是其物理结构上这个联合索引是怎样存在的，我想不懂。

解答：联合索引的结构

上网查阅了许多资料，总算有点眉目了。

假设这是一个多列索引(col1, col2,col3)，对于叶子节点，是这样的：

PS:该图改自《MySQL索引背后的数据结构及算法原理》一文的配图。

也就是说，联合索引(col1, col2,col3)也是一棵B+Tree，其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序(注意：子叶节点的data存储的是主键id,参见非聚集索引)。

配图可能不太让人满意，因为col1都是不同的，也就是说在col1就已经能确定结果了。自己又画了一个图（有点丑），col1表示的是年龄，col2表示的是姓氏，col3表示的是名字。如下图：

PS:对应地址指的是数据记录的地址。

如图，联合索引(年龄, 姓氏,名字)，叶节点上data域存储的是三个关键字的数据。且是按照年龄、姓氏、名字的顺序排列的。

因此，如果执行的是： select * from STUDENT where 姓氏=‘李‘ and 名字=‘安‘; 或者 select * from STUDENT where 名字=‘安‘; 那么当执行查询的时候，是无法使用这个联合索引的。因为联合索引中是先根据年龄进行排序的。如果年龄没有先确定，直接对姓氏和名字进行查询的话，就相当于乱序查询一样，因此索引无法生效。因此查询是全表查询。

如果执行的是： select * from STUDENT where 年龄=1 and 姓氏=‘李‘; 那么当执行查询的时候，索引是能生效的，从图中很直观的看出，age=1的是第一个叶子节点的前6条记录，在age=1的前提下，姓氏=’李’的是前3条。因此最终查询出来的是这三条，从而能获取到对应记录的地址。 如果执行的是： select * from STUDENT where 年龄=1 and 姓氏=‘黄‘ and 名字=‘安‘; 那么索引也是生效的。

而如果执行的是： select * from STUDENT where 年龄=1 and 名字=‘安‘; 那么，索引年龄部分能生效，名字部分不能生效。也就是说索引部分生效。

因此我对联合索引结构的理解就是B+Tree是按照第一个关键字进行索引，然后在叶子节点上按照第一个关键字、第二个关键字、第三个关键字…进行排序。

最左原则

而之所以会有最左原则，是因为联合索引的B+Tree是按照第一个关键字进行索引排列的。

索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。 (1)匹配全值(Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找18岁的李安 (2)匹配最左前缀(Match a leftmost prefix)：你可以利用索引查找年龄为21的人，仅仅使用索引中的第1列。 (3)匹配列前缀(Match a column prefix)：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。 (4)匹配值的范围查询(Match a range of values)：可以利用索引查找年龄在21到30之间的人，仅仅使用索引中第1列。 (5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找年龄为21，而姓氏以字母K开始的人。 (6)仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。 由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。当然，使用B-tree索引有以下一些限制： (1) 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。 (2) 不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。 (3) 存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE age=21 AND first_name LIKE ‘李%‘ AND Name=‘安‘，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

六. 索引优化

mysql执行计划中的extra列中表明了执行计划的每一步中的实现细节，其中包含了与索引相关的一些细节信息 其中跟索引有关的using index 在不同的情况下会出现Using index， Using where Using index ，Using index condition等 那么Using index 和 Using where;Using index 有什么区别？网上搜了一大把文章，说实在话也没怎么弄懂，于是就自己动手试试。

本文仅从最简单的单表去测试using index 和 using where using index以及简单测试using index condition的情况的出现时机 。 执行计划的生成与表结构，表数据量，索引结构，统计信息等等上下文等多种环境有关，无法一概而论，复杂情况另论。

测试环境搭建

　　测试表以及测试数据搭建，类似于订单表和订单明细表，暂时先用订单表做测试

　　测试表结构

create table test_order
(
    id int auto_increment primary key,
    user_id int,
    order_id int,
    order_status tinyint,
    create_date datetime
);
create table test_orderdetail
(
    id int auto_increment primary key,
    order_id int,
    product_name varchar(100),
    cnt int,
    create_date datetime
);
create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);
create index idx_orderid_productname on test_orderdetail(order_id,product_name);

创建存储过程（insertOrder），创建测试数据，脚本如下

BEGIN
	 declare v_uuid  varchar(50);
DECLARE  v_orderId int;
    while loopcount>0 do
        set v_uuid = uuid();
				SET v_orderId=rand()*100000;
        insert into test_order (user_id,order_id,order_status,create_date) values (rand()*1000,v_orderId,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));
        insert into test_orderdetail(order_id,product_name,cnt,create_date) values (v_orderId,v_uuid,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));
        set loopcount = loopcount -1;
    end while;
END

创建50W条测试数据

call insertOrder (50000);

Using index

1，查询的列被索引覆盖，并且where筛选条件是索引的是前导列，Extra中为Using index

-- 查询的列全部在索引中,并且where筛选条件是索引的前导列
-- type:ref(索引查找)+ Extra:using index
EXPLAIN SELECT user_id,order_id,create_date from test_order where user_id=1;

Using where Using index

1. 查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的不是前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据

-- 查询的列全部在索引中,并且where的筛选条件不符合索引的前导列
-- type:index(索引扫描) + Extra:Using where; Using index
EXPLAIN SELECT user_id,order_id,create_date from test_order where order_id=67260;

2. 查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据

-- 查询的列全部在索引中,并且where的筛选条件是索引前导列的一个范围
-- type:index(索引扫描) + Extra:Using where; Using index

EXPLAIN SELECT user_id,order_id,create_date from test_order where user_id<5;

NULL（既没有Using index，也没有Using where Using index，也没有using where）

　1，查询的列未被索引覆盖，并且where筛选条件是索引的前导列， 　　 意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)

-- 查询的列不全在索引中,并且where的筛选条件是索引的前导列
-- type:ref + Extra:NULL
EXPLAIN SELECT * from test_order where user_id=1;

Using where

　　 查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra中为Using where

-- 查询的列不全在索引中,并且where的筛选条件不是索引的前导列
EXPLAIN SELECT * from test_order where order_id=67260;

using where 意味着通过索引或者表扫描的方式进程where条件的过滤， 反过来说，也就是没有可用的索引查找，当然这里也要考虑索引扫描+回表与表扫描的代价。 这里的type都是all，说明MySQL认为全表扫描是一种比较低的代价。

Using index condition

1. 查询的列不全在索引中，where条件中是一个前导列的范围

-- 查询的列不全在索引中，where条件中是一个前导列的范围
EXPLAIN SELECT * from test_order where user_id>1 and user_id<10;

2. 查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）

   -- 查询的列不全在索引中，查询条件完全可以使用到索引（进行索引查找）
   EXPLAIN SELECT * from test_order where user_id=1 and order_id=67260 and create_date>‘2017-03-15‘;
   

结论：

1. Extra中的为Using index的情况 where筛选列是索引的前导列 &&查询列被索引覆盖 && where筛选条件是一个基于索引前导列的查询，意味着通过索引超找就能直接找到符合条件的数据，并且无须回表

2. Extra中的为空的情况 　　查询列存在未被索引覆盖&&where筛选列是索引的前导列，意味着通过索引超找并且通过“回表”来找到未被索引覆盖的字段，

3. Extra中的为Using where Using index： 出现Using where Using index意味着是通过索引扫描（或者表扫描）来实现sql语句执行的，即便是索引前导列的索引范围查找也有一点范围扫描的动作，不管是前非索引前导列引起的，还是非索引列查询引起的。

　MySQL执行计划中的Extra中信息非常多，不仅仅包括Using index，Using where Using index，Using index condition，Using where，尤其是在多表连接的时候，这一点在相对MSSQL来说，不够直观或者结构化。 　　MSSQL中是通过区分索引查找（index seek），索引扫描（index scan），表扫描（table scan）来实现具体的查询的，这图形化的执行计划在不同的场景下是非常直观的，要想完全弄懂MySQL的这个执行计划，可能要更多地在实践中摸索。

1. id 表示执行的顺序，id越大越先执行，id一样的从上往下执行。

2. select_type

3. table表名或者表的别名。

4. partitions分区信息，非分区表为null。

5. type 访问类型，表示找到所查询数据的方法，也是本文重点介绍的属性。该属性的常见值如下，性能从好到差：

• NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。

• system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。

• eq_ref： 每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引

• ref： 如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键

• fulltext: 全文搜索

• ref_or_null: 与ref类似，但包括NULL

• index_merge 表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。 这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）

• unique_subquery 在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！

• index_subquery 同上，但把形如”select non_unique_key_column“的子查询替换

• range 常数值的范围（索引范围扫描），对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

• index a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有UsingIndex）； b.以索引顺序从索引中查找数据行的全表扫描（无 UsingIndex）； c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思； d.如单独出现，则是用读索引来代替读行，但不用于查找

• all 遍历全表以找到匹配的行

• null：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

possible_keys 表示mysql此次查询中可能使用的索引。

key 表示mysql实际在此次查询中使用的索引。

key_len 表示mysql使用的索引的长度。该值越小越好。

ref 表示连接查询的连接条件。

rows 表示mysql估计此次查询所需读取的行数。该值越小越好。

extra 表示mysql解决查询的其他信息，有几十种不同的值，该信息也是我们优化sql可以专注的一个值。关于这个extra信息我可能会再下一篇中介绍，这里先略过。

索引结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

优化原则

禁用select *
使用select count(*) 统计行数
尽量少运算
尽量避免全表扫描，如果可以，在过滤列建立索引
尽量避免在where子句对字段进行null判断
尽量避免在where子句使用!= 或者<>
尽量避免在where子句使用or连接
尽量避免对字段进行表达式计算
尽量避免对字段进行函数操作
尽量避免使用不是复合索引的前缀列进行过滤连接
尽量少排序，如果可以，建立索引
尽量少join
尽量用join代替子查询
尽量避免在where子句中使用in,not in或者having，使用exists,not exists代替
尽量避免两端模糊匹配 like %***%
尽量用union all代替union
尽量早过滤
避免类型转换
尽量批量insert
优先优化高并发sql，而不是频率低的大sql
尽可能对每一条sql进行explain
尽可能从全局出发

七. 总结

八. 扩展:常见问题

1.如何解决非聚集索引二次查询(回表)的问题？

建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句：

select col1, col2 from t1 where col1 = ‘213‘;

因为复合索引的列包括了col1和col2，不需要查询别的列，所以不需要进行二次查询。

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

索引使用注意事项：

1.索引会忽略null值，所以我们在设计数据库的时候设置为为NOT NULL;

2.短索引（给索引设置长度）不仅能够提高查询速度，而且能节省I/O操作。

3. Mysql在查询的时候只会使用一个索引，但不是一个字段

4. 不鼓励使用like对索引操作：like"%aaa%"不会使用索引；但like“aaa%”会使用索引。即可以对固定起始值模糊查询

5.不适用于否地操作（not in , <>, !=，or） //用到or地方，尽量用union，或者程序两次查找

6.如果创建了索引，但是查询语句并没有使用，则会使原来的效率更差

https://blog.csdn.net/m0_37888031/article/details/80664138

https://blog.csdn.net/miaoqinian/article/details/80787105

https://www.cnblogs.com/bonelee/p/6225211.html

https://blog.csdn.net/that_is_cool/article/details/81069945

http://moguhu.com/article/detail?articleId=117

https://www.cnblogs.com/softidea/p/5977860.html

MySQL索引

原文：https://www.cnblogs.com/nov5026/p/11210078.html