mysql 查询优化笔记

查询的生命周期：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”是生命周期中最重要的阶段，大量的检索数据到存储引擎的调用以及调用后的数据处理，包括分组排序等。


慢查询优化：
1.确认程序是否需要检索大量超过需要的数据
多余的数据会给mysql服务器带来额外负担，并增加网络开销，另外也会消耗服务器的CPU和内存。
常见的问题有：
a.先用select查询大量结果，再获取前N行。（可通过limit解决）
b.多表关联返回全部列
c.总是取出全部列
d.重复查询相同的数据（如用户头像的url，可通过缓存解决）
2.确认mysql服务层是否在分析大量超过需要的数据行。
衡量查询开销的3个指标：
a.响应时间
了解这个查询需要哪些索引及它的执行计划是什么，然后计算大概需要个顺序和随机I/O，再乘以在具体硬件下一次I/O的消耗时间。
b.扫描的行数和返回的行数
理想情况下扫描的行数和返回的行数相同，实际上一般在1:1到1:10之间。
c.扫描行数和访问类型
在EXPLAIN语句中的type列反应了访问类型。从全表扫描、范围扫描、唯一索引扫描、常数引用等，速度由慢到快。如果没有办法找到合适的访问类型，合适的办法就是增加一个合适的索引。

一般mysql能使用如下三种方式应用where条件，从好到坏依次为：
1.在索引中使用where条件来过滤不匹配的记录，这是在存储引擎层完成的。
2.使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在mysql服务器层完成的，但无需再回表查询记录。
3.从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现了Using where），在mysql服务器完成的，需要回表查询数据。

需要扫描大量数据，但返回少数行时，可以通过如下方式优化：
1.使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无需回表获取数据。
2.改变库表结构，使用单独的汇总表。如现在工作中，在数据工厂中完成复杂计算，然后将结果表出库，供报表查询使用。
3.重写这个复杂的查询

重构查询方式
1.切分查询
mysql让连接和断开都很轻量级的，在返回一个小的查询结果方面很高效。
在其它条件相同的情况下，使用尽可能少的查询，且能够胜任，当然是好的，但有时候将大查询分解为多个小查询都是必要的。
如：删除旧数据。定期清除大量数据，如果使用一个大的语句一次完成，可能需要一次锁住很多数据、沾满整个事务日志、耗尽系统资源、阻塞很多小但重要的查询。例如：
delete from messages where created < date_sub(now(),interval 3 month);
可改成如下方式：
rows_affected = 0
do{
rows_affected = do_query(
"delete from messages where created < date_sub(now(),interval 3 month) limit 10000"
)
}while rows_affected > 0
一次删除一万条数据是一个比较高效且对服务器影响最小的做法。
2.分解关联查询
 select * 
from tag
join tag_post
 on tag_post.id = post.id
join post 
 on tag_post.post_id = post.id
where tag.tag='mysql';

select * from tag where tag='mysql';
select * from tag_post where tag_id=1234;
select * from post where post.id in (123,23,423,422)

应用对于分解关联查询的优势如下：
1.让缓存效率更高。可以方便地缓存单表查询对应的结果对象。对mysql来说，如果关联中的某个表发生了变化，就无法使用查询缓存了，而拆分后，如果某个表很少该表，就可以重复使用缓存。
2.拆分后，执行单个查询可以减少锁的竞争。
3.在应用层做关联，更容易对数据进行拆分。
4.查询本身效率也会有所提升。如：使用in()代替关联查询，可让mysql按照id顺序进行查询，比随机关联更高效。
5.可减少冗余记录的查询。在应用层做关联，意味着对于某条记录应用，只需要查询一次，而在数据库中做关联，可能需要重复访问。
6.相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联。

查询执行的基础
遵循一些原则，让优化器能够按照预想的方式运行。

show full processlist;
sleep 线程等待
    query 线程正在执行查询或正将结果发给客户端；
   locked 在mysql服务层，等待表锁；
  Annolyzing and statistics 线程正在收集存储引擎的统计信息，并生成查询的执行计划；
 Copying to tmp table [on disk] 线程正在执行查询，并将结果复制到一个临时表中，一般是在做group by，或文件排序或union操作，如果还有 on disk，表示mysql正将一个内存临时表放在磁盘上。
 Sorting result 线程正在对结果集排序

 Sending DATA 表示多种情况，传送数据，生成结果集，返回客户端数据等。

有多种原因会导致mysql优化器选择错误的执行计划：
 1.统计信息不准确。
 2.执行计划中的成本估算不等于实际执行的成本。
 3.mysql的最优是基于成本的，和自己想的基于时间的不一样。
 4.mysql不考虑并发执行的查询
 
 
 优化策略：
静态优化：直接对解析树进行解析，并完成优化。静态优化在第一次完成后一直有效，即使使用不同的参数重新执行也不变。
动态优化：和查询的上下文有关，需要在每次查询的时候重新评估。

mysql能够处理的优化类型：
1.重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序是优化器很重要的一部分功能。
2.将外连接转换为内连接
并不是所有的outer join语句都必须以外连接方式执行。如果：where条件、库表结构都会让外连接等价内连接。
3.使用等价变化规则
如：(a < b and b = c) and a = 5 改成 b > 5 and b = c and a = 5
4.优化count()、min()、max()
索引和列是否可为空，可帮助mysql优化这类表达式。
5.评估并转换为常数表达式
如：一个用户自定义的变量，在查询没有变化时就可转换为一个常数。甚至一个查询，如在索引列上执行min()，。
6.覆盖扫描索引
索引中的列覆盖了查询需要的列，使用索引返回需要的数据，无需返回表进行查询
7.子查询优化
8.提前终止查询
当已满足查询需求的时候，提前终止查询，如limit,和判断存在性的时候，
如：  select film.film_id
 from sakila.film
 left join sakila.film_actor using(film_id)
 where film_actior.film_id is null
9.列表in()的比较
in()列表中的数据进行排序，通过二分查找方式来确定表中值是否满足，这是一个O(log n)复杂度的操作
如果能够确认优化器给出的不是最优选择，并且清楚背后原理，就可以帮助优化器做进一步的优化。如：查询汇总添加hint提示，重写查询，重新设计更优的库表结构，添加更合适的索引。

Mysql执行关联查询
对任何关联都执行嵌套循环关联操作。即：一个表中循环取出单条数据，拿到另一个表中进行循环匹配。

执行计划
mysql生成查询的一颗指令树，然后通过存储引擎执行完成这课指令树并返回结果。如果对某个查询执行explain extended后，再执行show warnings，就可以看到重构出的全部查询。
 按oracle的描述就是：小表做驱动表（主要根据返回数据的多少），大表做被驱动表，这样小表扫描大表，循环次数少，大表上建索引，扫描数据快，整体效率高。


排序优化
 1.通过索引
 2.无法使用索引时，mysql自己排序，如果数据量小，在内存中进行，如果数据量大，需要使用磁盘，mysql将这个过程称为文件排序。
 排序的两种算法：
  1.两次传输排序（旧版本）
读取行指针和需要排序的字段，对其进行排序，再根据排序结果读取需要的数据行。会产生大量随机io,数据传输成本很高。
  2.单次传输排序（新版本）
先读取查询所需要的所有列，然后根据给定列进行排序，最后直接返回排序结果。只需一次顺序I/O，无需随机I/o,缺点是如果返回的列非常多，会额外占大量空间。

Mysql的局限性
 1.union 
 无法将限制条件从外层“下推”到内层。如果希望union的各个字句能根据limit只取部分结果集，或希望先排好序，再合并结果集，就要在union的各个字句中都使用这些字句。
 2.等值传递
 某些时候，等值传递会带来很大的额外消耗。如：一个非常大的in()列表，优化器发现存在where,on，将这个列表的值和另一个表的某列值进行关联。优化器会将in()列表都复制到关联的各个表中，将导致优化和执行变得很慢。
 3.并行执行
mysql不支持多核并行
4.哈希 关联
mysql不支持哈希关联，所有的关联都是嵌套循环
5.松散的索引扫描
 不支持 ，无法按照不连续的方式扫描索引，如：select * from tab1 where b between 2 and 3
6.在同一个表上查询和更新
不允许，如：
update tb1 as outer_tb1
set cnt = (
select count(*) 
from tb1 as inner_tb1 
where inner_tb1.type = outer_tb1.type
);

查询优化器提示
通过在查询中加入相应的提示，控制该查询的执行计划。


HIGH_PRIORITY和LOW_PRIORITY
当多个语句同时访问某一个表的时候，哪些语句优先级高，只是简单控制了mysql访问某个数据表的顺序。
这两个提示只对使用表锁的存储引擎有效，不要在InnoDB或其它细粒度机制和并发控制的引擎中使用。


DELAYED
对 insert 和 replace 有效。会将使用该提示的语句立即返回给客户端，并将插入的行数据放入缓冲区，然后在表空闲时批量将数据插入。
限制：并不是所有的引擎都支持这样的做法，且会导致函数 last_insert_id()无法正常工作。


STRAIGHT_JOIN
放在select关键字之后，或任何两个关联表的名字之间。用法1：让查询中所有的表按照在语句中出现的顺序进行关联。用法2：固定其前后两个表的关联顺序。
可以使用explain查看优化器选择的顺序，然后使用该提示重写查询，再看它的关联顺序。


SQL_SMALL_RESULT 和 SQL_BIG_RESULT
只对select语句有效。告诉优化器对group by或distinct查询如何使用临时表及排序。
sql_small_result告诉优化器结果集会很小，可以将结果放到内存中的索引临时表，避免排序操作。
sql_big_result告诉优化器结果集很大，建议使用磁盘。


SQL_BUFFER_RESULT
告诉优化器将结果放入一个临时表，尽可能快的释放表锁。当没法使用客户端缓存的时候，服务器端的缓存会很有效，但会消耗更多的内存。
SQL_CACHE 和 SQL_NO_CACHE
告诉mysql这个结果集是否应缓存在查询缓存中。


SQL_CALC_FOUND_ROWS
让返回的结果集包含更多的信息


FOR UPDATE 和 LOCK IN SHARE MODE
主要控制select语句的锁机制，只对实现了行级锁的存储引擎有效。该提示对符合查询条件的数据行枷锁。尽可能避免使用该提示。


USE INDEX 、 IGNORE INDEX 和 FORCE INDEX
使用或不使用哪些索引来查询记录。当发现优化器选择了错误的索引或某些原因（如不使用order by 但希望结果有序）要使用另一个索引时，使用该提示。




优化特定类型的查询
1.优化count()查询
count()作用：
(1).统计某个列值的数据，不统计null，要求列值非空。
(2).统计行数。count(*)
如果mysql知道某列col不可能为null值，mysql内部会将count(col)表达式优化为count(*)


简单的优化：
  select count(*) from city where id > 5;
 改成如下，大大减少扫描的行数：
  select (select count(*) from city) - count(*)
  from city 
  where id <= 5;


假设通过一个查询返回各个不同颜色的商品数量，此时不能用or
如:select count(color='blue' or color='red) from items;
可如下：
select sum(if(color='blue',1,0)) as blue
  ,sum(if(color='red',1,0)) as red
from items;


2.优化关联查询
(1)确保on的列上有索引。在创建索引的时候要考虑到关联顺序。如果表A和表B用列c关联，优化器的关联顺序是B、A，就不需要在B表对应列创建索引。
(2)确保group by 和 order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引优化这个过程。
(3)升级mysql要注意关联语法、运算符优先级等。


3.优化子查询
尽可能使用关联查询代替。


4.优化group by 和distinct
(1)使用索引优化
(2)无法使用索引时，group by使用两种策略来完成：使用临时表或文件排序来分组。可通过提升sql_big_result 和 sql_small_result。
如果需要对关联查询做group by，并且按照查找表中的某个列进行分组，通常采用查找表的标识列分组效率会比其他类更高。


优化group by with rollup
对返回的结果做一次超级聚合。


5.优化limit分页
通常是由limit加上偏移量的办法实现，同时加上合适的order by子句，如果有对应的索引，效率会比较高。
优化办法：尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，效率会提示很大。如：
select film_id,description 
from sakila.film 
order by title limit 50,5;
可改成：
select film.film_id,film.description
from sakila.film 
join (
select film_id 
from sakila.film
order by title limit 50,5
    ) as lim
on film.film_id = lim.film_id 


6.优化SQL_CALC_FOUND_ROWS
 243页
 
7.优化UNION查询
mysql总是填充临时表的方式执行UNION查询。因此很多优化策略在UNION查询中都无法使用。需要手工的将where,limit,order by 等子句下推到UNION的各个子查询中。
除非确实需要服务器消除重复的行，否则一定要使用UNION ALL。如果没有ALL,mysql会给临时表加上distinct选项，对整个临时表做数据唯一检查。


8.静态查询分析
pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在的问题的查询，并给出详细的建议。


9.

使用用户自定义变量
自定义变量是一个用来存储内容的临时容器，可使用如下的set和select 语句来定义它们:
set @one := 1;
set @min_ac := (select min(actor_id) from sakila.actor);
set @last_week := current_date - interval 1 week;
然后可以在任何使用表达式的地方，使用这些变量：
select ... where col <= @last_week;


变量的使用限制：
 a.无法使用查询缓存；
 b.不能在使用常量或标识符的地方使用自定义变量，如：表名、列名和limit子句中。
 c.如果使用连接池或持久化连接，可能让毫无关系的代码发生交互，通常是代码bug或连接池bug。
 d.不能显式的声明自定义变量的类型。
 e.赋值符号 := 的优先级非常低，所以需要注意，赋值表达式要使用明确的括号。
 f.使用未定义的变量不会有语法错误。
 
 优化排名语句
 可以给变量赋值的同时使用这个变量，具有“左值”特性。
 set @rownum := 0;
 select actor_id, @rownum := @rownum + 1 as rownum
  from sakila.actor limit 3;
  
--每个演员参演电影的数量 
select  actor_id,count(*) as cnt
from  salika.film_actor
group by actor_id 
order by cnt desc 
limit 10;




--一个记录当前的排名，一个记录前一个前一个演员的排名，一个记录当前演员参演电影的数量。只有当前演员参演电影的数据和前一个演员不同时，排名才变化。
set @curr_cnt := 0,@prev_cnt := 0, @rank := 0;
select actor_id,
@curr_cnt := count(*) as cnt,
@rank  := if(@prev_cnt <> @curr_cnt,@rank +１,@rank) as rank,
@prev_cnt := @curr_cnt as dummy
from sakila.film_actor
group by actor_id
order by cnt desc 
limit 10;


由于变量赋值的时间，导致出现错误；




set @curr_cnt := 0,@prev_cnt := 0, @rank := 0;
select actor_id,
@curr_cnt := count(*) as cnt,
@rank  := if(@prev_cnt <> @curr_cnt,@rank +１,@rank) as rank,
@prev_cnt := @curr_cnt as dummy
from (
select  actor_id,count(*) as cnt
 from  salika.film_actor
group by actor_id 
order by cnt desc 
limit 10
) as der




高效的更新时间戳，并查询当前的时间戳
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;
 
 --统计更新和插入的数量
insert into t1(c1,c2) values (2,3),(3,5),(2,5) on duplicate key update 
c1 = values(c1) +　(0* (@x := @x + 1));

让变量赋值和取值发生在执行查询的同一阶段：
set @rownum := 0;
select actor_id,@rownum as rownum 
from sakila.actor 
where (@rownum = @rownum +１) <= 1;




select id from users where id = 123
union all 
select id from users_archived where id = 123;


改成：只有第一个表中没有数据时，才在第二个表中查询。
一旦在第一个表中找到记录，就定义一个变量@found,通过在结果列中做一次赋值来实现，然后将赋值放在函数greatest中来避免返回额外的数据。


select greatest(@found := -1,id) as id,
'users' as which_tb1
from users 
where id = 1 
union all 
select id ,
  'users_archived'
from users_archived 
where id = 1 and @found is null 
union all
select 1,
'reset'
from dual
where (@found := null) is not null;