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值对分区无效;

　　　　分区列和索引列不匹配，无法进行分区过滤，是分区还是索引起作用，所以尽可能匹配

　　　　分区列固定尽可能不更改;（年里面再按照天处理）

　　　　维护分区的成本比较高，所以选择比较容易区分的分区列.(递增或者随机性比较大的值不建议使用)