Mysql查询性能优化

时间:2021-12-08 07:35:24

Mysql查询性能优化

慢查询优化基础:优化数据访问

  • 查询需要的记录。查询100条,应用层仅需要10条。
  • 多表关联时返回全部列。*,多表关联,字段查询要加前缀。
  • 总是取出全部列。*
  • 重复查询相同的数据。例如:在用户评论的地方需要查询用户头像URL,那么用户多次评论的时候,可能就会反复查询这个数据。比较好的方案,当初次查询的时候将这个数据缓存起来,需要的时候从缓存中取出,这样性能显然会更好。

重构查询方式

切分查询

  • 将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。如果一次性完成的话,则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

分解关联查询

  • 很多高性能的应用都会对关联查询进行分解。
  • 简单地,可以对每一个表进行一次单表查询,然后将结果在应用程序中进行关联。
select * from tag join tag_post on tag_post.id = tag.id join post on post.id = tag_post.id where tag.tag = 'msyql'; 分解为: select * from tag from where tag = 'msyql'; select * from tag_post where id = 1234; select * from post where id in (1,2,3); 
优势
  • 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如:上面查询中的tag已经被缓存了,那么应用就可以跳过第一个查询。再例如,应用中已经缓存了ID为1,2的内容,那么第三个查询的in()中就可以少了几个ID,对MYSQL的查询缓存来说,如果关联中的某个表发生了变化,那么久无法使用查询缓存了,而拆分后,如果某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了。
  • 将查询分解后,执行单个查询就可以减少锁的竞争。
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和高扩展。
  • 查询本身效率也可能会有所提升。使用IN()代替关联查询,可以让MYSQL按照ID顺序进行查询,这可能比随机的关联要更搞笑。
  • 可以减少冗余记录的查询。在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会减少网络和内存的消耗。
  • 更进一步,这样做相当于在应用中实现了哈希关联,而不是使用MYSQL的潜逃循环关联。某些场景哈希关联的效率要高很多。
  • 在很多场景下,通过重构查询将关联放到应用程序中将会更加高效,这样的场景有很多,比如:当应用能够方便地缓存单个查询的结果的时候,当可以将数据分布到不同的MYSQL服务器上的时候,当能够使用IN的方式代替关联查询的时候、当查询中使用同一个数据表的时候。

查询执行基础

MYSQL接收到请求都做了什么?

  1. 客户端发送一条查询给服务器。
  2. 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务器进行SQL解析、预处理,再由优化器生成对应的执行计划。
  4. MYSQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

MYSQLk客户端/服务端通信协议

MYSQL客户端和服务端之间的通信协议是“半双工”的,这意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生。一旦一端开始发送消息,另一端要接收完整个消息才能响应它。这就像来回抛球的游戏:在任何时刻,只能一个人控制球,而且只能空值求得人才能将球抛回去。
客户端用一个单独的数据包将数据传给服务器,这也是为什么当查询的语句很长的时候,参数mac_allow_package就特别重要了。一旦客户端发送了请求,它能做的事情就只能是等待结果了。
相反的,一般服务器响应给用户的数据通常很多,由多个数据包组成。当服务器开始响应客户端请求时,客户端必须完整地接收整个返回结果,而不能简单地只取前面几条结果,然后让服务器停止发送数据。这种情况下,客户端若接收完成的结果,然后取前面几条需要的结果,或者接完几条结果后就“粗暴”地断开连接,都不是好主意。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。

查询状态

对于一个MYSQL连接,或者说一个线程,任何时刻都有一个状态,该状态表示了MYSQL当前正在做什么。有很多方式能查询当前状态,最简单的是使用show full processlist命令。一个查询的生命周期中,状态会变化很多次。
  • Sleep
    • 线程正在等待客户端发送新的请求。
  • Query
    • 线程正在执行查询或者正在将结果发送给客户端。
  • Locked
    • 在MYSQL服务器层,该线程正在等待表锁。在存储引擎级别实现的锁。例如:Innodb的行锁,并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态,但在其他没有行锁的引擎中也经常出现。
  • Analyzing and statistics
    • 线程正在收集存储引擎的统计信息,并生成查询执行计划。
  • Copying to tmp table [on disk]
    • 线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么在做Group By操作,要么是文件排序操作,或者是UNION操作。如果这个状态后面还有“on disk”标记,那表示MYSQL正在讲一个内存临时表放到磁盘上。
  • Sorting result
    • 线程正在对结果集进行排序。
  • Sending data
    • 这表示多种情况:线程可能在多个状态之间传送数据,或者在生成结果集,或者在客户端返回数据。

了解这些状态的基本含义非常有用,这可以让你很好地了解当前“谁正在持球”。在一个繁忙的服务器上,可能会看到大量的不正常状态,例如statistics正占用大量的时间。这通常表示,某个地方有异常了。

查询优化

MYSQL如何执行关联查询

对于UNION查询,MYSQL先将一系列的单个查询结果放到一个临时表中,然后再重新读取临时表数据来完成UNION查询。

在MYSQL的概念中,每个查询都是一次关联,所以读取结果临时表也是一次关联。

当前MYSQL关联执行的策略很简单:MYSQL对任何关联都执行嵌套循环关联操作,即MYSQL先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,知道找到所有表中匹配的行为止。然后根据各个表匹配的行,返回查询中需要的各个列。MYSQL会尝试在最后一个关联表中找到所有匹配的行,如果最后一个关联表无法找到更多的行以后,MYSQL返回到上一层次关联表,看是否能够找到更多匹配记录,一次类推迭代执行。

简单的内连接查询: select tab1.col1, tab2.col2 from tab1 inner join tab2 using(col3) where tab1.col1 in (1,2); 实际执行的伪代码表示: outer_iter = iterator over tabl1 where col1 in (1,2) outer_row = outer_iter.next while outer_row inner_iter = iterator over tab2 where col3 = outer_row.col3 inner_row = inner_iter.next while inner_row output [ outer_row.col1, inner_row.col2] inner_row = inner_iter.next end outer_row = outer_iter.next end 
简单的外连接查询: select tab1.col1, tab2.col2 from tab1 outer join tab2 using(col3) where tab1.col1 in (1,2); 实际执行的伪代码表示: outer_iter = iterator over tabl1 where col1 in (1,2) outer_row = outer_iter.next while outer_row inner_iter = iterator over tab2 where col3 = outer_row.col3 inner_row = inner_iter.next if inner_row while inner_row output [ outer_row.col1, inner_row.col2] inner_row = inner_iter.next end else output [ outer_row.col, null ] end outer_row = outer_iter.next end 

MYSQL的临时表是没有任何索引的,在编写复杂的子查询和关联查询的时候需要注意这一点。这一点对UNION查询也是一样的。

关联子查询

MYSQL的子查询实现得非常糟糕。最糟糕的一类查询是where条件中包含IN()的子查询语句。
select * from tab1 where col1 in ( select col2 from tab2 where col3 = 1; ) 
MYSQL对IN()列表中的 选项有专门的优化策略,一般会认为MYSQL会先执行子查询返回所有包含col3为1的col2。一般来说,IN()列查询速度很快,所以我们会认为上面的查询会这样执行:
- SELECT GROUP_CONCAT(col2) from tab2 where col3 = 1; - Reuslt : 1,2,3,4, select * from tabl1 where col1 in (1,2,3,4); 
很不幸,MYSQL不是这样做的。MYSQL会将相关的外层表压到子查询中,它认为这样可以更高效率地查找到数据行。也就是说,MYSQL会将查询改成下面的这样:
select * from tab1 where exists ( select * from tab2 where col3 = 1 and tab1.col1 = tab2.col1 ); 
这时,子查询需要根据col1来关联外部表的film,因为需要到col1字段,所以MYSQL认为无法先执行这个子查询。
如果tab1表数据量小,性能还不是很糟糕,如果是一个非常大的表,那这个查询性能会非常糟糕。改写这个子查询
select * from tab1 inner join tab2 using(col1) where col3 = 1; && select * from tab1 where exists ( select * from tab2 where col3 = 1 and tab1.col1 = tab2.col1 ); 
一旦使用了DISTINCT和GROUP by,在查询执行的过程中,通常产生临时中间表。可以使用EXISTS子查询优化

UNION的限制

通过将两个表查询结果集合并取前20条
(select *from tab1 order by col1) union all (select * from tab2 order by col2) limit 20; 优化为: (select *from tab1 order by col1 limit 20) union all (select * from tab2 order by limit 20) 

UNION 临时表的数据会大大减少

 

优化COUNT()查询

Count()是一个特殊的函数,有两种非常不同的作用:它可以统计某个列的数量,也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式,则统计的就是这个表达式有值的结果数。
Count()的另外一个作用是统计结果集的行数。当MYSQL确定括号内的表达式值不可能为空时,实际上就是在统计行数。最简单的就是COUNT(*)。
简单的优化
select count(*) from tab1 where col >5; 优化为: select (select count(*) from tab1 ) - count(*) from tab1 where col <5; 扫描的数量会减少很多 子查询也会当成常数,使用expand可知 
情景:在同一个查询中统计一个列的不同值的数量,以减少查询的语句量

select sum(if(color = blue), 1, 0) as blue , sum(if(color = red), 1, 0) as red from items ; 同样也可以使用Count 

优化关联查询

  • 确保ON或者USING子句中的列有索引。
  • 确保任何group by和order by只涉及到一个表中的列。

优化LIMIT分页

select col1, col2 from tab1 order by col3 limit 50,5; 改写成: select col1, col2 from tab1 inner join ( select col1 from tab1 order by col3 limit 50,5 ) as lim using(col1); 
  • 这里的“延迟关联”将大大提升查询效率,它让MYSQL扫描尽可能少的页面,获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术可以优化LIMIT查询。

  • 有时候也可以将LIMIT查询转换为已知位置的查询,让MYSQL通过范围扫描获得到对应的结果。


select col1, col2 from tab1 where col1 between 30 and 50; select col1, col2 from tab1 where col1 < 500 order by col1 limit 20;