小北说在前面：

在一线互联网企业种，如网易、美团、字节、如阿里、滴滴、极兔、有赞、希音、百度、美团等大厂，数据库的面试题，一直是核心和重点的提问点，比如前段时间有位小伙伴面试字节，就遇到了下面这道面试题：

索引的设计规范，你知道那些？

小伙伴虽然用过索引，但是索引的设计规范忘记得一干二净，回答也是朦朦胧胧、支支吾吾， 当然，面试也就挂了。

在这里，小北给大家做一下系统化、体系化的梳理，按照下面的套路去回答，可以充分展示一下大家扎实的 “技术功底”，让面试官眼前一亮。

这个题目以及参考答案，也会收录入咱们的 《[小北Java面试宝典PDF][Java_PDF]》V154版本，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。

本文目录：

-1、索引原理

-2、索引的分类

-3、索引的优缺点

-4、参考的索引设计规范

-4.1 索引命名规范

-4.2 尽量选择整型列做索引

-4.3 优先建立唯一性索引

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

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

-4.6 限制索引的数目

-4.7 尽量使用数据量少的索引

-4.9 尽量使用前缀来索引

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

-4.11 最左前缀匹配原则，非常重要的原则

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

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

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

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

-参考文献

-说在最后：有问题可以找老架构取经

-部分历史案例

1、索引原理：

索引是帮助MySQL高效获取数据的数据结构，注意，是帮助高性能的获取数据

索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。

• 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
• 索引是一种能帮助mysql提高了查询效率的数据结构：索引数据结构。

索引的存储原理大致可以概括为一句话：以空间换时间。

数据库在未添加索引, 进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。

数据库添加了索引之后，通过索引快速找到数据在磁盘上的位置，可以快速地读取数据，而不用从头开始全表扫描。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

2、索引的分类：

主键索引：primary key

• 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

唯一索引：

• 索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。

复合索引：

• 一个索引可以包含多个列，多个列共同构成一个复合索引。

全文索引：

• Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
• 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

空间索引：

• MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引：

• 在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

3、索引的优缺点：

优点：

• 大大提高数据查询速度。
• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
• 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
• 被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
• 如果按照索引列的顺序进行排序，对order 不用语句来说，效率就会提高很多。

缺点：

• 索引会占据磁盘空间。
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
• 维护索引需要消耗数据库资源。

综合索引的优缺点：

• 数据库表中不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!

4、参考的索引设计规范：

每个公司，都有自己的 设计规范，

尼恩这里的梳理的设计规范，可以作为大家参考。当然，如果面试的时候能讲到这个水平，已经很牛掰了。

4.1 索引命名规范

单值索引，建议以 idx_ 为开头，字母全部小写。

例如：alter table t1 add key idx_r1(r1)；

组合索引，建议以 dx_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)));

4.2 尽量选择整型列做索引

索引本身有有序的，尽量选择整型列做索引，

所以，尽量不用uuid，而是使用雪花id，页段id，等整数id去建立索引 。

雪花id，页段id的源码和原理，请参见尼恩的《视频第32章：超高并发、超高可用1000W级 ID组件 架构与实操》

如果避免不了，只有字符串做索引，可以选择对字符类型做 HASH ，再基于 HASH 结果做索引；

主键列数据类型最好也是整型，

避免对不规则的字符串建立主键（由于 INNODB 表即索引，所以应该避免掉。不仅仅 UUID 非有序，而是因为单个 UUID 太大）

4.3 优先建立唯一性索引

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

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

如果使用姓名的话，可能存在同名现象，从而降低查询速度。

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

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

如果为其建立索引，可以有效地避免排序操作。

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

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

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

4.6 限制索引的数目

索引的数目不是越多越好。

每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

修改表时，对索引的重构和更新很麻烦。

越多的索引，会使更新表变得很浪费时间。

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

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

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

4.9 尽量使用前缀来索引

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

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。

如果只检索字段的前面的若干个字符，这样可以提高检索速度。

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

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

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

4.11 最左前缀匹配原则，非常重要的原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，

比如a1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

注意：=和in可以乱序。

比如a= 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，

mysql的查询优化器会帮你优化成索引可以识别的形式

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

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，

唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，

那可能有人会问，这个比例有什么经验值吗？

使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录

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

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。

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

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

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

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

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

注意：选择索引的最终目的是为了使查询的速度变快。

说在最后：有问题可以找小北取经

mysql相关的面试题，是非常常见的面试题。

以上的内容，如果大家能对答如流，如数家珍，基本上 面试官会被你 震惊到、吸引到。

最终，让面试官爱到 “不能自已、口水直流”。offer， 也就来了。

如有收获，请点击底部的"在看"和"赞"，谢谢

本文由博客一文多发平台 OpenWrite 发布！