MySQL 索引和锁机制总结

2022-07-25 11:55:28

今天总结一下 MySQL 的索引和锁机制相关的知识点。之所以总结索引，是因为索引非常重要，对于任何一个网站数据库来说，查询占 80% 以上，优化数据库性能，最主要是优化查询效率，使用索引是提高查询效率的最有效途径之一。之所以总结 MySQL 的锁机制，一方面是因为网上资料太少，平时大家也很少关注，另一方面是了解 MySQL 的锁机制，有利于数据库的优化设计，在一些重要场景中合理使用锁机制，能够有效保障数据的安全性。

一、MySQL 索引

MySQL 索引的主要用途就是提高数据的查询性能。索引本质上就是一种数据结构，在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，并且利用这些数据结构上实现高级查找算法，这种数据结构就是索引。

MySQL 索引按照功能分类，主要包含以下索引：

普通索引：最基本的索引，它没有任何限制。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
联合索引：就是将多个列组合在一起创建索引。
外键索引：只有 InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性，可以实现级联操作。
全文索引：快速匹配全部文档的方式。InnoDB 引擎 5.6 版本以后才支持全文索引。MEMORY 引擎不支持全文索引。

按照结构分类，主要包含两种索引：

B+Tree 索引：MySQL 中使用最频繁的一个索引数据结构，是 InnoDB 和 MyISAM 存储引擎默认的索引类型。
Hash 索引 : MySQL 中 Memory 存储引擎默认支持的索引类型。

（1）索引的创建和删除

-- MySQL 创建索引的语法格式如下：
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  -- 默认是 B+TREE 索引
ON 表名(列名...);

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

-- 在创建一张表时，可以直接创建主键索引
-- 因此主键列不需要单独创建索引
CREATE TABLE test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	data1 VARCHAR(100),
	data2 INT
);

-- 在登录用户表中，为【用户注册时间】创建普通索引
CREATE INDEX idx_register_time ON login_user(register_time);

-- 在登录用户表中，为【用户名】创建唯一索引
CREATE UNIQUE INDEX udx_user_name ON login_user(user_name);

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

-- 查看一个表中的索引，语法格式为：
-- SHOW INDEX FROM 表名;
SHOW INDEX FROM login_user;

-- 使用 ALTER 为表添加索引，语法格式如下：

-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 联合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

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

-- 为 login_user 表中家庭地址 address 列添加全文索引
ALTER TABLE login_user ADD FULLTEXT fdx_address(address);

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

-- 删除索引的语法格式为：
-- DROP INDEX 索引名称 ON 表名;
DROP INDEX fdx_address ON login_user;

（2）联合索引的特点

MySQL 在一张表中建立联合索引时，会遵循最左匹配的原则：即在查询数据时从联合索引的最左边的字段开始匹配。
假设我们在 login_user 表中，针对 user_name 、mobile、email 这三个字段创建联合索引：

ALTER TABLE login_user ADD INDEX idx_login(user_name, mobile, email);

联合索引 idx_login 实际建立了 (user_name)、 (user_name, mobile)、 (user_name, mobile, email) 三个索引。在查询 SQL 语句中，只要包含最左边的字段 user_name 即可，不需要考虑字段顺序，因为 MySQL 的优化器会自动帮助我们调整 where 条件中的字段顺序，匹配我们建立的索引。

-- 联合索引不需要考虑字段顺序，MySQL优化器会自动调整 where 条件后面的字段顺序，
-- 只要包含最左边的字段即可，所以下面的 SQL语句都可以命中索引：

-- 使用了  (user_name, mobile, email) 这个索引
SELECT * FROM login_user WHERE email='jobs@test.com' AND mobile='158xxxx2108' AND user_name='乔豆豆';

-- 使用了  (user_name, mobile) 这个索引
SELECT * FROM login_user WHERE user_name='候胖胖' AND mobile='134xxxx4820';

-- 使用了  (user_name) 这个索引
SELECT * FROM login_user WHERE user_name='蔺赞赞'

-- 使用了  (user_name) 这个索引， email 这个字段的查询条件没有索引
SELECT * FROM login_user WHERE email='wolfer@funny.com' AND user_name='任肥肥';

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

-- 下面的 SQL 查询语句，不会使用索引，因为不包含联合索引最左边的字段 user_name
SELECT * FROM login_user WHERE mobile='134xxxx6559' AND email='wolfer@funny.com';

（3）使用索引注意事项

MySQL 数据表中创建索引时请尽量考虑一些原则，便于提升索引的使用效率，更高效的使用索引：

对查询频次较高，且数据量比较大的表，一定要创建索引。
对于值不重复的字段，尽量使用唯一索引，区分度越高，使用索引的效率越高。
索引字段的选择，应当从 where 子句的条件中提取，如果 where 子句中的组合比较多，那么应当挑选最常用、过滤效果最好的字段。
尽量使用存储值比较短的字段创建索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率。
索引可以有效的提升查询数据的效率，但索引数量并不是越多越好。索引的存储也是占用硬盘空间的，当对一张表进行增、删、改操作后，其索引也是需要进行维护的，索引的存储量会随着数据量的增大而增大，过多的索引会增加数据库对该表索引的维护负担，降低该表增删改查的整体性能。

二、MySQL 锁机制

常用的数据库（Oracle，SQL Server，MySQL）的事务，都有四种隔离级别，如下所示：

隔离级别	中文名称	出现脏读	出现不可重复读	出现幻读	数据库默认隔离级别
read uncommitted	读未提交	是	是	是
read committed	读已提交	否	是	是	Oracle / SQL Server
repeatable read	可重复读	否	否	是	MySQL
serializable	串行化	否	否	否

以上排序，按照隔离级别从小到大，安全性越来越高，但是效率越来越低。
一般情况下，不会使用 read uncommitted 和 serializable ，因为 read uncommitted 安全级别最差，在并行处理时，各种问题都可能出现； serializable 虽然不会出现问题，但是所有对数据库的操作无法并行处理，只能单线程排队处理，性能效率比较低。
一般情况下，我们不要修改数据库事务的默认隔离级别。要想修改 MySQL 数据库隔离级别，使用以下 SQL 语句：

-- 将 MySQL 的数据库隔离级别，修改为串行化
-- 注意：每次修改完数据库隔离级别时，客户端需要重新连接 MySQL 才能生效
set global transaction isolation level serializable;

下面简单介绍一下脏读、不可重复读、幻读这三个问题：

问题	现象
脏读	在一个事务处理过程中读取了另一个未提交的事务中的数据，导致两次查询结果不一致
不可重复读	在一个事务处理过程中读取了另一个事务中修改并已提交的数据，导致两次查询结果不一致
幻读	查询某些记录时不存在，但 insert 时发现此记录已存在，造成添加失败。查询某些记录时不存在，但 delete 时却发现删除成功（受影响行数大于 0），导致误删了数据

从上面介绍的情况可以发现，MySQL 默认使用 repeatable read 隔离级别，只会有可能出现幻读的问题，怎么解决呢？
答案就是：采用 MySQL 的锁机制来解决。可以考虑在一些比较重要的场景中使用，比如跟钱有关的业务，以及动态数据迁移等等。

（1）锁的介绍

我们首先对 MySQL 的锁进行一下分类，这样能够从全局的角度去理解锁。

按操作分类：

读锁：也叫共享锁。针对同一份数据，多个事务的读取操作，可以同时加锁而不会互相影响，但不能修改数据记录。
写锁：也叫排他锁。一个事务对加锁数据的读写操作没有完成之前，会阻止其他事务对该数据的读写操作。

按粒度分类：

表锁：加锁时会锁定整张表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。
行锁：加锁时会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

存储引擎	表锁	行锁
MyISAM	支持	不支持
InnoDB	支持	支持
MEMORY	支持	不支持

按使用方式分类：

悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新数据时，需要先判断一下在此期间别人有没有去更新这个数据，然后决定自己要如何处理。常见的 3 种数据库（Oracle，SQL Server，MySQL）不提供乐观锁，需要自己去实现，实现起来也很容易。

InnoDB 同时支持表锁和行锁，MyISAM 和 MEMORY 只支持表锁。
下面我们针对 InnoDB 和 MyISAM 两种存储引擎进行锁操作介绍，有关 MEMORY 的锁操作，可以参考 MyISAM 的锁操作。
在演示相关锁操作的代码之前，我们先介绍一下要操作的示例表结构，我们有一张示例表 employee ，该表只有 3 个字段：

字段名称	说明
id	员工id，只针对该字段创建了主键索引，其它字段没有索引
name	员工姓名
money	员工薪水

（2）InnoDB 锁操作

InnoDB 同时支持表锁和行锁，默认使用行锁，当编写的 SQL 语句的 Where 条件无法使用索引时，则自动提升为表锁。

-- InnoDB 读锁（共享锁）语法格式如下：
SELECT语句 LOCK IN SHARE MODE;

/*
读锁（共享锁）的特点：数据可以被多个事务查询，但是不能修改
*/

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

/*
打开【第一个 MySQL 客户端工具】，先运行下面前 3 条 SQL，别运行 COMMIT 语句。
*/

-- 开启事务
START TRANSACTION;

-- 查询id为 1 的数据记录。加入共享锁
SELECT * FROM employee WHERE id=1 LOCK IN SHARE MODE;

-- 查询名称为其它某个人的数据记录。加入共享锁
SELECT * FROM employee WHERE name='侯胖胖' LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

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

/*
打开【第二个 MySQL 客户端工具】，手动逐条选中以下 SQL 语句并运行。
*/

-- 开启事务
START TRANSACTION;

-- 查询 id 为 1 的数据记录
-- 结果：可以查询
SELECT * FROM employee WHERE id=1;

-- 查询 id 为 1 的数据记录，并加入读锁（共享锁）
-- 结果：可以查询，这说明【共享锁】和【共享锁】是兼容的
SELECT * FROM employee WHERE id=1 LOCK IN SHARE MODE;

-- 修改 id 为 2 的姓名为蔺赞赞
-- 结果：修改成功，因为 id 是 employee 表的索引，而 InnoDB 引擎默认是行锁
-- 而且【第一个客户端工具】并没有对 id 为 2 的记录加锁
UPDATE employee SET name='蔺赞赞' WHERE id=2;

-- 修改id为 1 的姓名为任肥肥
-- 结果：不能修改，会出现阻塞。
-- 因为【第一个客户端工具】给 id 为 1 的记录加了读锁（共享锁），其它线程只能读，不能写
-- 只有当【第一个客户端工具】运行 COMMIT 后，才能修改成功
UPDATE employee SET name='任肥肥' WHERE id=1;

-- 通过 name 字段，修改 monkey 字段的值
-- 结果：不能修改。InnoDB引擎如果不采用带索引的列。则会提升为表锁
-- 所以由于 name 不是 employee 表的索引，无法使用行锁，自动升级为表锁
-- 但是此处的表锁，跟【第一个客户端工具】的 id 为 1 的行锁有冲突，所以这里会阻塞
-- 只有当【第一个客户端工具】运行 COMMIT 后，才能修改成功
UPDATE employee SET money=30000 WHERE name='乔豆豆';

-- 提交事务
COMMIT;

-- InnoDB 写锁（排他锁）语法格式如下：
SELECT语句 FOR UPDATE;

/*
写锁（排他锁）的特点：加锁的数据，不能被其他事务【加锁查询】或【修改】
*/

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

/*
打开【第一个 MySQL 客户端工具】，先运行下面前 2 条 SQL，别运行 COMMIT 语句。
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录，并加入排他锁
SELECT * FROM employee WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;

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

/*
打开【第二个 MySQL 客户端工具】，手动逐条选中以下 SQL 语句并运行。
*/

-- 开启事务
START TRANSACTION;

-- 查询 id 为 1 的数据记录
-- 结果：普通查询没问题
SELECT * FROM employee WHERE id=1;

-- 查询 id 为 1 的数据记录，并加入共享锁
-- 结果：不能查询，会出现阻塞。因为写锁（排他锁）不能和其他锁共存
-- 只有当【第一个客户端工具】运行 COMMIT 后，才能查询
SELECT * FROM employee WHERE id=1 LOCK IN SHARE MODE;

-- 查询 id 为 1 的数据记录，并加入写锁（排他锁）
-- 结果：不能查询，会出现阻塞。因为写锁（排他锁）不能和其他锁共存
-- 只有当【第一个客户端工具】运行 COMMIT 后，才能查询
SELECT * FROM employee WHERE id=1 FOR UPDATE;

-- 修改 id 为 1 的姓名为天蓬
-- 结果：不能修改，会出现阻塞。因为写锁（排他锁）不能和其他锁共存
-- 只有当【第一个客户端工具】运行 COMMIT 后，才能修改
UPDATE employee SET name='天蓬' WHERE id=1;

-- 提交事务
COMMIT;

最后得出的结论就是：

（读锁）共享锁和（读锁）共享锁：兼容
（读锁）共享锁和（写锁）排他锁：冲突
（写锁）排他锁和（写锁）排他锁：冲突
（写锁）排他锁和（读锁）共享锁：冲突

（3）MyISAM 锁操作

MyISAM 和 MEMORY 仅支持表锁，而且不支持事务，只有 InnoDB 才支持事务，下面仅对 MyISAM 进行锁操作演示。

-- MyISAM 读锁，进行加锁的语法格式如下：
LOCK TABLE 表名 READ;

/*
读锁的特点：所有连接只能读取数据，不能修改
*/

-- 解锁（将当前会话所有的表进行解锁）
UNLOCK TABLES;

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

/*
打开【第一个 MySQL 客户端工具】，先运行下面前 3 条 SQL，别运行解锁语句。
*/

-- 为 employee 表加入读锁
LOCK TABLE employee READ;

-- 查询 employee 表
-- 结果：查询成功
SELECT * FROM employee;

-- 修改 employee 表的某条记录
-- 结果：修改失败，不阻塞，直接报错
UPDATE employee SET money=20000 WHERE id=1;

-- 解锁
UNLOCK TABLES;

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

/*
打开【第二个 MySQL 客户端工具】，手动逐条选中以下 SQL 语句并运行。
*/

-- 查询 employee 表
-- 结果：查询成功
SELECT * FROM employee;

-- 修改 employee 表的任意一条记录
-- 结果：修改失败，会出现阻塞。
-- 只有当【第一个客户端工具】运行 UNLOCK TABLES 之后，才能修改成功
UPDATE employee SET money=25000 WHERE id=2;

-- MyISAM 写锁，进行加锁的语法格式如下：
LOCK TABLE 表名 WRITE;

/*
写锁的特点：一个连接加锁后，只能该连接进行查询和修改操作，其他连接【不能查询】和【不能修改】
*/

-- 解锁（将当前会话所有的表进行解锁）
UNLOCK TABLES;

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

/*
打开【第一个 MySQL 客户端工具】，先运行下面前 3 条 SQL，别运行解锁语句。
*/

-- 为 employee 表添加写锁
LOCK TABLE employee WRITE;

-- 查询 employee 表
-- 结果：查询成功。当前连接可以查询
SELECT * FROM employee;

-- 修改 employee 表某条记录
-- 结果：修改成功。当前连接可以修改
UPDATE employee SET money=3999 WHERE id=2;

-- 解锁
UNLOCK TABLES;

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

/*
打开【第二个 MySQL 客户端工具】，手动逐条选中以下 SQL 语句并运行。
*/

-- 查询 employee 表
-- 不能查询。
-- 只有当【第一个客户端工具】运行 UNLOCK TABLES 之后，才能查询成功
SELECT * FROM employee;

-- 修改 employee 表某条记录
-- 结果：不能修改。
-- 只有当【第一个客户端工具】运行 UNLOCK TABLES 之后，才能修改成功
UPDATE employee SET money=2999 WHERE id=3;

（4）悲观锁和乐观锁

悲观锁的特点：

顾名思义就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
上面介绍的行锁、表锁、读锁、写锁等等所有的锁都是悲观锁。

乐观锁的特点：

顾名思义就是很乐观，每次操作数据的时候认为没有人会来修改它，所以不去加锁。
但是在更新的时候会去判断在此期间数据有没有被修改。
需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查验证数据时效性。

悲观锁和乐观锁的使用场景：

对于读的操作远多于写的操作的时候，一个更新操作加锁会阻塞所有的读取操作，降低了并发处理性能，最后还得解锁，具有一定的开销。这时候可以选择乐观锁。
如果是读写操作的比例差距不是非常大，或者系统没有出现响应不及时、吞吐量瓶颈的问题，那就不要去使用乐观锁。因为乐观锁增加了实现的复杂度，也给业务带来了额外的风险。这时候可以选择悲观锁。

乐观锁的实现方式：

通过增加【版本号】来实现
- 给数据表中添加一个 version 列，每次更新后都将这个列的值加 1。
- 读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
- 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
- 用户自行根据这个特点，自行来决定怎么处理，比如重新开始一遍，或者放弃本次更新。
通过增加【标识符】来实现
- 和版本号方式基本一样，给数据表中添加一个列 dataflag，数据类型可以是 varchar(50)。
- 每次更新后都将最新时间更新到此列，或者随机生成一个 GUID 或者 UUID 更新到此列。
- 读取数据时，将 dataflag 读取出来，在执行更新的时候，比较 dataflag 的值。
- 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
- 用户自行根据这个特点，自行来决定怎么处理，比如重新开始一遍，或者放弃本次更新。

-- 获取数据的 version 值，假如获取出来的值是 100
SELECT version FROM employee WHERE id=1;

-- 同时使用 id 和 version 作为 where 条件，修改该数据的值 
UPDATE employee SET name='弼马温',version=version+1 WHERE id=1 AND version=100;

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

-- 获取数据的 dataflag 值，假如获取出来的值是 bedcb0aa-85ba-11ec-b014-902e16a6f8db
SELECT dataflag FROM employee WHERE id=2;

-- 同时使用 id 和 dataflag 作为 where 条件，修改该数据的值 
UPDATE employee SET name='齐天大圣',version=uuid()
WHERE id=2 AND version='bedcb0aa-85ba-11ec-b014-902e16a6f8db';

到此为止，MySQL 的索引和锁相关的知识点，基本上总结完毕，希望对大家有所帮助。

码农公寓