5.1 关于索引的结论：

  • 每个索引对应一颗 B+树（一种多叉树，一般最大四层，每个内节点的分叉大约1200左右），用户记录存储在 叶子节点目录项记录内节点（非叶子节点）
  • InnoDB引擎聚集索引B+树 索引结构，也就是说：在InnoDB引擎中，索引即数据，数据即索引
  • 除主键外建立的索引都称为 二级索引（包括：联合索引、唯一索引、普通索引等），二级索引索引列+主键（可以指定联合索引，则会有多个索引列，但对结构无影响，只是在数据按照索引顺序排序的时候参照索引顺序排序），查找到叶子节点后，如果没有索引列没有覆盖查询列，则会进行 回表回表二级索引聚集索引 中查找完整用户记录）
  • B+树页双向链表页）内的记录（不论是用户记录还是目录项记录）按照索引列的值从小到大排序组成 单向链表联合索引联合索引联合索引主键 划分出顺序
  • B+树页页目录在一个页面查找数据的时候，先根据索引值通过二分法定位到对应的数据组的第一个记录，然后顺序遍历数据组得到记录值（如果是内节点，则会定位到数据所在区间的槽，取出页号到下一层比较）。起始通过索引列查找数据的时候，则会从根节点开始逐层向下搜索
  • B+树联合索引联合索引 从最左开始连续的列。如果查询器觉得使用索引的性能不如全表扫描，则会直接文件排序查找。
  • 回表B+树聚集索引二级索引顺序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+树，重建索引 是将数据重新组织防止因为插入、页分裂等操作造成的空洞，使数据更紧凑）
  • 查询字符串一定要加上单引号，否则索引失效会行锁会升级为表锁