MySQL - 性能优化

MySQL 性能优化

优化 SQL 的一般步骤
EXPLAIN 分析执行计划
索引问题
常用 SQL 的优化
优化数据库对象
优化 MySQL Server
应用优化

优化 SQL 的一般步骤

通过 SHOW STATUS 了解 SQL 执行频率：

-- 查看全局状态
SHOW GLOBAL STATUS;

-- 重要的计数参数（对所有引擎）
SHOW STATUS LIKE 'Com_select';    -- SELECT 次数
SHOW STATUS LIKE 'Com_insert';    -- INSERT 次数
SHOW STATUS LIKE 'Com_update';    -- UPDATE 次数
SHOW STATUS LIKE 'Com_delete';    -- DELETE 次数

-- InnoDB 专有计数
SHOW STATUS LIKE 'Innodb_rows_read';     -- 查询返回行数
SHOW STATUS LIKE 'Innodb_rows_inserted'; -- 插入行数
SHOW STATUS LIKE 'Innodb_rows_updated';  -- 更新行数
SHOW STATUS LIKE 'Innodb_rows_deleted';  -- 删除行数

-- 其他重要参数
SHOW STATUS LIKE 'Connections';   -- 连接次数
SHOW STATUS LIKE 'Uptime';        -- 运行时间
SHOW STATUS LIKE 'Slow_queries';  -- 慢查询次数

定位执行效率较低的 SQL：

通过慢查询日志，用 --log-slow-queries[=file_name] 选项启动，记录执行时间超过 long_query_time 秒的 SQL
使用 SHOW PROCESSLIST 查看当前 MySQL 正在进行的线程，实时的查看 SQL 执行情况

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

EXPLAIN 分析执行计划

通过 EXPLAIN 或 DESC 获取 MySQL 如何执行 SELECT 语句的信息。

EXPLAIN SELECT sum(moneys) FROM sales a, companys b WHERE a.company_id = b.id AND a.year = 2006;

EXPLAIN 输出列说明：

列	说明
`id`	SELECT 标识符
`select_type`	SELECT 类型（SIMPLE/PRIMARY/SUBQUERY 等）
`table`	输出结果集的表
`type`	表的连接类型
`possible_keys`	查询时可以使用的索引列
`key`	实际使用的索引
`key_len`	使用的索引长度
`ref`	哪个列或常数与 key 一起从表中选择行
`rows`	扫描的行数估计值
`Extra`	执行情况的说明和描述

type 列的值（从好到坏）：

system：表仅有一行，最佳连接类型
const：表最多有一个匹配行
eq_ref：使用主键或唯一索引，每个索引键只匹配一行
ref：使用非唯一索引，返回所有匹配某个值的行
range：使用索引返回一个范围的行
index：扫描整个索引树
ALL：全表扫描，最差类型，需要考虑添加索引

Extra 列常见值：

Using index：使用覆盖索引，只从索引中获取数据
Using where：使用 WHERE 过滤
Using temporary：使用临时表，通常需要优化
Using filesort：使用文件排序，通常需要优化

索引问题

索引的存储分类：MyISAM 和 InnoDB 使用 B-树索引，MEMORY 表默认使用 hash 索引。

查看索引使用情况：

SHOW STATUS LIKE 'Handler_read%';

Handler_read_key 值高表示索引使用良好
Handler_read_rnd_next 值高意味着查询低效，可能需要更多索引或索引使用不当

两个简单实用的优化方法：

-- 定期分析表，更新索引统计信息
ANALYZE TABLE mytable;

-- 优化表，回收空间碎片（特别是大量删除/更新后）
OPTIMIZE TABLE mytable;

常用 SQL 的优化

大批量插入数据：

-- 大批量导入时，临时禁用索引和唯一性检查可加速
SET unique_checks = 0;
SET autocommit = 0;

-- 导入数据...
LOAD DATA INFILE '/path/to/data' INTO TABLE mytable;

SET unique_checks = 1;
COMMIT;

优化 INSERT 语句：

-- 使用多值 INSERT 代替多个单行 INSERT
INSERT INTO mytable (a, b) VALUES (1,2), (3,4), (5,6);

-- 使用 INSERT DELAYED（MyISAM）将插入放入队列

优化 GROUP BY 语句：默认 MySQL 会对 GROUP BY 的结果排序，如果不需要排序可以用 ORDER BY NULL。

SELECT cust_id, COUNT(*) FROM orders GROUP BY cust_id ORDER BY NULL;

使用 WITH ROLLUP 子句在分组统计基础上再做超级汇总：

SELECT year, country, product, SUM(profit)
FROM sales
GROUP BY year, country, product WITH ROLLUP;

优化 ORDER BY 语句：尽量让 ORDER BY 的列使用索引，避免 Using filesort。

优化 JOIN 语句：

在关联字段上建立索引
小表驱动大表，将小表放在 JOIN 的左边

MySQL 如何优化 OR 条件：OR 两边的列都有索引时才会使用索引合并。

查询优先还是更新优先：可以使用 LOW_PRIORITY 和 HIGH_PRIORITY 提示调整优先级。

-- 降低 INSERT 优先级，让查询优先
INSERT LOW_PRIORITY INTO mytable ...;

-- 提高 SELECT 优先级
SELECT HIGH_PRIORITY * FROM mytable;

优化数据库对象

优化表的数据类型：使用 PROCEDURE ANALYSE() 获取数据类型优化建议。

SELECT * FROM tbl_name PROCEDURE ANALYSE(16, 256);

通过拆分提高表的访问效率：
- 纵向拆分：按访问频度将经常访问和不经常访问的字段拆分到两个表
- 横向拆分：将数据按某种规则拆分到多个表或分区
逆规范化：对于查询操作很多的应用，适当冗余数据可以提高查询效率，代价是更新时需要维护多份。
使用冗余统计表：对大表的统计分析，将中间结果移到临时表比直接在大表上统计更高效。
选择更合适的表类型：锁冲突严重时考虑改为 InnoDB 行锁；查询多且对事务要求不严时考虑 MyISAM。

优化 MySQL Server

查看服务器参数：

-- 查看参数默认值
mysqld --verbose --help

-- 查看参数实际值
SHOW VARIABLES;

-- 查看运行状态
SHOW STATUS;

影响性能的重要参数：

参数	说明
`key_buffer_size`	MyISAM 索引缓存大小，只适用于 MyISAM
`table_cache`	打开表的缓存数量，与 max_connections 相关
`innodb_buffer_pool_size`	InnoDB 数据和索引缓存，专用服务器可设为物理内存的 80%
`innodb_flush_log_at_trx_commit`	事务提交时日志刷新策略（0/1/2）
`innodb_log_file_size`	日志文件大小，高写入负载时增大
`innodb_log_buffer_size`	日志缓冲大小，通常 8-16MB
`innodb_lock_wait_timeout`	行锁等待超时时间（默认 50 秒）
`innodb_doublewrite`	双写缓冲，对性能要求高于数据完整性时可关闭

innodb_flush_log_at_trx_commit 的取值：

0：日志缓冲每秒写一次到日志文件并刷新磁盘，崩溃时最多丢失 1 秒事务（最快但不安全）
1：每次事务提交都写日志并刷新磁盘（最安全，默认值）
2：每次提交写到日志文件但不刷新磁盘，每秒刷新一次（操作系统不崩溃就不丢数据）

应用优化

使用连接池：减少建立和释放连接的开销
减少对 MySQL 的访问：
- 避免对同一数据做重复检索
- 使用查询缓存（Query Cache）
- 在应用层加 cache
负载均衡：
- 利用 MySQL 复制分流查询操作（主写从读）
- 采用分布式数据库架构

检查表缓存是否过小：

SHOW STATUS LIKE 'Opened_tables';
-- 如果值很大，说明表缓存不够，应增大 table_cache

检查锁等待情况：

-- 表锁等待情况
SHOW STATUS LIKE 'Table%';
-- Table_locks_waited 值高说明表锁竞争严重

-- InnoDB 行锁等待情况
SHOW STATUS LIKE 'innodb_row_lock%';
-- Innodb_row_lock_waits 值高说明行锁竞争严重

MySQL - 性能优化