索引是一种特殊的数据结构,用于快速查询和检索数据库,相当于图书目录的作用;
优点:使用索引可以加快数据的检索速度,减少数据库需要扫描的数据行数,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
缺点:创建索引和维护索引需要耗费许多时间,索引需要使用物理文件存储,也会耗费一定空间。大多数情况下,索引查询比全表扫描快,但是数据量小的情况下使用索引也不一定会带来很大提升。当对表中的数据进行增加、删除和修改的时候,索引也要动态维护,这样就降低了增删改的速度。
创建索引
ALTER TABLE `table_name` ADD INDEX 索引名 ( 字段名 );
索引分类
存储方式区分
根据数据结构存储方式的不同,MySQL 中常用的索引,在物理上分为 B+Tree 索引 和 HASH 索引两类,两种不同类型的索引各有其不同的适用范围。
1.B+Tree索引
在了解B+Tree索引之前先要理解BTree:
B+Tree是BTree的一种特殊变种。
BTree是一个多路平衡查找树(Balance Tree,多路也就是多叉的意思),所有的叶子节点在
同一高度,能够保持数据有序,同时BTree为系统大块数据的读写操作做了优化。
假设要从图中查找id = X的数据,BTREE 搜索过程如下:
1.取出根磁盘块,加载40和60两个关键字。
2.如果X = 40,则命中;如果X < 40走P1;如果40 < X < 60走P2;如果X = 60,则命中;如果X > 60走P3。
3.根据以上规则命中后,接下来加载对应的数据, 数据区中存储的是具体的数据或者是指向数据的指针。
B+Tree
B+Tree在原有BTree的基础上补充了如下特性:
B+Tree 根节点和支节点没有数据区,数据data全部存储在叶子节点中;
每一个父节点的值,都包含在叶子节点中,是叶子节点中最大(或最小)的元素;
每一个叶子节点,都持有一个指向下一个叶子节点的指针,形成了有序链表;
BTree与B+Tree区别
1.BTree的数据(或指向数据的指针)存在每个节点里,而 B+Tree的数据(或指向数据的指针)仅存在叶子节点里,非叶子节点只有索引。
2.BTree 的查找,可能会在任意一个节点停止,而 B+Tree的查找相对稳定。
3.B+Tree 的非叶子节点可以存储更多的索引值,阶数更高
4.B+Tree 的叶子节点使用双向链表链接,提高顺序查询效率
5.相比于 BTree , B+Tree 在区间查找方面更胜一筹
MySQL选择B+Tree的原因
1.B+Tree全表扫描能力更强。如果我们要根据索引去进行数据表的扫描,如果基于BTREE进行扫描,需要把整棵树遍历一遍,而B+TREE只需要遍历所有叶子节点即可(叶子节点之间形成有序列表)。
2.B+Tree排序能力更强。
3.B+TREE磁盘读写能力更强。他的根节点和枝节点不保存数据区,所以根节点和枝节点同样大小的情况下,保存的关键字要比BTREE要多。所以,B+TREE读写一次磁盘加载的关键字比BTREE更多。
4.B+Tree查询性能稳定。B+Tree数据只保存在叶子节点,每次查询数据,查询IO次数一定是稳定的。
2.哈希索引
哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY存储引擎和 HEAP存储引擎支持这类索引。
哈希索引,是基于哈希表实现的一种索引结构。将字段的内容(key)通过哈希算法,计算该字段的哈希值,用于访问哈希表结构中的对应索引,该索引指向数据行。
哈希索引的特点:
无法用于排序与分组
只支持精确查找,无法用于部分查找和范围查找
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”,当某个索引值被使用的非常频繁时,会在 B+Tree 索引之上再创建一个哈希索引,这样就让 B+Tree 索引具有哈希索引的优点,比如:快速的精准查找。
使用逻辑区分
1.普通索引
普通索引也被称为辅助索引。是最基本的索引类型,没有任何限制,唯一任务就是加快对数据的访问速度。
普通索引允许在定义索引的列中,插入重复值和空值。
创建普通索引
CREATE INDEX 索引名 ON 表名(字段名);
2.唯一索引
唯一索引与普通索引类似,不同的是,创建唯一性索引的目的:除了提高访问速度以外,同时还能避免数据出现重复。
唯一索引列的值必须唯一,允许有空值。如果是组合索引,则列值的组合必须唯一。
创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
3.主键索引
主键索引就是专门为主键字段创建的索引,也属于索引的一种。主键索引是一种特殊的唯一索引,不允许值重复或者值为空。
创建主键索引
ALTER TABLE 表名 ADD PRIMARY KEY (id)
4.全文索引
全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。全文索引允许在索引列中插入重复值和空值。
CREATE FULLTEXT INDEX 索引名 ON 表名(字段名);
实际使用区分
在实际使用中,索引通常被创建成:单列索引和组合索引。
单列索引
单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引,单列索引只根据该字段进行索引。
单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引。只要保证该索引只对应一个字段即可。
组合索引
组合索引也称为复合索引或多列索引。相对于单列索引来说,组合索引是将原表的多个列共同组成一个索引。
多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。
CREATE INDEX 索引名 ON 表名(字段名1,字段名2);
一个表可以有多个单列索引,但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引,以此来加快查询速度。