数据库知识点

Posted on | In | Visitors:

写在前面

  • 文中的内容非原创，主要来源见文末的参考链接，本人仅作整理工作，用以记录自己的学习过程，由于个人水平有限，故部分内容可能会出现错误，还请包涵

  • 文中采用的数据库为 MySQL

MySQL 索引

MySQL 支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此 MySQL 数据库支持多种索引类型，如 BTree 索引，哈希索引，全文索引等等

索引的本质

官方定义为：索引（index）是帮助 MySQL 高效获取数据的数据结构。即索引的本质就是一种数据结构

索引的限制

给定这样一张表：

1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test(
    id int AUTO_INCREMENT PRIMARY KEY,
    user_name varchar(30) NOT NULL,
    sex bit(1) NOT NULL DEFAULT b'1',
    city varchar(50) NOT NULL,
    age int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建一个组合索引：ALTER TABLE user_test ADD INDEX idx_user(user_name, city, age);

  1. where 查询条件中不包含索引列的最左索引列，则无法使用到索引查询，如：

    1
    SELECT * FROM user_test WHERE city = '广州';

  2. 即使 where 的查询条件是最左索引列，若查询条件中出现通配符 % 且出现在开头，同样无法使用到索引查询，如：

    1
    SELECT * FROM user_test WHERE user_name like '%feinik';

  3. 如果 where 查询条件中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询，如：

    1
    SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE '广州%' AND age = 26;

同时，索引最多作用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引

  1. 如果查询条件中含有函数或表达式，则无法使用到索引查询
    1
    SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE '广州%' AND age - 1 = 26;

前缀索引

前缀索引要选择足够长的前缀以保证高的选择性，同时又不能太长，我们可以通过以下方式来计算出合适的前缀索引的选择长度值：

1
SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_column代表要添加前缀索引的列

  • 注：通过以上方式来计算出前缀索引的选择性比值，比值越高说明索引的效率也就越高效

    前缀索引的创建

    1
    ALTER TABLE table_name ADD INDEX index_name (index_column(length));

使用前缀索引的注意点

前缀索引是一种能使索引更小，更快的有效办法，但是 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY 以及使用前缀索引做覆盖扫描。

聚集索引与非聚集索引

简单地说，聚集索引 B+Tree 中的叶子节点包含了完整的行数据记录，而非聚集索引 B+Tree 中的叶子节点包含的是指向完整行数据记录的指针

聚集索引

聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，如果定义了主键，那么 InnoDB 会通过主键来聚集数据，如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB 会隐式定义一个主键来作为聚集索引。

聚集索引可以很大程度的提高访问速度，因为聚集索引将索引和行数据保存在了同一个 B+Tree 中，所以找到了索引也就相应的找到了对应的行数据，但在使用聚集索引的时候需注意避免随机的聚集索引（一般指主键值不连续，且分布范围不均匀），如使用 UUID 来作为聚集索引性能会很差，因为 UUID 值的不连续会导致增加很多的索引碎片和随机 I/O，最终导致查询的性能急剧下降。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录

InnoDB 采用此方式，因此 InnoDB 表数据文件本身就是索引文件

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了 InnoDB 的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在 InnoDB 中不是个好主意，因为 InnoDB 数据文件本身是一颗 B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

非聚集索引

与聚集索引不同的是非聚集索引并不决定数据在磁盘上的物理排序，且在 B-Tree 中包含索引但不包含行数据，行数据只是通过保存在 B-Tree 中的索引对应的指针来指向行数据，如：上面在（user_name，city, age）上建立的索引就是非聚集索引。MyISAM 存储引擎采用此方式

为什么使用 B+tree 作为 MySQL 索引的数据结构

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。因此，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取，I/O 存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘 I/O 操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘 I/O 的存取次数。

局部性原理和磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘 I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

  • 当一个数据被用到时，其附近的数据也通常会马上被使用。
  • 程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为 4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

B+ 树数据结构

一个 m 阶 B+ 树具有如下几个特征

  • 有 k 个子树的中间节点包含有 k 个元素（B-树中是 k-1 个），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点
  • 所有的叶子节点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子节点本身以关键字的大小从小到大顺序连接
  • 所有的中间节点元素都同时存在于子节点中，在子节点元素中是最大（或最小元素），故根节点元素中最小值（最大值）即为整颗树的最小值（最大值）

B-/+ 树索引的性能分析

B- 树和 B+ 树最重要的一个区别就是 B+ 树只有叶节点存放数据，其余节点用来索引，而 B- 树是每个索引节点都会有 Data 域。这就决定了 B+ 树更适合用来存储外部数据，也就是所谓的磁盘数据。从 Mysql（Inoodb）的角度来看，B+ 树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上

B-树（B 类树）的特定就是每层节点数目非常多，层数很少，目的就是为了就少磁盘 I/O 次数。当查询数据的时候，最好的情况就是很快找到目标索引，然后读取数据，使用 B+ 树就能很好的完成这个目的，而 B-树的每个节点都有 data 域（指针），这无疑增大了节点大小，说白了增加了磁盘 I/O 次数（磁盘 I/O 一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，I/O 次数增多），而 B+ 树除了叶子节点其它节点并不存储数据，节点小，磁盘 I/O 次数就少。这是优点之一

另一个优点是 B+ 树所有的 Data 域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问。
(数据库索引采用 B+ 树的主要原因是 B 树在提高了磁盘 I/O 性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树 应运而生。B+ 树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低）)

至于 MongoDB 为什么使用 B-树而不是 B+ 树，可以从它的设计角度来考虑，它并不是传统的关系性数据库，而是以 Json 格式作为存储的 nosql，目的就是高性能，高可用，易扩展。首先它摆脱了关系模型，上面所述的优点需求就没那么强烈了，其次 Mysql 由于使用 B+ 树，数据都在叶节点上，每次查询都需要访问到叶节点，而 MongoDB 使用 B-树，所有节点都有 Data 域，只要找到指定索引就可以进行访问，无疑单次查询平均快于 Mysql（但侧面来看 Mysql 至少平均查询耗时差不多）

总体来说，Mysql 选用 B+ 树和 MongoDB 选用 B-树还是以自己的需求来选择的（为什么不选用红黑树是因为红黑树高度相较 B 树太高，B 树一般都比较矮胖

总结

B+ 树的优势

  • 单一节点存储更多的元素（相较红黑树）且不含有 Data 域（相较 B-树），使得查询的 I/O 次数减少
  • 所有查询都要查找到叶子节点，查询性能稳定
  • 所有叶子节点形成有序链表，便于范围查询

DDL、DML、DCL 分别指什么

DML（data manipulation language）数据操纵语言

  • 如常用的 SELECT、UPDATE、INSERT、DELETE。用于定义和管理 SQL 数据库中的所有对象的语言

DDL（data definition language）数据定义语言

  • 如常用的 CREATE、ALTER、DROP 等。DDL 主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上

DCL（data control language）数据控制语言

  • 包括 GRANT、DENY、REVOKE 等。用于设置或更改数据库用户或角色权限的语句

数据库中的锁

从锁的粒度，可以分成两大类：

  • 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率高，并发度最低
  • 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突概率低，并发度高

不同的存储引擎支持的锁粒度是不一样的

  • InnoDB 行锁和表锁都支持
  • MyISAM 只支持表锁

InnoDB 只有通过 索引条件 检索数据 才使用行级锁，否则，InnoDB 将使用表锁

  • 即 InnoDB 的行锁是基于索引的

表锁

表锁又分为两种模式：

  • 表读锁
  • 表写锁
  • 在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞
    • 读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁
    • 读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁
    • 写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁

故读锁和写锁是互斥的，读写操作是串行的

  • 如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql里边，写锁是优先于读锁的！
  • 写锁和读锁优先级的问题是可以通过参数调节的：max_write_lock_count和low-priority-updates
  • MyISAM 可以支持查询和插入操作的并发进行。可以通过系统变量concurrent_insert来指定哪种模式，在 MyISAM 中它默认是：如果 MyISAM 表中没有空洞（即表的中间没有被删除的行），MyISAM 允许在一个进程读表的同时，另一个进程从表尾插入记录。
  • 但是 InnoDB 存储引擎是不支持的！

行锁

我们使用 Mysql 一般是使用 InnoDB 存储引擎的。InnoDB 和 MyISAM 有两个本质的区别：

  • InnoDB 支持行锁
  • InnoDB 支持事务

InnoDB实现了以下两种类型的行锁。

  • 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
    • 也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
  • 排他锁（X锁）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
    • 也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

  • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
  • 意向锁也是数据库隐式帮我们做了，不需要程序员操心！

参考链接