面试--mysql篇

Mysql优化

在mysql中如何定位慢查询

方案一：开源工具

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

方案二：mysql自带慢日志

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

如何定位慢查询？

我们当时对一个接口测试的时候非常慢，压测的结果大概是5秒钟
当时没有使用运维的监控系统像skywalking之类的，是在mysql中开启了慢查询日志，设置的值是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段）

参考问答

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

可以利用explain或者desc命令获取mysql如何执行select语句的信息

sql语句执行很慢，如果分析呢？

可以采用mysql自带的分析工具explain
通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）
通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或者全盘扫描
通过extra建议判断，是否出现了回表的情况；如果出现了，可以尝试添加索引或修改返回字段来修复

参考问答

什么是索引？

索引是帮助mysql高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式指向数据，这种数据结构就是索引

比如说如果数据库使用二叉排序树

索引的底层数据机构了解过吗？

B+树

B树

B+树

B树和B+树对比

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

什么是索引？
索引是帮助mysql高效获取数据的数据结构
提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排列，降低数据排序的成本，降低了CPU的消耗

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

参考问答

什么是聚簇索引（聚集索引），什么是非聚簇索引（非聚集索引），什么是回表

可以看到，聚簇索引每个叶子节点存储的都是一整行的数据；下面的图中给name字段添加索引，那么索引叶子节点中存储的是id

回表查询

比如说查Arm的数据，到二级索引的叶子节点后拿到了id，再到聚集索引中查找，这个过程叫回表查询

什么是聚簇索引？什么是非聚簇索引？
聚集索引：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个
非聚簇索引：数据与索引分开存储，B+书的叶子节点保存对应的主键，可以有多个

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

参考问答

什么是覆盖索引？

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

id为主键索引，也是聚集索引，因此第一个sql语句虽然查所有的列，但是索引中都包含
第二个sql语句，查询的都是索引列
第三个sql语句，gender字段需要回表查询

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

mysql超大分页

优化思路

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

mysql超大分页如何处理？
在数据量比较大时，limit分页查询，需要对数据进行排序，效率低
覆盖索引+子查询的方式优化

参考问答

索引创建原则有哪些

• 主键索引
• 唯一索引
• 根据业务创建的索引（复合索引）

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

索引创建原则有哪些
（在自己的项目中是如何使用索引的）
1、数据量大，且查询比较频繁的表建立索引（单表超过10万数据）
2、针对常作为查询条件where、排序orderby、分组group by操作的字段建立索引
3、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
4、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率

参考问答

什么情况下索引会失效？

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

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

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

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

mysql的查询优化器，会自动进行类型转换，造成索引失效

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

当时项目中是有一个查询优惠券的一个接口，title字段使用了索引，使用模糊查询查优惠券，%优惠券，结果走的是全盘扫描，此时索引失效，

参考问答

sql优化的经验

谈一谈对sql的优化经验

参考问答

Mysql其他面试题

事务的特性是什么？可以详细说一下吗？

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

参考问答

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

并发事务

https://cloud.tencent.com/developer/article/1450773

解决

对事务进行隔离
事务隔离级别越高，数据越安全，性能越低

并发事务带来了哪些问题？怎么解决这些问题？mysql的默认隔离级别是
1、脏读：事务读到了另外一个事务还没有提交的数据
2、不可重复读：一个事务先后读取同一条记录，但是读取的数据不同
3、幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现了这行数据已经存在，好像出现了幻影

read uncommitted 未提交读
read committed 读已提交
repeatable read可重复读
serializable 串行化

参考问答

undo log和redo log的区别

redo log

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

undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC
undo log是逻辑日志，redo log是物理日志

undo log和redo log的区别

redo log：记录的是数据页的物理变化，服务宕机可用来同步数据
undo log：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数
redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

参考问答

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

使用排他锁，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁

mvcc:多版本并发控制（Multi-Version Concurrency Control），维护一个数据的多个版本，使得读写操作没有冲突
主要依赖于数据库记录中的隐式字段、undo log日志、readView

• 隐式字段
  

• undo log
  

• undo log版本链
  
  事务5如何提取呢

• readView

readView是快照读sql执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id，这里维护的事务具体如下：

包含了四个核心字段：

版本链数据访问规则

不同的隔离级别，生成ReadView的时机不同：
read committed:在事务中每一次执行快照读时生成readView
第一个读视图能够读取的事务数据

第二个读视图

repeatable read:仅在事务中第一次执行快照读时乘胜ReadView，后续复用该readView
（能够访问事务2的数据）

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

参考问答

mysql主从同步原理

mysql主从复制的核心就是二进制日志
二进制日志（binlog）记录了所有的DDL和DML语句，但不包括数据查询语句（select、show）语句

主从同步原理

mysql主从复制的核心就是二进制日志binlog，包括DDL和DML语句
1、主库在事务提交时，会将数据变化记录在binlog中
2、从库读取主库的二进制日志文件binlog，写入从库的中间日志relay log中
3、从库重做中继日志中的事件

参考问答

项目用过分库分表吗？

分库分表的时机：

• 项目业务数据逐渐增多，单表的数据量达到1000w
• 优化已解决不了性能问题（主从读写分离、查询索引）
• IO瓶颈包括磁盘IO、网络IO、CPU瓶颈包括聚合查询、连接数太多

拆分策略

垂直分库

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

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

垂直分表

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

特点：
1、冷热数据分离
2、减少IO过渡争抢，两表互不影响

水平分库

将一个库的数据拆分到多个库中
特点：
1、解决了单库大数量、高并发的性能瓶颈问题
2、提高了系统的稳定性和可用性

水平分表

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

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

新的问题和新的技术

新的问题：

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

新的技术：
分库分表中间件：

• sharding-sphere
• mycat
  

你们项目中用过分库分表吗？
1、业务介绍（根据简历上的项目，想一个数据量大的业务；达到了什么样的量级）
2、拆分策略

参考问答