文章目录
本系列文章：
MySQL（一）SQL语法、数据类型、常用函数、事务
MySQL（二）MySQL SQL练习题
MySQL（三）视图、存储过程、索引
MySQL（四）存储引擎、锁
MySQL（五）MySQL架构、数据库优化、主从复制
MySQL（六）SQL语句优化
MySQL（七）MySQL和Oracle、PostgreSQL的区别
一、存储引擎
存储引擎：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。
MySQL 5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。InnoDB最常使用。
查看Mysql提供的所有存储引擎，命令为：show engines，示例：
查看某个表的存储引擎命令为：show table status like "table_name" ;。
- MEMORY存储引擎
MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。
每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中。这样有利于数据的快速处理，提高整个表的效率。服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。
MEMORY默认使用哈希索引。速度比使用B型树索引快。
MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。
1.1 MyISAM和InnoDB
1.1.1 MyISAM*
MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，扩展名分别是：FRM（存储表定义）、MYD（MYData，存储数据）、MYI （MYIndex，存储索引）。
数据文件和索引文件可以放置在不同的目录。
MyISAM类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能访问，会提示需要修复或者访问后返回错误的结果。MyISAM类型的表提供修复的工具，可以用CHECK TABLE语句来检查MyISAM表的健康，并用REPAIR TABLE语句修复一个损坏的MyISAM表。
表损坏可能导致数据库异常重新启动。
MyISAM不支持事务，但是每次查询都是原子的；支持表级锁，即每次操作是对整个表加锁。
1.1.2 InnoDB*
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
- 1、自动增长列
InnoDB表的自动增长列的值默认从1开始，可以通过ALTER TABLE table_name AUTO_INCREMENT = n;语句强制设置自动增长列的初识值。
对于InnoDB表，自动增长列必须是索引(一般会将主键设置为自动增长列，并添加主键索引)。如果是组合索引，也必须是组合索引的第一列。 - 2、外键约束
MySQL支持外键的存储引擎只有InnoDB。在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。
1.1.3 两者的区别
MyISAM与InnoDB区别：
| MyISAM | Innodb | |
|---|---|---|
| 存储结构 | 每张表被存放在三个文件：frm：表结构 MYD：数据文件 MYI：索引文件 | frm：表定义文件 ibd ：数据文件 InnoDB表的大小只受限于操作系统文件的大小，一般为2GB |
| 存储空间 | MyISAM可被压缩，存储空间较小 | InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
| 可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了 |
| 记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 锁支持 | 支持表级锁 | 支持行级锁、表级锁 |
| SELECT | MyISAM更优 | |
| INSERT、UPDATE、DELETE | InnoDB更优 | |
| select count(*) | myisam更快，因为myisam内部维护了一个计数器，可以直接调取。 | 扫描表来计算有多少行 |
| 索引的实现方式 | B+树索引，myisam 是堆表 | B+树索引，Innodb 是索引组织表 |
| 哈希索引 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持(在5.6之后支持) |
| 索引类型 | 非聚簇索引 | 聚簇索引 |
| 适合操作类型 | 大量select | 大量insert、delete、update |
| MVCC | 不支持 | 支持 |
| 存储限制 | 有 | 64TB(理论上一个表空间最多存储64TB的数据) |
- 1、事务支持
MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交。 - 2、存储结构
MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI(MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。 - 3、存储空间
MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 - 4、可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。 - 5、表锁差异
MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是innodb的最大特色。 - 6、表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。InnoDB的主键范围更大，最大是MyISAM的2倍。 - 7、表的具体行数
MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。
InnoDB：没有保存表的总行数(只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。 - 8、CURD操作
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。 - 9、外键
MyISAM：不支持。
InnoDB：支持。
InnoDB：
- 支持事务处理。
- 支持外键。
- 支持行锁。
- 不支持FULLTEXT类型的索引（在Mysql5.6已引入）。
- 不保存表的具体行数，扫描表来计算有多少行。
- 对于AUTO_INCREMENT类型的字段，必须包含只有该字段的索引。
- DELETE表时，是一行一行的删除。
- InnoDB把数据和索引存放在表空间里面。
- 跨平台可直接拷贝使用。
- 表格很难被压缩。
MyISAM：
- 不支持事务，回滚将造成不完全回滚，不具有原子性。
- 不支持外键。
- 支持全文搜索。
- 保存表的具体行数，不不带where时，直接返回保存的行数。
- DELETE表时，先drop表，然后重建表。
- MyISAM表被存放在三个文件 。frm：文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)。
- 跨平台很难直接拷贝。
- AUTO_INCREMENT类型字段可以和其他字段一起建立联合索引。
- 表格可以被压缩。
1.1.4 存储引擎选择*
如果没有特别的需求，使用默认的Innodb即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性（通过bin-log日志等，可以从灾难中恢复）；并发量高；支持事务和外键。比如OA自动化办公系统。
1.2 存储引擎相关问题
1.2.1 如何怎么针对表设置引擎
在建表时通过ENGINE=xxx设置引擎。示例：
create table person(
id int primary key auto_increment,
username varchar(32)
) ENGINE=InnoDB
1.2.2 怎么根据引擎选择合适的字符串类型
- MyISAM
MyISAM数据表，最好使用固定长度的数据列代替可变长度的数据列。 - InnoDB
建议使用VRCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列。因此本质上，使用固定长度的CHAR列不一定比使用可变长度的VARCHAR列简单。因此，主要的性能是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘IO是比较好的。
1.2.3 一张表，里面有ID自增主键，当插入了17条记录之后，删除了最后3条记录。把Mysql重启，再插入一条记录，这条记录的ID是18还是15*
1）如果表的存储引擎是MyISAM，那么是18。因为 MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。
2）如果表的存储引擎是InnoDB，那么是15。InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。
1.2.4 count( * )在不同引擎的实现方式*
MyISAM：把一个表的总行数存在了磁盘上，执行count(*)的时候直接返回这个数值，效率高。
InnoDB：执行count(*)的时候，需要把数据一行一行从引擎读出来然后累积计数。
1.2.5 Innodb如何实现Mysql的事务
事务进行过程中，每次sql语句执行，都会记录undo log和redo log，然后更更新数据形成脏页，然后redo log按照时间或者空间等条件进行落盘，undo log和脏页按照checkpoint进行落盘，落盘后相应的redo log就可以删除了。此时，事务还未COMMIT，如果发生崩溃，则首先检查checkpoint记录，使用相应的redo log进行数据和undo log的恢复，然后查看undo log的状态发现事务尚未提交，然后就使用undo log进行行事务回滚。事务执行COMMIT操作时，会将本事务相关的所有redo log都进行落盘，只有所有redo log落盘成功，才算COMMIT成功。然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了崩溃，则只使用redo log恢复数据。
1.2.6 为什么推荐InnoDB引擎使用自增主键*
InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置。
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
二、锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是：不同的存储引擎支持不同的锁机制：
MyISAM和MEMORY存储引擎采用的是表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。
2.1 表级锁、行级锁和页面锁*
在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁、表级锁和页级锁。
- 1、表级锁
表级锁是Mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。
该锁定机制获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。表锁被大部分的Mysql引擎支持，MyISAM和InnoDB都支持表级锁。
MyISAM只支持表锁，因此性能相对Innodb来说相对较低，而Innodb支持表锁和行锁。
表级锁的特点：
- 开销小，加锁快；
- 不会出现死锁；
- 锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 2、行级锁
行锁的是Mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。
主要是Innodb使用行锁，Innodb也是Mysql在5.5.5版本之后默认使用的存储引擎。
行级锁的特点：
- 开销大，加锁慢；
- 会出现死锁；
- 锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 3、页面锁
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。BDB引擎支持页级锁。 - 4、表级锁和行级锁的对比
仅从锁的角度来说：
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易；
OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。
- OLTP系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
- OLAP系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。
2.2 MyISAM中的锁
MyISAM存储引擎只支持表锁。
MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性：
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。MyISAM表的读操作与写操作之间，以及写操作之间是串行的。
2.2.1 表锁的使用
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预。
2.2.2 并发插入
在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
- 当concurrent_insert设置为0时，不允许并发插入。
- 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
- 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
MyISAM表的读和写是串行的，这是就总体而言的，在一定条件下，MyISAM也支持查询和插入操作的并发执行。
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺。
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 352 |
| Table_locks_waited | 2 |
+-----------------------+-------+
如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。
2.2.3 MyISAM的锁调度(默认写锁优先)
MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。
如果一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL会先让写进程获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因。大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
开发者可以通过一些设置来调节MyISAM的调度行为。
- 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
- 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
- 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
2.3 InnoDB锁
2.3.1 行锁的实现（命中索引+select…for update）*
InnoDB行锁是通过给索引上的索引项加锁来实现的。
InnoDB这种行锁实现特点意味着：InnoDB只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。
行锁使用示例：
-- 开始事务
START TRANSACTION;
-- 设置自动提交模式为手动提交
SET autocommit = 0;
-- 获取行锁
SELECT * FROM table_name WHERE id = 'xxx' FOR UPDATE;
-- 执行相关操作...
-- 提交事务
COMMIT;
- 1、在不通过索引条件查询的时候，innodb使用的是表锁
比如创建一张表：
create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
session1可以通过select * from tab_no_index where id = 1 for update命令，只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。
- 2、创建带索引的表进行条件查询，innodb使用的是行锁
比如创建一张表：
create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
此时当一个session对一个行加锁时，不影响另一个session对别的行的操作。
2.3.2 共享锁（select…lock in share mode）和排他锁（select…for update/insert/delete/update）*
InnoDB实现了以下两种类型的行锁：共享锁和排他锁。
- 1、共享锁（Shared Locks）
共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
示例：若事务A对数据对象1加上共享锁，则事务A可以读数据对象1但不能修改，其他事务只能再对数据对象1加共享锁，而不能加排他锁，直到事务A释放数据对象1上的共享锁。这保证了其他事务可以读数据对象1，但在事务A释放数据对象1上的共享锁之前不能对数据对象1做任何修改。sql示例：
SELECT * from TABLE where id = 1 lock in share mode;
在查询语句后面增加lock in share mode，MySQL会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。
加上共享锁后，对于update、insert、delete语句会自动加排它锁。
- 2、排他锁（Exclusive Locks）
排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁。
示例：若事务A对数据对象1加上排他锁，事务A可以读数据对象1也可以修改数据对象1，其他事务不能再对数据对象1加任何锁，直到事务A释放数据对象1上的锁。这保证了其他事务在事务A释放数据对象1上的锁之前不能再读取和修改数据对象1。
sql示例：
select status from TABLE where id=1 for update;
- 3、共享锁和排他锁的使用
InnoDB引擎默认的规则：
1）update、delete、insert都会自动给涉及到的数据加上排他锁；
2）select语句默认不会加任何锁类型。
如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句【不常用】。
加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据。但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。
2.3.3 意向共享锁和意向排它锁
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
- 意向共享锁（IS）
事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁。 - 意向互斥锁（IX）
事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁。
意向共享锁和意向排它锁总称为意向锁。意向锁的出现是为了支持Innodb支持多粒度锁。
意向锁是表级别锁。
当需要给一个表加锁的时候，需要根据意向锁去判断表中有没有数据行被锁定，以确定是否能加成功。如果意向锁是行锁，那么我们就得遍历表中所有数据行来判断。如果意向锁是表锁，则直接判断一次就知道表中是否有数据行被锁定了。所以说将意向锁设置成表级别的锁的性能比行锁高的多。
有了意向锁之后，前面例子中的事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。
意向锁的作用就是：当一个事务在需要获取资源的锁定时，如果该资源已经被排他锁占用，则数据库会自动给该事务申请一个该表的意向锁。如果自己需要一个共享锁定，就申请一个意向共享锁。如果需要的是某行（或者某些行）的排他锁定，则申请一个意向排他锁。
2.3.4 InnoDB四种锁的兼容情况
InnoDB2种行锁和2种表锁的兼容情况：
如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显式给记录集加共享锁或排他锁。
//共享锁（S）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
//排他锁（X)
SELECT * FROM table_name WHERE ... FOR UPDATE
用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。
2.3.5 间隙锁（Next-Key 锁）
当用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,…,100,101，下面的SQL：
Select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求。另外一方面，是为了满足其恢复和复制的需要。
在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。
2.3.6 记录锁和临键锁
间隙锁、记录锁、临键锁都是Innodb的行锁，前面我们说过行锁是基于索引实现的，一旦加锁操作没有操作在索引上，就会退化成表锁。
间隙锁：作用于非唯一索引上，主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。
如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。
记录锁：它封锁索引记录，作用于唯一索引上。
临键锁：作用于非唯一索引上，是记录锁与间隙锁的组合。
2.3.7 表锁的使用场景
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁：
- 1、事务需要更新大部分或全部数据，表又比较大
如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。 - 2、事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚
这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。
在InnoDB下，使用表锁要注意以下两点：
- 1、使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道 MySQL 加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB 才能自动识别涉及表级锁的死锁；否则，InnoDB 将无法自动检测并处理这种死锁。
- 2 、在用LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式示例：
#如果需要写表 t1 并从表 t 读，可以按如下做：
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;
2.3.8 死锁*
死锁是指两个或两个以上事务在执行过程中因争抢锁资源而造成的互相等待的现象。
在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。
比如，两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
以下是几种避免死锁的常用方法：
- 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
- 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
- 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；如果业务处理不好可以用分布式事务锁或者使用乐观锁。
- 在更新操作时，我们应该尽量使用主键来更新表字段，这样可以有效避免一些不必要的死锁发生。
- 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁。
- 在允许幻读和不可重复读的情况下，尽量使用RC事务隔离级别，可以避免gap lock（间隙锁） 导致的死锁问题。
- 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率。
如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
处理死锁的两种方式：
- 设置超时时间，超时后自动释放。在InnoDB中，参数innodb_lock_wait_timeout（单位：秒）是用来设置超时时间的。
- 发起死锁检测（innodb_deadlock_detect，值为：on/off），主动回滚其中一条事务，让其他事务继续执行。
解决MySQL死锁的步骤：
- 确认死锁情况。通过命令SHOW ENGINE INNODB STATUS;查看MySQL的日志文件，找到LOCK WAIT和DEADLOCK DETECTED相关的信息，确定是否发生了死锁。
- 确定死锁的原因。通过日志文件，查看死锁的原因，是由于某个事务的等待时间过长还是由于锁竞争造成的。
- 终止其中一个事务。需要终止造成死锁的其中一个事务，使另一个事务可以继续执行。可以通过命令KILL或通过MySQL客户端手动结束事务。
- 重试事务。终止死锁事务后，需要重新执行所有等待事务，以确保所有事务都能得到执行。
- 修改程序设计。在确定死锁的原因后，可以修改程序设计，减少事务之间的锁竞争，从而避免死锁的发生。
死锁问题参考：mysql死锁分析工具show engine innodb status。
2.4 乐观锁和悲观锁
- 分布式锁的功能
1）分布式锁使用者位于不同的机器中，锁获取成功之后，才可以对共享资源进行操作。
2）锁具有重入的功能：即一个使用者可以多次获取某个锁。
3）获取锁有超时的功能：即在指定的时间内去尝试获取锁，超过了超时时间，如果还未获取成功，则返回获取失败。
4）能够自动容错，比如：A机器获取锁lock1之后，在释放锁lock1之前，A机器挂了，导致锁lock1未释放，结果会lock1一直被A机器占有着，遇到这种情况时，分布式锁要能够自动解决，可以这么做：持有锁的时候可以加个持有超时时间，超过了这个时间还未释放的，其他机器将有机会获取锁。
2.4.1 乐观锁*
假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。乐观锁实现方式：一般会使用版本号机制或CAS算法实现。
乐观锁可以作为分布式锁的一种实现方式。
乐观锁不是数据库自带的，需要开发者去实现。比如以版本号机制为例：可以在表中新增一个版本号，每次更新数据时候将版本号作为条件，并且每次更新时候版本号+1。
数据库表里有三个字段，分别是 id,value、version。查询时：
select id,value,version from TABLE where id=#{id}
更新时：
update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};
乐观锁的优点：乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统整体性能表现。
乐观锁的缺点：乐观锁机制往往基于系统中的数据存储逻辑，因此也具备一定的局限性，如在上例中，由于乐观锁机制是在我们的系统中实现，来自外部系统的更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。在系统设计阶段，应该充分考虑到这些情况出现的可能性，并进行相应调整（如将乐观锁策略在数据库存储过程中实现，对外只开放基于此存储过程的数据更新途径，而不是将数据库表直接对外公开）。
总结：读用乐观锁，写用悲观锁。
2.4.2 悲观锁*
悲观锁：假定会发生并发冲突，在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制（select ... for update）。
当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
MySQL中，select for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在MySQL中用悲观锁务必要确定走了索引，而不是全表扫描。
实现悲观锁时，必须先使用set autocommit=0;关闭Mysql的autoCommit属性，因为查询出数据之后就要将该数据锁定。
悲观锁的使用示例：
# 1. 开始事务
begin; 或start transaction;
# 2. 查询表信息
select status from TABLE where id=1 for update;
# 3. 插入一条数据
insert into TABLE (id,value) values (2,2);
# 4. 修改数据为
update TABLE set value=2 where id=1;
# 5. 提交事务
commit;
在第2步我们将数据查询出来后直接加上排它锁（X）锁，防止别的事务来修改事务1，直到我们commit后，才释放了排它锁。
悲观锁的优点：保证了数据处理时的安全性。
悲观锁的缺点：加锁造成了开销增加，并且增加了死锁的机会。降低了并发性。
2.4.3 乐观苏和悲观锁的适用场景
乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。
悲观锁和乐观锁是数据库用来保证数据并发安全防止更新丢失的两种方法，例子在select ... for update前加个事务就可以防止更新丢失。
悲观锁和乐观锁大部分场景下差异不大，一些独特场景下有一些差别，一般可以从如下几个方面来判断。
响应速度：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁。冲突频率：如果冲突频率非常高，建议采用悲观锁，保证成功率，如果冲突频率大，乐观锁会需要多次重试才能成功，代价比较大。重试代价：如果重试代价大，建议采用悲观锁。
2.5 Mysql中的锁总结
2.5.1 MyISAM锁
- 共享读锁之间是兼容的，但共享读锁与排他写锁之间，以及排他写锁之间是互斥的，也就是说读和写是串行的。
- 在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
- MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
- 由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。
2.5.2 InnoDB锁
InnoDB是基于索引来完成行锁。示例： select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁。
在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：
- 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
- 选择合理的事务大小，小事务发生锁冲突的几率也更小；给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
- 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
- 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
- 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。
2.6 隔离级别与锁
在Read Uncommitted（读未提交）级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突。
在Read Committed（读已提交）级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁。
在Repeatable Read（可重复读）级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
SERIALIZABL（串行化） 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。
2.7 MVCC*
MVCC( Multiversion concurrency control ) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view（事务进行快照读操作的时候生产的读视图）和版本链找到对应版本的数据。
mysql的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能。MVCC只在REPEATABLE READ（可重复度读）和READ COMMITED（读已提交）两个隔离级别下工作，其它两个隔离级别下不存在MVCC。
- MVCC作用
提升并发性能。对于高并发场景，MVCC比行级锁开销更小。
2.7.1 MVCC实现原理
MVCC的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。
DB_TRX_ID：当前事务id，通过事务id的大小判断事务的时间顺序。
DB_ROLL_PRT：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链。
DB_ROLL_ID：主键，如果数据表没有主键，InnoDB会自动生成主键。
每条表记录大概是这样的：
使用事务更新行记录的时候，就会生成版本链，执行过程如下：
- 用排他锁锁住该行；
- 将该行原本的值拷贝到 undo log ，作为旧版本用于回滚；
- 修改当前行的值，生成一个新版本，更新事务 id，使回滚指针指向旧版本的记录，这样就形成一条版本链。
举个例子。
- 1、初始数据如下，其中DB_ROW_ID（主键）和DB_ROLL_PTR为空。
- 2、事务A对该行数据做了修改，将age修改为12。
- 3、之后事务B也对该行记录做了修改，将age修改为8。
- 4、此时undo log有两行记录，并且通过回滚指针连在一起。
2.7.2 read view
read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。
在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。
不同隔离级别创建read view的时机不同。
- read committed
每次执行select都会创建新的read_view，保证能读取到其他事务已经提交的修改。 - repeatable read
在一个事务范围内，第一次select时更新这个read_view，以后不会再更新，后续所有的select都是复用之前的read_view。这样可以保证事务范围内每次读取的内容都一样，即可重复读。
2.7.3 DATA_TRX_ID（当前事务ID）
DATA_TRX_ID表示每个数据行的最新的事务ID；up_limit_id表示当前快照中的最先开始的事务； low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。
- DATA_TRX_ID<up_limit_id
说明在创建 read view 时，修改该数据行的事务已提交，该版本的记录可被当前事务读取到。 - DATA_TRX_ID>=low_limit_id
说明当前版本的记录的事务是在创建read view之后生成的，该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本，然后重新判断该版本的记录对当前事务的可见性。 - up_limit_id<=DATA_TRX_ID<low_limit_i
需要在活跃事务链表中查找是否存在ID为DATA_TRX_ID的值的事务。
如果存在，因为在活跃事务链表中的事务是未提交的，所以该记录是不可见的。此时需要通过版本链找到上一个版本，然后重新判断该版本的可见性。
如果不存在，说明事务trx_id已经提交了，这行记录是可见的。
总结：InnoDB的MVCC是通过read view和版本链实现的，版本链保存有历史版本记录，通过read view判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。
2.8 快照读和当前读
表记录有两种读取方式。
快照读：读取的是快照版本。普通的SELECT就是快照读。通过mvcc来进行并发控制的，不用加锁。
当前读：读取的是最新版本。 UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。
快照读情况下，InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。
- MySQL如何避免幻读？
在快照读情况下，MySQL通过mvcc来避免幻读。
在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。
next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。
Serializable隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。
2.8 Mysql锁的相关问题
2.8.1 MySQL中InnoDB引擎的行锁是通过加在什么上完成的*
InnoDB行锁是通过给索引上的索引项加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
2.8.2 优化高并发事务
- 1、结合业务场景，使用低级别事务隔离
在高并发业务中，为了保证业务数据的一致性，操作数据库时往往会使用到不同级别的事务隔离。隔离级别越高，并发性能就越低。 - 2、避免行锁升级表锁
在InnoDB中，行锁是通过索引实现的，如果不通过索引条件检索数据，行锁将会升级到表锁，表锁是会严重影响到整张表的操作性能的。 - 3、控制事务的大小，减少锁定的资源量和锁定时间长度
多个线程尽量以相同的顺序去获取资源。
不能将锁的粒度过于细化， 不然可能会出现线程的加锁和释放次数过多， 反而效率不如一次加一把大锁。 - 4、读写分离
其实MySQL的并发事务调优和Java的多线程编程调优非常类似，都是可以通过减小锁粒度和减少锁的持有时间进行调优。
2.8.3 快照读和当前读
表记录有两种读取方式。
快照读：读取的是快照版本。普通的SELECT就是快照读。通过MVCC来进行并发控制的，不用加锁。
当前读：读取的是最新版本。 UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE 是当前读。
快照读情况下，InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。
- 例子
user表只有两条记录：
事务a和事务b同时开启事务 start transaction ；
事务a插入数据然后提交：
insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);
事务b执行全表的update；
update user set user_name = 'a';
事务b然后执行查询，查到了事务a中插入的数据。（下图左边是事务b，右边是事务a）：
这就是当前读出现的幻读现象。
- MySQL如何实现避免幻读
1）在快照读情况下，MySQL通过mvcc来避免幻读。
2）在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。
next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。
Serializable 隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。
3236
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
