mysql索引简记

索引：索引是提升查询速度的一种数据结构。插入时对数据进行了排序。MySQL 当前支持 B+树索引、全文索引、R 树索引；

B+ 树索引的高度通常为 3~4 层，高度为 4 的 B+ 树能存放 50 亿左右的数据；由于 B+ 树的高度不高，IO查询效率极高

• 普通索引和唯一索引：二者查询没有大区别。只在更新时，唯一索引必须读入内存判断索引是否唯一，普通索引可以把更新操作缓存在 change buffer 中，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。写多读少的情况下，使用普通索引性能更好。redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

• mysql索引采用B+树的演变

  • 二分查找可以快速定位查找数据
  • 为了解决二分查找极端情况退化为链表，引入平衡二叉树AVL
  • 为了压缩AVL的高度，引入B树
  • 为提高插入删除效率和范围查找，引入B+树。B+树非叶节点仅存储索引，更加矮胖，插入删除不会像 B 树那样会发生复杂的树的变化；叶子节点用双向链表连接，支持范围查询。
  • 页间通过B+树的逻辑结构实现二分查找，页内通过进一步划分槽，通过槽二分查找，槽内顺序查找，槽内记录数量有限制。

• 叶子节点是否包含数据分成聚簇索引和非聚簇索引，还有包含某个查询的所有列的覆盖索引等等。

• 联合索引遵循最左匹配原则。（ or 联合索引会失效，in不一定）

  • 联合索引从最左的索引列开始匹配，一但左边的列没有被使用，那么右边的所有列都无法走索引
  • 索引使用了某个列的范围查询，范围查询的列可以走联合索引，但是范围查询列后的列都不能走索引

• 有索引不用索引

  • 使用了 !=、LIKE 之类的查询。
  • 字段区分度不大。比如说你的 status 列只有 0 和 1 两个值，那么数据库也有可能不用。
  • 使用了特殊表达式，包括数学运算和函数调用。
  • 数据量太小，或者 MySQL 觉得全表扫描反而更快的时候。

• 索引与 NULL

  • 唯一索引允许有多行的值都是 NULL，但使用NULL是比较差的实践
  • 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂
  • NULL 值是一个没意义的值，但是它会占用物理空间，行格式中至少会用 1 字节空间存储 NULL 值列表

• 索引下推：条件判断的字段在二级索引的B+树里，就会下推到存储引擎层过滤，再回表，减少了回表次数

• 堆表中的索引都是二级索引，哪怕是主键索引也是二级索引，都要进行回表。索引组织表主键是聚集索引，索引的叶子节点存放表中一整行完整记录，索引组织表对比堆表，在海量并发的OLTP业务中能有更好的性能表现；

• 函数索引可以快速解决线上SQL的性能问题，虚拟列不占用实际存储空间，在虚拟列上创建索引本质就是函数索引。

• 索引失效：

  • 使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
  • 查询条件中对索引列使用函数，就会导致索引失效
  • 查询条件中对索引列进行表达式计算，也是无法走索引的
  • 遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，隐式转换相当于对索引使用函数，会导致索引失效
  • 联合索引要能正确使用需要遵循最左匹配，否则失效
  • OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

• CBO

  • 基于成本的优化器。其会判单每个索引的执行成本，从中选择出最优的执行计划
  • 可以通过 EXPLAIN 命令查看每个 SQL 的成本
  • 一般只对高选择度的字段和字段组合创建索引
  • 低选择性，但是数据存在倾斜，通过索引找出少部分数据，可以考虑创建索引
  • 若数据存在倾斜，可以创建直方图，让优化器知道索引中数据的分布，进一步校准执行计划

• 适用索引：

  • 字段有唯一性限制
  • 经常用于 WHERE 查询条件的字段
  • 经常用于 GROUP BY 和 ORDER BY 的字段

• 不需要创建索引

  • WHERE 条件，GROUP BY，ORDER BY 里用不到的字段
  • 字段中存在大量重复数据，数据分布均匀
  • 表数据太少
  • 经常更新的字段不用创建索引

• 索引优化

  • 前缀索引优化
  • 覆盖索引优化
  • 主键索引最好是自增
  • 防止索引失效