MySQL性能优化-EW帮帮网

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询日志文件路径（默认在数据目录下，名为hostname-slow.log）
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';

-- 设置慢查询时间阈值（单位：秒，默认10秒）
SET GLOBAL long_query_time = 1;

-- 记录未使用索引的查询（可选）
SET GLOBAL log_queries_not_using_indexes = 'ON';

永久生效（修改配置文件）：

修改MySQL配置文件 my.cnf（Linux）或 my.ini（Windows），在 [mysqld] 段添加：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

重启MySQL服务使配置生效：

# Linux
systemctl restart mysqld

# Windows（服务名可能不同）
net stop mysql
net start mysql

2> 查看慢查询日志

直接查看日志文件：

# 查看慢查询日志内容
cat /var/lib/mysql/slow.log

# 或使用分页工具
more /var/lib/mysql/slow.log

使用 mysqldumpslow 工具分析：

MySQL内置工具 mysqldumpslow 可统计慢查询日志中的高频SQL：

# 按执行时间排序
mysqldumpslow -s t /var/lib/mysql/slow.log

# 按出现次数排序
mysqldumpslow -s c /var/lib/mysql/slow.log

# 显示前10条最慢的查询
mysqldumpslow -t 10 /var/lib/mysql/slow.log

通过 information_schema 表查询（需开启日志记录到表）：

如果启用了 log_output = 'TABLE'，可通过以下SQL查询：

-- 查看慢查询记录
SELECT * FROM mysql.slow_log;

3> 关键参数说明

参数名	作用	默认值
`slow_query_log`	是否开启慢查询日志（`ON`/`OFF`）	`OFF`
`slow_query_log_file`	慢查询日志文件路径	主机名-slow.log
`long_query_time`	慢查询时间阈值（单位：秒），超过此时间的SQL会被记录	`10`
`log_queries_not_using_indexes`	是否记录未使用索引的查询（`ON`/`OFF`）	`OFF`
`min_examined_row_limit`	设置需要检查的最小行数阈值，低于此值的查询不会被记录	`0`
`log_output`	日志输出方式（`FILE`/`TABLE`/`NONE`）	`FILE`

2、EXPLAIN 执行计划分析

在执行SQL语句时，MySQL会对SQL进行解析、优化、执行三个步骤。在优化阶段，MySQL查询优化器会生成一个执行计划，该计划被称为查询计划或执行计划。执行计划告诉MySQL执行SQL语句的具体步骤，包括表的连接方式、扫描方式、过滤条件等。

查看sql执行计划的sql：

-- EXPLAIN 查询sql
EXPLAIN SELECT * FROM table_name WHERE conditions;

使用EXPLAIN ANALYZE（MySQL 8.0+）：获取实际执行时间和详细步骤

执行计划中的核心字段：

1> id：标识查询中每个SELECT子句的执行顺序。

相同id：按顺序执行（如多表JOIN）。
不同id：id值越大，优先级越高（如子查询）。
包含UNION时，可能出现<unionM,N>或<derivedN>。

2> select_type：表示查询类型。

SIMPLE：简单查询（无子查询或UNION）。
PRIMARY：外层主查询。
SUBQUERY：子查询中的第一个SELECT。
DERIVED：FROM子句中的派生表。
UNION：UNION中的第二个或后续查询。
UNION RESULT：UNION的结果集。

3> table：当前行操作的表名。

4> type：表示表的访问类型，，包括ALL（全表扫描）、index（索引扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（JOIN中通过主键或唯一索引关联）、const（通过主键或唯一索引查找，返回一行）等。

性能排序（从优到劣）：system > const > eq_ref > ref > range > index > ALL

5> possible_keys & key：可能用到的索引 & 实际使用的索引。

6> rows：预估扫描的行数。值越小越好。

7> Extra：包含MySQL执行计划中的其他信息，例如是否使用了临时表、是否使用了文件排序等。

Using index：覆盖索引（无需回表）。
Using where：存储引擎检索后过滤。
Using temporary：使用临时表（常见于GROUP BY）。
Using filesort：额外排序（需优化ORDER BY）。
Using join buffer：使用连接缓存。
Using index condition：触发索引下推（将部分 WHERE 条件提前到存储引擎层处理，减少回表次数）

重点关注：

type：访问类型（至少达到range级别）
key：实际使用的索引
rows：扫描行数
Extra：是否出现Using filesort或Using temporary

3、索引类型选择

按数据结构分类：

类型	特点	适用场景
B+Tree	默认索引类型，支持范围查询、排序、最左前缀匹配	绝大多数场景
Hash	仅支持精确查询（=、IN），无法排序或范围查询	等值查询且数据离散度高
FullText	全文检索（关键词匹配）	文本内容的模糊搜索
R-Tree	空间索引，支持地理数据查询	GIS数据存储与查询

MySQL主要用的是B+树索引。B+树索引的结构特点如下：

叶子节点存储完整数据行（聚簇索引）或主键值+索引列（非聚簇索引）。
所有叶子节点形成有序链表，支持高效范围查询。
非叶子节点仅存储索引键和指针，降低树的高度。

按逻辑功能分类

主键索引（PRIMARY KEY）：唯一且非空，表的主键自动成为聚簇索引。
唯一索引（UNIQUE）：确保列值的唯一性，允许NULL值。
普通索引（INDEX）：最基本的加速查询索引。
联合索引（Composite Index）：多列组合索引，遵循最左前缀原则。
覆盖索引（Covering Index）：索引包含查询所需全部字段，无需回表。

-- 创建普通索引
CREATE INDEX idx_name ON table(column);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_name ON table(column);

-- 创建联合索引：多列组合索引，遵循最左前缀原则
CREATE INDEX idx_name ON table(col1, col2, col3);

-- 创建全文索引（仅适用于InnoDB/MyISAM）
ALTER TABLE table ADD FULLTEXT INDEX idx_name(content);

-- 查看索引
SHOW INDEX FROM table_name;

-- 删除索引
DROP INDEX idx_name ON table;

-- 查看索引使用情况（Handler_read_key表示索引命中次数）
SHOW STATUS LIKE 'Handler_read%';

-- 索引碎片整理
-- 优化表重建索引（InnoDB）
ALTER TABLE table_name ENGINE=InnoDB;
-- 或使用OPTIMIZE TABLE
OPTIMIZE TABLE table_name;

4、索引使用原则

选择性原则：选择区分度高的列建索引（如COUNT(DISTINCT col)/COUNT(*)接近1）
最左前缀原则：联合索引必须从最左列开始使用（如索引(a,b,c)，查询条件需包含a或a,b等）
覆盖索引优先：索引包含查询所需字段，避免回表
短索引原则：对长字符串使用前缀索引（如INDEX(email(10))）

5、常见索引失效场景

场景	示例	解决方法
对索引列使用函数或运算	`WHERE YEAR(create_time) = 2023`	改写为范围查询
隐式类型转换	`WHERE id = '100'`（id为INT类型）	保持类型一致
OR条件未全索引覆盖	`WHERE a=1 OR b=2`（仅a有索引）	改用UNION或单独索引
LIKE以通配符开头	`WHERE name LIKE '%abc%'`	改用全文索引或倒序存储
联合索引未遵循最左前缀	索引`(a,b,c)`，查询条件只有`b=1 AND c=2`	调整查询条件或索引顺序

二、SQL语句优化

1、避免低效操作符

OR条件：改用UNION ALL，例如SELECT id FROM t WHERE num=10 OR num=20优化为分两次查询合并。
IN和 NOT IN：连续数值用BETWEEN代替（连续数值范围查询更高效），或使用EXISTS替代子查询。IN适合子查询结果集较小的情况。EXISTS 适用于子查询结果集可能很大的情况。

当使用 IN 时，MySQL 会首先执行子查询，然后将子查询的结果集用于外部查询的条件。这意味着子查询的结果集需要全部加载到内存中。

而 EXISTS 会对外部查询的每一行，执行一次子查询。如果子查询返回任何行，则 EXISTS 条件为真。EXISTS 关注的是子查询是否返回行，而不是返回的具体值。

-- 低效，IN 的临时表可能成为性能瓶颈
SELECT num FROM a WHERE num IN (SELECT num FROM b);
-- 高效，EXISTS 可以利用关联索引
SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.num = a.num);

模糊查询：避免左模糊（LIKE '%abc%'），改用右模糊（LIKE 'abc%'）或全文检索
避免 WHERE 子句中的函数：如 WHERE DATE(create_time) = '2023-01-01' 改为范围查询
避免字段计算

-- 低效
SELECT * FROM t WHERE num/2 = 100;
-- 高效
SELECT * FROM t WHERE num = 100 * 2; :cite[1]:cite[5]

2、减少数据扫描量

SELECT *：仅查询必要字段，减少数据传输和I/O开销。

SELECT * 可能导致覆盖索引失效，增加回表开销。

分页优化：使用游标分页（WHERE id > 1000 LIMIT 10 代替 LIMIT 1000,10）或使用子查询或覆盖索引（如 WHERE id > (SELECT id FROM table LIMIT 100000, 1)）

分页偏移量过大时，LIMIT M,N 需要扫描 M+N 行，子查询通过索引覆盖减少扫描范围

3、子查询优化

使用JOIN代替子查询，避免临时表创建

4、其他高频优化技巧

优化 COUNT(*)：对 InnoDB 表，可通过维护统计表或缓存近似值替代直接查询。
排序优化：利用索引排序（如 ORDER BY 字段与索引顺序一致），避免 filesort。
减少锁竞争：使用 InnoDB 行锁，避免批量更新导致锁升级为表锁

三、表设计优化

1、数据类型优化

1> 最小化存储原则

使用 TINYINT 代替 INT 存储状态值（0-255）
DATETIME(6) 保留微秒时优先于 VARCHAR 存储时间
金额字段使用 DECIMAL(18,2) 而非 DOUBLE 避免精度丢失

2> 字符串优化

固定长度用 CHAR(32)（如MD5值）
变长字段用 VARCHAR(255) 并避免过度预留长度
大文本分离到单独表，主表存储 TEXT 的指针

3> 时间类型选择

未来时间用 DATETIME（支持范围更大），DATETIME 的默认值为 null，占用 8 个字节
更新时间戳用 TIMESTAMP（自动更新特性），TIMESTAMP 的默认值为当前时间，占 4 个字节

4> 主键设计

自增ID：BIGINT UNSIGNED AUTO_INCREMENT
业务主键：订单号使用 CHAR(24) 包含时间戳+随机数
禁用UUID无序主键（导致页分裂）

5> 范式与反范式平衡

冗余设计：用户表增加 order_count 字段避免实时COUNT
预计算：商品表添加 avg_rating 字段定期更新

6> 字段约束

NOT NULL 字段默认值：
- status TINYINT NOT NULL DEFAULT 0
- gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

7> 注释规范

COMMENT '0-未支付 1-已支付 2-已取消'
COMMENT '单位：分'

四、架构设计优化

1、读写分离

主从复制：主库处理写操作，从库处理读请求。
中间件：使用ShardingSphere、MyCat实现自动路由。

2、分库分表

垂直分库：按业务拆分（用户库、订单库）。
水平分表：按分片键（如用户ID哈希）拆分到多张表。
工具选择：推荐ShardingSphere（Java友好，无需代理）。

3、缓存整合

本地缓存：Caffeine（本地缓存库）缓存静态数据（如配置表）。
分布式缓存：Redis缓存热点数据（如商品详情）。

五、硬件与配置优化

1、磁盘优化

使用SSD替代机械硬盘，提升随机IO性能。
调整RAID级别（如RAID 10）或使用NVMe SSD。

2、内存配置

1> innodb_buffer_pool_size

作用：InnoDB 存储引擎的核心缓存，用于缓存表数据、索引和事务日志。
推荐值：通常设置为物理内存的 70-80%（如果服务器专用于 MySQL）。
影响：增大此值可减少磁盘 I/O，显著提升查询性能。

2> innodb_log_buffer_size

作用：事务日志的缓冲区大小，用于暂存未写入磁盘的事务日志。是内存中的redo log buffer。
推荐值：默认 16MB，高并发事务场景可调整为 64-256MB。
影响：减少日志写入磁盘的频率，提高事务处理速度。

3、连接与线程相关

1> max_connections

作用：MySQL 允许的最大并发连接数。
推荐值：根据业务需求调整（默认 151），避免过高导致内存耗尽。
关联参数：
- thread_cache_size：缓存空闲线程数，减少线程创建开销。
- wait_timeout 和 interactive_timeout：控制非活动连接的自动断开时间。

2> back_log

作用：在短时间内处理大量连接请求时，排队等待的请求数量。
推荐值：高并发场景下可适当调高（如 500）。

4、查询优化相关

1> tmp_table_size 和 max_heap_table_size

作用：控制内存临时表的最大大小，避免复杂查询频繁使用磁盘临时表（*.ibd）。
推荐值：两者设为相同值（如 64M-256M）。

2> sort_buffer_size 和 join_buffer_size

作用：排序和连接操作的内存缓冲区。
注意：默认值通常足够，过高可能导致内存浪费。建议按需调整。

5、日志与持久化

1> innodb_log_file_size

作用：InnoDB 事务日志(redolog)文件大小。
推荐值：通常设置为 1-4GB，较大的日志文件可减少磁盘写入频率。
关联参数：innodb_log_files_in_group（日志文件数量，默认 2）。

2> sync_binlog

作用：控制二进制日志（binlog）写入磁盘的频率。
推荐值：
- 0：由系统决定（性能高，但可能丢失数据）。
- 1：每次事务提交都同步（安全性高，性能较低）。

3> innodb_flush_log_at_trx_commit

作用：控制事务日志（redo log）的刷盘策略。
推荐值：
- 1：每次提交都刷盘（ACID 安全，性能较低）。
- 2：每秒刷盘（平衡性能与安全）。
- 0：依赖系统刷盘（性能最高，风险最大）。

6、存储与I/O优化

1> innodb_io_capacity

作用：InnoDB 后台进程（如刷脏页）的 I/O 吞吐量上限。
推荐值：根据磁盘类型调整（如 HDD 设为 200，SSD 设为 2000-5000）。

2> innodb_flush_method

作用：控制 InnoDB 数据文件和日志文件的写入方式。
推荐值：
- O_DIRECT：绕过操作系统缓存，直接写入磁盘（推荐用于专用服务器）。
- fdatasync：默认方式，适合通用场景。

3> innodb_file_per_table

作用：每个 InnoDB 表使用独立的表空间文件（.ibd）。
推荐值：设为 ON，便于管理和空间回收。

7、其他关键参数

1> max_allowed_packet

作用：控制客户端发送的最大数据包大小（如大字段插入）。
推荐值：根据业务需求调整（如 64M-256M）。

2> innodb_lock_wait_timeout

作用：事务等待行锁的超时时间（秒）。
推荐值：默认 50，高并发场景可适当降低。

六、监控

1、关键监控指标

性能指标：QPS、TPS、慢查询数、锁等待时间。
资源利用：CPU使用率、内存缓冲池命中率、磁盘I/O吞吐量。
复制状态：主从延迟（Seconds_Behind_Master）、复制线程状态。
连接管理：活跃连接数（Threads_connected）、线程池利用率。

2、实时状态监控命令

1> SHOW PROCESSLIST

作用：查看当前所有客户端连接和正在执行的 SQL 语句。

SHOW FULL PROCESSLIST; -- 显示完整 SQL 语句

关键字段：
- State：连接状态（如 Sending data、Locked）。
- Time：查询已执行的时间（秒）。
- Info：正在执行的 SQL（需 FULL 关键字显示完整内容）。

2> SHOW ENGINE INNODB STATUS

作用：查看 InnoDB 存储引擎的详细状态，包括锁、事务、缓冲池等。
关键信息：
- TRANSACTIONS：当前活跃事务。
- BUFFER POOL AND MEMORY：缓冲池使用情况。
- ROW OPERATIONS：行级操作统计。

3> SHOW GLOBAL STATUS

作用：查看全局统计信息（如连接数、查询数、缓存命中率）。

SHOW GLOBAL STATUS LIKE 'Threads_connected'; -- 当前连接数  
SHOW GLOBAL STATUS LIKE 'Innodb_row_%';      -- InnoDB 行操作统计  
SHOW GLOBAL STATUS LIKE 'Qcache%';           -- 查询缓存命中率（仅适用于旧版本）

4> SHOW VARIABLES

作用：查看 MySQL 配置参数（如 max_connections、innodb_buffer_pool_size）。

SHOW VARIABLES LIKE 'innodb_log_file%'; -- 查看 InnoDB 日志文件配置

3、开源监控工具

工具名称	核心功能	适用场景
Prometheus+Grafana	时序数据采集+可视化，支持自定义仪表盘和告警规则；需配合MySQL Exporter采集指标。	云原生环境、分布式架构监控
Percona PMM	专为MySQL/MariaDB设计，提供慢查询分析、InnoDB状态监控、复制延迟检测等深度功能。	企业级性能优化与深度诊断
Zabbix	全栈监控（服务器+数据库），支持灵活告警和历史数据分析，需配置监控模板。	中大型集群监控、多维度指标跟踪
Nagios	服务可用性监控，支持插件扩展（如MySQL连接数、进程状态），侧重告警及时性。	基础服务存活监控与告警管理
Percona Toolkit	包含 `pt-query-digest`（慢查询分析）、`pt-table-checksum`（主从一致性校验）等工具。	慢查询优化、数据一致性校验

4、实战：Prometheus+Grafana监控MySQL

1> 安装MySQL Exporter：

wget https://github.com/prometheus/mysqld_exporter/releases/latest/download/mysqld_exporter.tar.gz
./mysqld_exporter --config.my-cnf=/etc/mysql/my.cnf

2> 配置Prometheus（prometheus.yml）：

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']  # MySQL Exporter端口

3> Grafana导入模板：使用ID 7362展示QPS、缓冲池命中率等指标

MySQL性能优化

一、索引优化

1、慢查询日志分析