- 一、基础篇
- 1. 基础架构
- 2. 日志系统
- 3. 事务隔离
- 4. 索引
- 5. 锁
- 二、实践篇
- 1. 选择普通索引还是唯一索引？
- 2. MySQL 为什么有时候会选错索引？
- 3. 怎么给长字符串加索引？
- 4. 为什么我的 MySQL 抖了一下？
- 5. 为什么表数据删掉一半，表文件大小不变？
- 6. count(*) 执行很慢该怎么办？
- 7. order by 是怎么工作的？
- 8. MySQL是怎么保证数据不丢的？
- 9. 备库什么时候会延迟？
- 8. 误删数据了怎么办？
- 9. 如何使用 join？
- 10. 什么时候需要使用临时表？
- 11. 什么时候需要使用 Memory 存储引擎？
- 12. 自增 ID 用完了会怎么样？
MySQL 基本架构
- 连接器
- 执行MySQL 的连接操作；
- 也可分为长连接和短连接，长连接占用系统资源，短连接频繁建立连接耗时。
- 查询缓存
- 可能之前查询过，则直接在缓存中以 key value 存储；
- 不建议使用，因为缓存命中率低，一旦对应表更新就会刷新缓存；
- 8.0 之后移除该功能。
- 分析器
- 如果没有命中缓存，就进入分析器，进行语法、词法的分析；
- 也就是看 SQL 语句有没有写错；
- 优化器
- 对于索引的选择优化；
- 执行器
- 执行前检查是否有对该表的操作权限；
- 有的话进行执行。
-
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 状态，如果其中有一个环节出错就回滚。
读已提交和可重复读的使用场景
- 读已提交
- 大部分业务的选择；
- 提高并发度，可以读到最新数据；
- 可重复读
- 例如月初，要对上个月的账单进行清算；
- 但是你不想在清算的过程中被新加入的转账操作影响；
- 就可以采用可重复读；
避免使用长事务
- 可以及时回收 undo log ，减少空间占用（undo log 会在事务提交，以及没有事务版本号小于undo log 中最大版本号的时候提交）；
- 减少对于锁资源的侵占，提高并发性；
主键索引和非主键索引查询的区别：
-
主键索引可以直接在叶子结点中查询出整个数据行；
-
而辅助索引则在叶子结点取得对应的主键，再通过主键构造的 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 的直接排除，减少回表查询的数据量；
什么是全局锁？有什么应用场景？
- 通过 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 隔离级别，对于非索引的查询也是锁住表，但是在查出数据之后，如果条件不符合则会即时解锁；
首先，该问题讨论在业务保证数据库不需要进行唯一性检查的情况下。
这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。
对于查询：都是通过索引找到对应的数据；
对于更新和插入操作：更新插入操作就是两者性能差距的主要方面：
- 如果对应数据页在 MySQL 内存中，则差距不大，找到对应位置，唯一性索引需要进行唯一判断，但是都是内存操作，两者差距微乎其微；
- 如果对应数据页不在内存中，对于唯一索引，要将对应的数据页调入内存，判断是否有唯一性冲突，然后插入；而对于普通索引，则只需要直接插入到 change buffer 中，减少了很多索引页数据页的IO操作。
2. MySQL 为什么有时候会选错索引？change buffer 是内存中的一个缓冲区，当使用非主键索引进行插入或者更新操作的时候，就会将操作写入到 change buffer 中来减少对于磁盘的随机 IO，等合适的时机再同步该数据，例如，对应数据页被读入内存，或者主线程的循环操作，或者对应数据页快没有空间的时候；
具体可看 https://blog.csdn.net/weixin_48922154/article/details/119152805
优化器是否选择以及如何选择索引，主要考虑什么？
- 扫描的行数；
- 是否使用临时表；
- 是否使用排序；
优化器如何判断扫描的行数？
-
MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。
-
这个统计信息就是索引的“区分度”。显然，索引上不同的值越多，索引的区分度就越好。索引上不同的值的个数，称之为“基数”（cardinality）。基数越大，索引的区分度越好。可以使用 show index 查看；
-
优化器得到基数的方式是采样分析，即，选取一些数据页得到不同值的个数，再乘所有的数据页，而不需要对全表进行一个扫描，这个值也是动态的，当修改的行数超过一个阈值，就会重新计算。
-
优化器再根据基数来预估具体需要扫描多少行才能得到对应的数据，再和回表查询的次数等进行一个综合判断，来选择索引；
如果优化器选择错索引怎么办？
- 采用 force index 强行选择一个索引：没有该语句时，会通过分析器分析到所有可用使用的索引，然后通过优化器进行选择，有该语句，则直接使用该索引；
- 修改语句，引导 MySQL 使用我们期望的索引；
- 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
- analyze table t 命令，可以用来重新统计索引信息。
例如，要给邮箱加上索引来优化查询性能，可以使用前缀索引，来减小索引大小，使得每个索引页增加索引个数，提高查询性能，但是，前缀索引带来的影响是，使得覆盖索引的优化失效，以及，可能会增加回表查询的次数，所以要选择区分度高的前缀。
但是对于例如身份证号，则前缀的区分度很低，前面是用来表示地区的编号，会增加大量回表查询，所以可以采用两种优化方式：
- 使用倒叙存储 + 前缀索引
- 因为对于身份证来说，后几位的辨识度高，重复概率小；
- 所以可以采用逆序的方式存储，并且加上前缀索引，查询的时候使用如下语句：
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 参数表示是否刷新邻接页，也就是刷新脏页的时候是否将附近的脏页一起刷新；
这个问题时因为， MySQL InnoDB 的删除操作，是将该数据页或者行记录标志为可复用，而不是物理上的删除，也就是例如 删除 ID 等于 4 的字段，这时候下一条数据可以复用该数据行的空间，但是记录的复用，只限于符合范围条件的数据。例如插入一条 ID 是 8 的记录，就不能占用该空间，所以删除操作可能并不能回收表空间。
不仅是删除，插入操作或者更新操作也会发生这种现象，对于插入，如果不是通过主键的插入，也就是离散的插入，可能造成页的分裂而导致空闲的记录行；对于更新操作，则看作是删除一条记录而插入一条新的记录，所以也可能发生这种情况。
所以，经过大量增删改的表，都是可能是存在空洞的，要想去除这些空洞，重建表是一个选择。
可以通过该命令对表进行一个重建，5.6之前是同步阻塞的重建过程，数据库无法对外界提供服务，5.6 之后，就采用类似 Redis AOF 重写的操作，先建立一个临时表，扫描原表中所有的主键组织的数据页，这时，数据库同时对外提供服务，将执行操作写入一个日志文件，临时表重建完成后再同步日志文件内容，再原子替换之前的数据文件。
alter table A engine=InnoDB6. 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(字段) 对于 order by 的排序，MySQL 内部会将对应的行数据的全字段，取出放在 sort_buffer 中进行排序，如果大小足够则在内存中排序，如果不够，则需要用到磁盘的临时文件辅助排序。 临时文件排序的方式是，分成多个文件，每个文件放在 sort_buffer 中排序，然后多个文件进行归并排序，所以 sort_buffer 值越小，则需要拆分越多临时文件。 平常的排序，InnoDB 会把需要的字段全部取出进行排序，如果字段很多很长，则 sort_buffer 中存储的数据行就很少，则需要更多的临时文件，效率较低且占用资源； 这时可以设置如下参数：该参数意为如果数据行长度大于该值，则只取出对应排序的行和ID，排序之后，再通过ID去查询对应的数据； order by 很消耗时间，怎么样可以避免排序？ 当然就是加索引啦！比如查询语句如下： 如果查出 a 的对应值之后，b就是有序的，那不就不需要再进行排序了吗，所以建立联合索引（a，b）可以很好解决这个问题。 binlog 的写入机制是什么样的？ 在执行事务的时候，会先把日志写到 binlog cache 中，如果超出缓冲大小，则会暂存到临时文件，无论一个事务有多大，都会在事务提交之后，将 binlog cache 一次性写入到 binlog 中，然后清空 binlog cache 内容。 对应两个方法： redo log 是如何写入的？ 对 redo log 的写入也是先写入到 redo log buffer ，再通过 write 写入到 page cache 再通过 fsync 同步到磁盘。 大致有四种情况会使 redo log 同步到磁盘： 组提交（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 首部的序号） MySQL 要保证高可用，最终一致性是远远不够的，但是很多时候，主从会发生一段时间的数据不一致问题。 在正常的网络状况中，一般延迟来自于备库接收完 binlog 和执行完这个事务之间的时间差。 主要原因有： 对于以上情况，延迟一般在分钟级别，因为都是由于性能的原因造成，一段时间后，从库一般都能追上来。 但是如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏。 5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。 之后，为了采用多线程复制操作，引入 coordinator 也就是原来的 sql_thread，负责读取中转日志和分发事务，真正更新日志的变成了worker线程。另外，coordinator 在分发事务的时候必须满足，不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。以及同一个事务不能被拆开。 对于数据行的误删 对于数据库或者表的误删 什么是驱动表，如何选择驱动表？ 在 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。 所以在选择驱动表的时候，应该让小表去驱动大表，小表进行全表扫描，取出数据，再去大表中通过索引查询出数据封装到结果级。所以需要有一个前提， 那就是可以使用上大表的索引进行查询。 什么是小表？ 小表不能单独只看两张表的数据量来判定小表，而应该是经过对应的查询过滤条件等信息筛选出来的小表作为驱动表！ 到底可不可以使用 join ？ 查询时，如何区分驱动表和被驱动表？ join 如何优化？ 什么是临时表？ 例如如下SQL 临时表有什么应用场景？ 什么时候会使用内部临时表？ 内部临时表就相当于一个内部的缓存，暂存对应的数据行信息 group by 可以如何优化？ InnoDB 存储引擎和 Memory 存储引擎有什么区别？ 为什么在生产中不建议使用内存表？ 什么时候可以使用内存表？ 在 join 语句优化的时候可以使用内存表； 例如，如下语句，对两张表进行 join 操作，需要对 t1 表进行全表扫描，然后筛选出 a 的值去 t2 表中搜索； 这时候可以利用临时表，如下：SET max_length_for_sort_data = 16;
SELECt * FROM t WHERe a = 1 ORDER BY b ASC;
9. 如何使用 join？
10. 什么时候需要使用临时表？
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);
select v from temp_ht order by t_modified desc limit 100;
11. 什么时候需要使用 Memory 存储引擎？
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);
12. 自增 ID 用完了会怎么样？
+对于 InnoDB 引擎，如果一个表没有制定主键，那么， 那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id；
所以范围为 0到2^48-1，当达到上限之后会又变成 0，然后重新循环，所以新插入的记录会覆盖之前的记录；
