MySQL优化（二）：索引的类型、匹配原则、创建原则

索引的优缺点

常见的索引结构有: B 树， B+树和 Hash。

• 优点：加快数据的检索速度
• 缺点：
  1. 创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  2. 占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

索引类型

聚簇索引（主键索引）

聚簇索引即索引结构和数据一起存放的索引，结构为B+树。
InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

聚簇索引结构如下：

非聚簇索引（二级索引、辅助索引）

在聚簇索引之上创建的索引称之为辅助索引（二次索引），辅助索引节点只包含索引列值和主键值，因此辅助索引访问数据总是需要二次查找（即：通过二次索引找到主键值后回到聚簇索引找到对应的数据行）。
辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
Innodb辅助索引非叶子节点只有索引列（不含主键），叶子节点并不包含行记录的全部数据，叶子节点只包含索引列和相应行数据的聚簇索引键。

Innodb二级索引，索引列值全相同的情况下，节点按主键值排序。

二级索引结构如下：

索引匹配的原则

最左匹配原则

如果创建一个联合索引, 此索引的任何前缀都会用于查询, 例如：
(col1, col2, col3)这个联合索引的所有前缀就是(col1), (col1, col2), (col1, col2, col3), 包含这些列的查询都会启用索引查询.
其他所有不在最左前缀里的列都不会启用索引, 即使包含了联合索引里的部分列也不会使用索引. 即上述中的(col2), (col3), (col2, col3) 都不会启用索引去查询.
注意：(col1, col3)会启用(col1)的索引查询。

无法使用索引的场景

1. 复合索引的情况下，查询条件不满足索引最左的原则
2. Mysql估计使用索引比全表扫描慢
3. 索引 不能是表达式（函数）的一部分
   只有独立的列能使用索引，如：SELECT * FROM table WHERE id -1 = 1000; 无法使用索引 因为索引 id-1 不为独立的列
4. 负向查询（not , not in, not like, <>, != ,!>,!< ） 不会使用索引
5. 以%开头的LIKE查询不能够利用B-tree索引
6. 用or分割开的条件，or前条件有索引，or后的列没有索引

   因为or后面的条件没有索引，那么后面的查询肯定要进行全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。

索引创建的原则

• 不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）
• 不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长），容易导致页分裂及随机IO，影响插入的速度。推荐使用自增值作为主键。
• 索引列的顺序：
  1. 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
  2. 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
  3. 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
• 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）
  冗余索引示例：index(a,b,c)、index(a,b)、index(a)
• 对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引
覆盖索引的好处：
避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。
可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

使不使用索引的依据到底是什么？

在满足了使用索引的条件下，是否使用索引取决于使用索引的成本。

此段摘抄自：MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！

MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。

答案很简单：成本。对于使用二级索引进行查询来说，成本组成主要有两个方面：
1. 读取二级索引记录的成本
2. 将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。

查询列不在二级索引时，要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）。

参考

• 什么是索引?
• 官方文档解释MySQL最左匹配(最左前缀)原则
• MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！
• MySQL优化指南
• mysql 存在索引但不能使用索引的典型场景
• 聚簇索引和非聚簇索引(通俗易懂 言简意赅)