数据库查询性能优化方法2：建立最好的索引

查询性能优化，有3个方法。

• 如何设计最优的库表结构

• 如何建立最好的索引

• 合理的设计查询

库表结构优化、索引优化、查询优化需要齐头并进，一个不落。

在尝试编写快速的查询之前，需要清楚一点，真正重要是响应时间。

提升性能的两个方法：空间换时间，随机转顺序

方法1：设计最优的库表结构，参考：数据库查询性能优化方法1：设计最优的库表结构

方法3：合理设计查询语句，参考：数据库查询性能优化方法3：合理设计查询语句

本篇为方法2：建立最好的索引

建立最好的索引

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

查找行是索引的常规功能，不再赘述。

下面来看使用索引扫描来优化排序。

MySQL 有两种方式可以生成有序的结果：

• 通过order by排序操作
• 按索引顺序扫描，如果EXPLAIN 出来的type列的值为“index”，则说明MySQL 使用了索引扫描来做排序（不要和Extra 列的“Using index，搞混淆了）。

只有当下面3个条件都满足，MySQL 才能够使用索引来对结果做排序，条件还是比较苛刻的

1. 索引的列顺序和ORDER BY子句的顺序完全一致

2. 所有列的排序方向（倒序或正序）都一样时

3. Order by中的字段全部在关联表中的第一张表中

排序本身就是一个成本很高的操作

索引设计规范

1. 限制每张表上的索引数量，建议单张表索引不超过5个

2. 禁止给表中的每一列都建立单独的索引

3. 不使用更新频繁的列做主键，不使用多列主键

4. 不使用UUID、MD5、HASH、字符串，作为主键

5. 主键建议使用自增ID列

6. 常见索引列建议

   • select update delete语句的where从句中的列
   • 包含在order by、group by、distinct中的字段
   • 多表join的关联列

7. 联合索引索引列的顺序

   • 在一个多列B+ Tree 联合索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和DISTINCT 等子句的查询需求。如果不是按照索引最左列开始查找（最左匹配原则），则无法使用索引。

   • 当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE 条件的查找。

     在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。

     然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

   • 在满足选择性的前提下，尽量把字段长度小的列放在联合索引的最左侧（每页容纳更多的索引）

   • 使用最频繁的列放到联合索引的左侧

8. 避免建立冗余索引和重复索引

   重复索引

   primary key(id)，unique index(id)，index(id)

   

   mysql的主键就是一个非空的唯一索引

   冗余索引

   

   比如上图，联合索引中是按照从左到右来使用的，index(a)就是冗余的

   冗余索引增加查询优化器选择的时间

   发生此现象的原因可能是为了使用联合索引，而忘记了将原a列上的索引删除

9. 对于频繁的查询优先考虑使用覆盖索引，避免回表。

   比如查询商品库存这样的sql，我们就要优先考虑是否使用覆盖索引，索引中包括全部字段

10. 尽量避免使用外键，而在表与表之间的关联键上建立索引

    保证数据的参照完整性，建议在业务端实现

11. 前缀索引和索引选择性

    有时候需要索引很长的字符列，这会让索引变得大且慢

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

    使前缀的选择性接近于完整列的选择性

12. 如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。索引NULL列需要额外的空间来保存，所以要占用更多的空间（索引空间占用越少越好，这样一页可以放更多的索引）

    参考：MySQL中NULL对索引的影响

    null列是可以用到索引的，不管是单列索引还是联合索引，但仅限于is null，is not null是不走索引的

13. 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

14. 使用索引时不能跳过索引中的列

15. 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

16. category这个字段有没有必要创建索引呢？这是mysql中一个比较讨厌的问题，因为其没有oracle中的位图索引bitmap

    比如网易云音乐里面的音乐类别，如摇滚、民谣啥的，如歌曲有100万首，类别不超过50个，所以类别不会进行索引

    如果说你要对类别进行查询，那应该怎么办呢？

    方法1：就是用list分区

    方法2：分多张表

    但这样做，开发效率就会变低了

    某一个类别，比如说流行，其歌曲会非常非常多，而如独立，其歌曲会比较少

    以上所说的都是一首歌曲仅仅只有一个category。如果一首歌曲有多个category，处理方法就不同了，类似多对多的关系，与用户和标签的多对多关系类似，这种情况使用关系型数据库就不太合适了，使用es的倒排索引机制更加合适。