MySQL阅读笔记——5.索引的使用

5.1 关于索引的结论：

• 每个索引对应一颗 B+树（一种多叉树，一般最大四层，每个内节点的分叉大约1200左右），用户记录存储在 叶子节点 ，目录项记录 （每个用户记录的页的最小项称为目录项）存储在 内节点（非叶子节点）

• InnoDB引擎 自动将数据以为主键为 聚集索引 建立 B+树 索引结构，也就是说：在InnoDB引擎中，索引即数据，数据即索引

• 除主键外建立的索引都称为 二级索引（包括：联合索引、唯一索引、普通索引等），二级索引 的叶子节点包含的数据是 索引列+主键（可以指定联合索引，则会有多个索引列，但对结构无影响，只是在数据按照索引顺序排序的时候参照索引顺序排序），查找到叶子节点后，如果没有索引列没有覆盖查询列，则会进行 回表 操作（回表 就是通过 二级索引 找到主键后再到 聚集索引 中查找完整用户记录）

• B+树 每层节点（也就是 页 ）按照索引列值从小到达的顺序排序组成 双向链表 ，而且每个节点（页）内的记录（不论是用户记录还是目录项记录）按照索引列的值从小到大排序组成 单向链表 ，如果是 联合索引 则页面和记录先按照 联合索引 的顺序排序，之后按照主键顺序排序，这样即使 联合索引 指定的列值相同也能通过 主键 划分出顺序

• B+树 每层节点（也就是 页 ）都按照索引列的值在靠近页尾部的部分建立了 页目录 （Page Directory）的存储结构。在一个页面查找数据的时候，先根据索引值通过二分法定位到对应的数据组的第一个记录，然后顺序遍历数据组得到记录值（如果是内节点，则会定位到数据所在区间的槽，取出页号到下一层比较）。起始通过索引列查找数据的时候，则会从根节点开始逐层向下搜索

• B+树 中索引都是按照索引列从小到大顺序排好序的，如果想使用 联合索引 中尽可能多的列则索引顺序必须是 联合索引 从最左开始连续的列。如果查询器觉得使用索引的性能不如全表扫描，则会直接文件排序查找。

• 回表 的代价在于两次查询 B+树 和查询 聚集索引 时候的随机I/O（访问 二级索引 索引列是从小到大排好序顺序存储在磁盘上的，是 顺序I/O），如果使用 二级索引 需要 回表 的记录太多，则查询优化器会直接扫描 聚集索引

全表扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足，把符合搜索条件的记录加入到结果集，即：找到叶子节点左侧最小值后顺序遍历双向链表依次和查询条件比较（全表扫描 过程中内节点没参与，但是计算全表扫描的 I/O成本 时将所有节点考虑进去了）

建立了索引不一定使用，只有在 二级索引+回表 的代价比 全表扫描 低时，才会使用索引

对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的范围区间（LIKE匹配字符串前缀也行），也就是说这些搜索条件都可能使用到索引，把一个查询中可能使用到的索引称之为possible keys

5.2 索引最佳实践

• 最好在查询列中只包含索引列（即：覆盖索引省去 回表 操作）

• 列值的重复度尽可能的低

• 为经常用于排序、搜索、分组的列建立索引

• 索引列的类型尽量小（主键更加适用，因为除外了 聚集索引 外，其他所用的 二级索引 都会存储表的主键）

• 如果字符串很长，可以只对字符串的前几个字符建立索引（缺点是order by排序的时候无法使用索引排序）

• 主键具有AUTO_INCREMENT，避免主键的忽大忽小（在一页中指定主键插入数据，可能造成页分裂，影响性能）

• 经常查询以某后缀结尾的列值时候，在存储时可以以列值倒序存储（通配符'%'，放在索引字符串后面任可用到索引，放在前面和中间则不可）

• 如果表中只有一个索引，并且是唯一索引（一般是身份证等业务编号），则不用另外建立唯一自增id，直接将业务id作为主键，防止回表查询

• 通过alter table T engine=InnoDB重建索引（无论创建索引还是删除索引，都会重建整个B+树，因此删除后添加过程正确但是语句错误，重建了两次B+树，重建索引 是将数据重新组织防止因为插入、页分裂等操作造成的空洞，使数据更紧凑）

• 查询字符串一定要加上单引号，否则索引失效会行锁会升级为表锁