MySQL索引——好好梳理了一下个人所理解的索引(Version: 1.0)

前言

不知道大家有没有这样的一个经历,搜索索引的时候,看到一大堆索引概念:聚集索引、非聚集索引、唯一索引、辅助索引、二级索引、普通索引、空间索引、全文索引、复合索引、前缀索引、覆盖索引、BTree索引、Hash索引。当时我看到这么多概念的时候整个人都懵了,有些其实是一个概念的东西,我会把它当成另一个概念。于是自己就参考书籍和网上的一堆资料来好好梳理一下索引整个概念。

笔者身份:目前还是学生,不是什么大佬,这篇文章仅是个人学习上的概念梳理,难免有错,我尽可能做的严谨,如果有误,希望各位不吝赐教,谢谢大家。

当前数据库版本:MySQL 5.7

前置知识条件:执行计划

索引的介绍

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。具体正儿八经的概念这里就不做过多的介绍了,大家搜出来的概念绝对比我介绍的好,我这里就简单带过,索引就类似书籍的目录,你查字典时如何快速找到你想找到字,总不能一页页翻对吧,那么为什么要建立索引,快,就是为了快。

但是索引并不是“特效药”,关于索引的优缺点还是很有必要好好梳理一下的,

索引的优缺点(敲重点):

优点:1、大大加速数据的检索速度;2、创建唯一索引,保证数据库的唯一性;3、加速表与表之间的连接;4、减少分组和排序的时间

缺点:1、索引是需要占用物理空间的;2、增删改时,索引也会变动,需要耗费一定时间;

索引的类别

如前言所说,大家听到了那么多的索引概念,但是究竟有哪些索引(类别)呢?

我认为应该从应用角度去看待索引的类别,将其归为两类:聚集索引和非聚集索引(或者说主键索引和辅助索引,聚集和非聚集是一种行为方式)

聚集(clustered)索引:聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引要比非聚簇索引查询效率高很多。聚集索引这种主+辅索引的好处是,当发生数据行移动或者页分裂时,辅助索引树不需要更新,因为辅助索引树存储的是主索引的主键关键字,而不是数据具体的物理地址。

非聚集索引:类似于图书的附录,那个专业术语出现在哪个章节,这些专业术语是有顺序的,但是出现的位置是没有顺序的。每个表只能有一个聚簇索引,因为一个表中的记录只能以一种物理顺序存放。但是,一个表可以有不止一个非聚簇索引。

InnoDB所使的是聚集索引方式,而MyISAM则是非聚集索引方式

(敲重点)我们在创建一张表时,要显式地为表创建一个主键,如果不主动创建主键,那么InnoDB会选择第一个不包含有null值的唯一索引作为主键。如果连唯一索引都没有,InnoDB就会为该表默认生成一个6字节的rowid作为主键

在MySQL中我们没办法手动地去创建聚集索引,那么我们能创建的索引有哪些?我通过了mysql的命令帮助进行了查看

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

我们能创建的只有非聚集索引,那么通过命令帮助我们可以到有三种类别,UNIQUE(唯一索引)、FULLTEXT(全文索引)、SPATIAL(空间索引),如果都不选的话,那我们将他就称为普通索引吧。

唯一索引:唯一索引是约束条件的一种,不允许有重复值,但是可以允许有null值 。这里引用Java开发手册中的一段话:业务上具有唯一性的字段,即使是组合字段,也必须建成唯一索引。不要以为唯一索引影响了insert速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据的产生。

全文索引(了解):全文索引,通过建立倒排索引(Inverted index,也常被称为反向索引、置入档案或反向档案,是一种索引方法,被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。),可以极大的提升检索效率,解决判断字段是否包含的问题. 例如: 有title字段,需要查询所有包含 "政府"的记录. 需要 like "%政府%"方式查询,查询速度慢,当查询包含"政府" OR "中国"的需要是,sql难以简单满足.全文索引就可以实现这个功能.但是MySQL并没有很好地支持中文,如果要使用全文索引的话,还是考虑一下搜索引擎吧(至少目前我所了解到的资料是这么说……我觉得他或许会有变得强大和能够支持的一天吧)

空间索引(了解):空间索引是指依据空间对象的位置和形状或空间对象之间的某种空间关系按一定的顺序排列的一种数据结构,MySQL在5.7之后的版本支持了空间索引,因为自己还没有涉及到这一方面的知识,所以对空间索引也没有去了解,如果以后有接触,再反过来补充。

索引原理

平常听到的B+tree索引和Hash索引,我觉得并不能将他们称作索引。mysql中虽然定义的名称叫index_type,但是我看了下navicat的中文翻译称作为索引方法。

所以个人认为的理解应该是以B+tree实现的索引和以Hash实现的索引

他们是索引的实现原理,彼此之间的关系应该比作接口和实现类,我想把“接口”才称之为索引

B+tree

B+tree是由二叉树->平衡二叉树->B-tree(这个就是B树,B 树是为了磁盘或其它存储设备而设计的一种多叉平衡查找树。)演化而来的 ,这里不详细地去说B树这一系列的具体原理,只是简单介绍一下B+tree的特点,有兴趣的话可以看一下《BTree和B+Tree》爱星星的阿狸,后续如果有自己整理就挂出自己的链接

(敲重点)

  1. B+tree的非叶子结点不保存关键字记录指针,只进行数据索引,这样使得B+tree每个非叶子结点所能保存的关键字大大增加,树的层级更少,所以查询数据更快。
  2. B+tree的叶子结点保存了父结点的所有关键字记录的指针,所有数据地址必须要到叶子结点才能获取到,所以每次查询数据的次数都是一样的
  3. B+tree叶子结点的关键字从小到大有序排序,左边结尾数都会保存到右边节点开始数据的指针,遍历整棵树只需要遍历叶子结点即可,有利于数据库做全表扫描。

Hash

Hash的话大家一定不会陌生,通过hash算法实现把键值换算成新的hash值,hash索引查询非常快,只需要一次hash算法即可立即定位到相应的位置(图摘自《MySQL王者晋级之路》)

但是hash存在着如下限制

  1. 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。

  2. 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。

  3. 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

  4. 哈希索引只支持等值比较查询,包括=、IN()、<>(注意<>和<=>是不同的操作)。也不支持任何范围查询,例如WHERE price>100。

  5. 访问哈希索引的数据非常快,除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行进行比较,直到找到所有符合条件的行。

  6. 如果哈希冲突很多的话,一些索引维护操作的代价也会很高。例如,如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引,那么当从表中删除一行时,存储引擎需要遍历对应哈希值的链表中的每一行,找到并删除对应行的引用,冲突越多,代价越大。

然而InnoDB不支持创建hash索引

InnoDB虽然不支持哈希索引,但是有一个特殊的功能叫做自适应哈希索引,它会在内存中基于B-Tree索引的基础上面创建一个哈希索引,这让B+tree索引具备了一些哈希索引的优点。

Innodb存储引擎会监控对表上二级索引的查找,如果发现某二级索引被频繁访问,二级索引成为热数据,建立哈希索引可以带来速度的提升

自适应hash索引访问速度快,降低了对二级索引树的频繁访问资源,但是会占用innodb buffer pool。

其余的索引概念

覆盖索引:覆盖索引不是人为去创建的,是一种概念,也可以说是索引覆盖,覆盖索引的作用是避免回表,举个例子

在一个user表中对用户名(user_name)建立索引,然后进行查询,通过执行计划看到Using index,这个就表示使用到了覆盖索引

如果我们这时候我们再查询一个sex字段,此时再看Extra则为Null

在此介绍一下回表的概念,回顾下上面的那张图,辅助索引定位到主键索引,再定位到行记录,这就是回表。

辅助索引保存了自身和指向的id,当我们在用户名上建立了索引,那么该索引就具有了用户名和id两个信息,所以我们查询的时候,只需要查询索引,是不需要再去访问表的,因此能够加快访问速度。

引自Java开发手册:【推荐】利用覆盖索引来进行查询,避免回表

因此我们需要做一个具体的权衡,利用联合索引的方式进行一定的查询优化

联合索引:既然上面提到了联合索引,那么就继续向下说,联合索引又叫复合索引,是在表中两个或两个以上的列上创建索引。利用索引中的附加列,可以缩小检索的段池范围,更快地搜索到数据。创建语法同普通索引一样

CREATE INDEX idx_c1_c2 ON t(c1,c2)

联合索引必须要满足最左前缀原则,一般把选择性高的列放在前面,一条查询语句可以只使用索引中的一部分。

前缀索引:对于BLOB、TEXT,或者很长的VARCHAR类型的列,为它们前几个字符建立索引,这样的索引就叫前缀索引。这样建立起来的索引更小,所以查询更快。

前缀索引不能在ORDER BY或GROUP BY中使用,也不能把它们用作覆盖索引

ALTER TABLE table_name ADD KEY(column_name(prefix_length))

(敲重点)

索引能够带来很大地效率提升,提高数据检索效率、聚合函数效率、排序效率。

创建索引应该在

  1. 经常被查询的列上(一般放在where条件后面)
  2. 经常用于表连接的列
  3. 经常排序分组的列(order by 或 group by后面的字段)

建立索引的四不要

  1. 选择性低的字段不要创建索引(例如性别、状态,索引的区分度应该是越大越好,例如唯一属性的字段上,可以使用SELECT COUNT(DISTINCT 字段)/COUNT(*) FROM 表)来看区分度
  2. 很少查询的列不要创建索引(这不浪费吗)
  3. 大数据类型字段不要创建索引(索引本身也是占有空间的,数据非常多的字段导致索引占用空间也非常大)
  4. 尽量避免使用NULL,应该指定字段为NOT NULL(在MySQL中,含有空值的列很难进行查询优化,它们会使索引、索引的统计信息及比较运算更加复杂。可以使用空字符串代替空值)

使用不到索引的情况

  1. 通过索引扫描的行记录数超过全表的30%, 优化器就不会走索引,而变成全表扫描。
  2. 联合索引中,第一个查询条件不是最左索引列。
  3. 联合索引中,第一个索引列使用范围查询,只能使用到部分索引,有ICP出现(范围查询是指<、=、<=、 BETWEEN and)。
  4. 联合索引中,第一个查询条件不是最左前缀列。
  5. 模糊查询条件列最左以通配符%开始(可以考虑放到子查询里面)。
  6. 两个单列索引,一个用于检索,一个用于排序。这种情况下只能使用到一个索引。因为查询语句中最多只能使用一个索引,考虑建立联合索引。
  7. 查询字段.上面有索引,但是使用了函数运算。

关于SQL的优化

引用Java开发手册中的一段话:SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好

但是对于SQL的优化不仅仅是建立索引,我们更不能盲目地建立索引,滥用索引

SQL优化思路

  1. 先看表的数据类型是否设计得合理,有没有遵守选取数据类型越简单越小的原则。
  2. 表中的碎片是否整理。
  3. 表的统计信息是否收集,只有统计信息准确,执行计划才可以帮助我们优化SQL。
  4. 查看执行计划,检查索引的使用情况,没有用到索引,考虑创建。
  5. 在创建索引之前,还要查看索引的选择性,来判断这个字段是否合适创建索引。(所谓索引的选择性是指不重复的索引值(也称为基数,cardinality) 和数据表的记录总数的比值。索引的选择性越高则查询效率越高(越接近于1越高),因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。主键索引和唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。)
  6. 创建索引之后,再查看一下执行计划,对比这两次的结果,看是否查询效率提高了。
     

结束语

至此索引的概念基本梳理完了,之后随着自己的提升也会再返回过来不断修改这篇文章,大家可以注意下版本的变更

 

参考资料

1、《MySQL王者晋级之路》 张甦

2、《Mysql聚簇索引和非聚簇索引原理(数据库)》 小楼东风细雨

3、《mysql覆盖索引与回表》Harri2012

4、《数据库中的索引技术——哈希索引》简之

5、《码出高效——阿里巴巴Java开发手册》

6、《Mysql使用全文索引(FullText index)》 程序猿小哈

7、《BTree和B+Tree》爱星星的阿狸

8、《InnoDB关键特性之自适应hash索引》

 

 

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读