MySql的索引分类、数据结构以及优化细节

实操案例： 选错「索引」导致的线上慢查询
explain指令：MySQL Explain详解

B树和B+树各自的优势：为什么 Mongodb 索引用 B 树，而 Mysql 用 B+ 树?

索引的分类

• 主键索引（不允许空值）
• 唯一索引（允许空值）
• 普通索引（其他普通列）
• 全文索引（关键字查询，mysql不推荐用）
• 组合索引（一个索引包含多个列）

mysql数据结构

• 树形结构的演化
  二叉树->BST（banary search tree，实现了二分查找，但是插入递增数据的话，会退化成近似链表的结构）->AVL Tree（自动旋转，最短子树和最长子树高度之差不超过1，查找性能提升是以插入性能降低为前提的）-> BRT（black red tree，最长子树和最短子树高度之差不超过1倍，在插入性能以及查询性能之间做了平衡，适用于插入频率和查询频率近似相等的场景）

• mysql为什么不使用以上树形结构

  • IO读取数据的最小单位是页（8K/16K等），每个节点上的数据不能保证对齐；
  • 数据量大的时候，树型结构变深，增加IO次数，降低效率。

• B-Tree

  • b树有个概念叫degree，可以定义每个节点存储的数据量（多个数据可以存放进一个节点，可以作为一个数据页）。
    

  • b树模型（InnoDB默认加载16K）
    

• B+树模型（非叶子节点全部存放指针和索引，数据全部放进叶子结点，减小了树的深度，提高查询效率），三层B+树可以支撑千万级别的数据量

• B+树所有的叶子结点之间存在双向链表。
  

• InnoDB–B+Tree，叶子结点直接存放数据

• 每建一个索引，就会有一颗B+树，换言之，一张表里可以有多棵树，但是带有原始数据的tree只有一颗（如下例子，name列也创建了索引，name树叶子节点存放的就是主键id的值）。
  

面试常问名词

• 回表：先查name树查到id值，再查id树找到数据，这样查多棵树的行为叫做回表，应该尽量避免。
• 索引覆盖：查询的索引信息包含查询数据，不需要回表即可返回数据，称为索引覆盖。

select id,name from tbl where name=‘zhang’;
select * from tbl where name=‘zhang’;
此两种方式查询过程不一样。

• 最左匹配：针对于组合索引，优先过滤不符合最左定义的索引的数据。

name age 组合索引
where name = ？ and age = ？
where name = ？
where age = ？
where age = ？ and name = ？ 依然会走最左匹配，优化器优化

• 索引下推：针对于组合索引，5.6之后根据name，age两个列的值去获取数据，直接返回（减少了整体io量）。

Mysql优化小细节

• 当使用索引列进行查询的时候，尽量不要使用表达式，把计算放在业务层而不是数据库层；

• 尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询（主键最好自增，否则不好维护，大小乱序会造成叶子结点分裂）；

• 使用索引的选择性。如果主键是varchar且较长（uuid），可以使用left（uid，7）来创建索引。
  

• 强制类型转化，会使索引失效，进行全盘扫描。
  

• 更新十分频繁，数据区分度不高的字段不宜建立索引。
  

• 索引列不允许为null（mybatis会把0当做null）；

• 表连接查询最好不要超过三张表；

• 能使用limit尽量使用limit，减少IO量；

• 创建索引应避免以下认知：

  • 索引越多越好
  • 过早优化，在不了解系统的情况下进行优化