1. 一条SQL的执行过程
- 系统和MySQL进行通信是通过MySQL驱动，比如数据库连接池。数据库连接池维护一定的连接数，线程使用则获取，使用完放回去。
- MySQL本身也有一个类似的池子，这样的话系统和MySQL交互就通过连接池
- SQL语句的请求在MySQL中是由一个个的线程去处理的。
- 线程在获取SQ语句后交给SQL接口去处理，通过查询解析器、优化器，最后交给执行器执行。真正执行的动作是在存储引擎完成的
那么，从执行器调用存储引擎接口，又做了哪些事呢？
- MySQL(InnoDB)会先去缓冲池BufferPool中去查找这条数据，没找到就会到磁盘查找，如果查找到就把这条数据加载到缓冲池
- 加载到缓冲池的通知，将这条数据的原始记录保存早undo log日志文件。
为什么这么做？ Innodb 存储引擎的最大特点就是支持事务，如果本次更新失败，也就是事务提交失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响
- InnoDB会在Buffer Pool中执行更新操作
- 更新后的数据记录到redo log buffer。等到事务提交写到redo log日志文件。redolog和binlog都有刷磁盘操作。
除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo 日志文件就需要来大显神通了
bin log 的刷盘是有相关的策略的，策略可以通过sync_bin log来修改，默认为 0，表示先写入 os cache，也就是说在提交事务的时候，数据不会直接到磁盘中，这样如果宕机bin log数据仍然会丢失。所以建议将sync_bin log设置为 1 表示直接将数据写入到磁盘文件中。
参考比较综合的流程 juejin.cn/post/693160…
2. mysql存储引擎myisam和innodb
- 事务： myisam不支持事务，innodb支持事务。默认隔离级别是可重复度，通过mvcc(并发版本控制)来实现，能够解决脏读和不可重复读的问题。
- 锁： innodb是行锁，可能产生死锁；myisam是表锁，并发行差，不容易产生死锁。
- 外键： innodb支持外键，myisam不支持外键
- 存储表结构： myisam会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是frm（存储表定义）、MYD（存储数据）、MYI（存储索引）。myisam只缓存索引文件，不存储数据文件。innodb 也是用frm文件存储表结构定义。不同的是，InnoDB的表数据与索引数据存储在一起，都在B+数的叶子节点上，而myisam的表数据和索引数据是分开的。
- 索引： 支持索引的类型基本相同，只不过在具体实现上，由于文件结构的不同有差异。myisam支持全文索引，innodb不支持
- 增删改查性能： 大量的增删改操作，推荐使用innodb存储引擎，删除时候，不会重建表。myisam一般是select性能比较高。
加餐（myisam索引和innodb索引的区别）：
- InnoDB是聚簇索引，MyISAM是非聚簇索引
- InnoDB的主键索引的叶子结点存储着行数据，所以主键索引非常高效，其他索引的叶子结点存储的是主键和其他带索引的列数据，也比较搞笑。
- MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据
3. 事务的四大特性ACID
原子性（A） 一致性（C） 隔离性（I） 持久性（D）
MySQL是如何保证ACID的
4. 事务的隔离级别
- 未提交读： 最低级别，允许脏读，也就是可能获取到其他会话中提交事务修改的数据。可能导致脏读、幻读或不可重复读。
- 已提交读： 只能读取已经提交的数据，可以阻止脏读
- 可重复读： 对同一字段的多次读取结果都是一致的，除非数据是本身事务所修改，可以避免脏读、不可重复读，但是幻读仍可能发生。MySQL默认的隔离级别
- 可串行化： 最高隔离级别，完全服从ACID的隔离级别。所有事务依次逐个进行。事务之间不可能产生干扰。
可重复读的核心就是一致性读;保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据，会造成幻读。
在事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。可重复读的时候会有幻读，可重复读再快照读的情况下不会幻读。
多版本并发控制清晰版 juejin.cn/post/702042…
多版本并发控制mvcc juejin.cn/post/709011…
mvcc核心思想： 可以查到事物开始之前已经存在的数据，即使它在后面被修改或者删除了。在这个事务之后新增的数据，是查不到的。
mvcc查找原则： 只能查找创建时间小于等于当前事务ID的数据和删除时间大于当前事务ID的行
mysql全局锁/表锁/行锁
innodb三种行锁（记录锁/间隙锁/临键锁） juejin.cn/post/684490…
5. 索引类型
主键索引： 数据不重复，不可以为NULL，一个表只能有一个。
唯一索引： 数据不重复，可以为NULL,可以有多列，也可以组合唯一索引。
普通索引： 基本的索引类型，没有限制，组合索引最左原则。
全文索引： 搜索文本。但实际场景比较少用。大文本大数据量都走ES了。
唯一索引和普通索引怎么选（涉及到change_buffer问题）
6. 索引失效的场景
- 联合索引不符合最左原则
- 使用了select *
- 使用or操作
- 错误的like 左边%
- 索引列参与运算 where id + 1 = 2; 可以改为内存运算
- 索引列使用函数 where SUBSTR(id_no,1,3) = '100';
- 类型隐式转换。 id_no varchar where id_no = 20; 但是id原本是int, where id = '20'没问题
- 两列都有索引，两列做比较。 id/age 比如 where id>agen;
- 不等于比较 where id != 0;
- not in 如果是主键索引
索引失效的原理 blog.csdn.net/BestandW1sh…
using filesort 优化
mysql分页查询优化
7. 聚簇索引和非聚簇索引
聚簇索引： 说来了就是能找到索引就能找到数据，将索引和数据存储放到一块。
优点：
- 理论上聚簇索引查找数据更快，毕竟非聚簇索引还要进行一次寻址，多一些I/O
缺点：
- 插入速度严重依赖插入顺序。
- 更新主键代价非常高，因为会导致被更新的行移动。所以对于InnoDB来讲，一般定义主键不可更改。
- 二级索引访问需要两次索引查找，第一次找主键。第二次根据主键找数据。
非聚簇索引： 索引和数据存储分开，索引的叶子节点指向数据的对应行。当通过索引访问数据时，在内存中直接搜索索引，再根据索引找到数据。
8. char和varchar
- char固定长度，存储时，会被空格填充到特定长度。存取速度快，牺牲存储空间来提高效率
- varchar长度依照的字符长度而定，存取速度较varchar慢，牺牲存取效率，提高存储空间利用率
9. 为什么B+树更适合做数据库索引
- B+树的非叶子节点只存储key，占用空间小，因此每一层的节点能索引到的数据范围更广，换句话说，每次IO操作可以查看更多的数据。
- B+树的查询效率比B树更稳定，数据只存在叶子节点上，所以查询效率固定O(log n)
- B+树的叶子节点之间用链表有序连接，所以扫面全部数据只需要扫描一遍叶子阶段，利于扫库和范围查询；B树非叶子节点也存数据，所以只能通过中序遍历。所以说对于范围查询和有序遍历而言，B+树效率更高。
10. mysql日志详解
undolog记录的数据操作前的样子， redolog记录的是数据操作后的样子。
redo log: 在存储引擎层，InnoDB独有的，又叫重做日志文件，记录的是事务修改后的值，不管事务是否提交都会记录下来，当系统崩溃时，虽然数据没有持久化，但是RedoLog已经持久化。系统可以根据RedoLog的内容，将所有数据恢复到最新的状态。 redolog写入机制：
bin log： 二进制日志，在MySQL Server层，只要发生了数据变更就会产品binlog日志。可以用于数据备份、主从、主主、canal监听数据变更。写入机制： 先把日志写到binlog cache，事务提交的时候再把binlog cache写到binlog文件中
11. 谈谈分库分表
背景：支付宝用户8亿；微信用户更是10亿，订单表更夸张，比如美团外面，每天都是几千万的订单。淘宝的历史订单总量应该在百亿或者千亿级别，这些海量数据不是一张表能hold住的。事实上MySQL单表可以存储10亿数据，但是性能很差。业界公认MySQL单表1kw以下是最佳状态。
那么需要考虑把这么多数据放到多个地方，普遍方案
- 分区
- 分库分表
- NoSQL（Mongo/ES）NewSQL（TiDB）
1. 数据库瓶颈判定
无论是IO瓶颈还是CPU瓶颈，都会导致数据库活跃连接数增加
**1> IO瓶颈 **
第一种：磁盘读写IO瓶颈，热点数据太多，数据库缓存放不下，每次查询产生大量IO->分库和垂直分表
第二种：网络IO瓶颈，请求的数据太多，网络带宽不够->分库
2> CPU瓶颈
第一种： SQL自身问题导致的CPU， 结合慢查询看->SQL优化，建立合适的索引
第二种： 单表数据量太大，查询扫描太多行，SQL效率低，CPU出现瓶颈->水平分表
2. 分库分表方式
1. 水平分库
以字段进行一定的策略（hash/range），讲一个库的数据拆到多个库。
每个库的结构一样，数据不一定，所有库的并集是全量数据。
库多了，IO和CPU可以缓解
2. 水平分表
以字段进行一定策略（hash/range）将一个表的数据拆到多个表
表的数据量减少，单次执行就少了。
3. 垂直分库
以表为依据，不同的表拆到不同的库。
可以结合微服务拆分
4. 垂直分表
表的记录不多，但是字段很多，热点数据和非热点数据一起。
比如列表和详情页这种。基础字段和附加信息
分库分表步骤总结
根据容量（当前容量和增长量）评估分库或分表个数 -> 选key（均匀）-> 分表规则（hash或range等）-> 执行（一般双写）-> 扩容问题（尽量减少数据的移动）
3. 分库分表带来的问题
- 非分片key的查询问题：可以增加映射、冗余表
- 扩容问题： 双写（第一个修改配置和代码，双写，第二步，历史数据复制迁移）-> 数据校准-> 改读取 -> 去掉双写，观察稳定性
- 复杂查询： NoSQL es/hbase
4. 实际案例
背景是啥
积分明细表单表1亿多，进行单服务压测加积分的接口，压出单数据库性能瓶颈为1100~1300qps，会出现主从延迟。目标qps5000。初步分析分库分担压力，需要5个库。
怎么评估容量
日活10万，目标活跃用户20万。20万*5=100万数据。一年就是3亿多数据。
活跃增长是3年目标，但是积分明细是保留一年，其他数据存档备份。
按照每张表1000万条记录，需要35张表。5个库10表。
怎么分的
按照ucid 分库分表
有什么问题
积分明细都是基于ucid个人的，不向订单相关的分库分表需要保留订单号和ucid的映射关系。
组件是啥
sharding jdbc
分片策略与分片算法
sharding-jdbc流程
分库分表的问题
-
事务一致性问题：评估分布式事务的必要性，一般使用最终一致性。
-
跨节点关联查询：拆分两次查询。
-
跨节点分页、排序
-
主键避重：雪花算法
-
公共表问题
分库分表非拆分键的问题解决
12. 实际遇到的问题与解决
- SQL治理的方法论
上线前的充分评估，索引合理性、代码review
关键业务的监控报警完善，早发现早解决。
索引优化
合理的数据容量评估
SQL治理专项（结合服务重构与业务流程梳理）
