数据库优化和实施

一、定位慢查询

1. 通过运维监控系统（如Skywalking）定位接口慢的问题，查看SQL的具体执行时间，从而确定是哪个SQL出了问题。
2. 在MySQL中，可以开启慢日志查询功能，在系统配置文件中设置SQL执行超过一定时间（如2秒）就记录到日志文件中，以便在日志文件中找到执行比较慢的SQL。

二、分析SQL执行慢的方法

使用MySQL自动的执行计划explain来查看SQL的执行情况，具体包括：

1. 通过key和key_len检查是否命中索引，以及索引是否失效。
2. 通过type字段查看SQL是否有优化空间，是否存在全索引扫描或全盘扫描。
3. 通过extra建议判断是否出现回表情况，并尝试通过添加索引或修改返回字段来修复。

三、索引相关

1. 索引定义：帮助MySQL高效获取数据的数据结构，主要用于提高数据检索效率，降低数据库的IO成本、数据排序成本和CPU消耗。
2. 底层数据结构：MySQL的默认存储引擎InnoDB采用B +树的数据结构来存储索引，选择B +树的原因包括阶数更多、路径更短、磁盘读写代价更低、便于扫库和区间查询。
3. B树和B +树的区别：
   • B树中，非叶子节点和叶子节点都会存放数据；B +树的所有数据都会出现在叶子节点，查询时查找效率更加稳定。
   • 在进行范围查询时，B +树效率更高，因为B +树的叶子节点存储数据，且叶子节点是一个双向链表。
4. 聚簇索引和非聚簇索引：
   • 聚簇索引是指数据与索引放到一块，B +树的叶子节点保存了整行数据，有且只有一个，一般情况下主键作为聚簇索引。
   • 非聚簇索引是指数据与索引分开存储，B +树的叶子节点保存对应的主键，可以有多个，一般自己定义的索引都是非聚簇索引。
5. 回表查询：通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据的过程。
6. 覆盖索引：指select查询语句使用了索引，在返回的列在索引中全部能够找到。使用id查询会直接走聚集索引查询，性能高。应尽量避免使用select *，尽量在返回的列中都包含添加索引的字段。

四、MySQL超大分页处理

采用覆盖索引和子查询来解决，先分页查询数据的id字段，确定id之后，再用子查询来过滤，只查询这个id列表中的数据。因为查询id的时候走的覆盖索引，所以效率可以提升很多。

五、索引创建原则

1. 表中的数据超过10万以上才创建索引，添加索引的字段是查询比较频繁的字段，如作为查询条件、排序字段或分组的字段。
2. 通常使用复合索引创建，一条SQL的返回值尽量使用覆盖索引。
3. 如果字段的区分度不高，会把它放在组合索引后面的字段。
4. 如果某一个字段的内容较长，会考虑使用前缀索引。
5. 不是所有的字段都要添加索引，索引的数量也要控制，因为添加索引会导致新增改的速度变慢。

六、索引失效的情况

1. 索引在使用时没有遵循最左匹配法则。
2. 模糊查询中%号在前面。
3. 在添加索引的字段上进行了运算操作或者类型转换。
4. 使用了复合索引，中间使用了范围查询，右边的条件索引也会失效。
5. 通常使用explain执行计划来分析SQL是否有索引失效的情况。

七、SQL优化经验

1. 建表时：参考阿里开发手册《嵩山版》，结合字段内容选择合适的类型，如数值类型（tinyint、int、bigint等）和字符串类型（char、varchar、text等）。
2. 使用索引时：参考索引创建原则进行优化。
3. SQL语句编写：
   • SELECT语句务必指明字段名称，不要直接使用select *。
   • 注意SQL语句避免造成索引失效的写法。
   • 聚合查询尽量用union all代替union，因为union会多一次过滤，效率比较低。
   • 表关联尽量使用inner join，不要使用left join和right join，如必须使用，一定要以小表为驱动。
4. 主从复制、读写分离。
5. 数据量比较大时，考虑分库分表。

八、事务相关

1. 事务的特性：ACID，分别指原子性、一致性、隔离性、持久性。
   • 原子性体现在事务操作要么都成功，要么都失败。
   • 一致性体现在事务过程中数据要一致。
   • 隔离性体现在事务之间不能相互干扰。
   • 持久性体现在事务提交后要把数据持久化。
2. 并发事务带来的问题：
   • 脏读：一个事务读取到另一个事务未提交的数据，该数据可能是“脏数据”。
   • 不可重复读：在一个事务内多次读同一数据，由于另一个事务的修改导致两次读取的数据不太一样。
   • 幻读：一个事务读取了几行数据后，另一个并发事务插入了一些数据，导致第一个事务发现多了一些原本不存在的记录。
3. 解决问题的方法：对事务进行隔离，MySQL支持四种隔离级别：
   • 未提交读（read uncommitted）：解决不了脏读、不可重复读和幻读问题，一般项目中不用。
   • 读已提交（read committed）：能解决脏读问题，但解决不了不可重复读和幻读问题。
   • 可重复读（repeatable read）：能解决脏读和不可重复读问题，但解决不了幻读问题，是MySQL默认的隔离级别。
   • 串行化（serializable）：可以解决所有问题，但事务串行执行，性能比较低。

九、undo log和redo log的区别

1. redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据。
2. undo log主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据。

十、事务中的隔离性保证（MVCC）

事务的隔离性是由锁和MVCC（多版本并发控制）实现的。MVCC的底层实现主要分为三个部分：

1. 隐藏字段：在MySQL中给每个表都设置了隐藏字段，包括事务id（trx_id）和回滚指针（roll_pointer）。
2. undo log日志：记录回滚日志，存储老版本数据，在内部会形成一个版本链。
3. readView读视图：解决事务查询选择版本的问题，在内部定义了匹配规则和当前的事务id，判断该访问哪个版本的数据。不同的隔离级别快照读不一样，最终的访问结果也不一样。

十一、MySQL主从同步原理

MySQL主从复制的核心是二进制日志（DDL语句和DML语句），具体步骤为：

1. 主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log。
3. 从库重做中继日志中的事件，将改变反映到自己的数据。

十二、分库分表相关

1. 项目中根据业务进行数据库的垂直拆分，每个微服务对应一个数据库。
2. 曾经使用过水平分库，当业务量迅速增长，某表数据超过1000万且性能变慢时，采用了水平分库。通过mycat作为数据库中间件，按照id（自增）取模的方式存取数据，并对旧数据进行清洗和按照id取模规则分别存储到各个数据库中，以分摊存储和读取的压力，解决性能问题。