Jerry

外观

数据库SQL了然于心

3024字约10分钟

2024-10-19

1. 存储引擎有哪些？

  1. InnoDB

MySQL默认引擎。支持事务、高并发能力（MVCC）、崩溃修复能力、行锁、聚簇索引。底层B+树实现，页结点就是数据本身。

  1. MyISAM

读取速度快，但不支持事务，崩溃修复能力差，表锁，非聚簇索引（索引和数据分开，.MYD用来存放数据文件，.MYI用来存放索引文件）。底层也是B+树，但页节点是指针。

  1. 如何选择？

如果表经常读取，且不需要事务，MyISAM是合适的选择。如果需要事务和崩溃安全修复，必须选 InnoDB。

2. MVCC 是什么？

多版本并发控制。通过保存数据在某个时间点的快照来实现非锁并发控制。不管事务执行多少时间，每个事务看到的数据都是一致的。在InnoDB中，MVCC的实现是：在每行记录后面保存两个隐藏列。一个保存行的创建时间，一个保存行的过期时间（删除时间）。

3.索引是什么？

索引是帮助提高查询效率的数据结构。从底层实现的角度，索引包括 BTree索引 和 Hash索引，InnoDB和MyISAM只支持BTree索引，Meemory两种都支持。从功能的角度，索引分为普通索引、唯一索引、组合索引、空间索引、全文索引。从结构组织的角度，索引分为聚簇索引和非聚簇索引。

4. 什么时候该创建索引？

  1. 主键：对于主键，会自动建立一个唯一索引，以保证值唯一
  2. 频繁查询：对于频繁查询的表或字段，建立索引无疑会提高查询效率
  3. 查询中需要排序的字段：使用索引去访问排序字段将大大提高排序速度
  4. 查询中需要统计或者分组字段

5. 什么时候不该使用索引？

  1. 表记录太少
  2. 查询少，增删改多的字段（因为修改字段的同时还要动态维护索引）
  3. WHERE 条件用不到的字段不需要索引
  4. 过滤性（选择性）不好的字段不适合使用索引，例如0/1，男/女

6. 什么时候索引会失效？

索引可以包含多个列的值，但是列的顺序十分重要，MySQL只能高效地使用索引的最左前缀列。

  1. WHERE 条件有 不等于号
  2. WHERE 条件使用了表达式或函数，如SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
  3. JOIN中，MySQL只有在 主键和外键的数据类型相同 时才能使用索引，否则无效
  4. LIKE ‘abc%’，MYSQL将使用索引；但 LIKE ‘%abc’，MySQL将不使用索引。
  5. 使用 OR 一般会使索引失效
  6. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。

对于基于BTree的组合索引，非最左、跳过列、范围查询 都会使索引失效。

7. 什么是事务？

事务就是由一组SQL语句组成的逻辑处理单元，一个事务中的SQL语句组，要么全部执行，要么全部不执行。

8. 事务有哪些并发问题？

  1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
  2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
  3. 幻读：事务A在读取某个范围内的记录时，事务B又在该范围内插入新的记录，事务A再次读取该范围的记录时会产生幻行。（两次不一致）

不可重复读侧重于记录被修改，幻读侧重于新增或删除了记录。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

9. 事务的四大特征是？

A、C、I、D

  1. 原子性（Atomicity）：一个事务必须被视为不可分割的最小工作单元。一个事务中的所有操作，要么全部成功提交，要么全部失败回滚。不可能只执行其中的一部分操作。
  2. 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。不会出现查询开始时的数据跟查询到一半的数据不一样的情况。
  3. 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。这意味着 事务处理过程中的中间状态对外部是不可见的，反之亦然。事务隔离分为不同级别，包括读未提交（Read uncommitted）、提交读（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  4. 持久性（Durability）: 事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

10. 事务的隔离级别？

事务的隔离级别规定了哪些修改在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离并发程度高，开销低。

  1. 未提交读(Read uncommitted)：事务中的修改，即使未提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也称为 脏读（Dirty Read）。该级别很少被使用。
  2. 提交读(read committed)：一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的，这个级别有时候叫做不可重复读，因为同一事务自己两次执行同样的查询，期间可能有其他事务修改并提交了数据，因此两次查询可能会得到不一样的结果。
  3. 可重复读(repeatable read)：解决了脏读问题，该级别保证了在同一个事务中多次读同样记录的结果是一致的，理论上无法解决幻读问题。
  4. 可串行化(Serializable):它通过强制事务串行执行，避免了前面说的幻读的问题。该级别用得较少。

MySQL 默认的事务隔离是第 3 级别，可重复读。

11. 死锁问题怎么解决？

  • 当查询超时自动放弃锁请求，这种方式不太友好。
  • InnoDB 的做法是，将持有最少行级排他锁的事务进行回滚。

12. 共享锁的排他锁

共享锁（shared lock）也叫做读锁，如果一个事务对数据对象A加了共享锁，其他事务只能读而不能写，直至当前事务释放该锁。

排他锁（exclusive lock）也叫做写锁，如果一个事务对数据对象A加了排他锁，其他事务不能再对A加锁，包括读和写，直至当前事务释放该锁。

13. 乐观锁和悲观锁

乐观锁假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。注意，乐观锁 不能 解决脏读的问题。所谓脏读，就是一个事务读取了另一个事务未提交的数据。

悲观锁假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

14. 数据库三大范式

  • 第一范式：字段不可再分
  • 第二范式：符合1NF，并且表中的每列都和主键相关。即一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
  • 第三范式：符合2NF，并且，消除传递依赖。即每一列数据都和主键直接相关，而不能间接相关。

15. 什么是事务日志

事务日志用于提高事务效率。存储引擎修改表数据时，只需修改内存拷贝，然后把修改记录持久在硬盘的事务日志中，而不用每次都把修改的数据本身持久到硬盘中。之后把再内存修改的数据再慢慢刷回磁盘中。

16. 大表优化

  1. 限制查询范围
  2. 读写分离
  3. 分库分表

17. 谈谈分库分表？

垂直拆分和水平拆分。

垂直拆分是把一个很多列的表拆分成多个表，使列数据变小，优点是在查询时减少读取的Block数，减少I/O次数，同时可以简化表的结构，易于维护。缺点是会引起Join操作，事务变得更加复杂，可以通过在应用层进行Join来解决。

水平拆分 是把一个很多行的表拆分到不同的分表和分库中去。常见的做法有客户端代理（如Sharding-JDBC）和 中间件代理（如MyCAT）。

18. 分库分表后主键怎么处理？

  1. UUID：不建议，无序，对mysql聚簇索引来说读写效率低
  2. 自增id：需要独立部署在不同的mysql服务器
  3. redis：性能比较好，灵活方便，不依赖于数据库，但引入了新的组件造成系统更加复杂
  4. Twitter的snowflake算法
  5. 美团的Leaf分布式ID生成系统

19. binlog 和 redo log 的区别？

  • redo log 是物理日志，是 InnoDB 引擎特有的，记录的是“在某个数据页上做了什么修改”这样的操作。使用了 WAL(Write-Ahead Logging) 技术，即先写日志，再写磁盘。所以能支持崩溃修复（crash-safe）。redo log 是循环写的，重复利用空间。之所以要有 redo log，是为了性能考虑，一个事务完成后，只写 redo log 就行了，后续将多个事务的变更刷到磁盘进去。
  • binlog 是逻辑日志，是 Server 层日志，存储引擎无关，记录的是“给ID=2这一行的c字段加1”这样的写操作。binlog是追加写的，适合归档保存，通常也用来恢复误删的数据，还有主从复制。

MySQL事务有两阶段提交，即一个 update 语句，需要依次经过 redo log prepare、binlog、 redo log commit 三个步骤，保证两份日志的一致性。

20. 给表加字段有什么要注意的？

注意长事务。表的CRUD操作会默认加一个元数据读写锁（MDL），如果表加字段的时刻该表有长事务语句，加字段这个操作会被阻塞，而后续所有读写操作又都被DDL阻塞。导致整张表不可用。

解决办法：在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

但是 MySQL 5.6+ 提供了 Online DDL 功能，可以避免这个问题。

21. 慢SQL如何优化？

先看执行计划：

  1. 关注 type 列，有 all（全表扫描）、index（全索引扫描）、range（范围查找）说明性能差
  2. 看 possible_keys 和 key 列，看是否使用了索引，没有索引是否能建合适的索引？
  3. 看 rows 列， mysql 扫描了多少行， filterd 列看过滤性
  4. 看 extra 列，是否有 Using temporary(临时表)、Using Where（条件过滤）、Using filesort（文件排序），能否消除？

通常，大部分的慢SQL都能通过建立合适的索引来优化：

  1. 业务SQL中，Where 条件和 Order by 排序用到的字段，通常可以建索引
  2. 是否用了 OR 等导致索引失效，能否改写SQL，拆分SQL
  3. 是否可以建多字段的组合索引，实现覆盖索引