结合阿里规范 java开发手册解读mysql索引，怎么创建和使用高性能的索引

一 原理解读部分

这个部分选取java开发手册的mysql某一部分进行解读

1【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

索引部分字符的优点是节约索引空间，从而提高索引效率，但也会降低索引的选择性。选择性指不重复的索引值和数据表的记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间)。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数“应该接近于完整列的”基数“

可以使用 count(distinct left(列名, 索引长度))/count(*)来确定索引的选择性

2【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合
索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

以上都是对最左前缀匹配特性的运用，很多人都知道最左前缀的要求是什么，比如现在表上有一个三个字段的联合索引abc，由于是按a相同后按b顺序排列，b相同后再按c顺序排列的原则。因此abc联合索引能够⽤来查找a、a%、ab、ab%、abc组合查找（%标识模糊匹配），这种根据索引对数据的存储顺序所能命中使⽤索引的规则，由于按最左字段顺序排列称为最左前缀原则。

索引失效的情况：
不从最左列开始查找：直接找bc，⽆法使⽤abc索引。
跳过中间的列查找：找ac，⽆法使⽤abc索引。
中间某个列是范围查找：找ab%c，则由于中间b为模糊匹配，范围查找或者 a=1 and b > 2 and c=4,b范围查找那么只能使⽤ab两列的索引，c列的索引⽆法使⽤。
列使用函数：当查询条件列使用函数时，无法使用索引，因为mysql无法解析，select id form table where id+1 = 5，其实此时很容易知道要查询的sql等价于select id form table where id = 4，要养成习惯将索引列单独放在比较符号的一侧

上面说到b相同在按c来排列，所以，如果b不同，则c就是无序的，而索引之所以为能提高查询速度，就是因为他是有序的，所以任何造成前一个索引列范围匹配的操作做，都是让当前列的索引匹配无效

4【推荐】利用覆盖索引来进行查询操作，避免回表。
5【推荐】利用延迟关联或者子查询优化超多分页场景。
以上两条都是为了避免回表，前者是利用索引覆盖来进行sql有优化，而后者是使用延迟关联子查询来优化sql
详情可参考limit入坑指南一：回表查询

6【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的
标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行.
count(*)是效率最高的统计方式也是最正确的统计方式，很多人误解它会匹配所有行影响效率，其实它是忽略所有行！！！

二 优秀经验部分

以下是一些很好的建议，在阿里是强制的

1. 【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

2. 【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为
   NULL，因此使用 sum()时需注意 NPE 问题。
   正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g))
   FROM table;

3. 【强制】使用 ISNULL()来判断是否为 NULL 值。
   说明：NULL 与任何值的直接比较都为 NULL。 1） NULL<>NULL 的返回结果是 NULL，而不是 false。 2） NULL=NULL 的返回结果是 NULL，而不是 true。 3） NULL<>1 的返回结果是 NULL，而不是 true。

4. 【强制】在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

5. 【强制】不得使用外键与级联，一切外键概念必须在应用层解决

三 多列索引补充

顾名思义，这里是对多列索引的一些补充，以纠正一些网上出现多次的错误

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

这里只关心第一个问题，为每个列创建独立的索引：这种索引策略，一般是由于人们听到一些专家诸如“把WHERE条件里面的列都建上索引”
这样模糊的建议导致的。实际上这个建议是非常错误的。

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”(index merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。直到现在还是有很多人认为一次查询只能使用到一个mysql认为最优的索引，这是极其过时的

但是索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟

1. 当出现服务器对多个索引做相交操作时（通常有多个AND条件)，通常意味着需要个包含所有相关列的多列索引，而不是多个独立的单列索引。

2. 当服务器需要对多个索引做联合操作时（通常有多个OR条件)，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

3. 更重要的是，优化器不会把这些计算到“查询成本”(cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的cpu和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL4.1或者更早时代一样，使用union的方式往往会更好

至于选择合适的顺序创建多列索引

在只有where 条件的情况下，明显选择性更高的列越靠前，sql效率就会越高

在order by的时候，就要非常注意到最左前缀的使用了，注意这里为什么order by一定要走索引——索引本身就是排序的。我们甚至可以利用这个特性使用limit1直接从索引拿到索引列的最小值来代替min()函数

另外在mysql8之前，order by是不支持不同一个方向的排序的，比如一个abc是哪个字段的组合索引，select * from table where a = 1 order by b asc,d desc是不会走索引的