MySQL面试考点

最新推荐文章于 2024-09-15 08:03:51 发布

学习两年半的Java实习生

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量903

点赞数 26

分类专栏： Java 文章标签： mysql 面试数据库

本文链接：https://blog.csdn.net/weixin_43785387/article/details/139565106

版权

Java 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

MySQL面试考点

MySQL-优化

定位慢查询

在MySQL中，如何定位慢查询？

方案一：开源工具
调试工具：Arthas
运维工具：Prometheus、Skywalking

方案二：MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
如果要开启慢查询日志，需要在MySQL的配置文件中配置如下信息：
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间2秒，SQL语句执行时间超过2秒，就会是为慢查询，记录慢查询日志
long_query_time=2

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

聚合查询 => SQL执行计划可以找到慢的原因
多表查询 => SQL执行计划可以找到慢的原因
表数据量过大查询 => SQL执行计划可以找到慢的原因
深度分页查询

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

EXPLAIN SELECT * FROM t_user WHERE ID = '1'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user	NULL	const	PRIMARY	PRIMARY	98	const	1	100.00	NULL

possible_key 当前sql可能会使用到的索引
key 当前sql实际命中的索引
ken_len 索引占用的大小
Extra 额外的优化建议
type 这条sql的连接的类型，性能由好到差为NULL、SYSTEM、CONST、EQ_REF、REF、RANGE、INDEX、ALL
NULL：表示这条SQL语句没事用到表
SYSTEM：查询系统的表
CONST：根据主键查询
EQ_REF：主键索引查询或唯一索引查询
REF：索引查询
RANGE：范围查询
INDEX：索引树扫描
ALL：全盘扫描

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

索引：是帮助MySQL高效获取数据的数据结构（B+树 + 双向链表），这写数据结构以某种方式指向数据，这样就可以在数据结构上实现高级查询算法，这种数据结构就是索引。叶子节点记录数据，非叶子节点记录指针
索引的底层数据结构：B+树
在这里插入图片描述

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

聚簇索引：将数据存储于索引放到一起的，索引结构的叶子节点保存了行数据。必要要有并且只有一个
二级索引：将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键。可以存在多个

聚簇索引选取规则：
如果存在主键，主键索引就是聚簇索引
如果不存主键，将使用第一个唯一（UNIQUE）索引最为聚簇索引
如果表没有主键并且没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚簇索引

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

知道什么叫覆盖索引嘛？

覆盖索引：查询使用了索引，并且需要返回的列，在该索引中已经全部能找到。（不需要回表查询就是覆盖索引）

MySQL超大分页怎么处理？（实际就是问覆盖索引）

# 这条SQL会进行分页，它会将前9000010进行排序，但仅仅返回9000000-9000010的记录，其他记录丢弃，查询排序的代价非常大
SELECT * FROM tb_sku limit 9000000,10;

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

SELECT * 
FROM tb_sku t,
(SELECT id FROM tb_sku ORDER BY id LIMIT 9000000,10) a
WHERE t.id = a.id;

解决方案：覆盖索引+子查询

索引创建原则有哪些？

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

什么情况下索引会失效？

违反最左前缀法则
范围查询右边的列，不能使用索引
不要在索引列上进行运算操作，索引将失效
字符串不加单引号，造成索引失效
以%开头的Like模糊查询，索引失效。

谈一谈你对SQL的优化的经验

表的设计优化（参考阿里开发手册《嵩山版》）
① 比如设置合适的数值（tinyint int bigint），要根据实际情况选择
② 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低
SQL语句优化
① SELECT语句避免使用SELECT *
② SQL语句避免造成索引失效的写法
③ 尽量使用UNION ALL代替UNION UNION会多一次过滤，效率低
④ 避免在WHERE子句中对字段进行表达式操作
⑤ JOIN优化能有INNER JOIN就不用LEFT JOIN RIGHT JOIN，如必须使用一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。LEFT JOIN或RIGHT JOIN，不会重新调整顺序
主从复制、读写分离
索引优化，索引创建原则
分库分表

MySQL-其他面试题

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

事务：是一组操作的集合，它是不可分割的工作单位，事务会把所有的操作作为一个整体向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
A：原子性，事务时不可分割的最小操作单元，要么全部成功，要么全部失败
C：一致性，事务完成时，必须是所有的数据都保持一致的状态
I：隔离性，数据库系统提供的隔离机制，保证事务不在受外部并发操作影响的独立环境下运行
D：持久性，事务一旦提交或回滚，它对数据库的数据的改变就是永久的

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

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

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

隔离级别	脏读	不可重复读	幻读
未提交读	√	√	√
读已提交	×	√	√
可重复读	×	×	√
串行化	×	×	×

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

undo log和redo log的区别

redo log：重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
undo log：回滚日志，用于记录数据被修改前的信息，作用包含两个，提供回滚和MVCC（多版本并发控制）。undo log和redo log记录物理日志不一样，他是逻辑日志。可以实现事务的一致性和原子性