被问到SQL调优怎么做的？我该如何回答？

本文是基于Mysql所写的一篇总结，欢迎指正。

1. 为什么要SQL调优？

SQL调优，首先调优的目的是什么？当然是SQL慢了。SQL慢又分为两大类，是偶尔慢呢还是一直都很慢。

我们对于Mysql服务器，我们都会有一些监控，比方说阿里云的服务器的话，就可以再arms看到监控，开源的话也可以使用skywalking之类的，很多。

如果sql偶尔很慢，首先要观察抖动，CPU性能之类的，是不是正在回写binlog，还是出现因为加锁导致很慢的情况。

我们常常的sql调优大多数关注的是一直很慢的情况。一直很慢，又该从哪些角度去分析呢？

2. 调优的过程是怎么样的？

2.1 了解表机构

了解表结构是前提，要大概知道这个表的字段，包括其类型，哪些字段建立了索引之类的，基本信息还是要了解下。

哪些可以帮助我们分析优化表结构的措施呢？PROCEDURE analyse。这个可以帮助我们分析表字段和实际的数据，并给出一些有用的建议。当然，只有表中有实际数据，这个建议才有意义，数据量小的时候，可能给出的建议也不大准确，因此，最终决策权还是由你来决定。

select * from order t PROCEDURE analyse(2);

我们可以得到一些优化建议在这个字段：Optimal_fieldtype。

为什么需要关注表结构呢？那就需要知道表结构对性能的影响。

（1）冗余数据的处理

每一列只能有一个值；
每一行可以被唯一的区分；
不包含其他表的已包含的非关键信息

（2）大表拆小表

不要设计属性过多的表
一般不超过500W到1000W数据的表
有大数据的列单独拆为小表

（3）根据需求展示更加合理的表结构，常用属性分离成小表

2.2 explain 我们的select查询

使用explain关键字，可以让我们知道mysql是怎么处理SQL语句的，可以帮助分析查询语句的性能瓶颈。我们可以得知表的索引主键是如何利用的，数据表如何搜索或者排序，扫描行等等。

explain执行后，字段解析

    1，ID：执行查询的序列号；
    2，select_type：使用的查询类型
        1，DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；
        2，DEPENDENT UNION：子查询中的UNION，且为UNION 中从第二个SELECT 开始的后面所有SELECT，同样依赖于外部查询的结果集；
        3，PRIMARY：子查询中的最外层查询，注意并不是主键查询；
        4，SIMPLE：除子查询或者UNION 之外的其他查询；
        5，SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；
        6，UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；
        7，UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY
        8，UNION RESULT：UNION 中的合并结果；
    3，table：这次查询访问的数据表；
    4，type：对表所使用的访问方式：
        1，all：全表扫描
        2，const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；
        3，eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；
        4，fulltext：全文检索，针对full text索引列；
        5，index：全索引扫描；
        6，index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据；
        7，index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；
        8，rang：索引范围扫描；
        9，ref：Join 语句中被驱动表索引引用查询；
        10，ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；
        11，system：系统表，表中只有一行数据；
        12，unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束；
    5，possible_keys：可选的索引；如果没有使用索引，为null；
    6，key：最终选择的索引；
    7，key_len：被选择的索引长度；
    8，ref：过滤的方式，比如const（常量），column（join），func（某个函数）；
    9，rows：查询优化器通过收集到的统计信息估算出的查询条数；
    10，Extra：查询中每一步实现的额外细节信息
        1，Distinct：查找distinct 值，所以当mysql 找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；
        2，Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；
        3，Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；
        4，No tables：Query 语句中使用FROM DUAL 或者不包含任何FROM 子句；
        5，Not exists：在某些左连接中MySQL Query Optimizer 所通过改变原有Query 的组成而使用的优化方法，可以部分减少数据访问次数；
        6，Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的时候；
        7，Using filesort：当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。
        8，Using index：所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据；
        9，Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引即可，而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是Using index for group-by；
        10，Using temporary：当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。
        11，Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where 信息；
        12，Using where with pushed condition：这是一个仅仅在NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开Condition Pushdown 优化功能才可能会被使用。控制参数为engine_condition_pushdown 。

2.3 Profiling:可以用来准确定位一条SQL的性能瓶颈

1，开启profiling：set profiling=1;
2，执行QUERY，在profiling过程中所有的query都可以记录下来；
3，查看记录的query：show profiles；
4，选择要查看的profile：show profile cpu, block io for query 6；

status是执行SQL的详细过程；
Duration：执行的具体时间；
CPU_user：用户CPU时间；
CPU_system：系统CPU时间；
Block_ops_in：IO输入次数；
Block_ops_out：IO输出次数；

profiling只对本次会话有效；

2.4 整个调优的原则

（1）explain 和 profile 入手

（2）永远小结果集驱动大结果集

（3）在索引中完成排序

（4）使用最小的Columns

（5）使用最有效的过滤条件

（6）避免复杂的Join和子查询

3 其他

大多数的sql慢查询来源于SQL不规范导致。下面来聊一些原则：

3.1 尽量使用查询缓存

大多数mysql服务器都会开启查询缓存，当很多相同的查询被执行多次后，mysql数据库引擎将会优化处理，将这些查询结果放到一个缓存中，这样，就不必反复操作表了。

下面举例：

查询缓存不开启：select * from order where create_at >= CURDATE();
查询缓存开启：select * from order where create_at >= ${date}

像now()，rand() 或者其他类似的SQL函数都不会开启查询缓存，因为这些函数的返回是易变的，我们需要就是用一个变量来代替，从而开启缓存。

3.2 针对只需要一行数据的查询，使用 limit 1

当查询我们知道只会有一条结果或者我们只需要结果集中的任一数据的时候，加上limit 1 可以增加性能，因为mysql 数据库引擎会在找到一条数据后停止检索，而不是往后查找下一条符合条件的数据。

举例，当我们判断某个分类下是不是已经存在资源，

效率低：select * from sources where category_id = '100001';
效率高：select 1 from sources where category_id = '100001' limit 1

3.3 千万不要使用order by rand()

原本这么写的意图是想随机打乱返回的数据行，再挑选一条数据，但数据库不得不去执行rand()的时候，很消耗CPU的性能，而且每一行记录都去执行然后排序。

举例：

效率低： select username from user_info order by rand() limit 1
效率高：先 select count(*) from user 得到总数，随机得到中间一个数字，再查询 select username from user_info limit ${rand}, 1

3.4 尽量不使用select *

需要什么字段就取什么字段。* 对数据库底层也会转换成所有字段。

举例：

效率低： select * from user where id = 1000;
效率高： select username from user where id = 1000;

3.5 选择合适的存储引擎

对于Mysql而言，

存储引擎	特点
MyISAM	适合一些需要大量查询的应用，对于大量写操作不是很友好表级锁，不支持行级锁
InnoDB	支持行级锁，写操作比较多的时候，会更优秀；支持事务；
Memory	由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉，生产不建议使用；适用场景：内存临时表，内部表支持hash索引，对复杂操作的加速效果可能有奇效。

3.6 拆分大的delete 和 insert

如果需要去执行一个大的delete或者insert，这两个操作会锁表的，可能会影响线上业务。limit 条件就会是一个好的拆分方法。

之前我就面临过千万级数据的update的操作，肯定不能是一把直接update，我是写了个python脚本，分批次来进行这样的操作。

操作的时候，可以随时停掉，也要注意记录当前操作的下标，方便我们追踪的同时可以在合理的时间继续执行。

3.7 合理拆分表

合理规划表的职能，假如一个表有一百个字段，看着就很恐怖，这个时候可以根据表字段的特性含义、是否常用查询字段等因素来进行垂直差分变成几张表，以降低表的复杂度和字段的数目从而达到优化的目的。

拆分的时候可考虑是否定长。如果表中的所有字段是定长的（即没有varchar, text, blob类型），固定长度的表会提高性能，因为搜索更快，因为固定长度很容易计算出下一个数据的偏移量，如果不是定长的，找到下一个，需要程序找到主键。而且固定长度的表容易被缓存和重建，缺点就是可能会浪费一些空间。

写在最后

当然，还有很多类似mysql 本身不擅长反向查询，我们应该避免 <> 这样的条件；like '%Alice%'不走索引的优化，尽量使用NOT NULL，把ip地址存成UNSIGNED INT诸如此类的，就不赘述了，

推荐阅读阿里的SQL编写规范和开发规范：

SQL编码原则和规范：https://help.aliyun.com/document_detail/98796.html?spm=5176.22414175.sslink.5.5ea56c57wTwCGv

SQL开发规范：https://help.aliyun.com/document_detail/98796.html?spm=5176.22414175.sslink.5.5ea56c57wTwCGv

本篇大致介绍下sql优化的分析过程。之后mysql专题也会继续更新相关的详细内容。

posted @ 2021-08-19 16:00 未知的九月阅读(857) 评论(0) 编辑收藏举报

刷新页面返回顶部

未知的九月