MySQL 索引小结 | Ray's Blog

简介

索引（在MySQL中也叫做键key）是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键。尤其是当表中的数据越来越大时，索引对性能的影响愈发重要。

索引的优点

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O

索引的缺点

创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大
索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大
在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护

数据结构

哈希索引

name	age
Jane	28
Peter	20
David	30

假设使用假想的哈希函数f()，生成对应的设想值：
f(‘Jane’) = 2323
f(‘Peter’) = 2456
f(‘David’) = 2400

则哈希索引的数据结构如下：

槽(slot)	值(value)
2323	指向第1行指针
2400	指向第3行指针
2456	指向第2行指针

哈希索引是Memory引擎默认采用索引，索引的结构十分紧凑，索引只包含哈希值和行指针，而不存储字段值

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

限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能用索引中的值来避免读取行。
哈希索引数据不是按照索引值的顺序排序的，所以也无法用于排序和范围查询
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的
哈希值只支持等值比较查询，也不支持任何范围查询
访问哈希索引的数据非常快，除非有哈希冲突，如果哈希冲突很多的话，一些索引的维护操作代价也会很高

B-Tree索引

B树索引

B-Tree索引能加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中的值的上限和下限。最终存储引擎要么是找到对应的值要么该记录不存在。

而叶子节点比较特别，他们的指针都是指向被索引的数据，而不是其他的节点页。B-Tree对索引列是顺序组织存储的所以很适合查找范围数据。

以InnoDB为例，从技术上来说使用的是B+Tree。是B-Tree的改进版本，同时也是数据库索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

可以使用B-Tree索引的查询类型

全值匹配，即和索引中的索引列进行匹配
匹配最左前缀
匹配列前缀，只匹配某一列的值的开头部分
匹配范围值
精确匹配某一列并范围匹配另外一列
只访问索引的查询，即查询只需要访问索引而不需要访问数据行

为什么索引结构默认使用B+Tree，而不是hash，二叉树，红黑树， B-Tree？

B+树索引的有序性，叶节点被双向链表连接，方便支持范围查找，可以分批加载至内存，而Hash不支持范围查找
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
红黑树：树的高度随着数据量增加而增加，IO代价高。在大规模数据存储的时候，红黑树(二叉查找树)往往出现由于树的深度过大而造成磁盘 IO 读写过于频繁，进而导致效率低下的情况。B 树可以有多个子女，从几十到上千，可以降树的高度。磁盘 IO 代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘 IO 频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，红黑树查找一个节点最多要查 logN 层，每一层都是一个内存页。虽然你只是想找一个节点，但硬盘必须一次读一个页，那么一共 logN 次 IO，消耗太大。
B+树的数据都集中在叶子节点，分支节点只负责索引。 B树的分支节点也有数据。所以 B+树的树高会小于 B 树，平均的 IO 次数会远大于 B+树。B+树索引节点没有数据，比较小，可以把索引完全加载至内存中。B+树更擅长范围查询。叶子节点数据是按顺序放置的双向链表。 B树范围查询只能中序遍历，做不到范围查询。

聊聊最左前缀匹配

如果不是按照索引的最左列开始查找，则无法使用索引。因为查找必须从根节点出发
不能跳过索引中的列，若跳过列，剩余的记录都是乱序状态，无法利用索引
如果查找中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找。根据B-Tree索引的特性，我们知道匹配前缀是可行的，但是下层的子节点的顺序会被打乱，从而导致索引失效

索引失效的情况

索引列在表达式或函数中
联合索引中，非最左前缀
联合索引中，最左前缀，但是中间有范围查询，那么范围查询后面的列都用不到索引
in 查询语句中多个值的数据类型不一致的情况
在无索引的列上使用了 or 那么有索引的列也用不上
查询的列中采用了!=
存在索引列的数据类型隐形转换

采用自增主键？

如果正在使用InnoDB表没有什么数据需要聚集，可以定义一个代理键作为主键，这种主键的数据应当和应用无关，最简单的方式是使用AUTO_INCREMENT自增列。这样可以保证数据行是顺序写入，对于主键做关联操作的性能也会更好。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型应用。 例如UUID聚簇索引。他会使得索引的插入完全随机，使得数据完全没有聚集特性。自增ID也不便频繁update

UUID聚簇索引缺点如下：

写入目标可能已经刷到磁盘上并从缓存中移除，或者是还没有加载到缓存中，InnoDB在插入前不得不找到并从磁盘中读取目标页到内存中。导致了大量的随机I/O
因为写入是乱序的，InnoDB不得不频繁的做页分裂、页旋转操作，以便为新行分配空间。页分裂操作导致了大量数据的移动，一次插入最少需要修改三个页而不是一个页
由于频繁的页分裂，页会变得稀疏并被不规则填充，数据会有碎片，每次操作需要做一次OPTIMIZE TABLE进行优化

全文索引

全文索引是一种特殊类型的索引，他查找的是文本的关键字，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。他有许多注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎干的事情，而不是简单的WHERE条件匹配。

在相同的列上同时创建全文索引和基于B-Tree的索引不会产生冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE操作

索引类型

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

主键索引

是一种特殊的唯一索引，不允许有空值。(主键约束，就是一个主键索引)

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

PS：如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

前缀索引和索引选择性

有时索引很长的字符列，会导致索引变得又大又慢。通常可以索引开始的部分字符来节约索引空间，提高索引效率，但是会牺牲索引的选择性。

索引的选择性：不重复的索引值和数据表的记录总数(#T)的比值，范围从1/#T到1之间，选择性越高说明查询效率越高

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，MySQL不允许索引这些列的完整长度

联合索引

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

为什么使用联合索引？

减少开销。建一个联合索引 (col1,col2,col3) ，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销!
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的 sql:
1
select col1,col2,col3 from test where col1=1 and col2=2
那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机 io 操作。减少 io 操作，特别的随机 io 其实是 dba 主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
效率高。索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出 10%的数据，如果只有单值索引，那么通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2 and col3= 3 的数据，然后再排序，再分页; 如果是联合索引，通过索引筛选出 1000w10% 10% *10%=1w，效率提升可想而知!

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。以InnoDB为例，其聚簇索引实际上在同一个结构中保留了B-Tree索引和数据行

当表有聚簇索引时，他的数据行实际上存放在索引的叶子页中，术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起

因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

叶子页包含了行的全部数据，但是节点页只包含了索引列

如图：

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

非聚簇索引

聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录

我们举例说明：

CREATE TABLE layout_test(
  col1 int NOT NULL,
  col2 int NOT NULL,
  PRIMARY KEY(col1),
  KEY(col2)
);

其中col1作为主键在InnoDB中作为聚簇索引，而col2作为非聚簇索引

如下为聚簇索引和非聚簇索引的数据结构分布情况：

主键分布

二级索引分布

我们看到二级索引的叶子节点包含了引用行的主键列，这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后再根据这个值去聚簇索引中查找到对应的行，我们把这种操作称为“回表”

索引下推优化

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

举例说明，如下，在表 T 创建联合索引 index(name,age)：

1	select * from T where name like '提莫%' and age = 100；

很明显的根据最左前缀匹配原则，我们知道name列的最左前缀导致了右侧一列age列的索引乱序失效了。所以我们最多只能索引到name一列

没有索引下推优化时，此时age列被忽略掉了，MySQL直接根据name的前缀情况查询出了若干个主键（里面会有不满足条件的数据），随后MySQL将这些数据带入主键索引树中进行回表查询，查出来了若干条记录（里面有不满足的），再使用WHERE条件查询对age列进行过滤。

这就很坑了，因为我们为了各条不满足情况的数据都进行了回表操作，大大的增加了I/O次数，最后再进行筛选显然是不明智的

无索引下推

而有索引下推时，我们将不正确的信息提前在回表前过滤掉，在索引内部取到name结果之后（步骤3），步骤4就顺便判断了结果中的age是否等于100，对于不等于100的记录直接跳过，这样可以大量的减少回表次数，提高效率

有索引下推

覆盖索引

MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含了要查询的数据，就没有必要再进行回表查询了。如果一个索引包含或者覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”

例如在上文回表举的例子中，有如下select语句

1	select col1 from layout_test where col2 = 13

我们通过col2非聚簇索引进行检索，获得了对应的若干个主键的col1的值，到此为止我们已经获得了所需要的一切，并不需要将拿到的主键值代回聚簇索引继续查找，省去了一次的回表，提高了效率

好处：

通过覆盖索引，MySQL可以极大的减少数据访问量
因为索引是按照列值顺序存储的（至少在单页如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O少得多
通过InnoDB的覆盖索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

慢查询分析

开启慢查询日志

慢查询日志介绍

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

参数设置

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

开启方式

修改配置文件在 my.ini 增加几行: 主要是慢查询的定义时间，以及慢查询log日志记录（ slow_query_log）
通过MySQL数据库开启慢查询:（重启失效）

记录内容

第一行：记录时间
第二行：用户名、用户的IP信息、线程ID号
第三行：执行花费的时间【单位：秒】、执行获得锁的时间、获得的结果行数、扫描的数据行数
第四行：这SQL执行的时间戳
第五行：具体的SQL语句

Explain分析慢查询SQL

示例

在查询语句前加入SELECT即可得到具体的分析情况：

mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%aaaa%' ;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1200009 |    13.86 | Using where |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

各列属性

id:SELECT的查询序列号，体现执行优先级，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
select_type:表示查询的类型。
table:输出结果集的表，如设置了别名，也会显示
partitions:匹配的分区
type:对表的访问方式
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

慢查询常用属性

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

存在的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从低到高）

ALL：(Full Table Scan) MySQL将遍历全表以找到匹配的行，常说的全表扫描

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

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

key列显示了SQL实际使用索引，通常是possible_keys列中的索引之一，MySQL优化器一般会通过计算扫描行数来选择更适合的索引，如果没有选择索引，则返回NULL。当然，MySQL优化器存在选择索引错误的情况，可以通过修改SQL强制MySQL“使用或忽视某个索引”。
- 强制使用一个索引：FORCE INDEX (index_name)、USE INDEX (index_name)
- 强制忽略一个索引：IGNORE INDEX (index_name)
rows

rows是MySQL估计为了找到所需的行而要读取（扫描）的行数，可能不精确。
Extra

这一列显示一些额外信息，很重要。
- Using index
  
  查询的列被索引覆盖，并且where筛选条件是索引的是前导列，Extra中为Using index。意味着通过索引查找就能直接找到符合条件的数据，无须回表。
- Using where
  
  说明MySQL服务器将在存储引擎检索行后再进行过滤；即没有用到索引，回表查询。
  
  可能的原因：
  1. 查询的列未被索引覆盖；
  2. where筛选条件非索引的前导列或无法正确使用到索引；
- Using temporary
  
  这意味着MySQL在对查询结果排序时会使用一个临时表。
- Using filesort
  
  说明MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。
- Using index condition
  
  查询的列不全在索引中，where条件中是一个前导列的范围
- Using where;Using index
  
  查询的列被索引覆盖，并且where筛选条件是索引列之一，但不是索引的前导列或出现了其他影响直接使用索引的情况（如存在范围筛选条件等），Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据，影响并不大。