MySQL学习笔记(六)索引类别

2021/7/21 19:16:07

本文主要是介绍MySQL学习笔记(六)索引类别,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

1.普通索引

MySQL中最基本的索引类型,没有限制,允许重复,允许NULL

CREATE INDEX index_code ON tb_student(code);

2.唯一索引

唯一索引跟普通索引类似,唯一索引列的值必须唯一,允许有空值。
如果是组合索引,则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字,例如电话号码必须唯一,创建唯一索引

CREATE INDEX index_phone_number ON tb_student(phone_number);

3.主键索引

主键索引其实也是唯一索引的一种,但是不允许值重复或者值为NULL。必须使用PRIMARY KEY,在创建表结构的时候创建,不能单独用CREATE 的方式创建,主键一般都是自增ID

4.全文索引

全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和NULL。
不过对于大容量的数据表,生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。

CREATE FULLTEXT INDEX index_name ON tb_student(name);

4.最左匹配原则

例如给日志表创建了3个联合索引,分别是
operate_type操作类型,
operate_user操作人,
operate_status操作状态

CREATE INDEX index_type_user_status ON eom_log(operate_type,operate_user,operate_status);

下面我们就来例如explain来看看是否命中索引

explain select * from eom_log where operate_type < 10

先来查一个字段,operate_type,结果如下,命中索引

在这里插入图片描述
再加一个字段,执行的结果也是命中索引

explain select * from eom_log where operate_type < 10 and operate_user < 10

在这里插入图片描述
那么如果我们查最右边的那个字段,会发生什么呢?

explain select * from eom_log where operate_status < 10

很遗憾,没有走索引,
在这里插入图片描述
同意的道理,只查询中间字段,也是不走索引的

explain select * from eom_log where operate_user < 10

在这里插入图片描述
所以在多个字段组成的联合索引中,where条件中必须要从最左边的第一个字段开始匹配,如果没有就无法命中索引

那么问题又来了,如果只用第一个和第三个 字段当条件能命中索引吗?我们来试试

explain select * from eom_log where operate_type < 10 and operate_status  < 10

在这里插入图片描述
结果显示命中索引,所以只要where条件中包含最左边的字段,那么一定是命中

那么问题又来了,如果把这3个字段的顺序颠倒一下,能命中索引吗?

explain select * from eom_log where operate_user < 10 and operate_status  < 10 and operate_type < 10

在这里插入图片描述
结果显示命中索引,这里可能会有小伙伴问,不是最左匹配吗,其实这是mysql的优化器的功劳,优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,他会排列where的查询顺序,使他能够命中索引,最后才生成真正的执行计划。所以mysql查询优化器会最终以这种顺序进行查询执行。

5.聚簇索引喝非聚簇索引

都属于B + tree的所有结构,那么他们的区别是什么呢?最大的区别就是存储方式的不同

  • 聚簇索引:将数据和索引放在一起存储,并且是按照顺序,数据的物理存放顺序和索引顺序是一直的,找到索引,就能找到数据
  • 非聚簇索引:叶子节点不存数据,存储的是数据对应的地址,也就是说先要查询到地址,然后在去磁盘中找到对应的数据,因为数据和索引是分开的,举个例子,我们要找一本书中的某一个知识点,那么就需要先去目录中查询对应的章节页码,然后通过页码找到对应的那一页,这个目录就相当于索引,而那一页就是对应的数据

优点

  • 聚簇索引查询可以直接获取数据,相比于非聚簇索引需要二次查询效率更高
  • 聚簇索引的范围查询效率更高,因为是有序排列的

缺点

  • 维护成本较高,特别是插入数据,会导致分页(page split),当数据插入一个已满的页中时,存储引擎就会把这一页分页,这样就导致占用更多磁盘空间
  • 更新数据代价高,需要把更新后的行移动到新的位置

6.覆盖索引

覆盖索引就是SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。说白了,就是你的查询走到了索引,并且需要返回的数据刚好是索引的组成字段,不需要回表就能得到想要的结果;
简单粗暴理解:如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引

看看例子,有个学生表student,里面有个联合索引学号和电话号码index_code_phone

SELECT code,phone from tb_user where code = 'a' and phone = 'b'

那么这个要查询的code 和phone 正好索引,那么查询的时候就直接能够返回数据,不需要走二次查询,这就是覆盖索引



这篇关于MySQL学习笔记(六)索引类别的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程