索引

在表数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

  • 功能分类
    • 普通索引: 最基本的索引,它没有任何限制。
    • 唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值组合必须唯一。
    • 主键索引:一种特殊的唯一索引,不允许有空值。一般在建表时同时创建主键索引。
    • 组合索引:顾名思义,就是将单列索引进行组合。
    • 外键索引:只有InnoDB引擎支持外键索引,用来保证数据的一致性、完整性和实现级联操作。
    • 全文索引:快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
  • 结构分类
    • B+Tree索引 :MySQL使用最频繁的一个索引数据结构,是InnoDB和MyISAM存储引擎默认的索引类型。
    • Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。

索引的操作

创建索引:

如果一个表中有一列是主键,那么就会默认为其创建主键索引

1
2
3
4
5
6
7
8
9
10
-- 标准语法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型] -- 默认是B+TREE
ON 表名(列名...);

-- 为student表中姓名列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);

-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);

查看索引:

1
2
3
4
5
-- 标准语法
SHOW INDEX FROM 表名;

-- 查看student表中的索引
SHOW INDEX FROM student;

添加索引:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);

-- 外键索引(添加外键约束,就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

删除索引:

1
2
3
4
5
-- 标准语法
DROP INDEX 索引名称 ON 表名;

-- 删除student表中的idx_score索引
DROP INDEX idx_score ON student;

索引的实现原则

  • 索引是在MySQL的存储引擎中实现的,所以每种存储引擎的索引不一定完全相同,也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的实现的B+Tree索引
  • B+Tree是一种树型数据结构,是B-Tree的变种。通常使用在数据库和操作系统中的文件系统,特点是能够保持数据稳定有序。我们逐步的来了解一下。

磁盘存储

  • 系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的
  • 位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
  • InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
  • InnoDB引擎将若干个地址连接磁盘块,以此来达到页的大小16KB,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。

BTree

  • BTree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述BTree,首先定义一条记录为一个二元组[key, data] ,key为记录的键值,对应表中的主键值,data为一行记录中除主键外的数据。对于不同的记录,key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个3阶的BTree:

    05

  • 根据图中结构显示,每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例,关键字为17和35,P1指针指向的子树的数据范围为小于17,P2指针指向的子树的数据范围为17~35,P3指针指向的子树的数据范围为大于35。

查找顺序:

1
2
3
4
5
6
7
8
模拟查找15的过程 : 

1.根节点找到磁盘块1,读入内存。【磁盘I/O操作第1次】
比较关键字15在区间(<17),找到磁盘块1的指针P1。
2.P1指针找到磁盘块2,读入内存。【磁盘I/O操作第2次】
比较关键字15在区间(>12),找到磁盘块2的指针P3。
3.P3指针找到磁盘块7,读入内存。【磁盘I/O操作第3次】
在磁盘块7中找到关键字15。

B+Tree

  • B+Tree是在BTree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。
  • 从上一节中的BTree结构图中可以看到每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。
  • B+Tree相对于BTree区别:
    • 非叶子节点只存储键值信息。
    • 所有叶子节点之间都有一个连接指针。
    • 数据记录都存放在叶子节点中。
  • 将上一节中的BTree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:

06

通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:

  • 【有范围】对于主键的范围查找和分页查找
  • 【有顺序】从根节点开始,进行随机查找

实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

总结

索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高效的使用索引。

  • 对查询频次较高,且数据量比较大的表建立索引。
  • 使用唯一索引,区分度越高,使用索引的效率越高。
  • 索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合。
  • 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。
  • 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗。另外索引过多的话,MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价。

当一个线程真正在操作数据的时候,其他线程只能等待。当一个线程执行完毕后,释放锁。其他线程才能进行操作!

锁的分类

按操作分类:

  • 共享锁:也叫读锁。针对同一份数据,多个事务读取操作可以同时加锁而不互相影响 ,但是不能修改数据记录。
  • 排他锁:也叫写锁。加锁的数据,不能被其他事务加锁查询或修改

按粒度分类:

  • 表级锁:操作时,会锁定整个表。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突概率高,并发度最低。偏向于MyISAM存储引擎!
  • 行级锁:操作时,会锁定当前操作行。开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高。偏向于InnoDB存储引擎!
  • 页级锁:锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间,会出现死锁,并发性能一般。

按使用方式分类:

  • 悲观锁:每次查询数据时都认为别人会修改,很悲观,所以查询时加锁。
  • 乐观锁:每次查询数据时都认为别人不会修改,很乐观,但是更新时会判断一下在此期间别人有没有去更新这个数据
存储引擎 表级锁 行级锁 页级锁
MyISAM 支持 不支持 不支持
InnoDB 支持 支持 不支持
MEMORY 支持 不支持 不支持
BDB 支持 不支持 支持

演示InnoDB锁

共享锁:数据可以被多个事务查询,但是不能修改

1
2
-- 标准语法
SELECT语句 LOCK IN SHARE MODE;
1
2
3
4
5
6
7
8
9
10
11
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录。加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99分的数据记录。加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询,可以查询)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录,并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1的姓名为张三三(不能修改,会出现锁的情况。只有窗口1提交事务后,才能修改成功)
UPDATE student SET NAME='张三三' WHERE id = 1;

-- 修改id为2的姓名为李四四(修改成功,InnoDB引擎默认是行锁)
UPDATE student SET NAME='李四四' WHERE id = 2;

-- 修改id为3的姓名为王五五(注意:InnoDB引擎如果不采用带索引的列。则会提升为表锁)
UPDATE student SET NAME='王五五' WHERE id = 3;

-- 提交事务
COMMIT;

排他锁:加锁的数据,不能被其他事务加锁查询或修改

1
2
-- 标准语法
SELECT语句 FOR UPDATE;
1
2
3
4
5
6
7
8
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录,并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录,并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1的数据记录,并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1的姓名为张三(不能修改,会出现锁的情况。只有窗口1提交事务后,才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;

-- 提交事务
COMMIT;

悲观锁和乐观锁使用前提:

  • 对于读的操作远多于写的操作的时候,这时候一个更新操作加锁会阻塞所有的读取操作,降低了吞吐量。最后还要释放锁,锁是需要一些开销的,这时候可以选择乐观锁。
  • 如果是读写比例差距不是非常大或者系统没有响应不及时,吞吐量瓶颈的问题,那就不要去使用乐观锁,它增加了复杂度,也带来了业务额外的风险。这时候可以选择悲观锁。

总结

  • 表锁和行锁

    • 行锁:锁的粒度更细,加行锁的性能损耗较大。并发处理能力较高。InnoDB引擎默认支持!
    • 表锁:锁的粒度较粗,加表锁的性能损耗较小。并发处理能力较低。InnoDB、MyISAM引擎支持!
  • InnoDB锁优化建议

    • 尽量通过带索引的列来完成数据查询,从而避免InnoDB无法加行锁而升级为表锁。

    • 合理设计索引,索引要尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定。

    • 尽可能减少基于范围的数据检索过滤条件。

    • 尽量控制事务的大小,减少锁定的资源量和锁定时间长度。

    • 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率。

    • 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁的产生。