4.锁机制

4.1 锁粒度划分

4.1.1 全局锁

顾名思义，全局锁就是对整个数据库实例加锁。

命令： Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

在可重复读隔离级别下开启一个事务也可以拿到一致性视图。

当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

MyISAM不支持事务，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

4.1.2 表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

4.1.2.1 表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

4.1.2.2 元数据锁(MDL锁)

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。

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

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

如何安全地给小表加字段？

1. 首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
2. 在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后再重试。

4.1.3 行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁。MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

• Record Lock：单个行记录上的范围
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

4.1.3.1 记录锁

单个行记录上的范围

记录锁很好理解，一般要通过主键或唯一索引加锁，以阻止其他事务插入，更新，删除id=1这一行。

select * from test where id=6 for update；//id是索引列时，该句准确且只锁id=6这一行。

4.1.3.2 间隙锁

间隙锁，锁定一个范围，但不包含记录本身。

比如下面的表里面的数据ID 为 1,4,5,7,10 ,那么会形成以下几个间隙区间， (－∞,1)、(1,4)、(7,10)(10,+∞)

间隙锁作用：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询出的结果会不一样。

加锁规则：

1.对主键或唯一索引，如果当前读时，where条件全部精确命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁。 　　

2.没有索引的列，当前读操作时，会加全表gap锁，生产环境要注意。 　　

3.非唯一索引列，如果where条件部分命中(>、<、like等)或者全未命中，则会加附近Gap间隙锁。

4.1.3.3 临键锁（next-key）

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

goods表中隐藏的临键锁有：(-∞, 96], (96, 99], (99, +∞]

4.2 锁操作划分

4.2.1 读锁（共享锁、S锁）

lock in share mode

• 允许其它事务也增加共享锁读取
• 不允许其它事物增加排他锁 (for update)
• 当事务同时增加共享锁时候，事务的更新必须等待先执行的事务 commit 后才行，如果同时并发太大可能很容易造成死锁

4.2.2 写锁（排它锁、X锁）

for update

当一个事物加入排他锁后，不允许其他事务加共享锁或者排它锁读取，更加不允许其他事务修改加锁的行。

4.2.3 意向共享锁、意向排它锁

T1:

SELECT * FROM A WHERE id = 1 lock in share mode；（加S锁）



T2:

SELECT * FROM A WHERE id > 0 for update; (加X锁)

看上面这2个SQL事务，T1执行时候，对id=1这行加上了S锁，T2执行前，需要获取全表的更新锁进行判断，即：

step1： 判断表A是否有表级锁

step2： 判断表A每一行是否有行级锁

当数据量较大时候（我们一张表一般500-5000万数据），step2这种判断极其低效。

意向锁协议

• 事务要获取表A某些行的S锁必须要获取表A的IS锁
• 事务要获取表A某些行的X锁必须要获取表A的IX锁

step2->发现表A有IS锁，说明表肯定有行级的S锁，因此，T2申请X锁阻塞等待，不需要判断全表，判断效率极大提高

4.3 锁设计思想

4.3.1 悲观锁

顾名思义，就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。

悲观锁的实现，通常依靠数据库提供的锁机制实现。

4.3.2 乐观锁

乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。

• 乐观锁的版本号机制

在表中设计一个版本字段 version，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE ... SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功。

• 乐观锁的时间戳机制

时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

4.4 死锁

事务A	事务B
begin;update lock_test set c = 32 where id = 1;
	begin;update lock_test set c = 112 where id = 2;
update lock_test set c = 132 where id = 2;(blocked)
	update lock_test set c = 113 where id = 1;( > 1213 - Deadlock found when trying to get lock; try restarting transaction)

出现死锁时，有2种解决方案：

1. 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout （默认值50s）来设置。（超时时间不好设置）
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

死锁检测

每个新来的被堵住的线程，都会判断会不会由于自己的加入导致了死锁，复杂度O(n)，加入有1000个并发线程，那么死锁检测操作是100万量级，导致CPU 利用率很高，但是每秒却执行不了几个事务。

控制并发度

1. 限制同一行最多只有10个线程在更新，并发控制在数据库服务端，可以考虑在中间件实现，或者修改mysql源码，基本思路是对于相同行的更新，在进入引擎之前排队。
2. 将一行改成逻辑上的多行来减少锁冲突，以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

5. 索引

5.1 索引结构

5.1.1 Hash结构

图 1 哈希表示意图

哈希表这种结构适用于只有等值查询的场景

5.1.2 有序数组

图 2 有序数组示意图

有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

5.1.3 二叉搜索树

图 3 二叉搜索树示意图

如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

5.1.4 N叉树

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准。以 InnoDB 为例，分析一下其中的索引模型。

5.1.4.1 B树

1. B树的节点可以有多个子节点；
2. B树非叶子节点也可以存放数据；
3. 所有的叶子节点位于同一层。
4. B树就像一棵“m叉搜索树”（m是子树的最大个数），时间复杂度为O(logm(n))
5. B树与AVL的时间复杂度是相同的，但由于B树的层数少，磁盘IO次数少，实践中B树的性能要优于AVL等二叉树。

5.1.4.2 B+树

1. 修改key与子树的组织逻辑，将索引访问都落到叶子节点
2. 按顺序将叶子节点串起来（方便范围查询）

5.2 索引物理结构

5.2.1 非聚簇索引

MyISAM索引物理结构：索引和数据不在一个文件

bottom_tab.frm：表定义，是描述表结构的文件

bottom_tab.myi：数据信息文件

bottom_tab.myd：索引信息文件

主键索引

普通索引（二级索引）

索引中每一个叶子节点仅仅包含行号(row number)，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

5.2.2 聚簇索引

Innodb索引物理结构：索引、数据在一个文件

bottom_tab.frm：表定义，是描述表结构的文件

bottom_tab.ibd：表数据和索引存储文件，索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

主键索引

聚簇索引：叶子节点包含了完整的数据记录。

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

普通索引（二级索引）

首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

5.3 索引逻辑结构

5.3.1 普通索引（单值、复合）、唯一索引

查询过程

1.二分查找法找到叶子节点；

2.普通索引，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录;

3.对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

性能差距：微乎其微，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存（每个数据页大小16KB），因此通常只需一次指针寻找和一次计算。如果下一个k=5记录不在这个数据页，必须读取下一个数据也，概率很低，忽略不计。

更新过程

（1）change buffer

当更新一个数据页时，如果数据也在内存中，直接更新，如果不在内存，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样不需要从磁盘中读入数据页了，下次查询时需将磁盘数据页读入内存，然后执行change buffer中与这个有关的操作（过程称为merge），得到正确的数据。

带change buffer的更新过程

1. Page 1 在内存中，直接更新内存；
2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3. 将上述两个动作记入 redo log 中。

带change buffer的读过程

1. 读 Page 1 的时候，直接从内存返回。
2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。

（2）触发持久化merge的操作

• 访问数据页；
• 后台线程定期merge；
• 数据集正常关闭（shut down）过程中，也会执行merge操作，change buffer在内存中有拷贝，也会被写入磁盘中。

（3）change buffer优点

• 减少读磁盘，执行速度得到明显提升；
• 避免占用内存，提高内存利用率

（4）change buffer适用场景

页面在写完后立马访问的概率比较低时，使用changeBuffer效果最好，若立马访问，随机IO的次数不会变少，反而增加changeBuffer维护成本，这种情况可选择关changeBuffer功能。

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

mysql>show variables like '%innodb_change_buffer%'

innodb_change_buffer_max_size	25	change buffer默认占buffer pool的25%
innodb_change_buffering	all	控制对哪些操作启用 Change Buffer 功能，默认all--all：默认值。开启buffer inserts、delete-marking operations、purges--none： 不开启change buffer--inserts： 只是开启buffer insert操作--deletes: 只是开delete-marking操作--changes： 开启buffer insert操作和delete-marking操作--purges： 对只是在后台执行的物理删除操作开启buffer功能

基于上述原因，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

（5）redo log和change buffer对比

简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

5.3.2 多列索引（联合索引）

多个字段联合起来（索引最左前缀原理）

联合索引也是排好序的数据结构 例如（a,b,c）依次从左到右排序 也就是先排好a，再b，再到c

5.4 索引优化

索引优化是优化器的工作，扫描行数是影响执行代价的因素之一，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

5.4.1 慢查分析

慢查分析工具

1.开启慢查日志

show variables like '%query%';

字段	默认值	含义
slow_query_log	OFF	是否显示慢查询日志，on-是;off-否
slow_query_log_file	/var/lib/mysql/ubuntu-slow.log	慢查询日志目录
long_query_time	10.000000	慢查询界定，单位s
log-queries-not-using-indexes	ON	记录未使用索引的查询

2.explain命令

-- 实际SQL，查找用户名为Jefabc的员工

select * from t4 where name = 'Jefabc';

-- 查看SQL是否使用索引，前面加上explain即可

explain select * from t4 where name = 'Jefabc';

• id：标识符，表示执行顺序
• select _type：查询类型
• table：输出行所引用的表
• partitions：使用的哪个分区，需要结合表分区才可以看到
• type：表示按某种类型来查询，例如按照索引类型查找，按照范围查找。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all
• possible_keys：可能用到的索引，保存的是索引名称，如果是多个索引的话，用逗号隔开
• key：实际用到的索引，保存的是索引名称，如果是多个索引的话，用逗号隔开
• key_len：表示本次查询中，所选择的索引长度有多少字节
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：显示mysql认为执行查询时必须要返回的行数
• filtered：通过过滤条件之后对比总数的百分比
• extra：额外的信息，例如：using file sort ，using where， using join buffer，using index等

5.4.2 优化方案

1. 注意条件函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

mysql>explain select * from test where month(time)=7;

mysql>explain select * from test where time=7;

2. 注意隐式类型转换

mysql> select * from tradelog where tradeid=110717;

tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。

那么，现在这里就有两个问题：数据类型转换的规则是什么？为什么有数据类型转换，就需要走全索引扫描？

这里有一个简单的方法，看 select “10” > 9 的结果：

• 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
• 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

对于优化器来说，这个语句相当于：

mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

3. 联合索引最左前缀原则

如果建立的是复合索引，索引的顺序要按照建立时的顺序，即从左到右，如：a->b->c（和 B+树的数据结构有关）

• a->c：a 有效，c 无效
• b->c：b、c 都无效
• c：c 无效

mysql> explain select * from test where a = -1 and b = 123 and c = 't';

explain select * from test where b = 123 and c = 't';

4. 索引不要放在范围查询右边

比如复合索引：a->b->c，当 where a="" and b>10 and c=""，这时候只能用到 a 和 b，c 用不到索引，因为在范围之后索引都失效（和 B+树结构有关）

5. like 模糊搜索

失效情况

• like "%张三%"
• like "%张三"

解决方案

• 使用复合索引，即 like 字段是 select 的查询字段，如：select name from table where name like "%张三%"
• 使用 like "张三%"

6. 利用覆盖索引避免回表操作

mysql>explain select * from test where a = 1 and b = 123 and c = 't';

mysql>explain select a,b from test where a = 1 and b = 123 and c = 't';

7. 在varchar上建立索引，指定索引长度

8. order by、group by 优化

当查询语句中使用 order by 进行排序时，如果没有使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者并发高的时候，会有性能问题，需要优化。