SQL优化思路

待我称王封你为后i 2024-02-21 07:17 7阅读 0赞

*  所谓的性能优化，一般针对的是MySQL查询的优化。既然是优化查询，我们自然要先知道查询操作要经过哪些环节，然后思考可以在哪些环节进行优化。
    
    查询操作需要经历的基本环节：
    
    ![图片][2fce4ed5cccc66cb26ddecdf1c1c1e4b.png]SQL查询的环节
    
    下面从5个角度介绍一下MySQL优化的一些策略。
    
    ![图片][cd373fffb55f5058ce36ea942b9c89fd.png]
    
    ### 1. 连接配置优化 ###
    
    处理连接是MySQL客户端和MySQL服务端亲热的第一步，第一步都迈不好，也就别谈后来的故事了。
    
    既然连接是双方的事情，我们自然从服务端和客户端两个方面来进行优化喽。
    
    #### 1.1 服务端配置 ####
    
    服务端需要做的就是尽可能地多接受客户端的连接，或许你遇到过`error 1040: Too many connections`的错误？就是服务端的胸怀不够宽广导致的，格局太小！
    
    ![图片][442fd4df303adaa504a20113826cad35.png]感谢媳妇儿给画的图
    
    我们可以从两个方面解决连接数不够的问题：
    
    1.  增加可用连接数，修改环境变量`max_connections`，默认情况下服务端的最大连接数为`151`个
    
        mysql> show variables like 'max_connections';
        +-----------------+-------+
        | Variable_name   | Value |
        +-----------------+-------+
        | max_connections | 151   |
        +-----------------+-------+
        1 row in set (0.01 sec)
    
    1.  及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点
    
        mysql> show variables like 'wait_timeout';
        +---------------+-------+
        | Variable_name | Value |
        +---------------+-------+
        | wait_timeout  | 28800 |
        +---------------+-------+
        1 row in set (0.01 sec)
    
    > MySQL有非常多的配置参数，并且大部分参数都提供了默认值，默认值是MySQL作者经过精心设计的，完全可以满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。
    
    #### 1.2 客户端优化 ####
    
    客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个SQL语句都创建个新连接，服务端和客户端的资源都吃不消啊。
    
    解决的方案就是使用**连接池**来复用连接。
    
    常见的数据库连接池有`DBCP`、`C3P0`、阿里的`Druid`、`Hikari`，前两者用得很少了，后两者目前如日中天。
    
    但是需要注意的是连接池并不是越大越好，比如`Druid`的默认最大连接池大小是8，`Hikari`默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么？
    
    对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。
    
    Hikari官方给出了一个`PostgreSQL`数据库连接池大小的建议值公式，`CPU核心数*2+1`。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的，大家面试的时候可以吹一吹。
    
    ### 2. 架构优化 ###
    
    #### 2.1 使用缓存 ####
    
    系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。
    
    如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。
    
    ![图片][c40afbbb31eb6ac9e5005d15909496af.png]缓存的使用
    
    #### 2.2 读写分离（集群、主从复制） ####
    
    项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。
    
    针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为`master`节点，其余节点作为组员，叫做`slave`。用户写数据只往`master`节点写，而读的请求分摊到各个`slave`节点上。这个方案叫做**读写分离**。给组长加上组员组成的小团体起个名字，叫**集群**。
    
    ![图片][b9bdf71e2b3baebe6a5c60487f8d9dcd.png]这就是集群
    
    > 注：很多开发者不满`master-slave`这种具有侵犯性的词汇（因为他们认为会联想到种族歧视、黑人奴隶等），所以发起了一项更名运动。
    > 
    > 受此影响MySQL也会逐渐停用`master`、`slave`等术语，转而用`source`和`replica`替代，大家碰到的时候明白即可。
    
    使用集群必然面临一个问题，就是多个节点之间怎么保持数据的一致性。毕竟写请求只往`master`节点上发送了，只有`master`节点的数据是最新数据，怎么把对`master`节点的写操作也同步到各个`slave`节点上呢？
    
    **主从复制**技术！
    
    `binlog`是实现MySQL主从复制功能的核心组件。`master`节点会将所有的写操作记录到binlog中，`slave`节点会有专门的I/O线程读取`master`节点的binlog，将写操作同步到当前所在的`slave`节点。
    
    ![图片][54d9275f347fc48b012caf2babd125a9.png]主从复制
    
    这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据啊，单表查询性能差，说的自然也是所有节点性能都差。
    
    这时我们可以把单个节点的数据分散到多个节点上进行存储，这就是**分库分表**。
    
    #### 2.3 分库分表 ####
    
    分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库；如果把单张表作为节点，那就是分表。
    
    大家都知道分库分表分成垂直分库、垂直分表、水平分库和水平分表，但是每次都记不住这些概念，我就给大家详细说一说，帮助大家理解。
    
    ##### 2.3.1 垂直分库 #####
    
    ![图片][c21dd243a5bcf6919e4f75754618c596.png]垂直分库
    
    在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这就是**垂直分库**啦。
    
    ![图片][92852d933d05ebf30ac369e9e5d57a5d.png]垂直分库
    
    ##### 2.3.2 垂直分表 #####
    
    ![图片][0173f0e2c16fc108d7fe668bd98ad07c.png]垂直分表
    
    垂直分表就是在单表的基础上垂直切一刀（或几刀），将一个表的多个字短拆成若干个小表，这种操作需要根据具体业务来进行判断，通常会把经常使用的字段（热字段）分成一个表，不经常使用或者不立即使用的字段（冷字段）分成一个表，提升查询速度。
    
    ![图片][7cc594071186294ab143cef5ff2b1e98.png]垂直分表
    
    拿上图举例：通常情况下商品的详情信息都比较长，而且查看商品列表时往往不需要立即展示商品详情（一般都是点击详情按钮才会进行显示），而是会将商品更重要的信息（价格等）展示出来，按照这个业务逻辑，我们将原来的商品表做了垂直分表。
    
    ##### 2.3.3 水平分表 #####
    
    把单张表的数据按照一定的规则（行话叫分片规则）保存到多个数据表上，横着给数据表来一刀（或几刀），就是**水平分表**了。
    
    ![图片][89b45fbe40163aaf262055b9fd3d8533.png]水平分表
    
    ![图片][d1ada3ef746c1c34225dcec7129b7bf5.png]水平分表
    
    ##### 2.3.4 水平分库 #####
    
    **水平分库**就是对单个数据库水平切一刀，往往伴随着水平分表。
    
    ![图片][32e90dfa1afe93bd5438ae3836b1a33d.png]水平分库
    
    ![图片][c11d00160f7bb5fb9a17f2fdf2340533.png]水平分库
    
    ##### 2.3.5 总结 #####
    
    **水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。**
    
    #### 2.4 消息队列削峰 ####
    
    通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。
    
    这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。
    
    ![图片][388d8a22799e2805563ae98faa85f43b.png]队列削峰
    
    ### 3. 优化器——SQL分析与优化 ###
    
    处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。
    
    只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为**慢查询**。
    
    #### 3.1 慢查询 ####
    
    **慢查询**就是执行地很慢的查询（这句话说得跟废话似的。。。），只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。
    
    因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态
    
        mysql> show variables like 'slow_query%';
        +---------------------+--------------------------------------+
        | Variable_name       | Value                                |
        +---------------------+--------------------------------------+
        | slow_query_log      | OFF                                  |
        | slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |
        +---------------------+--------------------------------------+
        2 rows in set (0.00 sec)
    
    `slow_query_log`表示当前慢查询日志是否开启，`slow_query_log_file`表示慢查询日志的保存位置。
    
    除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是`10S`，如果改成`0`的话就是记录所有的SQL。
    
        mysql> show variables like '%long_query%';
        +-----------------+-----------+
        | Variable_name   | Value     |
        +-----------------+-----------+
        | long_query_time | 10.000000 |
        +-----------------+-----------+
        1 row in set (0.00 sec)
    
    ##### 3.1.1 打开慢日志 #####
    
    有两种打开慢日志的方式
    
    1.  修改配置文件`my.cnf`
    
    此种修改方式系统重启后依然有效
    
        # 是否开启慢查询日志
        slow_query_log=ON
        # 
        long_query_time=2
        slow_query_log_file=/var/lib/mysql/slow.log
    
    1.  动态修改参数（重启后失效）
    
        mysql> set @@global.slow_query_log=1;
        Query OK, 0 rows affected (0.06 sec)
        
        mysql> set @@global.long_query_time=2;
        Query OK, 0 rows affected (0.00 sec)
    
    ##### 3.1.2 慢日志分析 #####
    
    MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具`mysqldumpslow`，为了演示这个工具，我们先构造一条慢查询：
    
        mysql> SELECT sleep(5);
    
    然后我们查询用时最多的1条慢查询：
    
        [root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log
        
        Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log
        Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
          SELECT sleep(N)
    
    其中，
    
     *  **Count**：表示这个SQL执行的次数
     *  **Time**：表示执行的时间，括号中的是累积时间
     *  **Locks**：表示锁定的时间，括号中的是累积时间
     *  **Rows**：表示返回的记录数，括号中的是累积数
    
    更多关于`mysqldumpslow`的使用方式，可以查阅官方文档，或者执行`mysqldumpslow --help`寻求帮助。
    
    #### 3.2 查看运行中的线程 ####
    
    我们可以运行`show full processlist`查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。
    
    ![图片][8042310414732cb6c71caca6f5070267.png]image-20220405182328247
    
    其中，
    
     *  **Id**：线程的唯一标志，可以使用Id杀死指定线程
     *  **User**：启动这个线程的用户，普通账户只能查看自己的线程
     *  **Host**：哪个ip和端口发起的连接
     *  **db**：线程操作的数据库
     *  **Command**：线程的命令
     *  **Time**：操作持续时间，单位秒
     *  **State**：线程的状态
     *  **Info**：SQL语句的前100个字符
    
    #### 3.3 查看服务器运行状态 ####
    
    使用`SHOW STATUS`查看MySQL服务器的运行状态，有`session`和`global`两种作用域，一般使用`like+通配符`进行过滤。
    
        -- 查看select的次数
        mysql> SHOW GLOBAL STATUS LIKE 'com_select';
        +---------------+--------+
        | Variable_name | Value  |
        +---------------+--------+
        | Com_select    | 168241 |
        +---------------+--------+
        1 row in set (0.05 sec)
    
    #### 3.4 查看存储引擎运行信息 ####
    
    `SHOW ENGINE`用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。
    
    例如：
    
        SHOW ENGINE INNODB STATUS;
    
    上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。
    
    #### 3.5 EXPLAIN执行计划 ####
    
    通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？
    
    MySQL提供了一个执行计划的查询命令`EXPLAIN`，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询…）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么，等等等等。
    
    > EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。
    
    这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明`EXPLAIN`的细节。
    
    #### 3.6 SQL与索引优化 ####
    
    ##### 3.6.1 SQL优化 #####
    
    SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：
    
     *  使用小表驱动大表；用join改写子查询；or改成union
     *  连接查询中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本；被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低
     *  大偏移量的limit，先过滤再排序
    
    针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB），大家感受一下：
    
        -- 1. 大偏移量的查询
        mysql> SELECT * FROM user_innodb LIMIT 9000000,10;
        Empty set (8.18 sec)
        
        -- 2.先过滤ID（因为ID使用的是索引），再limit
        mysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;
        Empty set (0.02 sec)
    
    ##### 3.6.2 索引优化 #####
    
    为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。
    
    ### 4. 存储引擎与表结构 ###
    
    #### 4.1 选择存储引擎 ####
    
    一般情况下，我们会选择MySQL默认的存储引擎存储引擎`InnoDB`，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。
    
    建议根据不同的业务选择不同的存储引擎，例如：
    
     *  查询操作、插入操作多的业务表，推荐使用`MyISAM`；
     *  临时表使用`Memory`；
     *  并发数量大、更新多的业务选择使用`InnoDB`；
     *  不知道选啥直接默认。
    
    #### 4.2 优化字段 ####
    
    字段优化的最终原则是：**使用可以正确存储数据的最小的数据类型**。
    
    ##### 4.2.1 整数类型 #####
    
    MySQL提供了6种整数类型，分别是
    
     *  **tinyint**
     *  **smallint**
     *  **mediumint**
     *  **int**
     *  **integer**
     *  **bigint**
    
    不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。
    
    例如，是否被删除的标识，建议选用`tinyint`，而不是`bigint`。
    
    ##### 4.2.2 字符类型 #####
    
    你是不是直接把所有字符串的字段都设置为`varchar`格式了？甚至怕不够，还会直接设置成`varchar(1024)`的长度？
    
    如果不确定字段的长度，肯定是要选择`varchar`，但是`varchar`需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用`char`，这会给你节约不少的内存空间。
    
    ##### 4.2.3 非空 #####
    
    非空字段尽量设置成`NOT NULL`，并提供默认值，或者使用特殊值代替`NULL`。
    
    因为`NULL`类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。
    
    ##### 4.2.4 不要用外键、触发器和视图功能 #####
    
    这也是「阿里巴巴开发手册」中提到的原则。原因有三个：
    
    1.  降低了可读性，检查代码的同时还得查看数据库的代码；
    2.  把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
    3.  数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。
    
    ##### 4.2.5 图片、音频、视频存储 #####
    
    不要直接存储大文件，而是要存储大文件的访问地址。
    
    ##### 4.2.6 大字段拆分和数据冗余 #####
    
    **大字段拆分**其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写`SELECT *`的情况下，列数多和数据量大导致的问题会被严重放大！
    
    **字段冗余**原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。
    
    ### 5. 业务优化 ###
    
    严格来说，业务方面的优化已经不算是MySQL调优的手段了，但是业务的优化却能非常有效地减轻数据库访问压力，这方面一个典型例子就是淘宝，下面举几个简单例子给大家提供一下思路：
    
    1.  以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式丛出不穷，这种方式叫做**预售分流**。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单；
    2.  双十一的凌晨你或许想查询当天之外的订单，但是却查询失败；甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种**降级策略**，集结不重要的服务的计算资源，用来保证当前最核心的业务；  
        储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。
    
    ### 5. 业务优化 ###
    
    严格来说，业务方面的优化已经不算是MySQL调优的手段了，但是业务的优化却能非常有效地减轻数据库访问压力，这方面一个典型例子就是淘宝，下面举几个简单例子给大家提供一下思路：
    
    1.  以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式丛出不穷，这种方式叫做**预售分流**。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单；
    2.  双十一的凌晨你或许想查询当天之外的订单，但是却查询失败；甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种**降级策略**，集结不重要的服务的计算资源，用来保证当前最核心的业务；
    3.  双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，需要调用银行接口，相比之下操作要慢了许多。

[2fce4ed5cccc66cb26ddecdf1c1c1e4b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/7ffaa018360142b4a73d9cc5bfa93c53.png
[cd373fffb55f5058ce36ea942b9c89fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/77e9dd19213d46b59e3ea4c200c9184b.png
[442fd4df303adaa504a20113826cad35.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/f6a2d54061794a20998beac196c3ff07.png
[c40afbbb31eb6ac9e5005d15909496af.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/c25a0c408c554f3ba71e290eaa7a4e50.png
[b9bdf71e2b3baebe6a5c60487f8d9dcd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/e5700bdcfdef4444bf9789f92c54f434.png
[54d9275f347fc48b012caf2babd125a9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/eb502f70b93741859b32fcda9a230d87.png
[c21dd243a5bcf6919e4f75754618c596.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/c442617c398d431794a4150df720419e.png
[92852d933d05ebf30ac369e9e5d57a5d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/4ccb7b115f5e427ab57d84ccfbb137bd.png
[0173f0e2c16fc108d7fe668bd98ad07c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/60153a94249749d4bdbb67124880f546.png
[7cc594071186294ab143cef5ff2b1e98.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/2c11c749fc804e6383d693ab9e95536d.png
[89b45fbe40163aaf262055b9fd3d8533.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/f9b4f8673c8144c0ad969328ce148ec8.png
[d1ada3ef746c1c34225dcec7129b7bf5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/5f5493d60ddc45ddbb0b4564f7918b94.png
[32e90dfa1afe93bd5438ae3836b1a33d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/190b00f124744e648e26db3578e07e7b.png
[c11d00160f7bb5fb9a17f2fdf2340533.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/6a17781a1e104e62b40e8feafb86f24d.png
[388d8a22799e2805563ae98faa85f43b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/176918d8fda14a37823a7f5f62d4228c.png
[8042310414732cb6c71caca6f5070267.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/eadb9ce6917848b68925425205682c67.png