Mysql 八股汇总

字数: 0 字 时长: 0 分钟

第一部分 | 索引

1. 什么是索引下推？

1. 它是在5.6及之后版本才有的
2. 索引下推是指在server层将索引和过滤条件一块传给存储引擎层，存储引擎层通过索引找到数据后使用过滤条件过滤掉不符合条件的数据，减少了返回的数据量，也就减少了回表次数

2. 索引的最左前缀原则是什么？

1. 在使用联合索引时，必须从最左边的索引开始依次往后，否则就用不上联合索引，这就是最左匹配原则
2. 比如联合索引abc，a/ab/abc是生效的，而b/c/ac不生效，但对于ac，在Mysql5.6版本引入了索引下推，即将部分查询条件一块传到存储引擎层，由存储引擎层进行过滤，减少返回的数据量
3. 与顺序无关，比如acb等同于abc
4. 为什么会这样呢？因为Mysql的底层存储是先对a进行排序，在a相等的情况下再根据b排，以此类推。所以说如果前面缺少一个或者前面的使用不带等号的范围查询，得到的数据是无序的，也就无法使用后续的索引了，这也是索引失效场景之一
5. 其中Mysql8有一个特性，就是对于联合索引ab，查询条件只有b，在a的基数很小的情况下，Mysql会自动拼上a，从而使用联合索引。比如a的值只有1和2，在查询的时候会直接使用a=1 and b=?和a=2 and b=?进行查询（Using Index for skip scan）

3. 索引失效场景有哪些？

1. 使用联合索引，但未遵循最左前缀原则
2. 索引使用了运算
3. 索引使用了函数
4. 对于联合索引，范围查询右边的索引会失效
5. 左模糊匹配
6. or左右两边有一边无索引
7. 字符串类型不加引号（隐式类型转换）
8. order by后面不是主键或覆盖索引，Mysql可能选择全表扫描再排序，不走索引

4. 索引类型有哪些?

1. 按数据结构：B+树索引、哈希索引、倒排索引、R-树索引
2. 按索引性质：主键索引、唯一索引、普通索引、联合索引、全文索引、空间索引
3. 按物理存储：聚簇索引、非聚簇索引

5. 聚簇索引与非聚簇索引的区别？

1. 聚簇索引：一般是表的主键，叶子结点存储的是完整的行数据，一张表只能有一个
2. 非聚簇索引：叶子结点存储的是索引列和主键，一张表可以有多个，若查询的数据不在该索引列中，需要根据ID进行回表查询

6. 建索引时应注意什么?

1. 不要在有大量重复数据的字段上建索引，比如性别字段（也有特例，比如定时任务成功状态远大于失败状态时可以考虑建索引）
2. 不要在长类型（text，longtext）字段上建索引，因为加载到内存上花的时间很长，其次数据量大，会把其他字段数据踢出内存，下次还要重新加载
3. 不要在频繁更新的字段上建索引，因为会减慢更新的效率
4. 如果查询sql中一个条件用的很频繁，那么可以考虑建索引，比如where后面的，如果是多条件，可建立联合索引，注意最左前缀原则就行
5. order by/group by/distinct后面字段可以建立索引，提高排序，分组，去重的效率
6. 最后明确一点，索引并不是越多越好，因为索引本身也会占据空间，并且增删改的时候除了对主键索引更新外，还会对其他有关索引进行更新，索引越多，花费时间越多

7. Mysql为什么使用B+树作为索引结构？

1. 从性能上来讲，B+树是一种自平衡的二叉树，2000w条数据大约才3层，增删查的时间复杂度为O(logn)，具有极短的响应时间，且查询时间更加均匀
2. 从数据存储上来讲，B+树的非叶子结点只存储索引列和指针，使得每个非叶子节点可存储的数据量很大，相应的树高增长也就不会很快，内存中可存放的数据个数很多，无需频繁的磁盘IO
3. 从查询方式上来讲，B+树相邻叶子结点之间使用双向链表连接，定位到起点后，顺序遍历即可，顺序IO效率远大于随机IO，范围查询效率大大增加

第二部分 | 事务

1. Mysql的事务隔离级别有哪些？

1. 读未提交：事务可以读取到另一个事物未提交的数据，会导致脏读、不可重复读、幻读
2. 读已提交：只能读取到已提交的数据，解决了脏读，但依然会出现不可重复读和幻读
3. 可重复读：Mysql默认的隔离级别，同一事务中多次读取同一数据的结果是一致的，解决了脏读、不可重复读，但会存在幻读
4. 串行化：最高的隔离级别，串行化操作，可避免所有的并发问题，但开销很大

2. 脏读、不可重复读、幻读指的是什么？

1. 脏读：一个事物读取到另一个事务未提交的数据，如果另一个未提交的数据最终回滚，那么这条数据就是脏的
2. 不可重复读：同一事务中多次读取同一数据，得到的结果不一致（针对内容）
3. 幻读：同一事务中多次执行相同的查询操作，前后得到的数据集合不一致（针对数量）

3. 事务的ACID指的是什么？（四大特性）

1. ACID分别对应原子性、一致性、隔离性、持久性
2. 原子性：一个事务内的操作要么全部成功，要么全部失败
3. 一致性：事务执行前后，必须保证数据是合法的（满足业务规则和完整性约束），在事务执行期间可以处于中间态
4. 隔离性：每个事务之间相互隔离，互不干扰，根据事务隔离级别的不同，可能会出现脏读、不可重复读、幻读的问题
5. 持久性：事务一但提交，数据变更一定是永久的，不会发生重启导致的数据丢失

4. Mysql是如何实现事务的？

1. 对于事务的原子性，通过undo log实现，undo log用于记录当前操作的反操作，以便于事务失败后进行回滚
2. 对于事务的隔离性，通过锁+mvcc实现
3. 对于事务的持久性，通过redo log实现，在服务宕机/重启后通过重放redo log进行数据的恢复
4. 最后对于事务的一致性，是通过原子性、隔离性、持久性共同保证的，以此达到一致性的目的

5. Mysql事务的两阶段提交是什么？

1. 分为准备阶段和提交阶段，用于保证redolog和binlog之间的一致性
2. 准备阶段：事务提交时，Mysql的InnoDB引擎会先写入redolog中，并将状态标记为prepare，此时redolog是预提交状态，还未真正提交
3. 提交阶段：当redolog的状态变为prepare后，Mysql的server层会将数据写入binlog，写入成功后会通知InnoDB，将redolog的状态标记为commit，redolog进行提交，至此二阶段提交结束
4. 好处：当redolog写完但还未提交（binlog可能写入，也可能没写入），重启后InnoDB会检查状态为parpare的日志，拿到他的XID，去binlog中找，如果找到，说明binlog已经写入，直接提交，否则回滚
5. 那么问题来了，为什么不是先写binlog后写redolog做两阶段提交呢？因为binlog没有事务状态标记，也不参与崩溃后恢复；而redolog可以标记事务状态并作为恢复依据

6. 长事务可能会导致哪些问题？

1. 长事务会长期持有行锁/间隙锁，导致其他事务阻塞，线程堆积，连接池耗尽，从而会引发应用层面的雪崩或不可用
2. 死锁风险大大增加，事务执行时间越长，和其他事务产生循环等待的概率就越大
3. undolog膨胀，多个长事务一直不提交，这个版本链就不能被清理，导致文件体积变大
4. 主从延迟，长事务在从库执行时，可能会在执行期间进行数据的查询，那么这期间数据是不一致的
5. 回滚代价大，长事务执行了好长时间，最终被回滚了，就白干了，而且回滚的时间跟事务执行时间差不多，也很大

第三部分 | 日志

1. Mysql的日志类型有哪些，以及他们之间有什么区别？

1. Mysql的日志类型主要有3种，分别是binlog，redolog，undolog，下面我将从日志类型、主要用途、删除策略来回答
2. binlog，逻辑日志；记录的是二进制日志，包括所有的DDL、DML语句，用于数据恢复、主从复制，它可以跨平台使用；按文件保留，可配置定期删除
3. redolog，物理日志；记录页号/页偏移量/修改长度/新值，用于Mysql发生崩溃时进行数据恢复；覆盖写
4. undolog，逻辑日志；记录表空间ID/行的主键/被修改字段的旧值/指向前一个版本的指针，用于事务失败后的回滚和mvcc一致性读；事务提交后延迟删除
5. 其中binlog和redolog都可以进行数据恢复，但他们的侧重点不同，binlog是server层的逻辑日志，侧重于某个时间点的恢复；而redolog是InnoDB存储引擎层的物理日志，侧重点是保证崩溃后已提交数据的不丢失，两者通过两阶段提交保证一致性，彼此不可替代

第四部分 | 锁

1. 介绍一下MVCC？

1. MVCC是多版本并发控制，在不加锁的情况下实现一致性读，解决读写冲突问题

2. 里面有个版本链的概念，数据库中的每条记录还有两个隐藏字段，trx_id和roll_pointer，分别表示事务id和指向哪个旧版本，对于新增、修改和删除，会在undolog中记录操作之前的数据，使更新完成的这条数据的roll_pointer指向这条日志，以此类推，就形成了一条链，undolog会在没有任何ReadView引用时进行删除

3. 查询数据时，会生成ReadView，里面有四个关键字段：

   • creator_trx_id（当前事务id，只读事务为0）
   • m_ids（已启动还未提交的事务集合）
   • min_trx_id（m_ids集合中最小的值）
   • max_trx_id（生成ReadView时，下一个即将被分配的事务id）

   判断规则如下：

   • trx_id=creator_id，说明是自己改的，可见
   • trx_id<min_trx_id，说明改这条数据的事务早就提交了，可见
   • trx_id>=max_trx_id，说明改这条数据的事务是在生成ReadView之后进行的，不可见
   • min_trx_id<=trx_id<max_trx_id，看trx_id是不是在m_ids里，如果在说明事务还未提交，不可见；如果不可见就顺着版本连往回找，直到找到一个可见的版本

4. 读已提交（RC）和可重复读（RR）的区别是，前者在一个事务中每次查询时都会生成ReadView，后者是在初次查询时生成ReadView，后面公用这一个。这也就说明了为什么读已提交会产生不可重复读的问题，而可重复读将这个问题解决了，因为可重复读读的是同一个ReadView，数据肯定是一致的

5. 还有两个概念，快照读和当前读。快照读走MVCC，读的是历史快照，不加锁；当前读是读取最新版本并且加锁，锁住记录和间隙，避免其他事务的影响。这也说明了为什么可重复读不能完全解决幻读问题，因为快照读和当前读混用的时候，就会出现幻读

6. 最后一点，MVCC并不能解决写写冲突，还是需要加锁

2. 二级索引有没有MVCC快照？

1. 没有，二级索引不存在trx_id和roll_pointer，无法进行判断，当需要某个历史版本的时候，会查看当前页的page_max_trx_id（最后修改这个索引页的最大id），如果他比当前事务启动时的最小活跃事务id还小并且未被标记删除，说明是可见的，直接用二级索引的数据即可；否则需要回表查聚簇索引
2. 聚簇索引更新时是直接覆盖旧值，使用roll_pointer指向undolog的版本链；而非聚簇索引则是将旧值打上删除的标记并插入新数据，等待purge线程来清理

3. Mysql中有哪些锁类型？

1. 共享锁（S）：允许多个事务同时读取同一资源，共享锁持有期间不允许加排他锁，共享锁之间不冲突
2. 排他锁（X）：只有获取到排他锁的事务才能对该资源进行读写
3. 行级锁：对行记录的索引进行加锁，不存在索引时可能会锁全表，可以同时持有共享锁，但排他锁互斥
4. 表级锁：对整张表进行加锁，在MyISAM中主要使用表锁，但在InnoDB中几乎不用，用表锁的场景只有DDL时
5. 间隙锁：锁住两个记录之间的间隙，在RR下，防止其他事务在这个间隙中插入数据，防止幻读，间隙锁之间不冲突
6. 临键锁：记录锁+间隙锁，锁住对应的行数据和其前面的间隙
7. 意向锁：分为意向共享锁（IS）和意向排他锁（IX），用于快速判断表中是否存在行锁，避免逐行检查。当需要对某条记录上S锁的时候，先在表上加个IS锁，代表此时表内有S锁；同样对记录上X锁的时候，也加个IX锁。当需要对表上S锁时，会检查是否存在IX锁，存在就不能上锁；同样对表加X锁时，看是否有IS和IX锁，存在也不能上锁
8. 插入意向锁：间隙锁锁住间隙后，其他事务要想在他们的间隙进行插入，必须等到间隙锁进行释放，在此期间，会对该事物加上插入意向锁，用于表示我正在等着你释放，如果有多个事务等待同一间隙，谁先抢到谁先来，如果一个事务达到指定时间还未抢到资格，则返回失败，防止饥饿现象
9. 自增锁：对表的主键进行自增操作，保证自增值的唯一性，在插入一条数据时，对表加自增锁，插入结束后，再释放这个锁（5.1.22版本之后，又加了个互斥量，性能高于自增锁，插入数据时，只要获得了递增值，就可以释放锁）【Mysql有个字段可以配置这些策略，默认值是1，即当已知插入数量时，用互斥量，不知道具体插入数量时，用自增锁；0-只用自增锁；2-只用互斥量】
10. 元数据锁：防止DDL和DML的冲突，当执行DDL时，对表加元数据写锁；执行DML时，加元数据读锁；还有一个作用是保护元数据的一致性，确保在执行DDL时，其他事务不能同时修改表结构
11. 其中表级锁、意向锁、自增锁、元数据锁是表锁；行级锁、间隙锁、临键锁、记录锁属于行锁；共享锁和排他锁既能做行锁也能做表锁

4. 说一说Mysql的悲观锁和乐观锁？

1. 悲观锁：假设一定会冲突，会在读取时进行加锁，适用于写多读少的场景，通过select …… for update或select …… lock in share mode实现
2. 乐观锁：不对数据进行加锁，而是通过版本号机制判断数据是否被别人改过，适用于读多写少的场景
3. 需要注意的是，如果使用悲观锁，select …… for update的时候，如果没走索引，会锁全表的所有行，因此必须要走索引
4. 乐观锁的version字段可以用int类型，因为int类型最大是21亿，即使每秒修改1000次，也能用60年
5. 在分布式系统下，如果是主从读写分离，那么可能出现版本号更新不及时的情况，这种情况下就要考虑使用redis，可以使用setnx，更新前检查是不是自己设置的值即可，更新完删除，其次redisson封装了RLock可以直接使用

5. 避免死锁的方法有哪些？

1. 固定访问顺序，比如规定先访问A表再访问B表，不要乱序
2. 避免大事务，可将大事务分为几个小事务，避免占用太多锁的时间
3. 建立索引，确保where字段走索引，如果字段没有索引，会对表中每一行依次进行加锁，变为全表锁
4. 不要再事务中进行耗时操作，比如大规模计算，减少锁的持有时间，尽快提交事务

6. 发生死锁的解决办法？

1. mysql InnoDB自带死锁检测，当发现死锁时，通常选择持有资源最小的那个事务回滚
2. 会有锁超时机制，当达到阈值时，自动释放锁回滚
3. 手动终止发生死锁的线程，使用 show engine innodb status 查看死锁信息，然后使用 kill <thread_id> 命令终止线程即可，这是紧急处理手段，生产环境尽量依赖于自动检测机制

第五部分 | 调优

1. 如何使用explain语句进行查询分析？

1. 主要分析几个字段，分别是type、key、rows、extra

2. type：访问类型，查询效率 const>eq_ref>ref>range>index>all （下面所指的结果是存储引擎层返回给Server层的，不是返回给应用的）

   • const：主键或唯一索引等值查询，结果最多一行 SELECT * FROM user WHERE id = 10;
   • eq_ref：连接查询里通过主键或唯一索引关联，外表每给一个值，InnoDB就返回一行 SELECT * FROM order o JOIN user u ON o.user_id = u.id;
   • ref：非唯一索引，可能返回多行 SELECT * FROM user WHERE city = 'beijing';
   • range：索引范围扫描 SELECT * FROM orders WHERE create_time BETWEEN t1 AND t2;
   • index：扫描整颗索引树，比all好点 SELECT id FROM user;
   • all：全表扫描 SELECT * FROM user;

3. key：实际用到的索引，最终被优化器选中的索引

4. rows：预估要扫描的行数，越少越好

5. extra：额外信息

   • using index：走覆盖索引，完全不用回表
   • using index condition：用了索引下推，减少了回表次数
   • using where：引擎层返回的数据，server层仍需进行where判断（不管走不走索引下推，都要再校验一遍）
   • using filesort：无法利用索引进行排序，要额外排序
   • using temporary：生成了临时结果集
   • using index for skip scan：补齐左列，使用上联合索引

2. explain都有哪些字段，各自的作用是什么？

1. type：上述提到

2. key：上述提到

3. rows：上述提到

4. extra：上述提到

5. select_type：查询类型

   • simple：简单查询，没有子查询，也没有union
   • primary：主查询，最外层查询
   • subquery：子查询，select或where里的子查询
   • derived：from里的子查询
   • union：union里第二个及之后的select
   • union result：union合并后的结果

6. table：所要查询的表

7. id：层级+优先级，同一id表示同一查询同一层级，值越大越优先执行

8. partitions：实际访问了哪些分区

9. possible_keys：可能用到的索引

10. key_len：用到的索引字节长度

11. ref：索引列与什么值进行比较

    • const：常量，如a=1
    • func：函数，如a=NOW()
    • db.table.col：另一个表中的列
    • NULL：没有用到索引

12. filtered：根据查询条件过滤掉行的百分百，越大越好

3. 走了索引但还是很慢，可能是什么原因？

1. 索引字段选择性低，比如性别字段，查询时几乎还要扫描半张表
2. 索引树太大，内存装不下需要进行频繁的磁盘IO
3. 回表次数太多，虽然走了二级索引，但如果查询出的数据很多，回表也占据大量时间
4. 返回数据量大，网络传输耗时，可能本身sql很快，是结果集过大拖慢的速度
5. 方案1：查看rows值的大小和extra有没有filesort或temporary
6. 方案2：对于选择性，可以使用count(distinct column)/count(*)【简单来说就是 有几个值/总行数】看它的值，如果低于0.1就不推荐作为索引

4. 如何进行sql调优？

1. 可以分为3步，首先查看慢查询日志定位慢sql；然后使用explain分析执行计划，主要关注type、key、rows、extra这几个字段，看是否用到了索引，索引是否失效等；最后定向进行调优，可以从以下几个方面调优
2. 第一，覆盖索引尽量包括要查询的数据，避免回表，还要保证覆盖索引不会失效
3. 第二，关注索引的失效场景，确保索引有效（比如最左前缀原则，隐式类型转换，在索引列上使用了函数/运算，左模糊匹配等，范围查询，or左右两边有一侧无索引等）
4. 第三，尽量避免 select * ,仅查找必须字段，减少网络传输
5. 第四，连表查询时检查字段字符集是否一致，比如utf8和utf8mb4字段join时会导致隐式转换，索引就没用了
6. 第五，避免深分页，如确实需要，可以考虑使用游标
7. 第六，单表超过2000w就要考虑分库分表提高读写性能

第六部分 | 缓冲区

1. Buffer Pool的作用？

1. 主要用来缓存数据页和索引页，查询时先看buffer pool中有没有，有的话直接从内存返回，否则去磁盘加载；更新时会将数据写入buffer pool同时生成redolog，等待后台线程定期刷盘/数据库正常关闭/checkpoint 推进/buffer pool空间不足

2. Change Buffer是什么，他有什么作用？

1. 它是InnoDB存储引擎中的一块内存区域，位于Buffer Pool中，用来记录二级索引的数据变更，当进行增删改时，如果涉及到的索引页不在内存中，不会从磁盘读到内存，而是将变更的数据记录到redolog和change buffer里，后续当这个索引列因查询被读进内存，或者后台线程做flush时（把内存中的数据刷到磁盘），把积攒的变更一次性合并进去
2. 好处：这样做提升了性能，不需要等到从磁盘加载数据页而是直接写到change buffer中，减少了IO阻塞，返回更快；其次批量变更相比于每次变更不需要频繁的磁盘读取，效率更高
3. 但需要注意的是，change buffer只对普通的二级索引生效，对于主键索引，索引页等同于数据页，修改时必须立即生效；对于唯一索引，要判断是否是唯一值，肯定要查数据，所以索引页一定会加载到内存中，可以直接更改；对于全文索引和空间索引，结构特殊，也不能延时更新
4. 对于数据丢失问题，每次写change buffer时也会写redolog，而redolog可以在数据崩溃时进行恢复，不需要担心

3. Doublewrite Buffer的作用？

1. 位于InnoDB的系统表空间ibdata1中
2. 解决数据页损坏问题，一个页是16kb，而操作系统IO单位是4kb，有可能在刷盘时突然断电，导致这个页的数据只传输了一部分，而redolog只能解决数据不丢失，对于数据损坏就无能为力了。而doublewrite就可以很好的解决这个问题。刷盘时使用顺序IO写入doublewrite buffer，再使用随机IO写入真正的数据文件

4. Log Buffer的作用？

1. 与Buffer Pool并列
2. 用来缓存redolog，将多次小写入合并成一次大写入，减少磁盘IO。它有三种刷盘策略，0-提交时不同步刷盘，靠后台线程每秒刷；1-提交时写入并同步刷盘；2-提交时写入操作系统缓存，不同步刷盘

第七部分 | 应用场景

1. 如何避免单点故障？

1. 使用主从集群（半同步）+读写分离：主库写，从库读，如果主库挂了将从库升为主节点，最常用的手段
2. 主备架构：备库平时不暴露身份，只有主库挂了后才使用替代主库，但资源利用率极低，好处就是实现简单
3. 主主架构：两台机器都能读写数据，不推荐，容易出现数据冲突问题
4. MRG集群（MySQL Group Replication）：Mysql官方提供，由多个节点组为一个集群，自动故障转移，保证强一致性
5. 第三方工具：如MHA，由Manager节点和Node节点组成，最强大功能在于主库挂了，切换到从库后使用ssh登录主库把binlog拷贝出来，应用到新主库上，最大限度减少数据丢失
6. 最重要的还是定期备份！可以定时全量备份，基于binlog做增量备份，异地备份等

2. 读写分离场景下如何处理主从延迟？

1. 首先明确一点，读写分离场景下无法彻底消除主从延迟，只能通过主库直读、复制优化等方面解决
2. 对一致性要求强的业务，强制走主库
3. 写操作结束后记录时间戳，短时间内的请求走主库，过了延迟窗口再走从库
4. 半同步复制，至少等一个从库返回确认收到binlog时才返回
5. 二次查询兜底，从库查不到再向主库查询一遍，但会有恶意攻击的风险
6. 另外，值得注意的是，如果事务里有读有写，大多数框架的做法是直接走主库

3. Mysql的主从同步机制是怎样的？

1. 主从复制是基于binlog的，他有三种类型，分别是statement、row、mixed。sratement是记录sql语句，row则是记录更新前后数据的变化，mixed是两种的混合策略，由Mysql自行判断选择；虽然statement体积比row小，但生产环境建议用row，保证数据一致性
2. 首先从库发起复制连接后，会在主库开启一个dump线程，根据从库请求的binlog文件名和position，将数据持续发送给从库
3. 从库通过IO线程接收主库的数据，把收到的binlog记录到relaylog中继日志中
4. 最后从库的sql线程读relaylog，逐条执行
5. 在Mysql5.6版本后支持并行复制，在8版本后引入WriteSet，支持更精细的行级别并行复制

4. 什么是级联复制？

1. 主库只同步给一级从库，二级从库再从一级从库获取数据，这样主库的dump线程压力就小了，适合于从库特别多的场景。比如多机房容灾，每个机房挂一个一级从库即可，机房内再挂多个二级从库，但这样延迟就叠加了

5. 什么是分库分表，它有哪些类型？

1. 分库分表就是将一张表中的数据拆分到多张表中，将一个库的数据拆分为多个库，分担读写压力，提高数据库性能
2. 分库分表的策略有两大类，水平拆分和垂直拆分，下面我分别说一下这两种模式的特点
3. 顾名思义，水平分表就是把表横着切一刀，按行划分数据，每张表的字段都相同；水平分库也相同，多库同结构，分担读写压力
4. 垂直分表就是竖着切一刀，按列划分数据，把一张表的字段分为多个表；垂直分库亦是如此，按服务类型进行拆分
5. 分表解决的是单表数据量过大导致的查询慢的问题；分库则是解决的是单机性能瓶颈问题
6. 常用的分库分表的中间件有：ShardingSphere、MyCat等，也可以用第三方封装的框架

6. 分库分表应该考虑哪些问题？

1. 主键ID选取

   • 分库分表后一定不能用自增ID了，会冲突，可以选择雪花算法，Leaf，UUID这类分布式ID，但注意UUID是无序的，索引效率差，且占用字节数多，尽量不要选UUID

2. 事务一致性

   • 使用分布式层面的两阶段提交，Mysql的XA事务就是这个，准备阶段先锁资源，提交阶段才真正写入（预提交+全体投票+统一决策），但性能较差
   • TCC（Try、Confirm、Cancel）模式，通过业务代码自己实现，但开发成本高
   • 本地消息表，一个事务成功之后发送消息到另一个库执行，搭配重试机制保证最终一致性

3. 跨库join失效

   • 代码层面，先查主表拿到关联字段，再批量查从表，最后进行整合
   • 字段冗余，比如将订单表冗余用户昵称，省掉一次查询
   • 按同一个键分片，这样join查询都能落到同一个库
   • 使用ES

4. 排序分页复杂，比如要查1000001-1000010条数据，就要查询所有分片，最后在内存中进行排序，取10条

   • 禁止跳页查询，使用游标
   • ShardingSphere、MyCat这些中间件实现了流式归并，边查询边排序

5. 聚合麻烦

   • 单独维护一张计数表
   • 各分片先count再累加，查询数等于分片数
   • 使用ES

7. 如果让你主导项目的分库分表任务，大致的思路是什么？

1. 分库分表不能盲目，要先评估必要性，如果说数据量较小就没必要分库分表，尝试从索引优化层面解决
2. 首先进行评估，看单表数据量有没有到达2000w，单机qps有没有到达5000，根据数据增长趋势预估未来几年的数据量，如果根本达不到瓶颈，直接优化索引即可
3. 然后进行方案设计，选取哪个字段作为分片的键、路由策略是什么、分片数量是多少。对于分片的键，首选查询量大的字段，比如user_id等；对于路由策略，推荐哈希取模，这样分布更均匀；对于分片数量，要根据增长趋势计算未来的增长量，最好一步到位，避免后续频繁扩容
4. 下面就到了技术选型，一类是客户端分片，有业务侵入但性能好，如ShardingSphere-JDBC；另一类是代理层分片，独立部署服务，业务无侵入但多一层网络开销，如MyCat，ShardingSphere-Proxy，我比较倾向于如ShardingSphere-JDBC
5. 其次进行数据迁移，可以使用canal监听旧库binlog同步数据，但在完成后要进行校验，比如行数对不对，关键字段值对不对，最好能跑一遍业务
6. 最后进行灰度切换，如果是上线业务，必须保证可用性，可以先切读请求到新库，一段时间稳定后再将写切换到新库，期间要维持双写，万一新库有问题可以随时使用旧库

第八部分 | 其他

1. Mysql的存储引擎有哪些，默认使用哪个，MyISAM和InnoDB的区别是什么？

1. InnoDB、MyISAM、Memory、CSV等，mysql5.5之前默认的存储引擎是MyISAM，5.5之后默认的存储引擎是InnoDB
2. InnoDB支持事务、外键、以及行级锁；MyISAM不支持事务、外键、只支持表级锁，并且锁的是整张表
3. 所以说对于读密集型任务，可以使用MyISAM；对于写密集型任务，应该用InnoDB，因为它使用的是行级锁，锁的粒度更细
4. 其次对于要保证事务一致性的场景，必须用InnoDB来保证数据的一致性

2. Mysql中数据排序是如何实现的？

1. Mysql的排序分为两种，内存排序和利用磁盘进行归并排序
2. 首先，如果排序字段命中索引，直接按索引顺序读取返回即可，不需要额外的排序（extra无using filesort）；但要注意order_by的顺序必须一致，不能一个升序一个降序，8.0版本之后可以在建立索引时指定排序方式
3. 如果没命中索引，根据数据量的大小判断是用内存排序还是磁盘归并排序，通过sort_buffer_size判断
4. 对于内存排序，又分为单路和双路排序，单路排序指把所有数据放到sort_buffer中，而双路排序只放排序字段和id，然后进行回表，通过max_length_for_sort_data判断
5. 最后就是磁盘进行归并排序，因为如果数据量太大，sort_buffer放不开，就会分批放在sort_buffer中进行排序，然后把排序的结果放到磁盘，等所有数据都到达磁盘，进行归并排序

3. 一条sql语句的执行过程？

1. 会依次经过连接器->分析器->优化器->执行器
2. 连接器：客户端通过TCP与数据库连接时会进行账号密码校验以及权限校验，整个连接周期内的sql都复用这份权限，如果权限修改了需要刷新或重连
3. 分析器：进行词法分析和语法分析，首先将sql语句拆分为多个token，进行词法解析，看看有没有词法错误；然后进行语法分析，看看sql语句格式是否正确，最后形成一颗抽象语法树（AST）
4. 优化器：基于语法树指定执行计划，包括索引的选取，访问表的顺序，是否使用临时表等，选择最优的方案
5. 执行器：按照执行计划，调用存储引擎层的接口，完成数据的存取；而对于非查询需求；会在存储引擎层写redolog和undolog、在server层写binlog，进行二阶段提交（执行器还会做行级权限校验且一行一行的从存储引擎层拿取数据，因为要判断该用户对特定的列是否有权限，只能在执行器阶段才能得到具体的列）
6. 再补充一点，Mysql8.0之前连接器后还会进行缓存的查询，如果有相同sql的缓存结果就直接返回了，但由于每次变更所有缓存都失效了，并且对于大量读的需求Redis就可以解决，因此没必要再在数据库层面进行缓存，所以8版本之后彻底移除了缓存

4. 讲一下Mysql的B+树查询数据的全过程？

1. 第一阶段：树的垂直查找，通过对页内索引记录的key进行二分查找，逐层向下，直到到达叶子结点
2. 第二阶段：页内查找，叶子结点就是一个数据页，数据页中很多槽，每个槽的指针指向了不同组的最后一个记录，因为不同组是连接成链表的，所以可以通过槽（二分）找到对应的分组，再进行遍历即可快速找到所需数据
3. 总结一下就是根节点->中间节点->叶子结点->页目录二分->组内链表遍历

5. Mysql中int(11)表示什么？

1. 首先明确一点，他并不是表示数值的存储范围，只要是相同数据类型，存储范围都是固定的；这里的11表示显示宽度，配合zreofill属性将左侧补0
2. 在mysql8之后将这个特性弃用了，因为现在有很多格式化工具，根本不需要格式化对齐
3. 但对比varchar(11)，他代表的是真真正正的长度为11的字符，不要搞混
4. 最后拓展一点，建字段时要选择合适的索引类型，以及是否需要符号，无符号比有符号数据范围可是整整提升了一倍

6. inner join/left join/right join的区别？

1. inner join：取交集
2. left join：返回左表所有行，右表如果无数据，填充null
3. right join：返回右表所有行，左表如果无数据，填充null
4. 如果团队规定必须用left join，对于right join只需交换两表位置即可；对于inner join，在where条件里加一个右表字段is not null的条件就行

7. 数据库的三大范式？

1. 1NF：字段必须是原子的，不可再分。比如省市区要拆开
2. 2NF：在1NF的基础上消除部分依赖，针对联合主键的场景，非主键字段必须完全依赖主键，不能只依赖主键的一部分。比如订单ID+商品ID作为联合主键，如果把订单时间也放进来，就是部分依赖，因为订单时间只依赖于订单ID
3. 3NF：在2NF的基础上消除传递依赖，比如订单表里存了用户ID和用户呢称，而用户昵称可以通过用户ID查用户表得到，这就是传递依赖
4. 但在大多数实际场景中，3NF基本用不到，对于订单表来说，除了存用户ID还可以吧用户昵称冗余进来，这样就减少了一次join查询，提高查询效率，属于以空间换时间。其次在分库分表的场景下，跨库join代价太大，更需要冗余

8. Mysql中text类型你了解多少？

1. 存储容量：tinytext-255字节、text-65535字节、mediumtext-约1600w字节、longtext-约42亿字节
2. 存储机制：在行内只存20字节的指针，真正的内容放到独立的溢出页里，这意味着查text字段时要多一次磁盘IO去溢出页找数据（行内放不下才采用这种方法，比如要存储的数据<8kb，可以直接放行内）
3. 是否能建索引：可以，但必须是前缀索引，根本原因是索引条目放不下这么大的数据，只能取一小部分（索引最大3072字节）
4. 模糊匹配：使用fulltext全文索引或者es，但fulltext中文分词效果一般，业界常用es做搜索，mysql只负责存储

9. Mysql中varchar类型你了解多少？

1. varchar()是变长字符，传入多少就占用多大的存储空间。但要注意的是在内存层面，比如排序、分组时会按预定的大小进行分配，因此并不是预设值的越大越好，此外单行的最大字节数是65535，所以varchar最大可分配的范围就是65535，前提是这一行没有其他列。还要提一点，就是varchar存储数据时由实际数据和长度标识组成，<=255时，长度标识占用1字节，大于255占用2字节，这也是为什么一些教程中255出现几率高的原因
2. 修改varchar大小时，如果不跨越255这个节点，通常不会导致锁表，只有在由100扩大到300，长度标识由1字节变为2字节，这就会重新建表，会锁表

10. sql中各关键字的执行顺序是什么？

1. from->join->where->group by->having->select->order by->limit
2. from：确定数据源，加载表数据
3. join：表连接，合并多表数据
4. where：分组前的行过滤，不能有聚合函数
5. group by：按指定列分组
6. having：分组后组过滤，可以用聚合函数
7. select：要返回的列
8. order by：排序
9. limit：返回行数

11. 为什么不推荐使用多表join？

1. 如果查询关联的表过多且每张表的数据量巨大，会占用大量cpu和内存资源，影响其他查询
2. 执行join时，Mysql会选择驱动表和被驱动表，首先遍历驱动表中每一行拿到关联字段，然后去被驱动表中查询；如果关联字段还没有索引，查询的次数=驱动表行数×被驱动表行数，这就导致查询量巨大，这也是为什么对于数据量大的表避免使用join的原因
3. 如果非要使用join，要遵循小表驱动大表的原则，把数据量小的表作为驱动表，数据量大的为被驱动表。因为查询次数的计算公式为 A×2log2B ，小表放在前面能大幅减少查询次数。Mysql优化器也会选择小表作为驱动表，但有时会选错（但一般不会出错），可以使用straight_join强制指定驱动表，这也是Mysql对join查询优化下的一种调优手段
4. 在表的数量不超过3个且每张表数据量在百万级以内且关联字段有索引的情况下可以使用join，但还是推荐在应用层分多次查询然后进行聚合，因为在关联查询的情况下分次查询速度不一定比一次查询慢，并且单表查询结果还可以进行缓存，第二次请求直接命中
5. 还有一点，join在分库查询场景中实现困难，如果最后进行分库操作，之前的join语句就全失效了，所以还是推荐在应用层处理

12. 如何处理Mysql的深度分页问题？

1. 先说一下深度分页导致的问题，通常情况下是使用 limit offset , count 的方式进行分页查询，但如果offset是百万甚至亿的级别，即使命中索引，也会扫描前offset条数据并丢弃，最后只取少的可怜的count个数据，这样性能非常低
2. 方式一，使用游标，在每次查询时向前端返回一个游标字段（主键/时间等），下一次查询带着这个字段拼接到sql里即可，但这样没法适用于跳页的查询情况
3. 方式二，使用子查询/join，先用子查询在二级索引上定位起始id，然后根据这个id去主键索引查数据。这样就把offset扫描成本控制在二级索引层，避免在主键索引上进行大量回表操作
4. 方式三，使用es做数据检索，但也会有es深度分页问题，一般使用 search_after 解决，本质依然是游标分页
5. 方式四，在应用层面限制，比如使用滚动式翻页适应游标分页策略，限制最大翻页数，热点数据放Redis等

13. Mysql中存金额数据，用什么数据类型？

1. 首先明确一点，不能用float/double，会存在精度丢失问题
2. 方案一：bigint存分，运算快，存储空间占用小，没有BigDecimal的性能开销，但在汇率计算等精度高的场景，无法处理
3. 方案二：decimal(总位数,小数)，可以处理精度高的场景，但性能开销大，处理复杂
4. Java中使用BigDecimal时需要注意要用字符串构造器，更精确；除法必须指定精度和舍入模式；比较用compareTo不要用equals，防止1.0和1.00不相等的情况出现

14. 逻辑删除中唯一索引冲突问题？

1. 场景：有一张报名表，user_id+activity_id作为唯一索引，用户先报名，又取消报名，再次报名的时候索引就冲突了
2. 方案一：将逻辑字段改为delete_at记录删除时间，然后和user_id+activity_id+delete_at做唯一索引
3. 方案二：is_delete默认值为0，删除时不是将这个值改为1而是改为主键ID，因为主键ID不可能重复
4. 方案三：使用同一数据，user_id+activity_id作为唯一索引，报名时为0，删除时为1，再次报名再改为1，不是新增而是更新，使用额外的一张流水表（只增）记录用户行为