title

date

面经整理

MySQL 为什么采用 B+ 树而不用 B 树

分析一下几种树

二叉搜索树

只有二叉或者一侧（链表结构），高度太高，遍历查询次数过多

高度越高，查询时间越长，浪费时间浪费空间

自动递增，二叉搜索树都用不了
AVL平衡二叉树 O(log2↓N)

对二叉树的优化，限制了高度，左子树和右子树都是平衡二叉树，且高度差不能多于 1 左旋、右旋，以某一个子节点变成父节点，保持相对平衡

减少了高度——减少查询次数

所在层数决定查找次数 50w 500w？？？还是不行 IO 次数
```
IO 次数
寻址、吞吐……
与磁盘交互就是 IO
```

IO 次数过多
MySQL innodb -- page 16kb

类比搬家一辆大卡车一次只带走了一卷手纸浪费资源

每一次 IO 操作获取的目标数据过少，造成 IO 浪费，降低速度

B-Tree 多路搜索树多叉平衡查找树
1. 每个节点关键字由一个变为多个
节点内容：关键字，数据区，子节点引用（指针）
1. 查找路数增加，二叉树只有两路，B 树可多路
2. B 树是绝对平衡树，所有节点没有高度差
```
5，20
(-无穷, 5) 5 (5, 20) 20 (20, +无穷)
最多关键字个数 = 路数（degree） - 1

AVL 瘦高树 ———— BTree 矮胖树
```
B+Tree

改进点
1. 节点关键字 = 路数
2. 所有数据区全部只在叶子节点，关键字和指针，数据区连在一起（天然排序结果）
```
1, 28, 66
```
疑问
1. 为了取到数据区，层数少时 IO 有时候还更多了？
  
  答：
  
  ①1 次 1s ，2 次 2s ，3 次 3s
  
  ②如果 B+，每次都是 1.5s ——————所有架构设计和实现中都是，稳定大于一切
  
  并且BTree：16kb/关键字+数据区+指针——存放少
  
  B+Tree：16kb/关键字+指针——存放更多，减少 IO
优化
1. order by 天然排序，基于索引的排序
2. select * from xxx 遍历只需要返回叶子节点，基于索引的扫库、扫表更快
3. 范围查找更好

索引的数据结构

见下索引底层剖析

见下四、索引

什么是聚簇索引和二级索引，原理是什么

索引全量，增量实时构建流程，查询语法树的构建

MySQL 主键为什么要用自增id

1.MySQL为什么要用数字做自增主键？

首先为什么我们使用的是int类型，而不是varchar类型

int永远是固定的4个字节，而char类型是1~255字节之间

优点

占用空间小，节省CPU开销
在使用中，通常会在主键上建立索引，使用int可以将更多地索引载入内存，提高性能
使用int才可以使用AUTO_INCREAMENT

缺点

产生锁竞争的问题

在某个时刻，用户甲、用户乙、用户丙可能会同时或者先后(前面一个作业还没有完成)对数据表A进行查询或者更新的操作。当某个线程涉及到更新操作时，就需要获得独占的访问权。在更新的过程中，所有其它想要访问这个表的线程必须要等到其更新完成为止。此时就会导致锁竞争的问题。

2.为什么不能没有主键？

主键起到了一个唯一标识的作用，保证我们可以安全的更改或删除表中指定的某一行

成为主键需要具备的两个条件

任何两行都不具有相同的主键值
每个行都必须具有一个主键值（主键列不允许NULL值）

3.主键的选择顺序？

（只选择这一个）选择与业务内容无关的一个自增字段作为主键
- 使用自增主键，那么新来的数据就顺序插入到末尾，当一页写满，操作系统开辟另一页供写；如果使用的是非自增主键，那么主键的值就是随机的，那么插入的时候就需要，插入到现有索引页的某一个位置。这时目标页的数据就需要移动，再从磁盘读取，增加了很多开销，且容易产生内存碎片。

MySQL 一定要设置主键 id 么，不设置主键 id 时 MySQL 如何处理？

不手动建立主键索引

造成资源浪费

MySQL 默认创建隐藏主键 _rowid int 型 6byte

正常主键索引例如int型 4byte

行锁升级为表锁

select * from user where name = chenzihao for update 悲观锁，排它锁

如果没有手动建立主键索引，行锁会升级为表锁

2.1 事务隔离 isolation mvcc redo/undo/bin log

MySQL 内部主键的原理，自定义主键与自增主键的优缺点

主键是一种唯一索引，InnoDB 存储引擎是 B+Tree 优化后实现的，自增主键是为了维持 B+Tree 的特性且不用频繁的分裂调整。

在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

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

InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。所以不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

InnoDB 数据文件本身是一颗 B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效。

1、B-Tree

B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。

结构如下：

2、B+Tree

由B-Tree变种而来，区别：内节点不存储data，只存储key；叶子节点不存储指针。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

3、InnoDB 存储引擎，在经典 B+Tree 的基础上进行了优化，增加了顺序访问指针。在 B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree。这样就提高了区间访问性能。

IN 是否参与索引

MySQL 优化器会自动判断 in 是否走二级索引，or 查询和 in 一样，都是可能会用到索引。

复合索引的结构，最左查询

索引什么时候不生效

索引优化规则：

1）如果 MySQL 估计使用索引比全表扫描还慢，则不会使用索引。

返回数据的比例是重要的指标，比例越低越容易命中索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。

2）前导模糊查询不能命中索引。

name 列创建普通索引：

前导模糊查询不能命中索引：

EXPLAIN SELECT * FROM user WHERE name LIKE '%s%';

非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询：

EXPLAIN SELECT * FROM user WHERE name LIKE 's%';

3）数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。

EXPLAIN SELECT * FROM user WHERE name=1;

EXPLAIN SELECT * FROM user WHERE name='1';

4）复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。

name,age,status列创建复合索引：

ALTER TABLE user ADD INDEX index_name (name,age,status);

user表索引详情：

SHOW INDEX FROM user;

根据最左原则，可以命中复合索引index_name：

EXPLAIN SELECT * FROM user WHERE name='swj' AND status=1;

注意，最左原则并不是说是查询条件的顺序：

EXPLAIN SELECT * FROM user WHERE status=1 AND name='swj';

而是查询条件中是否包含索引最左列字段：

EXPLAIN SELECT * FROM user WHERE status=2 ;

5）union、in、or 都能够命中索引，建议使用 in。

union:

EXPLAIN SELECT*FROM user WHERE status=1

UNION ALL

SELECT*FROM user WHERE status = 2;

in:

EXPLAIN SELECT * FROM user WHERE status IN (1,2);

or:

EXPLAIN SELECT*FROM user WHERE status=1OR status=2;

查询的CPU消耗：or>in>union。

6）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;

因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。

7）负向条件查询不能使用索引，可以优化为in查询。

负向条件有：!=、<>、not in、not exists、not like等。

status列创建索引：

ALTER TABLE user ADD INDEX index_status (status);

user表索引详情：

SHOW INDEX FROM user;

负向条件不能命中缓存：

EXPLAIN SELECT * FROM user WHERE status !=1 AND status != 2;

可以优化为in查询，但是前提是区分度要高，返回数据的比例在30%以内：

EXPLAIN SELECT * FROM user WHERE status IN (0,3,4);

8）范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。

status,age列分别创建索引：

ALTER TABLE user ADD INDEX index_status (status);

ALTER TABLE user ADD INDEX index_age (age);

user表索引详情：

SHOW INDEX FROM user;

范围条件查询可以命中索引：

EXPLAIN SELECT * FROM user WHERE status>5;

范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引：

EXPLAIN SELECT * FROM user WHERE status>5 AND age<24;

如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引：

EXPLAIN SELECT * FROM user WHERE status>5 AND age=24;

8）数据库执行计算不会命中索引。

EXPLAIN SELECT * FROM user WHERE age>24;

EXPLAIN SELECT * FROM user WHERE age+1>24;

计算逻辑应该尽量放到业务层处理，节省数据库的CPU的同时最大限度的命中索引。

9）利用覆盖索引进行查询，避免回表。

被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

user表的索引详情：

因为status字段是索引列，所以直接从索引中就可以获取值，不必回表查询：

Using Index代表从索引中查询：

EXPLAIN SELECT status FROM user where status=1;

当查询其他列时，就需要回表查询，这也是为什么要避免SELECT*的原因之一：

EXPLAIN SELECT * FROM user where status=1;

10）建立索引的列，不允许为 null。

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用not null约束以及默认值。

remark列建立索引：

ALTER TABLE user ADD INDEX index_remark (remark);

IS NULL可以命中索引：

EXPLAIN SELECT * FROM user WHERE remark IS NULL;

IS NOT NULL不能命中索引：

EXPLAIN SELECT * FROM user WHERE remark IS NOT NULL;

虽然IS NULL可以命中索引，但是NULL本身就不是一种好的数据库设计，应该使用NOT NULL约束以及默认值。

a. 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。

b. 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。

c. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。

d. 多表关联时，要保证关联字段上一定有索引。

e. 创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。

MySQL的引擎,区别

见下索引如何与磁盘交互

见下二、存储引擎

explain的作用

查看sql的性能是否走索引

见下 MySQL 服务器优化

查询一条记录需要查几次磁盘

一. 遍历 B+ 树的次数

首先，既然问题是一次查询，那我们肯定是要知道MySQL使用的存储引擎是哪个，要根据存储引擎的不同判断索引的结构，然后通过索引的B+树来回答这些问题。

MySQL中MyISAM和InnoDB的索引方式以及区别与选择

1、MySQL的 innodb 引擎的聚集索引和非聚集索引

网上看到很多资料，有的叫innodb的索引为聚集索引，有的叫做聚簇索引，其实都是一样的，只是在翻译过来了时候命名产生了分歧，聚簇（集）索引的叶子节点就是数据节点，而非聚簇（集）索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。非聚簇（集）索引在innodb引擎中，又叫做二级索引，辅助索引等。

2、分别遍历了几次 B+树

主键索引从上至下遍历一次B+树，直到找到具体的主键，拿到叶子结点存储的数据。

二级索引需要遍历两次B+树，第一次遍历是找到对应的主键，第二次遍历是根据主键找到具体的数据。

比如查询二级索引的SQL，先通过遍历二级索引的B+树来找到对应的主键，然后回表即通过主键遍历聚集索引 B+ 树，拿到具体的数据。（PS：MySQL 里面每次新建索引都会生成新的 B+ 树，这也是索引文件会随着索引字段不断增加的原因）

这部分是要参照索引的图来的，如图：

（1）主键索引（聚集索引）

（2）辅助索引（二级索引）

3、回表的概念

回表就是通过辅助索引拿到主键id之后，要再去遍历聚集索引的B+树，这个过程就叫做回表。回表的操作更多的是随机io,随机io在性能上还是比较低下的，例如：

比如user表中有三个字段，a,b,c，给a和b建立联合索引idex_a_b（a,b）
			sql：select * from  user where a=1 and b=2;
			
（1）首先是用二级索引index_a_b来查询，速度会很快。（顺序IO）
（2）拿到主键id之后，这个主键id并不是顺序排列的，还要用主键去查询聚簇索引（随机io）
（3）当随机io很多，也就是拿到的主键id很多的时候，回表的代价是巨大的。

所以最好是选用覆盖索引或者让where 之后的条件筛选更多的数据

二. 聚集索引和非聚集索引执行一次 SQL 的 IO 次数

1、聚集索引

大致步骤如下：

（1） 数据量小的话，直接把索引放到内存中，内存的O(logn)消耗是远远低于磁盘io的,所以可以忽略不计

（2） 数据量大的话，采用索引结构，我们这部分先从二叉树说起，对于普通二叉树，第一个步骤是二分，每次判断都是一次半数的数量级检索。假如有100W的数据，大概的时间复杂度是：log2N=1000000即N=20的节点获取，也就是磁盘I/O复杂度最大为O(20)，二分的时间复杂度是O(log2N)。

（3） 但是对于数据库来说，存储场景会更加复杂，二叉树的性能虽然好，但我们还是想要树的高度更低一些，存储的数据更多一些。因此MySQL引入了B+树的概念。除了根节点之外，第二层级的数量得到了充分的扩展，相对于普通的二叉树，B+树的结构更加庞大又不失美感，假设非叶节点不同元素占用情况为：下一条记录指针占4Byte，id值8Byte，目标记录指针4Byte，那么一个4Kb的磁盘块将大致可以容纳250个下级指针，100万行目标记录(假设叶子节点也是只保存了id值，则一个非叶子节点下面也包括大概250个叶子节点)只需log250N=1000000即N=3的I/O次数，充分提升了每次节点I/O带来的检索效用，时间复杂度是O（lognN）,这里的n是非叶子结点的个数。（PS：实际上innodb的数据页大小是16kb，这个n会更大，那么对应的，io次数也会更少）

（4） 在实际的查询中，IO次数可能会更小，因为有可能会把部分用到的索引读取到内存中，相对于磁盘IO来说，内存的io消耗可以忽略不计。一般来说B+Tree的高度一般都在2-4层，MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作（根节点的那次不算磁盘I/O）。

关于二分，我们假设有50W数据，下面看一下效果

1	50W
2	25W
3	12.5W
4	6W
5	3W
6	1.5W
7	7000
8	3500
9	1800			   //第9次的时候，数据范围就已经很小了，当然，它的效率还不够高，但是比遍历所有数据要快多了

根据以上的解释，我们可以知道聚集索引的磁盘IO次数大概是1-3次，这一切都是因为高效的B+树。如果大家也碰到类似的问题，就照着上面一顿胡扯，绝对很稳了。

2、辅助索引

（1） 参考上面对于B+树的解释，辅助索引获取主键的时间复杂度是 lognN（假设第二层级是n个节点）

（2） 再通过lognN获取主键对应的数据列

参考：https://juejin.im/post/5b4f710be51d45195c073912 （io解释）

三. 引申问题

在进行相关测试的时候，可能会因为一部分索引放到了内存，从而造成一定的误差。因此咱们这边就来探讨探讨，这个放进内存的索引有多大。

1、多大的索引数据可以放到内存中？

（1） 要参照自己的MySQL设置，一般是innodb_buffer_pool_size的值，这个值默认是128M，具体的要根据机器的性能设置。

			关于Innodb_buffer_pool_size：《深入浅出MySQL》一文中这样描述Innodb_buffer_pool_size：
 				 	该参数定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同，
			MyISAM 的 key_buffer_size只缓存索引键， 而 innodb_buffer_pool_size 却是同时为数据块和索引块做缓存，
			这个特性和 Oracle 是一样的。这个值设得越高，访问 表中数据需要的磁盘 I/O 就越少。在一个专用的数据库
			服务器上，可以设置这个参数达机器 物理内存大小的 80%。尽管如此，还是建议用户不要把它设置得太大，
			因为对物理内存的竞 争可能在操作系统上导致内存调度。

（2） 内存缓冲区主要包含上面第一条提到的内存缓冲区主要包括：数据缓存（innodb的行数据），索引数据，缓冲数据（在内存中修改尚未刷新(写入)到磁盘的数据），内部结构（自适应哈希索引，行锁等。）

（3） 所以说，放到内存中的索引大小，和这些配置息息相关，当索引在内存中的时候，自然是用不到磁盘io的

具体参考：https://www.cnblogs.com/wanbin/p/9530833.html

2、MySQL一次普通查询经过的步骤

从查询过程上看，大致步骤是：

查看缓存中是否存在id，
如果有 则从内存中访问，否则要访问磁盘，
并将索引数据存入内存，利用索引来访问数据，
对于数据也会检查数据是否存在于内存，
如果没有则访问磁盘获取数据，读入内存。
返回结果给用户。

实际查询中，索引可能会有部分是在内存的，所以理论始终是理论，实际情况下还是很复杂的，要根据实际的情况分析。

怎么分析优化慢查询

见下慢查询分析

MySQL 有哪些优化方案

见下 SQL 优化

事务的基本特征，事务的隔离级别，默认隔离级别

事务的基本要素（ACID）

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

如果在任何时刻都只有一个事务，那么其天然是具有隔离性的，这时只要保证原子性就能具有一致性。

如果存在并发的情况下，就需要保证原子性和隔离性才能保证一致性。

MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

MySQL默认的事务隔离级别为repeatable-read

脏读和幻读出现在什么事务隔离级别，分别代表什么含义

事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

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

MySQL 如何避免幻读

MySQL InnoDB支持三种行锁定方式：

行锁（Record Lock）：锁直接加在索引记录上面，锁住的是key。
间隙锁（Gap Lock）：锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。

锁选择

如果更新条件没有走索引，例如执行”update from t1 set v2=0 where v2=5;” ，此时会进行全表扫描，扫表的时候，要阻止其他任何的更新操作，所以上升为表锁。
如果更新条件为索引字段，但是并非唯一索引（包括主键索引），例如执行“update from t1 set v2=0 where v1=9;” 那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因：

首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值；
还要保证锁定的区间不能插入新的数据。

如果更新条件为唯一索引，则使用Record Lock（记录锁）。
InnoDB根据唯一索引，找到相应记录，将主键索引值和唯一索引值加上记录锁。但不使用Gap Lock（间隙锁）

可重复读、幻读

Innodb在RR级别如何避免幻读

幻读定义

事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。

同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。

那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

MySQL如何实现避免幻读

1.在快照读读情况下，MySQL通过mvcc来避免幻读。

2.在当前读读情况下，MySQL通过next-key来避免幻读。

什么是MVCC

mvcc全称是multi version concurrent control（多版本并发控制）。MySQL把每个操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号

select：事务每次只能读到创建版本号小于等于此次系统版本号的记录，同时行的删除版本号不存在或者大于当前事务的版本号。 update：插入一条新记录，并把当前系统版本号作为行记录的版本号，同时保存当前系统版本号到原有的行作为删除版本号。 delete：把当前系统版本号作为行记录的删除版本号 insert：把当前系统版本号作为行记录的版本号

什么是next-key锁

行锁+GAP间隙锁

什么是快照读和当前读

快照读：简单的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 ?;

MVCC 出现在什么级别，原理是什么

MVCC 主要适用于MySQL的 RC（不可重复读），RR（可重复读）隔离级别

英文全称为Multi-Version Concurrency Control,翻译为中文即多版本并发控制。无非就是乐观锁的一种实现方式。在Java编程中，如果把乐观锁看成一个接口，MVCC便是这个接口的一个实现类而已。

特点

1.MVCC其实广泛应用于数据库技术，像Oracle,PostgreSQL等也引入了该技术，即适用范围广

2.MVCC并没有简单的使用数据库的行锁，而是使用了行级锁，row_level_lock,而非InnoDB中的innodb_row_lock.

基本原理

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

基本特征

每行数据都存在一个版本，每次数据更新时都更新该版本。
修改时Copy出当前版本随意修改，各个事务之间无干扰。
保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

MVCC下InnoDB的增删查改是怎么work的

1.插入数据（insert）:记录的版本号即当前事务的版本号

执行一条数据语句：insert into testmvcc values(1,"test");

假设事务id为1，那么插入后的数据行如下：

2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。

比如，针对上面那行记录，事务Id为2 要把name字段更新

update table set name= 'new_value' where id=1;

3、删除操作的时候，就把事务版本号作为删除版本号。比如

delete from table where id=1;

4、查询操作：

从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

删除版本号未指定或者大于当前事务版本号，即查询事务开启后确保读取的行未被删除。(即上述事务id为2的事务查询时，依然能读取到事务id为3所删除的数据行)
创建版本号小于或者等于当前事务版本号，就是说记录创建是在当前事务中（等于的情况）或者在当前事务启动之前的其他事物进行的insert。

（即事务id为2的事务只能读取到create version<=2的已提交的事务的数据集）

补充：

1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）.

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。

3.串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

4.通过以上总结，可知，MVCC主要作用于事务性的，有行锁控制的数据库模型。

关于MySQL中MVCC的总结

客观上，我们认为他就是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功。

但由于MySQL的写操作会加排他锁（前文有讲），如果锁定了还算不算是MVCC？

了解乐观锁的小伙伴们，都知道其主要依靠版本控制，即消除锁定，二者相互矛盾，so从某种意义上来说，MySQL的MVCC并非真正的MVCC，他只是借用MVCC的名号实现了读的非阻塞而已。

MySQL 中的乐观锁和悲观锁实现

见下七、MySQL锁机制

如果避免、减少锁等待、团队中如何监控MySQL的锁等待的情况

通过锁机制，可以实现多线程同时对某个表进行操作。

如下图所示，在某个时刻，用户甲、用户乙、用户丙可能会同时或者先后(前面一个作业还没有完成)对数据表A进行查询或者更新的操作。当某个线程涉及到更新操作时，就需要获得独占的访问权。在更新的过程中，所有其它想要访问这个表的线程必须要等到其更新完成为止。此时就会导致锁竞争的问题。从而导致用户等待时间的延长。在这篇文章中，笔者将跟大家讨论，采取哪些措施可以有效的避免锁竞争，减少MySQL用户的等待时间。

背景模拟：　　

为了更加清楚的说明这个问题，笔者先模拟一个日常的案例。通过案例大家来阅读下面的内容，可能条理会更加的清晰。现在MySQL数据库遇到如上图所示这种情况。　　

首先，用户甲对数据表A发出了一个查询请求。　　

然后，用户乙又对数据表A发出了一个更新请求。此时用户乙的请求只有在用户甲的作业完成之后才能够得到执行。　　

最后，用户丙又对数据表A发出了一个查询请求。在MySQL数据库中，更新语句的优先级要比查询语句的优先级高，为此用户丙的查询语句只有在用户乙的更新作业完成之后才能够执行。而用户乙的更新作业又必须在用户甲的查询语句完成之后才能够执行。此时就存在比较严重的锁竞争问题。　　

现在数据库工程师所要做的就是在数据库设计与优化过程中，采取哪些措施来降低这种锁竞争的不利情况? 　　

措施一：利用Lock Tables来提高更新速度

对于更新作业来说，在一个锁定中进行许多更新要比所有锁定的更新要来得快。为此如果一个表更新频率比较高，如超市的收银系统，那么可以通过使用Lock Tables选项来提高更新速度。更新的速度提高了，那么与Select查询作业的冲突就会明显减少，锁竞争的现象也能够得到明显的抑制。　　

措施二：将某个表分为几个表来降低锁竞争

如一个大型的购物超市，如沃尔玛，其销售纪录表每天的更新操作非常的多。此时如果用户在更新的同时，另外有用户需要对其进行查询，显然锁竞争的现象会比较严重。针对这种情况，其实可以人为的将某张表分为几个表。如可以为每一台收银机专门设置一张数据表。如此的话，各台收银机之间用户的操作都是在自己的表中完成，相互之间不会产生干扰。在数据统计分析时，可以通过视图将他们整合成一张表。　　

措施三：调整某个作业的优先级

默认情况下，在MySQL数据库中，更新操作比Select查询有更高的优先级。如上图所示，如果用户乙先发出了一个查询申请，然后用户丙再发出一个更新请求。当用户甲的查询作业完成之后，系统会先执行谁的请求呢?注意，默认情况下系统并不遵循先来后到的规则，即不会先执行用户乙的查询请求，而是执行用户丙的更新进程。这主要是因为，更新进程比查询进程具有更高的优先级。　　

但是在有些特定的情况下，可能这种优先级不符合企业的需求。此时数据库管理员需要根据实际情况来调整语句的优先级。如果确实需要的话，那么可以通过以下三种方式来实现。　　

一是通过LOW_PRIOITY属性。这个属性可以将某个特定的语句的优先级降低。如可以调低某个特定的更新语句或者插入语句的优先级。不过需要注意的是，这个属性只有对特定的语句有用。即其作用域只针对某个特定的语句，而不会对全局造成影响。　　

二是通过HIGH_PRIOITY属性。与通过LOW_PRIOITY属性对应，有一个HIGH_PRIOITY属性。顾名思义，这个属性可以用来提高某个特定的Select查询语句的优先级。如上面这个案例，在用户丙的查询语句中加入HIGH_PRIOITY属性的话，那么用户甲查询完毕之后，会立即执行用户丙的查询语句。等到用户丙执行完毕之后，才会执行用户乙的更新操作。可见，此时查询语句的优先级得到了提升。这里需要注意，跟上面这个属性一样，这个作用域也只限于特定的查询语句。而不会对没有加这个参数的其他查询语句产生影响。也就是说，其他查询语句如果没有加这个属性，那么其优先级别仍然低于更新进程。　　

三是通过Set LOW_PRIORIT_UPDATES=1选项。以上两个属性都是针对特定的语句，而不会造成全局的影响。如果现在数据库管理员需要对某个连接来调整优先级别，该如何实现呢?如上例，现在用户需要将用户丙连接的查询语句的优先级别提高，而不是每次查询时都需要使用上面的属性。此时就需要使用Set LOW_PRIORIT_UPDATES=1选项。通过这个选项可以制定具体连接中的所有更新进程都是用比较低的优先级。注意这个选项只针对特定的连接有用。对于其他的连接，就不适用。　　

四是采用Low_Priority_updates选项。上面谈到的属性，前面两个针对特定的语句，后面一个是针对特定的连接，都不会对整个数据库产生影响。如果现在需要在整个数据库范围之内，降低更新语句的优先级，是否可以实现?如上面这个案例，在不使用其他参数的情况下，就让用户丙的查询语句比用户乙的更新具有更先执行?如果用户有这种需求的话，可以使用Low_Priority_updates选项来启动数据库。采用这个选项启动数据库时，系统会给数据库中所有的更新语句比较低的优先级。此时用户丙的查询语句就会比用户用户乙的更新请求更早的执行。而对于查询作业来说，不存在锁定的情况。为此用户甲的查询请求与用户丙的查询请求可以同时进行。为此通过调整语句执行的优先级，可以有效的降低锁竞争的情况。　　

可见，可以利用属性或者选项来调整某条语句的优先级。如现在有一个应用，主要供用户来进行查询。更新的操作一般都是有管理员来完成，并且对于用户来说更新的数据并不敏感。此时基于用户优先的原则，可以考虑将查询的优先级别提高。如此的话，对于用户来说，其遇到锁竞争的情况就会比较少，从而可以缩短用户的等待时间。在调整用户优先级时，需要考虑其调整的范围。即只是调整特定的语句、还是调整特定的连接，又或者对整个数据库生效。　　

措施四：对于混合操作的情况，可以采用特定的选项

有时候会遇到混合操作的作业，如即有更新操作又有插入操作又有查询操作时，要根据特定的情况，采用特定的选项。如现在需要对数据表同时进行插入和删除的作业，此时如果能够使用Insert Delayed选项，将会给用户带来很大的帮助。再如对同一个数据表执行Select和Delete语句会有锁竞争的情况。此时数据库管理员也可以根据实际情况来选择使用Delete Limint选项来解决所遇到速度问题。　　

通常情况下，锁竞争与死锁不同，并不会对数据库的运行带来很大的影响。只是可能会延长用户的等待时间。如果用户并发访问的机率并不是很高，此时锁竞争的现象就会很少。那么采用上面的这些措施并不会带来多大的收益。相反，如果用户对某个表的并发访问比较多，特别是不同的用户会对表执行查询、更新、删除、插入等混合作业，那么采取上面这些措施可以在很大程度上降低锁冲突，减少用户的等待时间。

MySQL 的主从复制

见下十、主从复制

数据库视图

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。——百度百科

关系型数据库中的数据是由一张一张的二维关系表所组成，简单的单表查询只需要遍历一个表，而复杂的多表查询需要将多个表连接起来进行查询任务。对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图（view）功能。

0 视图相关的MySQL指令

操作指令	代码
创建视图	`CREATE VIEW 视图名(列1，列2...) AS SELECT (列1，列2...) FROM ...;`
使用视图	`当成表使用就好`
修改视图	`CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];`
查看数据库已有视图	`>SHOW TABLES [like...];`（可以使用模糊查找）
查看视图详情	`DESC 视图名`或者`SHOW FIELDS FROM 视图名`
视图条件限制	`[WITH CHECK OPTION]`

1 视图

百度百科定义了什么是视图，但是对缺乏相关知识的人可能还是难以理解或者只有一个比较抽象的概念，笔者举个例子来解释下什么是视图。

朕想要了解皇宫的国库的相关情况，想知道酒窖有什么酒，剩多少，窖藏多少年，于是派最信任的高公公去清点，高公公去国库清点后报给了朕；朕又想知道藏书情况，于是又派高公公去清点并回来报告给朕，又想知道金银珠宝如何，又派高公公清点。。。过一段时间又想知道藏书情况，高公公还得重新再去清点，皇上问一次，高公公就得跑一次路。

后来皇上觉得高公公不容易，就成立了国库管理部门，小邓子负责酒窖，小卓子负责藏书，而小六子负责金库的清点。。。后来皇上每次想了解国库就直接问话负责人，负责人就按照职责要求进行汇报。

安排专人管理后，每次皇上想要了解国库情况，就不必让高公公每次都跑一趟，而是指定的人员按照指定的任务完成指定的汇报工作就可以了。

和数据库相对应，每次进行查询工作，都需要编写查询代码进行查询；而视图的作用就是不必每次都重新编写查询的SQL代码，而是通过视图直接查询即可。因此：

视图是虚拟表，本身不存储数据，而是按照指定的方式进行查询。

比如，我们希望从前文提到的四张表，order_baisc,order_details，user和product中查找所有记录，需要写入代码指令：想再次查询这几个表中uid为u0001的用户的记录，有需要键入一次操作指令：也就是说，每次查询都得重新键入查询指令SQL代码，这种费时费力的体力活，对于时间就是生命的你我来说，是不划算的。所以借助视图，来执行相同或相似的查询。

2 创建视图

2.1 创建视图create view 创建视图的代码为：

>CREATE VIEW 视图名(列1，列2...)
 AS SELECT (列1，列2...)
 FROM ...;123

可以看到，创建视图和查询相比，增加了前面的CREATE VIEW 视图名 AS

2.2 视图运用

使用视图和使用表完全一样，只需要把视图当成一张表就OK了。视图是一张虚拟表。

eg：创建order_baisc,order_details，user和product的查询视图，并通过视图查找uid为u0001的记录：

2.3 修改视图CREATE OR REPLACE VIEW

修改和创建视图可以使用代码：

CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];1

eg:

2.4 查看视图 (1)查看数据库中有哪些视图 show tables 前面提到，视图就是虚拟的表，因此，查看视图的方法和查看表的方法是一样的：

>SHOW TABLES;1

通过show tables;反馈得到所有的表和视图。同样的，我们可以通过模糊检索的方式专门查看视图，这个时候，视图的命令统一采用的优势就体现出来了。 （2）查看视图详情 查看视图详情的方法有两种，一种是和查看表详情一样使用desc 视图名，另外一种方法是show fields from 视图名：

>DESC 视图名;
或者
>SHOW FIELDS FROM 视图名;123

两种方法得到的详情都是一毛一样的。

3 视图与数据变更

3.1 表格数据变更 将表product中的数据进行更新，在通过视图检索：

可以看到表格数据变化后，在通过视图检索，得到的结果也同步发生了变化，因此，在此证明了：

视图不是表，不保存数据，知识一张虚拟表；

3.2 通过视图变更数据

（1）插入数据

>INSERT INTO v_order(pid,pname,price) VALUES('p010','柴油','34');1

在此查询视图，发现插入了数据。

（2）跨表插入数据通过上图，我们可以看到，跨表插入数据系统反馈报错，提示不能修改超过一个表的数据。

因此，可以通过视图插入数据，但是只能基于一个基础表进行插入，不能跨表更新数据。

（3）WITH CHECK OPTION 如果在创建视图的时候制定了“WITH CHECK OPTION”，那么更新数据时不能插入或更新不符合视图限制条件的记录。

eg:对表product创建一个单价超过3000的视图，并加上“WITH CHECK OPTION”，之后插入一个价格为42的记录：

可以看到系统提示错误CHECK OPTION FAILED。因为视图限制了价格要高于3000. 后面再次尝试了不加“WITH CHECK OPTION”的视图，后者可以成功插入。

同样的，在不加“WITH CHECK OPTION”的情况下，通过视图修改记录，也可以成功执行：

通过视图修改，可能导致数据无故消失，因此：

没有特殊的理由，建议加上“WITH CHECK OPTION”命令。

注意点：

视图不是表，不直接存储数据，是一张虚拟的表；

一般情况下，在创建有条件限制的视图时，加上“WITH CHECK OPTION”命令。

存储过程

一、存储过程是什么？

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

二、优势

1、可以减少程序在调用DB时候的信息传输量（其实减少的只有Request的时候）

2、存储过程是预先优化和预编译的，节省每次运行编译的时间，所以一般情况下认为存储过程的性能是优于sql语句的。

3、对调用者可以隐藏数据库的复杂性，将数据组装的过程封装。

4、参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

5、如果业务开发中，数据人员和业务代码人员是分离的，业务人员可以不用关心数据，直接调用存储过程，更加面向分层开发设计理念。

三、存储过程的劣势

1、存储过程这种“一次优化，多次使用”的策略节省了每次执行时候编译的时间，但也是该策略导致了一个致命的缺点：可能会使用错误的执行计划。

2、存储过程难以调试，虽然有些DB提供了调试功能，但是一般的账号根本就没有那种权限，更何况线上的数据库不可能会给你调试权限的，再进一步就算能调试效果也比程序的调试效果要差很多。

3、可移植性差，当碰到切换数据种类的时候，存储过程基本就会歇菜。

4、如果业务数据模型有变动，存储过程必须跟着业务代码一起更改，如果是大型项目，这种改动是空前的，是要命的。

四、为什么不推荐存储过程

以上存储过程的优缺点，你随便一下网络就可能查到，表面看来存储过程的优势还是不少的，这也说明为什么老一辈程序员有很多喜欢写存储过程。但是随着软件行业业务日益复杂化，存储过程现在在复杂业务面前其实有点有心无力。

作者在业务中并不推荐使用存储过程。

1、采用存储过程操作数据在网络数据量传输上确实比直接使用sql语句要少很多，但这通常并不是操作数据系统性能的瓶颈，在一次操作数据的过程中，假设用时100毫秒，采用存储过程节省数据传输时间0.5毫秒（就算是5毫秒），我觉得这点时间基本可以忽略。

2、存储过程是只优化一次的，这有时候恰恰是个缺陷。有的时候随着数据量的增加或者数据结构的变化，原来存储过程选择的执行计划也许并不是最优的了，所以这个时候需要手动干预或者重新编译了，而什么时候执行计划不是最优的了这个平衡点，预先无法知晓，这就导致了有些应用突然会变慢，程序员处于懵逼的状态。

3、存储过程确实可以对调用方隐藏数据库的细节，但是这种业务代码人员和数据库设计人员是两个团队的情况又有多少呢，如果真是两个团队，那业务就需要两个团队来理解和沟通，我想沟通的成本也一定很高，而且分歧更容易产生。

作者认为数据库就应该做它最擅长的事情：存储相关。我不止一次的看过把业务写在存储过程的情况，程序代码层面真是薄薄的贫血层，就是一个数据的透传**。**我不赞同这种写法，因为我就接手过这样的程序，令我头疼的不是业务，而是看着好几千行的存储过程熟悉业务，关键还没有调试的权限（线上更不能调试）。

一个业务系统的设计往往需要你从数据库的层面抽离出来，把主要精力放在业务模型的设计上，在程序层面体现业务逻辑，而不是把业务逻辑都交给数据层面的管理者。

前几天我排查过一个“Bug”：存储过程是输入参数是一个主键id的列表字符串，长度居然是 nvarchar（max），主要功能是根据id列表查询数据。我想说的是就算你是max的长度，也有超长的可能性发生，因为业务方传输什么参数，参数什么长度是你DB无法控制的，所以这类的业务一定要放在程序中做处理，而不是怀着侥幸心里丢给DB。

如果是抱着存储过程性能高的心态的话，我到时觉你这是误入歧途，作者认为存储过程从来都不是提高性能的关键，反而系统的架构，缓存的设计，数据一致性更是系统关键问题。

存储过程通常是一种解决方案，但是通常情况下不是唯一的解决方案，在选择存储过程作为方案前，请确保他们是正确的选择。

五、其他看法

除了本文作者的观点之外，小编还给大家整理了一下其他关于是否要使用从存储过程的观点：

观点一：

观点二：

观点三：

观点四：

观点五：

观点六：

观点七：

观点八：

MySQL 中数据量太大，怎么对数据库做优化，比如一天50万数据的情况下怎么设计数据库？

方案概述

方案一：优化现有MySQL数据库。优点：不影响现有业务，源程序不需要修改代码，成本最低。缺点：有优化瓶颈，数据量过亿就玩完了。
方案二：升级数据库类型，换一种100%兼容MySQL的数据库。优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能，缺点：多花钱
方案三：一步到位，大数据解决方案，更换newsql/nosql数据库。优点：没有数据容量瓶颈，缺点：需要修改源程序代码，影响业务，总成本最高。

以上三种方案，按顺序使用即可，数据量在亿级别一下的没必要换nosql，开发成本太高。三种方案我都试了一遍，而且都形成了落地解决方案。该过程心中慰问跑路的那几个开发者一万遍 :)

方案一详细说明：优化现有MySQL数据库

跟阿里云数据库大佬电话沟通 and Google解决方案 and 问群里大佬，总结如下（都是精华）：

1.数据库设计和表创建时就要考虑性能
2.sql的编写需要注意优化
3.分区
4.分表
5.分库

1.数据库设计和表创建时就要考虑性能

MySQL数据库本身高度灵活，造成性能不足，严重依赖开发人员能力。也就是说开发人员能力高，则 MySQL 性能高。这也是很多关系型数据库的通病，所以公司的 DBA 通常工资巨高。

设计表时要注意：

表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
使用枚举或整数代替字符串类型
尽量使用TIMESTAMP而非DATETIME
单表不要有太多字段，建议在20以内
用整型来存IP

索引

索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
字符字段只建前缀索引
字符字段最好不要做主键
不用外键，由程序保证约束
尽量不用UNIQUE，由程序保证约束
使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

简言之就是使用合适的数据类型，选择合适的索引

1.选择合适的数据类型

（1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
（4）尽可能使用not null定义字段
（5）尽量少用text，非用不可最好分表

2.选择合适的索引列

（1）查询频繁的列，在where，group by，order by，on从句中出现的列
（2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
（3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
（4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

原开发人员已经跑路，该表早已建立，我无法修改，故：该措辞无法执行，放弃！

2.sql的编写需要注意优化

使用limit对查询结果的记录进行限定
避免select *，将需要查找的字段列出来
使用连接（join）来代替子查询
拆分大的delete或insert语句
可通过开启慢查询日志来找出较慢的SQL
不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
不用函数和触发器，在应用程序实现
避免%xxx式查询
少用JOIN
使用同类型进行比较，比如用'123'和'123'比，123和123比
尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

原开发人员已经跑路，程序已经完成上线，我无法修改sql，故：该措辞无法执行，放弃！

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎：

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
不支持事务
不支持外键
不支持崩溃后的安全恢复
在表有读取查询的同时，支持往表中插入新纪录
支持BLOB和TEXT的前500个字符索引，支持全文索引
支持延迟更新索引，极大提升写入性能
对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

InnoDB

InnoDB在MySQL 5.5后成为默认索引，它的特点是：

支持行锁，采用MVCC来支持高并发
支持事务
支持外键
支持崩溃后的安全恢复
不支持全文索引

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表

MyISAM速度可能超快，占用存储空间也小，但是程序要求事务支持，故InnoDB是必须的，故该方案无法执行，放弃！

3.分区

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的，无需修改代码。

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引。

用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，我测试，查询时不带分区条件的列，也会提高速度，故该措施值得一试。

分区的好处是：

可以让单表存储更多的数据
分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
部分查询能够从查询条件确定只落在少数分区上，速度会很快
分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备
可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
可以备份和恢复单个分区

分区的限制和缺点：

一个表最多只能有1024个分区
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
分区表无法使用外键约束
NULL值会使分区过滤无效
所有分区必须使用相同的存储引擎

分区的类型：

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

具体关于MySQL分区的概念请自行google或查询官方文档，我这里只是抛砖引玉了。

我首先根据月份把上网记录表RANGE分区了12份，查询效率提高6倍左右，效果不明显，故：换id为HASH分区，分了64个分区，查询速度提升显著。问题解决！结果如下：PARTITION BY HASH (id)PARTITIONS 64

select count(*) from readroom_website; --11901336行记录

/* 受影响行数: 0 已找到记录: 1 警告: 0 持续时间 1 查询: 5.734 sec. */

select * from readroom_website where month(accesstime) =11 limit 10;

/* 受影响行数: 0 已找到记录: 10 警告: 0 持续时间 1 查询: 0.719 sec. */

4.分表

分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户。

分表分为垂直拆分和水平拆分，通常以某个字段做拆分项。比如以id字段拆分为100张表：表名为 tableName_id%100

但：分表需要修改源程序代码，会给开发带来大量工作，极大的增加了开发成本，故：只适合在开发初期就考虑到了大量数据存在，做好了分表处理，不适合应用上线了再做修改，成本太高！！！而且选择这个方案，都不如选择我提供的第二第三个方案的成本低！故不建议采用。

5.分库

把一个数据库分成多个，建议做个读写分离就行了，真正的做分库也会带来大量的开发成本，得不偿失！不推荐使用。

方案二详细说明：升级数据库，换一个100%兼容MySQL的数据库

MySQL性能不行，那就换个。为保证源程序代码不修改，保证现有业务平稳迁移，故需要换一个100%兼容MySQL的数据库。

开源选择

tiDB pingcap/tidb
Cubrid Open Source Database With Enterprise Features

开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距，有很多坑要踩，如果你公司要求必须自建数据库，那么选择该类型产品。

云数据选择

阿里云POLARDB

云数据库POLARDB_高吞吐在线事务处理_关系型云数据库_价格_购买 - 阿里云

官方介绍语：POLARDB 是阿里云自研的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量最高可达 100T，性能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征，又具有开源数据库简单、可扩展、持续迭代的优势，而成本只需商用数据库的 1/10。

我开通测试了一下，支持免费MySQL的数据迁移，无操作成本，性能提升在10倍左右，价格跟rds相差不多，是个很好的备选解决方案！

阿里云OcenanBase

淘宝使用的，扛得住双十一，性能卓著，但是在公测中，我无法尝试，但值得期待

阿里云HybridDB for MySQL (原PetaData)

云数据库HybridDB for MySQL_产品详情_阿里云

官方介绍：云数据库HybridDB for MySQL （原名PetaData）是同时支持海量数据在线事务（OLTP）和在线分析（OLAP）的HTAP（Hybrid Transaction/Analytical Processing）关系型数据库。

我也测试了一下，是一个olap和oltp兼容的解决方案，但是价格太高，每小时高达10块钱，用来做存储太浪费了，适合存储和分析一起用的业务。

腾讯云DCDB

分布式数据库 - 腾讯云

官方介绍：DCDB又名TDSQL，一种兼容MySQL协议和语法，支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表，数据却均匀的拆分到多个分片中；每个分片默认采用主备架构，提供灾备、恢复、监控、不停机扩容等全套解决方案，适用于TB或PB级的海量数据场景。

腾讯的我不喜欢用，不多说。原因是出了问题找不到人，线上问题无法解决头疼！但是他价格便宜，适合超小公司，玩玩。

方案三详细说明：去掉MySQL，换大数据引擎处理数据

数据量过亿了，没得选了，只能上大数据了。

开源解决方案

hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本，一般公司是玩不起的，没十万投入是不会有很好的产出的！

云解决方案

这个就比较多了，也是一种未来趋势，大数据由专业的公司提供专业的服务，小公司或个人购买服务，大数据就像水/电等公共设施一样，存在于社会的方方面面。

国内做的最好的当属阿里云。

我选择了阿里云的MaxCompute配合DataWorks，使用超级舒服，按量付费，成本极低。

MaxCompute可以理解为开源的Hive，提供sql/mapreduce/ai算法/python脚本/shell脚本等方式操作数据，数据以表格的形式展现，以分布式方式存储，采用定时任务和批处理的方式处理数据。DataWorks提供了一种工作流的方式管理你的数据处理任务和调度监控。

当然你也可以选择阿里云hbase等其他产品，我这里主要是离线处理，故选择MaxCompute，基本都是图形界面操作，大概写了300行sql，费用不超过100块钱就解决了数据处理问题。

学生教师课程成绩表求平均成绩>=60分的学生的id, 姓名，平均成绩（是后面学生的平均成绩）

一个表user_id,order_date,要查用户订单,某一天订单,某个用户某天订单,如何建索引

查询所有成绩大于80的学生的平均成绩。利用子查询

-- 子查询
select name, avg(score) from student where name not in 
(select distinct name from student where score < 80)
group by name;
-- 不用子查询
select name,avg(score) from student group by name having min(score) > 80;

索引底层剖析

like 'xxx%' 一定会用到索引吗
为什么 MySQL 要默认使用 B+Tree，而不是 B-Tree、AVL？
为什么不建议写 select * from 进行查询
最左匹配原则怎样理解
为什么主键 ID 是递增的，和 B+Tree 有什么关联
如何理解三星索引

索引本质详剖

熟悉关系型数据库—— SQL 优化

SQL 执行的速度更快

索引

where，order by

无效索引

什么是索引

索引——目录无效目录纸张资源 空间换时间

索引是帮助 MySQL 高效获取数据的数据结构；在关系型数据库中，索引存储在磁盘中。

MySQL——InnoDB

hash 索引 BTree 索引

InnoDB 采取AHI： adaptive hash index 自适应哈希索引时常不可强行手动更改

< > between and

order by

20w 200w 内存爆掉

哈希索引

id	fname	Iname
1	Alen	Lee
2	Cerlina	Zhang
3	Bob	Chen

根据 firstname 建立索引

f('Alen') = 1232 指向第一行指针比较当前这一行的值是 Alen，是的话返回

f('Cerlina') = 4273 指向第三行指针

f('Bob') = 3765 指向第二行指针

id	fname	lname
4	David	Lee
5	Kaven	Wu

f('David') = 1232 指向第一、四、五行指针 hash 碰撞逐一比较查找、删除代价高

f('Kaven') = 1232

hash 索引是将记录放在内存中
select name, age from user where ... hash 索引只适合等值查询
where age > 18 order by age ... hash 索引对于范围查询和排序无效
哈希碰撞 hash 相同的值

MySQL 有没有哪种存储引擎可以手动使用 hash

memory-- 放在内存中，所以可以手动使用 hash，只用于中间表

ABCDE————————中间表————————F

hash很重要，HashMap，Hash 寻址 2 的 n 次方等等

B+Tree

分析一下几种树

二叉搜索树

只有二叉或者一侧（链表结构），高度太高，遍历查询次数过多

高度越高，查询时间越长，浪费时间浪费空间

自动递增，二叉搜索树都用不了
AVL平衡二叉树

对二叉树的优化，限制了高度，左子树和右子树都是平衡二叉树，且高度差不能多于 1 左旋、右旋，以某一个子节点变成父节点，保持相对平衡

减少了高度——减少查询次数

所在层数决定查找次数 50w 500w？？？还是不行 IO 次数
```
IO 次数
寻址、吞吐……
与磁盘交互就是 IO
```
1. IO 次数过多
2. MySQL innodb -- page 16kb
  
  类比搬家一辆大卡车一次只带走了一卷手纸浪费资源
  
  每一次 IO 操作获取的目标数据过少，造成 IO 浪费，降低速度
B-Tree 多路搜索树多叉平衡查找树
1. 每个节点关键字由一个变为多个
节点内容：关键字，数据区，子节点引用（指针）
1. 查找路数增加，二叉树只有两路，B 树可多路
2. B 树是绝对平衡树，所有节点没有高度差
```
5，20
(-无穷, 5) 5 (5, 20) 20 (20, +无穷)
最多关键字个数 = 路数（degree） - 1

AVL 瘦高树 ———— BTree 矮胖树
```
B+Tree

改进点
1. 节点关键字 = 路数
2. 所有数据区全部只在叶子节点，关键字和指针，数据区连在一起（天然排序结果）
```
1, 28, 66
```
疑问
1. 为了取到数据区，层数少时 IO 有时候还更多了？
  
  答：
  
  ①1 次 1s ，2 次 2s ，3 次 3s
  
  ②如果 B+，每次都是 1.5s ——————所有架构设计和实现中都是，稳定大于一切
  
  并且BTree：16kb/关键字+数据区+指针——存放少
  
  B+Tree：16kb/关键字+指针——存放更多，减少 IO
优化
1. order by 天然排序，基于索引的排序
2. select * from xxx 遍历只需要返回叶子节点，基于索引的扫库、扫表更快
3. 范围查找更好
红黑

索引如何与磁盘交互

两个引擎

Innodb引擎概述

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

MyISAM引擎概述

MyISAM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyISAM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyISAM也是很好的选择。

简单介绍区别

1、MyISAM是非事务安全的，而InnoDB是事务安全的

2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁

3、MyISAM支持全文类型索引，而InnoDB不支持全文索引

4、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM

5、MyISAM表保存成文件形式，跨平台使用更加方便

应用场景

1、MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyISAM

2、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

————————————————————————————————————

数据文件在哪里

.frm 结构化文件

.ibd 数据

.myi 磁盘地址指针

.myd 磁盘数据

Myisam 引擎 .myi .myd

主键索引 叶子节点 .myi ——.myd

非主键索引 叶子节点 .myi ——.myd

InnoDB 引擎 高并发、事务 .ibd开辟空间

主键索引 叶子节点 不存放指针值，而存放行记录，直接返回数据

非主键索引 叶子节点 不存放行记录 保存主键值

聚集索引（聚簇索引）

在 InnoDB 引擎中，只有主键是聚集索引（挂载行记录），其他索引都是非聚集索引（挂载索引列的值和主键值）。

扩展

不手动建立主键索引

造成资源浪费

MySQL 默认创建隐藏主键 _rowid int 型 6byte

正常主键索引例如int型 4byte

行锁升级为表锁

select * from user where name = chenzihao for update 悲观锁，排它锁

如果没有手动建立主键索引，行锁会升级为表锁

2.1 事务隔离 isolation mvcc redo/undo/bin log

如何分析优秀的索引

列的离散性

count distinct col : count col

性别 2：500w

年龄 100：500w

姓名 300w：500w 重复度最低，离散性最佳

<0.1 离散性就很好
单列索引，联合索引

联合索引 (a, b, c)

a

a,b

a,b,c

最左匹配原则

B+树按照从左到右的顺序建立搜索树，复合结构

name，mobile，age

name 确定下一个搜索方向

而如果没有 name 第一个比较因子

B+树不知道查找到哪一点

（name, mobile, age）

select * from user where name=‘chenzihao’ and mobile>15632326288 and age=18;

age 没有用到 mobile 结果太多，条条大路通罗马，就每条路都不走。

范围匹配后，离散性变差

select * from user where name=‘chenzihao’ and age=18

没有 mobile，所有 name 然后计算 age
Select * from user where mobile=15632326288 and name=‘chenzihao’

2 个索引

MySQL 优化器，选择性较好的列，放在 where 的最左边——优化器的优化策略

MySQL 有连接器、分析器、优化器、执行器
select * from user where name like ‘chenzihao%’ 会不会走索引

都对

①ASCII

a=97 b=98 c=99

abc = 979899

where name > abc

bbc 989899

②

chenzihao1 chenzihao2 chenzihao3

name like 'chenizhao&' 没有走

需要结合数据来分析

覆盖索引

通过索引项的信息可以直接返回所查询的列，则该索引被称为查询 SQL 的覆盖索引。

尽量使用覆盖索引，因为能加快查询速度。

非主键索引叶子节点拿到的

index —name name id

用什么，拿什么

select * from user where name = 'chenzihao' 通过非主键索引逐渐找到叶子节点，取到主键，(回表操作)然后去主键索引下的叶子节点处拿到行记录数据

select id, name from user where name = 'chenzihao' 通过非主键索引找到叶子节点，取到主键，既有主键id又有非主键索引name，(覆盖索引)直接返回

思考

表 user InnoDB 引擎

表结构

主键 id 联合索引 name，mobile 唯一索引 userNum

下面哪些用了覆盖索引？
- select userNum from user where userNum = xxx; 1
- select * from user where name = xxx; 0
- select id, userNum from user where userNum = xxx; 1
- select name,mobile from user where userNum = xxx; 0
- select mobile from user where name = xxx; 1

三星索引

where 后面匹配的索引关键字列越多越好，扫描的数据越精确，越少越好。——通过索引筛选出的数据越少越好——离散性
避免再次排序
尽可能应用覆盖索引，减少回表操作。

索引下推

MRR

Explain 性能分析

执行计划

分表分库

分表策略，一般就是简单哈希确定位置；或者id区间；时间；

临时表

union 和 unionall

unionall可以重复，union去重

LBCC MVCC

LBCC 是基于锁的一个版本控制，间隙锁，临界锁，行锁

MVCC 多版本并发控制

SYS 库

Innodb_buffer_pool

对性能的提升超大

SQL 优化

慢查询分析

Windows：my.ini

Linux：my.cnf

slow-query_log = 1
slow_query_log_file = slow.log
long_query_time = 5

生产环境几十上百万条记录，如何实现排序、top n、去重等操作呢？

MySQL 自带工具：MySQLdumpslow

MySQLdumpslow -s at -t 5 /usr/local/MySQL/data/slow2.log
# -s 排序 at 平均时间
# -t top

服务器硬件

比如，CPU 占用 80% 甚至 100%，怎么定位？

Linux 的 top 命令

select name, type, processlist_id, thread_os_id from threads where thread_os_id = xxxx;

show processlist; # 找到线程中执行的 SQL 语句

MySQL 服务器优化

explain SQL语句

# select_type : using filesort
# 独立表空间
# sort_buffer

set sort_buffer_size = 256 * 1024 * 1024

SQL 本身优化

子查询改成关联查询

反范式设计优化

为了性能和读取效率考虑而适当违反数据库设计范式

允许存在少量冗余，使用空间来换取时间

索引优化

是否用到索引 —— explain 看 key 字段

是否充分用到索引——explain 看 key_length 字段

key_length 算法

字符类型 varchar+2， char+0
字符集 utf8 占用 3 字节， utf8mb4 占用 4 字节
本身长度 50 数据库字段长度
是否为 null null + 1， not null + 0

组合索引算一些 key_length 看用了哪些

组合索引范围条件（大于小于什么的）放最后

MyBatis 缓存详解

　　缓存是一般的ORM 框架都会提供的功能，目的就是提升查询的效率和减少数据库的压力。跟Hibernate 一样，MyBatis 也有一级缓存和二级缓存，并且预留了集成第三方缓存的接口。

　　缓存体系结构：

　　MyBatis 跟缓存相关的类都在cache 包里面，其中有一个Cache 接口，只有一个默认的实现类 PerpetualCache，它是用HashMap 实现的。我们可以通过以下类找到这个缓存的庐山真面目

DefaultSqlSession

　　-> BaseExecutor

　　　　-> PerpetualCache localCache

　　　　　　->private Map<Object, Object> cache = new HashMap();

　　除此之外，还有很多的装饰器，通过这些装饰器可以额外实现很多的功能：回收策略、日志记录、定时刷新等等。但是无论怎么装饰，经过多少层装饰，最后使用的还是基本的实现类（默认PerpetualCache）。可以通过 CachingExecutor 类 Debug 去查看。

　　所有的缓存实现类总体上可分为三类：基本缓存、淘汰算法缓存、装饰器缓存。

一级缓存（本地缓存）：

　　一级缓存也叫本地缓存，MyBatis 的一级缓存是在会话（SqlSession）层面进行缓存的。MyBatis 的一级缓存是默认开启的，不需要任何的配置。首先我们必须去弄清楚一个问题，在MyBatis 执行的流程里面，涉及到这么多的对象，那么缓存PerpetualCache 应该放在哪个对象里面去维护？如果要在同一个会话里面共享一级缓存，这个对象肯定是在SqlSession 里面创建的，作为SqlSession 的一个属性。

　　DefaultSqlSession 里面只有两个属性，Configuration 是全局的，所以缓存只可能放在Executor 里面维护——SimpleExecutor/ReuseExecutor/BatchExecutor 的父类BaseExecutor 的构造函数中持有了PerpetualCache。在同一个会话里面，多次执行相同的SQL 语句，会直接从内存取到缓存的结果，不会再发送SQL 到数据库。但是不同的会话里面，即使执行的SQL 一模一样（通过一个Mapper 的同一个方法的相同参数调用），也不能使用到一级缓存。

　　每当我们使用MyBatis开启一次和数据库的会话，MyBatis会创建出一个SqlSession对象表示一次数据库会话。

　　在对数据库的一次会话中，我们有可能会反复地执行完全相同的查询语句，如果不采取一些措施的话，每一次查询都会查询一次数据库,而我们在极短的时间内做了完全相同的查询，那么它们的结果极有可能完全相同，由于查询一次数据库的代价很大，这有可能造成很大的资源浪费。

　　为了解决这一问题，减少资源的浪费，MyBatis会在表示会话的SqlSession对象中建立一个简单的缓存，将每次查询到的结果结果缓存起来，当下次查询的时候，如果判断先前有个完全一样的查询，会直接从缓存中直接将结果取出，返回给用户，不需要再进行一次数据库查询了。

　　如下图所示，MyBatis会在一次会话的表示----一个SqlSession对象中创建一个本地缓存(local cache)，对于每一次查询，都会尝试根据查询的条件去本地缓存中查找是否在缓存中，如果在缓存中，就直接从缓存中取出，然后返回给用户；否则，从数据库读取数据，将查询结果存入缓存并返回给用户。

一级缓存的生命周期有多长？

MyBatis在开启一个数据库会话时，会创建一个新的SqlSession对象，SqlSession对象中会有一个新的Executor对象，Executor对象中持有一个新的PerpetualCache对象；当会话结束时，SqlSession对象及其内部的Executor对象还有PerpetualCache对象也一并释放掉。
如果SqlSession调用了close()方法，会释放掉一级缓存PerpetualCache对象，一级缓存将不可用；
如果SqlSession调用了clearCache()，会清空PerpetualCache对象中的数据，但是该对象仍可使用；
SqlSession中执行了任何一个update操作(update()、delete()、insert()) ，都会清空PerpetualCache对象的数据，但是该对象可以继续使用；

SqlSession 一级缓存的工作流程：

对于某个查询，根据statementId,params,rowBounds来构建一个key值，根据这个key值去缓存Cache中取出对应的key值存储的缓存结果
判断从Cache中根据特定的key值取的数据数据是否为空，即是否命中；
如果命中，则直接将缓存结果返回；
如果没命中：
1. 去数据库中查询数据，得到查询结果；
2. 将key和查询到的结果分别作为key,value对存储到Cache中；
3. 将查询结果返回；

　　接下来我们来验证一下，MyBatis 的一级缓存到底是不是只能在一个会话里面共享，以及跨会话（不同session）操作相同的数据会产生什么问题。判断是否命中缓存：如果再次发送SQL 到数据库执行，说明没有命中缓存；如果直接打印对象，说明是从内存缓存中取到了结果。

1、在同一个session 中共享（不同session 不能共享）

//同Session
SqlSession session1 = sqlSessionFactory.openSession();
BlogMapper mapper1 = session1.getMapper(BlogMapper.class);
System.out.println(mapper1.selectBlogById(1002));
System.out.println(mapper1.selectBlogById(1002));

　　执行以上sql我们可以看到控制台打印如下信息（需配置mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl），会发现我们两次的查询就发送了一次查询数据库的操作，这说明了缓存在发生作用：

　　PS：一级缓存在BaseExecutor 的query()——queryFromDatabase()中存入。在queryFromDatabase()之前会get()。

public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey key, BoundSql boundSql) throws SQLException {
        ErrorContext.instance().resource(ms.getResource()).activity("executing a query").object(ms.getId());
　　　　。。。。。。try {
                ++this.queryStack;//从缓存中获取
                list = resultHandler == null ? (List)this.localCache.getObject(key) : null;
                if (list != null) {
                    this.handleLocallyCachedOutputParameters(ms, key, parameter, boundSql);
                } else {//缓存中获取不到，查询数据库
                    list = this.queryFromDatabase(ms, parameter, rowBounds, resultHandler, key, boundSql);
                }
　　　　。。。。。。
    }

2.同一个会话中，update（包括delete）会导致一级缓存被清空

//同Session
SqlSession session1 = sqlSessionFactory.openSession();
BlogMapper mapper1 = session1.getMapper(BlogMapper.class);
System.out.println(mapper1.selectBlogById(1002));
Blog blog3 = new Blog();
blog3.setBid(1002);
blog3.setName("mybatis缓存机制修改");
mapper1.updateBlog(blog3);
session1.commit();// 注意要提交事务，否则不会清除缓存
System.out.println(mapper1.selectBlogById(1002));

　　一级缓存是在BaseExecutor 中的update()方法中调用clearLocalCache()清空的（无条件），query 中会判断。

public int update(MappedStatement ms, Object parameter) throws SQLException {
        ErrorContext.instance().resource(ms.getResource()).activity("executing an update").object(ms.getId());
        if (this.closed) {
            throw new ExecutorException("Executor was closed.");
        } else {
　　　　　　  //清除本地缓存
            this.clearLocalCache();
            return this.doUpdate(ms, parameter);
        }
}

3.其他会话更新了数据，导致读取到脏数据（一级缓存不能跨会话共享）

SqlSession session1 = sqlSessionFactory.openSession();
BlogMapper mapper1 = session1.getMapper(BlogMapper.class);
SqlSession session2 = sqlSessionFactory.openSession();
BlogMapper mapper2 = session2.getMapper(BlogMapper.class);
System.out.println(mapper2.selectBlogById(1002));
Blog blog3 = new Blog();
blog3.setBid(1002);
blog3.setName("mybatis缓存机制1");
mapper1.updateBlog(blog3);
session1.commit();
System.out.println(mapper2.selectBlogById(1002));

一级缓存的不足：

　　使用一级缓存的时候，因为缓存不能跨会话共享，不同的会话之间对于相同的数据可能有不一样的缓存。在有多个会话或者分布式环境下，会存在脏数据的问题。如果要解决这个问题，就要用到二级缓存。MyBatis 一级缓存（MyBaits 称其为 Local Cache）无法关闭，但是有两种级别可选：

session 级别的缓存，在同一个 sqlSession 内，对同样的查询将不再查询数据库，直接从缓存中。
statement 级别的缓存，避坑：为了避免这个问题，可以将一级缓存的级别设为 statement 级别的，这样每次查询结束都会清掉一级缓存。

二级缓存：

　　二级缓存是用来解决一级缓存不能跨会话共享的问题的，范围是namespace 级别的，可以被多个SqlSession 共享（只要是同一个接口里面的相同方法，都可以共享），生命周期和应用同步。如果你的MyBatis使用了二级缓存，并且你的Mapper和select语句也配置使用了二级缓存，那么在执行select查询的时候，MyBatis会先从二级缓存中取输入，其次才是一级缓存，即MyBatis查询数据的顺序是：二级缓存 —> 一级缓存 —> 数据库。

　　作为一个作用范围更广的缓存，它肯定是在SqlSession 的外层，否则不可能被多个SqlSession 共享。而一级缓存是在SqlSession 内部的，所以第一个问题，肯定是工作在一级缓存之前，也就是只有取不到二级缓存的情况下才到一个会话中去取一级缓存。第二个问题，二级缓存放在哪个对象中维护呢？要跨会话共享的话，SqlSession 本身和它里面的BaseExecutor 已经满足不了需求了，那我们应该在BaseExecutor 之外创建一个对象。

　　实际上MyBatis 用了一个装饰器的类来维护，就是CachingExecutor。如果启用了二级缓存，MyBatis 在创建Executor 对象的时候会对Executor 进行装饰。CachingExecutor 对于查询请求，会判断二级缓存是否有缓存结果，如果有就直接返回，如果没有委派交给真正的查询器Executor 实现类，比如SimpleExecutor 来执行查询，再走到一级缓存的流程。最后会把结果缓存起来，并且返回给用户。

　　开启二级缓存的方法

第一步：配置 mybatis.configuration.cache-enabled=true，只要没有显式地设置cacheEnabled=false，都会用CachingExecutor 装饰基本的执行器。

第二步：在Mapper.xml 中配置标签：

<cache type="org.apache.ibatis.cache.impl.PerpetualCache"
    size="1024"
eviction="LRU"
flushInterval="120000"
readOnly="false"/>

基本上就是这样。这个简单语句的效果如下:

映射语句文件中的所有 select 语句的结果将会被缓存。
映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存。
缓存会使用最近最少使用算法（LRU, Least Recently Used）算法来清除不需要的缓存。
缓存不会定时进行刷新（也就是说，没有刷新间隔）。
缓存会保存列表或对象（无论查询方法返回哪种）的 1024 个引用。
缓存会被视为读/写缓存，这意味着获取到的对象并不是共享的，可以安全地被调用者修改，而不干扰其他调用者或线程所做的潜在修改。

这个更高级的配置创建了一个 FIFO 缓存，每隔 60 秒刷新，最多可以存储结果对象或列表的 512 个引用，而且返回的对象被认为是只读的，因此对它们进行修改可能会在不同线程中的调用者产生冲突。可用的清除策略有：

LRU – 最近最少使用：移除最长时间不被使用的对象。
FIFO – 先进先出：按对象进入缓存的顺序来移除它们。
SOFT – 软引用：基于垃圾回收器状态和软引用规则移除对象。
WEAK – 弱引用：更积极地基于垃圾收集器状态和弱引用规则移除对象。

默认的清除策略是 LRU。

flushInterval（刷新间隔）属性可以被设置为任意的正整数，设置的值应该是一个以毫秒为单位的合理时间量。默认情况是不设置，也就是没有刷新间隔，缓存仅仅会在调用语句时刷新。

size（引用数目）属性可以被设置为任意正整数，要注意欲缓存对象的大小和运行环境中可用的内存资源。默认值是 1024。

readOnly（只读）属性可以被设置为 true 或 false。只读的缓存会给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这就提供了可观的性能提升。而可读写的缓存会（通过序列化）返回缓存对象的拷贝。速度上会慢一些，但是更安全，因此默认值是 false。

　　注：二级缓存是事务性的。这意味着，当 SqlSession 完成并提交时，或是完成并回滚，但没有执行 flushCache=true 的 insert/delete/update 语句时，缓存会获得更新。

　　Mapper.xml 配置了之后，select()会被缓存。update()、delete()、insert()会刷新缓存。：如果cacheEnabled=true，Mapper.xml 没有配置标签，还有二级缓存吗？（没有）还会出现CachingExecutor 包装对象吗？（会）

　　只要cacheEnabled=true 基本执行器就会被装饰。有没有配置，决定了在启动的时候会不会创建这个mapper 的Cache 对象，只是最终会影响到CachingExecutorquery 方法里面的判断。如果某些查询方法对数据的实时性要求很高，不需要二级缓存，怎么办？我们可以在单个Statement ID 上显式关闭二级缓存（默认是true）：

<select id="selectBlog" resultMap="BaseResultMap" useCache="false">

　　二级缓存验证（验证二级缓存需要先开启二级缓存）

1、事务不提交，二级缓存不存在

System.out.println(mapper1.selectBlogById(1002));
// 事务不提交的情况下，二级缓存不会写入
// session1.commit();
System.out.println(mapper2.selectBlogById(1002));

　　为什么事务不提交，二级缓存不生效？因为二级缓存使用TransactionalCacheManager（TCM）来管理，最后又调用了TransactionalCache 的getObject()、putObject 和commit()方法，TransactionalCache里面又持有了真正的Cache 对象，比如是经过层层装饰的PerpetualCache。在putObject 的时候，只是添加到了entriesToAddOnCommit 里面，只有它的commit()方法被调用的时候才会调用flushPendingEntries()真正写入缓存。它就是在DefaultSqlSession 调用commit()的时候被调用的。

2、使用不同的session 和mapper，验证二级缓存可以跨session 存在取消以上commit()的注释

3、在其他的session 中执行增删改操作，验证缓存会被刷新

System.out.println(mapper1.selectBlogById(1002));
//主键自增返回测试
Blog blog3 = new Blog();
blog3.setBid(1002);
blog3.setName("mybatis缓存机制");
mapper1.updateBlog(blog3);
session1.commit();
System.out.println(mapper2.selectBlogById(1002));

　　为什么增删改操作会清空缓存？在CachingExecutor 的update()方法里面会调用flushCacheIfRequired(ms)，isFlushCacheRequired 就是从标签里面渠道的flushCache 的值。而增删改操作的flushCache 属性默认为true。

什么时候开启二级缓存？

一级缓存默认是打开的，二级缓存需要配置才可以开启。那么我们必须思考一个问题，在什么情况下才有必要去开启二级缓存？

因为所有的增删改都会刷新二级缓存，导致二级缓存失效，所以适合在查询为主的应用中使用，比如历史交易、历史订单的查询。否则缓存就失去了意义。
如果多个namespace 中有针对于同一个表的操作，比如Blog 表，如果在一个namespace 中刷新了缓存，另一个namespace 中没有刷新，就会出现读到脏数据的情况。所以，推荐在一个Mapper 里面只操作单表的情况使用。

　　如果要让多个namespace 共享一个二级缓存，应该怎么做？跨namespace 的缓存共享的问题，可以使用来解决：

<cache-ref namespace="com.wuzz.crud.dao.DepartmentMapper" />

　　cache-ref 代表引用别的命名空间的Cache 配置，两个命名空间的操作使用的是同一个Cache。在关联的表比较少，或者按照业务可以对表进行分组的时候可以使用。

　　注意：在这种情况下，多个Mapper 的操作都会引起缓存刷新，缓存的意义已经不大了.

第三方缓存做二级缓存

　　除了MyBatis 自带的二级缓存之外，我们也可以通过实现Cache 接口来自定义二级缓存。MyBatis 官方提供了一些第三方缓存集成方式，比如ehcache 和redis：https://github.com/mybatis/redis-cache ,这里就不过多介绍了。当然，我们也可以使用独立的缓存服务，不使用MyBatis 自带的二级缓存。

自定义缓存：

　　除了上述自定义缓存的方式，你也可以通过实现你自己的缓存，或为其他第三方缓存方案创建适配器，来完全覆盖缓存行为。

<cache type="com.domain.something.MyCustomCache"/>

　　这个示例展示了如何使用一个自定义的缓存实现。type 属性指定的类必须实现 org.mybatis.cache.Cache 接口，且提供一个接受 String 参数作为 id 的构造器。这个接口是 MyBatis 框架中许多复杂的接口之一，但是行为却非常简单。

public interface Cache {
  String getId();
  int getSize();
  void putObject(Object key, Object value);
  Object getObject(Object key);
  boolean hasKey(Object key);
  Object removeObject(Object key);
  void clear();
}

　　为了对你的缓存进行配置，只需要简单地在你的缓存实现中添加公有的 JavaBean 属性，然后通过 cache 元素传递属性值，例如，下面的例子将在你的缓存实现上调用一个名为 setCacheFile(String file) 的方法：

<cache type="com.domain.something.MyCustomCache">
  <property name="cacheFile" value="/tmp/my-custom-cache.tmp"/>
</cache>

　　你可以使用所有简单类型作为 JavaBean 属性的类型，MyBatis 会进行转换。你也可以使用占位符（如 ${cache.file}），以便替换成在配置文件属性中定义的值。从版本 3.4.2 开始，MyBatis 已经支持在所有属性设置完毕之后，调用一个初始化方法。如果想要使用这个特性，请在你的自定义缓存类里实现 org.apache.ibatis.builder.InitializingObject 接口。

public interface InitializingObject {
  void initialize() throws Exception;
}

　　请注意，缓存的配置和缓存实例会被绑定到 SQL 映射文件的命名空间中。因此，同一命名空间中的所有语句和缓存将通过命名空间绑定在一起。每条语句可以自定义与缓存交互的方式，或将它们完全排除于缓存之外，这可以通过在每条语句上使用两个简单属性来达成。默认情况下，语句会这样来配置：

<select ... flushCache="false" useCache="true"/>
<insert ... flushCache="true"/>
<update ... flushCache="true"/>
<delete ... flushCache="true"/>

　　鉴于这是默认行为，显然你永远不应该以这样的方式显式配置一条语句。但如果你想改变默认的行为，只需要设置 flushCache 和 useCache 属性。比如，某些情况下你可能希望特定 select 语句的结果排除于缓存之外，或希望一条 select 语句清空缓存。类似地，你可能希望某些 update 语句执行时不要刷新缓存。

精华总结 + 随便扯皮暴打面试官

一、MySQL架构

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层：第二层服务层，主要完成大部分的核心服务功能，包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等
引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

画出 MySQL 架构图，这种变态问题都能问的出来 MySQL 的查询流程具体是？or 一条SQL语句在MySQL中如何执行的？

客户端请求 ---> 连接器（验证用户身份，给予权限） ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作） ---> 优化器（主要对执行的sql优化选择最优的执行方案方法） ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

说说MySQL有哪些存储引擎？都有哪些区别？

二、存储引擎

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎。

查看存储引擎

-- 查看支持的存储引擎
SHOW ENGINES

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'

--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename

--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

设置存储引擎

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

默认情况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表

存储引擎对比

常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。

InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键

文件存储结构对比

在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，.frm 文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为数据表名.frm，如user.frm。

查看MySQL 数据保存在哪里：show variables like 'data%'

MyISAM 物理文件结构为：

.frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
.MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据
.MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

InnoDB 物理文件结构为：

.frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
.ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

ps：正经公司，这些都有专业运维去做，数据备份、恢复啥的，让我一个 Javaer 搞这的话，加钱不？

面试这么回答

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

对比项MyISAM和InnoDB

主外键不支持支持事务不支持支持行表锁表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作缓存只缓存索引，不缓存真实数据不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响表空间小大关注点性能事务默认安装是是

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把MySQL重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；

如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

哪个存储引擎执行 select count(*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

三、数据类型

主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

CHAR 和 VARCHAR 的区别？

char是固定长度，varchar长度可变：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间

相同点：

char(n)，varchar(n)中的n都代表字符的个数
超过char，varchar最大长度n的限制后，字符串会被截断。

不同点：

char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。
能存储的最大空间限制不一样：char的存储上限为255字节。
char在存储时会截断尾部的空格，而varchar不会。

char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。

列的字符串类型可以是什么？

字符串类型是：SET、BLOB、ENUM、CHAR、CHAR、TEXT、VARCHAR

BLOB和TEXT有什么区别？

BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB

TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

BLOB 保存二进制数据，TEXT 保存字符数据。

四、索引

说说你对 MySQL 索引的理解？数据库索引的原理，为什么要用 B+树，为什么不用二叉树？聚集索引与非聚集索引的区别？ InnoDB引擎中的索引策略，了解过吗？创建索引的方式有哪些？聚簇索引/非聚簇索引，MySQL索引底层实现，为什么不用B-tree，为什么不用hash，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构
索引的目的在于提高查询效率，可以类比字典、火车站的车次表、图书的目录等。
可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。

左边的数据表，一共有两列七条记录，最左边的是数据记录的物理地址为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。

索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上
平常说的索引，没有特别指明的话，就是B+树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。

基本语法：

创建：
- 创建索引：CREATE [UNIQUE] INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
- 修改表结构(添加索引)：ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)
删除：DROP INDEX [indexName] ON mytable;
查看：SHOW INDEX FROM table_name\G --可以通过添加 \G 来格式化输出信息。
使用ALERT命令
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引，索引值可出现多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)该语句指定了索引为 FULLTEXT ，用于全文索引。

优势

提高数据检索效率，降低数据库IO成本
降低数据排序的成本，降低CPU的消耗

劣势

索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

MySQL索引分类

数据结构角度

B+树索引
Hash索引
Full-Text全文索引
R-Tree索引

从物理存储角度

聚集索引（clustered index）
非聚集索引（non-clustered index），也叫辅助索引（secondary index）聚集索引和非聚集索引都是B+树结构

从逻辑角度

主键索引：主键索引是一种特殊的唯一索引，不允许有空值
普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引
多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
唯一索引或者非唯一索引
空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

为什么MySQL 索引中用B+tree，不用B-tree 或者其他树，为什么不用 Hash 索引聚簇索引/非聚簇索引，MySQL 索引底层实现，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？使用索引查询一定能提高查询的性能吗？为什么?

MySQL索引结构

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

B+Tree索引

MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

先了解下 B-Tree 和 B+Tree 的区别

B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的大小：show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

每个节点最多有m个孩子
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
若根节点不是叶子节点，则至少有2个孩子
所有叶子节点都在同一层，且不包含其它关键字信息
每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字，且关键字升序排序
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3 阶的 B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

B+Tree

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息；
所有叶子节点之间都有一个链指针；
数据记录都存放在叶子节点中

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3 ）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

B+Tree性质

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

在MyISAM中，索引（含叶子节点）存放在单独的.myi文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）。

主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。

主键索引：

我们知道InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的id、stu_id、name数据项。

在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。Innodb的数据文件可以按照表来切分（只需要开启innodb_file_per_table)，切分后存放在xxx.ibd中，默认不切分，存放在xxx.ibdata中。

辅助（非主键）索引：

这次我们以示例中学生表中的name列建立辅助索引，它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

这就意味着，对name列进行条件搜索，需要两个步骤：

① 在辅助索引上检索name，到达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

InnoDB 索引结构需要注意的点

数据文件本身就是索引文件
表数据文件本身就是按 B+Tree 组织的一个索引结构文件
聚集索引中叶节点包含了完整的数据记录
InnoDB 表必须要有主键，并且推荐使用整型自增主键

正如我们上面介绍 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引，如果没有符合的列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。

那为什么推荐使用整型自增主键而不是选择UUID？

UUID是字符串，比整型消耗更多的存储空间；
在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速；
自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。
在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

为什么非主键索引结构叶子节点存储的是主键值？

保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真实名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。

Hash索引

主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。目前使用Hash索引的数据库并不多，主要有Memory等。 MySQL目前有Memory引擎和NDB引擎支持Hash索引。

full-text全文索引

全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本提供对全文索引的支持。
它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

R-Tree空间索引

空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型

为什么MySQL索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。

面试官：为何不采用Hash方式？

因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，高并发下倾向创建组合索引
查询中排序的字段，排序字段通过索引访问大幅提高排序速度
查询中统计或分组字段

哪些情况不要创建索引

表记录太少
经常增删改的表
数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
频繁更新的字段不适合创建索引（会加重IO负担）
where条件里用不到的字段不创建索引

MySQL高效索引

覆盖索引（Covering Index）,或者叫索引覆盖，也就是平时所说的不需要回表操作

就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含（覆盖）满足查询结果的数据就叫做覆盖索引。
判断标准 使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

五、MySQL查询

count(*) 和 count(1)和count(列名)区别 ps：这道题说法有点多

执行效果上：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count(*) 最优。

MySQL中 in和 exists 的区别？

exists：exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false
in：in查询相当于多个or条件的叠加

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

UNION和UNION ALL的区别?

UNION和UNION ALL都是将两个结果集合并为一个，两个要联合的SQL语句字段个数必须一样，而且字段类型要“相容”（一致）；

UNION在进行表连接后会筛选掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录；
UNION会按照字段的顺序进行排序，而UNION ALL只是简单的将两个结果合并就返回；

SQL执行顺序

手写 SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number>
机读 FROM <left_table> ON <join_condition> <join_type> JOIN <right_table> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> SELECT DISTINCT <select_list> ORDER BY <order_by_condition> LIMIT <limit_number>
总结

MySQL 的内连接、左连接、右连接有什么区别？什么是内连接、外连接、交叉连接、笛卡尔积呢？

Join图

六、MySQL 事务

事务的隔离级别有哪些？MySQL的默认隔离级别是什么？什么是幻读，脏读，不可重复读呢？ MySQL事务的四大特性以及实现原理 MVCC熟悉吗，它的底层原理？

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

ACID — 事务基本要素

事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。

A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
I (Isolation)隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰
D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

并发事务处理带来的问题

更新丢失（Lost Update)：事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题
脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读（Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读（Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

幻读和不可重复读的区别：

不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）

并发事务处理带来的问题的解决办法：

“更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
“脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：
- 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
- 另一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

事务隔离级别

数据库事务的隔离级别有4种，由低到高分别为

READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

查看当前数据库的事务隔离级别：

show variables like 'tx_isolation'

下面通过事例一一阐述在事务的并发操作中可能会出现脏读，不可重复读，幻读和事务隔离级别的联系。

数据库的事务隔离越严格，并发副作用越小，但付出的代价就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

Read uncommitted

读未提交，就是一个事务可以读取另一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

那怎么解决可能的不可重复读问题？Repeatable read ！

Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。 MySQL的默认事务隔离级别

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

那怎么解决幻读问题？Serializable！

Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，Serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

比较

事务隔离级别读数据一致性脏读不可重复读幻读读未提交（read-uncommitted）最低级被，只能保证不读取物理上损坏的数据是是是读已提交（read-committed）语句级否是是可重复读（repeatable-read）事务级否否是串行化（serializable）最高级别，事务级否否否

需要说明的是，事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差。所以要根据具体的应用来确定合适的事务隔离级别，这个地方没有万能的原则。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 **REPEATABLE-READ（可重读）**事务隔离级别下使用的是Next-Key Lock 算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 **SERIALIZABLE(可串行化)**隔离级别，而且保留了比较好的并发性能。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读已提交):，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

MVCC 多版本并发控制

MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。

可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。

MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。

典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。下边通过 InnoDB的简化版行为来说明 MVCC 是如何工作的。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

REPEATABLE READ（可重读）隔离级别下MVCC如何工作：

SELECT InnoDB会根据以下两个条件检查每行记录：
- InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的
- 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除

只有符合上述两个条件的才会被查询出来

INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号
DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识
UPDATE：InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

保存这两个额外系统版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且也能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

事务日志

InnoDB 使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。

事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 IO。

InnoDB 假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。

InnoDB 用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。

InnoDB 使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。

事务日志可以帮助提高事务效率：

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。
如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据。

目前来说，大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

事务的实现

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。

事务的实现就是如何实现ACID特性。

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。

事务是如何通过日志来实现的，说得越深入越好。

事务日志包括：重做日志redo和回滚日志undo

redo log（重做日志）实现持久化和原子性在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录Redo Log，通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。
undo log（回滚日志） 实现一致性 undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。 Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。二者记录的内容也不同，redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

又引出个问题：你知道MySQL 有多少种日志吗？

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
二进制日志：记录对数据库执行更改的所有操作。
中继日志：中继日志也是二进制日志，用来给slave 库恢复
事务日志：重做日志redo和回滚日志undo

分布式事务相关问题，可能还会问到 2PC、3PC，，，

MySQL对分布式事务的支持

分布式事务的实现方式有很多，既可以采用 InnoDB 提供的原生的事务支持，也可以采用消息队列来实现分布式事务的最终一致性。这里我们主要聊一下 InnoDB 对分布式事务的支持。

MySQL 从 5.0.3 InnoDB 存储引擎开始支持XA协议的分布式事务。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

在MySQL中，使用分布式事务涉及一个或多个资源管理器和一个事务管理器。

如图，MySQL 的分布式事务模型。模型中分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）:

应用程序：定义了事务的边界，指定需要做哪些事务；
资源管理器：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
事务管理器：协调参与了全局事务中的各个事务。

分布式事务采用两段式提交（two-phase commit）的方式：

第一阶段所有的事务节点开始准备，告诉事务管理器ready。
第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

七、MySQL锁机制

数据库的乐观锁和悲观锁？ MySQL 中有哪几种锁，列举一下？ MySQL中InnoDB引擎的行锁是怎么实现的？ MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事物，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类

从对数据操作的类型分类：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分类：

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

行锁表锁页锁MyISAM√BDB√√InnoDB√√Memory√

MyISAM 表锁

MyISAM 的表锁有两种模式：

表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

InnoDB 行锁

InnoDB 实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。

加锁机制

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式

悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

锁模式(InnoDB有三种行锁的算法)

记录锁(Record Locks)：单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项； SELECT * FROM table WHERE id = 1 FOR UPDATE; 它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行在通过主键索引与唯一索引对数据行进行 UPDATE 操作时，也会对该行数据加记录锁： -- id 列为主键列或唯一索引列 UPDATE SET age = 50 WHERE id = 1;
间隙锁（Gap Locks）：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。 InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。 SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE; 即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。 GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
临键锁(Next-key Locks)： 临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。) Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

select for update有什么含义，会锁表还是锁行还是其他

for update 仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！假设有个表单 products ，里面有id跟name二个栏位，id是主键。

明确指定主键，并且有此笔资料，row lock

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

明确指定主键，若查无此笔资料，无lock

SELECT * FROM products WHERE id='-1' FOR UPDATE;

无主键，table lock

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

主键不明确，table lock

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

主键不明确，table lock

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

MySQL 遇到过死锁问题吗，你是如何解决的？

死锁

死锁产生：

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁：

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB避免死锁：

为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
改变事务隔离级别

如果出现死锁，可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

八、MySQL调优

日常工作中你是怎么优化SQL的？ SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？如何写sql能够有效的使用到复合索引？一条sql执行过长的时间，你如何优化，从哪些方面入手？什么是最左前缀原则？什么是最左匹配原则？

影响MySQL的性能因素

业务需求对MySQL的影响(合适合度)
存储定位对MySQL的影响
- 不适合放进MySQL的数据
- - 二进制多媒体数据
  - 流水队列数据
  - 超大文本数据
- 需要放进缓存的数据
- - 系统各种配置及规则数据
  - 活跃用户的基本信息数据
  - 活跃用户的个性化定制信息数据
  - 准实时的统计信息数据
  - 其他一些访问频繁但变更较少的数据
Schema设计对系统的性能影响
- 尽量减少对数据库访问的请求
- 尽量减少无用数据的查询请求
硬件环境对系统性能的影响

性能分析

MySQL Query Optimizer

MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最耗费时间）
当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimize r时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看系统的性能状态

性能下降SQL慢执行时间长等待时间长原因分析

查询语句写的烂
索引失效（单值、复合）
关联查询太多join（设计缺陷或不得已的需求）
服务器调优及各个参数设置（缓冲、线程数等）

MySQL常见性能分析手段

在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

性能瓶颈定位

我们可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：

MySQL> show status ——显示状态信息（扩展show status like ‘XXX’）

MySQL> show variables ——显示系统变量（扩展show variables like ‘XXX’）

MySQL> show innodb status ——显示InnoDB存储引擎的状态

MySQL> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

Shell> MySQLadmin variables -u username -p password——显示系统变量

Shell> MySQLadmin extended-status -u username -p password——显示状态信息

Explain(执行计划)

是什么：使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

能干吗：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

怎么玩：

Explain + SQL语句
执行计划包含的信息（如果有分区表的话还会有partitions）

各字段解释

id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）
- id相同，执行顺序从上往下
- id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行
select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）
- SIMPLE ：简单的select查询，查询中不包含子查询或UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
- SUBQUERY：在select或where列表中包含了子查询
- DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
- UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
- UNION RESULT：从UNION表获取结果的select
table（显示这一行的数据是关于哪张表的）
type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）
- system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
- const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
- index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
- ALL：Full Table Scan，将遍历全表找到匹配的行

tip: 一般来说，得保证查询至少达到range级别，最好到达ref

possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）
key
- 实际使用的索引，如果为NULL，则没有使用索引
- 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

key_len
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
- key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
ref （显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）
rows （根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）
Extra（包含不适合在其他列中显示但十分重要的额外信息）

using filesort: 说明MySQL会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
using where：使用了where过滤
using join buffer：使用了连接缓存
impossible where：where子句的值总是false，不能用来获取任何元祖
select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

case:

第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】
第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=''】
第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】
第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。

long_query_time 的默认值为10，意思是运行10秒以上的语句
默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数开启

查看开启状态

SHOW VARIABLES LIKE '%slow_query_log%'

开启慢查询日志

临时配置：

MySQL> set global slow_query_log='ON';
MySQL> set global slow_query_log_file='/var/lib/MySQL/hostname-slow.log';
MySQL> set global long_query_time=2;

也可set文件位置，系统会默认给一个缺省文件host_name-slow.log

使用set操作开启慢查询日志只对当前数据库生效，如果MySQL重启则会失效。

永久配置修改配置文件my.cnf或my.ini，在[MySQLd]一行下面加入两个配置参数

[MySQLd]
slow_query_log = ON
slow_query_log_file = /var/lib/MySQL/hostname-slow.log
long_query_time = 3

注：log-slow-queries 参数为慢查询日志存放的位置，一般这个目录要有 MySQL 的运行帐号的可写权限，一般都将这个目录设置为 MySQL 的数据存放目录；long_query_time=2 中的 2 表示查询超过两秒才记录；在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数，表示记录下没有使用索引的查询。

可以用 select sleep(4) 验证是否成功开启。

在生产环境中，如果手工分析日志，查找、分析SQL，还是比较费劲的，所以MySQL提供了日志分析工具MySQLdumpslow。

通过 MySQLdumpslow --help 查看操作帮助信息

得到返回记录集最多的10个SQL MySQLdumpslow -s r -t 10 /var/lib/MySQL/hostname-slow.log
得到访问次数最多的10个SQL MySQLdumpslow -s c -t 10 /var/lib/MySQL/hostname-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句 MySQLdumpslow -s t -t 10 -g "left join" /var/lib/MySQL/hostname-slow.log
也可以和管道配合使用 MySQLdumpslow -s r -t 10 /var/lib/MySQL/hostname-slow.log | more

也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志

Show Profile 分析查询

通过慢日志查询可以知道哪些 SQL 语句执行效率低下，通过 explain 我们可以得知 SQL 语句的具体执行情况，索引使用等，还可以结合Show Profile命令查看执行状态。

Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤

是否支持，看看当前的MySQL版本是否支持 MySQL>Show variables like 'profiling'; --默认是关闭，使用前需要开启
开启功能，默认是关闭，使用前需要开启 MySQL>set profiling=1;
运行SQL
查看结果

MySQL> show profiles; +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.00385450 | show variables like "profiling" | | 2 | 0.00170050 | show variables like "profiling" | | 3 | 0.00038025 | select * from t_base_user | +----------+------------+---------------------------------+

1. 诊断SQL，show profile cpu,block io for query id(上一步前面的问题SQL数字号码)
2. 日常开发需要注意的结论

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
create tmp table 创建临时表，这个要注意
Copying to tmp table on disk 把内存临时表复制到磁盘
locked

查询中哪些情况不会使用索引？

性能优化

索引优化

全值匹配我最爱
最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
is null ,is not null 也无法使用索引
like "xxxx%" 是可以用到索引的，like "%xxxx" 则不行(like "%xxx%" 同理)。like以通配符开头('%abc...')索引失效会变成全表扫描的操作，
字符串不加单引号索引失效
少用or，用它来连接时会索引失效
<，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
少用Hint强制索引

查询优化

永远小标驱动大表（小的数据集驱动大的数据集）

slect * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id

当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists

select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`

当 A 表的数据集小于B表的数据集时，用 exists优于用 in

注意：A表与B表的ID字段应建立索引。

order by关键字优化

order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序
MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；
ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列
尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀
如果不在索引列上，filesort 有两种算法，MySQL就要启动双路排序和单路排序
- 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据
- 单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序
优化策略
- 增大sort_buffer_size参数的设置
- 增大max_lencth_for_sort_data参数的设置

GROUP BY关键字优化

group by实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了

数据类型优化

MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。
尽量避免NULL：通常情况下最好指定列为NOT NULL

九、分区、分表、分库

MySQL分区

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。

当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率

能干嘛

逻辑数据分割
提高单一的写和读应用速度
提高分区范围读查询的速度
分割数据能够有多个不同的物理文件路径
高效的保存历史数据

怎么玩

首先查看当前数据库是否支持分区

MySQL5.6以及之前版本： SHOW VARIABLES LIKE '%partition%';
MySQL5.6： show plugins;

分区类型及操作

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。MySQL将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，比如交易表啊，销售表啊等，可以根据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。 range 来分，好处在于说，扩容的时候很简单。
LIST分区：类似于按RANGE分区，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。 hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定MySQL是怎么玩的，不太可控

随着业务的发展，业务越来越复杂，应用的模块越来越多，总的数据量很大，高并发读写操作均超过单个数据库服务器的处理能力怎么办？

这个时候就出现了数据分片，数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。

区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是MySQL内部实现。分库分表和分区并不冲突，可以结合使用。

说说分库与分表的设计

MySQL分表

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

垂直拆分 垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。
水平拆分(数据分片) 单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。水平分割的几种方法：
- 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
- 还可根据时间放入不同的表，比如：article_201601，article_201602。
- 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
- 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二个表user_0001中，随用户增加，直接添加用户表就行了。

MySQL分库

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

分库是什么？

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上，通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

优点：

减少增量数据写入时的锁对查询的影响
由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是它无法解决单表数据量太大的问题

分库分表后的难题

分布式事务的问题，数据的完整性和一致性问题。

数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。跨库联合查询的问题，可能需要两次查询跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并额外的数据管理负担，如：访问数据表的导航定位额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

配主从，正经公司的话，也不会让 Javaer 去搞的，但还是要知道

十、主从复制

复制的基本原理

slave 会从 master 读取 binlog 来进行数据同步
三个步骤

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;
slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

复制的基本原则

每个 slave只有一个 master
每个 salve只能有一个唯一的服务器 ID
每个master可以有多个salve

复制的最大问题

延时

十一、其他问题

说一说三个范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段 x → 非关键字段y

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

腾讯 MySQL 底层原理总结

MySQL 作为互联网中非常热门的数据库，其底层的存储引擎和数据检索引擎的设计非常重要，尤其是 MySQL 数据的存储形式以及索引的设计，决定了 MySQL 整体的数据检索性能。

我们知道，索引的作用是做数据的快速检索，而快速检索的实现的本质是数据结构。通过不同数据结构的选择，实现各种数据快速检索。在数据库中，高效的查找算法是非常重要的，因为数据库中存储了大量数据，一个高效的索引能节省巨大的时间。比如下面这个数据表，如果 MySQL 没有实现索引算法，那么查找 id=7 这个数据，那么只能采取暴力顺序遍历查找，找到 id=7 这个数据需要比较 7 次，如果这个表存储的是 1000W 个数据，查找 id=1000W 这个数据那就要比较 1000W 次，这种速度是不能接受的。

一、MySQL 索引底层数据结构选型

哈希表（Hash）

哈希表是做数据快速检索的有效利器。

哈希算法：也叫散列算法，就是把任意值(key)通过哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构。

考虑这个数据库表 user，表中一共有 7 个数据，我们需要检索 id=7 的数据，SQL 语法是：

select \* from user where id=7;

哈希算法首先计算存储 id=7 的数据的物理地址 addr=hash(7)=4231，而 4231 映射的物理地址是 0x77，0x77 就是 id=7 存储的额数据的物理地址，通过该独立地址可以找到对应 user_name='g'这个数据。这就是哈希算法快速检索数据的计算过程。

但是哈希算法有个数据碰撞的问题，也就是哈希函数可能对不同的 key 会计算出同一个结果，比如 hash(7)可能跟 hash(199)计算出来的结果一样，也就是不同的 key 映射到同一个结果了，这就是碰撞问题。解决碰撞问题的一个常见处理方式就是链地址法，即用链表把碰撞的数据接连起来。计算哈希值之后，还需要检查该哈希值是否存在碰撞数据链表，有则一直遍历到链表尾，直达找到真正的 key 对应的数据为止。

从算法时间复杂度分析来看，哈希算法时间复杂度为 O（1），检索速度非常快。比如查找 id=7 的数据，哈希索引只需要计算一次就可以获取到对应的数据，检索速度非常快。但是 MySQL 并没有采取哈希作为其底层算法，这是为什么呢？

因为考虑到数据检索有一个常用手段就是范围查找，比如以下这个 SQL 语句：

select \* from user where id \>3;

针对以上这个语句，我们希望做的是找出 id>3 的数据，这是很典型的范围查找。如果使用哈希算法实现的索引，范围查找怎么做呢？一个简单的思路就是一次把所有数据找出来加载到内存，然后再在内存里筛选筛选目标范围内的数据。但是这个范围查找的方法也太笨重了，没有一点效率而言。

所以，使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适合作为 MySQL 的底层索引的数据结构。

二叉查找树(BST)

二叉查找树是一种支持数据快速查找的数据结构，如图下所示:

二叉查找树的时间复杂度是 O(lgn)，比如针对上面这个二叉树结构，我们需要计算比较 3 次就可以检索到 id=7 的数据，相对于直接遍历查询省了一半的时间，从检索效率上看来是能做到高速检索的。此外二叉树的结构能不能解决哈希索引不能提供的范围查找功能呢？

答案是可以的。观察上面的图，二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也算是比较容易实现。

但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。比如以下这个情况，二叉树已经极度不平衡了，已经退化为链表了，检索速度大大降低。此时检索 id=7 的数据的所需要计算的次数已经变为 7 了。

在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此，简单的二叉查找树存在不平衡导致的检索性能降低的问题，是不能直接用于实现 MySQL 底层索引的。

AVL 树和红黑树

二叉查找树存在不平衡问题，因此学者提出通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

首先简单介绍红黑树，这是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O（logn）），也就保证了查找效率不会明显减低。比如从 1 到 7 升序插入数据节点，如果是普通的二叉查找树则会退化成链表，但是红黑树则会不断调整树的形态，使其保持基本平衡状态，如下图所示。下面这个红黑树下查找 id=7 的所要比较的节点数为 4，依然保持二叉树不错的查找效率。

红黑树拥有不错的平均查找效率，也不存在极端的 O(n)情况，那红黑树作为 MySQL 底层索引实现是否可以呢？其实红黑树也存在一些问题，观察下面这个例子。

红黑树顺序插入 1~7 个节点，查找 id=7 时需要计算的节点数为 4。

红黑树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 6 次。观察一下这个树的形态，是不是当数据是顺序插入时，树的形态一直处于“右倾”的趋势呢？从根本上上看，红黑树并没有完全解决二叉查找树虽然这个“右倾”趋势远没有二叉查找树退化为线性链表那么夸张，但是数据库中的基本主键自增操作，主键一般都是数百万数千万的，如果红黑树存在这种问题，对于查找性能而言也是巨大的消耗，我们数据库不可能忍受这种无意义的等待的。

现在考虑另一种更为严格的自平衡二叉树 AVL 树。因为 AVL 树是个绝对平衡的二叉树，因此他在调整二叉树的形态上消耗的性能会更多。

AVL 树顺序插入 1~7 个节点，查找 id=7 所要比较节点的次数为 3。

AVL 树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。从树的形态看来，AVL 树不存在红黑树的“右倾”问题。也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题。

总结一下 AVL 树的优点：

不错的查找性能（O（logn）），不存在极端的低效查找的情况。
可以实现范围查找、数据排序。

看起来 AVL 树作为数据查找的数据结构确实很不错，但是 AVL 树并不适合做 MySQL 数据库的索引数据结构，因为考虑一下这个问题：

数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 id=7 这个数据我们就要进行磁盘 IO 三次，这是多么消耗时间的。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，我们可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理了。

B 树

下面这个 B 树，每个节点限制最多存储两个 key，一个节点如果超过两个 key 就会自动分裂。比如下面这个存储了 7 个数据 B 树，只需要查询两个节点就可以知道 id=7 这数据的具体位置，也就是两次磁盘 IO 就可以查询到指定数据，优于 AVL 树。

下面是一个存储了 16 个数据的 B 树，同样每个节点最多存储 2 个 key，查询 id=16 这个数据需要查询比较 4 个节点，也就是经过 4 次磁盘 IO。看起来查询性能与 AVL 树一样。

但是考虑到磁盘 IO 读一个数据和读 100 个数据消耗的时间基本一致，那我们的优化思路就可以改为：尽可能在一次磁盘 IO 中多读一点数据到内存。这个直接反映到树的结构就是，每个节点能存储的 key 可以适当增加。

当我们把单个节点限制的 key 个数设置为 6 之后，一个存储了 7 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。

一个存储了 16 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。相对于 AVL 树而言磁盘 IO 次数降低为一半。

所以数据库索引数据结构的选型而言，B 树是一个很不错的选择。总结来说，B 树用作数据库索引有以下优点：

优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；
尽可能少的磁盘 IO，加快了检索速度；
可以支持范围查找。
B+树

B 树和 B+树有什么不同呢？

第一，B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。因此 MySQL 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

二、Innodb 引擎和 Myisam 引擎的实现

MySQL 底层数据引擎以插件形式设计，最常见的是 Innodb 引擎和 Myisam 引擎，用户可以根据个人需求选择不同的引擎作为 MySQL 数据表的底层引擎。我们刚分析了，B+树作为 MySQL 的索引的数据结构非常合适，但是数据和索引到底怎么组织起来也是需要一番设计，设计理念的不同也导致了 Innodb 和 Myisam 的出现，各自呈现独特的性能。

MyISAM 虽然数据查找性能极佳，但是不支持事务处理。Innodb 最大的特色就是支持了 ACID 兼容的事务功能，而且他支持行级锁。MySQL 建立表的时候就可以指定引擎，比如下面的例子，就是分别指定了 Myisam 和 Innodb 作为 user 表和 user2 表的数据引擎。

执行这两个指令后，系统出现了以下的文件，说明这两个引擎数据和索引的组织方式是不一样的。

Innodb 创建表后生成的文件有：

frm:创建表的语句
idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有

frm:创建表的语句
MYD:表里面的数据文件（myisam data）
MYI:表里面的索引文件（myisam index）

从生成的文件看来，这两个引擎底层数据和索引的组织方式并不一样，MyISAM 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式；Innodb 引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式。下面将从底层实现角度分析这两个引擎是怎么依靠 B+树这个数据结构来组织引擎实现的。

MyISAM 引擎的底层实现（非聚集索引方式）

MyISAM 用的是非聚集索引方式，即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

当我们为某个字段添加索引时，我们同样会生成对应字段的索引树，该字段的索引树的叶子节点同样是记录了对应数据的物理地址，然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

Innodb 引擎的底层实现（聚集索引方式）

InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name='Bob'。

这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 MySQL 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY！拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

问题来了，为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢?

其实很简单，因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

在进行 InnoDB 和 MyISAM 特点对比时谈到，MyISAM 查询性能更好，从上面索引文件数据文件的设计来看也可以看出原因：MyISAM 直接找到物理地址后就可以直接定位到数据记录，但是 InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，那当然 MyISAM 查询性能更高。

本文首先探讨了哪种数据结构更适合作为 MySQL 底层索引的实现，然后再介绍了 MySQL 两种经典数据引擎 MyISAM 和 InnoDB 的底层实现。最后再总结一下什么时候需要给你的表里的字段加索引吧：

较频繁的作为查询条件的字段应该创建索引；
唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；
更新非常频繁的字段不适合创建索引。

FilesExpand file tree

MySQL.md

Latest commit

History

MySQL.md

File metadata and controls

面经整理

MySQL 为什么采用 B+ 树而不用 B 树

索引的数据结构

什么是聚簇索引和二级索引，原理是什么

索引全量，增量实时构建流程，查询语法树的构建

MySQL 主键为什么要用自增id

1.MySQL为什么要用数字做自增主键？

首先为什么我们使用的是int类型，而不是varchar类型

优点

缺点

2.为什么不能没有主键？

成为主键需要具备的两个条件

3.主键的选择顺序？

MySQL 一定要设置主键 id 么，不设置主键 id 时 MySQL 如何处理？

MySQL 内部主键的原理，自定义主键与自增主键的优缺点

IN 是否参与索引

复合索引的结构，最左查询

索引什么时候不生效

MySQL的引擎,区别

explain的作用

查询一条记录需要查几次磁盘

一. 遍历 B+ 树的次数

二. 聚集索引和非聚集索引执行一次 SQL 的 IO 次数

三. 引申问题

怎么分析优化慢查询

MySQL 有哪些优化方案

事务的基本特征，事务的隔离级别，默认隔离级别

脏读和幻读出现在什么事务隔离级别，分别代表什么含义

MySQL 如何避免幻读

锁选择

可重复读、幻读

Innodb在RR级别如何避免幻读

幻读定义

MySQL如何实现避免幻读

什么是MVCC

什么是next-key锁

什么是快照读和当前读

MVCC 出现在什么级别， 原理是什么

特点

基本原理

基本特征

InnoDB存储引擎MVCC的实现策略

MVCC下InnoDB的增删查改是怎么work的

关于MySQL中MVCC的总结

MySQL 中的乐观锁和悲观锁实现

如果避免、减少锁等待、团队中如何监控MySQL的锁等待的情况

措施一：利用Lock Tables来提高更新速度

措施二：将某个表分为几个表来降低锁竞争

措施三：调整某个作业的优先级

措施四：对于混合操作的情况，可以采用特定的选项

MySQL 的主从复制

数据库视图

0 视图相关的MySQL指令

1 视图

2 创建视图

3 视图与数据变更

存储过程

MySQL 中数据量太大，怎么对数据库做优化，比如一天50万数据的情况下怎么设计数据库？

方案概述

方案一详细说明：优化现有MySQL数据库

1.数据库设计和表创建时就要考虑性能

设计表时要注意：

索引

简言之就是使用合适的数据类型，选择合适的索引

2.sql的编写需要注意优化

引擎

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表

3.分区

分区的好处是：

分区的限制和缺点：

分区的类型：

4.分表

5.分库

方案二详细说明：升级数据库，换一个100%兼容MySQL的数据库

MVCC 出现在什么级别，原理是什么

学生教师课程成绩表求平均成绩>=60分的学生的id, 姓名，平均成绩（是后面学生的平均成绩）