专栏首页Linyb极客之路MySql查询性能优化

MySql查询性能优化

避免向数据库请求不需要的数据

在访问数据库时,应该只请求需要的行和列。请求多余的行和列会消耗MySql服务器的CPU和内存资源,并增加网络开销。 例如在处理分页时,应该使用LIMIT限制MySql只返回一页的数据,而不是向应用程序返回全部数据后,再由应用程序过滤不需要的行。 当一行数据被多次使用时可以考虑将数据行缓存起来,避免每次使用都要到MySql查询。 避免使用SELECT *这种方式进行查询,应该只返回需要的列。

查询数据的方式

查询数据的方式有全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。这些查询方式,速度从慢到快,扫描的行数也是从多到少。可以通过EXPLAIN语句中的type列反应查询采用的是哪种方式。 通常可以通过添加合适的索引改善查询数据的方式,使其尽可能减少扫描的数据行,加快查询速度。 例如,当发现查询需要扫描大量的数据行但只返回少数的行,那么可以考虑使用覆盖索引,即把所有需要用到的列都放到索引中。这样存储引擎无须回表获取对应行就可以返回结果了。

分解大的查询

可以将一个大查询切分成多个小查询执行,每个小查询只完成整个查询任务的一小部分,每次只返回一小部分结果 删除旧的数据是一个很好的例子。如果只用一条语句一次性执行一个大的删除操作,则可能需要一次锁住很多数据,占满整个事务日志,耗尽系统资源、阻塞很多小的但重要的查询。将一个大的删除操作分解成多个较小的删除操作可以将服务器上原本一次性的压力分散到多次操作上,尽可能小地影响MySql性能,减少删除时锁的等待时间。同时也减少了MySql主从复制的延迟。 另一个例子是分解关联查询,即对每个要关联的表进行单表查询,然后将结果在应用程序中进行关联。下面的这个查询:

SELECT * FROM tag
    JOIN tag_post ON tag_post.tag_id=tag.id    JOIN post ON tag_post.post_id=post.idWHERE 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,456,567,9098,8904);

将一个关联查询拆解成多个单表查询有如下有点:

  1. 让缓存的效率更高。如果缓存的是关联查询的结果,那么其中的一个表发生变化,整个缓存就失效了。而拆分后,如果只是某个表很少的改动,并不会破坏所有的缓存。
  2. 可以减少锁的竞争
  3. 更容易对数据库进行拆分,更容易做到高性能和可扩展。
  4. 查询本身的效率也有可能会有所提升。例如上面用IN()代替关联查询比随机的关联更加高效。

优化MIN()和MAX()

添加索引可以优化MIN()和MAX()表达式。例如,要找到某一列的最小值,只需要查询对应B-Tree索引的最左端的记录即可。类似的,如果要查询列中的最大值,也只需要读取B-Tree索引的最后一条记录。对于这种查询,EXPLAIN中可以看到"Select tables optimized away",表示优化器已经从执行计划中移除了该表,并以一个常数取而代之。

用IN()取代OR

在MySql中,IN()先将自己列表中的数据进行排序,然后通过二分查找的方式确定列的值是否在IN()的列表中,这个时间复杂度是O(logn)。如果换成OR操作,则时间复杂度是O(n)。所以,对于IN()的列表中有大量取值的时候,用IN()替换OR操作将会更快。

优化关联查询

在MySql中,任何一个查询都可以看成是一个关联查询,即使只有一个表的查询也是如此。 MySql对任何关联都执行嵌套循环的关联操作,例如对于下面的SQL语句:

SELECT tbl1.col1,tbl2.col2FROM tbl1 INNER JOIN tbl2 USING(col3)WHERE tbl1.col1 IN(5,6);

下面的伪代码表示MySql将如何执行这个查询:

//先从第一个表中取出符合条件的所有行out_iter = iterator over tbl1 where col1 IN(5,6)outer_row = out_iter.next//在while循环中遍历第一个表结果集的每一行while outer_row    //对于第一个表结果集中的每一行,在第二个表中找出符合条件的所有行
    inner_iter = iterator over tbl2 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

对于单表查询,那么只需要完成上面外层的基本操作。 优化关联查询,要确保ON或者USING子句中的列上有索引,并且在建立索引时需要考虑到关联的顺序。通常来说,只需要在关联顺序中的第二个表的相应列上创建索引。例如,当表A和表B用列c关联的时候,假设关联的顺序是B、A,那么就不需要在B表的c列上建立索引。没有用到的索引只会带来额外的负担。 此外,确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列,这样才能使用索引来优化这个过程。

临时表的概念

上面提到在MySql中,任何一个查询实质上都是一个关联查询。那么对于子查询或UNION查询是如何实现关联操作的呢。 对于UNION查询,MySql先将每一个单表查询结果放到一个临时表中,然后再重新读出临时表数据来完成UNION查询。MySql读取结果临时表和普通表一样,也是采用的关联方式。 当遇到子查询时,先执行子查询并将结果放到一个临时表中,然后再将这个临时表当做一个普通表对待。 MySql的临时表是没有任何索引的,在编写复杂的子查询和关联查询的时候需要注意这一点。 临时表也叫派生表。

排序优化

应该尽量让MySql使用索引进行排序。当不能使用索引生成排序结果的时候,MySql需要自己进行排序。如果数据量小于“排序缓冲区”的大小,则MySql使用内存进行“快速排序”操作。如果数据量太大超过“排序缓冲区”的大小,那么MySql只能采用文件排序,而文件排序的算法非常复杂,会消耗很多资源。 无论如何排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序。所以让MySql根据索引构造排序结果非常的重要。

子查询优化

MySql的子查询实现的非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。 应该尽可能用关联替换子查询,可以提高查询效率。

优化COUNT()查询

COUNT()有两个不同的作用:

  1. 统计某个列值的数量,即统计某列值不为NULL的个数。
  2. 统计行数。

当使用COUNT(*)时,统计的是行数,它会忽略所有的列而直接统计所有的行数。而在括号中指定了一个列的话,则统计的是这个列上值不为NULL的个数。 可以考虑使用索引覆盖扫描或增加汇总表对COUNT()进行优化。

优化LIMIT分页

处理分页会使用到LIMIT,当翻页到非常靠后的页面的时候,偏移量会非常大,这时LIMIT的效率会非常差。例如对于LIMIT 10000,20这样的查询,MySql需要查询10020条记录,将前面10000条记录抛弃,只返回最后的20条。这样的代价非常高,如果所有的页面被访问的频率都相同,那么这样的查询平均需要访问半个表的数据。 优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描,而不是查询所有的列。然后根据需要与原表做一次关联操作返回所需的列。对于偏移量很大的时候,这样的效率会提升非常大。考虑下面的查询:

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果这个表非常大,那么这个查询最好改写成下面的这样子:

SELECT film.film_id, film.description FROM sakila.filmINNER JOIN (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5) AS limUSING(film_id);

注意优化中关联的子查询,因为只查询film_id一个列,数据量小,使得一个内存页可以容纳更多的数据,这让MySQL扫描尽可能少的页面。在获取到所需要的所有行之后再与原表进行关联以获得需要的全部列。 LIMIT的优化问题,其实是OFFSET的问题,它会导致MySql扫描大量不需要的行然后再抛弃掉。可以借助书签的思想记录上次取数据的位置,那么下次就可以直接从该书签记录的位置开始扫描,这样就避免了使用OFFSET。可以把主键当做书签使用,例如下面的查询:

SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;

假设上面的查询返回的是主键为16049到16030的租借记录,那么下一页查询就可以直接从16030这个点开始:

SELECT * FROM sakila.rental WHERE rental_id < 16030ORDER BY rental_id DESC LIMIT 20;

该技术的好处是无论翻页到多么后面,其性能都会很好。 此外,也可以用关联到一个冗余表的方式提高LIMIT的性能,冗余表只包含主键列和需要做排序的数据列。

优化UNION查询

除非确实需要服务器消除重复的行,否则一定要使用UNION ALL。如果没有ALL关键字,MySql会给临时表加上DISTINCT选项,这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

本文分享自微信公众号 - Linyb极客之路(gh_c420b2cf6b47),作者:poype

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-04-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 抛开复杂的架构设计,MySQL优化思想基本都在这了

    数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以...

    lyb-geek
  • DBA大牛告诉你,如何让MySQL语句执行加速?

    like模糊查询形如'%AAA%'和'%AAA'将不会使用索引,但是业务上不可避免可能又需要使用到这种形式。

    lyb-geek
  • 一份全面的“容灾概要设计”是怎样的?

    系统采用统一的异常捕获和处理机制,为了便于团队开发的一致性,统一定义错误代码和友好显示信息。开发过程中根据具体情况可以扩展错误信息,制定更加详细的错误分类和信息...

    lyb-geek
  • Elasticsearch 6.x版本全文检索学习之Search API

      方式一、GET /_search,对es中所有的数据进行查询。   方式二、GET /my_index/_search,针对单个索引的数据进行查询。   ...

    别先生
  • Access参数查询(一)

    大家好前面分别介绍了选择查询中的汇总查询、重复项查询和不匹配项查询,本节将介绍参数查询。

    无言之月
  • HAWQ技术解析(十二) —— 查询优化

            即便对SELECT等数据库查询语句已经很熟悉了,但HAWQ里的查询有其自己的特点,还是需要研究一下。 一、HAWQ的查询处理流程        ...

    用户1148526
  • 企业面试题|最常问的MySQL面试题集合(二)

    嵌套查询 用一条SQL语句得结果作为另外一条SQL语句得条件,效率不好把握 SELECT * FROM A WHERE id IN (SELECT id FRO...

    民工哥
  • 用于工业界的机器学习:案例研究

    您好,我是Chris Burges。 在我过去在微软工作了14年,在此之前为贝尔实验室又工作了14年,我花了大量的时间在机器学习(ML)上,其中有部分时间又花在...

    哒呵呵
  • mysql数据库开启慢查询日志

    第一句使用来定义慢查询日志的路径(若是linux系统,会涉及权限问题) 第二句使用来定义用时超过过多少秒的查询是慢查询,单位:秒。

    流柯
  • 【PostgreSQL架构】为什么关系型数据库是分布式数据库的未来

    大约10年前,我加入了Amazon Web Services,在那里我第一次看到了在分布式系统中进行权衡的重要性。在大学里,我已经了解了一致性和可用性之间的权衡...

    首席架构师智库

扫码关注云+社区

领取腾讯云代金券