mysql优化实践

墨蓝 2021-10-24 01:07 295阅读 0赞

mysql单表超过50W会比较卡，而且in查询也很耗时间。现在有个需求是，查询关注圈的动态

首先是把关注的人查询出来，然后再查询关注的人的动态，最后再把自己的动态查询出来，关联分页，排序。

界面显示效果是这样的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9saWFveW9uZ2d1YW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70][]

动态表：

CREATE TABLE `dynamic_state` (
      `id` BIGINT(20) NOT NULL,
      `name` VARCHAR(50) DEFAULT NULL COMMENT '昵称',
      `head_portrait` VARCHAR(500) DEFAULT NULL COMMENT '头像',
      `talk_id` VARCHAR(50) DEFAULT NULL,
      `content` VARCHAR(1080) DEFAULT NULL COMMENT '内容'
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

关注表：

CREATE TABLE `concern` (
      `id` BIGINT(20) NOT NULL,
      `talk_id` BIGINT(20) DEFAULT NULL,
      `concern_talk_id` BIGINT(20) DEFAULT NULL COMMENT '我关注的talkId'
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

SELECT id,talk_id,content,head_portrait,NAME FROM dynamic_state WHERE  talk_id IN(SELECT concern_talk_id FROM concern WHERE talk_id='18977197')  UNION 
    SELECT id,talk_id,content,head_portrait,NAME  FROM dynamic_state WHERE    talk_id='18977197' ORDER BY id desc limit 0,10;

再没有使用任何优化的情况下，查询速度是13秒左右。

这里首先用一下索引，因为我们都是根据talk\_id关联查询的所以我们需要对动态表和关注表，建立索引。                                       
 ALTER TABLE dynamic\_state ADD INDEX talkId (talk\_id);  
 ALTER TABLE concern ADD INDEX talkId (talk\_id);

建立了索引以后速度在1.5秒左右。

n 和exists   
in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询。   
一直以来认为exists 比in 效率高的说法是不准确的。如果查询的两个表大小相当，那么用in 和exists 差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in  
这里动态表的数据量在20W，而关注表的数量在40W左右。所以这里用exists优化会更好一点。

SELECT *FROM dynamic_state d WHERE EXISTS(SELECT talk_id FROM concern WHERE talk_id=d.talk_id AND talk_id='18977197')

无论我把判断条件放在外面还是里面，都是一样的结果，查询很慢，很慢，差不多20几秒,完全比in慢很多。

这里来分析一下他们的实现原理：

select \* from A  
where id in(select id from B)

以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.  
它的查询过程类似于以下过程

List resultSet=\[\];  
Array A=(select \* from A);  
Array B=(select id from B);  
  
for(int i=0;i<A.length;i++) \{  
   for(int j=0;j<B.length;j++) \{  
      if(A\[i\].id==B\[j\].id) \{  
         resultSet.add(A\[i\]);  
         break;  
      \}  
   \}  
\}  
return resultSet;

可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次.  
如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000\*1000000次,效率很差.  
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000\*100次,遍历次数大大减少,效率大大提升.

结论:in()适合B表比A表数据小的情况

select a.\* from A a   
where exists(select 1 from B b where a.id=b.id)

以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.  
它的查询过程类似于以下过程

List resultSet=\[\];  
Array A=(select \* from A)  
  
for(int i=0;i<A.length;i++) \{  
   if(exists(A\[i\].id) \{    //执行select 1 from B b where b.id=a.id是否有记录返回  
       resultSet.add(A\[i\]);  
   \}  
\}  
return resultSet;

当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.  
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.  
如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.  
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000\*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

但是经过我的实验发现加了索引之后in 要比exists 快很多。

# 解决1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' #

出现场景：使用子查询时  
这版本的 MySQL 不支持使用 LIMIT 子句的 IN/ALL/ANY/SOME 子查询  
意味着下列查询不能正确的执行

select \* from my\_table where id in (select id from my\_table limit 10)  
解决方案：

select \* from my\_table where id in (select t.id from (select \* from my\_table limit 10)as t)   
再套一次查询就OK了

今天创建视图的时候，sql语句为 select \* from （select a from A union select b from B ） as C，但是查询1000条数据，居然花了45s的时间，于是查询了资料，资料显示：

UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。

而我的A表和B表不可能有重复数据，于是换成select \* from （select a from A union all select b from B  ) as C，时间缩短到 了1s以内，长见识了。以前只知道union和union all的用法

而不知道它们的使用，居然对sql的效率影响如此之大。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9saWFveW9uZ2d1YW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20211023/6e4d06e67b2444b08a6c04b8de1c4077.png