MySQL浅谈（索引、锁）

1. MySQL引擎

mysql> show engines;

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL

其他引擎详情请移步：dev.mysql.com/doc/refman/…

• InnoDB

InnoDB: The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.

• 翻译：

innodb: mysql 5.7 中的默认存储引擎。innodb 是 mysql 的事务安全 (符合 acid) 存储引擎, 具有提交、回滚和崩溃恢复功能, 可保护用户数据。innodb 行级锁定 (不升级到更粗粒度锁) 和 oracle 类型一致使用非锁定读取，可提高多用户并发性和性能。innodb 将用户数据存储在聚集索引中, 以减少基于主键的常见查询的 I/O。为了保持数据完整性, innodb 还支持外键。有关 innodb 的详细信息, 请参阅第14章, inodb 存储引擎。

InnoDB存储数据结构 - B+Tree

• 为什么使用B+Tree?
  索引的常见模型常见的有，哈希表、有序数组、搜索树。
  有序数组，优点是等值查询，范围查询都非常快，缺点也很明显，就是插入效率太低，因为如果从中间插入，要移动后面所有的元素。

Hash Index Characteristics(哈希索引特性)

Hash indexes have somewhat different characteristics from those just discussed:
They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible.

哈希结构只适用于等值查询（但这样速度非常快）。哈希结构不支持顺序检索例如'<'、'>'、"between and"等，这种存储结构属于“键值”查询，符合这种需求可以考虑使用哈希索引。

The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)

优化器不能使用哈希索引来加快 order by 操作。(此类型的索引不能用于按顺序搜索下一个条目。

MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM or InnoDB table to a hash-indexed MEMORY table.

mysql 不能大致确定两个值之间有多少行 (范围优化器使用它来决定要使用哪个索引)。如果将 MyISAM 或 InnoDB 表更改为哈希索引的内存表, 这可能会影响某些查询。

• Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

只有等值匹配才能适用哈希结构查询某一行。（而适用B-tree索引，最左前缀就可以用于查询。） 哈希表，优点就是查询快，缺点是范围查询效率很低（因为无序）。适用于等值查询。

B-Tree Index Characteristics(B树索引特性)

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

一个B树索引可以适用于=、>、>=、<、<=、BETWEEN 等操作符。B树索引也可以用于LIKE比较，只有当LIKE的参数是一个字符串常量并且不以通配符开始才可以适用索引。 树结构，优点有序，并且多叉树可以减少磁盘I/O次数。

• B-Tree和B+Tree。

首先，B-Tree读B树，而不读B减树。从上图可以看出B树可能没有遍历到叶子节点就命中目标，而B+树每个父节点都会出现在子节点中(如图中的10和15两个节点，也都会在叶子节点中出现)。另外B-Tree和B+Tree的卫星数据储存位置不同。

卫星数据：指索引元素所指向的数据记录。例如数据库中的某一行数据。

B-Tree中无论中间节点还是叶子节点都带有卫星数据。而B+Tree只有叶子节点带有卫星数据，中间节点只带有索引。如下图所示： B-Tree的结构和B+Tree结构类似，只是非叶子节点也会存储数据，而B+Tree只在叶子节点存储数据，虽然B-Tree可能在遍历到第二层时就可以得到数据返回，但是由于非叶子节点也会存储数据，导致每个数据页存储的索引更少，导致树的高度会很高，如果需要遍历的数据在叶子节点，则非常费时，所以查询性能不如B+Tree稳定。MySQL,InnoDB引擎一个数据页大小为16KB，所以从理论上讲，一个数据页存储的有用信息越多，树的高度就会越低，I/O次数越少，搜索效率越高。

InnoDB索引模型

CREATE TABLE `r` (
  `id` int NOT NULL primary key auto_increment,
  `k` int not null,
  `name` varchar(16),
   index(k)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在表中插入(1,10,"张三"),(2,20,"李四"),(3,30,"王五")。则索引如下图

主键索引的叶子节点存的是整行数据，非主键索引的叶子节点存的主键的值。
在InnoDB里，主键索引被称为聚簇索引或聚集索引（clustered index）,非主键索引被称为二级索引或辅助索引(secondary index)。 在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表成为索引组织表。每一个索引在InnoDB里对应一棵B+树，数据是有序排列的。 聚簇索引生成规则：

1. When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

定义主键用主键作为聚簇索引。

2. If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

没定义主键使用第一个唯一非空索引作为聚簇索引。

3. If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

没定义主键，也没定义唯一索引，生成一个隐藏的列作为聚簇索引。
更多详情

基于主键索引和普通索引查询有什么区别？

1. 如果sql是 select * from r where id = 1; 即通过主键方式查询，只需要搜索主键这棵B+树。
2. 如果sql是 select * from r where k = 10; 即通过普通索引查询，需要先搜索普通索引k这棵B+树，拿到主键id=1,在用id=1再去搜索主键索引的B+树。这个过程叫做回表。

在分析一个sql语句：select * from r where k between 8 and 22;

1. 在k索引树上找到k=10的记录，取得id=1;
2. 在id索引树上找到id=1的对应的行记录data(回表);
3. 在k索引树上找到k=20的记录，取得id=2;
4. 在id索引树上找到id=2的对应的行记录data(回表);
5. 在k索引树取下一个值k=30,不满足，循环结束。

这个例子由于要查询的结果只有主键索引上面才有，所以不得不回表。那么如何避免回表？

覆盖索引

如果sql语句是：select id from r where k between 8 and 22，由于这时只需要查询id值，而id值已经在k索引树上了，所以不需要回表查询，索引k已经覆盖了我们的查询需求，称之为覆盖索引。
由于覆盖索引可以减少数的搜索次数，显著提高查询性能，所以使用覆盖索引是一个常用的优化手段。
场景：假设有一个市民表：

CREATE TABLE `citizen` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

是否有必要创建身份证号和姓名的联合索引？
根据业务来看，如果有根据身份证号查询姓名的高频需求，可以考虑创建身份证号和姓名的联合索引，避免回表提高查询的效率。

最左前缀原则

select * from citizen where name = "张三" ;

这个肯定是可以用name索引的，如果要查询姓张的人，语句是

select * from citizen where name like '张%';

这时也可以用上name的索引，查找到第一个以张开头的人，向后遍历直到不满足条件为止。
而如果要检索姓张，年龄10岁的男孩。

select * from tuser where name like '张%' and age=10 and ismale=1;

这个在MySQL5.6以前是要根据查询到姓张的人开始一个一个回表去查询age是否满足10的，而5.6引入了索引下推优化(index condition pushdown)，可以在遍历中，对索引中包含的字段先判断，过滤掉不满足的记录，减少回表次数。 以下两句可以使用到索引。

1. SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
2. SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

第一句只有'Patrick' <= key_col < 'Patricl'的行才会被筛选出来，而第二句只有'Pat' <= key_col < 'Pau' 会被筛选出来。
以下两句不会使用索引：

3. SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
4. SELECT * FROM tbl_name WHERE key_col LIKE other_col;

第三句由于以通配符开始，不符合最左前缀原则，所以不能适用索引。第四句，由于LIKE的参数不是一个字符串常量，所以也不使用索引。
如果用 LIKE '%string%' 字符串长度超过3，会使用串匹配的BM算法提高查询效率。
另外，如果某一列有索引，如果值为空，使用where col_name IS NULL也是可以走索引的。

如果业务满足某字段唯一，是否可以考虑用该字段作为主键？

例如居民身份证号可以保证唯一，那么是否用身份证号当做主键建表？这里并太建议，根据上面介绍的聚簇索引和二级索引的结构之后，可以看出主键索引越长对于辅助索引建立需要更多的空间，另外对于聚簇索引，如果索引过长会导致主键索引树的高度变高，因为一个数据页默认是16k，主键索引越长则一个数据页能容纳的索引则越少。身份证号是18位，用字符串来存需要18个字节，而如果使用自增的long来做主键，则只有8个字节。另一个好处就是自增主键可以保证插入只需要插入到数据页的队尾，不需要插入中间，而身份证号按照顺序排序有可能会插入中间位置，这样会导致数据页存满，数据页分裂等消耗。

字符串应该如何创建索引？

场景一，根据邮箱登录是一个普遍场景，如果邮箱不加索引则需要全表扫描，而如果加入全量索引则需要占用很大的空间。由于字符串索引支持最左前缀原则，则我们可以这样创建索引：

alter table user add index index(email(5));

这里设置email的最左前5个字符作为索引可以缩小范围，但是如果前5个字符可能重复的数据很多，比如zhangsan@XX.com、zhangsi@XX.com、zhangwu@XX.com、zhangliu@XX.com、zhangqi@XX.com都会搜索出来在遍历，区别度太小，在某字段简历索引的一个原则就是这个字段的区别度，如此建立索引区别度太小。所以应该取得区别度可接受的最左前缀。

select count(distinct email) as L from user;（查询总数）

然后执行下列语句，来看每个前缀长度索引的区别度，找一个能够接受的长度，比如你的要求是区别度大于95%，那么可以算一下多长的前缀符合你的要求，区别度=L(n)/L。

select
count(distinct left(email,4) as L4,
count(distinct left(email,5) as L5,
count(distinct left(email,6) as L6,
count(distinct left(email,7) as L7,
from user;

• 场景二，还是身份证的场景，根据身份证进行等值查询，应该如何建立索引？ 提供两种方案：

1. 因为身份证前面都是省市生日等重复较多的信息，所以这里可以考虑倒序存储，并选择一个长度，比如倒数8位作为前缀索引。

select field_list from t where id_card = reverse('input_id_card_string');

2. 第二种是用hash，在创建一个身份证hash字段，用这个字段作为索引。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

查询时候用以下语句：

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string');

这样可以先快速缩小结果集的范围，在根据结果集遍历来查询精确的身份证号，提高效率。
缺点：以上几种方式都不支持范围查询，可以自己根据业务场景自己选择合适的方式。

隔离级别

未提交读(READ UNCOMMITTED)

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

提交读(READ COMMITTED)

读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

可重复读(REPEATABLE READ)

可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

串行化(SERIALIZABLE)

串行化，顾名思义是对于同一行记录，“写”会加“写锁”,"读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

1. 脏读

读取到其他事物未提交的结果。

2. 虚读

在事物中无读到其他事物提交的UPDATE更新结果。

3. 幻读（phantom read）

在事物中无读到其他事物提交的INSERT更新结果。

MySQL默认级别是可重复读，Oracel默认级别是提交读。

快照读和当前读

• 先看以下场景。在MySQL5.7版本下，默认隔离级别RR下，下面语句的执行结果是什么？

CREATE TABLE `r` (
  `id` int NOT NULL primary key auto_increment,
  `v` int not null
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  insert into r values (1,1);
  

1. 场景一：

事物A	事物B
begin;
1, select * from r;	begin;
	update r set v=v+1 where id =1;
	commit;
2, select * from r;
3, select * from r for update;
commit;
其中1,2,3句id等于3的列 v的值都等于多少？

2. 场景二：
   先恢复数值,

update r set v = 1 where id =1;

事物A	事物B
begin;
1, select * from r;	begin;
	update r set v=v+1 where id = 1;
	commit;
2, select * from r;
update r set v=v+1 where id = 1;
3, select * from r;
commit;

此时语句1、2、3查询结果中v的值是多少？

• 快照读

简单的select操作，属于快照读，不加锁。

select * from table where ?;

• 当前读

特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。
为什么并发读不需要加锁？
MVCC(参考高性能MySQL)
这里简单介绍一下MVCC(多版本并发控制)，MVCC的实现是通过保存数据在某个时间点的快照来实现的。不同存储引擎实现的方式也不同。这里简单介绍一下InnoDB简化版行为来说明MVCC是如何工作的。
InnoDB的MVCC是通过在每行记录后面保存的两个隐藏列来实现的。两个列一个保存了行的创建时间，另个一保存了行的过期时间。这里其实保存的并不是具体时间，而是系统版本号(system version number)。每新开启一个事物，系统版本号都会自动递增，事物开始时刻的系统版本号会作为事物的版本号，用来和查询到的每行记录版本作为比较。
下面看下InnoDB, REPEATABLE READ隔离级别下MVCC是如何操作的。

1. SELECT

a.查询版本号早于或等于当前事物版本的行数据。这样可以保证读取到的行，要么早于该事物已经存在，要么是本事物自己提交的。
b.行的删除版本要么未定义，要么大于当前事物的版本号。这样可以保证，当前事物读取到的行在事物开始之前是未被删除的。

2. INSERT

为新插入的每一行数据保存当前系统版本号作为行版本号。

3. DELETE

为删除的每一行保存当前系统版本号作为行删除标志。

4. UPDATE

a. 插入一行数据，保存当前系统版本号作为行版本号。 b. 同时添加当前的系统版本号作为原数据的删除标记。

InnoDB-锁

共享锁和排他锁

• 共享锁、读锁（Shared (S) Lock）

持有行记录读锁的事物允许读取该行记录。

• 排他锁、写锁（ Exclusive (X) Lock）

持有行记录写锁的事物允许更新活删除该行记录。

表锁

行锁（Record Locks）

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

行锁是作用在索引记录上的。例如， SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;会防止其他事物对于t.c1=10的增、删、改。

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

行锁总是锁索引记录的，尽管没有创建索引，对于没有创建索引的情况，InnoDB创建一个隐藏的聚簇索引并用该索引来实现行锁。

间隙锁（Gap Locks）

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

间隙锁是作用于索引记录之间或第一条索引记录之前或最后一条索引记录之后的锁。

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.

间隙锁不会作用于使用唯一索引去检索唯一行记录的情况。

为什么会有间隙锁？

防止幻读。（当前读）

• 下面这个语句加什么锁？已知条件MySQL5.7版本，InnoDB引擎，隔离级别RR。

delete from t where id = 10;

对id = 10的记录加写锁 (走主键索引)。这个答案对吗？
可能是对的也有可能是错的，因为已知条件不足。缺少下列条件：
1，id是否是主键？
2，id列如果不是主键，那么id上是否有索引？
3，id列上如果有二级索引，那么这个索引是否是唯一索引？
4，id=10的记录是否存在？
根据以下组合来判断加的锁：

1. id列是主键，只对id = 10 的记录加X锁。
2. id是唯一索引，并且id = 10 记录存在，对唯一索引id的B+Tree id = 10 的记录加行锁，同时对聚簇索引（主键索引）上对应id = 10 的主键 b 加X锁。
3. id是唯一索引，但是id = 10 的记录不存在，对唯一索引id的B+Tree id = 10 的空隙加就间隙锁，防止新的符合条件的记录插入，产生幻读，即锁住id索引树(8,b)至(15,c)之间的空间。

4. id非唯一索引，首先对id的索引树符合id = 10的记录加X锁，并且对符合id = 10对应的主键索引记录加X锁，还要对id索引树可能插入id = 10 的位置加入间隙锁，也就是(6,a)至(10,b), (10,b)至(10,c), (10,c)至(17,d)。

5. id无索引，这种是最恐怖的情况，对主键索引所有行记录加X锁，所有空隙加间隙锁，尽管MySQL对此有些优化，也不建议这样的方式，尤其是长事物，会使除了快照读的所有业务停摆。

看下面几个例子：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into t values(2,2),(6,6);

MySQL5.7版本，InnoDB引擎，隔离级别是RR情况下，创建表t，其中id是主键，k是普通索引。

问题1：其中语句 1、2、3可以插入吗？

|事物A | 事物B|事物C|事物D |--|--|--|--|--| begin;| select * from t where k=4 for update; |||| | | 1, insert into t values (3,3);|| | | |2, insert into t values (4,4);| | | | |3, insert into t values (5,5); |commit; |

是不能插入的，因为k是普通索引，所以会在索引 k 的B+树上 k=2 至 k=6 之间加上间隙锁，防止幻读。

问题2：其中语句 4、5、6、 7可以插入吗？

事物A	事物B	事物C	事物D	事物E
begin;
select * from t where k=4 for update;
	4, insert into t values (1,2);
		5, insert into t values (3,2);
			6, insert into t values (5,6);
				7, insert into t values (7,6);
commit;

其中语句 4, 7 是可以插入的，而语句 5, 6 是不能插入的。

根据前面的加锁机制分析一下原因：
首先，select * from t where k=4 for update，由于 k 是普通索引，所以在InnoDB引擎下，默认隔离级别RR情况下会加间隙锁防止新数据插入，防止幻读。加锁方式如下图：

如果4, 5, 6, 7语句可以插入的话应该插入在什么位置呢？
请看下图：

由于B+树索引有序排列，所以如果4、5、6、7均可以插入，位置应该插入普通索引k的B+Tree,如上图所示。从上图插入数据的位置就可以很明显的看出为什么语句5、6不能插入了。因为对于K索引树来说叶子节点存的是(k, id),除了要按k值排序，也要按照id排序，所以insert into t values (3,2)和insert into t values (5,6)插入的位置就落在了间隙锁的范围，所以是不能插入的，而insert into t values (1,2)和insert into t values (7,6)插入的位置落在间隙锁范围之外，所以可以插入。

• 间隙锁到底锁的是什么？ 通过上面这个例子可以看出，其实间隙锁并没有完全锁住k=2和k=6记录的插入，也就是锁住的不是具体的值，而是所有可能插入新值k=4的位置，在本例中也就是(2,2)至(6,6)之间的位置，因为索引是有序排列的，所以k=4只能插入(2,2)至(6,6)之间。

• 什么情况下产生间隙锁？

1. where条件后用非索引列约束的时候。
2. where后用非唯一索引列约束(等值搜索、字符串的最左前缀 LIKE '张%' )的时候。
3. where条件后用唯一索引、普通索引做范围查询时候( <、<=、>、>=、between and )。
4. 对于多列联合唯一索引，如果查询没有包含所有列的等值查询也会产生间隙锁。例如，联合唯一索引column_1、cloumn_2、column_3的联合唯一索引，如果当前读语句是 ··· where column_1 = value_1 and cloumn_2 = value_2，则会产生间隙锁，因为没有包含column_3。
5. 根据唯一索引delete、update 、select ··· lock in share mode 或 select ··· for update等值条件约束不存在的列的时候。

死锁问题