mysql手册17_经验总结

1、如何定位慢查询

• 方案一：使用开源工具比如 Skywalking
• 方案二：开启 MySQL 自带的慢查询日志

在MYSQL的配置文件（/etc/my.cnf）中配置如下信息开启慢查询日志

# 开启慢查询日志
slow_query_log = 1
# 指定慢查询日志文件路径
slow_query_log_file = /var/log/mysql/mysql-slow.log
# 设置慢查询的阈值（单位为秒，默认为10）
long_query_time = 2

2、如何分析慢查询语句

• id: 查询的标识符。如果查询包含子查询，MySQL 会为它们分配不同的 id。
• select_type: 查询的类型（SIMPLE 表示简单 SELECT，不是使用 UNION 或子查询等）。
• table: 输出行所引用的表。
• partitions: 匹配的分区。
• type: 连接类型，如 ALL（全表扫描）、index（索引全扫描）、range（索引范围扫描）等。
• possible_keys: 显示可能应用在这张表上的索引，但不一定被查询实际使用。
• key: 实际使用的索引。如果为 NULL，则没有使用索引。
• key_len: 使用的索引的长度。在某些情况下，不是索引的全部部分都会被使用。
• ref: 显示索引的哪一列或常量被用于查找值。
• rows: MySQL 认为必须检查的用来返回请求数据的行数。
• filtered: 表示返回结果的行占开始找到符合表条件的行的百分比。
• Extra: 包含不适合在其他列中显示但对执行计划非常重要的额外信息。

3、如何使用 EXPLAIN 优化查询

• 查看是否有索引被使用：确保 key 列不是 NULL，表示查询使用了索引。
• 注意 type 列：尽量避免 ALL（全表扫描），努力优化到 range、ref、const 等更高效的类型。
• 检查 rows 列：这个值越小越好，表示 MySQL 需要的扫描行数越少。
• 分析 Extra 列：查找是否有 Using filesort（表示 MySQL 需要对结果进行排序，可能意味着需要添加索引）或 Using temporary（表示 MySQL 需要使用临时表来存储中间结果，可能是索引不足或查询可以优化）。

4、什么是聚簇索引和二级索引（非聚簇索引）

聚簇索引：索引的叶子节点直接包含数据行，聚簇索引有且仅有一个。

如果存在主键，主键索引就是聚簇索引。
如果不存在主键，将使用第一个唯一索引作为聚簇索引。
如果既没有主键索引也没有唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚簇索引。

二级索引：索引的叶子节点关联对应的主键，所以一般需要回表操作，二级索引可以存在多个。

5、超大分页处理

在数据量较大时进行 limit 分页查询，越靠后的数据分页查询效率会越低。
可通过 覆盖索引 + 子查询 进行优化，先查询出需要分页的ID范围，然后再根据这个范围去查询实际的数据

select * from user t1,
(select id from user order by id limit 9000000,10) t2
where t1.id = t2.id;