优化

定位慢查询

聚和查询
多表查询
表数据量过大查询
深度分页查询

表现：页面加载过慢、接口测压响应时间过长（超过1s）

如何定位

开源工具

调试工具：Arthas

运维工具：Prometheus、Skywalking

MySQL自带慢日志

它记录了所有执行时间超过指定参数（默认10秒）所有SQL语句的日志，默认是关闭的，要开启需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息。慢日志文件存储在/var/lib/mysql/localhost-slow.log。

show_query_log=1
long_query_time=2# 一般配置在2秒左右

注：在调试阶段开启就可以了，生产阶段开启会损耗一点性能。

如何分析，解决

深度分页查询：

SQL执行计划

聚和查询：新增一张临时表。
多表查询：尝试优化SQL的结构。
表数据量过大查询：添加索引，添加了还是慢怎么办？

可以使用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息。在SELECT前面添加EXPLAIN或者DESC。

返回的不是查询结果，而是执行SQL的一些信息。

possible_keys：当前SQL可能会使用到的索引。

key：当前SQL实际命中的索引。

key_len：索引占用的大小。

Extra：额外的优化建议。

Using where；Using Index：查询使用了索引，需要的数据都能在索引列中能找到，不需要回表查询数据。
Using index condition：查询使用了索引，但是需要回表查询数据。有优化的空间。

type：这条SQL的连接的类型，性能由好到差为：NULL、system、const、eq_ref、ref、range、index、all。

NULL：查询没有用到表，出现的情况少。
system：查询系统中的表，出现的情况少。
const：根据主键查询。
eq_ref：根据主键或者唯一索引查询。
ref：索引查询。
range：范围查询。
index：索引树扫描。要优化
all：全盘扫描。要优化

SQL执行慢，如何分析

使用自带的工具EXPLAIN查看执行情况。

查看key和key_len检查是否命中了索引（索引本身存在失效的情况）。

查看type字段查看SQL是否由进一步的优化空间，是否存在全索引扫描或全盘扫描。

查询Extra建议判断是否出现了回表扫描。如果出现了，可以尝试添加索引或者修改返回字段来修复。

索引

索引就是帮助MySQL高效获取数据的数据结构（有序）。提高数据检索的效率，降低数据库IO的成本（不需要全盘扫描）。通过索引列对数据进行排序，减低数据排序的成本，降低了CPU的消耗。

MySQL的InnoDB引擎采用B+树来存储索引。

阶数更多，路径更短
磁盘读写代价B+树更低，非叶子节点只存key，叶子节点存储数据
B+树便于扫库和区间查询，叶子节点是双向循环链表。

聚簇和非聚簇索引

聚簇索引：将数据与索引存储在一块，索引结构的叶子节点保存了行数据。必须有且仅由一个。

非聚簇索引（二级索引）：将数据存与索引分开存储，索引结构的叶子节点关联的是对于的主键。可以存在多个。

聚簇索引的选举规则：

如果存在主键，主键就是聚簇索引。
不在主键，使用第一个唯一键索引做为聚簇索引。
不存在主键或没有合适的唯一键，则InnoDB会自动生成一个rowid作为隐藏的聚簇索引。

回表查询

先通过二级索引拿到主键值，然后根据主键值去聚簇索引拿到整行的数据。这个过程就是回表查询。

覆盖索引

覆盖索引是指查询使用了索引，且需要返回的列在该索引中已经全部能够找到。

使用id查询，直接走聚簇索引查询，一次索引扫描，直接返回数据，性能高。
如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *。

MySQL超大分页怎么处理

在数据量比较大的时候，如果使用limit分页查询，在查询时，越往后，分页查询效率越低。

例如select * from table_name limit 0, 10。这时候查询效率较高。

若是select * from table_name limit 9000000, 10，这时候查询效率很低，因为在进行分页查询的时候，如果执行limit 9000000, 10 ，此时MySQL排序前面9000010记录，仅仅返回9000000 - 9000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：一般分页查询时，通过创建覆盖索引能够比较好的地提高性能，可以通过覆盖索引加子查询形式进行优化。

select * from table_name t, (select id from table_name order by id limit 9000000, 10) a where t.id = a.id。

索引创建原则（复合索引）

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

索引失效场景

违法最左前缀法则。如果索引了多列，要遵循最左前缀法则。指的是查询从索引的最左前列开始，且不跳过索引中的列。
范围查询右边的列，不能使用索引。
在索引列上使用了运算操作。
字符串不加单引号。（类型转换）
以%开头的Like模糊查询。如果%放在字符串后面，索引不会失效。

SQL优化经验

表的设计优化

设置合适的数值类型（tinyint、int、bigint），要根据实际情况选择。
设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长，效率稍低。

索引的优化

索引创建原则，索引失效场景

SQL语句优化

SELECT语句务必指明字段名称（避免直接使用SELECT *）。尽量走覆盖索引。
SQL语句要避免造成索引失效的写法。
尽量使用union all代替union，union会多一次过滤，效率低。
避免在where子句中对字段进行表达式操作。
join优化能用inner join就不要使用left join、right join，必须要使用一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放里边。left join或right join不会调整顺序。

主从复制、读写分离

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

分库分表

如下。

其他

事务相关

事务特性

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

隔离级别

并发会带来脏读，不可重复读、幻读。

隔离级别是读未提交、读已提交、可重复度（MySQL默认）、串行化

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

不可重复读：一个事务先后读取同一条记录，但是读取到的数据不同。

幻读：事务查询按照条件查询时，没有对应的数据行，但在插入的时候这条数据又存在了。

隔离级别	脏读	不可重复读	幻读
读未提交	×	×	×
读已提交	√	×	×
可重复读（默认）	√	√	×
串行化	√	√	√

undo log和redo log的区别

缓冲池（buffer pool）：主内存中的一个区域，里面可以存储磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池里面的数据（若没有则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，以加快处理速度。未同步到磁盘的页叫脏页。

数据页（page）：是InnoDB存储引擎磁盘管理的最小单元，没个页的大小默认为16kb。页中存储的是行数据。

有了缓冲池后，如果数据还没同步到磁盘，数据库服务器宕机了就会造成数据丢失。

redo log：记录数据提交时物理的修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）和重做日志文件（redo log file）。当事务提交后会把所有修改信息都存储到该日志文件中。用于在刷新脏页同步到磁盘中发送错误时进行数据恢复使用。

undo log：回滚日志，用于记录数据被修改之前的信息，提供回滚和MVCC。undo log和redo log记录物理日志不一样，它是逻辑日志。是用来实现事务的一致性和原子性。

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

MVCC

MVCC（multi-version concurrency control）多版本并发控制。

事务的隔离性是由排他锁和mvcc保证的。

实现原理

记录中的隐藏字段

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID（自增）。
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。