MySQL 实战 45 讲笔记

一、基础篇

1. 基础架构

MySQL 基本架构

• 连接器
  • 执行MySQL 的连接操作；
  • 也可分为长连接和短连接，长连接占用系统资源，短连接频繁建立连接耗时。
• 查询缓存
  • 可能之前查询过，则直接在缓存中以 key value 存储；
  • 不建议使用，因为缓存命中率低，一旦对应表更新就会刷新缓存；
  • 8.0 之后移除该功能。
• 分析器
  • 如果没有命中缓存，就进入分析器，进行语法、词法的分析；
  • 也就是看 SQL 语句有没有写错；
• 优化器
  • 对于索引的选择优化；
• 执行器
  • 执行前检查是否有对该表的操作权限；
  • 有的话进行执行。

2. 日志系统

• redo log

  • redo log 记录的是每页数据的物理修改；
  • 执行事务的时候，会写入 redo log buffer，并且主线程的循环事件也会将缓冲中的内容刷新到磁盘，即使该事务没有提交；
  • redo log 采用循环写的方式，对于一组 redo log 循环写，如果要覆盖之前没有的内容，则需要将之前的内容脏页同步到磁盘；
  • 保证了事务的持久性；

• bin log

  • bin log 是server 层的日志；

  • 只有在日志提交之后，才会将记录同步到日志文件中。

  • 记录的形式有三种：

    • row：记录数据行的数据改变；
    • statement：记录 SQL 语句，使用rand()，now()等函数可能造成主从数据不一致；
    • mixed：两种形式混用；

  • 为什么 binlog 要有三种形式？

    • statement 可能造成主从数据不一致；
    • 而 row 则更占用空间，例如删除一万条数据，statement 只需要一条 SQL 而 row 需要记录每一条数据的删除；
    • 所以引入 mixed 形式，MySQL 会判断该语句是否会引发主从不一致，如果会，则使用 row，不会则使用 statement；

• 两者区别：

  • redo log 是 InnoDB 存储引擎层实现的日志，bin log 是 MySQL Server 层实现的，适用于所有存储引起；
  • redo log 是物理日志，记录物理数据页的改动，bin log 是逻辑日志，记录的是逻辑的修改，例如某个数据的更新或者一条 SQL；
  • redo log 是循环写的方式，会覆盖之前内容，bin log 则是不断追加写入；
  • redo log 会不断写入磁盘，即使事务没有提交，bin log 则在事务提交后同步磁盘；

更新一条数据流程如下：

为什么需要两阶段提交？也就是 prepare 阶段 和 commit 阶段

试想，如果redo log 写入成功，数据库宕机或重启等，bin log 没有写入成功，则两者数据不一致，会造成主库和从库数据不一致（使用 binlog 复制），或者进行备份的时候造成不一致，所以需要两阶段提交。

两阶段提交过程：

• 预写入 redo log ，进入 prepare 状态，没有异常则进入下一步，表示可以对事务进行提交；
• 写入 bin log 日志，成功则返回消息，让 redo log 也提交；
• redo log 将事务提交，设置为 commit 状态，如果其中有一个环节出错就回滚。

3. 事务隔离

读已提交和可重复读的使用场景

• 读已提交
  • 大部分业务的选择；
  • 提高并发度，可以读到最新数据；
• 可重复读
  • 例如月初，要对上个月的账单进行清算；
  • 但是你不想在清算的过程中被新加入的转账操作影响；
  • 就可以采用可重复读；

避免使用长事务

• 可以及时回收 undo log ，减少空间占用（undo log 会在事务提交，以及没有事务版本号小于undo log 中最大版本号的时候提交）；
• 减少对于锁资源的侵占，提高并发性；

4. 索引

主键索引和非主键索引查询的区别：

• 主键索引可以直接在叶子结点中查询出整个数据行；

• 而辅助索引则在叶子结点取得对应的主键，再通过主键构造的 B+ 树查询出对应数据，这成为回表查询；

索引是越多越好吗

• 索引的存储需要占用内存空间；
• 索引需要维护相对应的一颗 B+ 树，对于插入和删除操作，可能会造成页的分裂和合并，十分耗时；
• 可以通过自增主键插入使得变成顺序 IO，并且防止页的分裂；
• 通过逻辑删除可以防止页的合并；

哪些场景下应该使用自增主键，而哪些场景下不应该？

• 大部分场景都使用自增主键，可以保证插入操作是顺序IO，防止页分裂；
• 方便构建 B+ 树以及利于范围查询排序等。
• 并且主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小（所以不适合例如使用身份证号作为主键）。
• 不使用递增字段作为主键索引的场景：
  • 只有一个索引；
  • 该索引必须是唯一索引；
  • 也就是类似于 KV 的映射关系可以采用非递增主键；

索引下推是什么？

• 是在 5.6 之后加入的新特性；
• 索引下推指的是在使用联合索引的情况下，即使不满足最左前缀原则，也可以通过联合索引进行过滤，来减少回表查询的次数；

例如：对于联合索引（name，age），执行以下 SQL

SELECT * FROM t_user WHERE name LIKE '李%' AND age = 20 AND sex = 1;

• 因为 LIKE ‘李%’ 使用了范围查询，所以之后的联合索引不符合最左前缀原则，所以失效了；
• 按照逻辑，应该使用该部分查出的主键索引再去回表查询，找出对应的列，再比对后两个条件；
• 但是，由于索引下推，可以直接通过 age 对索引进行过滤，将 age != 20 的直接排除，减少回表查询的数据量；

5. 锁

什么是全局锁？有什么应用场景？

• 通过 Flush tables with read lock 命令，可以让整个数据库处于只读状态；
• 经典实用场景是对于全库的逻辑备份；

数据库出现死锁了怎么办？

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

事务到底是不是隔离的？

• 一般快照读的情况下，基于 MVCC ，事务之间是相互隔离的；
• 在 READ COMMIT 隔离级别下，每次读取会读到最新的快照；
• 在 REPEATBLE READ 隔离级别下，开启事务时，会生成一个事务 ID，每次只会读取事务 ID版本之前的行数据；
• 但是如果是更新语句，或者对SELECT 进行加锁（for update，lock in share mode），则会进行当前读，去读取最新的数据；

查询条件不是索引的时候，会锁住什么？

• 对于 RR 隔离级别，对于非索引的查询字段，会锁住整张表；
• 而对于 RC 隔离级别，对于非索引的查询也是锁住表，但是在查出数据之后，如果条件不符合则会即时解锁；

二、实践篇

1. 选择普通索引还是唯一索引？

首先，该问题讨论在业务保证数据库不需要进行唯一性检查的情况下。

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

对于查询：都是通过索引找到对应的数据；

对于更新和插入操作：更新插入操作就是两者性能差距的主要方面：

• 如果对应数据页在 MySQL 内存中，则差距不大，找到对应位置，唯一性索引需要进行唯一判断，但是都是内存操作，两者差距微乎其微；
• 如果对应数据页不在内存中，对于唯一索引，要将对应的数据页调入内存，判断是否有唯一性冲突，然后插入；而对于普通索引，则只需要直接插入到 change buffer 中，减少了很多索引页数据页的IO操作。

change buffer 是内存中的一个缓冲区，当使用非主键索引进行插入或者更新操作的时候，就会将操作写入到 change buffer 中来减少对于磁盘的随机 IO，等合适的时机再同步该数据，例如，对应数据页被读入内存，或者主线程的循环操作，或者对应数据页快没有空间的时候；
具体可看 https://blog.csdn.net/weixin_48922154/article/details/119152805

2. MySQL 为什么有时候会选错索引？

优化器是否选择以及如何选择索引，主要考虑什么？

• 扫描的行数；
• 是否使用临时表；
• 是否使用排序；

优化器如何判断扫描的行数？

• MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

• 这个统计信息就是索引的“区分度”。显然，索引上不同的值越多，索引的区分度就越好。索引上不同的值的个数，称之为“基数”（cardinality）。基数越大，索引的区分度越好。可以使用 show index 查看；
  

• 优化器得到基数的方式是采样分析，即，选取一些数据页得到不同值的个数，再乘所有的数据页，而不需要对全表进行一个扫描，这个值也是动态的，当修改的行数超过一个阈值，就会重新计算。

• 优化器再根据基数来预估具体需要扫描多少行才能得到对应的数据，再和回表查询的次数等进行一个综合判断，来选择索引；

如果优化器选择错索引怎么办？

• 采用 force index 强行选择一个索引：没有该语句时，会通过分析器分析到所有可用使用的索引，然后通过优化器进行选择，有该语句，则直接使用该索引；
• 修改语句，引导 MySQL 使用我们期望的索引；
• 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
• analyze table t 命令，可以用来重新统计索引信息。

3. 怎么给长字符串加索引？

例如，要给邮箱加上索引来优化查询性能，可以使用前缀索引，来减小索引大小，使得每个索引页增加索引个数，提高查询性能，但是，前缀索引带来的影响是，使得覆盖索引的优化失效，以及，可能会增加回表查询的次数，所以要选择区分度高的前缀。

但是对于例如身份证号，则前缀的区分度很低，前面是用来表示地区的编号，会增加大量回表查询，所以可以采用两种优化方式：

• 使用倒叙存储 + 前缀索引
  • 因为对于身份证来说，后几位的辨识度高，重复概率小；
  • 所以可以采用逆序的方式存储，并且加上前缀索引，查询的时候使用如下语句：

select field_list from t where id_card = reverse('xxxx');

• 使用 hash 字段
  • 也就是再增加一个字段，作为身份证号的校验码，值为身份证的散列值，为该字段加上索引，长度远小于身份证；
  • 但是可能造成hash 冲突，进行回表查询，但次数较少可以接受；
  • 查询时可以采用如下语句：

select field_list from t where id_card_crc=crc32('xxxx') and id_card='xxxx';

4. 为什么我的 MySQL 抖了一下？

这个问题换一个说法就是，为什么我的 SQL 执行那么慢？

我认为主要包括三种原因：

• SQL 语句本身原因，没有使用索引，索引失效等；
• 数据量太大，查询效率低；
• 正好碰上阻塞获取锁资源；
• 正好处于刷新脏页的时候；

这次，主要对最后一个问题进行讨论。

刷新脏页主要有四种情况：

• 新写入的 redo log 要覆盖之前的 check point 还未检查到的数据页，此时，就要将对应的脏页刷新到磁盘进行持久化；
• 由于 MySQL 需要调入内存页，而进行页面置换，将部分脏页换出内存，发现其还未被同步到磁盘，则刷新到磁盘；
• 刷新线程的周期操作，刷新脏页，会在压力大的时候刷新小部分或者不刷新，在空闲的时候刷新多一点的磁盘；
• 数据库关闭时，将所有脏页刷新到磁盘；

最后两种情况，对于执行SQL情况下影响不大，所以，可能是前两种情况造成延迟。

如何对这种情况进行调优？

• 适当的增大 redo log 大小；如果 redo log 太小，会导致经常覆盖之前的 redo log 而造成经常性的刷新脏页操作；
• 合理地设置 innodb_io_capacity 的值，合理的加快刷新脏页的速度；
• 关注脏页比例，不要让它经常接近 75%；
• innodb_flush_neighbors 参数表示是否刷新邻接页，也就是刷新脏页的时候是否将附近的脏页一起刷新；

5. 为什么表数据删掉一半，表文件大小不变？

这个问题时因为， MySQL InnoDB 的删除操作，是将该数据页或者行记录标志为可复用，而不是物理上的删除，也就是例如 删除 ID 等于 4 的字段，这时候下一条数据可以复用该数据行的空间，但是记录的复用，只限于符合范围条件的数据。例如插入一条 ID 是 8 的记录，就不能占用该空间，所以删除操作可能并不能回收表空间。

不仅是删除，插入操作或者更新操作也会发生这种现象，对于插入，如果不是通过主键的插入，也就是离散的插入，可能造成页的分裂而导致空闲的记录行；对于更新操作，则看作是删除一条记录而插入一条新的记录，所以也可能发生这种情况。

所以，经过大量增删改的表，都是可能是存在空洞的，要想去除这些空洞，重建表是一个选择。

可以通过该命令对表进行一个重建，5.6之前是同步阻塞的重建过程，数据库无法对外界提供服务，5.6 之后，就采用类似 Redis AOF 重写的操作，先建立一个临时表，扫描原表中所有的主键组织的数据页，这时，数据库同时对外提供服务，将执行操作写入一个日志文件，临时表重建完成后再同步日志文件内容，再原子替换之前的数据文件。

alter table A engine=InnoDB

6. count(*) 执行很慢该怎么办？

为什么 InnoDB 不将记录数单独存起来，取的时候直接返回？

• 因为对于 InnoDB 为了支持 MVCC，可能相同时间的几个事务，读取到的记录数是不一样的；
• InnoDB 存储引擎在获取 count(*) 时会遍历整张表获取对应记录数；

count(*)、count(主键 id)、count(字段) 和 count(1) 有哪些差别？

• 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
• 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
• 对于 count(字段) 来说：如果字段设置为 not null，则直接累加，如果不是，则要取出对应数据，判断不是null再累加；
• 但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。直接累加；

按照效率排序的话，count(字段)<count(主键id)<count(1) 约等于 count(*)。

7. order by 是怎么工作的？

对于 order by 的排序，MySQL 内部会将对应的行数据的全字段，取出放在 sort_buffer 中进行排序，如果大小足够则在内存中排序，如果不够，则需要用到磁盘的临时文件辅助排序。

临时文件排序的方式是，分成多个文件，每个文件放在 sort_buffer 中排序，然后多个文件进行归并排序，所以 sort_buffer 值越小，则需要拆分越多临时文件。

平常的排序，InnoDB 会把需要的字段全部取出进行排序，如果字段很多很长，则 sort_buffer 中存储的数据行就很少，则需要更多的临时文件，效率较低且占用资源；

这时可以设置如下参数：该参数意为如果数据行长度大于该值，则只取出对应排序的行和ID，排序之后，再通过ID去查询对应的数据；

SET max_length_for_sort_data = 16;

order by 很消耗时间，怎么样可以避免排序？

当然就是加索引啦！比如查询语句如下：

SELECT * FROM t WHERE a = 1 ORDER BY b ASC;

如果查出 a 的对应值之后，b就是有序的，那不就不需要再进行排序了吗，所以建立联合索引（a，b）可以很好解决这个问题。

8. MySQL是怎么保证数据不丢的？

binlog 的写入机制是什么样的？

在执行事务的时候，会先把日志写到 binlog cache 中，如果超出缓冲大小，则会暂存到临时文件，无论一个事务有多大，都会在事务提交之后，将 binlog cache 一次性写入到 binlog 中，然后清空 binlog cache 内容。

对应两个方法：

• 一个是 write，将日志写入到 page cache （文件系统的内核缓冲区）中。
• 还有一个是 fsync ，也就是将 page cache 中的内容刷新持久化到磁盘。

redo log 是如何写入的？

对 redo log 的写入也是先写入到 redo log buffer ，再通过 write 写入到 page cache 再通过 fsync 同步到磁盘。

大致有四种情况会使 redo log 同步到磁盘：

• 事务提交，将 redo log buffer 内容持久化到磁盘；
• InnoDB 后台主线程每秒一次的循环操作，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
• redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。
• 多个并行执行的事务，一个事务的提交，将其他事务的日志也顺带刷新到磁盘。

组提交（group commit）机制对日志持久化的优化是什么样的？

组提交指的就是，当日志在进行 fsync 的持久化时，可能有多个事务的日志已经写入到缓冲区中准备持久化，这时，下一次 fsync 就可以将对应的一组日志一起持久化到磁盘中。

在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。

为了保证多个事务日志写入的顺序已经不重复写入等，引入日志逻辑序列号（log sequence number，LSN）。

LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。（类似于 TCP 首部的序号）

9. 备库什么时候会延迟？

MySQL 要保证高可用，最终一致性是远远不够的，但是很多时候，主从会发生一段时间的数据不一致问题。

在正常的网络状况中，一般延迟来自于备库接收完 binlog 和执行完这个事务之间的时间差。

主要原因有：

• 一些部署条件下，从库性能比主库差；
• 从库的压力大，可能由于主库执行直接影响业务，所以使用比较克制，而忽视从库负载；这种情况一般采用一主多从的方式来分担从库压力，或者将binlog导入到外部系统例如 Hadoop 中；
• 对于大事务的操作，会造成主从数据不一致；

对于以上情况，延迟一般在分钟级别，因为都是由于性能的原因造成，一段时间后，从库一般都能追上来。

但是如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏。

5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。

之后，为了采用多线程复制操作，引入 coordinator 也就是原来的 sql_thread，负责读取中转日志和分发事务，真正更新日志的变成了worker线程。另外，coordinator 在分发事务的时候必须满足，不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。以及同一个事务不能被拆开。

8. 误删数据了怎么办？

对于数据行的误删

• 对于 delete 语句误删数据的话，可以使用Flashback 工具通过闪回把数据恢复回来。
• flashback 的原理是通过修改 binlog ，拿回原库中重放。所以要确保 bin log 使用的模式是 row，否则可能造成数据的不一致。
• 但是该方法只适用于对于数据行的删除，因为对于truncate /drop table 和 drop database来删除的数据尽管设置为 row，最后执行的也是 statement 模式。

对于数据库或者表的误删

• 可以使用全量备份加增量的方式。
• 当一个数据库或者数据表被误删的时候，比如每一天对一个数据库进行备份，被误删之后，就取最近的一次备份数据进行还原；
• 然后再从 bin log 中找出该备份之后执行的操作进行增量的更新；

9. 如何使用 join？

什么是驱动表，如何选择驱动表？

在 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

所以在选择驱动表的时候，应该让小表去驱动大表，小表进行全表扫描，取出数据，再去大表中通过索引查询出数据封装到结果级。所以需要有一个前提， 那就是可以使用上大表的索引进行查询。

什么是小表？

小表不能单独只看两张表的数据量来判定小表，而应该是经过对应的查询过滤条件等信息筛选出来的小表作为驱动表！

到底可不可以使用 join ？

• 如果可以使用 Index Nested-Loop Join 算法也就是如上提到的用法，可以利用到大表的索引，那 join 是不存在问题的；
• 对于使用 Block Nested-Loop Join 算法的情况，也就是无法使用大表索引的情况，具体如下：
  • 需要先把驱动表的数据读入到 join buffer 中，再扫描被驱动表全表，取出每一行进行比对，如果满足条件则加入到结果集中。
  • 如果 join buffer 的内存不够，就要进行分段的扫描，先读取驱动表一部分数据行进入 join buffer ，然后将被驱动表的数据一行一行匹配，然后再清空 join buffer 的内容，继续读取没读完的驱动表内容再进行匹配，循环往复。也就是增加了呗驱动表的读取次数。
  • 这种情况下，可能要扫描被驱动表很多次，会占用大量的系统资源。不建议使用。
  • 可以使用别的查询方式替代，例如分两次查询等；

查询时，如何区分驱动表和被驱动表？

• 在 left join 的时候，左侧为驱动表，右侧为被驱动表；
• right join 则变成了右侧为驱动表；
• inner join 则是选取两张表都相交的部分；默认也是选取左侧表为驱动表；

join 如何优化？

• 使用 Multi-Range Read 优化：也就是当通过辅助索引来进行范围查询或者 join 的连接的时候，会将查询出来的对应主键 ID 放在缓冲池中进行一个排序，再去磁盘中读取，这样可以把随机的IO转变为顺序IO；
• Batched Key Access 优化：也就是一次性从驱动表中取出更多行的数据，通过 MRR 批量的取被驱动表进行匹配；
• 自己实现基于 hash 的join，可以加快比对的时间，不需要循环遍历驱动表的每条数据；

10. 什么时候需要使用临时表？

什么是临时表？

例如如下SQL

create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

• 临时表的建表语法是 create temporary table …。
• 临时表可以存在于各种存储引擎，使用 InnoDB 或者 MyISAM 的时候，也会像写普通表一样写到磁盘；
• 临时表只能被创建它的会话可见，对于其他线程都是不可见的，会在会话结束的时候，自动删除临时表；
• 临时表和普通表可以重名，默认优先访问临时表；

临时表有什么应用场景？

• 分库分表系统的跨库查询就是一个典型的使用场景。例如如下查询：

select v from temp_ht order by t_modified desc limit 100; 

• 如果该查询涉及到分库分表的查询，需要在多个数据库中进行操作，主要有两种方式：
  • ①：通过应用程序和数据库中间的代理层，完成数据的读入排序筛选等操作；
  • ②：从各个分库中拿到对应的筛选数据，汇总到同一个表中建立临时表做逻辑操作；

什么时候会使用内部临时表？

内部临时表就相当于一个内部的缓存，暂存对应的数据行信息

• 使用 union 对两个子查询结果取并集；使用临时表来帮助 union 进行去重，如果不需要去重，例如 union all，则不需要临时表；
• 使用 group by 语句进行分组；会在临时表中插入数据。如下两种情况，就不需要再使用临时表

group by 可以如何优化？

• 给分组的字段加上索引，这样该字段就是有序的，可以直接通过遍历数据来进行分组；
• group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。就不需要使用临时表

11. 什么时候需要使用 Memory 存储引擎？

InnoDB 存储引擎和 Memory 存储引擎有什么区别？

• InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
• Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。
• InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
• 当数据有空洞的时候，InnoDB 为了保存数据的有序性会再固定的位置插入数据，而 Memoiry 引擎会在空位中插入对应的数据；
• 当有数据位置发生改动的时候，InnoDB 只需要更改主键索引对应的B+树，而Memory 存储引擎则需要修改所有；
• 在 InnoDB 存储引擎中对于主键索引的搜索只需要一次就可以定位数据，普通索引如果没有使用覆盖索引的话需要回表查询，而 Memory 都是通过定位索引再去读取数据；
• 内存表每行数据长度相同，不支持 Blob 类型和 Text 类型，并且varchar会转变成char处理；
• 内存表主键使用 Hash 索引，在进行范围查询的时候，可以建立 B+ 树索引或者使用数组来加快速度；并且内存表是都存储在内存中的，读取速度比磁盘快；

为什么在生产中不建议使用内存表？

• 锁粒度的问题：
  • 内存表不支持行锁，所有的操作都会锁表；
  • 锁力度增大，并发度降低；
• 数据持久化的问题：
  • 因为数据存在内存 RAM 中，断电会导致数据丢失；
  • 如果在 M-S 架构下，S 重启，主库的操作会导致 S 出错；
  • 如果是 M-M 架构下，一个 M 重启，binlog 中会记录 删除该表操作，导致另一台主库同步该信息；

什么时候可以使用内存表？

在 join 语句优化的时候可以使用内存表；

例如，如下语句，对两张表进行 join 操作，需要对 t1 表进行全表扫描，然后筛选出 a 的值去 t2 表中搜索；

select * from  t1 left join t2 on (t1.a = t2.a);

这时候可以利用临时表，如下：

create temporary table temp_t(id int primary key, a int, b int, index (b))engine=memory;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

• 这时候使用临时表可以在内存中操作，不需要磁盘 IO；
• 可以通过 hash 索引检索 b 的值，速度比 B+ 树快；

12. 自增 ID 用完了会怎么样？

• 对于表定义的自增 ID ：
  • 用完了之后会取得相同的 ID，然后在插入的时候抛出异常；
• 对于系统自增的 row_id
  +对于 InnoDB 引擎，如果一个表没有制定主键，那么， 那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id；
  所以范围为 0到2^48-1，当达到上限之后会又变成 0，然后重新循环，所以新插入的记录会覆盖之前的记录；
• 对于 Xid
  • Xid 是redo log 和 binlog 的共同字段，来标识一个事务；
  • Xid 由MySQL 内部维护的一个全局变量 global_query_id 来生成，基于内存生成所以在 MySQL 重启的时候会消失，但是重启之后也会生成新的 binlog 所以只需要保证在一个 binlog 中没有重复的 Xid 即可；
• 对于 InnoDB 的 trx_id ：
  • InnoDB 的 trx_id 也就是 MVCC 实现使用的事务 ID；
  • 该 ID 是会持久化的，所以当超过 ID 最大值之后，就会重新生成 0 的 trx_id，而此时，因为 MVCC 的实现，会导致该数据可见造成脏读（我认为是幻读）；
• 对于 thread_id
  • 对于 MySQL 实例，每新建一个连接，就会生成一个对应的 thread_id，通过内存中一个变量来进行递增；
  • 会将所有 thread_id 保存在数组中，当达到上限，就会一直循环寻找已经被删除的 thread_id 也就是释放连接的现场，复用 thread_id；