Mysql优化（二）查询优化器

最新推荐文章于 2024-04-14 08:35:10 发布

*豆豆

最新推荐文章于 2024-04-14 08:35:10 发布

阅读量1k

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/tedfdndg/article/details/121659185

版权

一、查询慢的原因

(1) 网络集群部署时，网络IO

(2) CPU 计算

(3) IO

(4) 上下文切换

(5) 系统调用

(6) 生成统计信息，如profile中的信息

(7) 锁等待时间，有表锁和行锁。

MISAM存储引擎中有共享读锁和独占写锁；INNODB有共享锁和排他锁。

MISAM只能锁表，INNODB可以锁表，可以锁行，INNODB锁的对象是索引。

INNODB中，列为索引列，则是行锁，否则是表锁。

其他还有自增锁、间隙锁。

二、索引插曲：

explain select rental_id,staff_id from rental where rental_data > '2006-5-25' order by rental_date,invertory_id;

explain select rental_id,staff_id from rental where rental_data > '2005-5-25' order by rental_date,invertory_id;

当查询出来的数据量很大的时候，就可能不会索引排序，mysql认为使用索引排序效率会低，这种情况没有绝对。书中数据的阈值是30%

三、优化数据访问

(1) 查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，可以通过减少访问数据量的方式进行优化：

Ⅰ确认应用程序是否在检索大量超过需要的数据

explaim select * from rental limit 100000,5; -- 取出5条数据，会全表扫描

优化sql : select * from rental a joion (select rental_id from rental limit 100000,5) b on a.rental_id = b.rental_id -- 使用索引查找出5条的id值，再去join5条记录

Ⅱ 在并行的应用场景中，可能会有很多条sql在查询大量的数据，确认mysql服务器是否在分析大量超过需要的数据行。IO量很大

(2) 是否向数据库请求了不需要的数据，可能的情况如下：

Ⅰ查询不需要的记录

误以为mysql只会返回需要的数据，实际上mysql却是先返回全部结果，再进行计算。

日常开发习惯中，经常先用select语句查询大量的结果，然后获取前面的 N行后关闭结果集。

优化方式是在查询后面添加limit。

Ⅱ 多表关联时返回全部列

select查询时不要使用* 查询全部的列；

查询的表要加别名，磁盘匹配的时候直接匹配别名，不会去表里面进行检索

Ⅲ 总是取出全部列

禁用select *

Ⅳ 重复查询相同的数据

如果需要不断重复执行相同的查询，并且返回相同的数据，则可以把这部分数据缓存起来，可以提高查询效率

四、执行过程的优化

(1) 查询缓存

低版本的mysql（5.x）有查询缓存，如果查询缓存打开，mysql会优先检查这个查询是否命中查询缓存中的数据，如果恰好命中，且用户有权限，则直接从缓存中拿结果。

命中率很低

(2) 查询优化处理

①语法解析器和预处理

mysql通过关键字将SQL语句进行解析，生成一颗解析树，解析器验证语法规则等，预处理也会进一步验证是否合法，表名、列名是否存在，是否有歧义，验证权限等。

②查询优化器

语法树没有问题后，优化器将其转成执行计划，并选择最有效的执行计划。

mysql使用的是基于成本的优化器，选择成本最小的一个。

Ⅰ show status likt ‘last_query_cost’；

查看最后一条sql大概需要的数据页数量才能找到对应的结果，这是经过一系列统计计算得来的。

Ⅱ 在很多情况下mysql会选择错误的执行计划，原因如下：

统计信息不准确：Innodb因为其是mvcc结构，并不能维护一个数据表行数的精确统计信息。

执行计划的成本预估不等同于实际执行的成本

mysql的最优可能跟你想的不一样：mysql是基于成本的优化，可能不是最快。

mysql不考虑其他并发执行的查询。

mysql不考虑不受其控制的操作成本：比如执行存储过程或用户自定义函数的成本

③ 优化器的优化策略

Ⅰ静态优化 直接对解析树分析优化

Ⅱ 动态优化 根据查询的上下文、取值、索引等进行优化，比如上面二中所写sql，数据量大以后，不选择使用索引排序。

Ⅲ mysql对查询的静态优化只需一次，对动态优化在每次执行时搜需要重新评估。

④ 优化器的优化类型

Ⅰ重新定义关联表的顺序

Ⅱ 将外连接转化为内连接，内连接的效率高于外连接

Ⅲ 使用等价变换规则，简化并规划表达式，比如a>9 and a<11 优化为 a=10

Ⅳ 优化count() min() max()

sql中使用group by 索引列再使用min()等函数，效率会更高

Ⅴ 预估值转换为表达式

当检测到一个表达式可以转换为常数，就会一直把该表达式作为常数处理

Ⅵ 索引覆盖扫描

Ⅶ 子查询优化

mysql在某种情况下可以将子查询转换为一种效率高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入缓存中。

Ⅷ 等值传播

如果两个列的值通过等式关联，那么mysql能够把其中一个列的where条件传递到另一个上：
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

⑤ 关联查询

Ⅰjoin的实现方式原理

Simple Nested-Loop Join nested loop -- 嵌套循环

Index Nested-Loop Join

Block Nested-Loop Join：

Join Buffer会缓存所有参与查新的列而不是只有Join的列；

Join_buffer_size的大小默认时256K，可调整，Mysql5.1.22版本前最大是4G，之后的版本才能在64位操作系统下申请大于4G的Join Buffer。

Block Nested-Loop Join算法需要开启优化器管理配置 optimizer_siwtch,默认是开启；

show variables like '%optimizer_switch%';---- 优化器开关

Ⅱ straight_join 可以指定join多表时，按照join的表顺序，进行读取表信息，

最好不用，mysql会选出最优的执行顺序

⑥ 排序优化 order by

排序的算法：

Ⅰ两次传输排序

第一次数据读取是将需要排序的字段读取出来，进行排序；

第二次是将排好序的结果按照需要去读取数据行；

缺点：效率低，第二次排序是随机IO，成本高。

优点：缓冲区没有多余的列数据，可以容纳更多的行数进行排序操作

Ⅱ 单次传输排序

读取查询所需要的所有列，再根据给定列排序。

此方式只需要一次顺序IO读取所有的数据，无需任何的随机IO；

但会占用大量的存储空间。

show variables like '%max_lehgth_for_sort_data%' ----- 排序缓存，默认是1024

如果需要排序的列的总大小加上orderby的列的大小超过 max_lehgth_for_sort_data，会选择双次排序，反之单次排序。可以设置修改此值。

五、优化特定类型的查询

(1) 优化count()查询

① select count(1) from dept;

select count(*) from dept;

select count(任意字段) from dept;

三种方式的效率相同，没有区别

② Myisam的count函数，只有在没有任何where条件的count(*)才比较快

③ 根据实际情况，可以使用近似值，比如 explain，OLAP应用中，某一列的基数，有一个计算近似值的算法叫hyperloglog

(2) 优化关联查询

① 确保on或者using子句的列上有索引

A和B表使用列C关联，优化器中关联的顺序是B、A，那么只需在A表中建索引，B 中不用，关联顺序的第二个表的相应列上建索引。

② groupby和order by中的表达式只有一个表中的列，这样mysql才能用索引优化此过程

(3) 优化子查询

尽量可能使用关联查询代替

子查询的结果会放入临时表中，再进行后续操作；而关联查询是将最后结果放入临时表 (4) select actor.first_name, actor.last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.actor_id

group by 标识列，查询显示的列可以是其他的任何，但貌似没有什么实际意义

(5) 优化limit分页

limit加偏移量的方法实现，同时加上合适的orderby的子句，效率会高，而且尽可能地使用覆盖索引。

select id, des from film order by title limit 50, 5;

select film.film_id,film.des from film inner join (select from id from film order by title limit 50, 5) as lim using(film_id);

使用explain观察，第二种sql涉及的行数rows 会少。

(6) 优化union查询

应用场景：行转列

case when： select userid, sum(case 'subject' when '语文' then score else 0 end) as '语文',

sum(case 'subject' when '数学' then score else 0 end) as '数学'

from tb_score

group by userid;

IF(): select userid， sum(if('subject' = '语文', score, 0)) as '语文'，

sum(if('subject' = '数学', score, 0)) as '数学'

from tb_score

froup by userid;

没有特殊需求，使用union all，因为union会在临时表上加distinct关键字，操作代价高

(7) 推荐使用用户自定义变量

① 自定义变量的使用

set @one:=1;

set @min_actor:=(select min(actor_id) from actor)

set @last_week:=current_date - interval 1 week

② 自定义变量的限制

无法使用查询缓存；

不能在使用常量或者标识符的地方使用自定义变量，例如表名/列名或者limit子句

用户自定义变量的生命周期是一个连接中有效，不能做连接间通信

不能显示地声明自定义变量地类型

mysql优化器在某些场景下可能会将这些变量优化掉，可能导致代码不按预想的方式运行

赋值符号:= 优先级非常低，在使用赋值表达式时要使用括号

使用未定义变量不会产生语法错误，只是该变量为null

③ 使用案例

Ⅰ 优化排名语句

select actor_id, @rownum:=@rownum+1 as rownum from actor limit 10;

select actor_id, cnt, @actor_number:=@actor_number+1 from (select actor_id, count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10) t;

actor_namber:= 赋值优先级低，所以在外层，否则在orderby 期间 actor_num会每条数据都+1；

Ⅱ 避免重新查询刚刚更新的数据

update t1 set lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;

------ 优化为

update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;

Ⅲ 赋值和读取的时候可能是在查询的不同阶段

where和select在查询的不同阶段执行，where先执行，会查到两条记录， rownum = 1，2：

set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;

加入 order by之后，打印全部结果，sql中使用了自定义变量后，orderby先执行进行全表排序，再根据自定义变量取值：

set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name;

解决上述问题关键在于让变量的赋值和取值发生在执行查询的同一阶段：

set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;