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