一次sql性能的简单优化

发布时间:2015-06-08 01:13:00作者:左潇龙阅读(466 )评论(4)

    引言

      

      最近LZ还算比较清闲,所以LZ的个人博客也算是以正常的速度在完善。基本上大部分的功能都已经具备,接下来需要的就是内容。不过内容其实是最难的部分,要保持原创,并且还要具有一定的价值。

      这部分暂时不着急,LZ先来和各位分享一个优化sql语句的小故事。

      

    问题的出现

      

      LZ近期换了一家互联网金融公司相信大家都知道,互联网公司大部分使用的是mysql。由于LZ近期做的这个项目数据比较多,因此各种较为复杂的sql是难免的。其中,就有这么一个复杂但又逻辑很简单的sql。为了方便大家理解,LZ就暂且使用伪sql来表示。

    select * from A where A.id in 
    (
       select B.a_id from B where B.id in 
        (
           select C.b_id from C where C.some_col in (1,2,3.....)   
        )  
    )    

      这是一个逻辑上比较简单的嵌套子查询语句,其中A.id和B.id都是有主键索引的,C.some_col也是有唯一索引的。这个查询语句在LZ看来不会特别慢。因为按道理来说,如果是由内而外的去计算,所有的查询都是可以用到索引的。

      但事实不是这样的,事实证明,这个查询非常慢,大约需要3-5分钟。

      

    问题分析

      

      得知这个结果,这让LZ一直想不通为什么。

      因为在LZ看来,就如计算a * ((b+c) * d)这类表达式一样,sql的查询也应该是按照这个顺序查询的。就拿上面的sql来说,应该是先查出最内层的C.b_id有多少个(也就是先查询C表),然后再把查出来的值用来查出B.a_id(也就是查询B表),最后再把查出来的值去查A表。

      如果按照上面的顺序,这样查询速度是非常快的。事实也如此,因为LZ把上面那个sql如果拆成三个的话,查询就会非常快。

      这就说明,mysql肯定不是按照上面的顺序去执行的这个sql,而是其它的顺序。否则的话,速度是不可能这么慢的。但让LZ想不通的是,自内而外去计算和查询不是最常规的办法吗。

      

    水落石出

      

      天下的事就是这么巧,或者也可以说LZ有先见之明。因为知道自己以后要常用mysql,所以LZ不久前刚买了一本mysql性能优化的书籍。

      回到家中,二话不说,LZ就开始查找mysql执行sql的顺序。果然不出所料,mysql并不是按照从内到外的顺序去执行的查询,而是会从外到内进行遍历。换句话说,这个sql的时间复杂度大约是(表A大小*表B大小*表C所取的ID个数)。

      为了验证这一结果,咱们使用以下简单的sql构建一套测试使用的数据库。

    CREATE TABLE a (
        id INT NOT NULL AUTO_INCREMENT,
        NAME VARCHAR(200),
        PRIMARY KEY(id)
    );
    
    CREATE TABLE b (
        id INT NOT NULL AUTO_INCREMENT,
        NAME VARCHAR(200),
        a_id INT NOT NULL,
        PRIMARY KEY(id)
    );
    
    CREATE TABLE c (
        id INT NOT NULL AUTO_INCREMENT,
        NAME VARCHAR(200),
        b_id INT NOT NULL,
        PRIMARY KEY(id)
    );
    
    INSERT INTO a (NAME) VALUES (""),(""),("");
    
    INSERT INTO b (NAME,a_id) VALUES ("",1),("",1),("",2);
    
    INSERT INTO c (NAME,b_id) VALUES ("",1),("",1),("",2);

      这三个表的关系非常简单,LZ就不过多介绍了,接下来咱们使用以下这个sql去查询。

    SELECT * FROM a WHERE id  IN (
        SELECT a_id FROM b WHERE id IN (
            SELECT b_id FROM c WHERE id IN (
                1,3
            )
        )
    )

      接下来咱们看看这个SQL的执行计划,是不是如我们猜测的一般。

      可以看到,其中有两个全表扫描,也就是A表和B表,并且对C表会进行范围扫描,使用了主键索引。由于这些检索都是嵌套的,因此印证了刚才我们的猜测,时间的复杂度大约为(表A大小*表B大小*表C所取的ID个数)。

      在LZ当时的真实案例当中,A表有大约6000个记录,B表大约有300个记录,而C表索取的ID个数一般在10个左右。也就是说,这个SQL需要进行6000 * 300 * 10 = 1800万次遍历。这样粗略的一算,查询需要好几分钟也并不奇怪了。

      

    解决问题

      

      知道原因,解决起来就有很多种方案了。第一种是使用straight_join改变连接的顺序(不推荐使用这种奇葩方式),第二种是比较简单粗暴的,把这个查询拆分成两个或三个查询。

      最终LZ根据实际需要,将SQL拆分成了两个,也就是拆分成如下两个。

    SELECT * FROM a WHERE id  IN (
        下面那个查询的结果
    )
    
    SELECT a_id FROM b WHERE id IN (
        SELECT b_id FROM c WHERE id IN (
            1,3
        )
    )

      这样的话,第二个查询的时间复杂度大约为(B表大小*表C所取的ID个数),第一个查询的时间复杂度为第二个查询的个数,在这个例子当中是1。换算到LZ的实际情况,第二个查询的时间复杂度为300 * 10 = 3000,第一个查询的时间复杂度一般为1000左右(也就是说第二个查询查询出来的结果一般有1000个左右),那么总的时间复杂度为3000 + 1000 = 4000,与开始的1800万相比,相差了大约4500倍。

      这个差距实在是太可怕了,事实证明,LZ改变查询方式以后,查询的总时间缩小到了1s左右,与原来的3-5分钟相比,实际缩小了大约200-300倍。不过4500只是咱们非常粗略计算的一个估值,实际执行过程当中,因为需要两次数据库交互以及其它一些细节,总会有误差的。

      但是,这已经足以。

      

    后话

      

      由于LZ所做的项目数据量巨大,因此以后这样的sql优化肯定还会有,到时再一一分享给大家。LZ希望大家能通过这一系列的简单分享,能够有所收获,也对实际当中的工作有所帮助。

      下期再见!  

      


    版权声明:本文版权归作者(左潇龙)所有,欢迎转载。但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

    2
    精彩
    0
    感动
    1
    搞笑
    0
    开心
    0
    愤怒
    0
    无聊
    0
    灌水
    0
    惊讶
#1楼     时间:2015-06-19 15:11:43      来源:上海市网友

 <html>

<body><h1>test</h1></body>

</html>

#2楼     时间:2015-06-19 15:28:39      来源:广东省网友

还可以。

#3楼     时间:2016-09-21 15:26:52      来源:东莞市网友
#4楼     时间:2017-03-16 20:45:14      来源:广州市网友
发表评论

站内搜索

最新评论