查询截取分析与优化

分析步骤：

• 慢查询日志与捕获
• 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优化）

1. 全值匹配是最优的查询（指where后条件都有索引去匹配）

2. 要遵循最佳左前缀法则『法则指的是---查询时，索引最前面的大哥不能无，中间也不能跳过』

3. 不要在索引列上做任何操作（计算、函数、（自动，手动）类型转换），会导致索引失效而转向全表扫描

4. where中，范围条件右边的查找列会失效，索引用不到他，因此要注意这点

5. ；尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），因此要减少select*『目的是使用到using index』

6. mysq|在使用不等于(!：或者<>）的时候无法使用索引会导致全表扫扫描

7. is null,is not null也无法使用索引

8. like以通配符开头('%abc...')mysql*'引失效会变成全表扫描。右侧单百分号会稍好点。解决方法：使用覆盖索引

9. 字符串不加单引号索引失效

10. 少用or，用它来连接时会索引失效

带头大哥不能死，中间兄弟不能断，索引列上无计算，like百分加右边，范围之后全失效（包含like in等），字符串里有引号

顺序不按索引的order by 会导致using filesort，group by同理，分组前必排序。
案例

image.png

2.3 order by优化 group by与他相同

1. 尽量在索引列上排序，使用索引要想避免using filesort 就要遵循最佳左前缀
2. 尽量不要使用select *
   当查询字段量小于max_length_for_sort_data,会使用单路复用，否则多路降低性能
3. 优化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