MYSQL总结（图片来源小林coding）

一、索引

1、mysql索引

• 按数据结构分【B+tree索引、Hash索引、Full-Text索引】
• 按物理引擎分【聚簇索引、二级索引】
• 按字段类型分【主键索引、唯一索引、普通索引、前缀索引】
• 按字段个数分【单列索引，联合索引】

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

• InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；
• MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

2、索引失效

1. 对索引使用左或者左右模糊匹配

// name 字段为二级索引
select * from t_user where name like '%xxx';

2. **对索引使用函数**

// name 为二级索引
select * from t_user where length(name)=6;

但是在8.0之后加入了对函数计算后的索引

alter table t_user add key idx_name_length ((length(name)));

3. 对索引进行表达式计算

explain select * from t_user where id + 1 = 10;

因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

4. 对索引进行隐式类型转换

如果索引字段是字符串类型（varchar），但是在条件查询中，输入的参数是整型（int）的话，你会在执行计划的结果发现这条语句会走全表扫描。

但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描。

5. 联合索引没有遵循最左匹配原则

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

6. where语句中的or

select * from t_user where id = 1 or age = 18;

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。解决办法就是让另一个字段也为索引

3、count(*)和count(1)和count(字段)哪个性能好

按照性能排序count(*) = count(1) > count(主键字段) > count(普通字段)

count()函数计算不为null的字段有多少个

select count(name) from t_order;

这条语句是统计「 t_order 表中，name 字段不为 NULL 的记录」有多少个。也就是说，如果某一条记录中的 name 字段的值为 NULL，则就不会被统计进去。

select count(1) from t_order;

这条语句是统计「 t_order 表中，1 这个表达式不为 NULL 的记录」有多少个。

1 这个表达式就是单纯数字，它永远都不是 NULL，所以上面这条语句，其实是在统计 t_order 表中有多少个记录。

count(主键索引)的执行过程

• 1、在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。
• 2、server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。
• 3、InnoDB 是通过 B+ 树来保存记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。

如果只有主键索引（聚簇索引），那就是按照上面的方式，如果还有二级索引，就是叶子节点的数据区存放的是主键id，这相对于聚簇索引来说是轻量级的，innoDB会优先选择使用二级索引，这样开销会更小

count(1)的执行过程

如果不含其他索引，只有主键索引

InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。

可以看到，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

count(字段)走的是全表扫描

查看当前字段有没有null，不是null则计数

4、优化count(*)

针对于大数据count(*)耗时比较大的

第一种，近似值

使用 show table status 或者 explain 命令来表进行估算。

第二种，维护一张表记录数据个数

当我们在数据表插入一条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，我们需要额外维护这个计数表。

二、事务

1、事务的特性（ACID）

• 原子性(Atomicity)：要么都做，要么都不错
• 一致性(Consistency)：事务前后，要保持数据一致的状态，可以用化学的能量守恒来表达
• 隔离性(Isolation)：防止事务并发执行由于交叉导致数据不一致（数据库允许多个并发事务同时对其数据进行读写和修改的能力）
• 持久性(Durability)：事务一旦提交，就不可修改

实现四种特性的具体表现

• 持久性是通过 redo log （重做日志）来保证的；
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；

2、并发事务引起的问题

• 脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

• 不可重复读

同一个事务先后读取的数据不一致

• 幻读

多次查询某个符合条件的【个数】，但是出现了前后两次数据记录数量不一样的情况，就像出现了幻觉一样

3、事务隔离级别(RU、RC、RR、S)

• 读未提交(read uncommitted)：一个事务还没提交，他执行的结果能被别人引用，会发生胀读、不可重复读、幻读
• 读已提交(read committed)：一个事务提交之后，他修改的数据才能被别人看见，会发生不可重复度、幻读，可以避免脏读
• 可重复度(repeatable read)：一个事务启动开始读的数据一直保持不变直到这个事务结束，会发生幻读，避免了脏读、不可重复读
• 串行化(serializable )：会对记录加上读写锁，在多个事务对这个进行操作的时候，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；避免了所有并发问题

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了，解决的方案有两种：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

在不同隔离级别下，事务 A 执行过程中查询到的余额可能会不同：

• 在「读未提交」隔离级别下，事务 B 修改余额后，虽然没有提交事务，但是此时的余额已经可以被事务 A 看见了，于是事务 A 中余额 V1 查询的值是 200 万，余额 V2、V3 自然也是 200 万了；
• 在「读提交」隔离级别下，事务 B 修改余额后，因为没有提交事务，所以事务 A 中余额 V1 的值还是 100 万，等事务 B 提交完后，最新的余额数据才能被事务 A 看见，因此额 V2、V3 都是 200 万；
• 在「可重复读」隔离级别下，事务 A 只能看见启动事务时的数据，所以余额 V1、余额 V2 的值都是 100 万，当事务 A 提交事务后，就能看见最新的余额数据了，所以余额 V3 的值是 200 万；
• 在「串行化」隔离级别下，事务 B 在执行将余额 100 万修改为 200 万时，由于此前事务 A 执行了读操作，这样就发生了读写冲突，于是就会被锁住，直到事务 A 提交后，事务 B 才可以继续执行，所以从 A 的角度看，余额 V1、V2 的值是 100 万，余额 V3 的值是 200万。

4、事务四种隔离级别的具体实现

• 读未提交：隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 串行化：隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 读提交、可重复读：隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。即读提交在每次执行查询的时候都要拍照，而可重复读是在事务一开始拍的照一直延用到事务结束。

5、在可重复读情况下并不能真正解决幻读的问题（MySQL Innodb 中的 MVCC 并不能完全避免幻读现象。）

例如：

事务 A 执行查询 id = 5 的记录，此时表中是没有该记录的，所以查询不出来。

# 事务 A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_stu where id = 5;
Empty set (0.01 sec)

然后事务 B 插入一条 id = 5 的记录，并且提交了事务。

# 事务 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_stu values(5, '小美', 18);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

此时，事务 A 更新 id = 5 这条记录，对没错，事务 A 看不到 id = 5 这条记录，但是他去更新了这条记录，这场景确实很违和，然后再次查询 id = 5 的记录，事务 A 就能看到事务 B 插入的纪录了，幻读就是发生在这种违和的场景。

# 事务 A
mysql> update t_stu set name = '小林coding' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_stu where id = 5;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  5 | 小林coding   |   18 |
+----+--------------+------+
1 row in set (0.00 sec)

自己理解：幻读在可重复读的情况下并不能避免，比如事务A在第一次执行普通的select操作，这个时候和正常的时候一样，此时事务B执行了新增操作同时提交了事务，正常可重复读情况下，事务A是获取不到这个新增的，假如此时事务A执行了一条update这条新增的数据（即使事务A看不到这条数据，但还是能执行），然后事务A就发起了一个当前读的视图（select * from xxx for update或者insert、delete、update语句执行的时候都是当前读，或加上间隙锁 + 记录锁（next-key lock）），就能拿到事务B提交的新数据，修改之后再查一次，他就发下了能看到事务B提交的数据，就发生了幻读。即一个事务开启后在起了当前读，就会发现有前后数据不一致的情况，就发生了幻读。

要避免上面的情况发生的话，就是在事务一开启的时候就发生加上间隙锁的操作（next-next lock）（当前读）的操作，这样其他事务就改不了，就不会发生幻读

三、Mysql锁

1、全局锁

-- 要使用全局锁，则要执行这条命令：
flush tables with read lock

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

• 对数据的增删改操作，比如 insert、delete、update等语句；
• 对表结构的更改操作，比如 alter table、drop table 等语句。

-- 要释放全局锁
unlock tables

应用于备份表

但是备份表的时候全局锁会有缺点

整个数据库只能是只读的状态，如果备份的表比较大，则备份时间久，会造成业务停滞

解决办法

数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

2、表级锁

• 表锁；
• 元数据锁（MDL）;
• 意向锁；
• AUTO-INC 锁；

2.1、表锁

-- 表级别的共享锁，也就是读锁；
lock tables t_student read;
-- 表级别的独占锁，也就是写锁；
lock tables t_stuent write;

表锁除限制别的线程的读写外，还会限制本线程接下来的读写操作，除非在执行前释放锁。

尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

unlock tables -- 释放锁

2.2、元数据锁（MDL）

MDL 不需要显示调用

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；（共享锁）

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

• 对一张表做结构变更操作的时候，加的是 MDL 写锁；（排他锁）

当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 不需要显示调用，那它是在什么时候释放的?

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

1. 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
2. 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
3. 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

因为申请MDL锁的操作会有一个队列，队列中获取写锁优先级高于获取读锁，一旦出现写锁等待，则后续的获取锁的操作都将会被阻塞。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

2.3、意向锁

意向锁的目的是为了快速判断表里是否有记录被加锁。

• 使用InnoDB引擎对某些记录加上共享锁之前，需要在表级别上加一个意向共享锁
• 使用InnoDB引擎对某些记录加上排他锁（独占锁）之前，需要在表级别上加一个意向排他锁（独占锁）

在对记录执行增、删、改的操作的时候，需要对表加上意向排他锁（独占锁），然后对记录加上排他锁

普通的select语句不会加锁，因为普通的select语句是采用==MVCC（多版本并发控制）==实现一致性读的操作，当然要给select加锁也行

-- 先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
-- 先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

2.4、AUTO-INC 锁

给用字段AUTO_INCREMENT修饰的属性在自增的时候加的锁

特殊的表级锁，在执行完一条insert语句后则释放锁，无需等到事务提交

在MYSQL5.1.22版本开始后InnoDB增加了一个轻量级锁，他不用等执行完insert语句，而是只要给用AUTO_INCREMENT修饰的字段加上锁，然后赋一个自增值即可释放，不需要语句执行完。

3、行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

行级锁的类型主要有三类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上；
• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

3.1、Record Lock（记录锁）

有**共享锁(S)和排他锁(X)**之分

读读共享、读写互斥、写写互斥

举个例子，当一个事务执行了下面这条语句：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。当事务执行 commit 后，事务过程中生成的锁都会被释放。

3.2、Gap Lock（间隙锁，前开后开区间）

只存在于可重复读，目的是为了防止幻读

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

3.3、Next-Key Lock（临键锁，前开后闭区间）

他是Record Lock（记录锁）+Gap Lock（临键锁），锁定范围，并且锁定本身，如果一个事务持有某个范围的X型临键锁，另一个事务想要持有交错范围的X型临键锁的时候将会发生阻塞。

3.4、插入意向锁

一个事务想要插入一条数据的时候会判断插入位置是否已经加了间隙锁（gap lock）或者临键锁(next-key lock)，如果有锁则会处于等待状态。

举个例子:

​ 假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。