🪴 Obsidian Publish

是否创建索引？

10分钟阅读

创建索引的情况

  1. 字段明确要求具有唯一性
  2. 频繁作为 WHERE 查询条件的字段
  3. 经常 GROUP BY 和 ORDER BY 的字段
  4. 经常 Limit 的字段
  5. UPDATE、DELETE 的 WHERE 条件列
  6. DISTINCT 字段
  7. JOIN 连接操作时，大表建立索引
  8. 联合索引优于单值索引，且联合索引的左侧，存放最频繁访问的字段

不适合创建索引的情况

  1. 数据量小的表
  2. 经常更新的表
  3. 很少使用或不再使用，需要删除索引
  4. 有大量重复数据的列。当数据重复度大，比如高于 10% 的时候，即 count(distinct column_name) / count(*) 要小于 90%。
  5. 无序的值。例如身份证、UUID(在索引比较时需要转为 ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
  6. 不要定义冗余或重复的索引。尽量扩展已有索引，而非建立新索引。

其他注意事项

不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0, 1, true, false 这样语义较为清晰的短值或短字符作为替代。

选择合适的字段创建索引

  • 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

限制每张表上的索引数量

索引并不是越多越好，建议单张表索引不超过 5 个！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

字符串类型的字段使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

  • 使用  SELECT *  进行查询; SELECT *  不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;
  • 创建了组合索引，但查询条件未遵守最左匹配原则;
  • 在索引列上进行计算、函数、类型转换等操作;
  • 以 % 开头的 LIKE 查询比如  LIKE '%abc';;
  • 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
  • IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
  • 发生隐式转换;
  • ……

推荐阅读这篇文章：美团暑期实习一面：MySQl 索引失效的场景有哪些？。

删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。

MySQL 5.7 可以通过查询  sys  库的  schema_unused_indexes  视图来查询哪些索引从未被使用。

知道如何分析 SQL 语句是否走索引查询

我们可以使用  EXPLAIN  命令来分析 SQL 的  执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN  并不会真的去执行相关的语句，而是通过  查询优化器  对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN  的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

列名含义
idSELECT 查询的序列标识符
select_typeSELECT 关键字对应的查询类型
table用到的表名
partitions匹配的分区，对于未分区的表，值为 NULL
type表的访问方法
possible_keys可能用到的索引
key实际用到的索引
key_len所选索引的长度
ref当使用索引等值查询时，与索引作比较的列或常量
rows预计要读取的行数
filtered按表条件过滤后，留存的记录数的百分比
Extra附加信息

反向链接