MySQL面试内容2

1、MySQL面试内容

2、如何定位慢查询

*聚合查询

*多表查询

*表数据量过大查询

*深度分页查询

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

2.1 使用开源组件

调试工具： Arthas

运维工具： Prometheus、Skywalking

2.2 使用MySQL自带的监控

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

2.3 相关面试内容

如何定位慢查询？

1. 介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测结果大概是5秒钟）

2.我们系统中当时采用了运维工具（Skywalking），可以检测出哪个接口，最终因为是sql的问题。

3.在mysql中开启了慢日志查询，我们设置的值为2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段，上线后会影响性能）。

2.4 SQL执行很慢，如何分析

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

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

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；

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

possible_key 当前SQL可能会使用到的索引

key 当前sql实际命中的索引

key_len 索引占用的大小

Extra 额外的优化建议

通过key key_len 查看是否可能会命中索引。

extra	含义
Using where;Using Index	查找使用了索引，需要的数据都在索引中能找到，不需要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询数据

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

如果这个SQL执行的很慢，如何分析呢？

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

3、MySQL支持的存储引擎有哪些，有什么区别？

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

特性	MyISAM	InnoDB	MEMORY
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
外键	不支持	支持	不支持

MySQL体系结构

InnoDB存储的特点

介绍：

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

特点：

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

文件

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

4、关于存储引擎的面试

MySQL支持哪些存储引擎，他们的区别是什么？

• InnoDB 是mysql5.5 之后默认的存储引擎，支持事务、表级锁和行级锁、外键 使用比较多
• myISAM 是早起的存储引擎，不支持事务 只支持表级锁 。
• memory 主要把数据存储在内存中，支持表级锁，不支持事务和外键，用的不多。

6、关于索引内容

6.1 什么是索引

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

6.2 索引的底层数据结构

B+树

数据结构对比

B-Tree

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5（5阶）的b-tree为例，那这个B树每个节点最多存储4个key。

B树与B+树对比：

1、磁盘读写代价B+树更低； 2、查询效率B+树更加稳定；3、B+树便于扫库和区间查询。

6.3 存储结构的面试内容

1、了解过索引吗？

索引在项目中还是比较常见的，它帮助MySQL高晓获取数据的数据结构。提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序成本，也能降低了CPU的消耗

2、索引的底层数据结构了解过吗

MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+数的主要的原因：第一阶数更多，路径更短；第二磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据；第三个B+树便于扫库和区间查询，叶子节点是一个双向链表。

3、B树和B+树的区别是什么？

第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有数据都会存放在叶子节点，在查询的时候，B+树查找效率更加稳定。

第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表。

7、索引

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

聚集索引：将数据和索引放到了一块，索引结构的叶子节点保存了行数据。 特点：有且只有一个

二级索引：将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键。 特点：可以有多个。

聚簇索引的选取规则：

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

索引面试内容：

知道什么是回表查询吗？

回表跟聚簇索引和非聚簇索引是有关系的，回表的意思是通过二级索引查询到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

知道什么是覆盖索引吗

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

（非覆盖索引需要回表，使用id查询，直接走聚集索引，一次索引扫描，直接返回数据）

MYSQL超大分页怎么处理

可以使用覆盖索引解决

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

select * from tb_sku limit 9000000,10;

当分页查询时，如果执行 limit 90000000,10，此时需要MySQL排序前90000010记录，

仅仅返回9000000-90000010的记录，其它记录丢弃，查询排序的代价非常大。

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

select * 
from tb_sku t,
  (select id from tb_sku order by id limit 9000000000,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）不要在索引列上进行运算操作，索引将失效

explain select * from tb_seller where substring(name,3,2) = '科技'

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

由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

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

5）范围查询右边的列会索引失效。

SQL优化的经验

MySQL体系结构