MYSQL之SQL优化的一点看法

我不是女神ヾ 2022-04-02 03:54 178阅读 0赞

## MYSQL之SQL优化的一点看法 ##

写这篇文章，是想告诉大家，不是所有的SQL都能优化，优化SQL不是一个一劳永逸的事。下面我阐述一下对SQL优化的一点看法。

**背景：很多朋友会问我SQL优化的事，把SQL丢过来，问他要执行计划，索引信息啥的，他也不懂， 真是心累，单凭SQL是很难去优化的啊，熊得！**

在一个关系型的数据库系统中，数据存取的速度往往能决定整个系统的运行状态，数据存取的快，一般系统用户的访问请求会快，数据存取的慢，那么系统一定是又臭又慢，用户的体验度一定很差，这无疑是老板最不想看到的。那么，为了保障整个系统以稳定高效的状态运行，应极力提升数据存取的速度。

*  SQL—select语句

相信现在每个程序员都或多或少都会写查询语句，即业界所说的SQL语句，只有十分简单的几个关键字，在其中填写你要查询的东西或者规则即可。也许正是由于它的简单，导致了很多开发人员在学习SQL的时候，只关注于查询结果的正确与否，不太关注查询的速度等其他状态。

在应用系统的开发中，SQL是需要审核才能上线的，这里大家应该能想到一点端倪吧，不重要的东西，还需要审核吗？对，确实很重要，正是因为不是每个开发人员的SQL都写的很规范、很高效，所以才需要进行专业的人员对其审核优化。在这里我们不对SQL的上线审核过程进行描述，这一过程现在完全可以通过程序来实现，本文重点讨论的是慢查询。

*  性能杀手—慢查询

一般，响应时间超过1秒的SQL，称之为慢SQL（实际生产中，业务代码里面的SQL执行时间最好不要超过0.1秒）。在mysql中，有记录慢查询的日志，打开慢查询的方式相信大家都已了解，简单来说即设定查询的时间阈值，当查询所用的时间超过了阈值则系统会将该查询记录下来。会记录所有超时的DML/DDL语句，这里我们又继续细分，只讨论select语句的问题。

大家可以思考一个问题，系统运行时为什么会出现慢查询？在SQL上线审核的时候，慢查询应该是被优化成非慢查询了，为何还会出现慢查询。这里有两种情况：

1.  SQL审核的系统存在缺陷（如只根据相应的规则来判断SQL的写法是否合理，而不检查它的执行时间），或者由于查询所涉及的表发生了很大的变化（如数据增多、索引删除、失效等等），导致原先审核通过的SQL还是变成了慢查询
2.  应用系统并发量很大，导致数据库的系统负载十分严重，这时你会发现慢查询陡增，并且将其单独拿出来，在业务并量小的时候执行，速度是很快的

从上面两种情况可以分析得出以下结论：

1.在审核时，应全面的检查SQL的执行情况，不仅是写法，还要对其执行计划进行深挖研究，使用了什么索引，是否用临时表等等，只要做足了准备工作，才能减少线上故障的发生。优化SQL往往只能解决当下的问题，并不能保证它永久不变成慢SQL ；展开来说，数据量与业务往往是随着时间不断地变化，并且可能表里的数据存在不断的插入 删除 更新，这里我们分开来说，20W的表，某个查询要0.5s，那么当这个表变成2000W时，执行时间还会只用0.5s吗？20W的表，某个查询要0.5s，那么当这个表有大量的删除更新插入，数据量还在20W左右时，执行时间还会只用0.5s吗？第一种情况我相信大家很能理解，数据变多，索引字段的区分度变的不高且索引层数变大时，执行时间要多余0.5s的。第二种存在一些底层的知识，数据修改频繁，索引页不断的合并、分裂，造成大量的碎片，是会造成索引失效而查询不走索引的，所以，执行时间并不能保证在0.5s左右了，这时我们可以定期的optimize table。

2.操作系统的负载能影响SQL的RT值（响应时间）；展开来说，当业务的并发很大时，在没有做负载均衡的情况下，操作系统的CPU往往十分繁忙，压力山大，特别是采用机械磁盘时，可以看到CPU的任务队列过长，IO等待十分严重，当然，采用固态硬盘时，并发相当之大，也会存在CPU负载严重的情况。好比原本一辆限载1000人的城际高铁，现在忽然有10000人需要上车，它还能保证这一万人到达终点站的时间跟以前一样吗，当然只能拉完一批再拉一批，后上车的人只能乖乖的等着下一趟列车，他们到达目的地的时间也就相应的延长了，遇到这样的高峰期，往往会采取加长列车或是加多班次，原本载1000的车，给他再加多两个车厢，变成能载1400人，或者本来就一趟车提供服务，现在给他开通十量车（当然这不太现实，哈哈）。纵向扩展与横向扩展的原理亦是如此，纵向扩展即提升硬件，如加大内存、CPU核数等，横向扩展即搭建集群，负载均衡。

*  优化慢查询

也许大家最关心的是这一部分，因为往往优化SQL是解决问题的最快的办法，也是最基础的办法，同时也最容易上手的办法。  
首先大家要清楚，不是所有的SQL都能优化的，一个1000W左右的innodb表，在没有自增主键的情况下，要查询出表的具体行数，能单纯的通过添加索引或者改写SQL来优化吗？显然是不能的，当然这种查询在OLTP的业务下很少出现，一个没有查询条件的SQL基本是不能通过传统的优化来提升其执行速度的。这里先介绍一下传统的SQL优化手段，主要是分为有索引与没索引的情况，当查询的表没有索引或者没有合适的索引时，我们往往添加合适的索引即可迅速达到优化的目的。有索引时，我们要细心的检查SQL的每部分是否走了索引，并且还要观察是否走了合适的索引，是否全部的利用了索引。  
要是根本就没有索引，导致出现了慢SQL，这种情况很少遇见，一般DBA都会针对开发人员写的SQL建立合适的索引（上线之前），建立合适的索引这里面也有大的文章，大致的方法原则就是：

1.  where后涉及到的字段要有索引，多个字段的话应建立多个字段的联合索引。
2.  group by、order by后面的字段在合适的情况下应建立索引，分组、排序能用到索引的条件很苛刻，有兴趣的同学可以自行了解。
3.  表的关联字段必须建立索引，换句话说，被驱动表的关联字段必须要有索引，这里有个知识点需要大家了解，t1 join t2 using(id)中，我们不要直观的认为t1就是驱动表，t2就是被驱动表，若是后面有where t2.name like “梅宏%”，t2表在经过where后的条件过滤了，结果集会比t1小很多，这时mysql的优化器会将t2表作为驱动表，具体我们应该通过explain来观察，这里只提及一点，explain中的id相同时，执行顺序由上往下，第一个就是驱动表，下面的是被驱动表。这里就不多介绍explain了，不得不说，知识是一环扣一环，学无止境，不可骄傲自满啊。
4.  不可建立冗余索引，二级索引的最后一个字段不可为主键，不可存在前N个字段相同的二级索引，即不可存在（B,C）与（B,C,D）这种
5.  索引及索引的字段不可过多，一个索引最好不要超过7个字段，字段过多，索引越大，维护麻烦，索引越多，数据的插入与更新则越慢
6.  另外不是所有的字段都适合建立索引，区分度不高、值的分布不均匀的字段是不适合单独建立索引的，如性别等。

要是相应的字段有了索引，那么我们应如何高效的利用索引呢？开发人员应该掌握这一部分的内容。具体知识我可能说的有遗漏，作为一名DBA，我本身不怎么在乎SQL怎么去写，我在乎的是如何去优化一条慢SQL，我更关注的是SQL的执行情况，网上确实已经有很多的资料，这里不重复造轮子咯，大家可以自行搜索学习，另外，我需要提醒一下大家，网上的知识可能会过时，MYSQL不断在优化进步，它的优化器很多时候会自动给你优化，比如or的使用，在以往我们通常是用union取代or，但是现在mysql的优化器会自动优化（index\_merge），不会在出现全表扫描的情况。

1.  不可对索引字段进行函数或者运算操作
2.  将or改写为union all，这是为了安全起见，同时也是让优化器少做一点事
3.  进行not 、 <> 、!=判断时，是不会利用索引
4.  联合索引的使用遵循向左匹配原则，在分组排序时亦是如此
5.  查询有效数据，避免无用的数据参与io。即查询A字段时不可select \*

作为SQL优化人员，又是该如何去优化一条可以优化的SQL呢？有一个很大很大的SQL，例如有个SQL是五个表的关联分组排序，往往让人看到就头痛，这里十分抱歉的是，我拿不出具体的例子，但问题不大，因为总是有下手点。 当看到这样的SQL，我的习惯是先获得SQL涉及的表的结构以及大致数据量，同时也需要知道慢SQL的执行时间，了解背景之后， 再研究SQL每部分的作用（可能要与开发人员进行沟通），这里就能判断SQL是否写的合理、是否能够改写。做完了上面的工作，接下来需要用上explain来观察SQL的执行计划了，根据SQL的执行计划来找出慢的根本原因。我的具体做法是，观察explain的执行顺序，找到扫描行数最多的一段，分析为何会扫描这么多行，弄清楚到底是写法原因还是索引原因，同时我们应清楚explain的每个字段输出的含义，往往全表扫描、文件排序、磁盘临时表最影响性能的，应尽可能减少他们的出现，Using filesort又分为内存与磁盘，这里我们还需要借助mysql的状态值来判断（show global status like “%tmp%”），总之就是尽可能使用索引（覆盖索引），避免回表查询，避免创建磁盘文件、临时表，让查询在内存、索引中完成。

说了一堆，不熟悉的人可能有点混乱，这里我从两个方面来总结，开发人员无法修改数据库的对象，只能是索引的利用者，故开发人员应清楚索引的使用原理，到底什么时候会走索引，如何有效的使用索引，这很关键。曾经隔壁项目组有为开发的同志叫我帮他们排查某系统缓慢的原因，后面我发现他们有个慢查询中涉及到几个字段，并且都是等值查询，然后他们建立索引的策略是每个字段单独建立一个索引，他非要说，明明走了索引还会很慢。。。。看来有必要给他们安排一期培训课程了。作为运维或者DBA来说，除了十分了解索引的工作原理之外，还要熟练的掌握一些SQL分析的工具，explain、profile，这两个工具是各有长短，前者主要是显示SQL的执行计划，索引使用情况、以及SQL的执行顺序等，并不是精确值，但还是很具有参考价值，后者主要是显示SQL每部分的操作系统性能开销，深度分析时用得着，同时还应清楚DBMS与OS之间的种种关联，一条SQL是如何读取数据的？什么时候开启QCache？……DBA要熟悉的知识是太多太多

看到这里，你们也许会骂我，这写的什么东西，乱七八糟的，我承认自己的语言组织能力不强，但我所说的一定都是本人的经验之谈的（没一点抄袭），是真诚的希望能帮到大家的，还是希望大家能别喷我，哈哈。很多知识只是提及了一下，没有深入，有些是我自己也不清楚，有些是我懒得去展开，如具体IN、OR是怎么个执行，mysql现在内部又是如何对其优化的，我也不知道，所以我建议用union all，这样也是保险起见。在很多时候会发现mysql的优化器很莫名其妙，明明有更好的索引不用，非要用较不合适的索引，这是DBA需要思考的问题，这些问题我也一直在思考，目前最简单的办法就是强制使用索引（force index）

百尺竿头，更进一步，千里之行，始于足下，还是要不断的学习，不断的总结，不断的反思与比较，将学习的知识用于实践，这样才不会老狗熊掰棒子。最后，由于今天是圣诞节，祝广大的猿类们，身体健康，工资越来越高！