十六、索引设计

　　一个好的索引设计，就是尽量让所有查询都以索引查询的形式进行，以提高查询效率。在之前提到过，使用主键查询会直接走聚簇索引，使用复合索引字段查询可能会回表，并且在使用复合索引时尽量遵循 “最左侧原则” ，这样才能大大提高索引的使用率，提高查询效率。

索引排序

　　前面说的都是在 where 条件中使用索引作筛选条件，而在 order by 排序子句中也可以使用索引来提高排序的效率。

　　在使用索引排序之前，通常排序可能使用这样的 SQL 语句：“ select * from user where name = 'zhangsan' order by name, age, type ”，这个语句首先会使用 where 条件筛选出一些数据，然后加载到内存在内存中排序，如果数据比较大内存都放不下，这时会放在一个临时磁盘文件中，然后在磁盘中排序（filesort），这个速度就会很慢很慢了。所以这时通常会添加 limit 子句，分批次查询，在内存中排序就会快一点。

　　如果建立了复合索引 “ index (filed1, filed2, filed3) ” 那么在使用 order by 排序时直接使用索引字段去排序 “ order by filed1, filed2, filed3 ”，因为这个复合索引树本身就是按照这三个字段排序的，所以在运行 SQL 语句时 “ select * from user where name = 'zhangsan' order by name, age, type ” ，首先会通过 where 子句在复合索引树筛选出一段数据，然后由于排序字段跟索引字段一样，所以可以直接取出这部分数据的主键去聚簇索引查询完整数据即可。所以说实际上这个 order by 子句的排序字段是索引字段时，实际上索引树就已经提前排序了，无需再次排序，只需要根据索引取出数据即可。

　　但是使用索引排序也是有限制的。因为复合索引中的字段值默认是从大到小排序的（ASC），所以在使用时要么不加排序关键字 “ order by filed1, filed2, filed3 ”，那么就是从大到小排序；

　　要么在所有字段后都添加排序关键字 “ order by filed1 desc, filed2 desc, filed3 desc ”，那么就是从小到大排序（DESC）。

　　而且不能一个字段 ASC，而另一个字段 DESC，这样也不能使用索引排序。

　　在排序字段中不能使用函数、计算式、其它非索引字段，否则还是不会使用索引排序。

　　再就是，如果排序字段后面没有跟 limit 子句限制查询行数，即使使用了索引字段也不会排序，因为查询所有字段就意味着查询了全部复合索引树后还要再次查询全部的聚簇索引，那这样还不如直接全表扫描，所以即使使用索引排序还是需要限制查询行数，避免全表扫描。

　　总结一下就是，在使用索引排序时，order by 子句的排序字段必须严格与索引字段一致，而排序规则要么是 ASC 或者 DESC，不能混合排序。

索引分组

　　使用分组最常见的就是用来统计一些分组的行数，比如 “ select count(*) from user group by name ”，这个语句会把数据都提取出来放在一个临时文件或加载到内存，然后对每个组执行聚合函数进行统计，这个过程效率其实是很低的。

　　而索引树对索引字段排序后实际上就已经分组了，字段值相同的索引排在一起，这不就是分组了吗？所以在 group by 子句中使用复合索引的最左侧索引字段进行分组，充分利用索引，以提高分组统计的效率。

覆盖索引

　　所谓的覆盖索引就是在使用复合索引查询时，查询的字段就是复合索引中的字段，那么这时就会直接把索引字段的值直接返回，而不会回表再去聚簇索引查询。所以一般查询时尽量不要写 “ select * from table ” 这样的语句，这会让查询必然会回表，而查询中回表频率过高会导致查询直接转入全表扫描而不会走索引查询了。即使需要回表查询其它字段也要添加查询条件限制，尽量让查询走索引。

索引设计

　　可以在系统开发完成后，对数据库设计索引，把索引的设计当成系统开发完成后查询的优化。

　　1. 尽可能的让索引覆盖到每一个查询语句。
　　2. 选择一些基数比较大的字段建立索引，像 state 这种只有几个值得字段建立索引意义就不大了，比如车牌号这种定长且基数大的字段就非常适合建立索引了。这样就能充分发挥 B+ 树的查询优势。但也不是绝对的，像多个小基数字段建立的复合索引也是可以的，还是需要建立在实际应用场景下分析。
　　3. 选择字段类型小的列建立索引，比如 tinyint （-128 ~ 127）类型的字段。像 name 这种 varchar(255) 类型的字段，可以用 “ index index_name(name(20), age) ” name 的前几个字符建立前缀索引，但是这样一来就不能在 order by、group by 语句中使用了。

　　然后在使用索引时，也需要注意一些事项。

　　1. 索引的数量不宜太多，过多的索引在频繁增删改的场景下，会导致大量的索引频繁的页分裂、更新、重排，会消耗大量资源且耗时，总之，修改索引是极为繁琐的事，尽量避免索引的频繁变更。
　　2. 再就是建表使用的主键不要使用 UUID 之类的无序ID，即使要自定义主键也要使用有自增长的有序的ID，因为无序ID 会导致聚簇索引频繁页分裂。

设计原则：

　　1. 选择唯一性索引：唯一性索引的值是唯一的，查询速度更快。
　　2. 为常用查询字段建立索引
　　3. 限制索引数量：过多的索引会占用更多的空间，增删改速度会变慢。
　　4. 使用类型值小的字段建立索引
　　5. 经常排序、分组、联合查询的字段建立索引
　　6. 尽量使用前缀索引
　　7. 删除不再使用或很少使用的索引
　　8. 小表不建立索引，比如只有千百条数据
　　9. 索引的创建必须考虑数据的操作的方式：经常查询很少修改的表可以适当添加索引；经常变动修改的表需要谨慎建立索引。
　　10. 索引会占用大量磁盘空间，减少不必要索引的创建

经验之谈

　　在很多场景下 where 查询条件和 order by 子句、group by 子句可能都是无法全都用上索引的，只能选其一使用索引，所以一般在 where 子句使用索引，因为 where 子句使用索引筛选出数据后加载到内存，在内存中排序和分组性能要高一些。特别在数据量比较大的场景下，利用索引快速筛选部分数据，这个性能就很高了。

　　可以总结一下索引设计原则的优先级：筛选 > 排序、组合

　　在一些特定场景下，多个基数小的字段也可以作为复合索引放在索引最左侧，比如“省份+城市+性别“，在一些场景下可能会经常使用这几个字段作为筛选条件，那么直接用这几个字段作为索引也是可以的。虽说这几个字段基数比较低，但是使用率比较高。

　　可以总结一下索引设计原则的优先级：高使用率 > 低基数

　　在多个基数小字段组成的复合索引，在一些场景下可以使用 in 子句来作为筛选条件，比如现在有个需求，需要筛选某省份下的某个城市12到18岁的不限性别的用户，那么怎么让这几个字段都使用索引？可以这样设计 index_name index(province, city, sex, age) 然后筛选时：where province = '广东' and city = '深圳' and sex in ('female', 'male') and age >= 12 and age <= 18。这样就做到不限性别，并且所有查询字段都使用了索引。但是需要注意的是，范围查询的字段一定是在筛选条件的末尾，因为筛选条件中有字段用索引作了范围查询，那么其后的字段都不能用索引了。

　　可以总结一下索引设计原则的优先级：等值查询 > 范围查询；in 子句也可以使用索引

　　在多个字段使用范围查询时，可以把一些范围查询转为等值查询，只留下最后一个字段在查询末尾使用范围查询。比如需要查询年龄12岁以下的7天内在线的用户，这个7天内在线的用户字段如果使用时间日期类型，就一定会使用范围比值查询，这种情况下查询条件可能就是这样的：where online > xx and age > 12 ，而两个字段只有一个能用上索引。现在变换一下思路，把7天内在线的用户字段设计为一个标志位，比如 where online = 1 and age > 12 ，此时两个字段就都能使用索引了，这个 online 字段通过系统去维护，比如用户登录时修改这个字段为 1 并且发送 MQ 延时消息或 redis 缓存保存这个登录消息，每次登录都更新这个消息。消息设置延迟为 7 天，如果用户在7天后未登录，再消费这个消息把 online 字段修改为 0，表示用户 7 天内未登录。

　　这里可以总结一下：多个范围查询字段可以通过转化为等值查询来使用索引，这只是在一些业务场景下适用。

优化案例

案例一

　　由于 MySQL 选择了不合适的扫描方式导致的慢查询。

　　排查经过：首先数据库监控平台发现大量的慢 SQL，导致数据库连接满载，无法处理新的请求。排查发现就是类似以下的 SQL ：

SELECT
    * 
FROM products 
WHERE
    category = 'xx' AND 
    sub_category = 'xx' 
ORDER BY id DESC 
LIMIT xx,xx

　　这个语句就是根据种类筛选数据，然后根据 id 排序，取出指定行数的数据。这本是一个很普通的 SQL 语句，并且一开始并没有什么问题，根据二级索引查询也很快。但就是这么一条 SQL 导致数据库差点崩溃。这条语句的查询极为频繁，但是需要扫描的行很大，可是由于加了二级索引，查询效率应该是没啥问题的。

　　然后就查看这条语句的执行计划，发现 possible_keys 的确存在 category 和 sub_category 组成的复合索引，但是 key 用的却是 primary ？并且 extra 描述为 Using where，怎么会这样？原来 MySQL 认为使用二级索引扫描了大段数据然后再去聚簇索引回表查询再使用文件排序的查询成本大于直接扫描主键索引，所以就在主键索引上对每一条数据做 where 条件判断，然后取出 limit 指定行数。一般情况下数据量不大也没啥问题，可是如果数据量极大，上亿的数据量，然后 where 条件始终无法查询到有效的数据，就会导致全表扫描，从而转入慢查询。

　　解决方案：改写 SQL 语句，使用 force index 语句，强制使用自定义的二级索引。这样，哪怕 MySQL 使用了错误的执行计划，也可以使用 force index 优化执行计划。

　　问题根源：根据聚簇索引扫描，在无法找到指定数据的情况下会导致全表扫描，数据量极大时会转为慢查询。

案例二

　　百万数据深分页的问题。优化之前的 SQL 语句为：

SELECT
    * 
FROM TABLE 
WHERE
    product_id = 'xx' AND 
    is_good = '1' 
ORDER BY id DESC 
LIMIT 100000,20

　　这个 SQL 语句就是根据 product_id 和 is_good 筛选出数据，然后根据 id 排序，取出指定数量的记录。其中 product_id 是一个索引的字段，而 is_good 不是索引字段。那么查询时会从二级索引中找到所有 product_id = 'xx' 的索引然后回表比对 is_good = '1' ，这时就会有一个问题，索引找到的数据量很大，并且全部回表查询，就会严重影响性能。

　　解决方案：改写 SQL 语句

SELECT
    * 
FROM
    TABLE AS t1,
    ( 
        SELECT id 
        FROM TABLE AS t2 
        WHERE 
            product_id = 'xx' AND 
            is_good = '1' 
        ORDER BY id DESC 
        LIMIT 100000, 20 
    ) AS t2 
WHERE
    t1.id = t2.id

　　改写后的 SQL 语句将完全改变执行计划，首先子查询在聚簇索引中根据 where 条件找出匹配的记录，然后把主键 id 取存放在临时表，由于 id 本身就是有序的，所以不需要再次排序，然后临时表全表扫描，去主表取出完整的数据就可以了。

　　问题根源：使用二级索引扫描会导致大量回表查询，反而会影响性能，不妨直接在主表扫描到指定的数据，在临时表全表 join 少量的数据更合适。

总结设计

　　索引的设计应该与业务需求息息相关，没有完美的索引设计，只有满足需求的索引设计，项目前期设计的索引不可能完美的满足后期需求，应随时根据业务合理取舍。所以索引应该在系统基本完成开发后作为优化项进行设计，这样能尽量根据业务需求去设计合适的索引。

　　索引设计应该对查询最为频繁，业务优先级最高的查询建立索引，对于业务优先级低、查询效率在忍受范围内的可以不用建立索引。

　　索引可以提高查询效率，但是会降低增删改的效率。对于表建立的索引数量并没有一个规定，比如一个查询极为频繁但增删改操作很少的表，对于查询而言就可以适量多建立索引；反之，频繁增删改的表则尽量少建立索引；那增删改查都很频繁的表对索引的建立就需要很谨慎了，数据量大建议分为读写库。