mysql搜索调优_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排序(**):

提高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的临时表放最终的结果的

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

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

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

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