MySQL索引
一、索引介绍
1.索引是什么
索引是帮助MySQL高效获取数据的数据结构,能优化数据库的查询速度,降低服务器负载(不需要所有数据库中的所有数据,)。
索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)。
我们通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉的)的索引。
- 索引有两种存储类型,包括B型树(BTREE)索引和哈希(HASH)索引,BTREE为默认索引。
- 所有的存储引擎对每个表至少支持16个索引,总索引长度至少为256字节。
2.索引的优势和劣势
优势
- 可以提高数据检索的效率,减低数据库IO成本,类似于书的目录。
- 通过索引列对数据进行排序,减低数据排序的成本,降低CPU消耗。
- 有索引的列会自动排序,包括
单列索引
和组合索引
,只是组合索引的排序要复制一些。 - 如果按照索引列的顺序进行排序,对于
order by
语句来说,效率会高很多。
- 有索引的列会自动排序,包括
劣势
- 索引会占据磁盘空间。
- 索引虽然会提高查询效率,但是会降低更新表的效率。每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或更新对于的所有文件。
并非在查询数据的时候总需要应用索引来优化查询,因为所有的创建和维护也需要消耗时间和空间。每插入一次都会根据索引进行排序。索引会影响用户的插入操作,这里的做法可以是在插入大量数据之前把索引删除,插入完成之后再把索引创建。
二、MySQL索引分类及创建
使用
SHOW CREATE TABLE 表名;
查看表结构。创建后使用该语句查看有没有创建成功!
-
主键索引(PRIMARY):索引列中的值必须是唯一的,不允许有空值。示例创建id为主键索引。
CREATE TABLE user_primary( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY (id) );
-
普通索引(INDEX):MySQL中基本索引类型,允许在定义索引的列中插入重复值和空值(无限制)。为id创建一个名为
Idx
的普通索引。CREATE TABLE user_index( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, INDEX Idx(id) );
-
唯一索引(UNIQUE):索引列中的值必须唯一,允许为空值。为id创建名为
UniqIdx
的唯一索引。CREATE TABLE user_unique( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, UNIQUE INDEX UniqIdx(id) );
虽然添加唯一索引可以约束字段的唯一性,但是有时候并不能提高用户查询速度,即不能实现优化查询的目的,使用时要充分参看场景。
-
全文索引(FULLTEXT):只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。为类型为varchar的name创建名为
FullIdx
的全文索引。MyISAM和InnoDB中都可以使用全文索引。
CREATE TABLE user_fulltext( id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(32) NOT NULL, FULLTEXT INDEX FullIdx(name) );
-
空间索引(SPATIAL):MySQL在5.7开始支持了空间索引,支持OpenGIS几何数据模型。只能建立在空间数据类型上,提高系统获取空间数据的效率。只有MyISAM支持空间索引。创建空间索引时需要色合作
SPATIAL
参数。为relation
创建一个命名为spatIdx
的空间索引,CREATE TABLE user_spatial( relation geometry not null, SPATIAL INDEX spatIdx(relation) )engine = MyISAM;
-
前缀索引:在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时,可以指定索引列的长度,但是数值类型不能指定。创建一个名为
NamePre
长度为10前缀索引。CREATE TABLE user_prefix( name varchar(32) NOT NULL, INDEX NamePre(name(10)) );
-
单列索引(INDEX):只对应一个字段的索引。为id创建单列索引。
CREATE TABLE user_index( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, INDEX(id) );
-
组合索引(INDEX()):遵循最左前缀匹配原则(最左匹配原则),一般会使用组合索引代替多个单列索引使用。为id和name创建一个命名为MultiIdx的组合索引。
CREATE TABLE user_index_multi( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, INDEX MultiIdx(id,name) );
要使用该索引,用户必须使用这些字段中的第一个字段
三、索引的数据结构
索引的数据结构有两种,哈希表和B+树,下面分析一下它们的各自的特点。
HASH
hash表,以键值对(key-value)存储数据。使用Hash表的key可以存储索引列,value可以存储记录或者行磁盘地址。Hash表在等值查询时效率很高,时间复杂度为O(1);但是不支持范围快速查找,范围查找时还是只能通过扫描全表方式。显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。
B+TREE
我们首先要分析一下索引在数据库中的需求:会频繁用到索引和会有范围查找需求。
优化点:尽量减少IO,降低树的高度,尽量使得数据都在一个层级内。
结合这些点,我们想到的最适合的数据结构就是B+TREE了,
B+TREE的数据结构特点是:
- 它的节点中存储着多个元素,每个内节点有多个分叉。
- 节点中的元素包含键值(没有数据),节点中的键值从小到大排列。
- 父节点当中的元素不会出现在子节点中。
- 所有的叶子结点都位于同一层,叶节点具有相同的深度。
- 叶子节点之间使用双向链表连接,最底层的叶子节点形成了一个双向有序链表。
- 只有叶子节点才会存储数据,非叶子节点只会存储键值。
B+树的最底层叶子节点包含了所有的索引项。B+TREE在查找所索引时,由于索引都是存放到最底层的叶子节上,所以每次查找都需要检索到叶子节点才能查询到数据。所以每次的磁盘的IO跟树的高度有直接的关系,从另一个角度来说,由于数据都被发到了叶子节点上,所以存放索引的磁盘块所存放的索引数是会跟着增加的。
又因为此时所有的索引都是以双向链表连接的,此时,只需要查找到索引即可立刻返回,无序后面的继续查找(当范围查找时,就只需要通过B+TREE查找一次找到第一个索引,之后直接根据双向链表获取下一个索引,岂不美哉)。
举个范围查找的例子:
假如查找9~26之间的索引,查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。
-
首先查找值等于9的数据,将值等于9的数据缓存到结果集。这一步将磁盘1,磁盘2和磁盘3加载到内存,发生了三次磁盘IO。
-
查找到9之后,底层的叶子节点是一个有序列表,我们从磁盘块6,键值9开始向后遍历筛选所有符合筛选条件的数据。
-
第四次磁盘IO:根据磁盘6后继指针到磁盘中寻址定位到磁盘块7,将磁盘7加载到内存中,在内存中从头遍历比较,9<25<26,9<26<=26,将data缓存到结果集。
主键具备唯一性(后面不会有<=26的数据),不需再向后查找,查询终止。将结果集返回给用户。
B+树保证快速查询到索引,所以MySQL就采用了B+树的数据结构存储索引。
四、在已有的表上创建和删除索引
再次提示:每操作一次索引,就使用
SHOW CREATE TABLE table_name
查看表结构是否发生相应的变化。
1.创建索引
ALTER和ADD关键字
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name(ength),…)
【例】在user_index表中的name字段上建立名为NIdx
的唯一索引
alter table user_index add unique index NIdx (name);
对于添加主键时,只能使用KEY,不能使用INDEX。
CREATE INDEX关键字
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[(length)],…)
【例】在user_index表中的name字段上建立名为NIdx
的唯一索引
create unique index NIdx on user_index(name(20));
2.删除索引
AUTO_INCREMENT约束字段的索引不能被删除。
ALTER TABLE table_name DROP INDEX index_name
【例】删除user_index表中的name普通索引
--1.查看user_index表中结构
show create table user_index;
CREATE TABLE `user_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
KEY `id` (`id`),--待删除
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
--2.删除name的index索引,并且查看表结构
alter table user_index drop index name;
show create table user_index;
CREATE TABLE `user_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
DROP INDEX index_name ON table_name
【例】 删除user_index表中的name的唯一索引
--1.查看usesr_index表的结构
show create table user_index;
CREATE TABLE `user_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
UNIQUE KEY `name` (`name`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
--2.删除名为'name'的索引
drop index name on user_index;
show create table user_index;
CREATE TABLE `user_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
关键在于多多练习~