分析步骤：
- 慢查询日志与捕获
- explain+慢查询日志分析
- show profile查询sql在mysql服务器中的执行细节和生命周期
- sql服务器参数调优
一、explain
explain用于分析查询语句的性能.
包含字段：id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
字段解释
-
id
表示表的读取顺序
若id相同，执行顺序从上到下
若id不同，执行顺序从大id到小id -
select_type
表示查询类型
simple：普通查询
primary：子查寻中最外层
subquery：select，where包含的子查询
derived：from后面的子查询 -
table
-
type
system>const>eq_ref>ref>range>index>all
image.png
-
possible_keys
该字段和key一起用来查看哪个索引可能被使用 -
key
-
key_len
查询索引使用的字节数，长度越短越好 -
ref
说明索引实际使用了哪些值，即where等号右值 -
rows
有多少行被查询过 -
Extra
- using filesort ：索引的排序未用到，使用文件排序（坏🌟🌟）
- using temperory：产生了新的临时表，常见于order，group没利用索引（坏🌟🌟🌟）
- using index：使用了索引（好）
- using where：使用了索引去约束（好）
二、查询优化
2.1 小表驱动大表
原则：外层循环要是小数据
驱动方式 ：in exit
若B数据集小，则使用in
select * from A where id in (select id from b)
等价于
for select B
for select A ...where b.id=a.id
若A数据集小，则使用exit
select * from A where exits (select 1 from b where b.id=a.id);
等价于
for select A
for select B...where b.id=a.id
2.2 where优化（其中5是select优化）
-
全值匹配是最优的查询（指where后条件都有索引去匹配）
-
要遵循最佳左前缀法则『法则指的是---查询时，索引最前面的大哥不能无，中间也不能跳过』
-
不要在索引列上做任何操作（计算、函数、（自动，手动）类型转换），会导致索引失效而转向全表扫描
-
where中，范围条件右边的查找列会失效，索引用不到他，因此要注意这点
-
；尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），因此要减少select*『目的是使用到using index』
-
mysq|在使用不等于(!：或者<>）的时候无法使用索引会导致全表扫扫描
-
is null,is not null也无法使用索引
-
like以通配符开头('%abc...')mysql*'引失效会变成全表扫描。右侧单百分号会稍好点。解决方法：使用覆盖索引
-
字符串不加单引号索引失效
-
少用or，用它来连接时会索引失效
带头大哥不能死，中间兄弟不能断，索引列上无计算，like百分加右边，范围之后全失效（包含like in等），字符串里有引号
顺序不按索引的order by 会导致using filesort，group by同理，分组前必排序。
案例
image.png
2.3 order by优化 group by与他相同
- 尽量在索引列上排序，使用索引要想避免using filesort 就要遵循最佳左前缀
- 尽量不要使用select *
当查询字段量小于max_length_for_sort_data,会使用单路复用，否则多路降低性能 - 优化filesort
filesort分为双路排序（两次io，旧，慢）和单路排序，但有可能数据量大于单路缓冲区
优化方法
增加sort_buffer_size、max_length_for_sort_data大小
order by a ASE ,b DESC 不一致 无法使用索引
若最左前缀是常量，则直接order by 第二个 可以使用索引 即：where a=const order by b
image.png
三、慢查询日志
日志默认关闭，开启会影响性能
set global_slow_query_log=1;开启 重启后失效
set global_long_query_log=xxx; >xxx秒被记录
3.1 分析工具 mysqldumpslow
四、show profile
查看资源消耗情况
show profiles
show profile cpu，block io for query xx
危险状态
- converting heap to myisam
- create tmp table
- copying to tmp table on disk
- lock
网友评论