基础篇
1.基础架构：一条SQL查询语句是如何执行的？
Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数字和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
连接器
连接器负责跟客户端建立链接、获取权限、维持和管理链接。
如果长时间没有动静，连接器默认8小时会自动断开，再次发送请求需要进行重连。
数据库中长连接指的是连接成功后，如果客户端持续有请求，则一直使用同一个连接，短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
全部使用长连接，会非常占用内存，导致内存占用太大，被系统强行杀掉（OOM），导致MySQL异常重启。
解决方案：
1.定期断开连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2.MySQL5.7及以上版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接回复到刚刚创建完的状态。
查询缓存
不建议使用查询缓存：
因为查询缓存往往弊大于利。
查询缓存的失效非常频繁，只要对一个表的更新，这个表上所有的查询花奴才能都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低，除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
MySQL8.0已经移除该功能。
分析器
分析器会先做”词法分析”。你输入的是由多个字符喜欢和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。
然后是“语法分析”，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的额这个NySQL语句是否满足MySQL语法。
优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
执行器
开始执行的时候，会先判断你对这个表T哟没有执行查询的权限，如果没有，就会返回没有权限的错误（在工程上实现，如果命中查询缓存，就会在查询缓存返回结果的时候，做权限验证，查询也会在优化器之前调用precheck权限验证）。
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
2.日志系统：一条SQL更新语句是如何执行的？
MySQL的逻辑架构图和查询一直，执行语句前要先连接数据库，这是连接器的工作。
一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表T上所有缓存结果都清空，这也就是我们一般不建议使用查询缓存的原因。
接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用ID这个索引，然后，执行器负责具体执行，找到这一行，然后更新。
与查询流程不一样的是，更新流程还涉及两个重要的日志模块:redo log(重做日志)和bin log(归档日志)。
redo log(重做日志)
在MySQL有一个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高，为了解决这个问题，MySQL的设计者采用WAL技术，全称 Write-Ahead Logging。它的关键点就是先写日志，再写磁盘。
具体俩说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。
于此类似，InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以自己录4GB操作。从头开始写，写到末尾就又回到开头循环写，如图所示。
write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos和checkpoint之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示“粉板”满了，这时候不能再执行新的操作，得停下来先擦掉一些记录，把checkpoint推进一下。
有了redo log，InnoDB就可以保存即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
binlog(归档日志)
MySQL整体来看，分为两块：一块是Server层，它主要做的是MySQL功能层面的事情；还有一块就是引擎层，负责存储相关的具体事宜。redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog(归档日志)。
为什么会有两个日志
因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档，而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统-也就是redo log来实现crash-safe能力。
两种日志的区别
1.redo log是InnoDB引擎特有的；binlog是MySQL的server层实现的，所有引擎都可以使用。
2.redo log是物理日志，记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2 这一行的c字段加 1”。
3.redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
执行器和InnoDB引擎在执行这个简单Update语句时的内部流程
1.执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在是的数据项本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2.执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3.引擎将这行新数据更新到内存中，同时将这个更新数据操作记录到redo log里面，此时redo log处于prepare状态，然后告知执行器执行完成了，随时可以提交事务。
4.执行器生成这个操作的binlog，并把binlog写入磁盘。
5.执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。
两阶段提交
为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。要说明这个问题，我们得从文章开头的那个问题说起：
怎样让数据库恢复到半个月内任意一秒的状态？
前面我们说过了，binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。
当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：
这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。
好了，说完了数据恢复过程，我们回来说说，为什么日志需要“两阶段提交”。这里不妨用反证法来进行解释。
由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。
仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？
先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。 然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
你可能会说，这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？
其实不是的，不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。
简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
3.事务隔离：为什么你改了我还看不见？
简单来说，事务就是保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。你现在知道，MySQL是一个支持多引擎的系统，但并不是所有的引擎都支持事务。MyISAM就不支持事务，InnoDB支持。
隔离性与隔离级别
提到事务，你肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性），今天我们就来说说其中 I，也就是“隔离性”。
当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。
在谈隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：读未提交（readuncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。下面我逐一为你解释：
1.读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
2.读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
3.可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
4.串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。
配置的方式是，将启动参数tranaction-isolation的值设置成READ-COMMITTED。你可以用show variables来查看当前的值。
总结来说，哪个隔离级别都有它自己的使用场景，要根据业务情况来定。
事务隔离的实现
在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作，记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
你一定会问，回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。
什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的read-view的时候。
基于上面的说明，建议不要使用长事务。
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
在MySQL5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里，即使长事务最终提交，回滚段被清理，文件也不会变小。有数据只有20GB，而回滚段有200GB的库，最终只好为了清理回滚段，重建整个库。
除了对回滚段的印象，长事务还占用锁资源，也可能拖垮整个库。
事务的启动方式
如前面所述，长事务有这些潜在风险，建议尽量避免，其实很多时候业务开发不是有意使用长事务，而是由于误用所致。
MySQL的事务启动方式有一下几种：
1.显示启动事务语句，begin或start transaction。配套的提交语句是commit，回滚语句是rollback。
2.set autocommit=0，这命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会主动提交，这个事务持续存到知道你主动执行commit或rollback语句，或者断开连接。
有些客户端连接框架会默认连接成功后先执行一个set autocommit=0命令，这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。
因此，建议总是使用set autocommit=1，通过显式语句的方式来启动事务。
在autocommit=1的情况下，用begin显式启动的事务。
但是有的开发同学会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 “begin”，减少了语句的交互次数。
如果你也有这个顾虑，我建议你使用 commit work and chain 语法。
在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。
你可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。
如何避免长事务对业务的影响？
首先，从应用开发端来看：
1.确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志 来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它 改成 1。
2.确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框 起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。 这种只读事务可以去掉。
3.业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命 令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
其次，从数据库端来看：
1.监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
2.Percona 的 pt-kill 这个工具不错，推荐使用；
3.在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
4.如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方 便。
4.深入浅出索引（上）
索引的出现其实是为了提高数据查询的效率，就像书的目录一样。
索引的常见模型
索引的出现是为了提高查询效率，但是实现索引的方式却又很多种，所以这里也就引入了索引模型的概念，可以用于提高读写效率的数据结构很多，比较常见、简单的数据结构分别是哈希表、有序数组和索引树。
从使用的角度，简单分析这三种模型的区别。
哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。
不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。
假设，你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下所示：
图中，User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链 表。假设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。
需要注意的是，图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询 的速度是很慢的。
你可以设想下，如果你现在要找身份证号在 [ID_card_X, ID_card_Y] 这个区间的所有用 户，就必须全部扫描一遍了。
所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。
而有序数组在等值查询和范围查询场景中的性能就都非常优秀。还是上面这个根据身份证 号查名字的例子，如果我们使用有序数组来实现的话，示意图如下所示：
这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候 如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))。
同时很显然，这个索引结构支持范围查询。你要查身份证号在 [ID_card_X, ID_card_Y] 区 间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证 号，退出循环。
如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就 麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有 人口信息，这类不会再修改的数据。
二叉搜索树也是课本里的经典数据结构了。还是上面根据身份证号查名字的例子，如果我 们用二叉搜索树来实现的话，示意图如下所示：
二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你 要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。
当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个 保证，更新的时间复杂度也是 O(log(N))。
树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从 左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉 树。其原因是，索引不止存在内存中，还要写到磁盘上。
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就 不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块 的大小。
N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引 擎中了。
在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引 擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实 现也可能不同。由于 InnoDB 存储引擎在 MySQL 数据库中使用最为广泛，所以下面我就 以 InnoDB 为例，和你分析一下其中的索引模型。
InnoDB 的索引模型
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组 织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
每一个索引在 InnoDB 里面对应一棵 B+ 树。
假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。
表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树 的示例示意图如下。
从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引 （clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引 （secondary index）。
基于主键索引和普通索引的查询有什么 区别？
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树； 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引 树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量 使用主键查询。
索引维护
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例， 如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请 一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合 并。合并的过程，可以认为是分裂过程的逆过程。
基于上面的索引维护过程说明，我们来讨论一个案例：
你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自 增主键。当然事无绝对，我们来分析一下哪些场景下应该使用自增主键，而 哪些场景下不应该。
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。
插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插 入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂
而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字 段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级 索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整 型（bigint）则是 8 个字节。
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。
有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求 是这样的：
- 只有一个索引；
- 该索引必须是唯一索引。
你一定看出来了，这就是典型的 KV 场景。
由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置 为主键，可以避免每次查询需要搜索两棵树。
总结
InnoDB采用B+树结构，因为B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问数。
由于InnoDB是索引组织表，一般情况下建议创建一个自增组件，这样非主键索引占用的空间最小，但是事无绝对，根据业务场景也可以使用业务逻辑字段做主键。
5.深入浅出索引（下）
6.全局锁和表锁：给表加个字段怎么有这么多阻碍？
数据库锁设计的初衷是处理并发问题，作为多用户共享的资源，当出现并发访问的时候，数据需要合理地控制资源的访问规则，而锁就是用来实现这些访问规则的重要数据结构。
根据加锁的范围，MySQL里面的锁大致可以分为全局锁、表级锁和行锁三类。
全局锁
顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock(FTWRL)。当你需要整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本。
以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做 备份。注意，在备份过程中整个库完全处于只读状态。
但是让整库都只读，听上去就很危险：
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从 延迟。
不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑 不一致的。其实是有一个方法能够拿到一 致性视图的，就是在可重复读隔离级别下开启一个事务。
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持， 这个过程中数据是可以正常更新的。
你一定在疑惑，有了这个功能，为什么还需要 FTWRL 呢？一致性读是好，但前提是引擎 要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有 更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。
所以，single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了 不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员 使用 InnoDB 替代 MyISAM 的原因之一。
你也许会问，既然要全库只读，为什么不使用 set global readonly=true 的方式呢？确 实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要 有两个原因：
1.在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库 还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
2.在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个 库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状 态，这样会导致整个库长时间处于不可写状态，风险较高。
业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作 （DDL）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操 作，都是会被锁住的。
但是，即使没有被全局锁住，加字段也不是就能一帆风顺的，因为你还会碰到接下来我们 要介绍的表级锁。
表级锁
MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock， MDL)。
**表锁的语法是lock tables…read/write.**与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放，需要注意，lock tables语法除了会限制别的线程的读写外，也限制了本线程接下来的操作对象。
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
在还没出现更细粒度的锁的时候，表锁是最常用的处理并发的方式，而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
另一类表级的锁MDL（metadata lock）。DML不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性，你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果更表结构对不上，肯定是不行的。
因此，在MySQL5.5版本中引入了MDL，当对一个表做增删查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变成表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
虽然MDL锁是系统默认会加的，但却是你不能忽略的一个机制。比如下面这个例子，我经常看到有人掉到这个坑里：给一个小表加个字段，导致整个库挂了。
你肯定知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响，而实际上，即使是小表，操作不慎也会出问题。
我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需 要的也是 MDL 读锁，因此可以正常执行。
之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。
如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读 锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。
你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会 马上释放，而会等到整个事务提交后再释放。
基于上面的分析，我们来讨论一个问题，如何安全地给小表加字段？
首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请 求很频繁，而你不得不加个字段，你该怎么做呢？
这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不 到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这 个过程。
MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
1 | ALTER TABLE tbl_name NOWAIT add column ... |
小结
全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，我建议你选择使用– single-transaction 参数，对应用会更友好。
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有 lock tables 这样的语句，你需要追查一下，比较可能的情况是：
要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。
MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。
最后，我给你留一个问题吧。备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一 列。这时候，从备库上会看到什么现象呢？
online ddl
Online DDL的过程是这样的：
- 拿MDL写锁
- 降级成MDL读锁
- 真正做DDL
- 升级成MDL写锁
- 释放MDL锁
1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表 可以正常读写数据，是因此称为“online ” 我们文中的例子，是在第一步就堵住了
7.行锁功过：怎么减少行锁对性能的影响？
8.事务到底是隔离还是不隔离的？
