MySQL笔记

1.索引

1.1 存储引擎

索引常见的类型有哈希索引，有序数组索引，二叉树索引，跳表等等。MySQL 支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同。为了避免混乱，本文主要探讨 MySQL 的默认存储引擎 InnoDB 的索引结构。
innodb: mysql 5.7 中的默认存储引擎。innodb 是 mysql 的事务安全 (符合 acid) 存储引擎, 具有提交、回滚和崩溃恢复功能, 可保护用户数据。innodb 行级锁定 (不升级到更粗粒度锁) 和 oracle 类型一致使用非锁定读取，可提高多用户并发性和性能。innodb 将用户数据存储在聚集索引中, 以减少基于主键的常见查询的 I/O。为了保持数据完整性, innodb 还支持外键。

mysql> show engines;

1.2 InnoDB的索引结构

在InnoDB中是通过一种多路搜索树——B+树实现索引结构的。在B+树中是只有叶子结点会存储数据，而且所有叶子结点会形成一个链表。而在InnoDB中维护的是一个双向链表。

我们知道访问磁盘需要访问到指定块中，而访问指定块是需要盘片旋转和磁臂移动的，这是一个比较耗时的过程，如果增加树高那么就意味着你需要进行更多次的磁盘访问，所以会采用n叉树。n叉树的代表就有B-Tree和B+Tree。在明确为什么InnoDB使用B+Tree作为数据存储结构之前，我们必须了解B-Tree和B+Tree的区别。

首先，B-Tree读B树，而不读B减树。从上图可以看出B-Tree可能没有遍历到叶子节点就命中目标，而B+Tree每个父节点都会出现在子节点中(如图中的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会为每个索引建立一个 B+Tree进行存储索引。比如这个时候我们建立了一个简单的测试表：

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. 定义主键用主键作为聚簇索引。
2. 没定义主键使用第一个唯一非空索引作为聚簇索引。
3. 没定义主键，也没定义唯一索引，生成一个隐藏的列作为聚簇索引。（更多索引生成规则）

1.3 覆盖索引

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

1. 如果sql是 select * from r where id = 1; 即通过主键方式查询，只需要搜索主键这棵B+树。
2. 如果sql是 select * from r where k = 10; 即通过普通索引查询，需要先搜索普通索引k这棵B+树，拿到主键id=1,在用id=1再去搜索主键索引的B+树。这个过程叫做回表。
   总结 一下就是，MySQL在辅助索引上找到对应的主键值并通过主键值在聚簇索引上查找所要的数据就叫回表。再分析一个sql语句：select * from r where k between 8 and 22;
3. 在k索引树上找到k=10的记录，取得id=1;
4. 在id索引树上找到id=1的对应的行记录data(回表);
5. 在k索引树上找到k=20的记录，取得id=2;
6. 在id索引树上找到id=2的对应的行记录data(回表);
7. 在k索引树取下一个值k=30,不满足，循环结束。

这个例子由于要查询的结果只有主键索引上面才有，所以不得不回表。而有时候我们查辅助索引的时候就已经满足了我们需要查的数据，这个时候 InnoDB 就会进行一个叫覆盖索引的操作来提升效率，减少回表。
如果sql语句是：select id from r where k between 8 and 22，由于这时只需要查询id值，而id值已经在k索引树上了，所以不需要回表查询，索引k已经覆盖了我们的查询需求，称之为覆盖索引。由于覆盖索引可以减少数的搜索次数，显著提高查询性能，所以使用覆盖索引是一个常用的优化手段。
再举一个例子，新建一个学生表：

CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `class` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_name` (`class`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

我们使用 class(班级号) 和 name 做一个 联合索引，你可能会问这个联合索引有什么用呢？我们可以结合着上面的覆盖索引去理解，比如这个时候我们有一个需求，我们需要通过班级号去找对应的学生姓名 。

select name from stu where class = 102;

这个时候我们就可以直接在辅助索引上查找到学生姓名而不需要再次回表。总的来说，设计好索引，充分利用覆盖索引能很大提升检索速度。

1.4 最左前缀原则

最左前缀原则是以联合索引作为基础的，是一种联合索引的匹配规则。这个时候，我们将上面的需求稍微变动一下，这时我们有个学生迟到，但是他在门卫记录信息的时候只写了自己的名字张三而没有写班级，所以我们需要通过学生姓名去查找相应的班级号。

    select class from stu where name = '张三';

这个时候我们就不会走我们的联合索引了，而是进行了全表扫描。为什么？因为 最左匹配原则。我们可以画一张简单的图来理解一下。

我们可以看到整个索引设计就是这么设计的，所以我们需要查找的时候也需要遵循着这个规则，如果我们直接使用name，那么InnoDB是不知道我们需要干什么的。最左匹配原则有如下的一些规则：

1. 全值匹配的时候优化器会改变顺序，也就是说你全值匹配时的顺序和原先的联合索引顺序不一致没有关系，优化器会帮你调好。
2. 索引匹配从最左边的地方开始，如果没有则会进行全表扫描，比如你设计了一个(a,b,c)的联合索引，然后你可以使用(a),(a,b),(a,b,c) 而你使用 (b),(b,c),(c )就用不到索引了。
3. 遇到范围匹配会取消索引。
   比如这个时候你进行一个这样的 select 操作

select * from stu where class > 100 and name = '张三';

在MySQL5.6 版本以前是需要进行回表的，这个时候 InnoDB 就会放弃索引而进行全表扫描，因为这个时候 InnoDB 会不知道怎么进行遍历索引，所以进行全表扫描。

1.5 索引下推

刚刚的操作在 MySQL5.6 版本以前是需要进行回表的，但是5.6之后的版本做了一个叫索引下推的优化。

select * from stu where class > 100 and name = '张三';

如何优化的呢？因为刚刚的最左匹配原则我们放弃了索引，后面我们紧接着会通过回表进行判断 name，这个时候我们所要做的操作应该是这样的

但是有了索引下推之后就变成这样了，此时 “李四” 和 “小明” 这两个不会再进行回表。

因为这里匹配了后面的name = 张三，也就是说，如果最左匹配原则因为范围查询终止了，InnoDB还是会索引下推来优化性能。

1.6 一些索引创建的建议

哪些情况需要创建索引？

1. 频繁作为查询条件的字段应创建索引。
2. 多表关联查询的时候，关联字段应该创建索引。
3. 查询中的排序字段，应该创建索引。
4. 统计或者分组字段需要创建索引。

哪些情况不需要创建索引？

1. 表记录少。
2. 经常增删改查的表。
3. 频繁更新的字段。
4. where 条件使用不高的字段。
5. 字段很大的时候。

其他

1. 尽量选择区分度高的列作为索引。
2. 不要对索引进行一些函数操作，还应注意隐式的类型转换和字符编码转换。
3. 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
4. 多考虑覆盖索引，索引下推，最左匹配。

2.事务

2.1 事务的基本要素

1. 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

2.2 事务的并发问题

1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

注意：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

2.3 事务的隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交(READ UNCOMMITTED)	是	是	是
读已提交(READ COMMITTED)	否	是	是
可重复读(REPEATABLE READ)	否	否	是
串行化(SERIALIZABLE)	否	是	是

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

3.锁

3.1 全局锁

MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
一般会在进行 全库逻辑备份 的时候使用，这样就能确保 其他线程不能对该数据库做更新操作。

3.2 表锁

表锁即MDL(Meta Data Lock)元数据锁。MDL锁用来保证只有一个线程能对该表进行表结构更改。MDL分为 MDL写锁 和 MDL读锁，加锁规则是这样的

• 当线程对一个表进行 CRUD 操作的时候会加 MDL读锁
• 当线程对一个表进行 表结构更改 操作的时候会加 MDL写锁
• 写锁和读锁，写锁和写锁互斥，读锁之间不互斥

lock tables xxx read/write;

这是给一个表设置读锁和写锁的命令，如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。
这种表锁是一种处理并发的方式，但是在InnoDB中常用的是行锁。

3.3 行锁

我们知道在5.5版本以前 MySQL 的默认存储引擎是 MyISAM，而 MyISAM 和 InnoDB 最大的区别就是两个

• 事务
• 行锁
  其实行锁就是两个锁，可以理解为写锁(排他锁 X锁)和读锁(共享锁 S锁)
• 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
• 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

而行锁还会引起一个一个很头疼的问题，那就是死锁。如果事务A对行100加了写锁，事务B对行101加了写锁，此时事务A想要修改行101而事务B又想修改行100，这样占有且等待就导致了死锁问题，而面对死锁问题就只有检测和预防了。

3.4 next-key锁

MVCC 和行锁是无法解决 幻读 问题的，这个时候 InnoDB 使用了 一个叫 GAP锁(间隙锁) 的东西，它配合 行锁 形成了 next-key锁，解决了幻读的问题。
但是因为它的加锁规则，又导致了扩大了一些加锁范围从而减少数据库并发能力。具体的加锁规则如下：

1. 加锁的基本单位是next-key lock 就是行锁和GAP锁结合。
2. 查找过程中访问到的对象就会加锁。
3. 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。
   MVCC 解决幻读的思路比较复杂，这里就不做过多的验证，具体可以参考这篇文章