mysql调优

dddaidai123

已于 2025-06-23 09:22:41 修改

阅读量3.7k

点赞数 10

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2025-05-08 18:40:24 首次发布

本文链接：https://blog.csdn.net/hsnsnusjs/article/details/147800105

Mysql调优

一、慢sql分析

1.1 如何查看慢sql

开启慢SQL日志记录并设置慢sql记录时间，默认是10秒

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询时间阈值，这里设置为 2 秒
SET GLOBAL long_query_time = 2;
-- 开启记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

查看慢sql内容

# 查看执行时间最长的前 10 条慢 SQL
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

其中，-s t 表示按照执行时间排序，-t 10 表示只显示前 10 条慢 SQL。

1.2 explain

关键参数：
id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra

Type

system: 当使用MyIsAm等引擎且表中只有一条数据时候
const: 使用主键进行精确匹配
eq_ref: 连接查询的时候，驱动表通过主键匹配被驱动表
ref: 普通索引进行精确匹配
fulltext:全文索引
ref_or_null：普通索引进行精确匹配但是可能匹配到null
range: 对普通索引的范围查找
index: 对普通索引整体扫描
all：全表扫描

二、外/内连接索引优化

无论是内连接还是外连接，优化方向都是将被驱动表中的数据加上索引

2.1 外连接

原理：从驱动表中取出一条数据，匹配被驱动表，最后返回驱动表
例如： table A 20条数据， table B 30条数据
A left join B
在没有索引的情况下：匹配20 $×\times$ 30次
（A中每次取出一条数据，去B中匹配，每次都要匹配30次）
在B加上索引的情况下：匹配20 $×\times$ 1 次

2.2 内连接

原理：和外连接类似，但是此时是由mysql自动指定被驱动表
所以优化方向是在数据大的表中加上索引
例如：
如果在A中加上索引（此时mysql会将A当作被驱动表），匹配次数为 30 $×\times$ 1
如果在B中加上索引（此时mysql会将B当作被驱动表），匹配次数为20 $×\times$ 1

三、子查询优化和排序优化

3.1 子查询优化

子查询瓶颈：

需要建立临时表，且还需删除临时表
临时表没有索引
优化：将子查询拆解成多个查询，使用join等结合

3.2 排序优化

对于联合索引，排序顺序要相同（要么都DESC要么都ASC）

无过滤不索引（limit）：因为回表，导致全表扫描（根据数据量大小决定，一般来说数据量大时会走全表，数据量小时可能会走索引）

当范围查询和order by同时出现的时候，看范围查询过滤的行数是否很多，如果很多且排序很多不多，索引加在过滤字段上，如果过滤掉的行数不多且排序行数很多，索引加在排序字段上，最好是两个都加

3.3 filesort算法

双路排序（慢）
先读取order by后字段的列，然后对该列排序，排完序之后然后回表查询其他数据（两次io）
单路排序
直接读取所有数据，然后排序（一次io）
超过sort_buffer_size会变成外部排序（只在缓冲区中排序一部分数据，然后再将排序好的数据存入临时文件，重复这个过程，最后会在临时文件中得到多个有序的块，最后使用归并排序合并）

优化：

提高sort_buffer_size 大小
max_length_for_sort_data控制使用单路还是双路排序
避免select *

order by尽量使用index自然排序，如果避免不了使用file sort，考虑file sort优化

四、group by和分页优化

group by和order by类似
分页优化主要是深度分页问题

深度分页

深度分页指的是查询偏移量（OFFSET）非常大的分页情况。例如，查询语句SELECT * FROM table_name LIMIT 100000, 10;，这里偏移量是 100000，意味着要跳过前面 100000 条记录，只取后续的 10 条记录。
随着偏移量的增大，数据库查询性能会显著下降。这是因为数据库在执行LIMIT和OFFSET查询时，需要先扫描偏移量指定数量的记录，然后再从这些记录之后开始取所需数量的记录。当偏移量很大时，扫描大量记录会消耗大量的 CPU、内存和磁盘 I/O 资源，导致查询变慢。
优化：