快捷键说明

MySQL高频面试题总结

一、索引与数据结构

1>索引的类型

①普通索引： 基本索引类型，允许为空值和重复值。
②唯一索引： 索引的值必须唯一，允许为空值。
③主键索引： 索引的值必须唯一，不可以为空。
④联合索引： 多个字段加索引，遵守最左匹配规则。
⑤全局索引： 只有在 MyISAM 引擎上才能使用。

2>索引的数据类型

Hash： 查询时调用Hash函数获得地址，回表查询实际数据。(InnoDB和MylSAM不支持，Memory支持)。
B+树： 每次从根节点出发去查询，然后得到地址，回表查询实际数据。

B+树(mysql默认)

B+树是一种平衡多路搜索树:

1. 叶子节点存储数据：所有数据都存储在叶子节点中，非叶子节点只存储索引（键值）。
2. 叶子节点通过指针连接：叶子节点之间通过指针连接，形成一个有序链表，支持高效的范围查询。
3. 高度平衡：B+树通过分裂和合并操作保持树的平衡，确保查询效率稳定。

优点:

1. 适合范围查询：
   • 由于叶子节点通过指针连接，范围查询（如BETWEEN、>、<）非常高效。
2. 查询性能稳定：
   • 由于树的高度平衡，查询时间复杂度为O(log n)，性能稳定。
3. 适合磁盘存储：
   • B+树的节点大小通常与磁盘块大小匹配，减少了磁盘I/O次数。

缺点

1. 插入和删除性能较低：
   • 插入和删除操作可能需要分裂或合并节点，导致性能开销较大。
2. 存储空间占用较大：
   • 非叶子节点只存储索引，叶子节点存储数据，整体存储空间较大。

适用场景

• 适合需要范围查询的场景（如时间范围查询、区间查询）。
• 适合磁盘存储的场景（如数据库索引）。

B+树 B树 Hash 对比

特性	B+树	B树	Hash索引
数据结构	平衡多路搜索树，叶子节点存储数据	平衡多路搜索树，节点存储数据	哈希表，键值对存储
查询性能	O(log n)，适合范围查询	O(log n)，适合等值查询	O(1)，适合等值查询
范围查询	支持高效范围查询	不支持高效范围查询	不支持范围查询
插入和删除性能	较低（需要分裂和合并节点）	较低（需要分裂和合并节点）	较高（哈希冲突时性能下降）
存储空间占用	较大（非叶子节点只存储索引）	较大（节点存储索引和数据）	较大（需要预留空间减少冲突）
适用场景	磁盘存储、范围查询	内存存储、等值查询	内存存储、等值查询

3>索引为什么使用树结构

可以加快查询效率，而且可以保持有序。

4>聚簇索引与非聚簇索引

聚簇索引（Clustered Index）和非聚簇索引（Non-Clustered Index）是数据库中两种不同的索引实现方式，它们在数据存储和查询性能上有显著差异。

InnoDB（聚簇索引）

• 适合需要事务支持的场景（如银行系统、电商系统）。
• 适合频繁查询和范围查询的场景。
• 适合高并发场景，支持行级锁。

MyISAM（非聚簇索引）

• 适合读多写少的场景（如日志系统、数据仓库）。
• 适合不需要事务支持的场景。
• 适合插入和更新较少的场景。

1.聚簇索引

• 聚簇索引将数据行存储在索引的叶子节点中，即索引和数据是绑定在一起的。
• 每个表只能有一个聚簇索引，因为数据行只能按照一种顺序存储。
• 在InnoDB中，主键索引就是聚簇索引。如果没有显式定义主键，InnoDB会选择一个唯一的非空索引作为聚簇索引；如果没有这样的索引，InnoDB会隐式创建一个隐藏的主键作为聚簇索引。

优点

1. 查询性能高：
   • 由于数据行和索引存储在一起，通过聚簇索引查询时可以直接获取数据，减少了额外的I/O操作。
   • 适合范围查询（如BETWEEN、>、<等），因为数据在物理上是连续存储的。
2. 减少磁盘I/O：
   • 数据行和索引存储在一起，减少了查询时需要访问的磁盘块数量。

缺点

1. 插入性能较低：
   • 插入新数据时，可能需要调整数据行的物理存储位置以保持顺序，导致插入性能下降。
2. 更新主键代价高：
   • 如果更新主键，可能需要移动数据行，导致性能开销较大。
3. 存储空间占用较大：
   • 由于数据和索引绑定在一起，聚簇索引的存储空间通常比非聚簇索引大。

2.非聚簇索引

• 非聚簇索引将索引和数据行分开存储，索引的叶子节点存储的是指向数据行的指针（如主键值或行ID）。
• 每个表可以有多个非聚簇索引。
• 在MyISAM中，所有索引都是非聚簇索引。索引的叶子节点存储的是数据行的物理地址（文件偏移量）。

优点

1. 插入性能高：
   • 插入新数据时，只需要更新索引结构，不需要调整数据行的物理存储位置。
2. 更新主键代价低：
   • 更新主键时，只需要更新索引，不需要移动数据行。
3. 适合多索引场景：
   • 一个表可以有多个非聚簇索引，适合需要频繁查询不同字段的场景。

缺点

1. 查询性能较低：
   • 查询时需要先通过索引找到数据行的指针，再通过指针访问数据行，增加了额外的I/O操作。
2. 范围查询性能较差：
   • 由于数据行在物理上是分散存储的，范围查询时可能需要多次磁盘I/O。

3.总结

• 聚簇索引在查询性能上有优势，但插入和更新性能较低，适合事务型应用。
• 非聚簇索引在插入和更新性能上有优势，但查询性能较低，适合读多写少的场景。
• InnoDB通过聚簇索引和行级锁提供了高并发和事务支持，而MyISAM通过非聚簇索引和表级锁提供了高效的读性能。

5>联合索引的最左前缀原则及索引失效场景

索引失效场景

场景1：跳过了最左列（查询条件没有从联合索引的最左列开始。）

场景2：使用了范围查询（在联合索引的某一列上使用了范围查询）

场景3：使用了函数或表达式 （在查询条件中对索引列使用了函数或表达式）

场景4：使用了OR条件（在查询条件中使用了OR连接多个条件）

场景5：数据类型不匹配（查询条件中的数据类型与索引列的数据类型不匹配。）

场景6：使用了LIKE通配符（在查询条件中使用了LIKE通配符，且通配符在开头。 WHERE a LIKE ‘%value’ ）

6>怎么查看是否使用到索引

使用explain

例如：explain select * from 表名 where 条件
结果：会查出key，key就是你使用的索引。还有type这个字段，可以看到索引是全表扫描还是索引扫描等等。
type字段内容性能对比：ALL < index < range ~ index_merge < ref < eq_ref < const < system

7>一条sql查询非常慢，怎么排查优化？

排查：
(1) 开启慢查询。
(2) 查看慢查询日志(定位低效率sql，命令：show processlist)。
(3) 使用explain查看sql的执行计划(看看索引是否失效或者性能低)

优化：
sql优化 + 索引 + 数据库结构优化 + 优化器优化

二、事务与隔离级别

原子性(Atomicity)： 要么全部成功要么全部失败。

一致性(Consistency)： 事务执行前和事务执行后，原本和数据库一致的数据仍然一致。

隔离性(Isolation)： 事务与事务之间互不干扰。

持久性(Durability)： 事务一旦被提交了，那么对数据库中的数据的改变就是永久的。

三、存储引擎与锁机制

MylSAM： mysql5.5之前的存储引擎，是表锁（悲观锁）级别的，不支持事务和外键。

InnoDB： mysql5.5之后的存储引擎，是行锁（乐观锁）级别的，支持事务和外键。

Memory： 内存数据库引擎，因为在内存操作，所以读写很快，但是Mysql服务重启，会丢失数据，不支持事务和外键。

InnoDB 和 MyISAM 比较

MyISAM 适合查询、插入为主的场景

InnoDB 支持事务（四个隔离级别），更新较为频繁的场景使用

1，InnoDB，支持行级锁（默认）行级锁提供了最高的并发性，但也是最复杂的，因为它需要管理更多的锁信息

2，MyISAM，表级锁，表级锁并不是事务型的，不支持事务

3，Memory，表级锁，并且数据存储在内存中，因此速度快，如果服务器崩溃，所有数据都会丢失

4，Archive，表级锁，仅支持 INSERT，SELECT，适合存储归档数据，如记录日志信息可以使用

四、SQL优化与执行

1>SQL执行顺序

（FROM → WHERE → GROUP BY → SELECT → ORDER BY）

①from 表1

②on(连接条件) 表1.字段 = 表2.字段

③[inner join | left join | right join](连接) 表2

④where 查询条件

⑤group by(分组) 字段

⑥聚合函数

⑦having 分组过滤条件

⑧select

⑨distinct(去重)

⑩order by(排序) 字段

⑪limit(分页) 0,10

2>SQL优化

①不要用select *，要使用具体字段。
②使用数值代替字符串，比如：0=唱，1=跳，2=rap。
③避免返回大量数据，采用分页最好。
④使用索引，提升查询速度，不宜建太多索引，不能建在重复数据比较多的字段上。
⑤批量插入比单条插入要快，因为事务只需要开启一次，数据量太小体现不了。
⑥避免子查询，优化为多表连接查询。
⑦尽量使用union all替代union，因为union会自动去重。

五、数据库设计与管理

1>数据库的三大范式

第一范式(1NF)： 保证字段不可再分，保证原子性。
第二范式(2NF)： 满足1NF前提下，表的每一列都必须和主键有关系。消除部分依赖关系。
第三范式(3NF)： 满足2NF前提下，表的每一列比必须和主键有直接关系，不能是间接关系。消除传递依赖

---

日夜颠倒头发少 ，单纯好骗恋爱脑 ，会背九九乘法表 ，下雨只会往家跑 ，搭讪只会说你好 ---- 2050781802@qq.com