mysql 性能调优

快来打我* 2023-06-02 09:27 3阅读 0赞

SQL 调优

转自：[https://blog.calvin.wang/2019/09/16/mysql-optimizer.html?hmsr=toutiao.io&utm\_medium=toutiao.io&utm\_source=toutiao.io][https_blog.calvin.wang_2019_09_16_mysql-optimizer.html_hmsr_toutiao.io_utm_medium_toutiao.io_utm_source_toutiao.io]  
为什么要有索引：以 JOIN Algorithms 为例  
假设存在表： School(id int, name char) 和 Clazz(id int, school\_id int refer School) sql：

select \*  
from  
school  
inner join  
clazz  
on school\_id = school.id  
where name > 'a'  
以上 SQL在各个算法下的实现

Nested-Loop Join Algorithm  
伪代码：

for each row in School matching range \{  
for each row in Clazz \[matching reference key\] \{  
if row satisfies join conditions, send to client  
\}  
\}  
没有 index 的情况下： 复杂度 n^2

存在 index 的情况下： 复杂度 nlog(n)

Block Nested-Loop Join Algorithm  
伪代码：

for each row in School matching range \{  
store used columns from School in join buffer  
if buffer is full \{  
for each row in Clazz \{  
for each School combination in join buffer \{  
if row satisfies join conditions, send to client  
\}  
\}  
empty join buffer  
\}  
\}  
减少循环次数为：s（单次存储大小） \* c（存储次数） / JoinBufferSize + 1

所以： JOIN 字段必须有索引 | JOIN 字段必须有索引 | JOIN 字段必须有索引 以及 不要进行太多表 join （阿里规范不超过 3 张）

基本原则： 最左匹配（5.7 及其以下版本）  
假设存在表：t1(id int, key1 int, key2 int, key3 int, nokey int) idx\_comb(k1, k2, k3)

索引idx\_comb的存在相当于建立如下三个索引： idx(k1), idx(k1, k2) 和 idx(k1, k2, k3)

意味着如下查询能使用索引：

select \* from t1 where c(k1);  
select \* from t1 where c(k1, k2);  
select \* from t1 where c(k1, k2, k3);  
但是如下查询不行：

select \* from t1 where c(k2);  
select \* from t1 where c(k2, k3);  
所以: 避免创建冗余索引

优化三个工具： EXPLAIN， PROFILE， OPTIMIZER\_TRACE  
网上文档提别多这里不细讲， 会在后面使用的时候介绍

Explain：  
用处： 查看一个这些SQL语句的执行计划。 比如有没有使用索引， 使用那些索引等等。  
使用:  
explain \[extended | format=json\] sql；  
\[show warnings;\]  
PROFILE  
用处： 查询SQL实际执行状态， 如 System lock、Table lock 和排序 花多少时间等等  
使用：  
SET profiling = 1;  
sql  
SHOW PROFILES;  
SHOW PROFILE FOR QUERY <QueryID>  
OPTIMIZER\_TRACE  
使用：查实际执行流程，分析、验证优化思路  
SET OPTIMIZER\_TRACE="enabled=on";  
SET OPTIMIZER\_TRACE\_MAX\_MEM\_SIZE=1000000;  
sql  
SELECT \* FROM INFORMATION\_SCHEMA.OPTIMIZER\_TRACE;  
Query Optimizer Hints:  
SQL\_NO\_CACHE: 不使用缓存(另外从 mysql 8 起不再支持 query cache)  
SQL\_BUFFER\_RESULT: 强制将结果先缓存至临时表, 再返回客户端.(在将大量数据发送至客户端时尽可能快的释放表锁)  
range(以下均适用于btree, hash 限制略有不同):  
mysql中所有的条件最终都会被优化为range, 等值是range的一种特列. 比如: a=2 会被解析为 2 <= a <= 2

Range Access Method for Single/Many-Part Indexes:  
限制:

几乎支持所有操作符, 其中like要求不以统配符作为起始字符, 即: 符合 like 'abc%' 不符合 like '%bc%'  
操作符两侧为index key(满足最左匹配) 和 常量  
常量包括: \[str, 来自 const(只查出一行) join, 来自 system(表只有一行) join, 非相关子查询\]  
估算索引扫描范围: 存在以下sql:

\-- keyword 有索引, ename 没有索引  
SELECT  
keyword  
FROM  
course  
WHERE  
(keyword < 'DZ' AND (keyword LIKE 'DQ%' OR keyword LIKE '%English' ))  
OR  
(keyword < 'Alo7' AND keyword IS NOT NULL AND ename = 'ABC Books Video')  
OR  
(keyword < 'u' AND keyword > 'z')  
通过OPTIMIZER\_TRACE得到条件优化结果:

// 最终优化为: "NULL < keyword < DZ"  
\{  
"range\_scan\_alternatives": \[  
\{  
"index": "index\_course\_on\_keyword",  
"ranges": \[  
"NULL < keyword < DZ"  
\],  
"index\_dives\_for\_eq\_ranges": true,  
"rowid\_ordered": false,  
"using\_mrr": false,  
"index\_only": false,  
"rows": 225,  
"cost": 271.01,  
"chosen": false,  
"cause": "cost"  
\}  
\]  
\}  
咱们来推导以下这个range 条件怎么得到的:

\-- 原始条件:  
(keyword < 'DZ' AND (keyword LIKE 'DQ%' OR keyword LIKE '%English' ))  
OR  
(keyword < 'Alo7' AND keyword IS NOT NULL AND ename = 'ABC Books Video')  
OR  
(keyword < 'u' AND keyword > 'z')

\-- 用TRUE替换非range 条件.  
(keyword < 'DZ' AND (keyword LIKE 'DQ%' OR TRUE ))  
OR  
(keyword < 'Alo7' AND keyword IS NOT NULL AND TRUE)  
OR  
(keyword < 'u' AND keyword > 'z')

\-- 简化/折叠 条件  
\--- 1. 评估并简化含TRUE/FALSE 表达式  
(keyword < 'DZ' AND TRUE)  
OR  
(keyword < 'Alo7' AND keyword IS NOT NULL AND TRUE)  
OR  
(FALSE)

\--- 2. 删除非必要TRUE/FALSE  
(keyword < 'DZ')  
OR  
(keyword < 'Alo7' )

\-- 合并条件  
(keyword < 'DZ')  
再举个例子: 存在如下两个sql:

select count(\*)  
from room m  
where  
m.scheduled\_time <= '2019-09-16 21:10:41.775'  
and DATE\_ADD(m.scheduled\_time, INTERVAL m.scheduled\_duration + 15 MINUTE) > '2019-09-16 21:10:41.775' ;

\-- expalin 结果  
\# id, select\_type, table, partitions, type, key, key\_len,ref, rows, filtered, Extra  
\# 1, SIMPLE, m, , range, idx\_on\_room\_scheduled\_time\_and\_expected\_end\_time, 5, , 3157, 100.00, Using index condition; Using where

select count(\*)  
from room m  
where  
m.scheduled\_time <= '2018-09-16 21:10:41.775'  
and m.scheduled\_time > DATE\_ADD('2018-09-16 21:10:41.775', INTERVAL -(50 + 15) MINUTE) ;

\-- expalin 结果  
\# id, select\_type, table, partitions, type, key, key\_len, ref, rows, filtered, Extra  
\# 1, SIMPLE, m, , range, idx\_on\_room\_scheduled\_time\_and\_expected\_end\_time, 5, , 3 , 100.00, Using where; Using index

\-- 问题: 两个SQL都是用了 idx\_on\_room\_scheduled\_time\_and\_expected\_end\_time 索引, 而且 使用索引长度均为: key\_len 但是 评估rows缺相差许多(3157 : 3)  
\-- 原因: 前者range范围为: m.scheduled\_time <= '2018-09-16 21:10:41.775'  
\-- 后者range范围为: DATE\_ADD('2018-09-16 21:10:41.775', INTERVAL -(50 + 15) MINUTE) < m.scheduled\_time <= '2018-09-16 21:10:41.775'  
\-- 另外: 第一个语句explain中的 Using index condition 说明他用到了 Index Condition Pushdown(ICP) 优化.

针对第一个sql可能的优化方案如下:

如果我们能在(model层)配置中得到scheduled\_duration的最大值那就能极快的改写成第二个语句;  
使用虚拟列(虚拟列最后介绍)  
改写成如下的语句并补充scheduled\_duration索引来达到同样效果  
select SQL\_NO\_CACHE \*  
from room m  
where  
m.scheduled\_time <= '2018-09-16 21:10:41.775'  
and m.scheduled\_time > DATE\_ADD(  
'2018-09-16 21:10:41.775' ,  
INTERVAL - (select max(scheduled\_duration) from room) - 15 MINUTE)

\-- 在scheduled\_duration存在索引的情况下 explain 会如下表示:  
\-- id, select\_type, table, type, key, key\_len, ref, rows, filtered, Extra  
\-- 1 PRIMARY m range idx\_on\_room\_scheduled\_time\_and\_expected\_end\_time 5 3 100.00 Using index condition; Using where  
\-- 2 SUBQUERY Select tables optimized away  
\-- 其中Select tables optimized away 表示读一次索引即可得到结果(min, max 等聚合函数优化)  
反正不要对索引列使用函数 不要对索引列使用函数 不要对索引列使用函数

Many-Valued Comparisons: index drive, index statistics  
index drive： 依据索引进行精确评估  
index statistics： 依据统计信息进行粗略评估  
两个参数受： eq\_range\_index\_dive\_limit 变量影响。  
存在如下数据：

\-- 存在如下索引  
show index in exercise\_answers;  
/\*  
exercise\_answers 0 PRIMARY 1 id A 1125424848 BTREE  
exercise\_answers 1 index\_exercise\_answers\_on\_passport\_id\_and\_exercise\_id 1 passport\_id A 7604221 YES BTREE  
exercise\_answers 1 index\_exercise\_answers\_on\_passport\_id\_and\_exercise\_id 2 exercise\_id A 1125424848 YES BTREE  
exercise\_answers 1 index\_exercise\_answers\_on\_owner\_type\_and\_owner\_id 1 owner\_type A 3390 YES BTREE  
exercise\_answers 1 index\_exercise\_answers\_on\_owner\_type\_and\_owner\_id 2 owner\_id A 112542484 YES BTREE  
\*/

\-- 第一组索引数据个数  
select count(\*) from exercise\_answers  
where passport\_id = '99987175' and exercise\_id in (460240,460241,460242,460243,460244,460245,460246,460247,460248,460249,460250,460251,460252,460253,460254,460255,460256,460257,460258,460261,460262,460263,460264,460266,460267,460268,460269,460270,460271,460272,460273,460274,460275,460276,460277,460278,460279,460280,460281,460282,460283,460284,460285,460286,460287,460288,460294,460295,460296,460297,460298,460329,460330,460331,460332,460333,460334,460335,460336,460337,460338,460339,460340,460341,460342,460343,460344,460345,460346,460347,460348,460349,460350,460351,460352,460353,460354,460355,460356,460357,460358,460359,460360,460361,460362,460363,460364,460365,460366,460367,460368,460369,460370,460371,460372,460389,460390,460391,460392,460393,460395,460396,460397,460398,460399,460404,460405,460406,460407,460408,460409,460410,460411,460412,460413,460416,460417,460418,460419,460420,460421,460422,460423,460424,460425,460426,460427,460428,460429,460430,460431,460432,460433,460434,460435,460444,460445,460446,460447,460448,460449,460450,460451,460452,460453,460454);  
\-- > 200

\-- 第二组索引数据个数  
select count(\*) from exercise\_answers  
where owner\_type='Homework' and owner\_id in (3199716,2471583);  
\-- > 1157

\-- 评估如下查询：使用哪个索引？  
select sql\_no\_cache count(\*) c from exercise\_answers  
where passport\_id = '99987175' and owner\_type='Homework'  
and exercise\_id in (460240,460241,460242,460243,460244,460245,460246,460247,460248,460249,460250,460251,460252,460253,460254,460255,460256,460257,460258,460261,460262,460263,460264,460266,460267,460268,460269,460270,460271,460272,460273,460274,460275,460276,460277,460278,460279,460280,460281,460282,460283,460284,460285,460286,460287,460288,460294,460295,460296,460297,460298,460329,460330,460331,460332,460333,460334,460335,460336,460337,460338,460339,460340,460341,460342,460343,460344,460345,460346,460347,460348,460349,460350,460351,460352,460353,460354,460355,460356,460357,460358,460359,460360,460361,460362,460363,460364,460365,460366,460367,460368,460369,460370,460371,460372,460389,460390,460391,460392,460393,460395,460396,460397,460398,460399,460404,460405,460406,460407,460408,460409,460410,460411,460412,460413,460416,460417,460418,460419,460420,460421,460422,460423,460424,460425,460426,460427,460428,460429,460430,460431,460432,460433,460434,460435,460444,460445,460446,460447,460448,460449,460450,460451,460452,460453,460454)  
and owner\_id in (3199716,2471583)  
Explain 对比：

\-- 使用 index stat  
set eq\_range\_index\_dive\_limit=1;  
\-- explain 结果（请注意使用的 index）  
/\*  
\{  
"query\_block": \{  
"select\_id": 1,  
"table": \{  
"table\_name": "exercise\_answers",  
"access\_type": "range",  
"possible\_keys": \[  
"index\_exercise\_answers\_on\_passport\_id\_and\_exercise\_id",  
"index\_exercise\_answers\_on\_owner\_type\_and\_owner\_id"  
\],  
"key": "index\_exercise\_answers\_on\_owner\_type\_and\_owner\_id",  
"used\_key\_parts": \[  
"owner\_type",  
"owner\_id"  
\],  
"key\_length": "773",  
"rows": 20,  
"filtered": 75,  
"index\_condition": "((\`saybot\_vw\`.\`exercise\_answers\`.\`owner\_type\` = 'Homework') and (\`saybot\_vw\`.\`exercise\_answers\`.\`owner\_id\` in (3199716,2471583)))",  
"attached\_condition": "((\`saybot\_vw\`.\`exercise\_answers\`.\`passport\_id\` = '99987175') and (\`saybot\_vw\`.\`exercise\_answers\`.\`exercise\_id\` in (460240,460241,460242,460243,460244,460245,460246,460247,460248,460249,460250,460251,460252,460253,460254,460255,460256,460257,460258,460261,460262,460263,460264,460266,460267,460268,460269,460270,460271,460272,460273,460274,460275,460276,460277,460278,460279,460280,460281,460282,460283,460284,460285,460286,460287,460288,460294,460295,460296,460297,460298,460329,460330,460331,460332,460333,460334,460335,460336,460337,460338,460339,460340,460341,460342,460343,460344,460345,460346,460347,460348,460349,460350,460351,460352,460353,460354,460355,460356,460357,460358,460359,460360,460361,460362,460363,460364,460365,460366,460367,460368,460369,460370,460371,460372,460389,460390,460391,460392,460393,460395,460396,460397,460398,460399,460404,460405,460406,460407,460408,460409,460410,460411,460412,460413,460416,460417,460418,460419,460420,460421,460422,460423,460424,460425,460426,460427,460428,460429,460430,460431,460432,460433,460434,460435,460444,460445,460446,460447,460448,460449,460450,460451,460452,460453,460454)))"  
\}  
\}  
\}\*/

\-- 使用 index dive  
set eq\_range\_index\_dive\_limit=3;  
\-- explain 结果  
/\*  
\{  
"query\_block": \{  
"select\_id": 1,  
"table": \{  
"table\_name": "exercise\_answers",  
"access\_type": "range",  
"possible\_keys": \[  
"index\_exercise\_answers\_on\_passport\_id\_and\_exercise\_id",  
"index\_exercise\_answers\_on\_owner\_type\_and\_owner\_id"  
\],  
"key": "index\_exercise\_answers\_on\_passport\_id\_and\_exercise\_id",  
"used\_key\_parts": \[  
"passport\_id",  
"exercise\_id"  
\],  
"key\_length": "773",  
"rows": 200,  
"filtered": 100,  
"index\_condition": "((\`saybot\_vw\`.\`exercise\_answers\`.\`passport\_id\` = '99987175') and (\`saybot\_vw\`.\`exercise\_answers\`.\`exercise\_id\` in (460240,460241,460242,460243,460244,460245,460246,460247,460248,460249,460250,460251,460252,460253,460254,460255,460256,460257,460258,460261,460262,460263,460264,460266,460267,460268,460269,460270,460271,460272,460273,460274,460275,460276,460277,460278,460279,460280,460281,460282,460283,460284,460285,460286,460287,460288,460294,460295,460296,460297,460298,460329,460330,460331,460332,460333,460334,460335,460336,460337,460338,460339,460340,460341,460342,460343,460344,460345,460346,460347,460348,460349,460350,460351,460352,460353,460354,460355,460356,460357,460358,460359,460360,460361,460362,460363,460364,460365,460366,460367,460368,460369,460370,460371,460372,460389,460390,460391,460392,460393,460395,460396,460397,460398,460399,460404,460405,460406,460407,460408,460409,460410,460411,460412,460413,460416,460417,460418,460419,460420,460421,460422,460423,460424,460425,460426,460427,460428,460429,460430,460431,460432,460433,460434,460435,460444,460445,460446,460447,460448,460449,460450,460451,460452,460453,460454)))",  
"using\_MRR": true,  
"attached\_condition": "((\`saybot\_vw\`.\`exercise\_answers\`.\`owner\_type\` = 'Homework') and (\`saybot\_vw\`.\`exercise\_answers\`.\`owner\_id\` in (3199716,2471583)))"  
\}  
\}  
\}  
\*/  
Profiling 对比:

status index dive index stat 备注  
Sending data 0.000825 0.002600 stat 处理了更多的数据  
statistics 0.000231 0.000127 stat 分析时间更短  
OPTIMIZER\_TRACE:

//use index stat  
\{  
"analyzing\_range\_alternatives": \{  
"range\_scan\_alternatives": \[  
\{  
"index": "index\_exercise\_answers\_on\_passport\_id\_and\_exercise\_id",  
"ranges": \[  
"99987175 <= passport\_id <= 99987175 AND 460240 <= exercise\_id <= 460240",  
"99987175 <= passport\_id <= 99987175 AND 460241 <= exercise\_id <= 460241",  
"......",  
"99987175 <= passport\_id <= 99987175 AND 460454 <= exercise\_id <= 460454"  
\],  
"index\_dives\_for\_eq\_ranges": false, // do not use dive  
"rowid\_ordered": false,  
"using\_mrr": true,  
"index\_only": false,  
"rows": 146,  
"cost": 199.88,  
"chosen": true  
\},  
\{  
"index": "index\_exercise\_answers\_on\_owner\_type\_and\_owner\_id",  
"ranges": \[  
"Homework <= owner\_type <= Homework AND 2471583 <= owner\_id <= 2471583",  
"Homework <= owner\_type <= Homework AND 3199716 <= owner\_id <= 3199716"  
\],  
"index\_dives\_for\_eq\_ranges": false,  
"rowid\_ordered": false,  
"using\_mrr": false,  
"index\_only": false,  
"rows": 20,  
"cost": 26.01, //cost 26 < 199  
"chosen": true  
\}  
\],  
"analyzing\_roworder\_intersect": \{  
"usable": false,  
"cause": "too\_few\_roworder\_scans"  
\}  
\}  
\}  
// use index dive; eq\_range\_index\_dive\_limit=3  
\{  
"analyzing\_range\_alternatives": \{  
"range\_scan\_alternatives": \[  
\{  
"index": "index\_exercise\_answers\_on\_passport\_id\_and\_exercise\_id",  
"ranges": \[  
"99987175 <= passport\_id <= 99987175 AND 460240 <= exercise\_id <= 460240",  
"99987175 <= passport\_id <= 99987175 AND 460241 <= exercise\_id <= 460241",  
"......",  
"99987175 <= passport\_id <= 99987175 AND 460454 <= exercise\_id <= 460454"  
\],  
"index\_dives\_for\_eq\_ranges": false, // not use  
"rowid\_ordered": false,  
"using\_mrr": true,  
"index\_only": false,  
"rows": 146,  
"cost": 199.88,  
"chosen": true  
\},  
\{  
"index": "index\_exercise\_answers\_on\_owner\_type\_and\_owner\_id",  
"ranges": \[  
"Homework <= owner\_type <= Homework AND 2471583 <= owner\_id <= 2471583",  
"Homework <= owner\_type <= Homework AND 3199716 <= owner\_id <= 3199716"  
\],  
"index\_dives\_for\_eq\_ranges": true, // use  
"rowid\_ordered": false,  
"using\_mrr": false,  
"index\_only": false,  
"rows": 1155,  
"cost": 1388, // 1388 > 199  
"chosen": false,  
"cause": "cost"  
\}  
\],  
"analyzing\_roworder\_intersect": \{  
"usable": false,  
"cause": "too\_few\_roworder\_scans"  
\}  
\}  
\}  
// use index dive; eq\_range\_index\_dive\_limit=300  
\{  
"analyzing\_range\_alternatives": \{  
"range\_scan\_alternatives": \[  
\{  
"index": "index\_exercise\_answers\_on\_passport\_id\_and\_exercise\_id",  
"ranges": \[  
"99987175 <= passport\_id <= 99987175 AND 460240 <= exercise\_id <= 460240",  
"......",  
"99987175 <= passport\_id <= 99987175 AND 460454 <= exercise\_id <= 460454"  
\],  
"index\_dives\_for\_eq\_ranges": true, // use  
"rowid\_ordered": false,  
"using\_mrr": true,  
"index\_only": false,  
"rows": 200, // rows 变得准确  
"cost": 274.38,  
"chosen": true  
\},  
\{  
"index": "index\_exercise\_answers\_on\_owner\_type\_and\_owner\_id",  
"ranges": \[  
"Homework <= owner\_type <= Homework AND 2471583 <= owner\_id <= 2471583",  
"Homework <= owner\_type <= Homework AND 3199716 <= owner\_id <= 3199716"  
\],  
"index\_dives\_for\_eq\_ranges": true,  
"rowid\_ordered": false,  
"using\_mrr": false,  
"index\_only": false,  
"rows": 1155,  
"cost": 1388,  
"chosen": false,  
"cause": "cost"  
\}  
\],  
"analyzing\_roworder\_intersect": \{  
"usable": false,  
"cause": "too\_few\_roworder\_scans"  
\}  
\}  
\}  
所以: 注意 in 条件个数不要过多, 过多的in条件会使用 index stat 来生成执行计划, 如果数据分布不均, 会导致错误的选择Index

下面这些优化都是特例优化. 在设计时不应该过多考虑.

NULL 优化:  
官方建议如果可能尽可能声明列为NOT NULL; 如果有需求使用NULL值, 请尽情使用. 文档地址

所以建议: 尽可能减少 NULL 列, NULL 会增加索引扫描和条件对比的复杂度

Index Merge:  
在特定情况下一个表可以使用多个索引. 依次介绍每一种方式 couse表存在 idx(category\_id), pk(id)

Index Merge Intersection Access Algorithm: 对多个索引同时扫描并对结果取交集  
select \* from course where category\_id = 10 and id < 10;  
\-- explain  
\# id, select\_type, table, type, key, key\_len, rows, Extra  
\# 1, SIMPLE, course, index\_merge, category\_id\_idx,PRIMARY, 9,4, , 1, Using intersect(category\_id\_idx,PRIMARY); Using where  
另外如果发生了这种优化, 建议使用复合索引. 具体参考文档

Index Merge Union Access Algorithm: 对多个索引同时扫描并对结果取并集  
select \* from course where category\_id = 10 or id < 10;  
\-- explain  
\# id, select\_type, table, type, key, key\_len, rows, Extra  
\# 1, SIMPLE, course, index\_merge, category\_id\_idx,PRIMARY, 5,4, , 12, Using union(category\_id\_idx,PRIMARY); Using where  
Index Merge Sort-Union Access Algorithm 对多个索引同时扫描并对结果取并集  
select \* from course where category\_id < 10 or id < 10;  
\-- explain  
\# id, select\_type, table, type, key, key\_len, rows, Extra  
\# 1, SIMPLE, course, index\_merge, category\_id\_idx,PRIMARY, 5,4, , 12, Using union(category\_id\_idx,PRIMARY); Using where

\-- 这个相比union: category\_id < 10 只能使用到 category\_id 这个一部分 , 而 category\_id = 10 使用到 category\_id, id 两个部分.  
所以请仔细思考会进行range查询的列在索引中的位置

函数调用优化:  
函数分为: 稳定(deterministic)非稳定(nondeterministic)两类函数. 非稳定函数会影响索引使用

select scheduled\_time from room where scheduled\_time > now() + INTERVAL 10 DAY;  
\-- explain  
\# id, select\_type, table, type, key, key\_len, ref, rows, filtered, Extra  
\# 1, SIMPLE, room, range, idx\_on\_room\_scheduled\_time\_and\_expected\_end\_time, 5, , 126263, 100.00, Using where; Using index

select scheduled\_time from room where scheduled\_time > now() + INTERVAL floor(rand()\* 10) DAY;  
\# id, select\_type, table, type, key, key\_len, ref, rows, filtered, Extra  
\# 1, SIMPLE, room, index, idx\_on\_room\_scheduled\_time\_and\_expected\_end\_time, 10, , 252527, 33.33, Using where; Using index

\-- 前者range 后者index  
所以: 请注意 不稳定函数使用

子查询优化  
type 优化类型 备注  
in/any Semijoin    
  Materialization    
  EXISTS strategy    
not in Materialization    
  EXISTS strategy    
derived table/views Merge the derived table into the outer query block 从5.7开始支持views的优化(下同) <br >5.6,5.7虽然都叫这个名字但是行为相差很大  
  Materialize the derived table to an internal temporary table    
SemiJoin  
简单说: 两张表关系为1:n, 结果只需要1中的内容, n中的内容仅仅用来过滤.

举个栗子: 问: 查询存在学生的班级?

SELECT SQL\_NO\_CACHE  
count(DISTINCT clazz.id)  
FROM  
clazz  
INNER JOIN  
clazz\_student ON clazz.id = clazz\_id;

\-- 平均耗时1s  
稍稍优化下(5.7 及以下):

SELECT SQL\_NO\_CACHE  
COUNT(id)  
FROM  
clazz c  
WHERE  
exists (SELECT  
1  
FROM  
clazz\_student  
WHERE  
clazz\_id = c.id);  
\-- 平均耗时0.55s  
由于 clazz 和 clazz\_student 是 1:n 在优化下:

SELECT SQL\_NO\_CACHE  
COUNT(id)  
FROM  
clazz c  
WHERE  
id IN (SELECT  
clazz\_id  
FROM  
clazz\_student  
WHERE  
clazz\_id = c.id)  
;  
\-- 平均耗时0.45s (单个查询优化了 55%)

\-- explain后看下show warnings  
/\* select\#1 \*/  
select  
sql\_no\_cache count(\`saybot\_vw\`.\`c\`.\`id\`) AS \`COUNT(id)\`  
from  
\`saybot\_vw\`.\`clazz\` \`c\`  
semi join // first match  
(\`saybot\_vw\`.\`clazz\_student\`) // 子查询上拉  
where (\`saybot\_vw\`.\`clazz\_student\`.\`clazz\_id\` = \`saybot\_vw\`.\`c\`.\`id\`)

子查询另外的策略:

\-- looseScan  
SELECT SQL\_NO\_CACHE  
COUNT(id)  
FROM  
clazz\_course cu  
WHERE  
clazz\_id IN (SELECT  
clazz\_id  
FROM  
clazz\_student  
WHERE  
clazz\_id < 20);  
/\*  
table, rows, extra  
clazz\_student, 1 , Using where; Using index; LooseScan  
c, , 1, Using index

解释:  
1. 遍历 cs 中每一组, 注意是组取到 cls\_id  
2. 去 cls 表取 数据.

与 firstmatch 的区别: 前者 是 cls 表为驱动表, 和改过程相反  
\*/

\-- duplicate weedout  
SELECT SQL\_NO\_CACHE  
COUNT(id)  
FROM  
clazz c  
WHERE  
id IN (SELECT  
clazz\_id  
FROM  
clazz\_student  
WHERE  
clazz\_id = c.id and id < 100);

/\*  
table, rows, extra  
clazz\_student, 1 , Using where; Start temporary  
c, , 1, Using index; End temporary

解释:  
1. 创建临时表 T  
2. 遍历 cls\_stu 中没一行  
3. 取到 C 中的记录  
4. 临时表去重  
\*/  
5.7 新特性  
Generated Column  
官网的例子

mysql> CREATE TABLE jemp (  
\-> c JSON,  
\-> g INT GENERATED ALWAYS AS (c->"$.id"),  
\-> INDEX i (g)  
\-> );  
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES  
> ('\{"id": "1", "name": "Fred"\}'), ('\{"id": "2", "name": "Wilma"\}'),  
> ('\{"id": "3", "name": "Barney"\}'), ('\{"id": "4", "name": "Betty"\}');  
Query OK, 4 rows affected (0.04 sec)  
Records: 4 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT c->>"$.name" AS name  
> FROM jemp WHERE g > 2\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: SIMPLE  
table: jemp  
partitions: NULL  
type: range  
possible\_keys: i  
key: i  
key\_len: 5  
ref: NULL  
rows: 2  
filtered: 100.00  
Extra: Using where  
1 row in set, 1 warning (0.00 sec)

派生表(Derived Tables)  
举个栗子:

SELECT \*  
FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived\_t2 ON t1.f2=derived\_t2.f1  
WHERE t1.f1 > 0;

SELECT t1.\*, t2.f1  
FROM t1 JOIN t2 ON t1.f2=t2.f1  
WHERE t1.f1 > 0;  
禁用方式(在子查中使用):

聚合函数  
distinct  
Group By  
HAVING  
Limit  
UNION  
…..  
索引  
B+tree (以clustered index来讲)  
图片依次盗自: 图1 图2

所以: 建议使用自增列作为主键 以减少索引分裂,重排的情况.

另外 innodb 默认一个 index page 为16kb. 以int为主键的列单page可以放入记录: 4k; 以bigint为主键可以放入记录 2k (实际单page只能使用容量的\[1/2-5/6\])

所以: 个人建议能使用更小的数据类型就用更小的, 尤其是主键, 而且当主键设置为bigint的表应该在早期就设计分表(该条不在规范里)

Hash  
图片依次盗自: 图1

hash index 只能使用在memory engine(重启清空, 不支持事务, 只有表锁); hash index 擅长等值查询(=, <=>, …); btree 支持 range 查询(>, >=, bwteen…)

推荐:  
数据库内核月报  
digoal/blog: Everything about database,bussiness.(Most for PostgreSQL).  
MySQL 官方文档  
未完成  
其他  
时区配置  
字符编码  
use index vs rs sorted key  
group/order/distinct 优化:  
group by  
order by null： sql 仅有 group by 语句时， 默认按照 groug by 字段顺序进行排序。 在很多情况下 并不需要这个行为。 order by null 可以忽略排序  
explain  
SELECT  
clazz\_id, student\_id, count(\*)  
FROM  
homeworks h  
inner join  
homework\_results hr on hr.homework\_id = h.id  
where h.id > 3398853  
group by clazz\_id, student\_id  
limit 2000;

/\*  
\# id, select\_type, table, type, possible\_keys, key, key\_len, ref, rows, Extra  
1, SIMPLE, hr, range, index\_hw\_id\_and\_hr\_id,index\_homework\_results\_on\_homework\_id, index\_hw\_id\_and\_hr\_id, 5, , 883, Using where; Using index; Using temporary; Using filesort  
1, SIMPLE, h, eq\_ref, PRIMARY, PRIMARY, 4, saybot\_vw.hr.homework\_id, 1,  
\*/  
\-- >>> 0.053

explain  
SELECT  
clazz\_id, student\_id, count(\*)  
FROM  
homeworks h  
inner join  
homework\_results hr on hr.homework\_id = h.id  
where h.id > 3398853  
group by clazz\_id, student\_id  
order by null  
limit 2000;  
/\*  
\# id, select\_type, table, type, possible\_keys, key, key\_len, ref, rows, Extra  
1, SIMPLE, hr, range, index\_hw\_id\_and\_hr\_id,index\_homework\_results\_on\_homework\_id, index\_hw\_id\_and\_hr\_id, 5, , 883, Using where; Using index; Using temporary  
1, SIMPLE, h, eq\_ref, PRIMARY, PRIMARY, 4, saybot\_vw.hr.homework\_id, 1,  
\*/

distinct 可以视为 group by 的一种特例， distinct 的优化参考 group by

著作权归作者所有。  
商业转载请联系作者获得授权,非商业转载请注明出处。  
原文: https://blog.calvin.wang/2019/09/16/mysql-optimizer.html?hmsr=toutiao.io&utm\_medium=toutiao.io&utm\_source=toutiao.io

转载于:https://www.cnblogs.com/linwenbin/p/11576333.html

[https_blog.calvin.wang_2019_09_16_mysql-optimizer.html_hmsr_toutiao.io_utm_medium_toutiao.io_utm_source_toutiao.io]: https://blog.calvin.wang/2019/09/16/mysql-optimizer.html?hmsr=toutiao.io&utm_medium=toutiao.io&utm_source=toutiao.io