数据库性能：索引及其优化

本文主要介绍数据库中索引及优化：

• Mysql的存储结构
  • B树和B+树
  • Mysql中的槽
  • B+ 树是如何进行记录检索的
• Mysql两种引擎的区别
  • MyISAM 和 InnoDB
• 索引名词
  • 主键索引、唯一索引、聚簇索引和非聚簇索引、覆盖索引、联合索引、索引下推、回表查询、索引代价
  • 索引判断
• 索引失效
• 优化建议
• Explain

正文开始

Mysql的存储结构

• 什么是B树
  • 任意非叶子结点最多只有M个儿子
  • 非叶子结点的关键字个数=儿子数-1
  • 关键字集合分布在整颗树中,任何一个关键字出现且只出现在一个结点中，该节点包含所有关键字信息。
• 什么是B+树
  • B+树的叶子节点互相间是双向链表，树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录，由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
  • B树是每个关键字都保存数据，而B+树除了叶子节点，其他节点保留的是指针，可以使得树更矮，所以IO操作次数更少。
  • 所有MySQL索引都以B树的形式保存
• Mysql中的槽
  • Mysql的页中存储来额很多数据，这些数据分组后，映射为一个slot，slot记录的是该组的最大地址，数据查询的时候，首先在slot上进行二分查找，最后通过slot进入分组，在分组内的链表上进行顺序查找。
• B+ 树是如何进行记录检索的
  • 如果通过 B+ 树的索引查询行记录，首先是从 B+ 树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。
• 参考博客：b树和b+树的区别

Mysql两种引擎的区别

• Mysql两种引擎的区别
  • InnoDB支持事务，MyISAM不支持
  • InnoDB支持外键，而MyISAM不支持
  • InnoDB是聚集索引，MyISAM是非聚集索引，因此InnoDB表必须有唯一索引（如主键），而Myisam可以没有
  • InnoDB不保存表的具体行数,而MyISAM用一个变量保存了整个表的行数
  • Innodb不支持全文索引，而MyISAM支持全文索引
  • InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
• 参考博客：MyISAM与InnoDB 的区别

索引名词

• 主键索引

  • 用主键作为索引
  • 主键索引和非主键索引的区别是：
    • 非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，
    • 其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引

• 唯一索引

  • 索引列的所有值都只能出现一次，即必须唯一
  • 允许有空值。
  • 如果是组合索引，则列值的组合必须唯一。
  • 在对该列进行增或改时，首先会检查是否重复，在执行增改操作，否则报出duplica错误，拒绝操作

• 聚簇索引和非聚簇索引

  • 同上

• 单列索引和组合索引

  • 组合索引大概率能用上覆盖索引（组合索引也叫联合索引，复合索引）
  • 联合索引：目录页由name列，age列，页号这三部分组成。目录会先按照name列进行排序，当name列相同的时候才对age列进行排序

• 覆盖索引

  • 通过该索引取到的数据涵盖来要求，如果不涵盖的话，会产生回表查询

• 索引下推

  • 5.6引入了索引下推优化，可以在遍历索引的过程中，对索引中包含的字段做判断，直接过滤掉不满足条件的数据，减少回表次数
    

• 最左前缀原则

  • 定位到最左边，然后向右遍历寻找，就是我们所说的最左前缀原则
  • 对于已经建立的索引，对自动对查询语句进行优化，按照第一项进行匹配，第二项匹配进行方式，就做最左前缀原则。
  • 如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描

• 回表查询

  • 一次索引查询取到的数据不够，需要根据这个主键去聚簇索引中查询其他信息。
  • 非覆盖索引，都会造成回表查询
  • 样例

select * from student where name = 'aa';//有回表
select name, age from student where name = 'aa';//没有回表

• 索引判断

  • 假设ID是int类型，name是char类型，并建立了相应的索引，下面四条语句，哪些走索引？
    • select * from table where id = “1”; //1
    • select * from table where id = 1; //2
    • select * from table where name = “1”; //3
    • select * from table where name = 1; //4
  • 这两句中，在ID存储的都是int类型的情况下，2,3毫无疑问使用了索引，
  • 等式两边类型不一致，会进行格式转换，一般来说是字符串转成数字，
    • “1”会转换成1，其他字符会转换成0
    • 验证：select ‘a’=0,select ‘1’=1;
  • 第4句如果走索引，需要把所有的name都转换成数字，才可以对比，有点得不偿失，因此走全表扫描（并且字符转换涉及到字符编码方式，不同编码方式，转换出来的结果不一样）
  • 延伸：select * from table where name = “1”+1；也不会走索引。

• 索引代价

  • 每修改数据记录，相关的索引就必须刷新一次。
  • 优化建议：为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。
  • 删除不经常使用的索引

索引失效

• 不满足最左前缀原则的索引会失效

  • 前导模糊查询不会使用索引，例如 like %李
  • 不要在索引列上进行运算或使用函数（见索引判断）
  • 负向条件索引不会使用索引，建议用in。负向条件有：!=、<>、not in、not exists、not like 等

• 参考博客：

  • 搞懂索引为什么会失效
  • 索引什么时候失效 和 如何排查慢查询

优化建议

• 利用explain(执行计划)来查看是否利用到了索引，进一步修改，尽量做到覆盖索引，避免回表查询。

• 主键尽量使用递增，避免雪花ID、UUID。非递增的主键会导致频繁的页分裂和合并。

  • InnoDB会在索引树上加写锁（x-latch）。在操作频繁的系统中这可能会是个隐患。它可能会导致索引的锁争用（index latch contention）。
  • 如果表中没有合并和分裂（也就是写操作）的操作，称为“乐观”更新，只需要使用读锁（S）
  • 带有合并也分裂操作则称为“悲观”更新，使用写锁（X）

• Using filesort

  • MySQL中，有两种方式生成有序结果集：
    • 通过有序索引顺序扫描直接返回有序数据
    • Filesort排序，对返回的数据进行排序
  • 因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作，
  • 所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EXPLAIN分析查询时，Extra显示为Using filesort，当出现Using filesort时对性能损耗较大，所以要尽量避免Using filesort

• 尽量使用联合索引（索引覆盖索引下推）

Explain

• 参考博客：explain