Mysql中的索引详解

explain用法

explain是用来显式mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

字段含义

• possible_keys:显式可能应用在这张表的索引，如果为空，没有可能的索引；
• key：实际使用的索引，如果为空，表示没有使用索引；
• key_len:使用的索引长度，在不损失精确性的情况下，长度越短越好；
• ref：显示索引的哪一列被使用了；
• rows：mysql认为必须检查的用来返回请求数据的行数；
• Extra:关于mysql如何解析查询的额外信息。

Extra返回描述的意义

• Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

• Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

• Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

• Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

• Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

• Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

• Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

• system 表只有一行：system表。这是const连接类型的特殊情况

• const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

• eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

• ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

• range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

• index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

• ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

每一列的含义

select_type：
PRIMARY:最外面的SELECT
UNION:UNION中的第二个或后面的SELECT语句
DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT:UNION 的结果
SUBQUERY:子查询中的第一个SELECT
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
DERIVED:导出表的SELECT(FROM子句的子查询)
type：

1. system:表仅有一行(=系统表)。这是const联接类型的一个特例。
2. const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
5. ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
6. index_merge:该联接类型表示使用了索引合并优化方法。
7. unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key
   FROM single_table WHERE some_expr)
8. unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
9. index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:
10. value IN (SELECT key_column FROM single_table WHERE some_expr)
11. range:只检索给定范围的行,使用一个索引来选择行。
12. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
13. ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。

创建信息表

CREATE TABLE `index_test`  (
  `last_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `first_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `dob` date NOT NULL,
  `gender` enum('m','f') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`last_name`, `first_name`, `dob`) USING BTREE
) 

将last_name, first_name, dob设为主键

全值匹配

全值匹配是指和索引中的所有列进行匹配

mysql> explain select * from index_test where last_name='Cuba' and first_name='Allen' and dob='1960-01-01';
+----+-------------+------------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | const | PRIMARY       | PRIMARY | 407     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+

可以看到查询经过了主键索引

匹配最左前缀
只使用索引的第一列

mysql> explain select * from index_test where last_name='Viven';
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | ref  | PRIMARY       | PRIMARY | 202     | const |    2 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+

匹配列前缀

explain select * from index_test where last_name like 'V%';
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | range | PRIMARY       | PRIMARY | 202     | NULL |    2 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

比如查找以所有名字以V开头的人；

匹配范围值

mysql> explain select * from index_test where last_name between 'Angelina' and 'Kisten';
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | range | PRIMARY       | PRIMARY | 202     | NULL |    7 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

精确匹配某一列并范围匹配另一列

mysql> explain select * from index_test where last_name like 'K%' and first_name='Allen';
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | range | PRIMARY       | PRIMARY | 404     | NULL |    2 |    10.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

关于B-Tree索引的限制

• 如果不是按照索引的最左列开始查找，则无法使用索引；
  未从最左列last_name开始查找，无法使用索引

mysql> explain select * from index_test where first_name='Allen';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

同样的

explain select * from index_test where dob='1980-03-04';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

• 不能跳过索引中的列；
  如果跳过索引的一列，则只能只用索引的第一列

//跳过了first_name  索引只用了第一列
mysql> explain select * from index_test where last_name ='Viven' and age=5 and dob='2019-04-30';
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | ref  | PRIMARY       | PRIMARY | 202     | const |    2 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

//没有跳过任意一列，索引用了查询的全部列
mysql> explain select * from index_test where last_name ='Viven'and first_name='Basinger' and age=5;
+----+-------------+------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | ref  | PRIMARY       | PRIMARY | 408     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+

• 如果查询中有某个列的范围查询，则其右边的列都无法使用索引优化查找；

mysql> explain select * from index_test where last_name='Viven' and first_name='Bas%';
+----+-------------+------------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | ref  | PRIMARY       | PRIMARY | 404     | const,const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+---------+---------+-------------+------+----------+-------+

mysql> explain select * from index_test where last_name='Viven' and first_name='Bas%' and dob='2018-01-11';
+----+-------------+------------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | ref  | PRIMARY       | PRIMARY | 404     | const,const |    1 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+

可以看到在ref列只有前面两列使用了索引；

哈希索引

hash index 基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎会对所有索引列计算一个hash码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每一个数据行的指针。

在mysql中，只有Memory引擎显式支持哈希索引，Mamory的默认索引类型，支持非唯一哈希索引。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

create table testhash(
    -> fname varchar(50) not null,
    -> lname varchar(50) not null,
    -> key using hash(fname)
    -> )engine=memory;

假设：

f('Arjen')=2323
f('Baron')=7437
f('Peter')=8784
f('Vadim')=2458

则哈希索引的数据结构

槽(Slot)	值(Value)
2323	指向第一行的指针
2458	指向第四行的指针
7437	指向第二行的指针
8784	指向第三行的指针

 explain select lname from testhash where fname='Peter';
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | testhash | NULL       | ref  | fname         | fname | 202     | const |    2 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+

先计算Peter的哈希值，并使用该值从hash表中寻找对应记录指针，找到指向数据表第三行的指针，比较第三行的值是否为Peter，以确保就是要查找的行。

哈希索引的限制

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；
• 哈希索引数据并不是按照索引值顺序存储，所以无法用于排序；
• 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较查询，不支持任何范围查询；
• 出现哈希冲突时，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有缝合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

InnoDB引擎中有一个功能叫"自适应哈希索引",当InnoDB注意到某些索引值被使用的非常频繁的时候，它会在内存中基于B-Tree缩影智商在创建一个哈希索引，这样让B-Tree索引页具有了哈希所用的一些优点。

空间数据索引(R-Tree)

全文索引

高性能的索引策略

独立的列

如果查询中的列不是独立的，则mysql不会使用索引。“独立的列”是指索引列不能使表达式得一部分，也不能是函数的参数。

mysql> explain select * from index_test where id=10-1;
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from index_test where id+1=10;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

我们应该养成where条件的习惯，始终将索引列单独放再比较符号得一侧：

前缀索引和索引选择性

有时候需要所以很长的字符列，这会让索引变得大且慢。需要选择前缀索引，选择足够长的前缀以保证较高的选择性，同时又不能太长(以便节约空间)。应该使得前缀索引的选择性接近于索引整个列。

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高Mysql的查询功能。Mysql5.0和更新版本引入了一种"索引合并"的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

聚簇索引

聚集数据的一些优点：

• 可以把相关数据保存在一起，以减少磁盘IO次数；
• 数据访问更快，将索引和数据保存在同一个B-Tree中。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

• 如果数据全部都放在内存中，则访问顺序没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行或者逐渐被更新导致需要移动行的时候，可能面临页分裂问题。当行的主键值要求必须将这一行插入某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂。会导致表占用更多的磁盘空间。
• 可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引可能比想像的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。因为二级索引中保存的行指针不是指向行的物理位置的指针，而是行的主键值。这意味着通过通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。

在InnoDB表中按主键顺序插入行
应该选择一个顺序自增列作为逐渐，这样可以保证数据行是按顺序写入的，对于根据主键做关联操作的性能也会更好。

使用顺序自增列建立索引和使用无序随机的uuid建立索引的对比

• 顺序自增列索引

把每一条记录都存储在上一条记录的后面，当达到页的最大填充因子是，默认为页大小的15/16，流出部分空间用于以后修改，下一条记就会写入新的页中。

• 使用无序uuid索引

• 写入的目标页可能已经刷到磁盘中，并从缓存中移除，或者是还没有被加载到缓存中。InnoDB在插入之前不得不先找到并从磁盘中读取目标也到内存中，这将导致大量的随机IO;
• 因为写入是乱序的，InnoDB不得不频繁地做分页操作，以便为新的行分配空间。页分裂会导致移动大量数据。
• 由于频繁的页分裂，页会变得系数并被不规则地填充，所以最终数据会有碎片化。

覆盖索引

如果一个索引包含所有需要查询的字段的值，就称为覆盖索引；最大的好处就是可以直接根据索引查到要查询的数据，而不需要从表中查找。

只扫描索引而无须回表所带来的好处

• 索引条目通常远小于数据行大小，会极大第减少数据访问量，这对缓存的负载是很重要的，觉少了数据拷贝的时间；
• 索引是按照列值顺序存储，所以对于IO密集型范围查询会比随即从磁盘读取每一行数据的IO要少得多。
• 如MyISAM这种引擎在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能导致严重的性能问题。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表有特别的作用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

使用覆盖索引的场合

覆盖索引必须要储存索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值。所以mysql只能使用B-Tree索引做覆盖索引。

无法使用覆盖索引的情况

• 使用select * 的时候，如果没有一个覆盖所有列的索引则还是需要从表中读取；
• 不能在索引中执行通配符开头的like操作，存储引擎无法作比较匹配，只能提取数据行的值，而不是索引值来做比较。

使用索引扫描来做排序

• 只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向即倒序和正序都是一样时，mysql才能够使用索引来对结果做排序。
• 有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，以第二列为最左前缀进行排序。
• 如果order by子句中有不是索引列的列，也不能走索引排序。

压缩索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存，默认只压缩字符串，通过参数设置也可以压缩整数。

压缩每个索引块的方法是先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到前缀相同前缀的字节数和剩余的不同后缀部分。
如第一个值是"perform"第二个值是"performance"，那么第二个值的前缀压缩后存储的是类似"7，ance"这样的形式。

冗余和重复索引

重复索引：是指在相同列上按照相同的顺序创建的相同类型的索引。
冗余索引：创建了(A,B)索引，再创建A索引，就是冗余索引，要是在创建(B,A)则不是，因为索引的顺序不同有很大的影响。在创建B也不是荣誉索引，因为B不是A,B的最左前缀索引。

索引和锁

索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处：

• 锁定行的时候会带来额外开销；
• 锁定超过需要的行会增加锁争用并减少并发性；