MySQL面试题分享

慢日志（了解）

慢日志开启的变量：slow_query_log='ON'; 如果值为 OFF ,那就是没有开启慢日志

耗时： long_query_time，默认是10秒

redis 和 mysql 慢日志的区别

redis 慢日志默认是没有开启的

mysql 慢日志默认是开启的

慢日志的输出格式：

TABLE 和 FILE

推荐使用 TABLE :从便利程度而言，它更方便于我们分析

-- 查看慢日志的配置
SHOW VARIABLES LIKE '%query%'


#一次性的，通过命令设置, long_query_time 是时间阈值。为方便测试，此处认为超过0.001s的就属于慢查询
-- 输出日志以 文件 的方式存储
mysql> SET GLOBAL log_output = 'FILE';
-- 输出日志以 数据表 的方式存储
mysql> SET GLOBAL log_output = 'TABLE';
-- 是否开启慢日志  【ON是开启的】
mysql> set GLOBAL slow_query_log=ON; 
-- 耗时
mysql> SET GLOBAL long_query_time = 0.001;
-- 以文件存储慢日志的位置
mysql> SET GLOBAL slow_query_log_file = '/usr/local/mysql/mysql-8.0/logs/slow_query.log';


-- -------------------------------------------------
-- 睡眠时间
select sleep(11);

输出日志的详情
select * from mysql.slow_log ORDER BY start_time desc

Explain优化

通过 skywalking 和 ELK 接口耗时查数据库表中哪些慢，或者通过 xxl_job 中的 mysql 慢日志查看，数据库慢就需要 Explain 进行分析

UNION 与 UNIONALL 的区别:

UNION：会去除两个结果集中重复的项

UNIONALL：不会去除

type 列（重点，必会）

级别都是从高到低的

system：表示只有一行的表，通常是系统表

const（常量）：表示从索引只能匹配到一行数据（性能最高的）【explain select * from student where id = 1688】

eq_ref：（关联查询）表示使用了等值连接（例如，使用主键或唯一索引连接表）【explain SELECT * FROM student s1 JOIN student s2 ON s1.id = s2.id WHERE s1.age = 25】

ref：表示使用了非唯一索引进行查找，并返回匹配的多行或一行数据 【explain select * from student where name = '张68'】

range：表示使用了索引进行范围查找，例如使用比较符（>, <, BETWEEN）或IN操作符 【explain select * from student where age < 1688】

index：表示全索引扫描，也就是说用了某一个索引的全部, 通常发生在查询使用索引覆盖的情况下 【explain select count(*) from student ；explain select sum(age) from student 】

all：表示全表扫描，即没有使用索引，需要遍历整个表进行查询 【explain select * from student】

MySQL

聚集索引和非聚集索引

聚集索引（主键索引）：索引和数据保存在同一个B+Tree（叶子节点）中（都是存放在一块的），因此从聚集索引中获取数据通常比在非聚集索引中查找要快。【主键 id】

非聚集索引（非主键索引） ：当需要查询索引以外的数据，就需要进行回表，性能比较差，如果不用回表就可以拿到用户需求的信息时，那就是索引覆盖，性能会较强【除主键索引外全都是非聚集索引 】

聚合函数（必会）

聚合函数可以用来统计、求和、求最值等等

分类：

COUNT：统计行数量

SUM：获取单个列的合计值

AVG：计算某个列的平均值

MAX：计算列的最大值

MIN：计算列的最小值

char 和 varchar 的区别

char：长度是固定的

varchar：长度是可变的，实际长度是根据字段进行判断

SQL 关键字 (必会)

分页：limit 【SELECT * FROM student limit 100,6; 查询学生表中数据，跳过100条，从第101条开始显示，取6 条】

倒序：desc 【select * from user order by id desc limit 0 6】

分组：group by （having）【SELECT sex , count(*) FROM student group by sex 】 根据性别查男生女生数量

去重：distinct 【select distinct name FROM student】

SQL Select 语句完整的执行顺序: (必会)

查询中用到的关键词主要包含如下展示，并且他们的顺序依次为

from...left join...on...where...group by...having..select...avg()/sum()...distinct...order by...asc/desc...limit...

from: 需要从哪个数据表检索数据

where: 过滤表中数据的条件

group by: 如何将上面过滤出的数据分组算结果

order by : 按照什么样的顺序来查看返回的数据

数据库三范式 (掌握 重点)

第一范式（1NF ）： 原子性，列或者字段不能再分，要求属性具有原子性，不可再分解

第二范式（2NF ）：

1、 每一行数据有唯一的主键

2、 非主键字段必须依赖于主键字段 【主要解决 行 的冗余】

第三范式（3NF ）：非主键字段不依赖于其它非主键字段 【主要是解决 列 的冗余 】

扩展：第三范式有时项目不遵循，

存储引擎

MyISAM 存储引擎 与 InnoDB 引擎区别

使用最多的的是 InnoDB

	MyISAM	InnoDB
事务支持	不支持事务处理	支持事务处理，可以通过使用事务来确保数据的完整性和一致性。
锁的粒度	（粒度到表）表级锁在执行 SELECT 操作时会对表进行读锁定，而执行 INSERT、UPDATE 或 DELETE 操作时会对表进行写锁定，因此在写操作执行时，读操作会被阻塞	（粒度到行）支持行级锁，不会对整个表进行锁定，可以减少锁定冲突和死锁的发生
外键的支持	不支持外键约束	支持外键约束，还可以通过外键约束来保证数据的引用完整性
并发性能		并发性能高

数据库事务（必会）

事务的特性：

原子性（A）：即不可分割性，事务要么全部被执行，要么就全部不被执行。

一致性（C）：事务必须使数据库从一个一致性状态变换到另一个一致性状态，即一个事务执行之前和执行之后都必须处于一致性状态

隔离性（I）：即一个事务执行之前和执行之后都必须处于一致性状态。

持久性（D）：事务一旦结束,数据就持久到数据库。

redo_log：解决持久性

undo_log：解决一致性和原子性

隔离级别：

1、读未提交：当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。这时就会产生脏读（读到别人没有提交的数据，造成回滚了）

脏读：所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

2、读已提交：针对 update 或 delete ，在事务处理期间，如果其他事务修改了相应的数据，那么同一个事务的多个 SELECT 语句可能返回不同的结果。在一个事务内，能看到别的事务提交的数据。出现不可重复读。

3、可重复读：针对 insert ，是 MySQL 的默认隔离级别，它确保了一个事务中多个实例在并发读取数据的时候会读取到一样的数据；不过理论上，这会导致另一个棘手的问题：幻读 （通过加锁机制，但新加的数据没有上锁，就会出现幻读）

解决办法：InnoDB 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决幻读。

幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。

4、可串行化：就是一个一个来，性能最慢

索引：

索引的概念和优点（必会）

索引概念：索引存储在磁盘上，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。 空间换时间

索引目的：提高检索效率，实现空间换时间

优点：加快查询效率

缺点（重要）：1、占用内存空间 2、影响增删改，效率低下

索引的分类（必会）

(1) 普通索引：最基本的索引，它没有任何限制。（值可重复）

(2) 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。（会员表里面的手机号，身份证号）

(3) 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。

(4) 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。（手机号和密码）商品表中的商品分类和状态

(5) 全文索引（一般用ElasticSearch做搜索引擎库）：老版本 MySQL 自带的全文索引只能用于数据库引擎为MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 1支持全文索引。默认 MySQL不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

索引的底层实现原理（高薪常问）

Mysql 目前提供了以下 4 种索引:

B+Tree 索引: 最常见的索引类型, 大部分索引都支持 B+树索引.

Hash 索引: 只有 Memory 引擎支持, 使用场景简单.

R-Tree 索引(空间索引): 空间索引是 MyISAM 引擎的一个特殊索引类型, 主要地理空间数据, 使用也很少.

S-Full-text(全文索引): 全文索引也是 MyISAM的一个特殊索引类型, 主要用于全文索引, InnoDB 从 Mysql5.6 版本开始支持全文索引.

以下是B树与B+树的主要区别：（高薪面试题）

1、 存储数据的位置:

• B树: 数据既存储在所有节点中（叶子节点和非叶子节点都有数据）
• B+树: 所有的数据记录都存储在叶子节点中，非叶子节点仅包含索引信息。叶子节点包含了完整的数据和索引键。

2、 叶子节点之间的链接:

• B树: 叶子节点之间没有链接。
• B+树: 叶子节点之间通过指针相互链接，形成一个链表或循环链表，这使得范围查询和遍历变得高效。

索引失效的几大情况（高薪常问）

1、范围条件查询，命中率大于百分之三十就不走索引，直接扫描全表

2、索引列上操作（使用函数、计算等）导致索引失效

3、字符串不加引号, 造成索引失效

4、尽量使用索引覆盖,避免 select *, 这样能提高查询效率.

explain select a ge,name,id,tel  from tb where age=22 and  name ='张三'

5、or 关键字连接（只要有一个or条件没有索引就全表扫描）【查找时只要有一个条件没有索引时就会全表扫】

6、使用 !=

7、like以通配符开头('%abc...')导致索引失效【通配符% ('%abc...')左右匹配不到也会导致索引失效】

8、排序列包含非同一个索引的列

用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序

索引扩展：

问题：索引时使用算法会导致索引失效，如果我需要在数据库中看一个商品，他只有生产日期，我怎么知道它什么时候能过期，怎么解决呢

冗余一个字段，添加上他的生产日期和保质期【根据派生列，在入库时就把过期时间计算出来，再插入到数据库中】

数据库锁(高薪常问)

数据库锁有哪些 ：行锁、表锁、库锁、悲观锁和乐观锁

1.行锁和表锁

1.1主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁

行锁：访问数据库的时候，锁定整个行数据， 防止并发错误。

表锁：访问数据库的时候，锁定整个表数据，防止并发错误。

1.2行锁 和 表锁 的区别：

表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

行锁： 开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2.悲观锁和乐观锁

（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。【在开发时一般是我们开发人员自己去赋值的，一般需要 Version 版本号控制，里面用到重试机制，请求次数超过三次不成功就会报错】

索引优化(高薪)

原则：

• 对查询频次较高, 且数据量比较大的表, 建立索引.
• 索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
• 使用唯一索引, 区分度越高, 使用索引的效率越高，能建唯一索引就建唯一索引，或者普通索引
• 索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率. 不是越多越好
• 使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.
• 如果 where 后有多个条件经常被用到, 建议建立复合索引, 复合索引需要遵循最左前缀法则, N 个列组合而成的复合索引, 相当于创 建了 N 个索引.

表的设计规则：

表的表名小写，表字段名称小写（一般 lastUpBy 需要用 last_up_by 这种格式，为什么不用呢：如果数据库使用 last_up_by ，就需要 mapper 文件去映射一个 mapping，如果要根据 Java 属性保持一致，lastUpBy就省去了这一部分），每个表都必须有主键 ID，尽量用 int（数字）