work hard work smart

专注于Java后端开发。 不断总结,举一反三。
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

如何调优SQL

Posted on 2020-01-25 19:57  work hard work smart  阅读(178)  评论(0编辑  收藏  举报

以MySQL为例

如何定位并优化慢查询Sql?

联合索引的最左匹配原则的成因?

索引是建立得越多越好吗?

 

一、如何定位并优化慢查询Sql?

根据慢日志定位慢查询sql

使用explain等工具分析sql

修改sql或者尽量让sql走索引

 

查看mysql变量

SHOW VARIABLES LIKE '%quer%'

 

 

 

显示慢查询的数量

SHOW STATUS LIKE '%slow_queries%'

 

 

打开慢查询日志

SET GLOBAL slow_query_log = ON;

 

设置慢查询时间为1秒

SET GLOBAL long_query_time = 1

然后重新连接才能生效

 

慢查询实践

如下面的查询语句,查过了1秒

 

 

然后打开慢查询日志

 

 可以发现执行了6秒,实际上就是6秒。

 

然后使用explain

 

explain的关键字段

当type为index或者All时,表示走的是全表扫描,这条sql是慢查询,需要优化

 

extra字段

extra中出现以下2项意味着MYSQL根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化

 

 

解决方法:

修改sql或者尽量让sql走索引。

 

二、联合索引的最左匹配原则的成因?

什么是联合索引,就是由多列组成的索引

如下图:area和title组成联合索引

 

如下的查询语句。查询where为area和title,走的是index_area_title索引

 

 

如下的查询语句。查询where为area,走的依然是index_area_title索引

 

 如下的查询语句。查询where为title,不走索引,而是全部扫描

 

 以上现象称为最左匹配。

 

 

1、最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between, like) 就停止匹配,如a = 3 and b=4 and c > 5 and d = 6

如果建立(a,b,c,d)顺序索引,d是用不到的所有。如果建立(a,b,d,c)索引则都可以用到, a,b,d的顺序可以任意调整。

2、 =和in可以乱序,比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

 

MYSQL  创建联合索引强调最左匹配的原因?

MYSQL  创建联合索引时,先对第一个字段进行排序的基础上,再对第二个字段进行排序。相当于实现了Order by字段1,再Order by字段2这样一种排序规则。所以第一个字段是绝对有序的,而第二个字段是无序的。因此使用第二个字段,是不需要索引的。

如下图联合索引(col3,col2)

 

 

索引是建立得越多越好吗?

答案是否定的。

数据量小的表不需要建立索引,建立会增加额外的索引开销。

数据变更需要维护索引,因此更多的索引意味着更多的维护成本。

更多的索引意味着也需要更多的空间。