大家好,我是烤鸭:
这是根据官方文档提炼出的mysql性能优化总结。
想看完整翻译版的请看 https://blog.csdn.net/Angry_Mills/article/details/87720396
1. 成本优化
成本包含: IO 和 CPU 从硬盘读取的花费
模型包含: 全表扫描(IO成本:表中的pages * IO阻塞读取成本 CPU成本: 行 * 行计算成本) 和 范围索引扫描(IO成本:范围中的行 * IO阻塞读取成本 IO成本:范围中的行 * 行计算成本)
2. 利用工具监视sql
MySQL Enterprise Monitor (MEM), Query Analyzer
Performance schema 执行计划
events_statements_history,events_statements_history_long
大部分最近执行的statement
events_statements_summary_by_digest
总结相似操作(相同的statement合并)
file_summary_by_event_name
Interesting event: wait/io/file/innodb/innodb_data_file
table_io_waits_summary_by_table
table_io_waits_summary_by_index_usage
统计存储引擎的每个表和索引
EXPLAIN/EXPLAIN FORMAT=JSON
调试查询计划:
SELECT trace FROM information_schema.optimizer_trace INTO OUTFILE <filename> LINES TERMINATED BY '';
SET optimizer_trace="enabled=off";
3.数据访问和索引优化
使用索引/添加索引/复合索引/索引合并
注意低选择性的索引!(字段长度或者变量比较少,比如sex int 1)
4.连接优化
找到最好的连接顺序
使用 STRAIGHT_JOIN
优化连接顺序
对非索引列进行过滤
部分表强制提前处理无需暗示
将派生表合并到外部查询中
无临时表
5.子查询优化
IN 转换为 EXISTS
例子:
SELECT title FROM film WHERE film_id IN (SELECT film_id FROM actor WHERE name=“Bullock”)
=> SELECT title FROM filmWHERE EXISTS (SELECT 1 FROM actor WHERE name=“Bullock” AND film.film_id = actor.film_id)
优势:子查询将计算更少的记录
Semi-join 半连接
将子查询转换为内连接,但需要一些方法去重
匹配优先(等价于IN—>EXISTS的方式)
懒扫描(索引扫描,跳过重复)
实体化 MatLookup(像子查询实体化),MatScan(实体化表在连接顺序的第一位)
去重(用唯一索引将半连接的行插入临时表;重复列将会被拒绝。无论连接顺序)
优势:有更多优化"连接顺序"的选择
限制:如果子查询包含union(并集)或者aggregation(聚合)不能使用半连接
子查询实例化:
执行一次子查询并在临时表中存结果,表有唯一索引可以快速查找并去重
执行外部查询并检查临时表中的匹配项
派生表:
例子:
SELECT AVG(o_totalprice) FROM ( SELECT * FROM orders ORDER BY o_totalprice DESC LIMIT 100000 ) td;
mysql 5.6 以前 : 单独执行并将结果存储在临时表中(实现)
mysql 5.7 处理类似于视图的派生表:可以与外部查询块合并
注意:用外部连接合并派生表,基于GROUP BY,DISTINCT,LIMIT或聚合函数的派生表将不会合并
外部查询与派生表MERGE
NO_MERGE: 可用于覆盖默认行为
SELECT /*+ NO_MERGE(dt) */ * FROM t1 JOIN (SELECT x, y FROM t2) dt ON t1.x = dt.x;
MERGE: 强制合并
SELECT /*+ MERGE(dt) */ * FROM t1 JOIN (SELECT x, y FROM t2) dt ON t1.x = dt.x;
也可以使用MERGE / NO_MERGE暗示查看视图
SELECT /*+ NO_MERGE(v) */ * FROM t1 JOIN v ON t1.x = v.x;
6.排序
通常的解决方案;"FileSort"
在排序之前将查询结果存储在临时表中
如果数据量很大,可能需要在磁盘上使用中间存储进行多次传递排序
优化
利用索引按排序顺序生成查询结果
对于"LIMIT n查询,保留内存中n个顶级项的优先级队列而不是文件排序。
执行计划
可从执行计划中对每个语句进行排序
优化方式
增加排序缓冲区 SET sort_buffer_size = 8*1024*1024;
使用索引避免排序
重新研究案例
7.影响的优化器
增加索引
强制使用特定索引 使用索引,强制索引,忽略索引
强制特定的关联顺序
STRAIGHT_JOIN
调整会话变量
set optimizer_switch="index_merge=off";set sort_buffer=8*1024*1024;set optimizer_search_depth = 10;
mysql 5.7 优化
(BKA) 批量key访问 (BNL) 阻塞嵌套循环算法
(Multi-Range Read ) 多范围读取 (表的索引)
连接和子查询(策略)
命中索引的情况
范围优化(索引)
阻塞查询
未来 mysql 8.0 语法可能的优化:
启用/禁用视图和派生表的合并
MERGE() NO_MERGE()
连接顺序
考虑添加的暗示:
强制/忽略index_merge替代方案
重新实现新语法中的索引暗示
暂时为一个查询设置会话变量
mysql 5.7 重写查询插件
无需更改应用程序即可重写有问题的查询
更新连接顺序... 更多
向表中添加重写规则
可调成本常量(不建议使用)