mysql常见面试问题

L.S.V.

已于 2022-04-07 16:42:34 修改

阅读量1.4k

点赞数 1

分类专栏： mysql 文章标签： java

于 2022-04-07 15:47:24 首次发布

本文链接：https://blog.csdn.net/weixin_45075231/article/details/124009849

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

测试数据：

1.count 数据丢失

当存在 NULL 值时，使用 count( 字段 ) 时，总数据会丢失。

2.distinct 数据丢失

使用 count(distinct col1,col2) 时即使一个字段无 NULL 值，另一个字段有，数据也会丢失。

3.select 数据丢失

如果某列存在 NULL 时，如果执行非等于查询 (<> !=) 会导致。

解决方式： SELECT * FROM user WHERE name <> "name1" or ISNULL(name)

4. 导致空指针异常

如果某列存在 NULL 时，可能会导致 sum(column) 的返回结果为 NULL 而非 0 ，如果 sum 结果为 NULL 可能导致程序空指针。

解决方式： SELECT IFNULL(sum(name),0)FROM user WHERE id>2

5.查询问题

如果某列中有 NULL 值，需要用 is null is not null 或 ISNULL(column) ! ISNULL(column)

is not null 不会忽略空字符串

如果某列中有空字符串，需要用 =' ' !=' '

！ = ' ' 会忽略 NULL

6.NULL 不会影响索引。

2.内外连接

内连接：只返回两个表的交集，匹配不到的不保留。外连接分三类：左外连接（ LEFT OUTER JOIN ）右外连接（ RIGHT OUTER JOIN ）和全外连接（ FULL

OUTER JOIN ）

左外连接：左连接的关键字是 left join ，左连接的基础表是 left join 的左侧数据表。是左表和右表的交集再并上左表不符合连接条件的数据( 没有匹配值返回空 ) 。

右外连接：右连接的关键字是 right join ，右连接的基础表是 right join 的右侧数据表。是左表和右表的交集再并上右表不符合连接条件的数据( 没有匹配值返回空 ) 。

全外连接：全连接的关键字是 FULL JOIN ，是左表和右表的交集再并上左表的不符合连接条件的数据再并上右表的不符合连接条件的数据( 没有匹配值返回空 ) 。

2.1内连接

2.2左外连接

2.3右外连接

2.4全外连接

3.sql执行顺序

1.from table

2.join on table

3.where

4.group by

5. 使用聚合函数计算

6.having 晒选分组后

7.select 的字段

8.order by

9.limit 分页

4.数据库引擎

InnoDB支持外键， Mylsam 不支持外键。

InnoDB支持事务， Mylsam 不支持事务。

InnoDB是聚簇索引， Mylsam 是非聚簇索引。

Mylsam支持 FULLTEXT 类型的全文索引。 InnoDB 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以通过sphinx 插件支持全文索引。 Mylsam 是表锁，一个更新语句会锁住整个表的数据， InnoDB 是行锁，一个更新语句会锁住这行数据。

Mylsam文件存储分为表的定义 .frm 文件和数据文件 .myd 和索引文件 .myi ， InnoDB 存储文件分为表的定义.frm 和 ibd 文件， ibd 文件是数据和索引存储文件，数据以主键进行聚集存储，真正的数据保存在叶子节点。

5.数据库锁

行锁：操作表中的一条数据会把这条数据锁住。

表锁：操作表中的一条数据会把这个表锁住。锁主要是加在索引上，如果对非索引字段更新，行锁可能

会变表锁。索引失效，行锁会变成表锁效率低要避免索引失效。

间隙锁：操作一个范围的数据会把这个范围的数据锁住。

6.数据库事务

数据库事务 (ACID)

原子性

事务中的一系列操作像原子一样是个整体不能分割，要么全部成功提交，要么全部失败回滚。如果事务中一个sql执行失败了，事务里已经执行得 sql 需要回滚恢复之前得状态，他的实现原理是 Undo Log 日志，需要把执行之前得状态记录下来，就跟上线之前版本代码需要备份，如果有问题可以回滚，当事务对数据库进行修改得时候，innodb 就会生成对应的 undo log 日志他会记录 sql 执行的相关信息，如果发生回滚innodb 根据 undo log 做相反的操作，比如 insert 操作，回滚的时候就会执行一个相反的操作

delete 。

持久性

事务一旦提交成功后，对数据库的修改是永久性的。他的实现原理通过redo log ，因为 mysql 的数据是存在磁盘中的，如果每次读数据都要经过磁盘IO ，那么他的效率特别低，所以 innodo 提供了一个缓存 buffffer， buffffer 中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲，当读取数据库时候就会先去buffffer 中取，如果 buffffer 中没有就会从磁盘中读取，读取完之后再放到 buffffer 缓存中，当向数据库中写入数据的时候，也会向buffffer 中写入数据，定期将 buffffer 中的内容刷新到磁盘上，进行持久化，这个就存在一个问题，虽然读写效率高了，但是存在丢失数据的风险，如果buffffer 中的数据还没来得及同步到磁盘上，这个时候mysql 宕机了，那么 buffffer 中的数据就会丢失掉，数据丢失持久化无法保证，然后 redo log buffffer就被引入进来解决这个问题，改进后当数据库写入数据时候，除了向 buffffer 中写入数据，还将这次操作记录到redo log 中，如果 mysql 发生宕机还有 redo log 恢复数据， redo log 的缓存区刷盘比buffffer 刷盘快， buffffer 是随机写的 io 而 redo log 是文件尾部追加模式顺序 IO ， buffffer 持久化是以 page数据页为单位大小 16k ，一个数据页的小小修改都需要把整个数据页写入， redo log 只需要写入真正需要的部分就可以了减少无效的IO 。他有几个持久化机制可以通过 innodb_flflush_log_at_trx_commit 去控制，分别为事务提交不会将redologbuffffer 写入磁盘日志，和提交事务同步写入磁盘和提交事务异

步写入磁盘。

隔离性

同一时间，一条数据只能有一个事务去访问，并发执行的事务不会互相影响，按顺序去执行。主要是写写操作和读写操作，读读不影响。两条事务同时对一行数据进行写操作，这个时候就需要排他锁来保证，获取到锁的事务执行完，其他事务才能抢到锁执行。

事务并发写读操作一条数据会发生脏读，不可重复读，幻读的情况，MVCC 去解决这个问题。

事务写读的并发问题

脏读：比如事务 A 读取了事务 B 更改的数据，然后事务 B 回滚操作，那么 A 读到的数据是脏数据。

不可重复读：比如事务 A 很多次读取同一条数据，这个过程 B 事务对这条数据更新并且提交，导致事务 A

多次读取同一数据结果不一致。

幻读：比如事务 A 查询表发现表没数据，之后事务 B 插入 1 条数据，事务再去查询表发现表有一条数据。

事务中第一次查和第二次查结果不一样。

区别：不可重复读重点在于 update 和 delete ，而幻读的重点在于 insert 。

MVCC

多版本并发控制，解决同一行数据读写发生冲突时上锁阻塞效率太慢， MVCC 可以在读写冲突时候不加锁而去处理读请求解决的。他的读不是加悲观锁方式的当前读，而是快照读多版本并发控制，不去竞争

锁提高性能。

数据库事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）常用	否	是	是
可重复读（repeatable-read）常用 mysql默认	否	否	是
序列化（serializable）	否	否	否

MVCC 通过 undolog 和版本链和 readview 实现的。

他的历史数据存储到 undolog 中，每条记录除了 id 和字段的数据还有事务 id 自增的标识 trx_id ，还有一个回滚指针roll_pointer 回滚到上一个版本用的。

因为版本链中一条数据有很多版本，我不知道去取哪个版本，readview 就是解决这个问题的作用。

读已提交和可重复读这两个隔离级别生成readview 的逻辑不一样。

读已提交每次查询都会生成一个readview ，可重复读是以事务为单位的。可重复读第一次查询会生成一个readview 。第二次查询会使用同一个 readview ，这就解决了不可重复读的问题，我两次读取的都是同一个readview ，其他事务修改不会影响这个事务的 readview ，不会因为其他事务修改导致两次查询结果不一致。而读已提交每次查询都会生成一个新的readview 所以其他是事务修改会导致数据不一致。

mysql默认的隔离级别是可重复读，通过 MVCC 解决了不可重复读这个问题。

MVCC不能完全解决 mysql 的幻读问题，如果都是快照读可以解决幻读问题，但是像增加修改删除操作会把快照读变成当前读，如果一个事务既有快照读，还有当前读，那么还是有可能发生幻读问题。如果是当前读的话，对读取到的范围加间隙锁，其他事务不能插入这个范围的数据解决幻读的问题。

一致性

事务开始前和结束后，数据库的完整性约束没有被破坏。事务提交后数据库的的完整性主键唯一，外键约束符合要求，字段类型大小长度符合要求。而且数据不能丢失比如A给 B 转账，转账后他俩总数不变，不会钱转丢了。

一致性是事务的最终目标，原子性，持久性，隔离性，都是为了保证数据库状态的一致性。

7.数据库索引

索引（Index ）是帮助 MySQL 高效获取数据的数据结构 (B+Tree) 。为经常筛选、排序、分组和联合操作的字段添加索引。EXPLAIN 关键字查询索引使用情况。

聚簇索引和非聚簇索引

聚簇索引 (innoDB) ：将数据和索引放一块存储，索引结构的叶子节点保存了行数据。像复合索引，前缀索引，唯一索引是在聚簇索引基础之上创建的。聚簇索引默认是主键，如果表中没有定义主键会选择一个唯一的非空索引代替，没有都没有innoDB 在内部会生成一个隐式的聚簇索引。

主键索引根据id 只查一遍，根据辅助索引查 id 也是只查一遍，查其他字段 * 需要根据 id 索引再查一遍。

非聚簇索引(Mylsam) ：将数据和索引分开存储，表数据存储顺序与索引顺序无关。

索引的类型

主键索引：值是唯一的，不可以有 null

唯一索引：值是唯一的，可以有 null

普通索引：没有限制

组合索引：多个字段形成的索引

覆盖索引： select 的列从索引中就能够获取到，不需要回表查询数据行

全文索引：对文本进行分词搜索

索引的数据结构原理

mysql 采用 B+Tree 结构的索引，为什么不采用其他索引。

Hash索引

Hash 表结构，如果是等值查找效率很快， Hash 值是无序的，所以他不能进行范围查找。进行排序操作也不能用hash 索引的 hash 值进行排序。还有比如我的值不同但是 hash 值相同，查找速度就比较慢了，他需要一个个去比对，相当于做全表扫描。

可视化： Closed Hashing Visualization (usfca.edu)

平衡二叉树

二叉树结构，他的左子树和右子树高度差不会超过 1 。随着树的高度增加，他的查找速度也越来越慢。范围查找特别慢，如果插入1-10 个树，查找大于 5 的，他会回旋去查找数据，如果这个范围的数据多会特别慢。

可视化： AVL Tree Visualzation (usfca.edu)

B Tree

一个节点可以存两个数据，他的树的高度会降低，查找速度会变快。也存在范围查找数据回旋查找问题。

可视化： B-Tree Visualization (usfca.edu)

B+Tree

mysql中数据都是存在磁盘上， InnoDB 为了减少磁盘 I/O ，把磁盘以页为单位来存储数据，要想查找速度快就需要减少查找次数来减少IO 次数提搞效率，所以使用了 B+Tree 的数据结构。非叶子节点存储的是表的主键和子节点的地址，叶子节点存的是表的主键和除了主键外的数据。

B+Tree和 B Tree 共同的特别一个叶子节点可以存两个值，树的高度变低查询速度快。

同时解决了回旋查找的问题，在B Tree 的基础上把所有的叶子节点进行一个链表的有序排序。比如我查大于5 通过树找到 5 就可以根据链表把大于 5 的数据全拿出来不需要回旋查找。

可视化： B+ Tree Visualization (usfca.edu)

索引失效场景

1.where 语句中包含 or 时，可能会导致索引失效。

需要看 or 左右两边的查询列是否命中相同的索引。

//USER表中的user_id列有索引,age列没有索引
select * from `user` where user_id = 1 or user_id = 2 //命中相同索引不会失效 
select * from `user` where user_id = 1 or age = 20;//无法命中索引 //age列也有索引的话 
select * from `user` where user_id = 1 or age = 20;//无法命中索引

2.where 语句中索引列使用了反向查询，可能会导致索引失效。

反向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。

3. 在索引列上使用内置函数，一定会导致索引失效。

select * from `user` where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;

4. 隐式类型转换导致的索引失效。低版本中，高版本是可以使用索引的。

//索引列user_id为varchar类型
select * from `user` where user_id = 12;//不会命中索引 
//因为MySQL做了隐式类型转换，调用函数将user_id做了转换。 
select * from `user` where CAST(user_id AS signed int) = 12;//不会命中索引

5. 隐式字符编码转换导致的索引失效

//两个表之间做关联查询时,如果两个表中关联的字段字符编码不一致的话,MySQL可能会调用CONVERT函数, 将不同的字符编码进行隐式转换从而达到统一。

6. 对索引列进行运算，一定会导致索引失效

+，-，*，/等 
select * from `user` where age - 1 = 10; 
//优化的话，要把运算放在值上，或者在应用程序中直接算好 
select * from `user` where age = 10 - 1;

7.like 通配符可能会导致索引失效

//like查询以%开头时，会导致索引失效。解决办法有两种：
// 将%移到后面 
select * from `user` where `name` like '李%';
//利用覆盖索引来命中索引。
select name from `user` where `name` like '%李%';

8. 联合索引中， where 中索引列违背最左匹配原则，一定会导致索引失效

当创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引，这 就是最左匹配原则。 
select * from t where k2=2;//不会命中索引
select * from t where k3=3;//不会命中索引 
select * from t where k2=2 and k3=3;//不会命中索引 
slect * from t where k1=1 and k3=3;//只会命中索引(k1):

9. 即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看 MySQL 优化器的

判断。

索引失效原理

数据库a 字段和 b 字段组成一个联合索引。联合索引一个节点上有多个键值对分别代表每个索引字段。而第一个字段在叶子节点的链表中是有序的，第二个字段在叶子节点的链表中是无序的，第二个字段在第一个字段相等的情况下是有序的。顺序类似于我们写sql 按两个字段去排序，先按照第一个字段去排序，如果第一个字段相等情况下，再按照第二个字段排序。

最佳左前缀法则失效原理

select * from testTable where a=1 and b=2 
首先a字段在B+树上是有序的，所以我们可以通过二分查找法来定位到a=1的位置。 
a确定的情况下，b是相对有序的，因为有序，所以同样可以通过二分查找法找到b=2的位置。
 select * from testTable where b=2 
b肯定是不能确定顺序,一个无序的B+树上是无法用二分查找来定位到b字段。

范围查询右边失效

select * from testTable where a>1 and b=2 
a字段在B+树上是有序的，所以可以用二分查找法定位到1,然后将所有大于1的数据取出来，a可以用到索引。
 
这个范围内b是无序的，不能使用二分查找法,b用不上索引。

like 索引失效原理

where name like "a%" 查询以"a"开头的数据 叫前缀 走索引 首字母有序其他无序
where name like "%a%" 查询数据中包含"a"的数据 叫中缀 不走索引 
where name like "%a" 查询以"a"为结尾的数据 叫后缀 不走索引

先按照第一个字母排序，如果第一个字母相同，就按照第二个字母排序，以此类推。

对查询进行优化尽量避免全表扫描，创建索引优化查询效率

遵循最左前缀法则避免索引失效场景。

mysql一次查询只能使用一个索引，如果对多个字段使用索引要建立复合索引。

尽量使用覆盖索引，查询语句减少select * 的使用，避免查找不必要的字段，浪费不必要的性能。覆盖索引就是查询字段和where 字段索引一致。

子查询exists 和 in 合理选择： in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。查询的两个表大小相当，那么用in 和 exists 差别不大。则子查询表大的用 exists，子查询表小的用 in 。

如果使用索引，尽量避免子查询，子查询将把结果放到临时表临时表可能用不到索引，改用关联查询。

如果关联查询：left join 时，尽量选择小表关联大表，减少 Loop 的循环次数。 inner join 会自动帮选择小表和大表。关联数据量太大的表，可以先筛选符合条件的创建临时表再去关联。关联查询where条件涉及到主表的列，创建索引，where 条件中涉及到从表列，可以创建组合索引。

优化order by 排序， order by 会把查出来的结果集放到临时表中在进行排序，会进行全表扫描。当 order by 字段出现在 where 条件中时或满足最左前缀，可以加入索引直接利用索引取到排好序的数据，不需要放入临时表排序。group by 的数据无序也是先排序后分组，能在 where 中筛选就不要去 having 中

筛选。

有时候索引和sql 没问题，但是 sql 优化器使用低效的索引， FORCE INDEX() 强制使用指定索引。

频繁更新的字段不适合创建索引：因为每次更新不单单更新记录，还更新索引，增加IO 。

8.主从复制原理

在实际的生产中，为了解决 Mysql 的单点故障和做负载均衡环境 mysql 服务的压力，一般都会采用主从复制和读写分离。

主从复制是异步进行的。master 开启 bin-log 日志文件用于记录数据库的增删改查。

Mysql的主从复制中主要有三个线程： master IO 线程， slave IO 线程 SQL 线程。

master IO 线程，主要负责 Master 库中有数据更新的时候，更新的内容写入到主库的 binlog 文件中。 Master会创建 log dump 线程通知 Slave 主库中存在数据更新。 Slave 接收到数据更新的消息， slave IO 线程把更新信息记录到 relay log （中继日志）中，当检测到 relay log 有日志更新，会开启 SQL 线程把 relay log更新内容同步到 slave 数据库中，完成主从数据的同步。