MySQL高级篇知识点——索引优化与查询优化

r囧r小猫 2023-10-06 23:43 53阅读 0赞

#### 目录 ####

*  1.数据准备
 *   *  1.1.建库建表
     *  1.2.创建相关函数
     *  1.3.创建存储过程
     *  1.4.调用存储过程
     *  1.5.删除某表上的索引
 *  2.索引失效案例
 *   *  2.1.全值匹配
     *  2.2.最佳左前缀匹配原则
     *  2.3.主键插入顺序
     *  2.4.计算、函数、类型转换（自动或手动）导致索引失效
     *  2.5 类型转换导致索引失效
     *  2.6.范围条件右边的列索引失效
     *  2.7.不等于（!= 或者<>）索引失效
     *  2.8.is null可以使用索引，is not null无法使用索引
     *  2.9.like 以通配符 % 开头索引失效
     *  2.10.OR 前后存在非索引的列，索引失效
     *  2.11.数据库和表的字符集统一使用utf8mb4
     *  2.12.练习
 *  3.关联查询优化
 *   *  3.1.数据准备
     *  3.2.采用左外连接
     *  3.3.采用内连接
     *  3.4.join 语句原理
     *   *  3.4.1.驱动表和驱动表
         *  3.4.2.Simple Nested-Loop Join（简单嵌套循环连接）
         *  3.4.3.lndex Nested-Loop Join（索引嵌套循环连接）
         *  3.4.4.Block Nested-Loop Join（块嵌套循环连接）
         *  3.4.5.join 小结
         *  3.4.6.Hash Join
 *  4.子查询优化
 *  5.排序优化
 *   *  5.1.问题引入
     *  5.2.优化建议
     *  5.3.测试
     *  5.4.案例实战
     *  5.5.filesort 算法：双路排序和单路排序
     *   *  5.5.1.双路排序 （慢）
         *  5.5.2.单路排序 （快）
         *  5.5.3.结论及引申出的问题
         *  5.5.4.优化策略
 *  6.GROUP BY 优化
 *  7.分页查询优化
 *  8.优先考虑覆盖索引
 *   *  8.1.什么是覆盖索引？
     *  8.2.覆盖索引的优缺点
     *  8.3.案例
 *  9.如何给字符串添加索引
 *   *  9.1.前缀索引
     *  9.2.前缀索引对覆盖索引的影响
 *  10.索引下推
 *   *  10.1.什么是索引下推？
     *  10.2.索引下推的开启与关闭
     *  10.3.ICP 的使用条件
     *  10.4.案例
     *  10.5.开启/关闭索引下推的性能对比
     *   *  10.5.1.准备数据
         *  10.5.2.性能对比
 *  11.普通索引 vs 唯一索引
 *   *  11.1.查询过程
     *  11.2.更新过程
     *  11.3. change buffer的使用场景
 *  12.其它查询优化策略
 *   *  12.1.EXISTS 和 IN 的区分
     *  12.2.COUNT(\*) 与 COUNT(具体字段)效率
     *  12.3.关于 SELECT(\*)
     *  12.4.LIMIT 1 对优化的影响
     *  12.5.多使用 COMMIT
 *  13.淘宝数据库的主键是如何设计的？
 *   *  13.1 自增 ID 的问题
     *  13.2.业务字段做主键
     *   *  13.2.1.选择卡号 (cardno)
         *  13.2.2.选择会员电话或身份证号
     *  13.3.淘宝的主键设计
     *  13.4.推荐的主键设计
     *   *  13.4.1.非核心业务
         *  13.4.2.核心业务

> 本文笔记整理来自尚硅谷视频[https://www.bilibili.com/video/BV1iq4y1u7vj?p=141][https_www.bilibili.com_video_BV1iq4y1u7vj_p_141]，相关资料可在视频评论区进行获取。

（1）都有哪些维度可以进行数据库调优？简言之：  
① 索引失效、没有充分利用到索引——索引建立。  
② 关联查询太多 JOIN（设计缺陷或不得已的需求）——SQL 优化。  
③ 服务器调优及各个参数设置（缓冲、线程数等）——调整 my.cnf。  
④ 数据过多——分库分表。

（2）关于数据库调优的知识点非常分散。不同的 DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里分为三个章节进行细致讲解。虽然 SQL 查询优化的技术有很多，但是大方向上完全可以分成**物理查询优化**和**逻辑查询优化**两大块。  
① 物理查询优化是**通过索引和表连接方式等技术来进行优化**，这里重点需要掌握索引的使用。  
② 逻辑查询优化就是**通过 SQL 等价变换提升查询效率**，直白一点就是说，换一种查询写法执行效率可能更高。

## 1.数据准备 ##

学员表插入 50万条数据， 班级表插入 1万条数据。

### 1.1.建库建表 ###

# 建库
    CREATE DATABASE atguigudb2;
    
    USE atguigudb2;
    
    # 建表
    CREATE TABLE `class` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `className` VARCHAR(30) DEFAULT NULL,
     `address` VARCHAR(40) DEFAULT NULL,
     `monitor` INT NULL ,
     PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
     
    CREATE TABLE `student` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `stuno` INT NOT NULL ,
     `name` VARCHAR(20) DEFAULT NULL,
     `age` INT(3) DEFAULT NULL,
     `classId` INT(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
     # CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

### 1.2.创建相关函数 ###

# 命令开启，允许创建函数设置
    SET GLOBAL log_bin_trust_function_creators=1; 
    
    # 随机产生字符串
    DELIMITER //
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN    
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO  
    SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
    SET i = i + 1;
    END WHILE;
    RETURN return_str;
    END //
    DELIMITER ;
    
    # 用于随机产生多少到多少的编号
    DELIMITER //
    CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
    BEGIN   
    DECLARE i INT DEFAULT 0;  
    SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
    RETURN i;  
    END //
    DELIMITER ;

### 1.3.创建存储过程 ###

# 创建往 stu 表中插入数据的存储过程
    DELIMITER //
    CREATE PROCEDURE  insert_stu(  START INT ,  max_num INT )
    BEGIN  
    DECLARE i INT DEFAULT 0;   
     SET autocommit = 0;    #设置手动提交事务
     REPEAT  #循环
     SET i = i + 1;  #赋值
     INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));  
     UNTIL i = max_num  
     END REPEAT;  
     COMMIT;  #提交事务
    END //
    DELIMITER ;
    
    # 执行存储过程，往 class 表添加随机数据
    DELIMITER //
    CREATE PROCEDURE `insert_class`(  max_num INT )
    BEGIN  
    DECLARE i INT DEFAULT 0;   
     SET autocommit = 0;    
     REPEAT  
     SET i = i + 1;  
     INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));  
     UNTIL i = max_num  
     END REPEAT;  
     COMMIT; 
    END //
    DELIMITER ;

### 1.4.调用存储过程 ###

# 执行存储过程，往 class 表添加 1 万条数据  
    CALL insert_class(10000);
    
    # 执行存储过程，往 stu 表添加 50 万条数据  
    CALL insert_stu(100000,500000);
    
    # 查看数据是否插入成功
    SELECT COUNT(*) FROM class;
    
    SELECT COUNT(*) FROM student;

### 1.5.删除某表上的索引 ###

下面定义了一个删除某库某表上的索引的存储过程，其目的是方便后面的一些操作。

DELIMITER //
    CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
    BEGIN
           DECLARE done INT DEFAULT 0;
           DECLARE ct INT DEFAULT 0;
           DECLARE _index VARCHAR(200) DEFAULT '';
           DECLARE _cur CURSOR FOR  SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname 
           							AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
    #每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
           DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done=2 ;      
    #若没有数据返回,程序继续,并将变量done设为2
            OPEN _cur;
            FETCH _cur INTO _index;
            WHILE  _index<>'' DO 
                   SET @str = CONCAT("drop index " , _index , " on " , tablename ); 
                   PREPARE sql_str FROM @str ;
                   EXECUTE  sql_str;
                   DEALLOCATE PREPARE sql_str;
                   SET _index=''; 
                   FETCH _cur INTO _index; 
            END WHILE;
       CLOSE _cur;
    END //
    DELIMITER ;

## 2.索引失效案例 ##

（1）MySQL 中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。  
① 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。  
② 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

（2）大多数情况下都默认采用 B+ 树来构建索引。只是空间列类型的索引使用 R- 树，并且 MEMORY 表还支持 hash 索引。其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于 **cost 开销 (CostBaseOptimizer)**，它不是基于**规则 (Rule-BasedOptimizer)**，也不是基于**语义**。怎么样开销小就怎么来。另外，**SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。**

### 2.1.全值匹配 ###

（1）系统种经常会出现类似如下的 SQL 语句：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=746;
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=746 AND NAME = 'BJPYyu';

在建立索引之前执行（主要关注消耗的时间，为 0.15s）：  
![在这里插入图片描述][4fef9e44e9c9498aa32cadb7c159f406.png]

（2）建立索引的 3 条语句如下：

CREATE INDEX idx_age ON student(age);
    CREATE INDEX idx_age_classid ON student(age,classId);
    CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

① 先建立索引 idx\_age，然后执行上述语句，根据结果可以看出，索引 idx\_age 已被使用，且消耗的时间为 0.03s，查询速度更快了。

![在这里插入图片描述][819ae839ea2a400ea05b87cd985f2f86.png]

② 再建立索引 idx\_age\_classid，同样执行上述语句，根据结果可以看出，**索引 idx\_age\_classid 已被使用，而之前建立的索引 idx\_age 未被使用，并且消耗的时间更短了**。其原因在于上述 SQL 语句中的查询条件与索引列中的顺序更加匹配，简单来说：  
1）索引 idx\_age 可以匹配 WHERE age=30 AND classId=4 AND NAME = ‘abcd’ 中的 age；  
2）而索引 idx\_age\_classid 则可以匹配 age 和 classId，因此使用该索引可以实现更快的查找。

![在这里插入图片描述][daf0809eb9e54594a3a2931fd2224644.png]

③ 最后建立索引 idx\_age\_classid\_name，同样执行上述语句，根据结果可知，**索引 idx\_age\_classid\_name 已被使用，而之前建立的索引 idx\_age 和 idx\_age\_classid 未被使用，即失效了**。同理，可以分析原因：索引 idx\_age\_classid\_name 中的列 age、classId、NAME 与 WHERE age=30 AND classId=4 AND NAME = ‘abcd’ 完全匹配（包括字段名和顺序），这也称为全值匹配，这种情况下我们比较希望见到的。

![在这里插入图片描述][e05fba8f320b4ff7b091251415acd152.png]

### 2.2.最佳左前缀匹配原则 ###

（1）MySQL可以为多个字段创建索引，一个索引可以包括16个字段。在 MySQL 建立联合索引时会遵守**最佳左前缀匹配原则**：  
① 最左优先，在检索数据时从联合索引的最左边开始匹配。  
② 对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。  
③ 如果查询条件中没有使用这些字段中第 1 个字段时，多列（或联合）索引将会失效。

（2）举例  
① 目前 student 表上有 4 个索引。

SHOW INDEX FROM student;

![在这里插入图片描述][6728d4cc5f214f3dbae82c78f6e60a19.png]

② 下面 SQL 语句中的查询条件涉及的字段依次为：**age、name**。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND name = 'abcd';

![在这里插入图片描述][420cce03f95a492db95e0b2dd5710c17.png]

根据上面的分析可知，**索引 idx\_age 被使用了**，因为 (age, name) 中的 age（从左往右看）正好可以和 idx\_age 中的索引列 age 匹配，因此可以使用该索引。

③ 下面 SQL 语句中的查询条件涉及的字段依次为：**classid、name**。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid=1 AND name = 'abcd';

![在这里插入图片描述][8095ceadc98546ceb62c076deffb1172.png]

根据上面的分析可知，该语句上**没有索引被使用**，因为根据最佳左前缀匹配原则，(classid、name) 无法与已有的任何索引进行匹配（即使优化器对字段顺序进行调整之后）。

④ 下面 SQL 语句中的查询条件涉及的字段依次为：**classid、age、name**。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid=4 AND age=30 AND name = 'abcd';

![在这里插入图片描述][17b9a477124c4da38b3db1cafffdf86e.png]

根据上面的分析可知，**索引 idx\_age\_classid\_name 被使用了**，尽管第一眼看起来，没有一个索引的索引列是以 classid 开始，(classid, age, name) 并不能与已有的索引匹配，但是 **MySQL 中的优化器会对字段顺序进行一定的调整，看能否匹配上已有的索引**，而如果将原本的 (classid, age, name) 调整为 (age, classid, name)，那么此时就可以匹配上索引 idx\_age\_classid\_name！

④ 先删除索引 idx\_age 和 idx\_age\_classid：

DROP INDEX idx_age ON student;
    DROP INDEX idx_age_classid ON student;

下面 SQL 语句中的查询条件涉及的字段依次为：**age、name**。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';

![在这里插入图片描述][681d0d426bc54b0bbb575c39e4d23a1f.png]  
根据上面的分析可知，**索引 idx\_age\_classid\_name 被使用了**，但是 key\_len = 5，即实际使用到的索引长度只有 5 个字节，涉及的所索引列为 age，索引并未被充分利用（有关 key\_len 的介绍可以参考[MySQL高级篇知识点——性能分析工具的使用][MySQL]这篇文章的 6.4.7节）。

其原因在于索引 idx\_age\_classid\_name 的索引列依次为 (age, classid, name)，而上述查询条件涉及的字段依次为 (age、name)，根据最佳左前缀匹配原则，该索引的索引列 classid 被跳过后，其自身以及后面的字段都无法被使用！

### 2.3.主键插入顺序 ###

（1）对于一个使用 `InnoDB` 存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在 `聚簇索引` 的叶子节点的。而记录又存储在数据页中的，数据页和记录又是按照记录 `主键值从小到大` 的顺序进行排序，所以如果我们 `插入` 的记录的 `主键值是依次增大` 的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的`主键值忽小忽大`的话，则可能会造成 `页面分裂` 和 `记录移位`。

（2）假设某个数据页存储的记录已经满了，它存储的主键值在 `1~100` 之间：

![在这里插入图片描述][e40383f478304ced9a2974a6ab4bfe55.png]

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

![在这里插入图片描述][7efa68f8d6c44c3486282f5a69266508.png]

（3）可这个数据页已经满了，再插进来怎么办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。**页面分裂**和**记录移位**意味着什么？意味着： **性能损耗**！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增 ，这样就不会发生这样的性能损耗了。所以我们建议：**让主键具有 AUTO\_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入**，比如： person\_info 表：

CREATE TABLE person_info( 
    id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
    name VARCHAR(100) NOT NULL, 
    birthday DATE NOT NULL, 
    phone_number CHAR(11) NOT NULL, 
    country varchar(100) NOT NULL, 
    PRIMARY KEY (id), 
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number) 
    );

我们自定义的主键列 id 拥有 AUTO\_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

### 2.4.计算、函数、类型转换（自动或手动）导致索引失效 ###

（1）在 student 表上建立索引 **idx\_name** 和 **idx\_sno**。

CREATE INDEX idx_name ON student(name);
    
    CREATE INDEX idx_sno ON student(stuno);

（2）下面的 2 条 SQL 语句查询得到的结果一样（数据顺序可能略有不同），但是第一条语句能够使用索引 idx\_name，而第二条语句由于用到了函数LEFT()，从而导致索引 idx\_name 失效。（**索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就无法使用索引了**）

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
    SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 
    SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

![在这里插入图片描述][f01d991142b94b10b5091bb93070a98a.png]  
![在这里插入图片描述][ce8cc37690a24bbaa1a7b4134f626874.png]

![在这里插入图片描述][685487c9614c41dabd9c084562637c12.png]  
![在这里插入图片描述][0941ecec5d0945d1b01cb0c7f5787bc9.png]

（3）同理，由于下面的第二条 SQL 语句中含有计算（即 stuno + 1），所以它使用不了索引 idx\_sno。

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
    
    EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno + 1 = 900001;

![在这里插入图片描述][fbe6c06b7f484c30ada125510252b7e9.png]  
![在这里插入图片描述][a3742770926d47a883ecf6ef583cec22.png]

### 2.5 类型转换导致索引失效 ###

在 student 表中，name 字段的类型是 VARCHAR(20)，而在下面的第一条 SQL 语句中，NAME = 123 存在隐式类型转换，故导致索引 idx\_name 失效。

# 隐式类型转换
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';

![在这里插入图片描述][14dc0a139d074120abff396e2ae25620.png]  
![在这里插入图片描述][5be6ec26842b4e32bcf841c8de2d581a.png]

### 2.6.范围条件右边的列索引失效 ###

（1）删除数据库 atguigudb2 中 student 表上除主键索引之外的所有索引，防止之前存在的索引对下面的例子产生影响。

SHOW INDEX FROM student;
    
    CALL proc_drop_index('atguigudb2','student');

（2）先创建索引 idx\_age\_classId\_name，然后再执行后面的 SQL 语句。

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
    SHOW INDEX FROM student;
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

![在这里插入图片描述][7d76a2cff5d34708b5c54849764de0fd.png]  
![在这里插入图片描述][6de93bd49993448d88f5d99ec488a62a.png]  
通过上面 EXPLAIN 的执行计划可知，索引 idx\_age\_classId\_name 被使用了，但是没有完全使用！key\_len = 10 就可以说明这一点，索引 idx\_age\_classId\_name 上的三个字段 (age,classId,NAME) 中，只有 age 和 classId 被使用了（其长度均为 5，故 key\_len = 5 + 5 = 10），其原因就在于范围条件右边的列索引失效，即 student.classId>20 是一个范围条件，其右边的索引列 NAME 失效了。

> 此时可能有人会问：如果将 SQL 语句中的 student.classId > 20 与 student.name = ‘abc’ 颠倒顺序，这样范围条件右边就没有索引列了，这样是不是就可以完全使用上索引 idx\_age\_classId\_name 了？  
> 答案是否定的，这样依然不能完全使用上索引 idx\_age\_classId\_name，并且 key\_len 仍然为 10。其原因在优化器会根据已有索引来调整查询条件的顺序，以索引 idx\_age\_classId\_name 为例，其索引列的顺序依次为(age,classId,NAME)，如果查询条件中正好出现了这 3 个字段，那么**优化器会自动调整它们之间的顺序，使其保持与索引列的顺序一致，以便可以使用上索引**。所以单纯地调整 student.classId > 20 与 student.name = ‘abc’ 的顺序是没有用的。  
> 如果想要完全使用使用上索引，可以从以下两个方面进行改进：  
> ① 重新创建索引 idx\_age\_name\_classId，**将需要进行范围查询的索引列放到最后**；  
> ② 修改 SQL 语句，**只对索引中的最后一个索引列进行范围查询，但是该方法限制性太大**；

（3）注意事项：  
**① 创建的联合索引中，务必把范围涉及到的字段写在最后；**  
② 应用开发中应将查询条件（例如：金额查询，日期查询往往都是范围查询）放置 where 语句的最后。

### 2.7.不等于（!= 或者<>）索引失效 ###

CREATE INDEX idx_name ON student(NAME);
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

![在这里插入图片描述][474f5a7bf1714a99943bdafad5eeb5e3.png]

### 2.8.is null可以使用索引，is not null无法使用索引 ###

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

![在这里插入图片描述][ecf3c13431c54ad38f0a295c7762d061.png]  
![在这里插入图片描述][4240e4fa0227403691847bfef5654a83.png]

> 结论：最好在设计数据表的时候就将`字段设置为 NOT NULL 约束`，比如可以将 INT 类型的字段，默认值设置为 0。  
> 拓展：同理，在查询中使用 `not like` 也无法使用索引，只能用全表扫描。

### 2.9.like 以通配符 % 开头索引失效 ###

在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会起作用。只有“%"不在第一个位置，索引才会起作用。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';

![在这里插入图片描述][5bdee1cd6d4f4474b3491834f6bdaf11.png]  
![在这里插入图片描述][c2ac4c7fd3d142cb902747c03e9a82fe.png]

> 拓展：Alibaba《Java开发手册》  
> 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

### 2.10.OR 前后存在非索引的列，索引失效 ###

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。也就是说，**OR前后的两个条件中的列都是索引时，查询中才使用索引。**

SHOW INDEX FROM student;
    CALL proc_drop_index('atguigudb2','student');
    
    # 只有索引 idx_age 
    CREATE INDEX idx_age ON student(age);
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
    
    # 有索引 idx_age 和 idx_cid 
    CREATE INDEX idx_cid ON student(classid);
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

![在这里插入图片描述][856310358d984bf98b3a427adf099773.png]  
![在这里插入图片描述][316d8ec38e184b1593f11f78edc2c857.png]

### 2.11.数据库和表的字符集统一使用utf8mb4 ###

统一使用 utf8mb4（5.5.3版本以上支持），兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

### 2.12.练习 ###

假设存在联合索引 index(a, b, c)，索引列依次为 a, b, c。  
![在这里插入图片描述][f42dfc2179df4bd7909d25eedb415c7c.png]

**一般性建议：**  
① 对于单列索引，尽量选择针对当前 query 过滤性更好的索引；  
② 在选择组合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。  
③ 在选择组合索引的时候，尽量选择能够包含当前 query 中的 where 子句中更多字段的索引。  
④ 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

**总之，书写SQL语句时，尽量避免造成索引失效的情况。**

## 3.关联查询优化 ##

### 3.1.数据准备 ###

use atguigudb2;
    
    # 分类表
    CREATE TABLE IF NOT EXISTS `type` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `card` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
    );
    
    # 图书表
    CREATE TABLE IF NOT EXISTS `book` (
    `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `card` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`bookid`)
    );
    
    # 向分类表中添加 20 条记录
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    
    # 向图书表中添加 20 条记录
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

### 3.2.采用左外连接 ###

（1）type 表和 book 表的字段 card 上均没有索引。

# type 表为驱动表，book 表为被驱动表
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

![在这里插入图片描述][2b89ad1bfab044d5970e7046c4450349.png]

（2）为被驱动表 book 中的字段 card 添加索引。

CREATE INDEX Y ON book(card);
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

![在这里插入图片描述][870e9d732d624a258afb2c7549a75311.png]

（3）为驱动表 type 中的字段 card 也添加索引。

CREATE INDEX X ON `type`(card);
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

![在这里插入图片描述][79238057118e485dab32a2a41848ea09.png]

（4）删除被驱动表 book 中的字段 card 上的索引。

DROP INDEX Y ON book;
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

![在这里插入图片描述][d2fdc707f46b4196979e3b9455144cb9.png]

### 3.3.采用内连接 ###

（1）删除驱动表 type 中的字段 card 上的索引，这样两张表的 card 字段上都没有索引。

DROP INDEX X ON `type`;
    
    # 内连接
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

![在这里插入图片描述][91e0afa537f74dc88d68bc2a9f0f9762.png]

（2）为被驱动表 book 中的字段 card 添加索引。

CREATE INDEX Y ON book(card);
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

![在这里插入图片描述][26d1095deccf4391be1559b486e753ff.png]

（3）为驱动表 type 中的字段 card 也添加索引。

CREATE INDEX X ON `type`(card);
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

![在这里插入图片描述][fee0d1af0d7c40aa9dcbac360094a749.png]

对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的

（4）删除被驱动表 book 中的字段 card 上的索引。

DROP INDEX Y ON book;
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

![在这里插入图片描述][5c236936d5fb4e9cbed2a3d6405d730e.png]

结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。之前 book 表为被驱动表，但是将其字段 card 上的索引删除之后，那么连接条件中就只有 type 表上的 card 字段上有索引，此时，type 会从原本的驱动表变为被驱动表，其目的就是为了更加快速地进行内连接查询。

（5）重新为驱动表 type 中的字段 card 添加索引，此时可以看到 book 表又变回了被驱动表。

CREATE INDEX Y ON book(card);
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

![在这里插入图片描述][66fc4b188e244e5fab1d69f32a2ebde0.png]

此时向驱动表（即 type 表）中添加 20 条数据，那么 type 表中一共就有 40 条数据了，而 book 表中还是只有 20 条数据。

INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
    INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));

再次执行下面的 SQL 语句：

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

![在这里插入图片描述][9e84ecaa20dd4ce088753a28c261fa33.png]  
此时可以发现：**数据量较少的被驱动表 book 变为了驱动表，而数据量较多的驱动表 type 变为了驱动表！**。  
结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，即“小表驱动大表”。特别需要注意的是，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

### 3.4.join 语句原理 ###

join 方式连接多个表，本质就是**各个表之间数据的循环匹配**。MySQL 5.5 版本之前，MySQL 只支持一种表间关联方式，就是**嵌套循环 (Nested Loop Join)**。如果关联表的数据量很大，则 join 关联的执行时间会非常长。在 MySQL 5.5 以后的版本中，MySQL 通过引入**BNLJ 算法**来优化嵌套执行。

#### 3.4.1.驱动表和驱动表 ####

驱动表就是主表，被驱动表就是从表、非驱动表。  
**（1）对于内连接来说**

SELECT * FROM A JOIN B ON ...

A 一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表，反之就是被驱动表。通过explain 关键字可以查看。

**（2）对于外连接来说**

SELECT * FROM A LEFT JOIN B ON ...
    # 或
    SELECT * FROM B RIGHT JOIN A ON ...

通常，大家会认为 A 就是驱动表，B 就是被驱动表。但也未必。测试如下：

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;
    CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;
    
    INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
    INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
    
    # 测试 1
    EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE(a.f2=b.f2);
    # 测试 2
    EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND(a.f2=b.f2);

![在这里插入图片描述][4b71e0d9a6884156bed186ad4142af72.png]  
![在这里插入图片描述][e867b9308bd4473fb893ebcfca22e9b4.png]

#### 3.4.2.Simple Nested-Loop Join（简单嵌套循环连接） ####

算法相当简单，从表 A 中取出一条数据 1，遍历表 B，将匹配到的数据放到 result…以此类推，驱动表 A 中的每一条记录与被驱动表 B 的记录进行判断：  
![在这里插入图片描述][1714f148e1dd4b7c98b2c809e02c284c.png]  
可以看到这种方式效率是非常低的，以上述表 A 数据 100 条，表 B 数据 1000 条计算（这里指过滤之后的），则 A \* B = 10 万次。开销统计如下（下表中的 A 和 B 分别代表数据条数，即 100 和 1000）：  
![在这里插入图片描述][ba4f806564a74c81a5a0f624c418e708.png]

通过上表的**内表扫描次数**和**读取记录数**可知，A 的值越小越好！

#### 3.4.3.lndex Nested-Loop Join（索引嵌套循环连接） ####

Index Nested-Loop Join 优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

![在这里插入图片描述][46bb30620d1a45dca6e62a638976ad70.png]

驱动表中的每条记录通过被驱动表的索引进行访问，因为**索引查询的成本是比较固定的**，故 MySQL 优化器都倾向于使用记录数少的表作为驱动表（外表），开销统计如下。  
![在这里插入图片描述][8aeb2c45437b4baaa6401fb371ef8871.png]

如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次**回表查询**。**因此相比之下，被驱动表的索引是主键索引，效率会更高。**

#### 3.4.4.Block Nested-Loop Join（块嵌套循环连接） ####

（1）如果存在索引，那么会使用 index 的方式进行 join，如果 join 的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样大大增加了 I/O 的次数。为了减少被驱动表的 I/O 次数，就出现了 Block Nested-Loop Join 的方式。

（2）不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer 缓冲区，将驱动表 join 相关的部分数据列（大小受 join buffer 的限制）缓存到 join buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

> 注意：这里缓存的不只是关联表的列，select 后面的列也会缓存起来。在一个有 N 个 join 关联的 SQL 中会分配 N - 1 个 join buffer。所以查询的时候尽量减少不必要的字段，可以让 join buffer 中可以存放更多的列。

![在这里插入图片描述][0941a647374b4b9997a5db20da692d87.png]

![在这里插入图片描述][04d478049c1c4174a07747117557d32f.png]

**（3）参数设置**  
① block\_nested\_loop  
通过下面的语句可以查看 block\_nested\_loop 的状态，默认是开启的。

show variables like '%optimizer_switch%'

② join\_buffer\_size  
驱动表能不能一次加载完，要看 join buffer 能不能存储所有的数据，可以通过下面的语句进行查看，**默认情况下 join\_buffer\_size=256k**，当然也可以自行设置。join\_buffer\_size 的最大值在 32 位系统可以申请 4G，而在 64 位操做系统下可以申请大于 4G 的 Join Buffer 空间（64位 Windows 除外，其大值会被截断为 4G 并发出警告）。

show variables like '%join_buffer%';

#### 3.4.5.join 小结 ####

（1）整体效率比较：INLJ > BNLJ > SNLJ。

（2）永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量，小的度量单位指的是表行数 \* 每行大小）。

select t1.b,t2.* from t1 straight.join t2 on (t1.b=t2.b) where t2.id<=108;		# 推荐
    
    select t1.b, t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;		# 不雅荐

在上面的例子中，表 t1 和 t2 都是只有 100 行参加 join，但是这 2 条语句每次查询放入 join\_buffer 的数据是不一样的：  
① 表 t1 只查字段 b，因此如果把 t1 放到 join\_buffer 中，则 join\_buffer 中只需要放入 b 的值；  
② 表 t2 需要查所有的字段，因此如果把表 t2 放到 join\_buffer 中的话，就需要放入三个字段 id、 a 和 b。

（3）为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）。

（4）增大 join buffer size 的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）。

（5）减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）。

（6）需要 join 的字段，数据类型保持绝对一致。

（7）能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)

（8）**不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 JOIN 来代替子查询**。

（9）衍生表建不了索引。

#### 3.4.6.Hash Join ####

（1）从 MySQL 的 8.0.20 版本开始将废弃 BNLJ，因为从 MySQL 8.0.18 版本开始就加入了 hash join，默认都会使用 hash join。

（2）Nested Loop：对于被连接的数据子集较小的情况，Nested Loop 是个较好的选择。

（3）Hash Join 是做**大数据集连接**时的常用方式，优化器使用两个表中较小（相对较小）的表利用 Join Key 在内存中建立**散列表**，然后扫描较大的表并探测散列表，找出与 Hash 表匹配的行。  
① 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。  
② 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成**若干不同的分区**，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高 I/O 的性能。  
③ 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是 Join 的重型升降机。Hash Join 只能应用于等值连接（例如 WHERE A.COL1=B.COL2），这是由 Hash 的特点决定的。

![在这里插入图片描述][19510de07a8d470e80a01804e03b596a.png]

## 4.子查询优化 ##

（1）MySQL 从 4.1 版本开始支持子查询，使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的 SQL 操作。

（2）子查询可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是子查询的执行效率不高。其原因在于：  
① 执行子查询时，MySQL 需要为内层查询语句的查询结果**建立一个临时表**，然后外层查询语句从临时表中查询记录。查询完毕后，再**撤销这些临时表**。这样会消耗过多的 CPU 和 I/O 资源，产生大量的慢查询。  
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都**不会存在索引**，所以查询性能会受到一定的影响。  
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

（3）在 MySQL 中，可以使用连接 (JOIN) 查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

（4）举例  
**① 查询学生表 student 中是班长的学生信息**  
1）使用子查询

# 创建班级表中班长的索引
    CREATE INDEX idx_monitor ON class(monitor);
    
    # 查询班长的信息
    EXPLAIN SELECT * FROM student stu1
    WHERE stu1.`stuno` IN (
    SELECT monitor
    FROM class c
    WHERE monitor IS NOT NULL
    );

![在这里插入图片描述][f57e168e9cfb47b785e013a8bbc767e9.png]

2）使用 join

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
    ON stu1.`stuno` = c.`monitor`
    WHERE c.`monitor` IS NOT NULL;

![在这里插入图片描述][cc7f661b246b43888a978357e7647297.png]

**② 查询不为班长的学生信息**  
1）使用子查询

EXPLAIN SELECT SQL_NO_CACHE a.* 
    FROM student a 
    WHERE a.stuno NOT IN (
    			SELECT monitor FROM class b 
    			WHERE monitor IS NOT NULL)

![在这里插入图片描述][11ce785e52ea4bf795d74fe075ff3867.png]

2）使用 join

EXPLAIN SELECT SQL_NO_CACHE a.*
    FROM  student a LEFT OUTER JOIN class b 
    ON a.stuno =b.monitor
    WHERE b.monitor IS NULL;

![在这里插入图片描述][1393898b9fc04232a8fff9501dfd14c6.png]

> 注意：尽量不要使用 NOT IN 或者 NOT EXISTS，用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代

## 5.排序优化 ##

### 5.1.问题引入 ###

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？  
回答：  
在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序：  
① 在 lndex 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。  
② FileSort 排序则一般在内存中进行排序，占用 CPU 较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

### 5.2.优化建议 ###

（1）SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中是为了**避免全表扫描**，在 ORDER BY 子句是为了**避免使用 FileSort 排序**。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，还是要避免，以提高查询效率。  
（2）尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。  
（3）无法使用 Index 时，需要对 FileSort 方式进行调优。

### 5.3.测试 ###

（1）删除 student 和 class 表中的非主键索引。

CALL proc_drop_index('atguigudb2','student');
    CALL proc_drop_index('atguigudb2','class');

（2）无索引可用。

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid; 
    
    EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid LIMIT 10;

![在这里插入图片描述][9e4ba3d44f864692b125a66201b68e2e.png]

（3）在 student 表上创建索引 idx\_age\_classid\_name。

# 创建索引  
    CREATE INDEX idx_age_classid_name ON student(age,classid,NAME);

**① 查询所有字段，索引失效**

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid;

![在这里插入图片描述][1466fa9a4bc544f7802d838acceb8a2a.png]

由上图可知，索引 idx\_age\_classid\_name 失效，其原因在于优化器估计了使用与不适用该索引的成本，如果使用该索引，由于查询的是所有字段（上述 SQL 语句中的 \* ），所以在使用二级索引 idx\_age\_classid\_name 之后还要进行**回表操作**，当数据量比较大时，其成本可能要比不使用索引而直接查询的成本还要大！所以上述 SQL 未使用索引 idx\_age\_classid\_name 。

**② 查询部分字段，索引生效**

EXPLAIN  SELECT SQL_NO_CACHE age, classid FROM student ORDER BY age, classid;

![在这里插入图片描述][fedc494ac20d42b5b180463bc5c9727e.png]  
由上图可知，索引 idx\_age\_classid\_name 被使用了，其原因在于**要查询的字段不是全部的字段，而只是部分索引上的字段，此时不需要回表就能查到**，其成本较小，此时索引 idx\_age\_classid\_name 也被称为**覆盖索引**。

**③ 查询所有字段，但使用 limit，索引生效**

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

![在这里插入图片描述][f31018cbcf95425fab62292b5fb8c2d5.png]

由于 **limit 限制了要查询的数量，所以此时的回表操作的成本就低了许多**，所以使用上了索引 idx\_age\_classid\_name 。

（4）创建索引 idx\_age\_classid\_stuno。

CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 
    
    SHOW INDEX FROM student;

![在这里插入图片描述][4b7c5ea0b65940efb804f27fd6e368a6.png]  
判断：下面的语句会使用到索引吗？

# 没有使用到索引
    EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;

![在这里插入图片描述][1bee4b40f82c4496b0a1f9a25087a14b.png]

# 没有使用到索引
    EXPLAIN  SELECT * FROM student ORDER BY classid, NAME LIMIT 10;

![在这里插入图片描述][db5e5588b9dc43ff93f3d71fa35ff8c6.png]

# 使用了索引 idx_age_classid_stuno
    EXPLAIN  SELECT * FROM student ORDER BY age, classid, stuno LIMIT 10;

![在这里插入图片描述][ab563be5d2714ca7881fceefc1d6d768.png]

# 使用了索引 idx_age_classid_name
    EXPLAIN  SELECT * FROM student ORDER BY age, classid LIMIT 10;

![在这里插入图片描述][490566cc3c844846ac7d371eb66be657.png]

# 使用了索引 idx_age_classid_name
    EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;

![在这里插入图片描述][1e89d6aae8d44e8e9261d631f8cd9253.png]

（5）order by 时规则不一致，索引失效 （顺序错，不索引；方向反，不索引）

# age 字段的排序方式 (DESC) 与索引中的相反
    EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;

![在这里插入图片描述][390efa406dc643caa3b95cfb14e1f066.png]

# classid 字段的排序方式 (DESC) 与索引中的相反
    EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;

![在这里插入图片描述][4e727e00a6684dad91e653a642f6842b.png]

# age 与 classid 的排序方式一升一降
    EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10;

![在这里插入图片描述][1fdc426b13bf49908f3c6e386a13f952.png]

# age 与 classid 的排序方式同为降序时，此时 MySQL 会选择从后往前遍历，即 "Backward index scan"
    EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

![在这里插入图片描述][5b68b048d5d94ef7a0c8aaaa98e36c86.png]

（6）无过滤，不索引

EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;

![在这里插入图片描述][ab47fcbcfe49434fabb21c45eb832f80.png]  
根据上图可知，索引 idx\_age\_classid\_name 被使用了，但需要注意的是，key\_len = 5，其原因在于经过 where age = 45 这个条件过滤之后，剩下的数据量可能比较少（上图中的 rows = 19500，说明过滤之后只剩 19500 条数据，这对于 CPU 来说是比较少的），所以此时不需要再使用 idx\_age\_classid\_name 中的索引列，而是直接回表并将得到的数据进行排序。

--------------------

EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid, NAME;

![在这里插入图片描述][39acd08b4ec149b1a10ae55b6f065aa9.png]  
结果分析与上面的类似，这里就不再赘述。

--------------------

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

![在这里插入图片描述][c4b1050aafae4e0ea14cfa6aee2cb35c.png]

--------------------

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;

![在这里插入图片描述][5898f7b0bde74221ab5a73e761ea66f3.png]  
（7）小结

INDEX a_b_c(a,b,c) order by 能使用索引最左前缀
    - ORDER BY a
    - ORDER BY a,b
    - ORDER BY a,b,c
    - ORDER BY a DESC,b DESC,c DESC 如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
    - WHERE a = const ORDER BY b,c
    - WHERE a = const AND b = const ORDER BY c
    - WHERE a = const ORDER BY b,c
    - WHERE a = const AND b > const ORDER BY b,c 不能使用索引进行排序
    - ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
    - WHERE g = const ORDER BY b,c /*丢失a索引*/
    - WHERE a = const ORDER BY c /*丢失b索引*/
    - WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
    - WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

### 5.4.案例实战 ###

（1）先清除 student 表上的索引，只留主键：

call proc_drop_index('atguigudb2', 'student');

**（2）场景：查询年龄为 30 岁的，且学生编号小于 101000 的学生，按学生名称排序。**

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
    
    SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

![在这里插入图片描述][dab1fab361ae4ee3b1d25d5567934369.png]  
![在这里插入图片描述][fe8f0616c569440c9fd6593d49b7a06a.png]

> 结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort，也是最坏的情况，所以需要进行优化。

（3）优化思路  
① 为了去掉 filesort，可以把建立如下索引：

CREATE INDEX idx_age_name ON student(age, NAME);

![在这里插入图片描述][52826ea52f6242dc9aba67a4d1f1f836.png]  
![在这里插入图片描述][18bc98cb6cfa4467a4465388674046c5.png]

② 尽量让 where 中的过滤条件和排序使用上索引

CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);

![在这里插入图片描述][9c4102e247d743fa8def7e5ba7c24c0c.png]  
![在这里插入图片描述][c8870db4af544143a9484664dfe9d21d.png]

仔细观察上面的结果会发现：有 filesort的 SQL 运行速度， 超过了已经优化掉 filesort 的 SQL ，而且几乎一瞬间就出现了结果！  
原因：所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的 stuno < 101000 这个条件，如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

> 结论：  
> ① 两个索引同时存在，mysql自动选择最优的方案（对于这个例子，MySQL 选择 idx\_age\_stuno\_name）。**但是随着数据量的变化，选择的索引也会随之变化的。**  
> ② **当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。**

下面这种建立索引的方式也是可行的。

DROP INDEX idx_age_stuno_name ON student;
    
    CREATE INDEX idx_age_stuno ON student(age,stuno);

### 5.5.filesort 算法：双路排序和单路排序 ###

#### 5.5.1.双路排序 （慢） ####

（1）**MySQL 4.1 之前是使用双路排序**，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和 order by 列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。  
（2）从磁盘取排序字段，在 buffer 进行排序，再**从磁盘取其他字段**。

取一批数据，要对磁盘进行两次扫描，众所周知，I/O 是很耗时的，所以在 MySQL 4.1 之后，出现了第二种改进的算法，就是**单路排序**。

#### 5.5.2.单路排序 （快） ####

从磁盘读取查询需要的**所有列**，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序 I/O，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

#### 5.5.3.结论及引申出的问题 ####

（1）由于单路排序是后出的，总体而言好过双路排序；  
（2）但是单路排序也存在一定的缺陷：  
① **在 sort\_buffer 中，单路排序要比多路排序要多占用很多空间**，因为单路排序是把所有字段都取出，所以有可能取出的数据的总大小超出了 **sort\_buffer** 的容量，导致每次只能取 sort\_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort\_buffer 容量大小，再排…从而多次有 I/O。

② 单路本来想省一次 I/O 操作，**反而导致了大量的 I/O 操作**，反而得不偿失。

#### 5.5.4.优化策略 ####

**（1）尝试提高 sort\_buffer\_size。**  
不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程 (connection) 的 1M-8M 之间调整。再 MySQL 5.7 中，InnoDB 存储引擎默认值是 1048576 字节，即 1MB，可以使用下面的语句进行查看。

SHOW VARIABLES LIKE '%sort_buffer_size%';

**（2）尝试提高 max\_length\_for\_sort\_data。**  
① 提高这个参数，会增加用改进算法的概率。

# 默认 1024 字节
    SHOw VARIABLES LIKE ' %max_length_for_sort_data%' ;

② 但是如果设的太高，数据总容量超出 sort\_buffer\_size 的概率就增大，明显的症状是就高的磁盘 l/O 活动和低的处理器使用率。如果需要返回的列的总长度大于max\_length\_for\_sort\_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整。

**（3）Order by 时 select \* 是一个大忌，最好只查询需要的字段。**  
① 当查询的字段大小总和小于 max\_length\_for\_sort\_data，而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法――**单路排序**，否则用老算法—―**多路排序**。  
② 两种算法的数据都有可能超出 sort\_buffer\_size 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要提高 sort\_buffer\_size。

## 6.GROUP BY 优化 ##

（1）group by 使用索引的原则几乎跟 order by 一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。  
**（2）group by 先排序再分组，遵照索引建的最佳左前缀法则。**  
**（3）当无法使用索引列，增大 max\_length\_for\_sort\_data 和 sort\_buffer\_size 参数的设置。**  
（4）where 效率高于 having，能写在 where 限定的条件就不要写在 having 中了。  
（5）减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct 这些语句较为耗费 CPU，数据库的 CPU资 源是极其宝贵的。  
（6）包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。

## 7.分页查询优化 ##

一般分页查询时，通过创建**覆盖索引**能够比较好地提高性能。一个常见又非常头疼的问题就是 **limit 2000000,10**，此时需要 MySQL 排序前 2000010 条记录，但仅仅返回 2000000 - 2000010 之间的记录，其他记录丢弃，查询排序的代价非常大。

EXPLAIN SELECT * FROM student LIMIT 2000000, 10;

![在这里插入图片描述][997573c108f7487384e250171eb954bd.png]

**（1）优化思路一**  
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

![在这里插入图片描述][7dee03535580413cb924ccca3f609a27.png]

**（2）优化思路二**  
该方案适用于主键自增的表，可以把 limit 查询转换成某个位置的查询。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

![在这里插入图片描述][944576c803d448529cfd27448bb16144.png]

## 8.优先考虑覆盖索引 ##

### 8.1.什么是覆盖索引？ ###

（1）理解方式一  
索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。**一个索引包含了满足查询结果的数据就叫做覆盖索引。**

（1）理解方式二  
非聚簇复合索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列，**即建索引的字段正好是覆盖查询条件中所涉及的字段**。

简单说就是，索引列+主键包含 SELECT 到 FROM 之间查询的列 。

### 8.2.覆盖索引的优缺点 ###

**（1）优点**  
① 避免 Innodb 表进行索引的二次查询（回表）；  
InnoDB 是以聚集索引的顺序来存储的，对于 InnoDB 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。在覆盖索引中，二级索引的键值中可以获取所要的数据，**避免了对主键的二次查询，减少了 I/O 操作**，提升了查询效率。

② 可以把随机 I/O 变成顺序 I/O 加快查询效率；  
由于覆盖索引是按键值的顺序存储的，对于 I/O 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 I/O 要少的多，因此利用覆盖索引在访问时也可以**把磁盘的随机读取的 I/O 转变成索引查找的顺序 I/O**。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

**（2）缺点**  
索引字段的维护总是有代价的，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA，或者称为业务数据架构师的工作。

### 8.3.案例 ###

（1）先删除 student 表上除主键索引之外的所有索引，防止其余的索引对下面的例子产生干扰。

CALL proc_drop_index('atguigudb2','student');
    
    SHOW INDEX FROM student;

（2）建立索引 idx\_age\_name。

CREATE INDEX idx_age_name ON student (age, NAME);

（3）案例 1

EXPLAIN SELECT * FROM student WHERE age <> 20;
    
    EXPLAIN SELECT age, NAME FROM student WHERE age <> 20;

![在这里插入图片描述][a3beb7a937a4401ab359800d15ad7dca.png]  
![在这里插入图片描述][97b947d4e2404bdc8f24181927f3ef21.png]  
（4）案例 2

EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
    
    EXPLAIN SELECT id, age FROM student WHERE NAME LIKE '%abc';

![在这里插入图片描述][4c8f8706f25c471abb4b2f4e255deb00.png]  
![在这里插入图片描述][d23b041a70754420b663c42a6d919852.png]

看到这里，可能有的读者会有疑问：  
前面的第 2.7 节和第 2.9 节中不是说了不等于（!= 或者<>）索引失效以及 like 以通配符 % 开头索引失效吗，怎么上面 2 个案例中的第 2 条 SQL 语句都能使用上索引？  
这里需要解释一下，前面的第 2 节总结的索引失效的情况指的是**一般规律，并非完全绝对的**。对于具体问题需要具体分析，这里的分析主要指优化器会对 SQL 语句的运行成本进行具体分析，看是否需要使用索引。上面的 2 个案例中的第 2 个 SQL 语句均可以使用覆盖索引 idx\_age\_name，并且其成本比需要回表操作的要低，所以就使用了该索引。

## 9.如何给字符串添加索引 ##

有一张教师表，表定义如下：

create table teacher( 
    ID bigint unsigned primary key, 
    email varchar(64), 
    ... 
    )engine=innodb;

讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

mysql> select col1, col2 from teacher where email='xxx';

如果 email 这个字段上没有索引，那么这个语句就只能做**全表扫描**。

### 9.1.前缀索引 ###

（1）MySQL是支持**前缀索引**的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

mysql> alter table teacher add index index1(email); 
    # 或
    mysql> alter table teacher add index index2(email(6));

（2）这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图。

![在这里插入图片描述][f91632decb18488ebc709f3aff17b7f1.png]

![在这里插入图片描述][284dcac042cb470b9cd3d4fd5f48bc73.png]

**（3）如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：**  
① 从 index1 索引树找到满足索引值是 ‘zhangssxyz@xxx.com’ 的这条记录，取得 ID2 的值；  
② 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；  
③ 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email = ‘zhangssxyz@xxx.com’ 的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

**（4）如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：**  
① 从 index2 索引树找到满足索引值是 ’zhangs’ 的记录，找到的第一个是ID1；  
② 到主键上查到主键值是 ID1 的行，判断出email的值不是 ’zhangssxyz@xxx.com‘，这行记录丢弃；  
③ 取 index2 上刚刚查到的位置的下一条记录，发现仍然是 ”zhangs‘，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；  
④ 重复上一步，直到在 indxe2 上取到的值不是 ‘zhangs’ 时，循环结束。

**也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。**

### 9.2.前缀索引对覆盖索引的影响 ###

**结论：使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。**

## 10.索引下推 ##

### 10.1.什么是索引下推？ ###

（1）索引下推 (Index Condition Pushdown, ICP) 是 MySQL 5.6 中新特性，是一种**在存储引擎层使用索引过滤数据的一种优化方式**。  
（2）如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估 WHERE 后面的条件是否保留行。  
（3）启用 ICP 后（一般是默认开启的），如果部分 WHERE 条件可以仅使用索引中的列进行筛选，则 MySQL 服务器会把这部分 WHERE 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。  
① 优点：ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。  
② 缺点：ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。

### 10.2.索引下推的开启与关闭 ###

（1）**默认情况下启用索引条件下推**。可以通过设置系统变量 optimizer\_switch 控制：

# 关闭索引下推
    SET optimizer_switch = 'index_condition_pushdown=off';
    # 打开索引下推
    SET optimizer_switch = 'index_condition_pushdown=on';

（2）当使用索引条件下推时，EXPLAIN 语句输出结果中 **Extra** 列内容显示为 **Using index condition**。

### 10.3.ICP 的使用条件 ###

（1）只能用于二级索引 (secondary index)；  
（2）explain显示的执行计划中 type 值（join 类型）为 range 、 ref 、 eq\_ref 或者 ref\_or\_null 。  
（3）并非全部where条件都可以用ICP筛选，如果 where 条件的字段不在索引列中，还是要读取整表的记录到 server 端做 where 过滤。  
（4）ICP 可以用于 MyISAM 和 InnnoDB 存储引擎  
（5）MySQL 5.6 版本的不支持分区表的 ICP 功能，5.7 版本的开始支持。  
（6）当 SQL 使用覆盖索引时，不支持 ICP 优化方法。

### 10.4.案例 ###

**（1）单列索引（特殊情况）**

USE atguigudb1;
    
    EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

![在这里插入图片描述][89a1d0c189164d0b9b77dfb3dd36227b.png]

以上面的 SQL 语句为例，开启 ICP 后，在使用二级索引 idx\_key1 的情况下，假设通过查询条件 key1 > ‘z’ 过滤得到了 1000 条记录（注意这里并不是完整的记录，只是包括**索引列+主键**，即 key1 + id），此时先不着急回表，而是在后面的查询语句 key1 LIKE ‘%a’ 的基础上，对这 1000 条记录再次进行过滤，假设只剩下 100 条记录，此时再根据 id 进行回表操作查找完整的记录即可。

**（2）联合索引（普遍情况）**

USE atguigudb1;
    
    # 建立 people 表
    CREATE TABLE `people`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
    `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
    `lastname` VARCHAR(20)COLLATE utf8_bin DEFAULT NULL,
    `address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
    PRIMARY KEY (`id `),
    # 建立联合索引
    KEY `zip_last_first`(`zipcode`, `lastname`, `firstname`)
    )ENGINE=INNODB AUTo_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;
    
    # 插入数据
    INSERT INTO `people` VALUES
    ('1', '000001', '三', '张', '北京市'),
    ('2', '000002', '四', '李', '南京市'),
    ('3', '000003', '五', '王', '上海市'),
    ('4', '000001'，'六', '赵', '天津市');

EXPLAIN SELECT * FROM people 
    WHERE zipcode='000001'
    AND lastname LIKE '%张%'
    AND address LIKE '%北京市%';

![在这里插入图片描述][4164ef41cfaf472c83007e955b648f22.png]

① 由上面的查询计划可知，key\_len = 63，这说明索引 zip\_last\_first 中只有索引列 zip 被使用了（like 以通配符 % 开头索引失效）。

② 开启 ICP 后，在使用二级索引（联合索引） zip\_last\_first 的情况下，假设通过查询条件 zipcode = ‘000001’ 过滤得到了 1000 条记录（注意这里并不是完整的记录，只是包括**索引列+主键**，即 `zipcode`, `lastname`, `firstname` \+ `id`），此时先不着急回表，而是在后面的查询语句 lastname LIKE ‘%张%’ 的基础上，对这 1000 条记录再次进行过滤，假设只剩下 100 条记录，而此时由于字段 address 不在 `zipcode`, `lastname`, `firstname` \+ `id` 中，所以无法再次过滤，此时再根据 id 进行回表操作查找完整的记录即可。

③ 如果关闭 ICP，在使用二级索引（联合索引） zip\_last\_first 的情况下，假设通过查询条件 zipcode = ‘000001’ 过滤得到了 1000 条记录，此时会直接回表，根据 id 查询到完整的记录，然后再根据后面的 2 个查询条件进行过滤。**其缺点就比较明显，如果通过查询条件 zipcode = ‘000001’ 过滤得到的记录数非常大（比如有几百万条），那么此时直接全部回表的效率会比较低。**

④ 注：具体 key\_len 的计算可以查看[MySQL高级篇知识点——性能分析工具的使用][MySQL]这篇文章的第 6.4.7 节，key\_len = 63 的由来如下：  
varchar(20) 变长字段且允许 NULL = 20 \* (character set:utf8 = 3, gbk = 2, latin1 = 1) + 1(NULL) + 2(变长字段) = 20 \* 3 + 1 + 2 = 63

### 10.5.开启/关闭索引下推的性能对比 ###

#### 10.5.1.准备数据 ####

# 创建存储过程，向 people 表中添加 1000000 条数据，测试 ICP 开启和关闭状态下的性能
    DELIMITER //
    CREATE PROCEDURE insert_people (max_num INT)
    BEGIN
    	DECLARE i INT DEFAULT 0;
    	SET autocommit = 0;
    	REPEAT
    	SET i = i + 1;
    	INSERT INTO people (zipcode, firstname ,lastname, address ) VALUES ('000001', '六', '赵', '天津市');
    	UNTIL i = max_num
    	END REPEAT;
    	COMMIT;
    END //
    DELIMITER;
    
    # 调用存储过程，插入 1000000 条数据
    CALL insert_people(1000000);

![在这里插入图片描述][94db63f9ef4947c1ad9d8cc5be95407a.png]

#### 10.5.2.性能对比 ####

（1）打开 profiling。

set profiling = 1;

（2）执行 SQL 语句，此时默认打开索引下推。

SELECT * FROM people WHERE zipcode='008801' AND lastname LIKE '%张%';

![在这里插入图片描述][fc9b7d1409ea406cb8fa6c3b71e5ddc6.png]

（3）再次执行 SQL 语句，不使用索引下推（也可通过设置 optimizer\_switch 来关闭索引下推）。

SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode=' e88801' AND lastname LIKE '%张%' ;

![在这里插入图片描述][5c5a34e55d4945729bc20e7180be5174.png]

（4）查看 profiles。

![在这里插入图片描述][36752140f228498a83ac72c9c1112cb1.png]  
![在这里插入图片描述][326dff86fa434213a08926f5dd33c376.png]  
![在这里插入图片描述][67e590bc8f6d488fbb8f0970d1c4fdbf.png]

**对比结果可知，开启 ICP 后查询所消耗的时间明显少于关闭 ICP 的！**

## 11.普通索引 vs 唯一索引 ##

（1）**从性能的角度考虑**，你选择唯一索引还是普通索引呢？选择的依据是什么呢？

（2）假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引，假设字段 k 上的值都不重复。这个表的建表语句是：

mysql> create table test( 
    id int primary key, 
    k int not null, 
    name varchar(16), 
    index (k) 
    )engine=InnoDB;

表中 R1~R5 的 (ID, k )值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)。

### 11.1.查询过程 ###

假设，执行查询的语句是：

select id from test where k = 5;

① 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k = 5 条件的记录。  
② 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是， **微乎其微 。**

### 11.2.更新过程 ###

（1）为了说明普通索引和唯一索引对更新语句性能的影响这个问题，介绍一下 **change buffer**。

（2）当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下， **InooDB 会将这些更新操作缓存在 change buffer 中**，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

（3）将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 **merge** 。除了**访问这个数据页**会触发 merge 外，系统有**后台线程会定期 merge**。在**数据库正常关闭 (shutdown) 的过程**中，也会执行 merge 操作。

（4）如果能够将更新操作先记录在 **change buffer**， **减少读磁盘**，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够**避免占用内存**，提高内存利用率。

（5）唯一索引的更新就不能使用 change buffer ，实际上也只有普通索引可以使用。

如果要在这张表中插入一个新记录 (4, 400) 的话，InnoDB 的处理流程是怎样的？

### 11.3. change buffer的使用场景 ###

（1）普通索引和唯一索引应该怎么选择？其实，这两类索引在查询能力上是没差别的，主要考虑的是对**更新性能**的影响。所以，建议**尽量选择普通索引**。  
（2）在实际使用中会发现， **普通索引**和 **change buffer** 的配合使用，对于数据量大的表的更新优化还是很明显的。  
（3）如果所有的更新后面，都马上伴**随着对这个记录的查询**，那么你应该关闭 change buffer 。而在其他情况下，change buffer 都能提升更新性能。  
（4）由于唯一索引用不上 change buffer 的优化机制，因此如果**业务可以接受**，从性能角度出发建议优先考虑非唯一索引。但是如果"业务可能无法确保"的情况下，怎么处理呢？  
① 首先， 业务正确性优先 。我们的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。这种情况下，本节的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，给你多提供一个排查思路。  
② 然后，在一些"归档库"的场景，你是可以考虑使用唯一索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

## 12.其它查询优化策略 ##

### 12.1.EXISTS 和 IN 的区分 ###

问题：不太理解哪种情况下应该使用 EXISTS，哪种情况应该用 IN。选择的标准是看能否使用表的索引吗？  
回答：索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为**小表驱动大表**。在这种方式下效率是最高的。

比如下面这样：

SELECT * FROM A WHERE cc IN (SELECT ce FROM B)
    SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

① 当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

for i in A
    	for j in B
    		if j.cc == i.cc then ...

② 当 B 小于 A 时用 IN，因为实现的逻辑类似于：

for i in B
    	for j in A
    		if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN。

### 12.2.COUNT(\*) 与 COUNT(具体字段)效率 ###

问：在 MySQL 中统计数据表的行数，可以使用三种方式：SELECT COUNT(\*)、SELECT COUNT(1) 和 SELECT COUNT(具体字段)，使用这三者之间的查询效率是怎样的？  
答：  
前提：如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。  
**环节1**：`COUNT(*)` 和 `COUNT(1)` 都是对所有结果进行 `COUNT`，`COUNT(*)` 和 `COUNT(1)` 本质上并没有区别（二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的）。如果有 WHERE 子句，则是对所有符合筛选条件的数据行进行统计；如果没有WHERE子句，则是对数据表的数据行数进行统计。

**环节2**：如果是 MyISAM 存储引擎，统计数据表的行数只需要`O(1)`的复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了`row_count`值，而一致性则是由表级锁来保证的。

如果是 InnoDB 存储引擎，因为 InnoDB 支持事务，采用行级锁和 MVCC 机制，所以无法像 MyISAM 一样，维护一个 row\_count 变量，因此需要采用`扫描全表`，是 `O(n)` 的复杂度，进行循环+计数的方式来完成统计。

**环节3**：在 InnoDB 引擎中，如果采用 `COUNT(具体字段)` 来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于 `COUNT(*)` 和 `COUNT(1)` 来说，它们不需要查找具体的行，只是统计行数，系统会`自动`采用占用空间更小的二级索引来进行统计。

如果有多个二级索引，会使用 key\_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

### 12.3.关于 SELECT(\*) ###

在表查询中，建议明确字段，不要使用 \* 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：  
① MySQL 在解析的过程中，会通过`查询数据字典`将"\*"按序转换成所有列名，这会大大的耗费资源和时间。  
② 无法使用`覆盖索引`。

### 12.4.LIMIT 1 对优化的影响 ###

（1）针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上`LIMIT 1`的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

（2）如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上`LIMIT 1`了。

### 12.5.多使用 COMMIT ###

（1）只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。  
（2）COMMIT 所释放的资源：  
① 回滚段上用于恢复数据的信息；  
② 被程序语句获得的锁；  
③ redo / undo log buffer 中的空间；  
④ 管理上述 3 种资源中的内部花费；

## 13.淘宝数据库的主键是如何设计的？ ##

聊一个实际问题：淘宝数据库的主键是如何设计的？  
某些错的离谱的答案还在网上年复一年的流传着，甚至还成为了所谓的 MySQL 军规。其中，一个最明显的错误就是关于 MySQL 的主键设计。大部分人的回答如此自信：**用 8 字节的 BIGINT 做主键，而不要用 INT。 错**！这样的回答，**只站在了数据库这一层，而没有从业务的角度思考主键**。主键就是一个自增 ID 吗？用自增做主键，架构设计上可能连及格都拿不到！

### 13.1 自增 ID 的问题 ###

自增 ID 做主键，简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已。自增 ID 除了简单，其他都是缺点，总体来看存在以下几方面的问题：  
**（1）可靠性不高**  
存在自增 ID 回溯的问题，这个问题直到最新版本的 MySQL 8.0 才修复。

**（2）安全性不高**  
对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户 ID 的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。

**（3）性能差**  
自增 ID 的性能较差，需要在数据库服务器端生成。

**（4）交互多**  
业务还需要额外执行一次类似 last\_insert\_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

**（5）局部唯一性**  
最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

### 13.2.业务字段做主键 ###

为了能够唯一地标识一个会员的信息，需要为 会员信息表 设置一个主键。那么，怎么为这个表设置主键，才能达到我们理想的目标呢？ 这里我们考虑业务字段做主键。假设表数据如下：

![在这里插入图片描述][abf686882af6418598da6ef569431e31.png]

在这个表里，哪个字段比较合适呢？

#### 13.2.1.选择卡号 (cardno) ####

（1）会员卡号 (cardno) 看起来比较合适，因为会员卡号不能为空，而且有唯一性，可以用来标识一条会员记录。

mysql> CREATE TABLE demo.membermaster
    -> (
    -> cardno CHAR(8) PRIMARY KEY, -- 会员卡号为主键
    -> membername TEXT,
    -> memberphone TEXT,
    -> memberpid TEXT,
    -> memberaddress TEXT,
    -> sex TEXT,
    -> birthday DATETIME
    -> ); 
    Query OK, 0 rows affected (0.06 sec)

不同的会员卡号对应不同的会员，字段“cardno”唯一地标识某一个会员。如果都是这样，会员卡号与会员一一对应，系统是可以正常运行的。

（2）但实际情况是， **会员卡号可能存在重复使用的情况**。比如，张三因为工作变动搬离了原来的地址，不再到商家的门店消费了 （退还了会员卡），于是张三就不再是这个商家门店的会员了。但是，商家不想让这个会员卡空着，就把卡号是“10000001”的会员卡发给了王五。

（3）从系统设计的角度看，这个变化只是修改了会员信息表中的卡号是“10000001”这个会员 信息，并不会影响到数据一致性。也就是说，修改会员卡号是“10000001”的会员信息， 系统的各个模块，都会获取到修改后的会员信息，不会出现“有的模块获取到修改之前的会员信息，有的模块获取到修改后的会员信息，而导致系统内部数据不一致”的情况。因此，**从信息系统层面上看是没问题的**。

（4）**但是从使用统的业务层面 来看，就有很大的问题了**，会对商家造成影响。比如，我们有一个销售流水表 (trans)，记录了所有的销售流水明细。2020 年 12 月 01 日，张三在门店购买了一本书，消费了 89 元。那么，系统中就有了张三买书的流水记录，如下所示：

![在这里插入图片描述][2413e4aa87e5446ea17de5cf98db3d09.png]

接着，我们查询一下 2020 年 12 月 01 日的会员销售记录：

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
    -> FROM demo.trans AS a
    -> JOIN demo.membermaster AS b
    -> JOIN demo.goodsmaster AS c
    -> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber); 
    +------------+-----------+----------+------------+---------------------+
    | membername | goodsname | quantity | salesvalue | transdate 		   | 
    +------------+-----------+----------+------------+---------------------+
    | 张三		 | 书 		 | 1.000 	| 89.00 	 | 2020-12-01 00:00:00 | 
    +------------+-----------+----------+------------+---------------------+
    1 row in set (0.00 sec)

如果会员卡“10000001”又发给了王五，我们会更改会员信息表。导致查询时：

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
    -> FROM demo.trans AS a
    -> JOIN demo.membermaster AS b
    -> JOIN demo.goodsmaster AS c
    -> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber); 
    +------------+-----------+----------+------------+---------------------+
    | membername | goodsname | quantity | salesvalue | transdate 		   | 
    +------------+-----------+----------+------------+---------------------+
    | 王五 		 | 书 		 | 1.000 	| 89.00 	 | 2020-12-01 00:00:00 | 
    +------------+-----------+----------+------------+---------------------+
    1 row in set (0.01 sec)

这次得到的结果是：王五在 2020 年 12 月 01 日，买了一本书，消费 89 元。显然是错误的！结论：**千万不能把会员卡号当做主键。**

#### 13.2.2.选择会员电话或身份证号 ####

（1）会员电话可以做主键吗？不行的。在实际操作中，**手机号也存在被运营商收回，重新发给别人用的情况**。  
（2）那身份证号行不行呢？好像可以。因为身份证决不会重复，身份证号与一个人存在一一对 应的关系。可问题是，**身份证号属于个人隐私 ，顾客不一定愿意给你**。要是强制要求会员必须登记身份证号，会把很多客人赶跑的。其实，客户电话也有这个问题，这也是我们在设计会员信息表的时候，允许身份证号和电话都为空的原因。  
**（3）所以，建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。**

> 经验：刚开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。

### 13.3.淘宝的主键设计 ###

（1）在淘宝的电商业务中，订单服务是一个核心业务。那订单表的主键淘宝是如何设计的呢？是自增 ID 吗？打开淘宝，看一下订单信息：

![在这里插入图片描述][70a97b4766dd4c27b06c889bfd932795.png]

从上图可以发现，订单号不是自增 ID！我们详细看下上述4个订单号：

1550672064762308113 
    1481195847180308113 
    1431156171142308113 
    1431146631521308113

（2）订单号是 19 位的长度，且订单的最后 5 位都是一样的，都是 08113。且订单号的前面 14 位部分是单调递增的。大胆猜测，淘宝的订单 ID 设计应该是：

订单ID = 时间 + 去重字段 + 用户ID后 6 位尾号

**这样的设计能做到全局唯一，且对分布式系统查询及其友好。**

### 13.4.推荐的主键设计 ###

#### 13.4.1.非核心业务 ####

非核心业务：对应表的主键自增 ID，如告警、日志、监控等信息。

#### 13.4.2.核心业务 ####

核心业务 ：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。这里推荐最简单的一种主键设计：UUID。

**（1）UUID的特点**  
全局唯一，占用36字节，数据无序，插入性能差。

**（2）认识UUID：**  
MySQL 数据库的 UUID 组成如下所示：

UUID = 时间+UUID版本（16字节）- 时钟序列（4字节） - MAC地址（12字节）

我们以 UUID 值 e0ea12d4-6473-11eb-943c-00155dbaa39d 举例：  
![在这里插入图片描述][c34ad1ab05414d5e888a939c64a73c18.png]  
**① 为什么 UUID 是全局唯一的？**  
在 UUID 中时间部分占用 60 位，存储的类似 TIMESTAMP 的时间戳，但表示的是从1582-10-15 00：00：00.00 到现在的 100ns 的计数。可以看到 UUID 存储的时间精度比 TIMESTAMPE 更高，时间维度发生重复的概率降低到 1/100 ns。时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC地址用于全局唯一。

**② 为什么 UUID 占用36个字节？**  
UUID根据字符串进行存储，设计时还带有无用"-"字符串，因此总共需要36个字节。

**③ 为什么 UUID 是无序的？**  
因为在 UUID 的设计中，将时间低位放在最前面，而这部分的数据是一直在变化的，并且是无序。

**（3）改造 UUID**  
① 若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL 8.0 可以更换时间低位和时间高位的存储方式，这样 UUID就是有序的 UUID 了。MySQL 8.0 还解决了 UUID 存在的空间占用的问题，除去了 UUID 字符串中无意义的 “-” 字符串，并且将字符串用二进制类型保存，这样存储空间降低为了 16 字节。

② 可以通过 MySQL8.0 提供的 uuid\_to\_bin 函数实现上述功能，同样的，MySQL 也提供了bin\_to\_uui函数进行转化：

SET @uuid = UUID(); 
    
    SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

![在这里插入图片描述][2a02c68ce2cf44baa6b6a2faad9bce3a.png]

③ 通过函数 uuid\_to\_bin(@uuid,true) 将 UUID 转化为有序 UUID 了。全局唯一 + 单调递增，这不就是我们想要的主键！

**（4）有序 UUID 性能测试**  
16 字节的有序 UUID，相比之前 8 字节的自增 ID，性能和存储空间对比究竟如何呢？我们来做一个测试，插入 1 亿条数据，每条数据占用500 字节，含有 3 个二级索引，最终的结果如下所示：

![在这里插入图片描述][406172d417aa4275ac667ad5d6c5fe14.png]

从上图可以看到插入 1 亿条数据有序 UUID 是最快的，而且在实际业务使用中有序 UUID 在业务端就可以生成 。还可以进一步减少 SQL 的交互次数。另外，虽然有序 UUID 相比自增 ID 多了 8 个字节，但实际只增大了 3G 的存储空间，还可以接受。

> 在当今的互联网环境中，非常不推荐自增ID作为主键的数据库设计。更推荐类似有序 UUID 的全局唯一的实现。另外在真实的业务系统中，主键还可以加入业务和系统属性，如用户的尾号，机房的信息等。这样的主键设计就更为考验架构师的水平了。

**（5）如果不是 MySQ L8.0 怎么办？**  
手动赋值字段做主键！比如，设计各个分店的会员表的主键，因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。可以在总部 MySQL 数据库中，有一个管理信息表，在这个表中添加一个字段，专门用来记录当前会员编号的最大值。门店在添加会员的时候，先到总部 MySQL 数据库中获取这个最大值，在这个基础上加 1，然后用这个值作为新会员的“id”，同时，更新总部 MySQL 数据库管理信息表中的当 前会员编号的最大值。这样一来，各个门店添加会员的时候，都对同一个总部 MySQL 数据库中的数据表字段进 行操作，就解决了各门店添加会员时会员编号冲突的问题。

[https_www.bilibili.com_video_BV1iq4y1u7vj_p_141]: https://www.bilibili.com/video/BV1iq4y1u7vj?p=141
[4fef9e44e9c9498aa32cadb7c159f406.png]: https://img-blog.csdnimg.cn/4fef9e44e9c9498aa32cadb7c159f406.png
[819ae839ea2a400ea05b87cd985f2f86.png]: https://img-blog.csdnimg.cn/819ae839ea2a400ea05b87cd985f2f86.png
[daf0809eb9e54594a3a2931fd2224644.png]: https://img-blog.csdnimg.cn/daf0809eb9e54594a3a2931fd2224644.png
[e05fba8f320b4ff7b091251415acd152.png]: https://img-blog.csdnimg.cn/e05fba8f320b4ff7b091251415acd152.png
[6728d4cc5f214f3dbae82c78f6e60a19.png]: https://img-blog.csdnimg.cn/6728d4cc5f214f3dbae82c78f6e60a19.png
[420cce03f95a492db95e0b2dd5710c17.png]: https://img-blog.csdnimg.cn/420cce03f95a492db95e0b2dd5710c17.png
[8095ceadc98546ceb62c076deffb1172.png]: https://img-blog.csdnimg.cn/8095ceadc98546ceb62c076deffb1172.png
[17b9a477124c4da38b3db1cafffdf86e.png]: https://img-blog.csdnimg.cn/17b9a477124c4da38b3db1cafffdf86e.png
[681d0d426bc54b0bbb575c39e4d23a1f.png]: https://img-blog.csdnimg.cn/681d0d426bc54b0bbb575c39e4d23a1f.png
[MySQL]: https://blog.csdn.net/weixin_43004044/article/details/126460359
[e40383f478304ced9a2974a6ab4bfe55.png]: https://img-blog.csdnimg.cn/e40383f478304ced9a2974a6ab4bfe55.png
[7efa68f8d6c44c3486282f5a69266508.png]: https://img-blog.csdnimg.cn/7efa68f8d6c44c3486282f5a69266508.png
[f01d991142b94b10b5091bb93070a98a.png]: https://img-blog.csdnimg.cn/f01d991142b94b10b5091bb93070a98a.png
[ce8cc37690a24bbaa1a7b4134f626874.png]: https://img-blog.csdnimg.cn/ce8cc37690a24bbaa1a7b4134f626874.png
[685487c9614c41dabd9c084562637c12.png]: https://img-blog.csdnimg.cn/685487c9614c41dabd9c084562637c12.png
[0941ecec5d0945d1b01cb0c7f5787bc9.png]: https://img-blog.csdnimg.cn/0941ecec5d0945d1b01cb0c7f5787bc9.png
[fbe6c06b7f484c30ada125510252b7e9.png]: https://img-blog.csdnimg.cn/fbe6c06b7f484c30ada125510252b7e9.png
[a3742770926d47a883ecf6ef583cec22.png]: https://img-blog.csdnimg.cn/a3742770926d47a883ecf6ef583cec22.png
[14dc0a139d074120abff396e2ae25620.png]: https://img-blog.csdnimg.cn/14dc0a139d074120abff396e2ae25620.png
[5be6ec26842b4e32bcf841c8de2d581a.png]: https://img-blog.csdnimg.cn/5be6ec26842b4e32bcf841c8de2d581a.png
[7d76a2cff5d34708b5c54849764de0fd.png]: https://img-blog.csdnimg.cn/7d76a2cff5d34708b5c54849764de0fd.png
[6de93bd49993448d88f5d99ec488a62a.png]: https://img-blog.csdnimg.cn/6de93bd49993448d88f5d99ec488a62a.png
[474f5a7bf1714a99943bdafad5eeb5e3.png]: https://img-blog.csdnimg.cn/474f5a7bf1714a99943bdafad5eeb5e3.png
[ecf3c13431c54ad38f0a295c7762d061.png]: https://img-blog.csdnimg.cn/ecf3c13431c54ad38f0a295c7762d061.png
[4240e4fa0227403691847bfef5654a83.png]: https://img-blog.csdnimg.cn/4240e4fa0227403691847bfef5654a83.png
[5bdee1cd6d4f4474b3491834f6bdaf11.png]: https://img-blog.csdnimg.cn/5bdee1cd6d4f4474b3491834f6bdaf11.png
[c2ac4c7fd3d142cb902747c03e9a82fe.png]: https://img-blog.csdnimg.cn/c2ac4c7fd3d142cb902747c03e9a82fe.png
[856310358d984bf98b3a427adf099773.png]: https://img-blog.csdnimg.cn/856310358d984bf98b3a427adf099773.png
[316d8ec38e184b1593f11f78edc2c857.png]: https://img-blog.csdnimg.cn/316d8ec38e184b1593f11f78edc2c857.png
[f42dfc2179df4bd7909d25eedb415c7c.png]: https://img-blog.csdnimg.cn/f42dfc2179df4bd7909d25eedb415c7c.png
[2b89ad1bfab044d5970e7046c4450349.png]: https://img-blog.csdnimg.cn/2b89ad1bfab044d5970e7046c4450349.png
[870e9d732d624a258afb2c7549a75311.png]: https://img-blog.csdnimg.cn/870e9d732d624a258afb2c7549a75311.png
[79238057118e485dab32a2a41848ea09.png]: https://img-blog.csdnimg.cn/79238057118e485dab32a2a41848ea09.png
[d2fdc707f46b4196979e3b9455144cb9.png]: https://img-blog.csdnimg.cn/d2fdc707f46b4196979e3b9455144cb9.png
[91e0afa537f74dc88d68bc2a9f0f9762.png]: https://img-blog.csdnimg.cn/91e0afa537f74dc88d68bc2a9f0f9762.png
[26d1095deccf4391be1559b486e753ff.png]: https://img-blog.csdnimg.cn/26d1095deccf4391be1559b486e753ff.png
[fee0d1af0d7c40aa9dcbac360094a749.png]: https://img-blog.csdnimg.cn/fee0d1af0d7c40aa9dcbac360094a749.png
[5c236936d5fb4e9cbed2a3d6405d730e.png]: https://img-blog.csdnimg.cn/5c236936d5fb4e9cbed2a3d6405d730e.png
[66fc4b188e244e5fab1d69f32a2ebde0.png]: https://img-blog.csdnimg.cn/66fc4b188e244e5fab1d69f32a2ebde0.png
[9e84ecaa20dd4ce088753a28c261fa33.png]: https://img-blog.csdnimg.cn/9e84ecaa20dd4ce088753a28c261fa33.png
[4b71e0d9a6884156bed186ad4142af72.png]: https://img-blog.csdnimg.cn/4b71e0d9a6884156bed186ad4142af72.png
[e867b9308bd4473fb893ebcfca22e9b4.png]: https://img-blog.csdnimg.cn/e867b9308bd4473fb893ebcfca22e9b4.png
[1714f148e1dd4b7c98b2c809e02c284c.png]: https://img-blog.csdnimg.cn/1714f148e1dd4b7c98b2c809e02c284c.png
[ba4f806564a74c81a5a0f624c418e708.png]: https://img-blog.csdnimg.cn/ba4f806564a74c81a5a0f624c418e708.png
[46bb30620d1a45dca6e62a638976ad70.png]: https://img-blog.csdnimg.cn/46bb30620d1a45dca6e62a638976ad70.png
[8aeb2c45437b4baaa6401fb371ef8871.png]: https://img-blog.csdnimg.cn/8aeb2c45437b4baaa6401fb371ef8871.png
[0941a647374b4b9997a5db20da692d87.png]: https://img-blog.csdnimg.cn/0941a647374b4b9997a5db20da692d87.png
[04d478049c1c4174a07747117557d32f.png]: https://img-blog.csdnimg.cn/04d478049c1c4174a07747117557d32f.png
[19510de07a8d470e80a01804e03b596a.png]: https://img-blog.csdnimg.cn/19510de07a8d470e80a01804e03b596a.png
[f57e168e9cfb47b785e013a8bbc767e9.png]: https://img-blog.csdnimg.cn/f57e168e9cfb47b785e013a8bbc767e9.png
[cc7f661b246b43888a978357e7647297.png]: https://img-blog.csdnimg.cn/cc7f661b246b43888a978357e7647297.png
[11ce785e52ea4bf795d74fe075ff3867.png]: https://img-blog.csdnimg.cn/11ce785e52ea4bf795d74fe075ff3867.png
[1393898b9fc04232a8fff9501dfd14c6.png]: https://img-blog.csdnimg.cn/1393898b9fc04232a8fff9501dfd14c6.png
[9e4ba3d44f864692b125a66201b68e2e.png]: https://img-blog.csdnimg.cn/9e4ba3d44f864692b125a66201b68e2e.png
[1466fa9a4bc544f7802d838acceb8a2a.png]: https://img-blog.csdnimg.cn/1466fa9a4bc544f7802d838acceb8a2a.png
[fedc494ac20d42b5b180463bc5c9727e.png]: https://img-blog.csdnimg.cn/fedc494ac20d42b5b180463bc5c9727e.png
[f31018cbcf95425fab62292b5fb8c2d5.png]: https://img-blog.csdnimg.cn/f31018cbcf95425fab62292b5fb8c2d5.png
[4b7c5ea0b65940efb804f27fd6e368a6.png]: https://img-blog.csdnimg.cn/4b7c5ea0b65940efb804f27fd6e368a6.png
[1bee4b40f82c4496b0a1f9a25087a14b.png]: https://img-blog.csdnimg.cn/1bee4b40f82c4496b0a1f9a25087a14b.png
[db5e5588b9dc43ff93f3d71fa35ff8c6.png]: https://img-blog.csdnimg.cn/db5e5588b9dc43ff93f3d71fa35ff8c6.png
[ab563be5d2714ca7881fceefc1d6d768.png]: https://img-blog.csdnimg.cn/ab563be5d2714ca7881fceefc1d6d768.png
[490566cc3c844846ac7d371eb66be657.png]: https://img-blog.csdnimg.cn/490566cc3c844846ac7d371eb66be657.png
[1e89d6aae8d44e8e9261d631f8cd9253.png]: https://img-blog.csdnimg.cn/1e89d6aae8d44e8e9261d631f8cd9253.png
[390efa406dc643caa3b95cfb14e1f066.png]: https://img-blog.csdnimg.cn/390efa406dc643caa3b95cfb14e1f066.png
[4e727e00a6684dad91e653a642f6842b.png]: https://img-blog.csdnimg.cn/4e727e00a6684dad91e653a642f6842b.png
[1fdc426b13bf49908f3c6e386a13f952.png]: https://img-blog.csdnimg.cn/1fdc426b13bf49908f3c6e386a13f952.png
[5b68b048d5d94ef7a0c8aaaa98e36c86.png]: https://img-blog.csdnimg.cn/5b68b048d5d94ef7a0c8aaaa98e36c86.png
[ab47fcbcfe49434fabb21c45eb832f80.png]: https://img-blog.csdnimg.cn/ab47fcbcfe49434fabb21c45eb832f80.png
[39acd08b4ec149b1a10ae55b6f065aa9.png]: https://img-blog.csdnimg.cn/39acd08b4ec149b1a10ae55b6f065aa9.png
[c4b1050aafae4e0ea14cfa6aee2cb35c.png]: https://img-blog.csdnimg.cn/c4b1050aafae4e0ea14cfa6aee2cb35c.png
[5898f7b0bde74221ab5a73e761ea66f3.png]: https://img-blog.csdnimg.cn/5898f7b0bde74221ab5a73e761ea66f3.png
[dab1fab361ae4ee3b1d25d5567934369.png]: https://img-blog.csdnimg.cn/dab1fab361ae4ee3b1d25d5567934369.png
[fe8f0616c569440c9fd6593d49b7a06a.png]: https://img-blog.csdnimg.cn/fe8f0616c569440c9fd6593d49b7a06a.png
[52826ea52f6242dc9aba67a4d1f1f836.png]: https://img-blog.csdnimg.cn/52826ea52f6242dc9aba67a4d1f1f836.png
[18bc98cb6cfa4467a4465388674046c5.png]: https://img-blog.csdnimg.cn/18bc98cb6cfa4467a4465388674046c5.png
[9c4102e247d743fa8def7e5ba7c24c0c.png]: https://img-blog.csdnimg.cn/9c4102e247d743fa8def7e5ba7c24c0c.png
[c8870db4af544143a9484664dfe9d21d.png]: https://img-blog.csdnimg.cn/c8870db4af544143a9484664dfe9d21d.png
[997573c108f7487384e250171eb954bd.png]: https://img-blog.csdnimg.cn/997573c108f7487384e250171eb954bd.png
[7dee03535580413cb924ccca3f609a27.png]: https://img-blog.csdnimg.cn/7dee03535580413cb924ccca3f609a27.png
[944576c803d448529cfd27448bb16144.png]: https://img-blog.csdnimg.cn/944576c803d448529cfd27448bb16144.png
[a3beb7a937a4401ab359800d15ad7dca.png]: https://img-blog.csdnimg.cn/a3beb7a937a4401ab359800d15ad7dca.png
[97b947d4e2404bdc8f24181927f3ef21.png]: https://img-blog.csdnimg.cn/97b947d4e2404bdc8f24181927f3ef21.png
[4c8f8706f25c471abb4b2f4e255deb00.png]: https://img-blog.csdnimg.cn/4c8f8706f25c471abb4b2f4e255deb00.png
[d23b041a70754420b663c42a6d919852.png]: https://img-blog.csdnimg.cn/d23b041a70754420b663c42a6d919852.png
[f91632decb18488ebc709f3aff17b7f1.png]: https://img-blog.csdnimg.cn/f91632decb18488ebc709f3aff17b7f1.png
[284dcac042cb470b9cd3d4fd5f48bc73.png]: https://img-blog.csdnimg.cn/284dcac042cb470b9cd3d4fd5f48bc73.png
[89a1d0c189164d0b9b77dfb3dd36227b.png]: https://img-blog.csdnimg.cn/89a1d0c189164d0b9b77dfb3dd36227b.png
[4164ef41cfaf472c83007e955b648f22.png]: https://img-blog.csdnimg.cn/4164ef41cfaf472c83007e955b648f22.png
[94db63f9ef4947c1ad9d8cc5be95407a.png]: https://img-blog.csdnimg.cn/94db63f9ef4947c1ad9d8cc5be95407a.png
[fc9b7d1409ea406cb8fa6c3b71e5ddc6.png]: https://img-blog.csdnimg.cn/fc9b7d1409ea406cb8fa6c3b71e5ddc6.png
[5c5a34e55d4945729bc20e7180be5174.png]: https://img-blog.csdnimg.cn/5c5a34e55d4945729bc20e7180be5174.png
[36752140f228498a83ac72c9c1112cb1.png]: https://img-blog.csdnimg.cn/36752140f228498a83ac72c9c1112cb1.png
[326dff86fa434213a08926f5dd33c376.png]: https://img-blog.csdnimg.cn/326dff86fa434213a08926f5dd33c376.png
[67e590bc8f6d488fbb8f0970d1c4fdbf.png]: https://img-blog.csdnimg.cn/67e590bc8f6d488fbb8f0970d1c4fdbf.png
[abf686882af6418598da6ef569431e31.png]: https://img-blog.csdnimg.cn/abf686882af6418598da6ef569431e31.png
[2413e4aa87e5446ea17de5cf98db3d09.png]: https://img-blog.csdnimg.cn/2413e4aa87e5446ea17de5cf98db3d09.png
[70a97b4766dd4c27b06c889bfd932795.png]: https://img-blog.csdnimg.cn/70a97b4766dd4c27b06c889bfd932795.png
[c34ad1ab05414d5e888a939c64a73c18.png]: https://img-blog.csdnimg.cn/c34ad1ab05414d5e888a939c64a73c18.png
[2a02c68ce2cf44baa6b6a2faad9bce3a.png]: https://img-blog.csdnimg.cn/2a02c68ce2cf44baa6b6a2faad9bce3a.png
[406172d417aa4275ac667ad5d6c5fe14.png]: https://img-blog.csdnimg.cn/406172d417aa4275ac667ad5d6c5fe14.png