SQL性能优化技巧，常见优化方法，一文就让你学会

可以看到第一条SQL中 name = ‘weiwei%’ ;根据 B;tree 的结构可以知道其后的 age,position 列是无序的;应该无法使用 age,position 列过滤数据才对;但是最后的 key_len 确是140;也就意味着 MySQL 利用到了 name,age,postion 三个列来查询;这是因为 MySQL 在5.6版本做的优化;引入了索引下推。

索引下推可以在索引遍历过程中;对索引中包含的所有字段先做判断;过滤掉不符合条件的记录之后再回表;可以有效的减少回表次数。

即当 name = ‘weiwei%’ 过滤时;使用了索引下推优化;过滤 name 时同时还会在索引里过滤 age,position 两个列的条件;拿着过滤完剩下的索引对应的主键 id 再回表查整行数据。

那么很简单;第二条 SQL 没有使用索引的原因是因为 MySQL 计算使用索引下推过滤出数据后;查询二级索引;回表的查询成本大于全表扫描;所以 MySQL 选择全表扫描。

9. 类型不匹配 MySQL 自动转型导致索引失效

字符串不加单引号索引失效;亦或是数值类型加单引号索引失效;也就是第三点中提到的自动类型转换(也叫隐式转换)导致索引失效。

10. 少用 or 或 in ;用它查询时;MySQL 不一定使用索引

MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

二、order by 和 group by 优化

1. order by 优化

在前面的文章中小二给大家介绍了索引的底层数据结构;知道了索引本身就是一种排好序的数据结构;所以排序优化最好的办法就是落实到索引上;这样查询出来的数据就已经排好序了;这种排序在 MySQL 中被称之为 Using Index;即覆盖索引。那么如果查询出来后的数据本身没有按所需字段排序;那么就会出现 Using filesort;即文件排序。

所以;我们要优化 order by;那么主要就是消灭低效的 Using filesort;建立合适的联合索引使用覆盖索引来排序。

Using filesort文件排序原理详解

Using filesort 分为单路排序和双路排序(又叫回表排序模式)。

• 单路排序;一次性取出满足条件行的所有字段;然后在 sort buffer 中进行排序;用 trace 工具可以看到 sort_mode 信息里显示 < sort_key, additional_fields > 或者 < sort_key, packed_additional_fields >
• 双路排序;是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID;然后在 sort buffer 中进行排序;排序完后需要再次取回其它需要的字段;用 trace 工具可以看到sort_mode 信息里显示 < sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

• 如果字段的总长度小于 max_length_for_sort_data ;那么使用单路排序模式
• 如果字段的总长度大于 max_length_for_sort_data ;那么使用双路排序模式

接下来使用 trace 工具来瞅瞅单路排序和双路排序;

-- 单路排序



mysql> set session optimizer_trace=;enabled=on;, end_markers_in_json=on;



 



mysql> select * from employees where name = ;itwxe; order by position;



 



mysql> select * from information_schema.OPTIMIZER_TRACE;



 



;join_execution;: {  -- sql执行阶段



  ;select#;: 1,



  ;steps;: [



    {



      ;filesort_information;: [



        {



          ;direction;: ;asc;,



          ;table;: ;;employees;;,



          ;field;: ;position;



        }



      ] /* filesort_information */,



      ;filesort_priority_queue_optimization;: {



        ;usable;: false,



        ;cause;: ;not applicable (no LIMIT);



      } /* filesort_priority_queue_optimization */,



      ;filesort_execution;: [



      ] /* filesort_execution */,



      ;filesort_summary;: {  -- 文件排序信息



        ;rows;: 1,  -- 预计扫描行数



        ;examined_rows;: 1,  -- 参与排序的行数



        ;number_of_tmp_files;: 0,  -- 使用临时文件的个数;这个值如果为0代表全部使用的sort_buffer内存排序;否则使用的 磁盘文件排序



        ;sort_buffer_size;: 262056,  -- 排序缓存的大小;单位Byte



        ;sort_mode;: ;<sort_key, packed_additional_fields>;  -- 排序方式;这里用的单路排序



      } /* filesort_summary */



    }



  ] /* steps */



} /* join_execution */



 



-- 双路排序



mysql> set max_length_for_sort_data = 10;  -- employees表所有字段长度总和哪怕一条记录也肯定大于10字节



 



mysql> select * from employees where name = ;itwxe; order by position;



 



mysql> select * from information_schema.OPTIMIZER_TRACE;



 



;join_execution;: {



  ;select#;: 1,



  ;steps;: [



    {



      ;filesort_information;: [



        {



          ;direction;: ;asc;,



          ;table;: ;;employees;;,



          ;field;: ;position;



        }



      ] /* filesort_information */,



      ;filesort_priority_queue_optimization;: {



        ;usable;: false,



        ;cause;: ;not applicable (no LIMIT);



      } /* filesort_priority_queue_optimization */,



      ;filesort_execution;: [



      ] /* filesort_execution */,



      ;filesort_summary;: {



        ;rows;: 1,



        ;examined_rows;: 1,



        ;number_of_tmp_files;: 0,



        ;sort_buffer_size;: 262136,



        ;sort_mode;: ;<sort_key, rowid>;  -- 排序方式;这里用的双路排序



      } /* filesort_summary */



    }



  ] /* steps */



} /* join_execution */

select * from employees limit 90000,10;

select * from employees where id > 90000 limit 10;

select * from employees order by name limit 90000,10;



select * from employees ed_all inner join (select id from employees order by name limit 90000,10) ed_id on ed_all.id = ed_id.id;

-- 示例表



CREATE TABLE ;t1; (



  ;id; int(11) NOT NULL AUTO_INCREMENT,



  ;a; int(11) DEFAULT NULL,



  ;b; int(11) DEFAULT NULL,



  PRIMARY KEY (;id;),



  KEY ;idx_a; (;a;)



) ENGINE=InnoDB DEFAULT CHARSET=utf8;



 



create table t2 like t1;



 



-- 往t1表插入1w行测试数据



drop procedure if exists insert_t1;



delimiter $$



create procedure insert_t1()



begin



  declare i int;



  set i = 1;



  while(i <= 10000)do



    insert into t1(a,b) values(i,i);



    set i = i ; 1;                  



  end while;



end$$



delimiter ;



call insert_t1();



 



-- 往t2表插入100行测试数据



drop procedure if exists insert_t2;



delimiter $$



create procedure insert_t2()



begin



  declare i int;



  set i = 1;



  while(i <= 100)do



    insert into t2(a,b) values(i,i);



    set i = i ; 1;                  



  end while;



end$$



delimiter ;



call insert_t2();

explain select * from t1 inner join t2 on t1.a = t2.a;

explain select * from t1 inner join t2 on t1.b = t2.b;

select * from A where id in (select id from B);



# 等价于;



for(select id from B) {



    select * from A where A.id = B.id;



}

select * from A where exists (select 1 from B where B.id = A.id);



# 等价于;



for(select * from A) {



    select 1 from B where B.id = A.id;



}

-- 临时关闭MySQL查询缓存



set global query_cache_size=0;



set global query_cache_type=0;



 



-- count



explain select count(*) from employees;



explain select count(1) from employees;



explain select count(id) from employees;



explain select count(name) from employees;

select count(*) from test_myisam;



explain select count(*) from test_myisam;