MySQL常见面试题

MySQL存储引擎

1、InnoDB存储引擎

默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀。
数据存储在共享表空间，可以通过配置分开。也就是多个表和索引都存储在一个表空间中，可以通过配置文件改变此配置。
对主键查询的性能高于其他类型的存储引擎。
内部做了很多优化，从磁盘读取数据时会自动构建hash索引，插入数据时自动构建插入缓冲区。
通过一些机制和工具支持真正的热备份。
支持崩溃后的安全恢复。
支持行级锁。
支持外键。

2、MyISAM存储引擎

拥有全文索引、压缩、空间函数。
不支持事务和行级锁、不支持崩溃后的安全恢复。
表存储在两个文件，MYD和MYI。
设计简单，某些场景下性能很好，例如获取整个表有多少条数据，性能很高。
全文索引不是很常用，不如使用外部的ElasticSearch或Lucene。

MySQL锁机制

表锁是日常开发中的常见问题，因此也是面试当中最常见的考察点，当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。共享锁和排他锁，就是读锁和写锁。

共享锁（Shared Lock S锁，读锁）
- 不堵塞，多个用户可以同时读一个资源，互不干扰，且阻止其他事务修改该数据
- 一个事物在读数据，其他事物也能读但是不能改写
排他锁（Exclusive Lock X锁，写锁）
- 一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源
- 一个事务在对数据改写，其他事物不能读也不能改写

锁的粒度

表锁，系统开销最小，会锁定整张表，MyIsam使用表锁。
行锁，最大程度的支持并发处理，但是也带来了最大的锁开销，InnoDB使用行锁。

常见问题梳理

请说明InnoDB和MyISAM的区别

InnoDB支持事务，MyISAM不支持
InnoDB数据存储在共享表空间，MyISAM数据存储在文件中
InnoDB支持行级锁，MyISAM只支持表锁
InnoDB支持崩溃后的恢复，MyISAM不支持
InnoDB支持外键，MyISAM不支持
InnoDB不支持全文索引，MyISAM支持全文索引

MyISAM索引与InnoDB索引的区别？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

innodb引擎的特性

插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

索引对性能的影响

大大减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机I/O变顺序I/O
大大提高查询速度
降低写的速度（不良影响）
磁盘占用（不良影响）

索引的类型

索引很多种类型，是在MySQL的存储引擎实现的

普通索引：最基本的索引，没有任何约束限制
唯一索引：和普通索引类似，但是具有唯一性约束
主键索引：特殊的唯一索引，不允许有空值

MySQL索引的创建原则

最适合创建索引的列是出现在WHERE或ON子句中的列，或连接子句中的列而不是出现在SELECT关键字后的列
索引列的基数越大，数据区分度越高，索引的效果越好
对于字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间
根据情况创建联合索引，联合索引可以提高查询效率
避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率
主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率

创建MySQL联合索引应该注意什么？

需遵循前缀原则

列值为NULL时，查询是否会用到索引？

在MySQL里NULL值的列也是走索引的。当然，如果计划对列进行索引，就要尽量避免把它设置为可空，MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂

优化查询过程中的数据访问

访问数据太多导致查询性能下降
确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
确认MySQL服务器是否在分析大量不必要的数据行
避免犯如下SQL语句错误
查询不需要的数据。解决办法：使用limit解决
多表关联返回全部列。解决办法：指定列名
总是返回全部列。解决办法：避免使用SELECT *
重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
是否在扫描额外的记录。解决办法：
使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
改变数据库和表的结构，修改数据表范式
重写SQL语句，让优化器可以以更优的方式执行查询。

优化长难的查询语句

一个复杂查询还是多个简单查询
MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。
切分查询
将一个大的查询分为多个小的相同的查询
一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。
分解关联查询，让缓存的效率更高。
执行单个查询可以减少锁的竞争。
在应用层做关联更容易对数据库进行拆分。
查询效率会有大幅提升。
较少冗余记录的查询。

优化特定类型的查询语句

count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)
MyISAM中，没有任何where条件的count(*)非常快。
当有where条件时，MyISAM的count统计不一定比其它引擎快。
可以使用explain查询近似值，用近似值替代count(*)
增加汇总表
使用缓存

优化关联查询

确定ON或者USING子句中是否有索引。
确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

优化子查询

用关联查询替代
优化GROUP BY和DISTINCT
这两种查询据可以使用索引来优化，是最有效的优化方法
关联查询中，使用标识列分组的效率更高
如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。
WITH ROLLUP超级聚合，可以挪到应用程序处理

优化LIMIT分页

LIMIT偏移量大的时候，查询效率较低
可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

优化UNION查询

UNION ALL的效率高于UNION

优化WHERE子句

解题方法

对于此类考题，先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

SQL语句优化的一些方法？

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

1	select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

1	select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

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

1	select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。
如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

1	select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

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

1	select id from t where num/2=100应改为:select id from t where num=100*2

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

1	select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为:select id from t where name like ‘abc%’

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

Mysql事务了解吗，隔离级别呢，每种隔离级别会出现什么问题？

MySQL 事务

事务是数据库操作的逻辑单元，它保证一组 SQL 语句要么全部执行成功（提交，Commit），要么全部失败回滚（Rollback），确保数据的一致性。
事务具有 ACID 特性：

A（Atomicity）原子性：事务是不可分割的最小单位，要么全部成功，要么全部失败。
C（Consistency）一致性：事务执行前后，数据库从一个合法状态变为另一个合法状态（如转账前后总金额不变）。
I（Isolation）隔离性：多个事务并发执行时，一个事务不应影响其他事务。
D（Durability）持久性：事务一旦提交，对数据的修改是永久性的，即使系统崩溃也不会丢失。

事务隔离级别（Isolation Levels）

MySQL 支持 4 种隔离级别，用于控制并发事务之间的可见性，防止数据不一致问题。隔离级别从低到高依次为：

隔离级别	脏读（Dirty Read）	不可重复读（Non-Repeatable Read）	幻读（Phantom Read）	适用场景
READ UNCOMMITTED（读未提交）	✅ 可能发生	✅ 可能发生	✅ 可能发生	几乎不用，性能最高但数据最不安全
READ COMMITTED（读已提交）	❌ 防止	✅ 可能发生	✅ 可能发生	Oracle 默认级别，适合大多数 OLTP 系统
REPEATABLE READ（可重复读）	❌ 防止	❌ 防止	✅ 可能发生（但 MySQL InnoDB 通过 MVCC + 间隙锁避免了幻读）	MySQL 默认级别
SERIALIZABLE（串行化）	❌ 防止	❌ 防止	❌ 防止	完全串行执行，性能最差，但数据最安全

READ UNCOMMITTED（读未提交）
- 事务可以读取其他事务尚未提交的修改（即“脏读”）
READ COMMITTED（读已提交）
- 事务只能读取其他事务已经提交的修改（避免脏读）
REPEATABLE READ（可重复读）
- 只锁定已存在的行，不阻止其他事务插入新数据，所以有可能出现幻读
- 如果加上MVCC + 间隙锁，那么事务 A 在整个事务期间看到的数据是“快照”（Snapshot），所以在同一个事务内，多次读取同一数据的结果是一致的，即使其他事务在此期间修改了该数据并提交，也不会影响当前事务看到的数据。
SERIALIZABLE（串行化）
- 最高的隔离级别，所有事务串行执行（完全隔离），性能最低，但数据最安全。通过 完全加锁 的方式避免所有并发问题。

并发事务可能引发的问题

脏读（Dirty Read）
- 问题：事务 A 读取了事务 B 未提交的数据，如果事务 B 回滚，事务 A 读到的就是“脏数据”。
- 示例：事务 A 读取了事务 B 修改但未提交的余额，结果事务 B 回滚，事务 A 看到的余额是错误的。
- 解决：READ COMMITTED 及以上级别可避免。
不可重复读（Non-Repeatable Read）
- 问题：事务 A 多次读取同一数据，期间事务 B 修改并提交了该数据，导致事务 A 两次读取结果不一致。
- 示例：事务 A 第一次查询余额是 100，事务 B 修改余额为 200 并提交，事务 A 第二次查询余额变成 200。
- 解决：REPEATABLE READ 及以上级别可避免。
幻读（Phantom Read）
- 问题：事务 A 多次查询同一范围的数据，期间事务 B 插入或删除了符合该范围的数据，导致事务 A 两次查询结果的行数不一致（如新增或减少记录）。
- 示例：事务 A 查询年龄 > 20 的用户有 5 条，事务 B 插入一条年龄 25 的记录并提交，事务 A 再次查询发现变成 6 条。
- 解决：SERIALIZABLE 可完全避免，MySQL 的 REPEATABLE READ 通过 MVCC（多版本并发控制） 和 间隙锁（Gap Lock） 也能避免大部分幻读。

这个不可重复读和幻读之间的区别是

问题类型	不可重复读（Non-Repeatable Read）	幻读（Phantom Read）
操作类型	针对同一行数据的修改（UPDATE）	针对符合查询条件的新增/删除（INSERT/DELETE）
数据变化	某一行数据的值变了（如余额从 100 → 200）	结果集的行数变了（如从 5 条 → 6 条）
锁定范围	锁定已存在的行	锁定符合条件的行范围（包括“间隙”）