数据库优化 + 执行计划分析

读写分离

- 基础思想就是使用多个数据库，其中一个作为主库用来写入数据，然后同步到其他数据库并承接读操作。

- 主从复制的实现原理为利用了主数据库的binlog持久化文件，会存储所有的写入操作，然后同步给从数据库

- 如何避免复制延迟？

        - 同步完成前，将从数据的读操作路由到主数据库

        - 如果业务没有强要求，可以手动设置延迟0.5s到1s左右。例如支付完成后跳转到支付成功页面，需要用户手动返回用户中心才会查询支付详情。

分库分表

遇到下面几种场景可以考虑分库分表：

• 单表的数据达到千万级别以上，数据库读写速度比较缓慢。
• 数据库中的数据占用的空间越来越大，备份时间越来越长。

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

分库后需要面临的问题有：

跨库聚合查询问题：需要使用sql路由，根据查询的条件是否带有sharding-key来路由到对应的数据库中。或者查询所有库之后进行结果合并。

分布式ID：mysql自带的自增主键不能满足分库的场景，需要使用分布式ID来解决这个问题。常用的有数据库号段模式。我们可以批量获取，然后存在在内存里面，需要用到的时候，直接从内存里面拿就舒服了！这也就是我们说的 基于数据库的号段模式来生成分布式 ID。

SQL调优

针对慢sql，我们可以采用查看执行计划的方式来调优：explain执行计划包含以下关键字：

        type: 

                - const : 使用主键或唯一索引只查询出一条数据时

                - eq_ref: 使用了主键或唯一索引进行联表查询

                - ref：使用了普通索引进行查询

                - range：对索引进行了范围查询

                - index：查询遍历了所有索引

                - all： 全表扫描

        key: 表示使用了哪个索引

        extra：

                - Using filesort：没有使用索引排序，而是全量查询后手动排序，性能较差。优化：需要对于orderby的字段创建索引

                - Using temporary：创建了临时表，常见于orderby 和group。优化：需要对于orderby的字段创建索引

                - Using index：使用了索引

                - Using index condition：使用了索引条件下推

                - Using where：没有使用到索引，使用了where条件的子语句查询。优化：需要对于where的条件字段创建索引

                - Using join buffer 没有使用到索引，使用了联表查询。优化：需要对于join联表的字段创建索引

索引

索引的原理，为什么建立了索引会更快？

        - 索引即是针对某一个或多个字段提前建立一个 索引和主键的键值对，然后将这些键值对用B+树的形式存储起来，这样我们在根据索引条件搜索时，可以直接用O(1)的复杂度找到对应的主键，再去表里搜索整行的内容，这样会更快，当然如果我们要找的内容都已经在索引里了，那会更快，因为不用回表。B+树是B树的加强版，本质上也是一个允许多个子节点的二叉树，只不过B+树只允许子节点存放数据，其他节点只能存放索引，这样的好处是在增删时不会对树的形状有太大的影响。并且所有的叶子节点是相连的，在范围查找时不用从根节点出发，而是在链表上查找就行。

如果发现我们建立了索引，但是没有被使用到，以下是几种常见情况：

        - 搜索条件没有使用最左匹配原则。例如索引字段为a,b,c 但是where语句里是 b,c

        - 搜索条件使用了左或者左右模糊匹配，例如使用了右like： like "%xxx"。 尽量使用左like即： like "xxx%"

        - 搜索条件中使用了函数等复杂方法。

        - 条件的左右类型不一致，例如我们数据库中年龄是整型数 10，但是条件中却写成 age = "10"，与字符串比较会导致索引失效

索引的建立也有讲究，我们要尽量选择区分度大的字段作为索引，例如年龄和性别之间，使用年龄来当做索引能更好的的区分不同的人。有时候即使我们建立了索引，查询反而会变慢，可能是因为没有索引覆盖，如果数据量较大，且没有索引覆盖，会造成回表查询，效率更低。

             

分页

无论我们使用MyBatis还是Mysql自带的分页，本质上都是使用了offset+size的方式来查找分页数据，这个方式当offset过大时都会导致性能急剧下降。所以我们要确保：

        - offset不应该过大，当offset过大时，要求用户提供更精细的查询条件。

        - 对有分页查询的条件和orderby的字段都建立索引，避免回表。

        - 对于热点数据使用redis缓存数据，避免从数据库读取。

事务

事务具有ACID特性：

        - Atommic原子性：要么全部完成，要么全部失败。也就是说需要提供回滚功能来确保一旦事务中有提交失败，则全部回滚。通过undo log来实现

        - consistent一致性：指操作后数据库的数据要保持前后一致性，例如转账前和转账后A和B的总余额不变。通过其他三个特性来实现。

        - Isolation隔离性：允许多个事务同事操作数据库，不存在不同事务之间互相影响数据导致数据不一致。通过锁来实现

        - Durabilty可用性：事务结束后，数据不会丢失。通过redo log来实现

隔离级别

首先事务如果完全不隔离，会有三种问题：

        - 脏读：读到了另一个事务未提交的数据

        - 不可重复读：同一个事务中由于另一个事务的提交先后读到了不一样的数据。

        - 幻读：同一个事务中由于另一个事务的提交先后读到了不一样数据的数量。

MYSQL 支持四种不同的隔离级别：

        - 读未提交：完全无法解决三种问题。

        - 读提交：可以解决脏读问题。通过读快照的方式实现，在每一行语句提交前生成一个快照

        - 可重复度：可以解决脏读和不可重复读问题，和通过读快照和行锁的方式来解决绝大部分场景的幻读问题。是innoDb引擎的默认级别。（MVCC） 在每个事务开始前生成一个快照，本次事务读取的都是这个快照里的内容，而不是真正数据库里的内容。

        - 串行：解决所有问题，但是性能消耗过大，需要通过锁来避免竞争。

需要注意的是，在可重复读的场景下，执行update语句时会在事务结束前对当前记录或者一定范围的记录加上行锁，阻塞其他线程访问这些数据，所以update时一定要确保语句走了索引，不然走全表扫描的话会把全表的数据都加上锁。