创建索引的几大原则

这篇文章将为大家介绍索引建立所需要遵循的

1 列的离散型

离散型的计算公式：count(distinct column_name):count(*)，就是用去重后的列值个数比个数。值在 (0,1] 范围内。离散型越高，选择型越好。

如下表中各个字段，明显能看出Id的选择性比gender更高。

为什么说离散型越高，选择型越好？
因为离散度越高，通过索引最终确定的范围越小，最终扫面的行数也就越少。

2 最左匹配原则

对于索引中的关键字进行对比的时候，一定是从左往右以此对比，且不可跳过。之前讲解的id都为int型数据，如果id为字符串的时候，如下图：

当进行匹配的时候，会把字符串转换成ascll码，如abc变成97 98 99，然后从左往右一个字符一个字符进行对比。所以在sql查询中使用like %a 时候索引会失效，因为%表示全匹配，如果已经全匹配就不需要索引，还不如直接全表扫描。

3 最少空间原则

前面已经说过，当关键字占用的空间越小，则每个节点保存的关键字个数就越多，每次加载进内存的关键字个数就越多，检索效率就越高。创建索引的关键字要尽可能占用空间小。

联合索引

单列索引：节点中的关键字[name]
联合索引：节点中的关键字[name, age]

可以把单列索引看成特殊的联合索引，联合索引的比较也是根据最左匹配原则。

联合索引列的选择原则

• 经常用的列优先（最左匹配原则）
• 离散度高的列优先（离散度高原则）
• 宽度小的列优先（最少空间原则）

覆盖索引

如果查询的列，通过索引项的信息可直接返回，则该索引称之为查询SQL的覆盖索引。覆盖索引可以提高查询的效率。

如上图，如果通过name进行数据检索：
select * from users where name = ?
需要需要在name索引中找到name对应的Id，然后通过获取的Id在主键索引中查到对应的行。整个过程需要扫描两次索引，一次name，一次id。

如果我们查询只想查询id的值，就可以改写SQL为：
select id from users where name = ?
因为只需要id的值，通过name查询的时候，扫描完name索引，我们就能够获得id的值了，所以就不需要再去扫面id索引，就会直接返回。

当然，如果你同时需要获取age的值：
select id，age from users where name = ?
这样就无法使用到覆盖索引了。

知道了覆盖索引，就知道了为什么sql中要求尽量不要使用select *，要写明具体要查询的字段。其中一个原因就是在使用到覆盖索引的情况下，不需要进入到数据区，数据就能直接返回，提升了查询效率。在用不到覆盖索引的情况下，也尽可能的不要使用select *，如果行数据量特别多的情况下，可以减少数据的网络传输量。当然，这都视具体情况而定，通过select返回所有的字段，通用性会更强，一切有利必有弊。

总结

• 索引列的数据长度满足业务的情况下能少则少。
• 表中的索引并不是越多越好，冗余或者无用索引会占用磁盘空间并且会影响增删改的效率。
• Where 条件中，like 9%， like %9%， like%9，三种方式都用不到索引。后两种方式对于索引是无效的。第一种9%是不确定的，决定于列的离散型，结论上讲可以用到，如果发现离散情况特别差的情况下，查询优化器觉得走索引查询性能更差，还不如全表扫描。
• Where条件中IN可以使用索引， NOT IN 无法使用索引。
• 多用指定查询，只返回自己想要的列，少用select *。
• 查询条件中使用函数，索引将会失效，这和列的离散性有关，一旦使用到函数，函数具有不确定性。
• 联合索引中，如果不是按照索引最左列开始查找，无法使用索引。
• 对联合索引精确匹配最左前列并范围匹配另一列，可以使用到索引。
• 联合索引中，如果查询有某个列的范围查询，其右边所有的列都无法使用索引。