MySQL索引的分类及性能分析及问题排查

目录

1. MySQL索引的数据结构和索引的介绍

1.1 关于索引的介绍

2. MySQL索引的分类

2.1 按照底层存储方式角度划分

 2.2 按照应用维度划分

2.3 主键索引和唯一索引的区别

2.4 覆盖索引的使用

2.5 联合索引的优点

2.6 关于在数据量较多的情况下全文索引相对于模糊查询的优越性显示

3. 使用Explain命令查看SQL语句的执行计划

4. 索引失效的场景有哪些

 4.1 当查询条件中有or，即使有部分条件索引也会失效

 4.2 当like查询时是以%开头时也不会命中索引

 4.3 不遵守联合索引的最左匹配原则的话也会发生索引失效的现象

4.4 当索引列上参与计算也会导致索引失效

5、MySQL的问题排查 

5.1 MySQL问题排查的手段有哪些

---

1. MySQL索引的数据结构和索引的介绍

1.1 关于索引的介绍

索引是一种用于快速查询和检索数据的数据结构。使用索引可以提高数据的查询效率，但是创建索引和维护索引需要耗费时间，整个时间随着数据量的增加而增加，而且添加索引还需要占据物理空间。索引底层数据结构有B树，B+树，Hash表等。在MySQL中，都是使用B+树作为索引结构。尽量在最频繁使用的、需要排序的、用以缩小查询范围的字段上建立索引。对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。对于一些特殊的数据类型，不宜建立索引，比如文本字段等。

2. MySQL索引的分类

2.1 按照底层存储方式角度划分

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB中的主键索引就属于聚簇索引。如下图所示，数据库中的每个表对应着磁盘中的文件，该文件中包含着索引文件和数据文件。

 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引（辅助索引）属于MyISAM引擎，不管是主键还是非主键，使用的都是非聚簇索引。

 2.2 按照应用维度划分

主键索引：加速查询+列值唯一（不可有NULL）+表中只有一个

为数据库中的表使用主键索引的SQL语句为：ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

普通索引：仅加速索引

添加普通索引的SQL语句为：ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

唯一索引：加速索引+列值唯一（可以有NULL）

添加唯一索引的SQL语句为：ALTER TABLE `table_name` ADD UNIQUE (`column`);

覆盖索引：一个索引包含（或者是覆盖）所有需要查询的字段的值

添加覆盖索引的SQL语句为：ALTER TABLE ‘table_name’ ADD INDEX index_name('主键字段'，‘想要覆盖字段’)。

联合索引：多列值组成一个索引，专门用于组合索引，其效率大于索引合并，添加联合索引的语句为 ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );

在使用联合索引进行查询的时候，遵循最左前缀匹配原则。最左前缀匹配原则是指在使用联合索引时，MySQL会根据联合索引中的字段顺序从左到右依次从查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，就会使用该字段过滤到一批数据，直到联合索引中全部字段匹配完成。所以在使用联合索引时，可以将区分度高的字段放到最左边，这可以过滤更多数据。

全文索引：对文本的内容进行分词，进行搜索。目前只有CHAR、VARCHAR、TEXT列可以进行全文索引。通过数值比较、范围过滤可以完成大部分的查询要求，但是如果想通过使用关键字的匹配进行查询就需要相似度的查询，而不是原来的精确数值比较。全文索引比使用like+%可以实现模糊匹配，但是全文索引在大量数据场景下，全文索引要比like+%快的多。

2.3 主键索引和唯一索引的区别

1、主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

2、主键创建后一定包含一个唯一索引，唯一索引不一定就是主键。

3、唯一索引允许空值，而主键列不允许为空值。

4、主键可以被其他表引用为外键，而唯一索引不能。

5、一个表最多只能创建一个主键，但是可以创建多个唯一索引。

6、主键更适合那些不容易改变的唯一标识，如自动递增列、身份证号等。

2.4 覆盖索引的使用

对于一些百万级别的数据库，我们如果想要查询数据库中的后几个数据的话，耗时会很久。但是我们可以使用覆盖索引，将想要查询出的字段和主键建立覆盖索引，此时我们再去做查询就会大大提高查询效率。因为对于普通索引来说，在数据结构的叶子节点存储的主键值，再根据主键值查询到所有数据，使用覆盖索引可以不用去这一步，不用根据主键再去查询所有数据。

覆盖索引：一个索引中包含所有需要查询字段的值

优点：无需回表

1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
4.innodb的聚簇索引，覆盖索引对innodb表特别有用

2.5 联合索引的优点

如果我们要运行下面这个SQL语句

SELECT * FROM 'user_info' WHERE username='XX',password='XXXX';

 我们在username和password建立联合索引，当索引在检索 password字段的时候，数据量大大缩小，索引的命中率减小，增大了索引的效率。联合索引的索引体积比单独索引的体积要小，而且只是一个索引树，相比单独列的索引要更加的节省时间复杂度和空间复杂度。

联合索引的本质：

当创建(col1,col2,col3)联合索引时，相当于创建了(col)单列索引，(clo1,clo2)联合索引以及(col1,col2,col3)联合索引想要索引生效，只能使用col1和col1,col2和col1,col2,col3三种组合；当然，col1,col3组合也可以，但实际上只用到了col1的索引，col3并没有用到！也就是说，联合索引必须要带col1这个索引列。

2.6 关于在数据量较多的情况下全文索引相对于模糊查询的优越性显示

首先我们使用循环向一个测试数据库中插入10000条数据：

运行，然后查询数据：

然后我们测试使用like模糊查询，和使用全文索引需要的时间。首先使用SQL语句（ALTER TABLE fulltext_test ADD FULLTEXT INDEX fulltext_article(text,tag);）创建全文索引，然后我们在下面使用全文索引查询和模糊查询查询一条数据，我们可以看到全文索引能够大大减少查询的时间，可以使用show index from 数据库表名 可以查看索引：

3. 使用Explain命令查看SQL语句的执行计划

我们使用explain命令可以查询一个SQL语句的执行信息，总共有十列，接下来我们分别介绍一下这十列分别代表什么。

1. id，这一列代表的是SQL执行的顺序的标识,SQL从大到小的执行。如果id相同时，执行顺序由上至下，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

2. select_type,这一列代表查询中每个select子句的类型。

SIMPLE(简单SELECT,不使用UNION或子查询等)。

PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)。

UNION(UNION中的第二个或后面的SELECT语句)。

DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询）。 

UNION RESULT(UNION的结果)。

SUBQUERY(子查询中的第一个SELECT)。

DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)。

DERIVED(派生表的SELECT, FROM子句的子查询)。

UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

3. table，这一列显示的是查询的哪张表的数据。

4. type,表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有ALL, index,  range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

5.possible_keys，指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询可以有效提高查询效率。

6.Key，key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7.Key_len，表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的），不损失精确性的情况下，长度越短越好 。

8.ref，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

9.rows，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

10.Extra，该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

我们使用Explain命令可以查看SQL语句的执行计划，可以查看该SQL语句是否使用使用了索引，观察SQL语句查询缓慢的原因。由下图可以看出，在type列的数值为ALL，这代表MySQL将遍历全表以找到匹配的行。而下面的key和key_len为null代表该查询语句没有走索引，且需要查询（10019）rows才能得到结果，所以模糊查询的效率才会这么低。

4. 索引失效的场景有哪些

 对于如上图所示的表，我们使用下面所示的代码添加主键索引，普通索引和联合索引。

ALTER TABLE `student2` ADD PRIMARY KEY (`id`);
ALTER TABLE `student2` ADD INDEX index_name (`name`);
ALTER TABLE `student2` ADD INDEX index_name_age (`name`,`age`);

 4.1 当查询条件中有or，即使有部分条件索引也会失效

explain SELECT * FROM `student2` where id =1 

使用explain我们可以看到我们的SQL语句命中了主键索引 。

 但是当我们的查询语句带了or之后，我们可以看到并没有命中索引。

 4.2 当like查询时是以%开头时也不会命中索引

 如所图所示，当我们查询name为张三的数据时，命中了我们添加的普通索引。

但是当我们使用以%开头的模糊查询时，并没有命中索引。

 4.3 不遵守联合索引的最左匹配原则的话也会发生索引失效的现象

 如果遵守联合索引的最左匹配原则，我们可以看到命中了联合索引。

 当不遵守最左匹配原则，此时的联合索引失效，所以在查询的适合我们应该根据自己制定的索引，编写对应的SQL语句。

4.4 当索引列上参与计算也会导致索引失效

5、MySQL的问题排查 

5.1 MySQL问题排查的手段有哪些

1. 使⽤ show processlist 命令查看当前所有连接信息；

2. 使⽤ Explain 命令查询 SQL 语句执⾏计划；

3. 开启慢查询⽇志，查看慢查询的 SQL。