MySQL最常见的10道面试题

 1. innodb和myisam的区别？

MyISAM和innoDB都是MySQL存储引擎。

区别：

• InnoDB支持事务，并发量大的系统的表现更好，MyISAM不支持，但也因此更快。

• 都使用B+树的数据结构，但innodb使用聚集索引，索引和记录存储在一起；MyISAM为非聚集索引，索引和记录分开存储。

• InnoDB支持表锁、行级锁(默认)，MyISAM只支持表级锁。但注意：InnoDB的行锁实现在索引上，即如果访问没有命中索引，无法使用行锁，退化为表锁。

如何选择？

• 如果执行大量的读操作，MyISAM性能更好；如果读写都有，选择innodb(默认引擎)，支持事务、行锁。

2. 索引的数据结构和作用？

索引的作用：

• 数据库中的数据需要存放在硬盘中，不可避免地需要进行磁盘IO操作。磁盘IO操作需要寻道：找到数据所在的同心圆，寻址：找到数据所在的同心圆位置，都是耗时操作，所以要想办法减少磁盘IO操作。

• 索引就相当于目录，为了方便查找书中的内容，通过对内容建立索引形成目录。

• 索引是一个文件，占据物理空间的。

索引的数据结构：

① MySQL选择B+树作为索引数据结构

• B+树的非叶节点不存储data，可以存储更多的索引值，使得树更矮，减少磁盘IO操作。

• B+树的叶节点构成了一个有序链表，对于范围型的查找和搜索，可以减少磁盘IO操作。

② hash表 

• 将数据库字段数据转换成定长的Hash值，将这条数据的行指针并存入Hash表

• 如果发生Hash碰撞，则在对应Hash键下以链表形式存储多个行指针。

3. 索引失效的场景有哪些？

1. 使用联合索引时，没有遵循最左匹配原则，例如：创建了 (a, b, c) 联合索引，符合最左匹配原则的是：

where a = 1;
where a = 1 and b = 2;
where a = 1 and b = 2 and c = 3;

2. 左like语句："%ABC" 索引失效

3. 索引使用了函数：比如查询条件中对 name 字段使用了 LENGTH 函数

4. OR条件中有非索引：OR 的含义是两个只要满足一个即可，只要有一个条件列不是索引，就会进行全表扫描。

4. 数据库事务隔离级别有哪些？分别解决什么问题？

• Read Uncommitted（读未提交）：一个事务可读取另一个事务未提交的数据，会引发脏读。

• Read Committed （读已提交）：一个事务要等另一个事务提交后才能读取数据，会引发不可重复读。

• Repeatable Read （可重复读）【默认】：事务开始读取数据时，不再允许修改操作，会引发幻读。

• Serializable （序列化）：事务串行执行，可避免脏读、不可重复读、幻读，但效率低，不推荐。

5. MVCC的作用和原理？

数据库并发场景有三种：

• 读读：不需要并发控制
• 读写：可能造成脏读、幻读、不可重复读
• 写写：可能存在更新丢失问题。

MVCC：多版本并发控制，用于处理读写并发场景，使用无锁并发控制的策略处理读写冲突，读不阻塞写，写不阻塞读，提升事务并发处理能力。

原理：每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库快照。

6. 什么是回表？如何减少回表？

什么是回表？

select * from one_piece where name = “x”

• 通过name这个普通索引只能获取到该记录的主键id值为2，需要再到id索引树搜索一次。即先定位主键值，再定位行记录。

• 所以，基于非主键索引的查询需要多扫描一次索引树，回表操作会影响查询性能。

如何避免回表？

• 覆盖索引包含了查询所需的所有列，不需要回表。

7. binlog、redo log、undo log的区别？

• binlog：记录数据库的数据变更操作，以二进制的形式保存在磁盘中。使用场景：主从复制和故障恢复。

• redo log：事务操作执行时，会同时生成redo log，因为事务的数据先写入内存，再写入磁盘，当写入磁盘过程中出现数据库异常，可利用redo log 确保数据被持久化。

• undo log：数据库事务开始前，会将修改先存放到 undo log，当事务回滚或数据库崩溃，可利用undo log 撤销操作。

8. 如何分库分表？

为什么要分库分表？

• MySQL单表容量超过1千万时，查询性能明显下降。这时候就要引入数据分片策略。

如何分库分表？

• 数据分片的分类：垂直拆分/水平拆分、分库/分表

① 垂直分库：大系统拆分为多个小系统 

② 垂直分表

• 把一个表的多个字段拆成多个表，冷热拆分：热字段一个表，冷字段一个表，提升查询性能。

• 垂直分片的缺点：分库后无法Join，只能通过接口聚合方式解决，提升开发复杂度。如果表字段之间关联性强，优先水平拆分。

③ 水平分库

④ 水平分表

⑤ 垂直拆分&水平拆分相结合

• 先梳理好业务模块，做垂直拆分；再根据表数据，做水平拆分。

分库分表有哪些常用的规则？

① Hash取模：id%分表数量

• 优点：数据分片均匀

• 缺点：扩容时，映射关系发生变化，需要迁移旧数据。

② 数值Range：按照id值切分

• 优点：扩展方便

• 缺点：导致查询不均匀，如：按时间字段分片，最近数据频繁读写，历史数据很少读写。

③ 一致性Hash

• 将 2^32想象成一个圆 → 确定服务器在哈希环的位置：hash（服务器的IP） % 2^32 → 数据映射到哈希环上：hash（图片名称） % 2^32 → 沿顺时针方向遇到的第一个服务器就是图片存放的服务器

• 优点：节点的增减都只需重定位环空间中的一小部分数据

• 缺点：数据倾斜

  • 解法：虚拟节点

  • 虚拟节点均匀分布，一个实际物理节点可以对应多个虚拟节点

分库分表有哪些常用的中间件？

① Sharding-Sphere [ʃɑːdɪŋ sfɪə]

• Sharding-JDBC 最早是当当网使用的一款分库分表框架，2017年开始对外开源，现已更名为 ShardingSphere，它是一款分布式数据库中间件， 提供数据分片、读写分离、加密等能力。

9. MySQL主从复制的作用和原理？

为什么需要主从复制？

• 主从复制是读写分离的前提，可提升性能

• 主从复制是故障切换的前提，提升可用性

主从复制的原理：

• 主记录binlog日志（记录所有修改操作）

• 从启动IO线程，读取主的binlog日志，写入relay log（中继日志）

• 从启动SQL线程，回放relay log

10. 如何提升数据库的查询速度？

1. 定位原因

• MySQL提供了慢查询日志功能，可以记录运行时间超过设定值的查询语句相关信息。通常，执行时常超过1s认为是慢SQL

• 使用 EXPLAIN 查看 SQL 语句执行过程

• 也可以使用慢SQL分析工具来协助定位问题

2. 索引优化

• 避免全表扫描，优先考虑对where、order by的字段建立索引。

• 使用覆盖索引减少回表

• 避免索引失效的场景：联合索引没有遵循最左原则、左like、索引字段使用函数等

3. SQL语句优化

• 避免使用 SELECT * ，只查询需要的列

• 如果需要查询多个表的数据并合并结果集，可以使用 UNION ALL 来代替 UNION，因为 UNION ALL 不会去重，所以执行速度更快，业务代码自行去重

• 使用 LIMIT 分页查询

• 使用 join（inner join或left join）时，小表在前，大表在后，重复关联键少的表放在前面可以提高join的效率。

4. 数据库服务器配置优化：内存、连接池

5. 分库分表