MySQL 优化
2023-02-05 21:03:49 19 举报AI智能生成
MySQL 优化 技巧
作者其他创作
大纲/内容
<b><font color="#f44336">EXPLAIN</font></b>
<b><font color="#0000ff">id</font></b>
id列越大，执行优先级越高，id相同则从上往下执行， id为NULL的最后执行
<b><font color="#0000ff">select_type</font></b>
<font color="#ba68c8"><b>simple</b></font>
简单查询
<font color="#ba68c8"><b>primary</b></font>
复杂查询中最外层查询
<font color="#ba68c8"><b>subquery</b></font>
包含在select中的字查询(不在from子句中)
<font color="#ba68c8"><b>derived</b></font>
包含在from子句中的字查询，Mysql会将结果放入临时表(派生表/derived)
<font color="#ba68c8"><b>union</b></font>
在union 中的第二个和随后的select
<b><font color="#0000ff">table </font></b>
表示explan 的这一行正在访问哪个表
当有from子查询时，table列是<derivenN>格式，当前查询依赖id=N的查询，先执行id=N的查询
当union查询时， UNION RESULT 的table 列值为<union1,2>， 1和2 表示参与union的select 行id
<font color="#0000ff"><b>type(性能从上到下变差)</b></font>
<font color="#ba68c8"><b>NULL</b></font>
在优化阶段分解查询语句，执行阶段不需要访问表
<b><font color="#ba68c8">system</font></b>
CONST 的特例，整表只有一条元组匹配时为system。例:【explain select * from (select * from a limit 1) tmp】
<b><font color="#ba68c8">const</font></b>
对查询某部分优化为常量，表最多有一个匹配行，读取一次，速度较快【可以看show warnings】
<b><font color="#ba68c8">eq_ref</font></b>
连接查询时 使用primary key 或 unique key， 最多只会返回一条符合的记录
<b><font color="#ba68c8">ref</font></b>
相比于eq_ref, 不使用唯一索引，使用普通索引或唯一性索引的部分前缀，可能找到多个符合条件的结果
<b><font color="#ba68c8">range</font></b>
范围扫描， in() / between, > , < , >=, <= 等操作中，使用索引来检索范围的行
<b><font color="#ba68c8">index</font></b>
扫描全索引就能拿到结果，一般扫描某个二级索引，不会从索引树根节点开始查找，而是便利叶子结点，速度较慢，通常使用覆盖索引
<b><font color="#ba68c8">ALL</font></b>
全表扫描，扫描聚簇索引的所有叶子结点
<b><font color="#0000ff">possible_key</font></b>
可能使用哪些索引来查找
<b><font color="#0000ff">key</font></b>
实际采用哪个索引来优化， force index(强制使用索引) ignore index(强制忽略索引)
<b><font color="#0000ff">key_len</font></b>
根据指定的列的字符长度计算得到的字节长度， 索引使用的字段字节长度
<b><font color="#0000ff">ref</font></b>
显示了表查找值所用到的列或常量,常见有： const(常量),film.id(字段名)
<font color="#0000ff"><b>rows</b></font>
预估监测行数
<font color="#0000ff"><b>Extra</b></font>
<font color="#ba68c8">Using index</font>
使用覆盖索引
<font color="#ba68c8">Using where</font>
使用where 语句来处理结果，并且查询的列未被索引覆盖
<font color="#ba68c8">Using Index condition</font>
查询的列不完全被索引覆盖， where条件中是一个前导列的范围
<font color="#ba68c8">Using temporary</font>
Mysql 需要创建一张临时表来处理查询
<font color="#ba68c8">Using filesort</font>
将用外部排序而不是索引排序
单路排序
sort buffer内存排序, trace工具可看到<sort_key,additional_fields>或<sort_key,packed_additional_fields>
双路排序
先取出排序字段和行ID, 在 sort buffer 排序后，再取回其他需要字段。trace可看到<sort_key,rowid>
<font color="#ba68c8">Select tables optimized away</font>
使用某些聚合函数(比如：max(),min()) 来访问存在索引的某个字段
<font color="#f44336">索引优化</font>
全值匹配
最左前缀法则
不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)， 例如 字符串不加单引号会导致索引失效
存储引擎不能使用索引中范围条件右边的列，like 可以，5.6之后引入了索引下推【扫描like的时候会扫描后续数据条件】
尽量使用覆盖索引
使用不等于(!= 或 <>), not in, not exist 的时候无法使用索引
is null， is not null 一般也无法使用索引
like 以通配符开头无法使用索引
使用覆盖索引，可以只扫描二级索引即可拿到结果，like %xx% 可以走索引
不能使用覆盖索引则可能需要借助搜索引擎
or 和 in 会根据检索比例，表大小等因素评估是否走索引，不确定
范围过大的查询回表消耗高，可能All 全表扫描， 可以分段查询来走索引提高效率
分页优化- (limit 9000,10)使用子查询去查找对应id,然后直接扫描聚簇索引， 避免limit 回表太多的数据，再丢弃
select * from t1 where id in (select id from t1 limit 9000,10)
<font color="#ff0000"><b>Join 连接优化</b></font>
T1(10000data)/ T2(100data)
小表驱动大表
traight_join 可指定驱动表，只有 inner join 可指定，一般情况不需要调整，myslq自动会优化
嵌套循环连接 <font color="#ba68c8"><b>Nested-Loop Join(NLJ)</b></font> 算法【<font color="#4caf50"><b>连接字段走索引</b></font>】
T2表扫描到 join buffer,再拿join buffer 中的值去扫描T1 的索引树【共扫描200次】
基于块的嵌套循环连接 <font color="#ba68c8"><b>Block Nested-Loop Join(BNL)</b></font> 算法
T2表扫描到join buffer, 把T1 中的每一行去跟 join_buffer 对比，返回满足join条件的数据【扫描 100 次，内存100万次比较判断】
如果join buffer 放不下，则分段依次放入 join buffer 进行比对
<font color="#ff0000"><b>in 和 exists 优化</b></font>
小表驱动大表
in 先子查询，后主查询
exists 先主查询，再过滤exists条件
当B表数据集小于A表时，in优先于exsits
select * from A where id in (select * from B)
当A表数据集小于B表时, exists优先于in
select * from A where exists( select 1 from B where B.id = A.id)
<font color="#ff0000"><b>count() 优化</b></font>
count(字段)不会统计 NULL 值
字段有索引：count(*) =~ count(1) > count(字段) > count(主键id)
字段无索引：count(*) =~ count(1) > count(主键) > count（字段）
count(*) 效率很高
优化方式
自己维护总行数
show table status 【字段 rows】 估计值
Redis 维护总行数
本地事务内维护行数
职业：暂无
0 条评论
下一页
