MySQL索引详解

  • Post author:
  • Post category:mysql


什么是索引

索引类似于书本的目录,是存储引擎用于提高数据库表的访问速度的一种数据结构。在mysql内部,将热点数据,以B+树的形式将所有page页,进行组织成一定的数据结构,再用其配套的查找算法进行查找,叫做索引查询。

为什么要有索引

数据是存储在磁盘上的,查询数据时,如果没有索引,会加载所有数据到内存,依次进行检查,读取磁盘的次数较多。

有了索引,就不需要加载所有数据,因为B+树的高度一般在2-4层,最多只需要读取2-4次磁盘,大大提升了查询速度。

索引的类型

按照数据结构分类:

BTree索引

MySQL数据库使用最多的索引类型就是BTree索引,底层基于B+树的数据结构来实现的。B+树是基于B树和叶子节点顺序访问指针进行实现,它具有B树的平衡性,并且通过顺序访问指针来提高区间查询的性能。

哈希索引

哈希索引是基于哈希表实现的,对于每一行数据,存储引擎会对索引列进行哈希计算得到哈希码,并且哈希算法要尽量保证不同的列值计算出来的哈希码值是不同的,将哈希码的值作为哈希表的key值,将数据行的指针作为哈希表的value值。

全文索引(fulltext)

目前只有MyISAM引擎支持(InnoDB在1.2.x之后也支持)。只有char、varchar、text列上可以创建全文索引。

RTree索引

RTree在MySQL很少使用,仅支持geometry数据类型。相对于BTree,RTree的优势在于范围查找。

为什么不是其他数据结构

B+ vs 链表

链表需要线性遍历,搜索效率低。

B+ vs 二叉搜索树

二叉搜索树的搜索效率不稳定,在极端情况下会退化成线性结构,同样存在搜索效率低的问题。

B+ vs AVL树/红黑树

虽然AVL树和红黑树是平衡或者近似平衡,但毕竟是二叉结构,相比较多阶B+,意味着树整体过高,同样是自顶向下查找,层高越低,意味着系统与磁盘有着更少的IO交互。

B+ vs 哈希

B+索引与哈希索引的区别:

  1. 哈希索引不支持排序,因为哈希表是无序的。

  2. 哈希表不支持范围查找。

  3. 哈希表不支持模糊查询及多列索引的最左前缀匹配。

  4. 因为哈希表中会存在哈希冲突,所以哈希索引的性能是不稳定的,而B+树索引的性能是相对稳定的,每次查询都是从根节点到叶子节点。

官方的索引实现方式中,MySQL是支持哈希索引的,不过InnoDB和MyISAM并不支持。虽然哈希的查找速度很快,但对于范围查找就明显不行。

B+ vs B

B+树与B树的区别:

  1. B树节点,既有数据,又有Page指针,而B+,只有叶子节点有数据,其它目录页,只有键值和Page指针。

  2. B+叶子节点,全部相连,而B没有。

为何选择B+:

  1. 非叶子节点不存储data,这样一个节点就可以存储更多的key,可以使得树更矮,所以IO操作次数更少。

  2. 叶子节点相连,更便于范围查找。

聚簇索引和非聚簇索引

聚簇索引

像InnoDB这种,将用户数据与索引数据放在一起的索引方案,叫做聚簇索引。

非聚簇索引

向MyISAM这种,将用户数据与索引数据分离的方案,叫做非聚簇索引。

区别

1.叶节点存放数据不同

MyISAM引擎同样使用B+树作为索引结果,叶节点的data域存放的是数据记录的地址。MyISAM最大的特点是,将索引Page和数据Page分离,也就是叶子节点没有数据,只有对应数据的地址。相较于InnoDB索引,InnoDB是将索引和数据放在一起的。

2.MyISAM不需要回表查询

MySQL除了默认会建立主键索引外,用户也有可能建立按照其他列信息建立的索引,一般这种索引叫做辅助(普通)索引。

对于MyISAM,建立辅助(普通)索引和建立主键索引没有差别,无非就是主键不饿能重复,而非主键可以重复。叶子节点放的同样是指向改行数据的指针。

而对于InnoDB,InnoDB的非主键索引中的叶子节点并没有数据,而只有对应记录的key值,所以通过辅助(普通)索引,找到目标记录,需要两遍索引:首先检验辅助索引获得主键,然后用主键到主键索引中检索获得记录。这种过程,就叫做回表查询。

总结

这样看来MyISAM引擎是优于InnoDB引擎的,因为MyISAM做到了将用户数据与索引数据进行解耦,一方面降低了数据之间的耦合度,另一方面,提升了查找效率。而且MyISAM还支持全文索引,更方便用于进行查找,但为什么InnoDB任然是主流呢?是因为InnoDB支持事务,而MyISAM并不支持。

索引设计原则

  1. 使用区分度高的字段作为索引列,索引效果更好。

  2. 比较频繁作为查询条件的字段应该创建索引。

  3. 不会出现在where子句中的字段不该创建索引。

  4. 更新非常频繁的字段不适合创建索引。

  5. 尽量使用短索引,对于较长的字符串进行索引时,应该指定一个较短的前缀长度,因为较小的索引使得目录页中能存放更多的目录,能有效的减少高度,使系统与磁盘的IO次数减少,查询速度加快。

  6. 索引不是越多越好,每个索引都需要额外的物理空间,维护也需要花费时间。

索引失效

1.对于组合索引,不使用组合索引最左边的字段,则不会使用索引。

2.以%开头的like查询如%abc,无法使用索引;非%开头的like查询,如abc%,相当于范围查询,会使用索引。

3.查询条件中列类型是字符串,没有使用引号,可能会因为类型不同发生隐式转换,使索引失效。

4.判断索引列是否不等于某个值时。

5.对索引列进行运算。

6.查询条件使用 or 连接。

索引操作

创建主键索引

方式一:


-- 在创建表的时候,直接在字段名后指定 primary key create table user1(id int primary key, name varchar(30));

方式二:


-- 在创建表的最后,指定某列或某几列为主键索引 create table user2(id int, name varchar(30), primary key(id));

方式三:


create table user3(id int, name varchar(30)); -- 创建表以后再添加主键 alter table user3 add primary key(id);

创建唯一索引

方式一:


-- 在表定义时,在某列后直接指定unique唯一属性。 create table user4(id int primary key, name varchar(30) unique);

方式二:

`– 创建表时,在表的后面指定某列或某几列为unique create table user5(id int primary key, name varchar(30), unique(name));

方式三:


create table user6(id int primary key, name varchar(30)); alter table user6 add unique(name);

创建普通索引

方式一:


create table user8(id int primary key, name varchar(20), email varchar(30), index(name) --在表的定义最后,指定某列为索引 );

方式二:


create table user9(id int primary key, name varchar(20), email varchar(30)); alter table user9 add index(name); --创建完表以后指定某列为普通索引

方式三:


create table user10(id int primary key, name varchar(20), email varchar(30)); -- 创建一个索引名为 idx_name 的索引 create index idx_name on user10(name);

创建全文索引

当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有要求,要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文。如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。


CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) )engine=MyISAM;

查询索引

方式一:

show keys from 表名;

方式二:

show index from 表名;

方式三(信息比较简略):

desc 表名;

删除索引

方式一:

-删除主键索引:

alter table 表名 drop primary key;

方式二:

-其他索引的删除:

alter table 表名 drop index 索引名;

索引名就是show keys from 表名中的Key_name 字段。

方式三:


drop index 索引名 on 表名;



版权声明:本文为weixin_60954394原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。