精选(52) 一篇文章解决你大部分数据库所遇到的问题

Dear 丶 2022-02-01 08:29 141阅读 0赞

# 写在前面 #

本文对面试/笔试过程中经常会被问到的一些关于数据库(MySQL)的问题进行了梳理和总结，包括数据库索引、数据库锁、数据库事务和MySQL优化等基础知识点

# 数据库范式 #

*  第一范式：列不可分，eg:【联系人】（姓名，性别，电话），一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF；
 *  第二范式：有主键，保证完全依赖。eg:订单明细表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID，不符合2NF；
 *  第三范式：无传递依赖(非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况)，eg:订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。

# 数据库索引 #

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nvd2JpbjIwMTI_size_16_color_FFFFFF_t_70]  
**索引的底层实现原理和优化**

在数据结构中，我们最为常见的搜索结构就是二叉搜索树和AVL树(高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度)了。然而，无论二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉树结构成为不二选择。特别地，B-Tree的各种操作能使B树保持较低的高度，从而保证高效的查找效率。

**B-Tree(平衡多路查找树)**

*  树中每个结点最多有m个孩子结点；
 *  若根结点不是叶子节点，则根结点至少有2个孩子结点；
 *  除根结点外，其它结点至少有(m/2的上界)个孩子结点； \*所有的叶结点都在同一层上，并且不带信息（可以看作是外部结点或查找失败的结点，实际上这些结点不存在，指向这些结点的指针为空）。

**B+Tree ： InnoDB存储引擎的索引实现**

*  n 棵子树的结点中含有n个关键码；
 *  所有的叶子结点中包含了全部关键码的信息，及指向含有这些关键码记录的指针，且叶子结点本身依关键码的大小自小而大的顺序链接；
 *  非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键码。

**为什么说B±tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？**

*  B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；
 *  B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；
 *  数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

**文件索引和数据库索引为什么使用B+树?**

*  文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适
 *  B+树还有一个最大的好处：方便扫库。B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因

**索引的优点**

*  大大加快数据的检索速度，这也是创建索引的最主要的原因；
 *  加速表和表之间的连接；
 *  在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间； \*通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

**什么情况下设置了索引但无法使用？**

*  以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
 *  OR语句前后没有同时使用索引；
 *  数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
 *  对于多列索引，必须满足 最左匹配原则 (eg：多列索引col1、col2和col3，则 索引生效的情形包括 \*col1或col1，col2或col1，col2，col3)。

**什么样的字段适合创建索引？**

*  经常作查询选择的字段
 *  经常作表连接的字段
 *  经常出现在order by, group by, distinct 后面的字段

**创建索引时需要注意什么？**

*  非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
 *  取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
 *  索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

**索引的分类**

*  普通索引和唯一性索引：索引列的值的唯一性
 *  单个索引和复合索引：索引列所包含的列数
 *  聚簇索引与非聚簇索引：聚簇索引按照数据的物理存储进行划分的。对于一堆记录来说，使用聚集索引就是对这堆记录进行堆划分，即主要描述的是物理上的存储。正是因为这种划分方法，导致聚簇索引必须是唯一的。聚集索引可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了；而非聚集索引是把一个很大的范围，转换成一个小的地图，然后你需要在这个小地图中找你要寻找的信息的位置，最后通过这个位置，再去找你所需要的记录。

**主键、自增主键、主键索引与唯一索引概念区别**

*  主键：指字段 唯一、不为空值 的列；
 *  主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；
 *  自增主键：字段类型为数字、自增、并且是主键；
 *  唯一索引：索引列的值必须唯一，但允许有空值。主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为\* 唯一索引允许空值，主键不允许有空值，所以不能说唯一索引也是主键。

**主键就是聚集索引吗？主键和索引有什么区别**

主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引

# 数据库事务 #

**事务的特征**

*  原子性(Atomicity)：事务所包含的一系列数据库操作要么全部成功执行，要么全部回滚；
 *  一致性(Consistency)：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态；
 *  隔离性(Isolation)：并发执行的事务之间不能相互影响； \*持久性(Durability)：事务一旦提交，对数据库中数据的改变是永久性的。

**事务并发带来的问题**

*  脏读：一个事务读取了另一个事务未提交的数据；
 *  不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
 *  幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。

**隔离级别**

*  READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没commit的数据，这样可能会提高性能，但是会导致脏读问题；
 *  READ COMMITTED：在一个事务中只允许对其它事务已经commit的记录可见，该隔离级别不能避免不可重复读问题；
 *  REPEATABLE READ：在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务commit或rollback。但是，其他事务的insert/delete操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复select的结果一样，除非本事务中update数据库。
 *  SERIALIZABLE：最高级别的隔离，只允许事务串行执行。

**MySQL默认的隔离级别是REPEATABLE READ。**

**mysql的事务支持**

*  MyISAM：不支持事务，用于只读程序提高性能；
 *  InnoDB：支持ACID事务、行级锁、并发；
 *  Berkeley DB：支持事务。

**索引优化**

*  以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
 *  OR语句前后没有同时使用索引；
 *  数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
 *  对于多列索引，必须满足最左匹配原则(eg,多列索引col1、col2和col3，则 索引生效的情形包括col1或col1，col2或col1，col2，col3)。

**MySQL存储引擎中的MyISAM和InnoDB区别详解**

*  存储结构：每个MyISAM在磁盘上存储成三个文件：第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
 *  存储空间：MyISAM可被压缩，占据的存储空间较小，支持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
 *  可移植性、备份及恢复：MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，同时在备份和恢复时也可单独针对某个表进行操作。InnoDB免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
 *  事务支持：MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。
 *  AUTO\_INCREMENT：在MyISAM中，可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。InnoDB中必须包含只有该字段的索引，并且引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
 *  表锁差异：MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
 *  全文索引：MyISAM支持 FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
 *  表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
 *  表的具体行数：MyISAM保存表的总行数，select count() from table;会直接取出出该值；而InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
 *  CURD操作：在MyISAM中，如果执行大量的SELECT，MyISAM是更好的选择。对于InnoDB，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
 *  外键：MyISAM不支持外键，而InnoDB支持外键。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nvd2JpbjIwMTI_size_16_color_FFFFFF_t_70]: /images/20220201/41f999d526c443eead42237fd75fd315.png