字节面试：索引的设计规范，你知道多少？

索引的设计规范主要包括索引命名、列选择、索引类型选择、索引数量控制、索引使用优化等多个方面，具体如下：

• 索引命名规范

  单值索引：建议以idx_为开头，字母全部小写。例如：alter table t1 add key idx_r1(r1);

  组合索引：建议以idx_multi_为开头，字母全部小写。例如：alter table t1 add key idx_multi_1(r1,r2,r3);

  唯一索引：建议以udx_为开头，字母全部小写；如果是多值唯一索引，则命名方式类似udx_multi_1等。例如：alter table t1 add unique key udx_f1(r1);或者alter table t1 add key udx_multi_1(r1,r2,r3);

  全文索引：建议以ft_为开头，字母全部小写，并且建议默认用ngram插件。例如：alter table t1 add fulltext ft_r1(r1) with parser ngram;

  前缀索引：建议以idx_为开头，以_prefix结尾。例如：alter table t1 add key idx_r1_prefix(r1(10));

  函数索引：建议以idx_func_为开头，字母全部小写。例如：alter table t1 add key idx_func_r1((mod(r1,4)));

• 尽量选择整型列做索引

  索引本身是有序的，整型列做索引效率更高。

  避免使用uuid等不规则字符串建立索引，推荐使用雪花id、页段id等整数id。

  如果必须使用字符串做索引，可以考虑对字符类型做HASH，再基于HASH结果做索引。

• 优先建立唯一性索引

  唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录。

  例如，为学生表中的学号字段建立唯一性索引，可以快速确定某个学生的信息。

• 为经常需要排序、分组和联合操作的字段建立索引

  经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。

  为这些字段建立索引，可以有效地避免排序操作，提高查询效率。

• 为常作为查询条件的字段建立索引

  如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。

  为这样的字段建立索引，可以提高整个表的查询速度。

• 限制索引的数目

  索引的数目不是越多越好，每个索引都需要占用磁盘空间。

  索引越多，需要的磁盘空间就越大，修改表时对索引的重构和更新也会更麻烦。

• 尽量使用数据量少的索引

  如果索引的值很长，那么查询的速度会受到影响。

  例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

• 尽量使用前缀来索引

  如果索引字段的值很长，最好使用值的前缀来索引。

  例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段的前面的若干个字符，这样可以提高检索速度。

• 删除不再使用或者很少使用的索引

  表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。

  数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

• 遵循最左前缀匹配原则

  MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。

  例如，a1="" and b="2" c=""> 3 and d = 4，如果建立(a,b,c,d)顺序的索引，d是用不到索引的；如果建立(a,b,d,c)的索引则都可以用到。

  =和in可以乱序，MySQL的查询优化器会优化成索引可以识别的形式。

• 尽量选择区分度高的列作为索引

  区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例。

  比例越大扫描的记录数越少，唯一键的区分度是1，一些状态、性别字段可能在大数据面前区分度就是0。

  一般需要join的字段要求区分度是0.1以上，即平均1条扫描10条记录。

• 索引列不能参与计算，保持列“干净”

  例如，from_unixtime(create_time) = '2014-05-29'就不能使用到索引。

  语句应该写成create_time = unix_timestamp('2014-05-29');

• 尽量的扩展索引，不要新建索引

  例如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

• 考虑建立联合索引来提高查询效率

  当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率。