MySQL口试题分享

慢日志（相识）

慢日志开启的变量：slow_query_log='ON'; 如果值为 OFF ,那就是没有开启慢日志
耗时： long_query_time，默认是10秒

redis 和 mysql 慢日志的区别

redis 慢日志默认是没有开启的
mysql 慢日志默认是开启的
慢日志的输出格式：

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

1. -- 查看慢日志的配置
2. SHOW VARIABLES LIKE '%query%'
5. #一次性的，通过命令设置, long_query_time 是时间阈值。为方便测试，此处认为超过0.001s的就属于慢查询
6. -- 输出日志以 文件 的方式存储
7. mysql> SET GLOBAL log_output = 'FILE';
8. -- 输出日志以 数据表 的方式存储
9. mysql> SET GLOBAL log_output = 'TABLE';
10. -- 是否开启慢日志  【ON是开启的】
11. mysql> set GLOBAL slow_query_log=ON;
12. -- 耗时
13. mysql> SET GLOBAL long_query_time = 0.001;
14. -- 以文件存储慢日志的位置
15. mysql> SET GLOBAL slow_query_log_file = '/usr/local/mysql/mysql-8.0/logs/slow_query.log';
18. -- -------------------------------------------------
19. -- 睡眠时间
20. select sleep(11);
22. 输出日志的详情
23. 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 bysex 】 根据性别查男生女生数目
去重：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 *, 这样能提高查询效率.

1. 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（数字）

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。