Mysql调优-5查询优化
查询优化
1.分析查询慢的原因:
硬件+程序的原因
1>网络对查询是有影响的,网络IO的影响;
2>CPU:
3>IO:
4>上下文切换:服务器中n个任务执行,任务的切换
5>系统调用:
6>生成统计信息:show profiles;
7>锁等待时间:
2.优化数据访问:
MyISAM:共享读锁+读占写锁;只能锁表
InnoDB:共享锁+排他锁;锁表或者行;InnoDB锁的对象是索引,如果锁的列是索引列,锁的是行;没有索引的话锁的是表;
2.1查询性能低下的主要原因是访问的数据太多,某些查询不可避免的需要筛选大量的数据,我们可以通过减少访问数据量的方式进行优化->IO的问题;
1>确认应用程序是否在检索大量超过需要的数据;
两条sql一致,只是查询条件不一致,索引字段是一致的,rows不一致,因为如果查询的数据量非常大的话,就有可能出现不用索引,但是阈值是不确定的(30%?)。
实际只需要5条,但是rows缺筛选了16008条;->SQL优化
limit 10000,10000这个值特别大,影响了sql的查询进行全表扫描,可以使用子查询来解决?
2>确认mysql服务器是否在分析大量超过需要的数据行:
->总之就是是否能筛选更少的数据来达到需要的效果->减少IO量
2.2是否向数据库请求了不需要的数据:
1>查询不需要的记录:mysql一般是先返回全部数据再进行计算,优化查询limit记录
2>多表关联时返回全部列:
通过查询时间可以看出来,*尽量不要出现在select中,而且多表关联的时候,筛选哪些字段就明确获取哪些字段,多表关联,每个表尽量加别名.
3>总是取出全部列:
4>重复查询相同的数据:可以数据库缓存实现(8以后去掉了),但是需要考虑到数据库不停更新的情况,所以用redis实现,涉及到数据淘汰策略,LRU策略;
3.执行过程的优化:
3.1查询缓存:->命中率比较低,常量表可以放到缓存中;
3.2查询优化处理:
1>语法解析器
apach calcite ->AST
2>查询优化器
执行方式多种,选择最有效的优化方式来执行,CBO(基于成本的优化->选择)+RBO(基于规则的优化)
mysql的优化一般是基于成本的优化,不一定是执行最快的优化(例如多表的查询,并不能保证join的查询,除非强制,所以具体join顺序是不能保证的)
mysql不会考虑不受其控制的操作成本.
优化器的优化策略:
动态优化:每次执行都需要重新评估
静态优化:只需要一次
优化器的优化类型:
重新定义关联表的执行顺序,不强制指定顺序的话,优化器决定
将外连接转换为内连接,内连接效率高于外连接
使用等价变换规则: in推荐使用替代多个查询条件, and 和or推荐使用or,不影响
聚合函数使用的时候需要注意分组优于min,max,但是具体看需求
索引的效率更高
覆盖索引:所有查询列包含在索引字段中
子查询优化:
等值传播:film.film_id>500 and film_actor.film_id>500, 很少这样写,都是只写一个film_id>500即可.
? limit 10000,5 的只筛选5条数据;
join_buffer;->show variables like 'JOIN_BUFFER';
一般情况很少去改Mysql的优化器;
3.3排序(**):
排序:https://www.cnblogs.com/wt645631686/p/8320525.html
提高ORDER BY速度的技巧
1:ORDER BY时不要使用SELECT *,只查需要的字段。
a:当查询的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法---单路排序,否则用老算法---多路排序。假设只需要查10个字段,但是SELECT *会查80个字段,那么就容易把sort_buffer缓冲区用满。
b:两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size大小。
2:增大sort_buffer_size参数大小
不管用哪种算法,提高这个参数都会提高效率。当然要根据系统能力去提高,因为这个参数是针对每个进程的。
3:增大max_length_for_sort_data参数大小
提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。
4.优化特定类型的查询:
4.1优化count的查询:
count(*) count(id) 和 count(1)在mysql中的查询效率是相同的;
myisam中只有没有任何where条件的count(*)才会比较快
使用近似值:OLAP中HyperLoglog
更复杂的优化:每次统计查询的时候,例如每次更新一条记录,缓存中要统计,可以借助于汇总表,缓存系统来优化.
4.2优化关联查询:
确保on或者using子句中的列上有索引,在创建索引的时候就要考虑到关联的顺序
确保任何的group by和order by中的表达式只涉及到一个表中的列,这样mysql才有可能使用索引来优化这个过程
4.3优化子查询:
子查询的sql尽量使用关联查询来进行替代,因为子查询的结果放临时表,涉及到io,所以能不用子查询尽量不要用子查询,join的临时表放最终的结果的
说明:https://www.cnblogs.com/cjjjj/p/12738334.html
4.4优化limit分页
limit的优化最简单的方法是尽可能使用覆盖索引而不是查询所有的列
4.5优化union查询
union
union all
如果没有要求消除重复的行,那么要使用union all,因为没有all关键字,mysql会在查询的时候给临时表加上distinct关键字,操作的代价比较高
行转列的方式:
join
union
case when
5.推荐使用用户自定义变量,但是有使用的限制
select @@autocommit;
select @i:=@i+1;
优化排名语句:
1>在给一个变量赋值的时候使用这个变量
set @rownum:=100;
select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;
2>查询获取演过最多电影的前10名演员,然后根据出演电影次数做一个排名
select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;
3>避免重新查询刚刚更新的数据
update user set tdate=now() where id=1 and @now:=now();
select @now;
变量只在当前会话有效;
4>确定取值的顺序:
where和order by用自定义变量的时候,顺序会改变
如何使用分区表?
查询的数据总是某个规则的数据;
热点数据;
使用分区的注意点:
null值对分区无效;
分区列和索引列不匹配,无法进行分区过滤,是分区还是索引起作用,所以尽可能匹配
分区列固定尽可能不更改;(年里面再按照天处理)
维护分区的成本比较高,所以选择比较容易区分的分区列.(递增或者随机性比较大的值不建议使用)