MySQL 索引简介

什么是索引

索引是一种提高查找速度的机制。

数据库中的索引与书的目录类似,表中的数据类似于书的内容。书的目录有助于读者快速找到书中相关的内容,数据库的索引有助于加快数据检索的速度。

索引的本质

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

索引的存储分类

索引是在MYSQL的存储引擎层中实现的,而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。MYSQL目前提供了一下4种索引。

  • B-Tree 索引:最常见的索引类型,大部分引擎都支持B树索引。
  • HASH 索引:只有Memory引擎支持,使用场景简单。
  • R-Tree 索引(空间索引):空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型。
  • Full-text (全文索引):全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。

索引文件如何加快查找速度

举例,学生表student中建立“学号”索引(升序),如图:

没有索引文件时,如果要找位于第10000条的学号”20070201”的记录, 计算机要在表中查找10000次

有索引文件时(二分法查找实例):计算机先在索引文件中学号为”20070201”的记录,找到相应的记录号,再到学生表中直接读取相关记录.

整个过程如下:

  1. 排序前,只能顺序查找记录
  2. 索引后,指针在索引文件中顺序移动
  3. 索引文件中记录是有序的
  4. 有序后,可以用各种方法加快查询速度,如折半(二分)查找法

B-树(BTREE)介绍

B-Tree 索引是最常见的索引类型,大部分引擎都支持B树索引。B-树方式构建为包含了多个节点的一棵树,顶部的节点构成了索引的开始点,叫做根;每个节点中含有索引列的几个值,节点中的每个值又都指向另一个节点或者指向表中的一行。这样,表中的每一行都会有索引中有一个对应值,查询的时候根据索引值就可以直接找到所有的行。

如上图,是一颗b+树。浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。

真实的数据存在于叶子节点,即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

在上图中,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

直观例子:

B-TREE索引类型

  • 普通索引
    这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
    (1)创建索引: CREATE INDEX 索引名 ON 表名(列名1,列名2,…);
    (2)修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1,列名2,…);
    (3)创建表时指定索引:CREATE TABLE 表名 ( […], INDEX 索引名 (列名1,列名 2,…) );
  • UNIQUE索引
    表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique:
    (1)创建索引:CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
    (2)修改表:ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
    (3)创建表时指定索引:CREATE TABLE 表名( […], UNIQUE 索引名 (列的列表) );
  • 主键:PRIMARY KEY索引
    主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。
    (1)主键一般在创建表的时候指定:“CREATE TABLE 表名( […], PRIMARY KEY (列的列表) ); ”。
    (2)但是,我们也可以通过修改表的方式加入主键:“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。
    每个表只能有一个主键。 (主键相当于聚合索引,是查找最快的索引)
    注:不能用CREATE INDEX语句创建PRIMARY KEY索引

创建索引

在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1.ALTER TABLE – ALTER TABLE用来创建普通索引UNIQUE索引PRIMARY KEY索引

  • ALTER TABLE table_name ADD INDEX index_name (column_list)
  • ALTER TABLE table_name ADD UNIQUE (column_list)
  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)

2.CREATE INDEX – CREATE INDEX可对表增加普通索引或UNIQUE索引。

  • CREATE INDEX index_name ON table_name (column_list)
  • CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

  • DROP INDEX index_name ON talbe_name
  • ALTER TABLE table_name DROP INDEX index_name
  • ALTER TABLE table_name DROP PRIMARY KEY

其中,前两条语句是等价的,删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

查看索引

mysql> show index from tblname;
mysql> show keys from tblname;

索引的弊端

首先,索引是以文件的形式存储的,索引文件要占用磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。

其次,在更新表中索引列上的数据时,对索引也需要更新,这可能需要重新组织一个索引,如果表中的索引很多,这是很浪费时间的。也就是说,这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多,则更新表的时间就越长。

但是这些弊端并不妨碍索引的应用,因为索引带来的好处已经基本掩盖了它的缺陷,在表中有很多行数据的时候,索引通常是不可缺少的

 

参考资料

码中人 微信公众号

关注微信公众号

码中人 微信公众号