MySQL-优化

mysql中，如何定位慢查询？

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

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

方案一：开源工具

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

方案二： MySQL自带慢日志查询

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

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

/var/lib/mysql/localhost-slow.log

问：MySQL中如何定位慢查询？

答：我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题

如果，项目中没有这种运维的监控系统，其实在MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。 

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

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

• possible_key  当前sql可能会使用到的索引
• key  当前sql实际命中的索引
• key_len  索引占用的大小
• Extra  额外的优化建议
• 
• type  这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all

1. system：查询系统中的表
2. const：根据主键查询
3. eq_ref：主键索引查询或唯一索引查询
4. ref：索引查询
5. range：范围查询
6. index：索引树查询
7. all：全盘扫描

问：那这个SQL语句执行很慢，如何分析呢？

答：如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划lexplain来去查看这条sql的执行情况，比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了可以尝试添加索引或修改返回字段来修复。

了解过索引吗？（什么是索引）

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（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+树便于扫库和区间查询

问：了解过索引吗？（什么是索引）

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

问：索引的底层数据结构了解过吗？

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

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

问：B树和B+树的区别是什么呢？

• 第一，在B树中，非叶子节点和叶子节点都会存储数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定。
• 第二，在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链接。

什么是聚簇索引（聚集索引）？什么是非聚簇索引（二级索引）？

• 聚集索引（Clustered Index），将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。特点是必须有，而且只有一个。
• 二级索引（Secondary Index），将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。特点是可以存在多个。

聚集索引选取规则：

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

回表查询

什么是回表查询？

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

什么是覆盖索引？

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

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

MYSQL超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

执行limit分页查询耗时对比图如下：

因为，当在进行分页查询时，如果执行limit9000000，10，此时需要MYSQL排序前9000000 10记录，仅仅返回9000000 - 9000000 10的记录，其他记录丢弃，查询排序的代价非常大。

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

问：MYSQL超大分页怎么处理？

超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了，因为查询id的时候，走的覆盖索引，所以效率可以提升很多。

索引创建原则有哪些？

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

什么情况下索引会失效？

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

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

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

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

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

谈一谈你对sql的优化经验

• 表的设计优化
• 索引优化（参考优化创建原则和索引失效）
• SQL语句优化
• 主从复制、读写分离
• 分库分表

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

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

SQL语句优化

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

主从复制、读写分离

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

问：sql的优化经验

答：这个在项目中还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如建表的时候，使用索引、sql语句的编写、主从复制、读写分离，还有一个是如果量比较大的话，可以考虑分库分表。

问：创建表的时候，你们是如何优化的？

答：这个我们主要参考的阿里出的开发手册《嵩山版》，就比如，在定义字段的时候需要结合字段的内容来选择合适的字段类型，如果是数值的话，像tinyint、int、bigint这些类型，要根据实际情况选择，如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型。

问：对sql语句优化有哪些了解？

答：比如SELECT语句务必指明字段名称，不要直接使用select *,还有就是要注意SQL语句避免造成索引失效的写法，如果是聚合查询，尽量用uninon all代替union，union会多一次过滤，效率比较低，如果是表关联的话，尽量使用inner join，不要使用left join right join，如必须使用一定要以小表为驱动。

事务

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

ACID是什么？可以详细说一下吗？

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

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

• 并发事务问题：脏读、不可重复读、幻读
• 隔离级别：读未提交、读已提交、可重复读、串行化

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

undo log和redo log的区别

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

上图所示，从磁盘中拷贝数据到内存中进行数据更新时，此时服务器宕机，内存中的数据无法同步更新到磁盘，此时无法进行数据的持久化。

redo log

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

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

Redolog buffer在进行数据同步的时候是顺序的磁盘IO，因为日志文件都是追加的，所以顺序的磁盘IO性能就提升很多

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

记录中的隐藏字段

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除

而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会被立即删除

undo log版本链

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

readview

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

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

• read committed：在事务中每一次执行快照读时生成ReadView
• repeatable read：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView
• 
• 

当前读

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

快照读

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

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

问：事务中的隔离性是如何保证的呢？（解释一下MVCC）

答：事务的隔离性是由锁和mvcc实现的。

其中mvcc的意思是多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为三个部分，第一个是隐藏字段，第二个是undolog日志，第三个是readView读视图。

隐藏字段是指，在mysql中给每个表都设置了隐藏字段，有一个是trx_id（事务id），记录每一次操作的事务id，是自增的；另一个字段是roll_pointer（回滚指针），指向上一个版本的事务版本记录地址。

undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表。

readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问哪个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样，如果是rc隔离级别，每一次执行快照读时生成readview，如果是rr隔离级别仅在事务中第一次执行快照读时生成readview，后续复用。

MySQL主从同步原理

Mysql主从复制的核心就是二进制文件

二进制文件（BINLOG）记录了所有的DDL（数据定义语言）和DML(数据操纵语言)语句，但不包括数据查询（SELECT 、SHOW）语句。

复制分成三步：

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

分库分表

分库分表的时机

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

垂直拆分

垂直分库

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

特点：

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

垂直分表

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

特点：

• 冷热数据分离
• 减少IO过度争抢，两表互不影响

水平拆分

水平分库

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

特点：

• 解决了单库大数据量、高并发的性能瓶颈问题
• 提高了系统的稳定性和可用性

路由规则

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

水平分表

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

特点：

• 优化单一表数据量过大而产生的性能问题
• 避免IO争抢并减少锁表的几率

分库分表的策略有哪些？

新的问题和新的技术

分库之后的问题：

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

分库分表中间件：

• sharding-sphere
• mycat

问：你们项目中用过分库分表吗？

答：业务介绍

1. 根据自己实际项目，想一个数据量较大业务（请求数多或业务累积大）
2. 达到了什么样的量级（单表1000万或超过20G）

具体拆分策略

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