mysql45讲--09-44实践篇总结

09讲普通索引和唯一索引，应该怎么选择

查询过程

执行查询语句 select id from T where k=5
普通索引：查找到满足条件的第一个记录（5,500）后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录
唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索
两者的差异时间较小

更新过程

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB的处理流程如下：

• 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 和 redo log

唯一索引用不了change buffer，因为每次都要比对信息是否唯一，所以要先读入内存才能比对。

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

1. Page 1在内存中，直接更新内存；

2. Page 2没有在内存中，就在内存的change buffer区域，记录下“我要往Page 2插入一行”这个信息

3. 将上述两个动作记入redo log中（图中3和4）

做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

1. 读Page 1的时候，直接从内存返回。有几位同学在前面文章的评论中问到，WAL之后如果读数据，是不是一定要读盘，是不是一定要从redo log里面把数据更新以后才可以返回？其实是不用的。你可以看一下图3的这个状态，虽然磁盘上还是之前的数据，但是这里直接从内存返回结果，结果是正确的。

2. 要读Page 2的时候，需要把Page 2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

可以看到，直到需要读Page 2的时候，这个数据页才会被读入内存。

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

10讲MySQL为什么有时候会选错索引

优化器会根据扫描行数，回表次数，是否使用临时表，是否排序等进行综合判断

mysql通过采样统计获得索引的基数

选错索引的解决办法：

1. analyze table t 重新统计索引信息命令

2. 1不行的话用 force index 强行选一个索引。

3. 引导mysql使用我们期望的索引（删除或者新增索引）

11讲怎么给字符串字段加索引

主要要权衡空间和查询成本

1.使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

select id,email from SUser where email='zhangssxyz@xxx.com';

2.使用前缀索引就用不了索引覆盖（就算定义长度足够长也不行，邮箱被省略了一些，找不到email）

加大区分度的方式（减小查询成本）：

1.使用倒序存储，绕过字符串本身的浅醉的区分度不够的问题

2.使用hash字段，查询效率高，用crc32（）这个函数查询效率接近o1，不支持范围查询

12讲为什么我的MySQL会“抖”一下

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。术语就是flush（redo log 写入磁盘的过程）

触发flush的情况：

1.redo log 满了(尽量避免，因为会停止所有更新操作)

2.系统内存不足，需要淘汰一些内存中的数据页，如果是脏页，则flush

InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

• 第一种是，还没有使用的；
• 第二种是，使用了并且是干净页；
• 第三种是，使用了并且是脏页。

3. mysql认为系统空闲时，大量flush，忙的时候也要见缝插针的flush

4.mysql正常关闭前，要flush

innodb刷脏页的策略

考虑两个因素，脏页比例 （m）和redo log写盘速度（n）

根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

 13讲为什么表数据删掉一半，表文件大小不变

删除数据只是把这个记录标称已删除，可以复用，相邻的数据页利用率都很小或者整个数据页被delete，就会把整个数据页标记成可复用

delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的

记录的可服用和数据页的可复用不同，记录的复用，只限于符合范围条件的数据。而数据页的复用可以到任何位置

 经过增删改的数据都可能出现空洞。

重建表

解决空洞的方法

用alter table A engine=InnoDB，但不是online的，执行阶段不能更新

MySQL 5.6版本开始引入的Online DDL，对这个操作流程做了优化。

1.在更新过程中加入 row log，记录更新过程中的操作，之后在写入到临时文件，在更新过程中实现online，

Online 和 inplace（原地）

根据表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出来的。整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

1. DDL过程如果是Online的，就一定是inplace的；

2. 反过来未必，也就是说inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

14讲count(*)这么慢，我该怎么办

你首先要明确的是，在不同的MySQL引擎中，count(*)有不同的实现方式。

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
• 而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

因为在innodb里，由于mvcc的原因，InnoDB表“应该返回多少行”也是不确定的。所以要根据一致性视图看这个记录是不是对这个会话可见。

redis记录cnt数会导致逻辑上不精确（先插数据还是先redis+1？），可以利用数据库事务的特性来记录cnt树，实现逻辑一致

对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

对于count(字段)来说：

1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；

2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

count(字段)<count(主键id)<count(1)≈count(*)，所以我建议你，尽量使用count(*)。

16讲“orderby”是怎么工作的

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

全字段排序

如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

rowid排序

如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

这也就体现了MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

可以利用索引来排序

因为索引取出来的数据本来就是有序的，避免了排序

甚至可以用索引覆盖避免回表

17讲如何正确地显示随机消息

1. order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

2.利用堆来排序（需要的行数小的时候用，维护的堆小）

 

 3. 随机算法1（主键id之间随机）

1. 取得这个表的主键id的最大值M和最小值N;

2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;

3. 取不小于X的第一个ID的行。

4.随机算法2（1的改进）（行号之间随机，避免了主键id差距过大引起的随机性不均匀）

1. 取得整个表的行数，并记为C。

2. 取得 Y = floor(C * rand())。 floor函数在这里的作用，就是取整数部分。

3. 再用limit Y,1 取得一行。

4，2最好 。1最差。

18讲为什么这些SQL语句逻辑相同，性能却差异巨大

1.对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

2.隐式类型转换

mysql> select * from tradelog where tradeid=110717;

交易编号tradeid这个字段上，本来就有索引，但是explain的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid的字段类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换。

3.隐式字符编码转换  utf8mb4是utf8的超集 

2和3的本质就是做了函数操作，内部违反了1的操作，所以不能走索引

 MySQL的优化器确实有“偷懒”的嫌疑，即使简单地把where id+1=1000改写成where id=1000-1就能够用上索引快速查找，也不会主动做这个语句重写。

19讲为什么我只查一行的语句，也执行这么慢

第一类：查询长时间不返回

1.等MDL锁

show processlist命令查看时的状态为：Waiting for table metadata lock

解决方法：找到谁持有MDL写锁，然后把它kill掉。

2. 等flush

show processlist命令查看时的状态为：Waiting for table flush

flush很快，很可能是因为flush的时候被别的语句阻塞住了，导致flush阻塞，从而导致后面的select也阻塞

解决方法：看show processlist结果 ，谁堵了找谁

 3.等行锁

事务持有写锁未提交

可以通过sys.innodb_lock_waits 表查到。

查询方法是：

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

 第二类：查询慢

当前读比一致性读快，带lock in share mode的SQL语句，是当前读，因此会直接读到1000001这个结果，所以速度很快；而select * from t where id=1这个语句，是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回。

 20讲幻读是什么，幻读有什么问题

 幻读是什么？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

2. 上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

 幻读有什么问题？

1. 首先是语义上的。session A在T1时刻就声明了，“我要把所有d=5的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。

 

 BC都可以对d=5进行修改，但是A的语意就是要锁住d=5的行（读锁）

2. 数据一致性的问题

binglog是按照提交时间写的，而数据库中是当前读，binlog恢复出来的数据和数据库不一致

但是就算把所有的扫描的记录都加上锁也防止不了幻读，因为幻读的数据之前根本不存在，加不了锁

如果之前在的数据还可以通过加锁保证不被改变。

如何解决幻读？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。这，也是现在不少公司使用的配置组合。

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作，间隙锁之间都不存在冲突关系。

跟行锁有冲突关系的是“另外一个行锁”。 

 

 间隙锁和行锁合称next-key lock

间隙锁带来的”困扰“

1.死锁的概率更高

 间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的

 21讲为什么我只改一行的语句，锁这么多

我总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

1. 原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。

2. 原则2：查找过程中访问到的对象才会加锁。

3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

22讲MySQL有哪些“饮鸩止渴”提高性能的方法

短连接风暴

如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

第一种方法：先处理掉那些占着连接但是不工作的线程。

 第二种方法：减少连接过程的消耗。如跳过权限验证阶段

慢查询性能问题

1. 索引没有设计好；

2. SQL语句没写好；如select * from t where id + 1 = 10000

3. MySQL选错了索引。force index解决，或者引导优化器走索引

23讲MySQL是怎么保证数据不丢的

 合并到binlog redo log 那边了！

24讲MySQL是怎么保证主备一致的

 1. 在备库b上通过change master命令，设置主库a的ip，端口，用户名，密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。

2. 在备库b上执行start slave命令，启动io_thread和sql_thread线程，io_thread是负责与主库建立连接的

3. 主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。

4. 备库B拿到binlog后，写到本地文件，称为中转日志（relay log）。

5. sql_thread读取中转日志，解析出日志里的命令，并执行。

循环复制问题

主要思想就是设置一个serverid，来判断是不是与自己相同的serverid，一样就不执行，具体如下

1. 规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；

2. 一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；

3. 每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志

25讲MySQL是怎么保证高可用的

主备延迟

1. 主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1;

2. 之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2;

3. 备库B执行完成这个事务，我们把这个时刻记为T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是T3-T1。

主备延迟的来源

1.备库的性能比主库差

2.备库压力大

3.大事务

主备切换的策略

1.可靠性优先策略

2.可用性优先策略

在实际的应用中，我更建议使用可靠性优先的策略。毕竟保证数据准确，应该是数据库服务的底线。在这个基础上，通过减少主备延迟，提升系统的可用性。

26讲备库为什么会延迟好几个小时

多线程复制机制，把sql_thread，拆成多个线程，也就是都符合上面的这个模型

分发原则：

1. 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。

2. 同一个事务不能被拆开，必须放到同一个worker中。

27讲主库出问题了，从库怎么办

GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.

可以用GTID模式来做一主多从的切换，以全局事务id为单位，代替了binlog和position号的主从复制搭建方式

GTID的好处：

（1）GTID使用master_auto_position=1代替了binlog和position号的主从复制搭建方式，相比binlog和position方式更容易搭建主从复制。

（2）GTID方便实现主从之间的failover，不用一步一步的去查找position和binlog文件。

28讲读写分离有哪些坑

主从有延迟，过期读咋办？

• 强制走主库方案；
• sleep方案；
• 判断主备无延迟方案；
• 配合semi-sync方案；

   semi-sync做了这样的设计：

1. 事务提交的时候，主库把binlog发给从库；

2. 从库收到binlog以后，发回给主库一个ack，表示收到了；

3. 主库收到这个ack以后，才能给客户端返回“事务完成”的确认。

加上两种模式的等待

• 等主库位点方案；
• 等GTID方案。

29讲如何判断一个数据库是不是出问题了

1. select 1判断​​​​​​​ ： select 1成功返回，只能说明这个库的进程还在，并不能说明主库没问题

2 .查表判断 ：在一个系统库（mysql库）中建立一个表，放一条数据，定期查询，能查出来就是没问题

3.更新判断：同2，一般更新当前时间

4. 内部统计：开启redo log和binlog这两个统计信息，看io请求速度来判断是不是有问题

30:答疑略

31讲误删数据后除了跑路，还能怎么办

误删行

Flashback工具通过闪回把数据恢复回来。

误删库/表

全量备份+增量日志

 32讲为什么还有kill不掉的语句

这些“kill不掉”的情况，其实是因为发送kill命令的客户端，并没有强行停止目标线程的执行，而只是设置了个状态，并唤醒对应的线程。而被kill的线程，需要执行到判断状态的“埋点”，才会开始进入终止逻辑阶段。并且，终止逻辑本身也是需要耗费时间的。

所以，如果你发现一个线程处于Killed状态，你可以做的事情就是，通过影响系统环境，让这个Killed状态尽快结束。

比如，如果是第一个例子里InnoDB并发度的问题，你就可以临时调大innodb_thread_concurrency的值，或者停掉别的线程，让出位子给这个线程执行。

而如果是回滚逻辑由于受到IO资源限制执行得比较慢，就通过减少系统压力让它加速。

做完这些操作后，其实你已经没有办法再对它做什么了，只能等待流程自己完成。

33讲我查这么多数据，会不会把数据库内存打爆

由于MySQL采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在server端保存完整的结果集。所以，如果客户端读结果不及时，会堵住MySQL的查询过程，但是不会把内存打爆。

而对于InnoDB引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于InnoDB对LRU算法做了改进，冷数据的全表扫描，对Buffer Pool的影响也能做到可控。

innodb的改进lru

这个策略，就是为了处理类似全表扫描的操作量身定制的。还是以刚刚的扫描200G的历史数据表为例，我们看看改进后的LRU算法的操作逻辑：

1. 扫描过程中，需要新插入的数据页，都被放到old区域;

2. 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；

3. 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是young区域），很快就会被淘汰出去。

 可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了Buffer Pool，但是对young区域完全没有影响，从而保证了Buffer Pool响应正常业务的查询命中率。

34讲到底可不可以使用join

Index Nested-Loop Join（NLJ）

先查驱动表，根据驱动表里的关联字段，用上被驱动表的索引，查出被驱动表中的记录，然后合并

Simple Nested-Loop Join （被驱动表没有索引的情况）

 被驱动表没有索引的情况，驱动表和被驱动表都要走全局扫描，很慢。

Block Nested-Loop Join(BNL)

就是把两个表都先读入内存，再做Simple Nested-Loop Join 

1. 两个表都做一次全表扫描，所以总的扫描行数是M+N；

2. 内存中的判断次数是M*N。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

35讲join语句怎么优化

Multi-Range Read优化

​​​​​​​​​​​​因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

这，就是MRR优化的设计思路。此时，语句的执行流程变成了这样：

1. 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;

2. 将read_rnd_buffer中的id进行递增排序；

3. 排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势

Batched Key Access

们就把表t1的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是join_buffer

 大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

BNL转BKA

1. 把表t2中满足条件的数据放在临时表tmp_t中；

2. 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；

3. 让表t1和tmp_t做join操作。

36讲为什么临时表可以重名

临时表的应用

处理35节的jion，分库分表时非分批建查询，可以多表全查询后统一放到某库某实力上，做一个临时的统一表，在进行limit操作等

为什么临时表可以重名？

这个语句的时候，MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保存表数据。

这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_{线程id}_序列号

不同线程不同名！！

在binlog_format='row’的时候，临时表的操作不记录到binlog中，也省去了不少麻烦，这也可以成为你选择binlog_format时的一个考虑因素。

37讲什么时候会使用内部临时表

union 执行流程

(select 1000 as f) union (select id from t1 order by id desc limit 2);

1. 创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段。

2. 执行第一个子查询，得到1000这个值，并存入临时表中。

3. 执行第二个子查询：

   • 拿到第一行id=1000，试图插入临时表中。但由于1000这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；
   • 取到第二行id=999，插入临时表成功。

4. 从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是1000和999。

主要用了临时表主键id的唯一性约束

group by 执行流程

select id%10 as m, count(*) as c from t1 group by m;

1. 创建内存临时表，表里有两个字段m和c，主键是m；

2. 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

   • 如果临时表中没有主键为x的行，就插入一个记录(x,1);
   • 如果表中有主键为x的行，就将x这一行的c值加1；

3. 遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

group by 优化方法 --索引

 group by优化方法 --直接排序

 

 对需要排序的最短先在buffer中排序，排序的字段少，速度快，比起全部找完再排序来说。

 38讲都说InnoDB好，那还要不要使用Memory引擎

InnoDB和Memory引擎的数据组织方式是不同的：

• InnoDB引擎把数据放在主键索引上，其他索引上保存的是主键id。这种方式，我们称之为索引组织表（Index Organizied Table）。
• 而Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

 

从中我们可以看出，这两个引擎的一些典型不同：

1. InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；

2. 当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；

从中我们可以看出，这两个引擎的一些典型不同：

1. InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；

2. 当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；

3. 数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引；

4. InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。

5. InnoDB支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob 和 Text字段，并且即使定义了varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

hash索引和B-Tree索引

b-树

 不建议你在生产环境上使用内存表的原因：

1. 锁粒度问题；

2. 数据持久化问题。

39讲自增主键为什么不是连续的

表的结构定义存放在后缀名为.frm的文件中，但是并不会保存自增值。

不同的引擎对于自增值的保存策略不同。

• MyISAM引擎的自增值保存在数据文件中。
• InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，

自增值修改机制

 没有传id就自增，传了如果大于等于就变成传的。

自增主键id不连续的原因

1.唯一键冲突是导致自增主键id不连续的第一种原因。（自增值修改在插入前，插入失败也会先++）

2.回滚也会产生类似的现象

3.批量插入

但如果一个select … insert语句要插入10万行数据，按照这个逻辑的话就要申请10万次。显然，这种申请自增id的策略，在大批量插入数据的情况下，不但速度慢，还会影响并发插入的性能。

因此，对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

1. 语句执行过程中，第一次申请自增id，会分配1个；

2. 1个用完以后，这个语句第二次申请自增id，会分配2个；

3. 2个用完以后，还是这个语句，第三次申请自增id，会分配4个；

4. 依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。

40讲insert语句的锁为什么这么多

insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给select的表里扫描到的记录和间隙加读锁。

而如果insert和select的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。

insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的next-key lock(S锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

41讲怎么最快地复制一张表

mysqldump方法

一种方法是，使用mysqldump命令将数据导出成一组INSERT语句。你可以使用下面的命令：

mysqldump -h$host -P$port -u$user --add-locks --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/cli

把结果输出到临时文件。

导出CSV文件

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

得到.csv导出文件后，你就可以用下面的load data命令将数据导入到目标表db2.t中。

load data infile '/server_tmp/t.csv' into table db2.t;

物理拷贝方法 （上面两种都是逻辑，都是select出来再导入）

在MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法，可以通过导出+导入表空间的方式，实现物理拷贝表的功能。

 424344略

45讲自增id用完怎么办

1. 表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。

2. row_id达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据。

3. Xid只需要不在同一个binlog文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。

4. InnoDB的max_trx_id 递增值每次MySQL重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的bug，好在留给我们的时间还很充裕。

5. thread_id是我们使用中最常见的，而且也是处理得最好的一个自增id逻辑了。