MySQL面试题-2023

 1.什么情况下使用索引，什么情况下不使用索引？

 

2.如何给order_by加索引？

可以通过在order by语句中使用索引来优化查询性能。具体来说，可以使用CREATE INDEX语句为ORDER BY子句中的列创建索引。

 3、NOW（）和CURRENT_DATE（）有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。

CURRENT_DATE（）仅显示当前年份，月份和日期。

 3、主键索引与唯一索引的区别

(1).主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

(2).唯一性索引列允许空值，而主键列不允许为空值。

(3). 一个表最多只能创建一个主键，但可以创建多个唯一索引。

(4).主键索引和唯一索引都可以作为外键。

 4、MySQL中有哪些不同的表格？

共有5种类型的表格：

1、 MyISAM

2、 Heap

3、 Merge

4、 INNODB

5、 ISAM

 5、SQL的生命周期？

1、 应用服务器与数据库服务器建立一个连接

2、 数据库进程拿到请求sql

3、 解析并生成执行计划，执行

4、 读取数据到内存并进行逻辑处理

5、 通过步骤一的连接，发送结果到客户端

6、 关掉连接，释放资源

 6、你怎么看到为表格定义的所有索引？

 索引是通过以下方式为表格定义的：

SHOW INDEX FROM <tablename>;

7、数据库为什么使用B+树而不是B树？

1、 B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

2、B+树查询效率更高，数据都存储在叶子节点上，并且是一个双向链表的形式，使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历

8、数据库三大范式是什么？

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

9、怎么优化SQL查询语句？

1.尽量避免全表扫描，经常查询的字段、where条件字段或者使用order_by排序的字段，建立索引

2.SELECT子句中避免使用*号，查询对应的字段即可，尽量全部大写SQL

3.应尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用 IS NOT NULL

4.where 子句中使用 or 来连接条件，也会导致引擎放弃使用索引而进行全表扫描

5.in 和 not in 也要慎用，否则会导致全表扫描

10、覆盖索引、回表等这些，了解过吗？

1、 覆盖索引： 在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，

2、 回表：二级索引的B+树无法直接查询所有列的数据，所以需要通过查找主键，然后到主键索引(聚簇索引)的B+树里去查找具体的数据，这个过程就叫回表。(二级索引的B+树只存储主键值)

 11、MySQL数据库cpu飙升的话，要怎么处理呢？

1、 使用top 命令观察，确定是MySQLd导致还是其他原因。

2、 如果是MySQLd导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。

3、 找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

1、 kill 掉这些线程(同时观察 cpu 使用率是否下降)，

2、 进行相应的调整(比如说加索引、改 sql、改内存参数)

3、 重新跑这些 SQL。

其他情况：

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

 12、说说对SQL语句优化有哪些方法？（选择几条）

1、 Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

2、 用EXISTS替代IN、用NOT EXISTS替代NOT IN。

3、 避免在索引列上使用计算

4、 避免在索引列上使用IS NULL和IS NOT NULL

5、 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

6、 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

7、 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

 13、Innodb的事务与日志的实现方式

 有多少种日志

innodb两种日志redo和undo。原子性、隔离性、持久性、一致性。具体的日志与事务的原理，看mysql的事务篇与日志篇

14、非聚簇索引一定会回表查询吗？

不一定，如果查询的数据就在二级索引上，就不会进行回表查询，也就是说覆盖索引。比如，有一张学生表，用身份证号建立二级索引，我就只查询身份证号，就不用回表查询

15、Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理：

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据 。B+树，根节点到叶子节点，只有叶子节点才是存放真正的数据，其他节点存放索引，叶子节点是双向链表的形式，存储前一个元素和后一个元素的指引

优劣：

1、hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询

2、 hash索引不支持使用索引进行排序。

3、 hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。

 

在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

16、select for update有什么含义，会锁表还是锁行还是其他。

select for update 含义

select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁呢，而且它是悲观锁哦。至于加了是行锁还是表锁，这就要看是不是用了索引/主键啦。

没用索引/主键的话就是表锁，否则就是是行锁。

select for update 加锁验证

17、乐观锁与悲观锁（分表级的，还是行级的）。

在数据库中，乐观锁和悲观锁是两种并发控制机制，用于处理多个事务同时访问和修改相同数据的情况，以防止数据冲突和不一致性。 

悲观锁： 悲观锁假设在整个事务过程中，其他事务可能会修改数据，因此在进行读写操作时，悲观锁会将数据锁定，阻止其他事务对该数据的访问和修改，直到当前事务完成或释放锁。悲观锁主要通过数据库的锁机制实现，例如在MySQL中，可以使用SELECT ... FOR UPDATE来获取悲观锁。 示例： 假设有一个银行账户表（Account），其中有账户余额（Balance）字段。现在有两个用户同时想从同一个账户中取款，如果不使用悲观锁，可能会导致余额计算错误。

UserA：
BEGIN TRANSACTION;
SELECT Balance FROM Account WHERE AccountNumber = '123' FOR UPDATE;
-- 在这里假设对余额进行了计算，然后更新余额
UPDATE Account SET Balance = Balance - 100 WHERE AccountNumber = '123';
COMMIT;

User B (同时进行)：
BEGIN TRANSACTION;
SELECT Balance FROM Account WHERE AccountNumber = '123' FOR UPDATE;
-- 在这里假设对余额进行了计算，然后更新余额
UPDATE Account SET Balance = Balance - 150 WHERE AccountNumber = '123';
COMMIT;

在使用悲观锁的情况下，User B 的事务会等待 User A 的事务完成并释放锁后才能继续执行，从而避免了并发问题。

2. 乐观锁： 乐观锁假设在整个事务过程中，不会有其他事务对数据进行修改。因此，乐观锁不会阻止其他事务读取或修改数据，但在提交事务时，会检查在事务开始时是否有其他事务修改了相同的数据。如果检测到冲突，乐观锁会回滚当前事务，让用户处理冲突。

乐观锁一般通过在数据表中增加一个版本号（Version）字段或者使用时间戳来实现。在更新数据时，检查版本号或时间戳是否匹配，若匹配则更新数据并更新版本号，否则拒绝更新。

示例： 假设还是上述的银行账户表（Account），但这次使用乐观锁来处理并发更新余额的问题。

假设账户初始版本号（Version）为1，User A 和 User B 同时进行：

UserA：
BEGIN TRANSACTION;
SELECT Balance, Version FROM Account WHERE AccountNumber = '123';
-- 在这里假设对余额进行了计算，然后更新余额和版本号
UPDATE Account SET Balance = Balance - 100, Version = Version + 1 WHERE AccountNumber = '123' AND Version = 1;
COMMIT;

UserB(同时进行)：
BEGIN TRANSACTION;
SELECT Balance, Version FROM Account WHERE AccountNumber = '123';
-- 在这里假设对余额进行了计算，然后更新余额和版本号
UPDATE Account SET Balance = Balance - 150, Version = Version + 1 WHERE AccountNumber = '123' AND Version = 1;
-- 这里如果没有更新成功，表示版本号不匹配，可能是其他事务已经更新了数据
COMMIT;

在使用乐观锁的情况下，User B 的事务不会被阻塞，但在更新数据时会检查版本号是否匹配，如果不匹配则表示有冲突，事务会回滚，让用户处理冲突后重新尝试更新。

18、你们数据库是否支持emoji表情存储，如果不支持，如何操作？

更换字符集utf8-->utf8mb4

19、最左匹配原则？

在创建联合索引时候，一般需要遵循最左匹配原则。即联合索引中的属性识别度最高的放在查询语句的最前面。

20、列对比运算符是什么？

 在SELECT语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR或LIKE运算符。

<>：不等于运算符（Not Equal To）
SELECT * FROM products WHERE price <> 100;
返回所有价格不等于100的产品记录


<=>：NULL安全的相等运算符（NULL-Safe Equal To）
SELECT * FROM customers WHERE email <=> NULL;
返回所有邮箱地址为NULL的客户记录

<<，>>：位移运算符(二进制树，向左位移两位)：

SELECT value, value << 2 AS left_shifted_value
FROM numbers;

 

 

 

 重点：https://www.cnblogs.com/souyunku/p/15633392.html

 

 

 

 

参考链接：

https://blog.csdn.net/weixin_41622043/article/details/103426652

https://xiaolincoding.com/mysql/base/how_select.html