Mysql查询性能优化

时间：2022-09-23 20:33:30

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;

 

标签：

相关文章