MySQL索引的设计原则

本文已参与「新人创作礼」活动，一起开启掘金创作之路

本文参考自宋红康老师的课程

3.索引的设计原则

3.1 适合创建索引的情况

• 字段的数值有唯一性的限制

  具有唯一性的组合字段也必须建立唯一索引(来源：Alibaba)

• 频繁作为WHERE查询条件的字段

• 经常GROUP BY和ORDER BY的列

• UPDATE、DELETE、WHERE条件列

• DISTINCT字段

• 多表JOIN连接操作时，创建索引注意事项

  • 连接表的数量尽量不超过3张
  • 对WHERE条件创建索引
  • 对于用于连接的字段创建索引，该字段在多张表中的类型必须一致

• 使用列的类型小的创建索引

  此处的类型大小指该类型表示的数据范围的大小。

  • 数据类型越小，在查询时进行的比较操作越快
  • 数据类型越小，索引占用的存储空间越少，在一个数据页可以存放更多记录，从而减少磁盘IO带来的性能损耗

  计算不同长度的选择性

  先查看字段在全部数据中的选择度：

  select count(distinct address) / count(*) from shop;

  通过不同长度计算，与全表的选择性对比：

  count(distinct left(列名, 索引长度))/count(*)

  例如：（越接近1越好）

  引申的问题：索引列前缀对排序的影响

  如果只把address列的前12个字符放到了二级索引中，order by address可能会有问题(如果前12个字符相同，后面的字符不同)。因此无法支持使用索引排序，只能使用文件排序。

• 区分度高(散列性高)的列适合作为索引

  列的基数指某一列中不重复数据的个数。在记录行数一定的情况下，列的基数越大，值越分散；列的基数越小，值越集中。最好为列的基数大的列建立索引。

  计算区分度：(越接近1越好，一般超过33%就算比较高效的索引了)

  select count(distinct a)/count(*) from t1

  联合索引把区分度高的列放在前面。

• 使用最频繁的列放到联合索引的左侧

  由于最左前缀原则，可以增加联合索引的使用率。

• 在多个字段都要创建索引的情况下，联合索引优于单值索引

3.3 限制索引的数目

建议单张表索引数量不超过6个。

3.4 不适合创建索引的情况

• where使用不到的字段

• 数据量小的表

• 有大量重复的列

• 经常更新的字段或表

• 无序的值