【mysql优化五】——sql语句优化查询

前言

上篇讲解了索引搜索优化,其实索引只是sql查询优化的一部分,本篇主要讲解的是sql优化主要要优化的部分!

内容

一.order by 优化

  • orderby最好使用index排序方式,避免使用FileSort方式排序;
  • 在索引列上完成排序,遵照索引最佳做前缀
  • orderby最好不要使用select *;
  • 如果使用fileSort方式,尝试提高sort_buffer_size
  • 如果使用fileSort方式,尝试提高max_length_for_sort_data

使用filesort排序优化策略

orderby最好不要使用select *;
提高max_length_for_sort_data
提高sort_buffer_size

【解答】:

1.为什么不用select *?
当query的字段大小总和小于max_length_sort_data,而且排序字段不是text|blob类型,会用改进后的算法单路排序,否则多路排序。

1.双路排序,两次扫描磁盘,最终得到数据;读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的数据从磁盘中取排序的字段,在buffer进行排序,再从磁盘中去其他字段(mysql4.1之前)


2.单路排序,从磁盘读取需要的所有列,按照order by列在buffer对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据,并且报随机I/O变成了顺序I/O,但是它会使用更多的空间。

2.为什么要提高sort_buffer_size?
上诉两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次的i/O操作,但是单路排序的算法风险会更大一些,所以提高sort_buffer_size.
3.如何提高sort_buffer_size和max_length_for_sort_data参数值?

查看本sql的sort_buffer_size;

show variables like '%sort_buffer%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+

其中以后字节为单位的,所以sort_buffer_size=262144/1024=256KB
设置本sql的中sort_buffer_size:

SET GLOBAL sort_buffer_size = 1024*1024; //2MB的buffer大小

3.是不是buffer的大小越大越好?
Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。

参考博客:
http://www.cnblogs.com/wy123/p/7744171.html
https://blog.csdn.net/mydriverc2/article/details/79026337

order by索引方式优化策略

orderby最好不要使用select *;
在索引列上完成排序,遵照索引最佳做前缀


创建复合索引:

create index idx_three on tblA(age,birth,addr);

1.索引最左前缀,orderby 使用索引有效

   select * from tblA  order by age;
   select * from tblA  order by age,birth;
   select * from tblA  order by age, birth,addr;

2.where索引的最左前缀为常量,orderby 使用索引有效

第一种情况最普遍:

 explain select * from tblA where age=24 order by birth,addr;

第二种情况,如果birth使用了范围,但是由于头索引生效了,所以order by使用索引生效。

mysql> explain select * from tblA where age=24 and birth >'2018-07-11 18:09:10' order by birth,addr;
| id | select_type | table | partitions | type  | possible_keys            | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | range | idx_A_ageBirth,idx_three | idx_three | 9       | NULL |    1 |   100.00 | Using where; Using index |

【注意】
三种情况导致索引失效:
1.复合索引头索引不存在;

explain select * from tblA order by  birth,addr;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_three | 100     | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+

1.复合索引中间的索引不存在

explain select * from tblA where age=24 and birth >'2018-07-11 18:09:10' order by addr;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_three | 100     | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+

报告中分析extra指标中存在 using filesort说明这条语句写的糟糕

3.order by中存在了不是复合索引中的字段

explain select * from tblA order by  age,birth,addr,xing1;

4.范围查询也会导致索引失效;

explain select * from tblA where age in (22,24) order by  birth,addr;
+----+-------------+-------+------------+-------+--------------------------+-----------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys            | key       | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+--------------------------+-----------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | range | idx_A_ageBirth,idx_three | idx_three | 5       | NULL |    2 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+--------------------------+-----------+---------+------+------+----------+------------------------------------------+

查看分析中type为range,说明是范围查询,实际用了idx_three,但是依旧存在filesort说明语句还是糟糕。像这种情况我们只能根据需求的不同,创建合适的索引了。
优化原则:

小表驱动大表,小的数据集驱动大的数据集。

【分析】:
子查询结果集必须要比主查询结果集大,因为in和exists本质是一个for循环,内部多次循环可以减少从内部到外部的时间,减少执行的时间。
in和exists语句中,in 后面的语句先执行,所以是外循环,必须要保证in后面的表比in前面的表小;exists前面的语句先执行,所以是外循环,必须要保证前面表的数据集小于后面的表:
这里写图片描述

select * from tb1_emp e where e.deptId in (select id from tb1_dept d);
select * from  tb1_emp e where exists(select 1 from tb1_dept d where d.id=e.deptiD);

二.索引优化

可以参照我之前博客:【mysql学习三】——索引搜索优化

三.group by

  • 本质排序好了进行分组,遵照索引建的最佳前索引
  • 使用filesort方式时,增大max_length_for_sort_data参数设置
  • 使用filesort方式时,增大sort_buffer_size参数设置
  • where高于having,能写where限定条件就不要去写having的限定
    【分析】
    由图中中可以看出来,执行的顺序依次是from,on,(join,where),group by ,having,select ,order by limit;
    这里写图片描述

四.join优化

1,尽可能减少Join 语句中的Nested Loop 的循环总次数,用小结果集驱动大结果集; 注意不是:小表连接大的快,而是结果集 
2,优先优化Nested Loop 的内层循环; 做索引 
3,保证Join 语句中被驱动表上Join 条件字段已经被索引; 
4,扩大join buffer的大小;

五.limit优化

数据量少的时候我们可以使用

Select * from A order by id limit 1,10;

但是数据量大的时候,可以在id上建立一个索引,写成如下的方式:

Select * from A where id>=(Select id from a limit 10000000,1) limit 10;

或者写成如下:

Select * from A where id between 10000000and 10000010;

六.where子句优化

阅读更多

扫码向博主提问

常银玲-Judy

非学,无以致疑;非问,无以广识
  • 擅长领域:
  • Java
  • Angular
去开通我的Chat快问
版权声明:欢迎访问,本文为小编原创文章 https://blog.csdn.net/changyinling520/article/details/81011457
上一篇【mysql学习三】——索引搜索优化
下一篇【mysql学习四】——批量导入sql语句脚本模板
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭