Mysql相关知识(三)索引

Posted by shuyou on Saturday, April 10, 2021

本文介绍Mysql索引相关知识

索引是什么

索引是一种帮助数据库高效查询数据的数据结构

索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中。(可能是单独的索引文件,也可能是和数据一起存储在数据文件中)

通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉的)的索引。

索引的类型

  • 主键索引:索引列中的值必须是唯一的,且不允许有空值。
  • 唯一索引:索引列中的值必须是唯一的,但允许为空值。
  • 全文索引:只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。
  • 组合索引: 可以是单列上创建的索引,也可以是在多列上创建的索引。
  • 普通索引: 最基本的索引类型,没有唯一性之类的限制。

索引相关操作

主键索引

#建表的时候创建主键索引
create table 表名 (字段1 数据类型,字段2 数据类型,primary key (列名));

#修改表增加主键索引
alter table 表名 add primary key (列名);

#删除主键索引
alter table 表名 drop primary key;

唯一索引

#创建
create unique index 索引名 on 表名 (列名);
alter table 表名 add unique 索引名 (列名);
create table 表名 (字段1 数据类型,字段2 数据类型,unique 索引名 (列名));

全文索引:

create fulltext index 索引名 on 表名 (列名);

alter table 表名 add fulltext 索引名 (列名);

create table 表名 (字段1 数据类型,字段2 数据类型,fulltext 索引名 (列名));

组合索引

create table 表名 (字段1 数据类型,字段2 数据类型,index 索引名 (列名1,列名2));
#需要满足最左原则,因为select语句的 where 条件是依次从左往右执行的,所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效。
#  select * from 表名 where 列名1='...' and(or) 列名2='...'

普通索引

create index 索引名 on 表名 (列名[(length)]);

#(列名[(length)]:length 为可选项,如果忽略 length 的值,则使用整个列的值作为索引。如果指定使用列的前 length 个字符来创建索引,这样有利于减小索引文件的大小。

alter table 表名 add index 索引名 (列名);

查看索引

show index from T;
show keys form T;

Mysql索引的数据结构B+Tree 不使用下列数据结构的原因:

  • 二叉树:极端情况下会成为类似链表的结构,而且树高会增大磁盘IO,影响查询效率
  • 平衡二叉树:树高会增大磁盘IO,影响查询效率
  • hash表:适合等值查询,不适合范围查询会查询整个表

BTree:

  • 叶子节点和非叶子节点都存放数据,不支持范围查询的快速查询(会多词遍历根节点)。
  • 如果data存储的是行记录,行的大小随着列数的增多,所占空间会变大。这时,一个页中可存储的数据量就会变少,树相应就会变高,磁盘IO次数就会变大。

B+Tree:

  • 只有叶子节点存储数据,非叶子节点存储键值。叶子节点之间使用双向指针连接,最底层的叶子节点形成了一个双向有序链表。

InnoDB索引

主键索引也叫聚簇索引,使用B+Tree构建,叶子节点存放的是数据表的整行记录。一般情况下,聚簇索引等同于主键索引,当一个表没有创建主键索引时,InnoDB会自动创建一个ROWID字段来构建聚簇索引。

除聚簇索引之外的所有索引都称为辅助索引。辅助索引叶子节点存储的是该行的主键值,在检索时,InnoDB使用此主键值在聚簇索引中搜索行记录,这个过程也称回表。

哪些情况需要创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其他表关联的字段,外键关系应该建立索引
  4. 频繁更新的字段不适合创建索引
  5. where条件里用不到的字段不创建索引
  6. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
  7. 查询中统计或者分组的字段

哪些情况不需要创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据列有许多重复的内容

索引优化

  • 覆盖索引
  • 最左前缀原则
  • 索引下推

参考

  1. Mysql索引相关知识
  2. Mysql索引概念相关知识
  3. Mysql45讲

「真诚赞赏,手留余香」

ShuYou's Blog

真诚赞赏,手留余香

使用微信扫描二维码完成支付