Mysql索引优化

Mysql索引优化

SQL性能分析方法

执行频次

方式：

1

show status like 'Com_%';

慢查询日志

方式：

1
2
3
4
5
6
7
8

-- 开启慢查询日志
set global slow_query_log = 1;

-- 查看慢查询日志配置
show variables like '%slow_query_log%';

-- 设置慢查询日志的阈值为3秒
set global long_query_time = 3;

profile分析

方式：

1
2
3
4
5

-- 查看是否开启profile
show variables like 'profiling';

-- 开启profile
set profiling = 1;

explain(执行计划)

方式：

1
2

-- 查看执行计划
explain select * from table_name where column_name = 'value';

EXPLAIN 输出详解

1. id

说明：查询中每个 SELECT 子句的唯一标识符。 详细解读：

• id 相同：如果多行的 id 相同，表示它们属于同一个查询块，执行顺序 从上到下。
• id 不同：如果 id 不同，id 值 越大，优先级越高，越先被执行。这通常出现在子查询、派生表或 UNION 中。
• id 为 NULL：这一行是 UNION 操作的结果集，它合并了其他 id 的查询结果。

示例：

1
2

EXPLAIN SELECT * FROM employees e
WHERE e.emp_no IN (SELECT emp_no FROM salaries WHERE salary > 90000);

在这个查询中，子查询 SELECT emp_no FROM salaries ... 的 id 会是 2，而外部主查询的 id 会是 1。MySQL 会先执行 id=2 的子查询。

2. select_type

说明：SELECT 查询的类型。 常见值及解读：

• SIMPLE：最常见的类型，表示查询不包含子查询或 UNION。
• PRIMARY：查询中若包含任何复杂的子部分（如子查询、UNION），最外层的 SELECT 被标记为 PRIMARY。
• SUBQUERY：在 SELECT 列表或 WHERE 子句中出现的子查询（非 FROM 子句中）。
• DERIVED：在 FROM 子句中出现的子查询。MySQL 会将该子查询的结果放到一个临时表（派生表）中，该行显示的就是对这个临时表的操作。
• UNION：在 UNION 操作中，第二个及之后的 SELECT 语句。
• UNION RESULT：UNION 操作的结果集。

3. table

说明：这一行数据是关于哪个表的。 详细解读：

• 直接显示表名。
• 可能是别名（如果查询中使用了别名）。
• 可能是 <derivedN>：表示这一行是针对 id 为 N 的派生表进行的操作。
• 可能是 <unionM,N>：表示这一行是 id 为 M 和 N 的 UNION 结果集。

4. partitions

说明：查询匹配到的分区。 详细解读：

• 这个字段只有在表是分区表时才有意义。
• 它显示了查询将要访问的分区。如果为 NULL，表示该表不是分区表，或者查询没有触发任何分区裁剪（Partition Pruning）优化。
• 如果能明确命中少数几个分区，说明分区设计是有效的。

5. type (⭐ 极其重要 ⭐)

说明：表的连接类型或数据访问类型，这是衡量查询性能好坏的 最关键指标。 性能从好到坏的顺序： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > range > index > ALL

• system：表只有一行数据（系统表），是 const 类型的特例。几乎不会出现。
• const：通过主键或唯一索引，最多只匹配一行数据。查询速度极快，因为数据库把它当做一个常量来处理。
  • ... WHERE primary_key = 123
• eq_ref：在 JOIN 查询中，前一张表的每一行，都能在后一张表中通过主键或唯一索引精确匹配到 一行 数据。这是 JOIN 的最佳类型。
  • ... FROM t1 JOIN t2 ON t1.pk = t2.pk
• ref：非唯一性索引扫描，或者唯一索引的最左前缀扫描。对于每个匹配的键值，可能会返回 多行 数据。
  • ... WHERE non_unique_index_col = 'some_value'
• range：使用索引进行范围扫描。常见于 BETWEEN, >, <, IN() 等操作。比全索引扫描好，因为它只扫描索引的一部分。
  • ... WHERE id BETWEEN 100 AND 200
• index：全索引扫描（Full Index Scan）。它会遍历整个索引树。虽然比 ALL 快（因为索引通常比表数据小），但仍然是性能不佳的表现。通常发生在查询的列全部在索引中（覆盖索引），但没有 WHERE 条件来缩小范围时。
• ALL：全表扫描（Full Table Scan）。MySQL 将遍历整张表来寻找匹配的行。这是 最坏 的情况，是性能优化的首要目标。

优化建议：你的目标应该是让 type 至少达到 range 级别，最好是 ref 或 eq_ref。如果看到 ALL，通常意味着需要添加索引。

6. possible_keys

说明：MySQL 认为 可能 在这个查询中使用的索引列表。 详细解读：

• 这个字段是“理论上”可用的索引。
• 如果这里是 NULL，表示没有找到任何合适的索引可供使用。这时你需要检查 WHERE 子句涉及的字段是否建了索引。

7. key

说明：MySQL 实际决定 使用的索引。 详细解读：

• 这是 possible_keys 中的一个，也可能是 NULL。
• 如果 key 是 NULL 但 possible_keys 有值，说明 MySQL 评估后认为全表扫描比使用索引更快（例如，表数据量很小）。
• 强制使用索引：可以使用 FORCE INDEX(index_name) 来强制 MySQL 使用某个索引，但这通常不推荐，因为优化器通常更准。

8. key_len

说明：实际使用的索引的长度（字节数）。 详细解读：

• 这个值可以帮助你判断 MySQL 使用了联合索引中的哪些部分（最左前缀原则）。
• key_len 越短通常越好。
• 计算规则（大致）：
  • VARCHAR(N): N * 字符集字节数 + 2 (变长字段有2字节记录长度)
  • CHAR(N): N * 字符集字节数
  • INT: 4 字节
  • BIGINT: 8 字节
  • 如果字段允许为 NULL，则需要额外 1 个字节。
• 通过 key_len 的值，你可以精确推断出联合索引 (a, b, c) 中，查询用到了 (a) 还是 (a, b) 还是 (a, b, c)。

9. ref

说明：显示将哪些列或常量与 key 列指定的索引进行比较，以从表中选择行。 详细解读：

• const：表示用一个常量值与索引进行比较，如 WHERE id = 10。
• db_name.table_name.col_name：表示用到了一个列进行关联，常见于 JOIN 操作中，如 t1.id = t2.ref_id，在 t2 表的执行计划行中，ref 字段会显示 db_name.t1.id。

10. rows

说明：MySQL 估算的为了找到所需行而需要读取的行数。 详细解读：

• 这是一个 估算值，不是精确值。
• 这个数字越小越好。如果 rows 值很大，而实际返回的行数很少，说明查询效率低下。

11. filtered

说明：一个百分比，表示经过 WHERE 条件过滤后，剩下行数的百分比（相对于 rows 列）。 详细解读：

• rows * (filtered / 100) = 估算出的将与下一张表连接的行数。
• 这个值越高越好。如果 filtered 很低（例如 10%），说明 rows 中读取的大部分行都被 WHERE 条件丢弃了，这可能意味着索引的区分度不高。

12. Extra (⭐ 非常重要 ⭐)

说明：包含不适合在其他列中显示的额外重要信息。 常见关键值解读：

• Using index：（好） 这是一个非常好的信号！表示查询的列信息可以直接从索引中获取，而无需回表（访问实际的数据行）。这被称为“覆盖索引”（Covering Index）。这是性能优化的一个重要目标。
• Using where：（正常） 表示 MySQL 服务器层在从存储引擎获取数据后，需要进行额外的 WHERE 条件过滤。这是很常见的。
• Using index condition：（好） 索引下推（Index Condition Pushdown, ICP）优化。在访问索引时，服务器把 WHERE 条件的一部分下推到存储引擎层去判断，这样可以减少存储引擎返回给服务器层的数据量。
• Using temporary：（坏） 表示 MySQL 需要创建一个临时表来存储中间结果，以完成查询。常见于 GROUP BY 和 ORDER BY 的列不同，或 UNION 操作。创建临时表开销很大，是主要的性能瓶颈之一。应尽量避免。
• Using filesort：（坏） 表示 MySQL 无法利用索引来完成排序，必须在内存或磁盘上进行额外的排序操作（文件排序）。这也是一个主要的性能瓶瓶颈。通常可以通过创建合适的索引或修改 ORDER BY 子句来避免。
• Using join buffer (Block Nested Loop)：（坏） 在 JOIN 操作中，被驱动表（后一个表）没有使用索引，MySQL 不得不使用一个连接缓冲区来批量处理。这意味着关联字段上缺少索引。

如何解读和优化

1. 定位问题查询：首先找到 type 为 ALL 或 index 的查询，这是全表扫描或全索引扫描，是首要优化对象。
2. 检查索引使用：查看 key 字段，确认是否使用了正确的索引。如果 key 为 NULL，检查 possible_keys 和 WHERE 子句。
3. 分析 Extra 信息：密切关注 Extra 列中是否出现 Using temporary 和 Using filesort。如果出现，需要分析 GROUP BY, ORDER BY, DISTINCT 等子句，并尝试通过创建合适的索引来消除它们。
4. 追求覆盖索引：如果查询的列都在一个索引里，Extra 会显示 Using index，这是最高效的查询方式之一。
5. 减少扫描行数：关注 rows 和 filtered 字段，确保 MySQL 估算的扫描行数尽可能少。

Mysql索引使用

索引分类

• 主键索引
• 唯一索引
• 普通索引
• 全文索引（少用）
• 联合索引 (多个字段上创建的索引)
• 二级索引 (非聚簇索引)
• 聚簇索引 (一般主键索引)
• 前缀索引 (对字段的前缀创建索引) 语法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建全文索引（MySQL 5.6+ 支持）
CREATE FULLTEXT INDEX index_name ON table_name (column_name);

-- 创建联合索引
CREATE INDEX index_name ON table_name (column1, column2, ...);

-- 创建前缀索引（MySQL 5.7+ 支持）
CREATE INDEX index_name ON table_name (column_name(prefix_length));

注意项

• 索引字段的顺序：在联合索引中，字段的顺序会影响索引的效率。一般将区分度高的字段放在前面。
• 索引字段的长度：前缀索引对字段的前几个字符创建索引，适用于文本字段的索引。
• 索引的维护成本：索引会占用额外的磁盘空间，并且在插入、更新、删除操作时需要维护索引。因此，需要根据实际情况评估是否需要创建索引。
• 使用索引不一定能提高查询效率：在某些情况下，即使创建了索引，MySQL 优化器也可能选择不使用索引，例如当查询条件中包含函数操作、OR 条件等。
• 值的区分度：索引的区分度越高，索引的效率就越高。因此，在创建索引时，应该选择区分度高的字段。
• 不应该选择区分度低的字段创建索引，例如性别、状态等字段。
• 不应该选择经常变化的字段创建索引，例如订单状态等字段。
• 不应该选择小表创建索引，例如只有几条记录的表（数据量小的表查询时全表扫描比索引效率高）。
• 索引覆盖：当查询的列都在索引中时，可以避免回表查询，提高查询效率。
• 索引合并：当查询条件中包含多个索引时，MySQL 可以选择使用索引合并来提高查询效率。
• 如果索引列不能为NULL，那么在创建字段时，应该将字段设置为NOT NULL，这样可以避免索引失效。

索引优化

• 尽可能使用联合索引：当查询条件中包含多个字段时，应该考虑创建联合索引。
• 不能创建过多的索引：一般每个表的索引数量不能超过5个，否则会影响查询效率。
• 最左前缀原则：在联合索引中，查询条件中必须包含索引的最左边的字段，否则索引不会被使用。