一张表究竟建多少个索引才是适合呢

上周的一天，到公司接了杯水刚刚坐稳，就看到 DBA就在群里@ 某个研发帅哥，说“你们表曾经有10个索引了，怎样这次还要加呢？”

那我就在想：一张表究竟建多少个索引才是适合呢？

要搞懂这个疑问，我们就须要弄清楚以下这几个疑问：

1）经常出现的索引分类有哪些？

2）MySQL 是如何经常使用索引的？

3）一张表最多可以建多少索引？

4）新建索引的规范准则有哪些？

本文我们就一同来开展聊聊这几个疑问~

1、经常出现的索引分类有哪些？

1.1 运行层分类

从运行层面，经常出现分类：

1.2 数据结构层分类

从数据结构层面，分类如下：

2、新建索引的规范准则有哪些？

关于新建索引，通常须要留意以下规范准则：

2.1 最左前缀婚配准则

MySQL 会不时向右婚配直到遇到范畴查问(>、<、between、like)就中止婚配

比如a = 1 and b = 2 and c > 3 and d = 4 假设树立(a,b,c,d)顺序的索引，d是用不到索引的；

假设树立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以恣意调整。

2.2 尽量选用辨别度高的列作为索引

辨别度的公式是：count(distinct col)/count(*)，示意字段不重复的比例，比例越大我们扫描的记载数越少。

惟一键的辨别度是1，而一些 status 形态、性别等 字段或许在大数据面前辨别度就是0。

2.3 索引列不能介入计算

坚持索引列“洁净”，这个要素其实很便捷，b+树中存的都是数据表中的字段值。但是在启动检索时，须要把一切元素都运行函数才干比拟，显然老本太大。

比如 from_unixtime(create_time) = ’2014-05-29’ 就不能经常使用到索引，须要将语句改写成：create_time = unix_timestamp(’2014-05-29’)。

2.4 尽量的裁减索引，不要新建索引

比如表中曾经有a的索引，如今要加(a,b)的索引，那么只须要修正原来的索引即可，而不倡导再独自去建一个b索引。

3、MySQL 是如何经常使用索引的？

索援用于极速查找具备特定列值的行，其目的在于提高查问效率。

与我们查阅图书所用的目录是一个情理：先定位到章，而后定位到该章下的一个小节，而后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。

实质都是：经过不时地增加想要失掉数据的范畴来挑选出最终想要的结果，同时把随机的事情变成顺序的事情。

也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂得多，由于不只面临着等值查问，还有范畴查问(>、<、between、in)、含糊查问(like)、并集查问(or)等等。数据库应该选用怎样样的方式来应答一切的疑问呢？

大少数 MySQL 索引（PRIMARY KEY、UNIQUEINDEX 和FULLTEXT）都存储在 B树 中。

另外：

空间数据类型经常使用 R 树；

MEMORYtable 还支持哈希索引；

InnoDB 对 FULLTEXT 索引经常使用倒陈列表。

在 MySQL 中，经常使用索引启动以下操作：

3.1 = 和 in 可以乱序

比如a = 1 and b = 2 and c = 3 树立(a,b,c)索引可以恣意顺序，MySQL 的查问优化器会帮你优化成索引可以识别的方式。

3.2 and 与 or

联结索引：(d,a,b,c)

1）查问条件：

MySQL 会依照联结索引，从左到右的顺序找一个辨别度高的索引字段(这样便可以极速锁定很小的范畴)，减速查问，即依照d—>a->b->c的顺序

2）查问条件：

MySQL 会依照条件的顺序，从左到右依次判别，即a->b->c->d

4、一张表最多可以建多少个索引？

4.1 通常过去说

MySQL 的存储引擎（如 InnoDB、MyISAM 等）自身并没有对一个表能创立的索引数量设置一个固定数值限度，，而是由MySQL数据库引擎外部的数据结构和算法选择的。

从数据库设计和架构的角度，通常上只需满足以下条件，就可以新增创立索引：

4.2 实践运行状况

但是在实践运行场景中，通常不会有限度地创立索引。一方面是由于上述提到的功能疑问，过多的索引往往会造成数据降级操作变得极为缓慢，重大影响系统的反常运转。

例如，在一个高并发的电商订单解决系统中，假设对订单表的少量字段都创立了索引，那么每一次性订单的拔出、修正或删除操作，都要破费少量期间来降级相关索引，造成订单解决效率大幅降低。

另一方面，不同的 MySQL 版本以及不同的存储引擎在实践体现上也会有差异。

例如，关于一个领有百万条记载的用户消息表，在 MySQL 5.7 中依照用户姓氏启动含糊查问，或许须要遍历相当一局部数据，查问速度相对较慢，假定平均每次查问须要 20 秒。而 MySQL 8.0 引入了新的索引算法和数据结构优化，新的索引算法对这种含糊查问的支持更好，雷同的查问或许只须要 5 秒左右，功能优化显著。

总结

索引是运行程序设计和开发的一个关键方面。若索引太多，运行程序的功能或许会遭到影响。而索引太少，对查问功能又会发生影响，要找到一个平衡点，这对运行程序的功能至关关键。

MySQL 表能创立的索引数量没有一个确切的、通用的相对下限，而是要综合思考多方面要素，在满足功能要求和存储空间准许的条件下正当创立索引。

其实做了这么长期间的语句优化后才发现，任何数据库层面的优化都抵不上运行系统的优化，雷同是MySQL，可以用来撑持Google/FaceBook/Taobao运行，但或许连你的团体网站都撑不住。套用最近比拟盛行的话：“查问容易，优化不易，且写且珍惜！”