MySQL篇

一、优化

1.1 定位慢查询

聚合查询、多表查询、表数据量过大查询、深度分页查询…,页面加载过慢、接口压测响应时间过长（超过1s）；

如何定位慢查询？

方案1：开源工具

• 调试工具：Arthas
• 运维工具：Prometheus、Skywalking

方案2：MySQL自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志，如果要开启慢查询日志，需要再MySQL的配置文件（/etc/my.cnf）中配置如下消息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息，/var/lib/mysql/localhost-slow.log

如何定位慢查询？

可以采用EXPLAIN或DESC命令获取MySQL如何执行SELECT语句的信息；
语法：

# 直接在select语句之祈安加上关键字explain或desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；

possible_key  当前sql可能会使用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
Extra 额外的优化建议

type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all 
system：查询系统中的表
const：根据主键查询
eq_ref：主键索引查询或唯一索引查询
ref：索引查询
range：范围查询
index：索引树扫描
all：全盘扫描

1.2 SQL执行计划

1.3 索引

1.3.1 存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

MySQL体系结构

存储引擎的特点

InnoDB

1. 介绍
   InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。
2. 特点

• DML操作遵循ACID模型，支持事务、外键、表级锁和行级锁；
• 行级锁 ，提高并发访问性能
• 支持外键FOREIGN KEY约束，保证数据的完整性和正确性

3. 文件

• xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
• xxx.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

1.3.2 索引底层数据结构

什么是索引？

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

索引底层的数据结构？

MySQL默认使用的索引底层数据结构是B+树。再聊B+树之前，我们先聊聊二叉树和B树；


选择B+树作为底层结构的主要原因：

1. 阶数更多、路径更短；
2. 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据；
3. B+树便于扫库和区间查询，叶子节点是一个双向链表；
   
   B树和B+树的区别：

• B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定；
• 在进行范围查询时，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表；
  B树与B+树对比:
  ①：磁盘读写代价B+树更低；
  ②：查询效率B+树更加稳定；
  ③：B+树便于扫库和区间查询；

1.3.3 聚簇和非聚簇索引

聚集索引选取规则:

1. 如果存在主键，主键索引就是聚集索引。
2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3. 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
   什么是回表？
   回表与聚簇索引和非聚簇索引是有关系的，回表就是通过二级索引找到对应的主键值，然后通过主键值找到聚集索引中所对应的整行数据，该过程就是回表。

1.3.3.1 覆盖索引

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到 。

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

1.3.3.2 MYSQL超大分页处理

• 使用limit分页查询，但查询过程中，越往后，分页查询效率越低；
• 一般分页查询时，通过创建覆盖索引能够较好地提高性能，可以通过覆盖索引加子索引形式进行优化；

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

1.3.4 索引创建原则

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

1.3.5 索引失效场景

1.3.5.1 违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

遵守最左前缀法则：

违反最左前缀法则：

1.3.5.2 范围查询右边的列，不能使用索引

1.3.5.3 不要在索引列上进行运算操作， 索引将失效

1.3.5.4 字符串不加单引号，造成索引失效

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

1.4 sql的优化经验

表的设计优化**（参考阿里开发手册《嵩山版》）**

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

1.4.1 表的设计优化

1.4.2 索引优化

1.4.3 SQL语句优化

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

1.4.4 主从复制、读写分离

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

1.4.5 分库分表

二、其他

2.1 事务相关

2.1.1 事务特性（ACID）

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

ACID

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

并发事务问题

并发事务问题

undo log和redo log的区别

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

redo log

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

undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。
可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，
当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
undo log可以实现事务的一致性和原子性

2.1.2 隔离级别

事务中的隔离性是如何保证的呢？

• 锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）
• mvcc : 多版本并发控制

2.1.3 MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突
MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

• undo log
  回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
  当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
  而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。
  
  不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

ReadView

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id;
ReadView常用的四个字段：

当前读

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

快照读

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

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

2.2 主从同步原理

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

2.3 分库分表

分库分表的时机：

1. 前提，项目业务数据逐渐增多，或业务发展比较迅速
2. 优化已解决不了性能问题（主从读写分离、查询索引…）
3. IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多

拆分策略

垂直拆分

水平拆分