数据库-锁&优化.md · Arieszhang/learn-node

## **题目 01- 请你说一说 MySQL 的锁机制**

要求：

- 按照锁的粒度，锁的功能来分析
- 什么是死锁，为什么会发生，如何排查？
- 行锁是通过加在什么上完成的锁定？
- 详细说说这条 SQL 的锁定情况： delete from tt where uid = 666 ;

深入理解MySQL如何加锁，有两个比较重要的作用：

- 可以根据MySQL的加锁规则，写出不会发生死锁的SQL；

- 可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

- 可以根据MySQL的加锁规则，透过现象看本质，理解数据库层 面 阻塞执行的根本原因

### 1、锁分类

**按锁粒度分：**

- 全局锁：锁整Database，由MySQL的SQL layer层实现

- 表级锁：锁某Table，由MySQL的SQL layer层实现

- 行级锁：锁某Row的索引，也可锁定行索引之间的间隙，由存储引擎实现【InnoDB】

#### 1. 全局锁

全局锁是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

加全局锁的命令为：

```sql
flush tables with read lock;
```

释放全局锁的命令为：

```sql
unlock tables;
```

**或者断开加锁session的连接，自动释放全局锁。**

说到全局锁用于备份这个事情，还是很危险的。

如果在**主库上加全局锁**，则整个数据库**将不能写入**，备份期间影响业务运行，

如果在**从库上加全局锁**，则会导致不能执行主库同步过来的操作，造成主从延迟。

对于innodb这种支持事务的引擎，使用mysqldump备份时可以使用**--single-transaction**参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。

对于有MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为**--lock-all-tables**。

```sql
# 提交请求锁定所有数据库中的所有表，以保证数据的一致性，全局读锁 
mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables > /root/db.sql 
# 一致性视图 
mysqldump -uroot -p --host=localhost --all-databases --single-transaction > /root/db.sql
```

#### 2. 表级锁

MySQL的表级锁有四种：

- 表读锁（Table Read Lock）

- 表写锁（Table Write Lock）

- 元数据锁（meta data lock，MDL)

- 自增锁(AUTO-INC Locks)

##### 2.1. 表 读锁/写锁

1）表锁相关命令

MySQL 实现的表级锁定的争用状态变量：

```sql
# 查看表锁定状态 
mysql> show status like 'table%';
```

![image-20221027215055688](/Users/aries/Library/Application Support/typora-user-images/image-20221027215055688.png)

> table_locks_immediate：产生表级锁定的次数；
>
> table_locks_waited：出现表级锁定争用而发生等待的次数；

表锁有两种表现形式：

- 表读锁（Table Read Lock）

- 表写锁（Table Write Lock）

手动增加表锁：

```sql
lock table table_name1 read(write),table_name2 read(write)，其他; 
# 举例： 
#为表t加读锁 
lock table t read; 
#为表t加写锁
lock table t write;

#查看表锁情况：
show open tables;
#删除表锁：
unlock tables;
```

##### 2.2.元数据锁

元数据锁不需要显式指定，在访问一个表的时候会被自动加上，锁的作用是保证读写的正确性。可以想象一下：如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，**在** **MySQL 5.5** **版本中引入了元数据锁**，当对一个表做增删改查操作的时候，**加 元数据 读锁**；当要对表做结构变更操作的时候，**加 元数据 写锁**。

- **读锁是共享的，是互相不阻塞的**：因此你可以有多个线程同时对一张表加读锁，保证数据在读取的过程中不会被其他线程修改。

- **写锁则是排他的：**也就是说一个写锁会阻塞其他的写锁和读锁，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

##### 2.3.自增锁(AUTO-INC Locks)

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

#### 3. 行级锁

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。**InnoDB行锁**是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点：***只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！***

- InnoDB的行级锁，按照**锁定范围**来说，分为四种：

- **记录锁（Record Locks）**：锁定索引中一条记录。

- **间隙锁（Gap Locks）**：要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。

- **临键锁（Next-Key Locks）**：是索引记录上的记录锁和在索引记录之前的间隙锁的**组合（间隙锁 + 记录锁）**。

- **插入意向锁(Insert Intention Locks)**：做insert操作时添加的对记录id的锁。

- InnoDB的行级锁，按照**功能**来说，分为两种：

- 读锁：允许一个事务去读一行，阻止其他事务更新目标行数据。同时阻止其他事务加写锁，但不阻止其他事务加读锁。

- 写锁：允许获得排他锁的事务更新数据，阻止其他事务获取或修改数据。同时阻止其他事务加读锁和写锁。

如何加行级锁？

- **对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加写锁；**
- 对于普通SELECT语句，InnoDB**不会加任何锁**
- 事务可以通过以下语句手动给记录集加共享锁或排他锁

```sql
#创建表
CREATE TABLE `t1_simple`
(
    `id`      int(11) NOT NULL,
    `pubtime` int(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `idx_pu` (`pubtime`) USING BTREE
) ENGINE = InnoDB;
#新增数据
INSERT INTO `t1_simple`VALUES (1, 10);
INSERT INTO `t1_simple`VALUES (4, 3);
INSERT INTO `t1_simple`VALUES (6, 100);
INSERT INTO `t1_simple`VALUES (8, 5);
INSERT INTO `t1_simple`VALUES (10, 1);
INSERT INTO `t1_simple`VALUES (100, 20);

#添加读锁：
SELECT * FROM t1_simple WHERE id=4 LOCK IN SHARE MODE;
#添加写锁：
SELECT * FROM t1_simple WHERE id=4 FOR UPDATE;
```

##### 3.1记录锁（Record Locks）

记录锁（Record Locks）仅仅锁住索引记录的一行，在单条索引记录上加锁。记录锁锁住的永远是**索引**，而**非记录本身**，即使该表上没有任何显示索引，那么innodb会在后台创建一个隐藏的聚簇索引索引，那么锁住的就是这个隐藏的聚簇索引索引。

```sql
-- 加记录读锁 
select * from t1_simple where id = 1 lock in share mode;

-- 加记录写锁 
select * from t1_simple where id = 1 for update;

-- 新增，修改，删除加记录写锁 
insert into t1_simple values (2, 22);

update t1_simple set pubtime=33 where id =2;

delete from t1_simple where id =2;
```

##### 3.2 间隙锁（Gap Locks）

（1）间隙锁(Gap Locks)，仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
（3）间隙锁可用于防止幻读，保证索引间隙不会被插入数据

**主键id索引的行锁区间划分图：**

![image-20221028061314774](/Users/aries/Library/Application Support/typora-user-images/image-20221028061314774.png)

```sql
#Session 01 锁住 id 大于 4 的所有记录
begin; 
select * from t1_simple where id > 4 for update; 
commit;
```

```sql
-- 阻塞 id=7，大于4 需要等Session01 事务提交
insert into t1_simple values (7,100); 
-- 成功 id=3，没有被锁住
insert into t1_simple values (3,100);
```

##### 3.3 临键锁(Next-Key Locks)

（1）临键锁(Next-Key Locks)相当于记录锁 + 间隙锁【左开右闭区间】，例如（5,8]
（2）默认情况下，innodb使用临键锁来锁定记录，但在不同的场景中会退化
（3）当查询的索引含有唯一属性的时候，临键锁会进行优化，将其降级为记录锁，即仅锁住索引本身，不是范围。

**普通索引index(pubtime)行锁的区间划分图：**

![image-20221028061838928](/Users/aries/Library/Application Support/typora-user-images/image-20221028061838928.png)

| **场景** | **退化成的锁类型** |
| -------------------------------------------- | ------------------ |
| 使用Unique index 精确匹配【=】，且记录存在 | 记录锁 |
| 使用Unique index 精确匹配【=】，且记录不存在 | 间隙锁 |
| 使用Unique index 范围匹配【<和>】 | 临键锁 |

当前数据库中 【t1_simple】数据

![image-20221028063701580](/Users/aries/Library/Application Support/typora-user-images/image-20221028063701580.png)

```sql
#Session-01
begin; 
-- 临键锁区间(10,20],(20,100]
select * from t1_simple where pubtime = 20 for update; 
commit;
```

```sql
#Session-02
insert into t1_simple values (16, 19); -- 阻塞
select * from t1_simple where pubtime = 20 for update; -- 阻塞
insert into t1_simple values (16, 50); -- 阻塞
insert into t1_simple values (16, 101); -- 成功
```

##### 3.4 插入意向锁(Insert Intention Locks)

（1）插入意向锁(Insert Intention Locks)是一种**间隙锁**，不是**意向锁**，*在insert操作时产生*。
（2）在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
（3）假设有一个记录索引包含键值10和100，不同的事务分别插入60和70，每个事务都会产生一个加在10-100之间的插入意向锁，获取在插入行上的写锁，但是不会被互相锁住，因为数据行并不冲突。
（4）插入意向锁不会阻止任何锁，对于插入的记录会持有一个记录锁。

![image-20221028064133851](/Users/aries/Library/Application Support/typora-user-images/image-20221028064133851.png)

#### 4.加锁规则

**主键索引**

- 等值条件，命中，加**记录锁**

- 等值条件，未命中，加**间隙锁**

- 范围条件，命中，包含where条件的临键区间，加**临键锁**

- 范围条件，没有命中，加**间隙锁**

**辅助索引**

- 等值条件，命中，命中记录的辅助索引项 + 主键索引项加记录锁，辅助**索引项两侧加间隙锁**

- 等值条件，未命中，加**间隙锁**

- 范围条件，命中，包含where条件的临键区间加**临键锁**。命中记录的**id索引项加记录锁**

- 范围条件，没有命中，加**间隙锁**

#### 5.意向锁

##### 1）什么是意向锁？

##### 相当于存储引擎级别的表锁

InnoDB也实现了表级锁，也就是意向锁【Intention Locks】。意向锁是MySQL内部使用的，不需要用户干预。**意向锁和行锁可以共存**，意向锁的主要作用是为了全表更新数据时的提升性能。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。那么将是一件非常繁琐且耗时操作。

**举个栗子：**

事务A修改user表的记录r，会给记录r上一把行级的**写锁**，同时会给user表上一把**意向写锁（IX）**，这时

事务B要给user表上一个表级的**写锁**就会被阻塞。**意向锁**通过这种方式实现了行锁和表锁共存，且满足事务隔离性的要求。

当我们需要加一个写锁时，需要根据意向锁去判断表中有没有数据行被锁定；

（1）如果行锁，则需要遍历每一行数据去确认；

（2）如果表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能

##### **2）作用**

- 表明：“某个事务正在某些行持有了锁、或该事务准备去持有锁”

- 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存

##### 3）意向锁和读锁【S锁】、写锁【X锁】的兼容关系

![image-20221030142203147](/Users/aries/Library/Application Support/typora-user-images/image-20221030142203147.png)

- 意向锁相互兼容：因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。
- 表级S锁和X、IX锁不兼容：因为上了表级S锁后，不允许其他事务再加X锁。
- 表级X锁和 IS、IX、S、X不兼容：因为上了表级X锁后，会修改数据。

> 注意：上了行级写锁后，行级写锁**不会因为有别的事务上了意向写锁**而堵塞，一个MySQL是允许多个行级写锁同时存在的，只要他们*不是针对相同*的数据行。

#### 6. 锁相关参数

InnoDB所使用的**行级锁定**争用状态查看：

```sql
mysql> show status like 'innodb_row_lock%';
```

![image-20221030144429072](/Users/aries/Library/Application Support/typora-user-images/image-20221030144429072.png)

- Innodb_row_lock_current_waits：当前正在等待锁定的数量；

- **Innodb_row_lock_time**：从系统启动到现在锁定总时间长度；等待总时长

- **Innodb_row_lock_time_avg**：每次等待所花平均时间；等待平均时长

- Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

- **Innodb_row_lock_waits**：系统启动后到现在总共等待的次数；等待总次数

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

```sql
# 查看锁的SQL
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
# 查看事务SQL
select * from information_schema.innodb_trx;
# 查看未关闭的事务详情
SELECT  a.trx_id,a.trx_state,a.trx_started,a.trx_query,
        b.ID,b.USER,b.DB,b.COMMAND,b.TIME,b.STATE,b.INFO,
        c.PROCESSLIST_USER,c.PROCESSLIST_HOST,c.PROCESSLIST_DB,
        d.SQL_TEXT
FROM information_schema.INNODB_TRX a
         LEFT JOIN information_schema.PROCESSLIST b
                   ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep'
         LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
         LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
```

### 2. 死锁

所谓死锁：是指**两个或两个以上的进程**在执行过程中,因**争夺资源**而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

#### 1）产生原因

- 死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：**让不同的session加锁有次序**

#### 2）产生示例

准备工作：

```
CREATE TABLE `t1_deadlock`
(
    `id`      int(11) NOT NULL,
    `name`    varchar(100) DEFAULT NULL,
    `age`     int(11) NOT NULL,
    `address` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_age` (`age`) USING BTREE,
    KEY `idx_name` (`name`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;
Insert into t1_deadlock(id, name, age, address)values (1, '刘备', 18, '蜀国');
Insert into t1_deadlock(id, name, age, address)values (2, '关羽', 17, '蜀国');
Insert into t1_deadlock(id, name, age, address)values (3, '张飞', 16, '蜀国');
Insert into t1_deadlock(id, name, age, address)values (4, '关羽', 16, '蜀国');
Insert into t1_deadlock(id, name, age, address)values (5, '诸葛亮', 35, '蜀国');
Insert into t1_deadlock(id, name, age, address)values (6, '曹孟德', 32, '魏国');
```

案例一：

| 时刻 | Session - 1 | Session - 2 |
| ---- | ------------------------------------------------ | ----------------------------------- |
| T1 | begin; | begin; |
| T2 | select * from t1_deadlock where id=1 for update; | |
| T3 | | delete from t1_deadlock where id=5; |
| T4 | update t1_deadlock set name='qqq' where id=5; | |
| T5 | 死锁 | delete from t1_deadlock where id=1; |
| T6 | commit; | commit; |

![image-20221030164825703](/Users/aries/Library/Application Support/typora-user-images/image-20221030164825703.png)

```sql
-- Session01
begin;
select * from t1_deadlock where id = 1 for update;
update t1_deadlock set name='qqq' where id = 5;
commit;

-- Session02
begin;
delete from t1_deadlock where id = 5;
delete from t1_deadlock where id = 1;
-- 死锁
commit;
```

案例二：

![image-20221030165835703](/Users/aries/Library/Application Support/typora-user-images/image-20221030165835703.png)

```sql
CREATE TABLE `t1_deadlock03`
(
    `id`  int(11) NOT NULL AUTO_INCREMENT,
    `cnt` varchar(32) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE index `idx_cnt` (`cnt`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;
insert into t1_deadlock03(id, cnt) values (1, 'abc-130-sz');
select * from t1_deadlock03
-- Session01
    begin;
delete from t1_deadlock03 where cnt='abc-130-sz';
insert into t1_deadlock03(cnt) values ('abc-130-sz');
commit;

-- Session01
begin;
delete from t1_deadlock03 where cnt='abc-130-sz';
commit; 
```

#### 3）结论

死锁的发生与否，并不在于事务中有多少条SQL语句，【**死锁的关键在于**】：两个(或以上)的Session【**加锁的顺序**】不一致。

#### 4）怎样避免死锁的发生

MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该事务。

1. 注意程序的逻辑：根本的原因是程序逻辑的顺序交叠，最常见的是交差更新

2. 保持事务的轻量：越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小

3. 提高运行的速度：避免使用子查询，尽量使用主键等等

4. 尽量快提交事务，减少持有锁的时间：越早提交事务，锁就越早释放

### 3. 行锁是通过加在什么上完成的锁定？

行锁实际上锁住的是聚簇索引（主键索引），若没有设置聚簇索引，那么InnoDB 会创建一个隐藏的聚簇索引，也就是RowId来实现行锁。

![image-20221030171545619](/Users/aries/Library/Application Support/typora-user-images/image-20221030171545619.png)

### 4.SQL锁定情况

```sql
delete from tt where uid = 666 ;
```

#### 1.Read commited （读已提交）隔离级别

##### 1）场景一：uid 是 主键

**uid 是主键时**，此SQL只需要在id=10这条记录上加**写锁**即可。

##### 2）场景二：uid 是唯一索引

- 首先，在唯一索引（辅助索引）中找到符合条件的记录（仅一条），添加**写锁**。
- 然后，拿着对应主键，在主键索引中找到相应记录，添加**写锁**。

##### 3）场景三：uid 是非唯一索引

- 首先，在**非唯一索引**（辅助索引）中找到符合条件的记录（全部），添加**写锁**。
- 然后，拿着对应主键，在主键索引中找到相应记录，添加**写锁**。

##### 4）场景四：uid不是任何索引

若id列上没有索引，SQL会走**聚簇索引**的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上**写锁**。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在**判断后放锁**，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。

#### 2.**Repeatable read** （可重复读）

##### 1）场景一：uid 是 主键

同场景一

##### 2）场景二：uid 是唯一索引

同场景一

##### 3）场景三：uid 是非唯一索引

RR隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10;

- 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的写锁，加GAP上的间隙锁；
  - **这个多出来的间隙锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键**

- 然后加主键聚簇索引上的记录**写锁**，然后返回；

- 然后读取下一条，重复进行。

- 直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录**写锁**，但是仍旧需要加间隙锁，最后返回结束。

##### 4）场景四：uid不是任何索引

在RR隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有间隙，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

## **题目 02- 请你说一说 MySQL 的 SQL 优化**

### 1. 为什么要对MySQL进行优化？

- 提升网站整体通吐量，优化用户体验数据库是关键之一

- 流畅页面的访问速度

- 良好的网站功能体验

- 避免网站页面出现访问错误
    - 由于数据库连接timeout产生页面5xx错误
    - 由于慢查询造成页面无法加载
    - 由于阻塞造成数据无法提交

- 增加数据库的稳定性
  - 很多数据库问题都是由于低效的查询引起的

### 2. 有哪些优化手段？

- 服务器： OS、CPU、memory、network

- MySQL ：
  - 数据库表结构【对性能影响最大】
  - **低下效率的SQL语句**
  - 超大的表
  - 大事务
  - 数据库配置
  - 数据库整体架构

### 3. SQL优化

MySQL 调优的方向有很多，现在我们主要针对SQL优化方面详细聊聊。

**SQL优化：根据需求创建结构良好的SQL语句【实现同一个需求，SQL语句写法很多】**

#### 3.1 **查看SQL执行计划【Explain】**

MySQL 提供了一个 Explain 命令, 它可以对 SELECT 语句的执行计划进行分析，并输出 SELECT 执行的详细信息，以供开发人员针对性优化。使用explain命令来查看该SQL语句有没有使用上了索引，有没有做全表扫描等等。

Explain 命令用法很简单, 在 SELECT 语句前加上 explain 就可以了。

```sql
EXPLAIN SELECT * FROM tb_seckill_goods;
```

![image-20221031061340471](/Users/aries/Library/Application Support/typora-user-images/image-20221031061340471.png)

| 字段名称 | 字段描述 |
| --------------- | ------------------------------------------------------------ |
| id | SELECT识别符，这是SELECT查询序列号 |
| **select_type** | 表示单位查询的查询类型，比如：simple-普通查询、primary-查询的主要部分、union-联合查询(union、union all)、子查询等复杂查询。 |
| table | 表示查询的表 |
| partitions | 使用的哪些分区（对于非分区表值为null）。 |
| **type** | **（重要）表示表的连接类型。** const：使用唯一索引或者主键 eq_ref：唯一性索引扫描 ref：非唯一性索引扫描 fulltext：全文索引检索 all：全表扫描数据文件 |
| possible_keys | 此次查询中可能选用的索引 |
| key | 查询真正使用到的索引 |
| key_len | 显示MySQL决定使用的索引长度。 |
| ref | 哪个字段或常数与 key 一起被使用 |
| rows | 显示此查询一共扫描了多少行，这个是一个估计值，不是精确的值。 |
| filtered | 表示此查询条件所过滤的数据的百分比 |
| **Extra** | 额外信息 |

#### 3.2 关键结果说明

##### 1）**select_type**

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。

有以下几种值：

1. **simple****： 普通查询，**表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。

2. **primary****： 查询的主要部分，**一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。

3. **union****：连接查询**

- derived在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

- union若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

- union result 从UNION表获取结果的SELECT，

4. **dependent union****： 依赖连接查询**，与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响

5. **subquery****： 子查询**，除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

6. **dependent subquery****： 依赖子查询，**与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

7. **derived****：派生表，** from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

##### 2）type

显示的是单位查询的**连接类型**或者理解为**访问类型**，访问性能依次从好到差：

> system
> const
> eq_ref
> ref
> fulltext
> ref_or_null
> unique_subquery
> index_subquery
> range
> index_merge
> index
> ALL

- **system**：表中**只有一行数据或者是空表**。等于系统表，这是const类型的特列，平时不会出现，可以忽略不计

- **const（重要）**：使用**唯一索引或者主键**，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

- **eq_ref（重要）**：**唯一性索引扫描**，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

- **ref（重要）**：**非唯一性索**引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

- 组合索引

- 非唯一索引

- **fulltext**：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

- **ref_or_null**：与ref方法类似，只是增加了null值的比较。实际用的不多。

- **unique_subquery**：用于where中的in形式子查询，子查询返回不重复值唯一值

- **index_subquery**：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

- **range（重要）**：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

- **index_merge**：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所有索引，性能可能大部分时间都不如range

- **index****（重要）**：select结果列中使用到了索引，type会显示为index。**全部索引扫描**，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

- **all（重要）**：这个就是全表扫描数据文件，然后再**在server层进行过滤**返回符合要求的记录。

##### 3）**Extra**

这个列包含不适合在其他列中显示的，但十分重要的额外的信息，这个列可以显示的信息非常多，有几十种。解释几个经常遇到的。

**01-Using filesort**

使用了文件排序，说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。这种操作需要优化sql

```sql
-- 无索引，按照文件排序 
explain select price from tb_seckill_goods where price >100 order by cost_price; 
-- 有索引，不按照文件排序 
explain select price from tb_seckill_goods where price >100 order by price;
```

![image-20221031211341403](/Users/aries/Library/Application Support/typora-user-images/image-20221031211341403.png)

**02-using index**

表示相应的SELECT查询中使用到了索引，避免访问表的数据行，这种查询的效率很高！

- 如果同时出现Using Where ，索引在where之后，用作查询条件

- 如果没有同时出现Using Where ，索引在where之前，用作查询结果读取

```sql
-- 使用where，索引在where之后，用作查询条件 
explain select id,title,price from tb_seckill_goods where price>100; 
-- 没有使用where，索引在where之前，用作查询结果读取 
explain select id,title,price from tb_seckill_goods where title="华为5G手机";
```

![image-20221031211520412](/Users/aries/Library/Application Support/typora-user-images/image-20221031211520412.png)

**03-Using where**

表示MySQL将对InnoDB提取的结果在SQL Layer层进行过滤，过滤条件字段无索引；

```sql
-- 只有where 
explain select * from tb_seckill_goods where cost_price>100;
```

![image-20221031211555522](/Users/aries/Library/Application Support/typora-user-images/image-20221031211555522.png)

**04-Using join buffer**

表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

#### 3.3 索引优化

##### 1）哪些场景需要创建索引

1. 频繁出现在where 条件字段，order排序，group by分组字段
2. select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
3. 多表join关联查询，on字段两边的字段都要创建索引

##### 2）索引优化建议

1. **表记录很少不需创建索引 ：**索引是要有存储的开销
2. **一个表的索引个数不能过多：**

（1）空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。

（2）时间：更新（插入/Delete/Update）变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。

所以索引虽然能够提高查询效率，索引并不是越多越好，应该只为需要的列创建索引。

3. **频繁更新的字段不建议作为索引：**频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。
4.  **区分度低的字段，不建议建索引：**

> 比如性别，男，女；比如状态。区分度太低时，会导致扫描行数过多，再加上回表查询的消耗。如果使用索引，比全表扫描的性能还要差。这些字段一般会用在组合索引中。
>
> 姓名，手机号就非常适合建索引。

5.  **在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段：**

主键索引树一个页节点是16K，主键字段越长，一个页可存储的数据量就会越少，比较臃肿，查询时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值，主键值越长，一个页可存储的数据量就会越少，查询时磁盘IO次数会增多，查询效率会降低。

6.  **不建议用无序的值作为索引：**例如身份证、UUID。更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间。
7. **尽量创建组合索引，而不是单列索引：**

> 优点：
>
> （1）1个组合索引等同于多个索引效果，节省空间。
>
> （2）可以使用覆盖索引
>
> **创建原则：组合索引应该把频繁用到的列、区分度高的值放在前面。频繁使用代表索引的利用率高，区分度高代表筛选粒度大，这样做可最大限度利用索引价值，缩小筛选范围**

##### 3.4 **索引失效-组合索引心法口诀：**

> - 全值匹配我最爱，最左前缀要遵守；
>
> - 带头大哥不能死，中间兄弟不能断；
>
> - 索引列上不计算，范围之后全失效；
>
> - Like百分写最右，覆盖索引不写星；
>
> - 不等空值还有OR，索引失效要少用。

#### 3.4 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然很灵活，但是执行效率并不高。

**那么问题又来了啊？ 为什么它效率不高？**

把内层查询结果当作外层查询的比较条件的

```sql
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
```

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以子查询的速度会受到一定的影响。这多了一个创建临时表和销毁表的过程。

**优化方式：可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快**

#### 3.5 其他查询优化

- **小表驱动大表：**建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。

- JOIN两张表的关联字段最好都建立索引，而且最好字段类型一致

```sql
SELECT * FROM orders o LEFT JOIN user u on o.user_id = u.id 
# orders表中的user_id和user表中的id，类型要一致
```

- **避免全表扫描：**注意索引失效的口诀，避免索引失效导致的全表扫描
- 避免MySQL放弃索引：如果MySQL估计使用全表扫描要比使用索引快，则不使用索引
  - 最典型场景：查询数据量到一定阈值的时候出现的索引失效，数据量达到一定阈值使用索引不如全表扫描来的更快！
- **WHERE条件中尽量不要使用not in语句，建议使用not exists**
- **利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况**

Arieszhang/learn-node

简介

发行版

贡献者

近期动态

Arieszhang/learn-node .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

Arieszhang/learn-node