MySQL-性能优化

AE86LW
150 阅读12分钟

MySQL性能优化是一个持续的过程: 定义SLI,SLO，建立监控体系->性能优化（软件：SQL层、server层（索引，分库分表，参数调优），架构层（读写分离，缓存）；硬件）-> 验证SLO是否达成 ->持续改进

而在这个流程中，80%的问题源于应用层也就是SQL本身或者执行计划不合理

SLI,SLO

定义系统的SLI（服务级别指标） SLO（服务级别目标）的目的是让技术目标和业务价值精准对齐，为服务稳定性提供可衡量的标准， 避免资源错误分配和优化目标偏离。如果没有方向，优化会偏离计划。

SLI将用户体验转为具体的指标比如可用性，延迟，响应时间，错误率等。

SLO则设定SLI的最低目标值，为了让用户满意，描述允许SLI达到的最低限度是多少

另外需要DBA或者运维使用PMM工具建立完整的监控体系： 主动监控和被动监控相结合：

被动监控就是根据performance_schema和慢查询日志，innodb的引擎统计和状态变量，定期采样，入库绘图。

主动监控：针对一些关键SQL进行轻量级探测，出现异常情况后立即报警

主动监控帮我们掌握长期趋势，当某些指标出现恶化的时候，主动探测可以更快定位问题

定位系统瓶颈

查看PMM上影响MySQL性能的关键指标，比如慢查询。IO飙升，连接数太多等问题

执行计划分析

通过查看执行计划可以知道

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询 重点关注全表扫描，是否使用了索引，filesort,临时表

id: SQL查询的序列号，标识子查询联合查询的执行顺序

   id 如果都相同，那么顺序从上到下
   id 都不同，如果是子查询，id越大越先执行
   id 部分相同。id越大越先执行，相同的部分从上往下执行
   id 为null代表 union操作的合并结果集

type - const：基于主键或唯一索引的常量匹配。 - eq_ref：多表连接中的唯一匹配。 - ref：基于非唯一索引的匹配。 - index：扫描整个索引。 - ALL：扫描整个表。

possible_keys: 优化器在生成执行计划时考虑用到了哪些索引

keys: 实际用到的索引

key_len 最大可能长度

rows： 需要读取的行数

extra:

  • using filesort：排序时没有走索引，在内存或磁盘对数据排序，一般order by无索引触发，得优化

  • using temporary:使用了临时表，group by distinct union触发

  • using where: 使用了where条件过滤，有where条件过滤

  • using index：索引树全表扫描，走覆盖索引

  • using join buffer：使用了join buffer来完成嵌套循环连接NLJ，被驱动表没有索引，需要用缓冲区临时保存数据，一般需要建立索引优化

  • using index condition: 索引下推ICP，5.6版本之后的新特性，通过where进一步减少回表次数，在存储引擎层就进行过滤。

  • using union(primary,idx_xxx) 索引合并策略, 通常代表表中没有联合索引，mysql尝试对表中的多个单列索引进行取交集或者并集，这可能会带来严重性能问题，比如取交集一般是where后面跟了AND条件，取并集一般是where后跟了or条件。这会导致走多次索引树，返回的数据集庞大，并且还要排序合并结果，消耗内存。执行速度可能还不如全表扫描。需要建立联合索引进行优化。 慢查询日志分析

慢查询日志分析

 # 1 配置
 在/etc/mysql/conf.d下新增配置slow_query.cnf，并重启服务，如果是容器化的mysql则直接重启容器

 cat slow_query.cnf
 [mysqld]
 slow_query_log = 1
 slow_query_log_file = /tmp/slow-query.log
 long_query_time = 2

 # 2 查询慢查询是否开启
 mysql> show variables like 'slow_query_log%';
 +---------------------+---------------------+
 | Variable_name       | Value               |
 +---------------------+---------------------+
 | slow_query_log      | ON                  |
 | slow_query_log_file | /tmp/slow-query.log |
 +---------------------+---------------------+
 2 rows in set (0.00 sec)

使用mysqldumpslow工具分析慢查询日志

最慢的10条 mysqldumpslow -s r -t 10 log_file_path

最频繁执行的5条 mysqldumpslow -s c -t 5 log_file_path

锁竞争最严重的查询 mysqldumpslow -s l -t N log_file_path

低效SQL

Rows_examined（扫描行数）远大于 Rows_sent（返回行数），说明查询效率低（如全表扫描）。 Query_time（查询总时间）较高，可能达到秒级或更高。 无索引或索引使用不当，导致全表扫描或复杂操作（如文件排序、临时表）

锁问题

Lock_time（锁定时间）显著不为零，甚至接近 Query_time，表明查询因等待锁而延迟。 频繁出现同一SQL的锁等待，可能由长事务或高并发操作引发

重复查询

Count（执行次数）很高，表明同一 SQL 被频繁执行。 Query_time 单次可能不高，但总时间（Sum_query_time）因重复执行而累积。 Rows_sent 较少，但重复执行导致资源浪费

系统优化

SQL

straight_join 不要关联太多表,根据阿里巴巴规范最多三张。 优化器指定的连接顺序不一定合理，需要使用straight_join保证mysql使用结果集更少的表（结果集少!=表数据量少）作为驱动表来提升效率。

例子：查询社交平台的消息中心查询用户和好友的最新消息超时。

用户表的数据量100，消息表数据量10W，receiver\_id无索引，优化器可能会选择user表作为驱动表，但这样会导致性能问题，
如果是user作为驱动表，那么遍历user 100条，每个user都会与消息表做关联查询，如果没有索引 那么会走全表扫描 100 \* len(msg)，如果有索引，那也会走100 \* 100次
但如果是msg作为驱动表，先通过索引找出满足条件的100条消息，再通过遍历100个user找出数据，总的遍历次数只有100 + 100条

    SELECT m.*, u.username 
    FROM msgs m 
    JOIN users u ON m.sender_id = u.id 
    WHERE m.receiver_id = 123 
    ORDER BY m.send_time DESC 
    LIMIT 20;

select 只拉取必要的列，减少IO，TCP包大小和网络带宽占用，增强SQL可读性，sql易维护 select尽量命中索引，如果所有列都在某个索引中，那么可以利用覆盖索引特性避免回表查询。 用不上索引则尝试添加联合索引。

update/delete

1 一定加where条件：避免整张表,

2 where条件最好走索引：优先等值匹配，执行前查看执行计划，避免全表扫描或者范围条件过宽降低执行效率；

3 谨慎更新：先使用select查询保证修改行的范围，再更新，更新完成查看更新行数是否准确，保证没更新错数据；

4 事务控制：如果有多条update 将所有语句塞到事务中，保证原子性，出错及时rollback，避免脏数据

5 大批量更新:按照主键或索引顺序分批批量更新：

避免死锁，锁超时

不分批被锁住的行会更多，死锁概率也会增大，持锁时间也会更长导致innodb超时 分批+统一更新顺序可以避免死锁，并且可以做进一步的优化，在应用层每一批次的末尾校验影响行数，对死锁错误进行捕获并重试

避免长事务

长事务会维护巨量的undo链表，这会占用大内存造成性能瓶颈，切分成小事务可以保证原子性与可恢复性保证整体更新的成功率 如果不分批，某一个update出现问题就需要全部回滚。

insert 1 批量插入代替单条插入，减少IO和网络成本

2 规避插入冲突: 在批量插入的时候使用以下策略规避

方式冲突行为适用场景
INSERT冲突时报错，整个语句结束对数据严格，不能容忍重复或错误
INSERT IGNORE冲突行跳过，其它行正常插入批量导入时希望跳过重复行或脏数据
INSERT ... ON DUPLICATE KEY UPDATE冲突时更新已有行，不插入新行想要“插入或更新”二合一的场景
REPLACE INTO冲突时先删除已有行，再插入新行（相当于 delete+insert）想要用新行数据彻底替换旧行，但不推荐频繁使用，因为会产生额外删除和插入开销

exists in 以小驱大原则

IN 后面跟小表的子查询
SELECT * FROM A WHERE id IN (SELECT id FROM B);
IN关键字原理： 先执行子查询生成结果集 再拿结果到主表匹配

EXISTS 大表的子查询
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
EXISTS关键字原理：遍历主表每一行，拿每一行与子查询表进行匹配， exists子查询返回布尔值

order by order by，where，索引列三者顺序匹配时候，可以利用联合索引最左前缀特性避免文件排序filesort

group by having 1 使用低基数分组，如性别，状态，这类分组少，性能好

2 避免使用高基数的分组，比如ID，可能性能等同于全表扫描+排序

3 优先where过滤，having再做二次过滤： 优化器扫描的时候就使用where过滤，having则是之后分组以后才会过滤，越早过滤，后续计算量越小，性能越好

limit

offset不大时，使用limit offset,cnt即可 offset过大时，使用程序+游标分页，避免跳过大量记录带来的性能开销，这种方式只需要使用一次索引定位+顺序读取即可。

SELECT id, name
FROM users
WHERE id > :last_seen_id（应用侧写入）
ORDER BY id
LIMIT 10;

第一次last_seed_id=0 从0开始取十条 第二次last_seed_id=100000，先查到100000，再往后取10条即可

SQL优化以后用执行计划检查一下

索引

索引的建立和使用是高效查询的关键

建索引 索引的建立有一个基本的三个要点：

一是保持高选择性（字段不重复值个数/总记录数），命中的数据量少，性能越好

选择性范围判断
≥ 0.8选择性非常高，索引非常有效
0.3 ~ 0.8一般可以考虑建索引（需测试）
< 0.3索引选择性低，效果可能很差

二是索引尽可能短，索引占用空间更少

三是索引列的值要选择均匀分布的值（前缀索引则选择前缀），不能选择热点值来做索引，极端情况下可能退化为全表扫描

-- 名称前缀索引（适合LIKE 'prefix%'查询）
CREATE INDEX idx_name_prefix ON task_center (name(20));
适用场景：
-   按任务名称前缀搜索
-   名称模糊查询

联合索引则必须遵循最左前缀原则，要明白查询条件要放在最左列才可以命中索引，另外最常查询的列放在最左边，范围查询的列放到后面，同时考虑覆盖索引还有索引下推

- 任务中心的状态 + 执行时间（覆盖80%查询场景）
CREATE INDEX idx_status_exec_time ON task_center (status, exec_time);
适用场景：
-   查询特定状态的任务（如待处理）
-   查询特定状态+时间范围的任务（如过去24小时失败的任务）
-   按状态分组统计任务数量

-- 状态+ID组合索引(考虑覆盖索引)
CREATE INDEX idx_status_id ON task_center (status, task_id);

**适用场景**：
-   分页查询特定状态的任务列表
-   按状态快速获取任务ID集合
-   状态变更时的快速定位

删除冗余、重复索引，比如已经创建了A,B 还要再创建A的索引就是冗余索引

用索引 也有一些基本准则，要清楚主键索引快于二级索引，等值查询比范围查询快，尽可能的利用索引。

  • 联合索引查询条件必须首先命中联合索引的最左前缀才会走索引
  • 要学会利用覆盖索引覆盖我们想要的查询结果，避免回表
  • 排序的时候过滤条件也要尽量走索引 避免文件排序
  • 避免查询条件不对导致索引失效的场景 比如索引列使用函数或者表达式，or关键字，like的时候使用前置通配符，使用一些不等比较符号

表结构schema

  • 1 表结构至少满足数据库三范式，选择合适数据类型，多使用tinyint,int节省空间
  • 2 控制列数和宽度，必要时将类数据拆分到辅助表，降低查询和缓存压力
  • 3 避免使用ENUM和NULL字段
  • 4 根据业务看看能不能分区或归档，避免全表扫描

调整innodb参数

如果调整了索引，表结构、优化了sql都还不能解决问题，那么得考虑调整配置参数了

  • 增大缓冲池大小，比如调整为内存的60%到75%。让更多的数据和索引留在内存中，降低IO交互
  • 调整redo log大小和组的数量，降低fsync带来的写延迟。控制日志刷盘频率和恢复时长
  • 调整innodb_flush_method为O_DIRECT让innodb绕过OS缓存，直接通过自己的缓冲池来读写数据
  • 调整刷脏页的速率参数innodb_io_capacity和innodb_io_capacity_max，提升脏页刷盘的速率。加速purge回收

优化硬件

请运维同学选择单核性能好的CPU，将MySQL线程绑定到固定CPU，减少缓存抖动 选择更快容量更大适合mysql的硬盘

压测

性能优化好以后，模拟峰值负载进行压测，验证调优效果，最后上线重监控，确保无其他新瓶颈出现

持续优化

随着业务和数据不断演变，原有索引和查询优化可能失效，新功能上线可能也会带来新的性能瓶颈，因此需要监控并持续优化

avatar
文章
阅读
粉丝