1：预写日志，定期刷脏，防止数据丢失，redo log
InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中(这一过程称为刷脏)。Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL(Write-ahead logging，预写式日志)，所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：
(1)刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
(2)刷脏是以数据页(Page)为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。
总之：WAL预写日志就是快，要不是能快点谁愿意搞得这么复杂
redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写)，而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
update t set c=c+1 where id = 2执行流程，崩溃恢复情况
在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象。如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃(crash)，由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。到这里，大家都可以理解。大家出现问题的地方，主要集中在时刻 B，也就是 binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？我们先来看一下崩溃恢复时的判断规则。如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。
2：脏读、不可重复读和幻读
脏读：A事务读到B事务还没提交的修改
不可重复读：一次事务中的两次读的同一条数据内容不同
幻读：一次事务中的两次读数据条数不同，幻读仅专指“新插入的行”，应修改被第二次查出的数据不属于幻读
产生幻读的原因：行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。
隔离级别
1：都未提交(read uncommitted)，一个事务还没提交时，它做的变更就能被别的事务看到，直接返回最新的数据，不需要创建视图
2：读提交(read committed)，一个事务提交之后，它做的变更才会被其他事务看到，执行每条语句都会创建一个视图，相当于实时更新
3：可重复读(repeatable read)，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的，在事务启动的时候，会创建一个视图，事务执行期间都读这个视图
4：串行化(serializable )，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行，加锁保证了事务执行期间数据不被修改，不需要创建视图
3：磁盘IO与阅读
前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：
4：脏页
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页(flush)
5：为什么表数据删掉一半，表文件大小不变？
delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。实际上，不止是删除数据会造成空洞，插入数据也会。
重建表：alter table A engine=InnoDB
在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。
6：count(*)具体操作
InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。
count(*)、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能对比：
count(主键 id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
count(1) ：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
count(字段)：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。
count(*)：并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
count(字段) < count(主键 id) < count(1) ≈ count(*)，所以我建议你，尽量使用 count(*)。
7：order by
全字段排序 VS rowid排序
全字段排序：将所有需要的字段都放到内存中
rowid排序：只需要把主键id和排序的字段存入内存中，然后按照主键id回表查出需要的字段
如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
如何正确显示随机数据？
1：先查出总行数，通过随机函数得到一个值
2：select * from table limit offset,1
8：字符串和数字做比较的话，是将字符串转换成数字。
//tradeid 类型为varchar(40)，并建有索引
mysql> select * from tradelog where tradeid=110717; //不会使用索引
mysql> select * from tradelog where tradeid=“110717"; //使用索引
基于字符串转数字的规则，tradeid会被转换成数字，转换后的sql为：
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
9：保证数据不丢失
mysql会分两步提交，先写redo log，在写binlog，第一步只是调用文件write函数，将数据写入page cache中，第二步调用文件fsync函数将page cache中的数据写入磁盘，通过技术手动将redo log和binlog一起写入文件，以提高性能，当文件落盘之后再将事务状态更新为成功
10：自增主键为什么不是连续的
insert into t values(null, 1, 1);
这个语句的执行流程就是：
1：执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1);
2：InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；
3：将传入的行的值改成 (2,1,1);
4：将表的自增值改成 3；
5：继续执行插入数据操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。
既然已经回滚了，为什么不把自增的值改回去呢？因为实在是不知道回滚啊！
如有多个事务同时插入数据，有的成功有的失败，回滚到哪个值
1：唯一键冲突是导致自增主键 id 不连续的第一种原因，插入的时候指定了主键id，且没有冲突，插入成功后，可能照成空洞
2：事务回滚也会产生类似的现象，这就是第二种原因
11：mysql取数据和发数据给client的流程
1：获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
2：重复获取行，直到 net_buffer 写满，调用网络接口发出去。
3：如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
4：如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈(socket send buffer)写满了，进入等待。直到网络栈重新可写，再继续发送。
12:前缀索引
只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
13：锁
1：全局锁，2：表锁(表锁，元数据锁meta data lock，MDL)，3：行锁
当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
14：没命中索引的情况
1：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能
2：隐式类型转换，类型是string传int查询，类型是int传string查询，类型是int传string没问题，类型是string就只能传string，类型保持一致就不用管这些细节
3：隐式字符编码转换
15：for update排他锁
1：for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
2：当开启一个事务进行for update的时候，另一个事务也有for update的时候会一直等着，直到第一个事务结束
3：如果查询条件中有明确的主键查询就是行锁，如果没有明确的主键查询就是表锁
聚集索引的叶子节点直接存储了数据，也是数据节点，而非聚集索引的叶子节点没有存储实际的数据，主键索引的id，需要二次查询。
聚集索引是指索引的逻辑顺序与表记录的物理存储顺序一致的索引，一般情况下主键索引就符合这个定义，所以一般来说主键索引也是聚集索引
