SQL阶段性优化

素颜马尾好姑娘i 2024-04-23 13:00 30阅读 0赞

>  *  ?**作** **者**：[是江迪呀][Link 1]
>  *  ✒️**本文关键词**：`MySQL`、`SQL优化`、`阶段性优化`
>  *  ☀️**每日 一言**：`我们要把懦弱扼杀在摇篮中。`

#### 文章目录 ####

*  一、前言
 *  二、数据准备
 *  三、阶段一：从代码和业务场景方面优化
 *   *  （1）不要写过于复杂的SQL
     *  （2）分页查询优化
     *  （3）避免大事务
 *  四、阶段二：从SQL方面优化
 *   *   *  （1）避免使用子查询
         *  （2）避免使用SELECT \*
         *  优化建议：
         *  （3）避免使用函数
         *   *  优化建议：
 *  五、阶段三：从数据库索引方面优化
 *   *   *  （1）使用索引
         *  （2） 索引类型以及使用场景：
         *  （3）如何SQL查询是否走索引
 *  六、阶段四：从缓存方面优化
 *   *   *  （1）使用数据库自带的缓存机制
         *   *  数据库自带缓存弊端：
         *  （2）使用Redis或者Memcached
         *   *  中间件缓存弊端：
 *  七、 阶段五：从数据库设计方面优化
 *   *   *  （1）合理设计数据库
         *  （2）使用合适的数据库类型
         *  （3）分区分表
         *   *  分区表注意事项：
         *  （4）分库分表
 *  八、阶段六：从硬件方面优化
 *   *   *  （1）升级硬件

![在这里插入图片描述][61b10620f513478581396e7d05585b4f.png_pic_center]

## 一、前言 ##

我们在做系统的过程中，难免会遇到页面查询速度慢，性能差的问题，当遇到这些问题时，我们就不得不想到`SQL优化`。`SQL优化`可以有效的提升系统的性能提高用户的体验，降低资源消耗比如CPU的使用率、内存消耗的情况，从而能够支持更大规模的数据。但是`SQL优化`是要`分阶段`的，只有在某个阶段中优化遇到了瓶颈，才会进入下个阶段寻找优化的点。

## 二、数据准备 ##

如果你没有大数据量进行测试，可以通过下面的存储过程生成白玩甚至是千万级别的数据量。代码如下：

-- 创建班级表
    CREATE TABLE class (
        class_id INT PRIMARY KEY,
        class_name VARCHAR(50) NOT NULL
    );
    
    -- 创建课程表
    CREATE TABLE course (
        course_id INT PRIMARY KEY,
        course_name VARCHAR(50) NOT NULL
    );
    
    -- 创建学生表，并将班级表和课程表作为外键关联
    CREATE TABLE student (
        student_id INT PRIMARY KEY,
        student_name VARCHAR(50) NOT NULL,
        age INT,
        gender VARCHAR(10),
        class_id INT,
        course_id INT,
        create)time DATETIME,
        FOREIGN KEY (class_id) REFERENCES class(class_id),
        FOREIGN KEY (course_id) REFERENCES course(course_id)
    );

-- 生成100w数据的存储过程
    CREATE PROCEDURE InsertTestData2()
    BEGIN
        DECLARE i INT DEFAULT 1;
        WHILE i <= 1000000 DO
            INSERT INTO
                class (class_id, class_name)
            VALUES
                (i, CONCAT('班级',i));
            INSERT INTO
            	course (course_id, class_name)
            VALUES
    	        (i,CONCAT('班级',i));
    
            INSERT INTO
                student (student_id, student_name, age, gender,class_id,course_id)
            VALUES
                (i, CONCAT('姓名',i), FLOOR(RAND() * 4) + 15, RAND(), i, i);
            SET i = i + 1;
        END WHILE;
    END

## 三、阶段一：从代码和业务场景方面优化 ##

这个阶段最为重要，也是可以优化的地方最多的。

### （1）不要写过于复杂的SQL ###

**示例：**

当产品要求在一个学生信息页面中需要展示以下列：学生姓名、性别、班级、课程，那你是不是要这样写SQL：

select 
    	s.student_name,
    	s.gender ,
    	c.class_name ,
    	c2.course_name 
    from student s
    left join class c on s.class_id = c.class_id 
    left join course c2 on c2.course_id = s.course_id

**分析原因：**

上面的SQL没有一点问题，但是随着`student`表中的数据日益增加SQL查询必定变慢，更何况这只是关联**三张表**，遇到更加复杂的业务场景甚至要关联1**0几张表**，这样查询效率是很慢的。

**优化建议：**

将关联的表拆分开，使用`代码`处理关联关系。比如，你先查询出`student`表中的数据，拿着`class_id`去查询`class`表，再拿着`course_id`去查询`course`表中的数据，最后将数据组装起来返回。

这样做的好处如下：

*  一是可以减少关联的表，提高SQL查询的性能。
 *  二是可以提高`SQL的可读性`，如果写个十几二十行的SQL再加上子查询啥的，我感觉维护的人一定背地里骂娘。

### （2）分页查询优化 ###

**示例：**  
如果产品要求页面都是需要分页的，并且或根据某个字段进行排序，SQL可能是这样的：

select 
    	s.student_name,
    	s.gender ,
    	c.class_name ,
    	c2.course_name 
    from student s
    left join class c on s.class_id = c.class_id 
    left join course c2 on c2.course_id = s.course_id
    order by s.create_time DESC 
    limit 10

我就要开始优化啦。什么的代码依然是没什么问题，但是如果三张表中的数据都是百万级别的，那就说不好了，查询会很慢。

**分析原因：**

上面查询之所以会慢主要有一下几点：

*  关联的表太多。
 *  数据量太大，每个表都是全表扫描。
 *  排序，`order by`的执行顺序实在`limit`之前的，故而是`全表数据`进行`排序`后，再进行截取分页。

**优化建议：**

*  将关联的表拆分开来，先查询出`student`表中的数据，拿着`class_id`去查询`class`表，再拿着`course_id`去查询`course`表中的数据，最后将数据组装起来返回。这样做在查询`student`表时，字段可以减少很多。拿着`course_id`后续查询的表只会查询10条，查询压力会小很多。
 *  切记不要在`student`表中进行`order by`排序，使用`代码`进行排序，代码排序只会处理`limit`后的`10条数据`，性能会提高很多。

select 
    	s.student_id,
    	s.student_name,
    	s.class_id,
    	s.gender 
    from student s
    limit 10

代码进行排序

select 
    	c.class_name
    from class 
    where class_id = ${class_id}

select 
    	c.course_name 
    from course 
    where course_id = ${course_id}

### （3）避免大事务 ###

在某些业务场景中，我们需要保证业务的原子性，比如一个场景：  
用户下单商品，并使用优惠券增加积分，假设此业务需要操作四张表订单、库存、优惠券、积分。在处理业务逻辑时不应当将四个表的操作都揉成一个原子性操作，而应该是订单和减库存保持一个原子性操作，减去优惠券和增加积分是一个原子性操作，保证事务的最小粒度，避免锁定过多的资源。（这里场景只是举个例子，实际上订单、库存、优惠券、积分应该在不同的系统）

## 四、阶段二：从SQL方面优化 ##

#### （1）避免使用子查询 ####

**子查询弊端：**

*  多次执行：子查询通常会在每次主查询的行被处理时执行一次，这可能导致多次查询操作，增加数据库的负担。
 *  循环执行：子查询会导致主表扫描多次。

**优化建议：**

*  使用连接查询，使用join进行查询。
 *  在一些情况下，可以使用`EXISTS`或`NOT EXISTS`子查询来替代`IN`或`NOT IN`子查询，因为它们更有效。

**为什么使用EXISTS 或 NOT EXISTS 子查询来替代 IN 或 NOT IN 子查询可以提高性能？**

*  **处理 NULL 值：** `NOT IN` 子查询中如果存在 `NULL` 值，会导致整个 `NOT IN`条件无法判断。而 `NOT EXISTS`并不会受到 `NULL` 值的影响，因为它只关心子查询是否返回结果。
 *  **性能优化：** `NOT IN` 子查询可能需要对外部查询中的每个值与子查询中的每个值进行比较，这可能导致大量的比较操作，尤其是在子查询的结果集较大时。而 `NOT EXISTS`子查询在找到第一个匹配项后就会停止比较，这可以提高效率。
 *  **查询优化器的作用：** 数据库查询优化器在处理 `NOT EXISTS`时通常会使用半连接(`semi-join`)或反连接(`anti-join`)的方式，而在处理 `NOT IN`时可能会选择更简单的方式，这会导致性能下降。

#### （2）避免使用SELECT \* ####

**select \* 弊端：**

*  **降低索引利用率：** 会导致数据库无法充分利用索引。
 *  **增加磁盘IO：** 查询的数据量越大，需要从磁盘读取的数据也越多。如果只查询需要的列，可以减少磁盘IO，提高查询速度。
 *  **降低缓存效率：** 数据库系统在执行查询时会使用缓存来存储查询结果，如果查询的数据量过大，会降低缓存的效率，影响其他查询的性能。
 *  **减低数据库安全性：** 查询数据库的所有字段并返回到客户端，很容易被看储底层数据库结构设计。

#### 优化建议： ####

*  业务场景中按需返回数据库字段。

#### （3）避免使用函数 ####

**SQL：中使用函数的弊端：**

*  **函数计算的开销：** 使用函数会导致数据库对每一行数据进行计算，这会增加查询的计算开销。特别是在大数据表中，频繁使用函数可能会导致性能下降。
 *  **索引无法使用：** 在查询条件中使用函数会导致数据库无法充分利用索引。通常情况下，索引是在列上建立的，而不是在函数的结果上。如果在查询条件中使用函数，数据库可能无法使用索引，从而影响查询性能。
 *  **查询执行计划变得复杂**： 使用函数可能会导致查询的执行计划变得更加复杂，从而增加了数据库优化器的工作量，降低了查询性能。
 *  **难以阅读和维护：** 降低SQL的可读性，有些时候系统维护人员并不知道该函数是用来干什么的。

##### 优化建议： #####

*  可以使用代码来替代并实现SQL函数的功能。

## 五、阶段三：从数据库索引方面优化 ##

#### （1）使用索引 ####

数据库索引是一种数据结构，用于加快数据库表中数据的检索速度。索引可以类比于书的目录，它提供了一种快速访问表中特定数据行的方法，从而避免全表扫描，提高查询效率。

#### （2） 索引类型以及使用场景： ####

1.  **B树索引（B-Tree Index最常用）：**
2.  作用：用于加速等值查询、范围查询和排序操作。
3.  使用场景：适合大多数查询，特别是在查询中涉及到等值匹配、范围查询、排序和分组。
4.  **唯一索引（Unique Index）：**
5.  作用：保证索引列的唯一性，避免重复数据。
6.  使用场景：适合需要确保某一列的值是唯一的情况，如主键、唯一约束等。
7.  **主键索引（Primary Key Index）：**
8.  作用：是一种唯一索引，同时标识了表的主键。
9.  使用场景：每个表应该有一个主键索引，用于唯一标识表中的每一行数据。
10. **全文索引（Full-Text Index）：**
11. 作用：用于全文搜索，查找文本中的关键字。
12. 使用场景：适合文本字段的模糊搜索，如文章内容、评论等。
13. \*\* 哈希索引（Hash Index）：\*\*
14. 作用：用于等值查询，通过哈希函数映射到索引值。
15. 使用场景：适合精确匹配的查询，不适用于范围查询。
16. **空间索引（Spatial Index）：**
17. 作用：用于地理信息和几何数据的查询。
18. 使用场景：适合存储包含空间坐标信息的数据，如地图数据。
19. **前缀索引（Prefix Index）：**
20. 作用：用于索引列的前缀，节省存储空间。
21. 使用场景：适合较长的字符串列，但可能影响查询性能。
22. **联合索引（Composite Index）：**
23. 作用：多个列上的组合索引，加速组合查询。
24. 使用场景：适合多个列的联合查询，但顺序很重要，查询只能使用索引的最左前缀。

#### （3）如何SQL查询是否走索引 ####

使用 `EXPLAIN` 命令可以分析查询语句的执行计划，以便了解查询是如何被优化和执行的。`EXPLAIN`查询会返回一张表，其中包含了查询优化器的执行计划，以及每个步骤的详细信息。

1.  **id：** 表示查询中每个操作步骤的标识，主要用于标识操作的执行顺序。
2.  **select\_type：** 表示操作的类型，如 `SIMPLE`（简单查询）、`PRIMARY`（主查询）、`SUBQUERY`（子查询）、`DERIVED`（派生表的子查询）等。
3.  **table：** 表示涉及的表名，如果涉及多个表，则会依次显示。
4.  **partitions：** 表示查询使用的分区。
5.  **type：** 表示表访问的类型，常见的类型有：
    
     *  ALL： 全表扫描，效率最低。
     *  index： 通过索引进行扫描。
     *  range： 使用索引范围进行扫描。
     *  ref： 使用非唯一索引来查找匹配行。
     *  eq\_ref： 类似于`ref`，但是使用的是唯一索引。
     *  const： 使用常量条件进行查询。
     *  system： 查询表只有一行，快速访问。
     *  NULL： 没有表，结果为空。
     *  possible\_keys： 显示可能应用在这张表中的索引。
6.  **key：** 显示实际使用的索引。如果为 `NULL`，则没有使用索引。
7.  **key\_len：** 表示索引字段的最大长度。
8.  **ref：** 显示连接匹配条件的列，如果没有，则为 `NUL`L。
9.  **rows：** 表示预计需要扫描的行数，越小越好。
10. **filtered：** 表示根据表连接条件和 `where` 子句中的条件，过滤的行的百分比。
11. **Extra：** 额外的信息，可能包括 “**Using filesort**”（使用文件排序）、“**Using temporary**”（使用临时表）等。

## 六、阶段四：从缓存方面优化 ##

#### （1）使用数据库自带的缓存机制 ####

*  查询缓存： **MySQL** 查询缓存是一种基于 **SQL** 查询的结果的缓存机制，它可以缓存完全相同的查询语句的结果集。

query_cache_type = 1  # 开启查询缓存
    query_cache_type = 0  # 关闭查询缓存

*  键缓存： **MySQL** 使用键缓存来存储数据字典中的表、索引和列名等信息，这样可以在查询时更快地查找元数据。键缓存是自动开启的，不需要手动配置。
 *  缓存索引： **MySQL** 在 **InnoDB** 存储引擎中提供了缓存索引的功能，它可以将索引数据存储在内存中，从而加速索引查找。你可以通过配置参数进行设置：

innodb_buffer_pool_size = 128M  # 设置 InnoDB 缓存池大小

##### 数据库自带缓存弊端： #####

*  查询缓存：查询缓存的使用在实际情况中往往不太推荐，因为它在高并发环境下可能会带来锁竞争，同时对于稍微复杂的查询和更新操作，可能会导致整个查询缓存被清空。
 *  缓存索引：增加磁盘压力。

#### （2）使用Redis或者Memcached ####

使用中间件将热点数据存放在缓存中，当查询来临时将缓存数据返回，从而避免直接打到数据库，减轻数据库压力。这种方式是我们在实际开发中最长用的方式，在此不过多赘述。

##### 中间件缓存弊端： #####

*  数据一致性保证。
 *  缓存穿透。
 *  缓存失效时间合理设置。

## 七、 阶段五：从数据库设计方面优化 ##

#### （1）合理设计数据库 ####

如何合理的设计数据库是个非常有技术含量的工作，我从一个简单的场景入手说下：假如你需要开发一个博客网站，那么你如何设计数据库用于博客内容的存储呢？拿一些`博客网站`来说，页面先展示`文章标题`、`浏览量`、`点赞量`，点击文章后进入文章详情，展示具体的内容。  
那么我们在设计数据库时就应该想到，将`标题`、`浏览量`、`点赞量`放到一张表中去，文章内容单独一张表，文章内容由于很多查询消耗的性能比较大，所以我们应该避免。  
通过一个简单的例子说明下数据库设计对数据库优化起着至关重要的作用，设计的拉胯，再高超的优化手段也无济于事。

#### （2）使用合适的数据库类型 ####

使用合适的数据库类型是优化 **MySQL** 数据库性能的一个重要方面，不同的数据类型在存储、查询和计算方面会有不同的影响。

1.  **选择合适的整数类型：** 当存储整数值时，应该根据数值的范围来选择合适的整数类型，以节省存储空间和提高查询性能。例如，如果某个字段的取值范围在`0`到 `255`之间，可以选择使用`TINYINT UNSIGNED`类型来存储，而不是使用`INT`类型。
2.  **选择合适的浮点数类型：** 如果需要存储小数，可以根据精度的要求选择合适的浮点数类型。`FLOAT` 和`DOUBLE` 类型可以存储更大范围的数值，但是会占用更多的存储空间。应根据业务需要权衡存储空间和精度。
3.  **避免使用不必要的文本类型：** 对于存储长度固定的文本数据，可以使用`CHAR`类型，而对于长度可变的文本数据，可以使用 VARCHAR 类型。避免使用过大的文本类型，因为它们可能导致额外的存储开销和查询性能下降。
4.  **使用适当的日期和时间类型：** 选择合适的日期和时间类型来存储日期和时间信息，例如`DATE`、`TIME`、`DATETIME` 和 `TIMESTAMP`。使用`TIMESTAMP`类型可以充分利用 **MySQL** 的自动更新特性，但要注意其范围。
5.  **选择合适的枚举类型：** 当某个字段的取值在一个固定的集合内时，可以使用枚举类型。这不仅节省存储空间，还可以提高查询性能，因为查询和索引枚举类型字段通常更高效。
6.  **使用合适的字符集和排序规则：** 对于需要支持多语言的应用，应选择合适的字符集和排序规则。例如，`utf8mb4`字符集支持更广泛的字符范围，但也会占用更多的存储空间。

#### （3）分区分表 ####

分区表是一种将数据库表按照某个规则划分为多个子表的技术，可以提高查询性能、管理效率以及数据维护。实现步骤如下：

1.  **选择分区键：** 分区键是用来划分表的依据，通常选择一个与业务相关的字段作为分区键，例如日期、地区、用户等。选择合适的分区键可以使查询在特定范围内更加高效。
2.  **分区类型：** MySQL 提供了多种分区类型，包括范围分区、列表分区、哈希分区和键值分区。选择分区类型要根据业务需求和查询模式来确定。
3.  **创建分区表：** 在创建表的时候，使用 PARTITION BY 子句来指定分区方式和分区键。例如，以下是一个按照日期范围分区的示例：
4.  **管理分区表：** 分区表需要特殊的管理和维护。例如，当数据增长时，可能需要添加新的分区。可以使用 ALTER TABLE 命令来添加分区。
5.  **查询分区表：** 查询分区表时，数据库会根据查询条件自动选择需要扫描的分区，从而提高查询性能。

下面就是一个根据日期进行分区的表（**切记分区表是一张表分为多个不同的区域，可不是多张表**）：

CREATE TABLE sales (
        sale_id INT AUTO_INCREMENT,
        sale_date DATE,
        sale_amount DECIMAL(10, 2),
        PRIMARY KEY (sale_id, sale_date)
    )
    PARTITION BY RANGE (YEAR(sale_date)) (
        PARTITION p0 VALUES LESS THAN (2020),
        PARTITION p1 VALUES LESS THAN (2021),
        PARTITION p2 VALUES LESS THAN (2022)
    );

##### 分区表注意事项： #####

*  分区表的索引也需要按照分区键来创建，以保证查询性能。
 *  需要根据业务需求来选择合适的分区策略和分区类型。
 *  分区表的设计需要考虑数据均衡，避免某个分区数据过多而导致性能问题。

#### （4）分库分表 ####

分库分表是处理大数据量的常用手段，可以提高数据库的扩展性和性能。分库分表的基本思想是将数据分散存储到多个数据库实例或多张数据表中，从而减少单个数据库或表的负载。我们可以使用`MyCat`工具来实现分库分表（如何使用mycat分库分表可以自行百度）。

1.  **数据库水平分库：** 将数据按照一定的规则分散存储到不同的数据库中，每个数据库独立管理。可以使用分片键（如用户ID、时间等）来决定数据存放在哪个数据库中。
2.  **数据库垂直分表：** 将同一个表中的不同列分散到不同的数据表中，从而减小单张表的数据量。例如，将一个订单表分成订单基本信息表和订单明细表（类似于分区，只不过分区是一张表，这是多张）。

## 八、阶段六：从硬件方面优化 ##

#### （1）升级硬件 ####

1.  **增加内存（RAM）：** 内存是数据库性能的重要因素之一，足够的内存可以缓存更多的数据和索引，减少磁盘IO，从而提高查询速度。适用于需要频繁进行数据读取的场景。
2.  **使用快速存储设备：** 使用SSD（固态硬盘）代替传统的机械硬盘可以显著提高数据的读写速度，从而减少IO等待时间。
3.  **提升CPU性能**： 数据库查询过程中会进行大量的计算操作，提升CPU性能可以加快计算速度，从而提高查询效率。、
4.  **优化网络带宽：** 如果数据库涉及跨网络的查询，优化网络带宽和延迟可以减少数据传输时间。
5.  **使用多核处理器**： 如果数据库支持并行查询，使用多核处理器可以同时处理多个查询，提高处理能力。
6.  **分布式架构：** 将数据库分布到多个服务器上，采用分布式架构可以减轻单一服务器的负担，提高整体查询吞吐量。
7.  **使用专用硬件：** 针对特定的任务，如数据分析、数据仓库等，可以选择使用专用的硬件加速器，例如GPU（图形处理器）等。

想`加机器`?这考验的就不是你的技术能力了，而是说话沟通能力，如何说服老板领导同意你升级硬件的方案。

[Link 1]: https://blog.csdn.net/qq_42785250?spm=1011.2124.3001.5343
[61b10620f513478581396e7d05585b4f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/4bfa8925ebf541ada6171b06c5bfd214.png