数据库优化

---

一、定位慢查询

（一）慢查询场景

1、聚合查询

2、多表查询

3、表数据量过大查询

4、深度分页查询

（二）定位方式

1. 开源工具

调试工具：Arthas
运维工具：Prometheus,skywarking

2. 开启mysql自带的慢日志查询

二、分析慢SQL

（一）sql执行计划——Explain

extra 额外的优化建议

可以采用MySQL自带的分析工具 EXPLAIN

• 通过key和key len检査是否命中了索引(索引本身存在是否有失效的情况)
• 通过type字段査看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
• 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

三、索引介绍

索引(index)是帮助MySQI高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定査找算法的数据结构(B+树)，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级査找算法这种数据结构就是索引。

（一）什么是索引

索引(index)是帮助MySQL高效获取数据的数据结构(有序)提高数据检索的效率，降低数据库的I0成本(不需要全表扫描)
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

（二）索引底层数据结构

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引 阶数更多，路径更短
磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据B+树便于扫库和区间查询，叶子节点是一个双向链表

1、二叉树&红黑树

2、B树

B-Tree，B树是一种多叉路衡査找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数(max-deqree)为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key。

4、B+ 树

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

5、B树与B+树对比:

②磁盘读写代价B+树更低;
②:查询效率B+树更加稳定;
③:B+树便于扫库和区间查询1:

（二）聚集索引（聚簇索引）&二级索引（非聚簇索引）

（三）索引的创建原则

索引创建原则有哪些?
1).针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据(增加用户体验)。
2).针对于常作为查询条件(where)、排序(order by)、分组（group by）操作的字段建立索引。
3).尽量选择区分度高的列作为索引，尽量建立唯索引，区分度越高，使用索引的效率越高。
4).如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5).尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6).要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7).如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

（四）索引的失效场景

1).违反最左前缀法则如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:

违法最左前缀法则， 索引失效:

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效:

2).范围查询右边的列，不能使用索引。

3).不要在索引列上进行运算操作， 索引将失效。

4).字符串不加单引号，造成索引失效。

5).以%开头的Like模糊査询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

四、sql优化经验

（一）表的设计优化

表的设计优化(参考阿里开发手册《嵩山版》)
比如设置合适的数值(tinyint int bigint)，要根据实际情况选择
比如设置合适的字符串类型(char和varchar)char定长效率高，varchar可变长度，效率稍低

（二）索引优化

参考三

（三）SQL语句优化

1、SELECT语句务必指明字段名称(避免直
2、SQL语句要避免造成索引失效的写法
3、尽量用union all代替union union会多一次过滤，效率低
4、避免在where子句中对字段进行表达式
5、Join优化 能用innerjoin 就不用left joinright join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。leftjoin 或 right join，不会重新调整顺序

（四）主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。
读写分离解决的是，数据库的写入，影响了查询的效率。

MySQL主从复制的核心就是二进制日志
二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句。

复制分成三步:

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

主从同步原理
MySQL主从复制的核心就是二进制日志binlog(DDL(数据定义语言)语句和 DML(数据操纵语言)语句)
① 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
② 从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log.
③ 从库重做中继日志中的事件，将改变反映它自己的数据

（五）分库分表

主从复制只是分担访问压力，但是无法解决数量太大的问题
分库分表的时机:
1，前提，项目业务数据逐渐增多，或业务发展比较迅速单表的数据量达1000W或20G以后
2，优化已解决不了性能问题(主从读写分离、查询索引…)
3，10瓶颈(磁盘10、网络10)、CPU瓶颈(聚合查询、连接数太多)

1、拆分策略

1，水平分库，将一个库的数据拆分到多个库，解决海量数据存储和高并发的问题
2，水平分表，解决单表存储和性能的问题
3，垂直分库，根据业务进行拆分，高并发下高磁盘I0和网络连接数
4，垂直分表，冷热数据分离，多表互不影响

1.1 垂直分库

垂直分库:以表为依据，根据业务将不同表拆分到不同库中。

特点:

1. 按业务对数据分级管理、维护、监控、扩展
2. 在高并发下，提高磁盘I0和数据量连接数

1.2 垂直分表

垂直分表:以字段为依据，根据字段属性将不同字段拆分到不同表中。
拆分规则:

1. 把不常用的字段单独放在一张表
2. 把text，blob等大字段拆分出来放在附表中

特点:

1. 冷热数据分离
2. 减少I0过渡争抢，两表互不影响

1.3 水平分库

水平分库:将一个库的数据拆分到多个库中

路由规则

1. 根据id节点取模
2. 按id也就是范围路由，节点1(1-100万)节点2(100万-200万)

1.4 水平分表

水平分表:将一个表的数据拆分到多个表中(可以在同一个库内)。

特点:
1.优化单一表数据量过大而产生的性能问题,
2.避免I0争抢并减少锁表的几率;

2、分库分表带来的新的问题和技术

五、事务

（一）事物特性

• 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
• 隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
• 

（二）隔离级别

1、并发事物问题

2、隔离级别

事务中的隔离性是如何保证的呢?
锁:排他锁(如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁)；共享锁
mvcc:多版本并发控制

（三）undo log & redo log

1、缓冲池&数据页

• 缓冲池(buffer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改査操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘10，加快处理速度.
• 数据页(page):是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据.

2、redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用,

3、undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚 和 MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样，它是逻辑日志。

• 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然
• 当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log可以实现事务的一致性和原子性

（四）MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

1、一行数据中的隐藏字段

2、undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除.

3、undo log 版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

4、当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select … lock in share mode(共享锁)，select. . for update、update、insert、delete(排他锁)都是一种当前读。

5、快照读

简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

• Read Committed:每次select，都生成一个快照读。
• Repeatable Read:开启事务后第一个select语句才是快照读的地方。

6、readview

ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id.

事务中的隔离性是如何保证的呢?(你解释一下MVCC)
其中mycc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undolog日志，第三个是readView读视图
隐藏字段是指:在mysq!中给每个表都设置了隐藏字段，有一个是txid(事务id)，记录每一次操作的事务id，是自增的:另一个字段是roll.pointer(回滚指针)，指向上一个版本的事务版本记录地址
undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是r隔离级别，每一次执行快照读时生成ReadView，如果是rr隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用