MySQL索引（1)

前缀索引和索引选择性

• 前缀索引
  前缀索引是一种能使索引更小，更快的方式，即只索引开始的部分字符，这样可以节约索引空间，提高索引效率。
  对于BLOB，TEXT，很长的VARCHAR列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

ALTER TABLE dept add key(name(5))

但是这也会降低索引选择性。

• 索引选择性
  不重复的索引值和数据表的记录总数的比值，索引选择性越高则查询效率越高，因为选择性高的索引能让MySQL在查找时过滤更多的行。

• 计算索引选择性

SELECT
    count( DISTINCT LEFT ( NAME, 3 ) ) / count( 1 ) sel3,
    count( DISTINCT LEFT ( NAME, 4 ) ) / count( 1 ) sel4,
    count( DISTINCT LEFT ( NAME, 5 ) ) / count( 1 ) sel5
FROM
    dept

一般来说能够接近0.031，基本就可用。

多列索引及索引列的顺序

• 多列索引
  即索引包含多列，例如下篇文章的idx_age_deptid_name

在多个列上建立独立的单列索引大部分情况并不能提高查询性能（查询条件不止一个的情况），因为早期MySQL只能使用其中一个单列索引，5.0后引入了索引合并（index merge）策略，即可以同时使用几个索引，并将结果合并，但是在合并操作上会耗费大量资源，且MySQL还遵循range优先原则，即一个索引的连续段包含所有符合查询条件的索引时，不使用索引合并。

• 多列索引顺序经验法则
  将选择性最高的列放到索引最前列

聚簇索引

聚簇索引不是一种索引类型，而是一种数据存储方式。
InnoDB的聚簇索引实际上在同一结构中保存了B-Tree索引和数据行，即当表有聚簇索引时，数据行实际存放在索引的叶子页中。

InnoDB一定会有聚簇索引，有主键时就是主键，没有定义主键，InnoDB会选择唯一的非空索引代替，如有没有这样的索引，会隐式定义一个主键。

• 缺点

  • 插入速度依赖插入顺序，如果不按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE重新组织表。下面两张图分别为id为递增和UUID的情况。
    

  

  • 更新聚簇索引列代价高，因为会强制将被更新的行移动到新位置。
  • 可能会有‘页分裂（page split）’问题，当行插入到某个已满的页中，存储引擎会将页分裂成两个页，这会导致表占用更多的空间。
  • InnoDB的二级索引会有‘回表’问题，因为二级索引叶子节点保存的是行的主键值，而不是‘行指针’，所以使用二级索引的查询需要先找到二级索引的叶子节点获取主键值，然后去聚簇索引查找对应的行。

覆盖索引

如果一个索引包含所有需要查询的字段的值，就称为覆盖索引，MySQL只能用B-TREE索引做覆盖索引，因为只有B-TREE索引存储了索引列的值。

如果InnoDB的查询能用到覆盖索引，那么就可以避免‘回表’。

如果查询的列由索引和主键组成，也是覆盖索引，因为二级索引的叶子节点保存了主键值。

本文作者： 飞光
本文链接： https://zhucj94.github.io/article/b5580210.html
版权声明： 本作品采用 CC BY-NC-SA 4.0 进行许可。转载请注明出处！