Mysql优化

慢查询

如何定位慢查询

慢查询可能发生的场景 l聚合查询 l多表查询 l表数据量过大查询 l深度分页查询 表象：页面加载过慢、接口压测响应时间过长（超过1s）

定位慢查询的方案

方案一：开源工具

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

方案二：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。

总结

如何定位慢查询

1.介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟） 2.我们系统中当时采用了运维工具（ Skywalking ），可以监测出哪个接口，最终因为是sql的问题 (没用过可不讲) 3.在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段）

SQL语句执行很慢，如何分析

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

语法：

- 直接在select语句之前加上关键字 explain / desc
EXPLAIN   SELECT   字段列表   FROM   表名   WHERE  条件 ;

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

• system：查询系统中的表

• const：根据主键查询

• eq_ref：主键索引查询或唯一索引查询

• ref：索引查询

• range：范围查询

• index：索引树扫描

• all：全盘扫描

最少要确保达到range

总结

那这个SQL语句执行很慢, 如何分析呢？ 可以采用MySQL自带的分析工具 EXPLAIN l通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况） l通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描 l通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

索引

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

索引的底层数据结构了解过嘛 ? (B+树)

数据结构对比

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

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key

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

B树与B+树对比: ①：磁盘读写代价B+树更低； ②：查询效率B+树更加稳定； ③：B+树便于扫库和区间查询

总结

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

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

聚簇索引 非聚簇索引

聚集索引和二级索引

聚集索引选取规则:

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

回表查询

总计

什么是聚簇索引什么是非聚簇索引 ?

• 聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个

• 非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个

知道什么是回表查询嘛 ?

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

覆盖索引

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

id为主键，默认是主键索引 name字段为普通索引

select * from tb_user where id = 1; 					-- 覆盖索引
select id，name from tb_user where name = ‘Arm’;		   -- 覆盖索引
select id，name，gender from tb_user where name = ‘Arm’; -- 非覆盖索引(需要回表查询)

总结

1. 知道什么叫覆盖索引嘛 ?

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

2. MYSQL超大分页怎么处理 ? 问题：在数据量比较大时，limit分页查询，需要对数据进行排序，效率低 解决方案：覆盖索引+子查询

MYSQL超大分页处理

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

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

索引创建原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。 单表超过10万数据（增加用户体验）

2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

总结

索引创建原则有哪些？

1). 数据量较大，且查询比较频繁的表 重要

2). 常作为查询条件、排序、分组的字段 重要

3). 字段内容区分度高

4). 内容较长，使用前缀索引

5). 尽量联合索引 重要

6). 要控制索引的数量 重要

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

索引失效

1). 违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引：正常情况如下

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

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

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

根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

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

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

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

总结

①违反最左前缀法则

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

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

④字符串不加单引号，造成索引失效。(类型转换)

⑤以%开头的Like模糊查询，索引失效

SQL优化经验

表的设计优化 索引优化 参考优化创建原则和索引失效 SQL语句优化 主从复制、读写分离 分库分表 后面有专门章节介绍

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

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

l主从复制、读写分离

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

读写分离解决的是，数据库的写入，影响了查询的效率。

总结

1.表的设计优化，数据类型的选择 2.索引优化，索引创建原则 3.sql语句优化，避免索引失效，避免使用select * …. 4.主从复制、读写分离，不让数据的写入，影响读操作 5.分库分表

事务

事务特性

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

事务的特性 ACID

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

并发事务问题

并发事务问题的解决方案

解决方案：对事务进行隔离

注意：事务隔离级别越高，数据越安全，但是性能越低。

总结

并发事务带来哪些问题？怎么解决这些问题呢？MySQL的默认隔离级别是？

l并发事务的问题：

①脏读：一个事务读到另外一个事务还没有提交的数据。 ②不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同 ③幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

l隔离级别：

①READ UNCOMMITTED 未提交读 脏读、不可重复读、幻读 ②READ COMMITTED 读已提交 不可重复读、幻读 ③REPEATABLE READ 可重复读 幻读 ④SERIALIZABLE 串行化

undo log和redo log的区别

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

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

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

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

WAL（Write-ahead logging预写日志）是数据库用于实现事务原子性和持久性的技术。

undo log

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

• 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，

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

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

总结

undo log和redo log的区别

• redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据

• undo log ：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据

• redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

事务中的隔离性如何保证

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

MVCC

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

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

MVCC实现原理

1. 记录中的隐藏字段

2. undo log

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

3. undo log版本链

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

4. readview

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

5. 当前读

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

6. 快照读

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

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

ReadView中包含了四个核心字段：

不同的隔离级别，生成ReadView的时机不同：

• READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。

• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

总结

事务中的隔离性是如何保证的呢？(你解释一下MVCC)

MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

l隐藏字段：

①trx_id(事务id)，记录每一次操作的事务id，是自增的

②roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

lundo log：

①回滚日志，存储老版本数据

②版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

lreadView解决的是一个事务查询选择版本的问题

• 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据

• 不同的隔离级别快照读是不一样的，最终的访问的结果不一样

  RC ：每一次执行快照读时生成ReadView

  RR：仅在事务中第一次执行快照读时生成ReadView，后续复用

MySQL主从同步原理

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，IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

拆分策略

垂直拆分

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。 特点： 1.按业务对数据分级管理、维护、监控、扩展 2.在高并发下，提高磁盘IO和数据量连接数

l垂直分表

拆分规则：

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

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。 特点： 1，冷热数据分离 2，减少IO过渡争抢，两表互不影响

水平拆分

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

特点： 1.解决了单库大数量，高并发的性能瓶颈问题 2.提高了系统的稳定性和可用性

路由规则

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

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

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

分库之后的问题：

• 分布式事务一致性问题
• 跨节点关联查询
• 跨节点分页、排序函数
• 主键避重

解决方法使用分库分表中间件

分库分表中间件：

• sharding-sphere
• mycat

总结

你们项目用过分库分表吗

l业务介绍

1，根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）

2，达到了什么样的量级（单表1000万或超过20G）

l具体拆分策略

1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题

2，水平分表，解决单表存储和性能的问题

3，垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数

4，垂直分表，冷热数据分离，多表互不影响

Mysql存储引擎

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

MySQL体系结构

存储引擎特点

InnoDB

介绍

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

特点

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

文件

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

总结

MYSQL支持的存储引擎有哪些, 有什么区别 ?

在mysql中提供了很多的存储引擎，比较常见有InnoDB、MyISAM、Memory

InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁 MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多 Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

存储引擎在mysql的体系结构哪一层，主要特点是什么

• MySQL体系结构

• InnoDB存储的特点

MySQL面试题-参考回答.pdf