MySQL索引的类型

前言

MySQL索引是面试中最常见的问题,笔者前几天接到一个HR小姐姐的面试电话,小姐姐说公司实行996,问我能不能接受?,我没996过,我哪里知道996是什么感觉呀啊,我就敷衍的说了一句应该可以吧,然后我回到家仔细想了想,还是委婉的拒绝了面试的邀请。

最有趣的是第一次接受HR小姐姐的技术面试,问了我一堆MySQL索引的问题,这是要闹哪样?被一个HR面试了一堆技术问题,我表示不服(开个玩笑),侧面也反映了公司估计确实很忙,HR需要通过技术问题过滤掉一部分简历,这样就可以减少开发的时间成本。

然鹅,上周突然github上的一个996-icu的repository突然就火了,截止到目前已经11K的star了。就是抗议现在很多的公司实行的996政策,很多公司被口诛笔伐,我也看了许多关于996的讨论,我总结的关键点是:开发觉得强制996让他们不爽,把996当成了理所应当让他们不爽。他们觉得如果公司忙可以接受加班,而且大部分的开发如果事情没做完,都会自觉加班。我个人表示赞同这些观点。

额,说了那么多废话,现在就开始介绍些MySQL索引的类型,其中部分内容参考了《MySQL高性能》。

MySQL索引的类型

索引的类型有多种,可以为不同的场景提供更好的性能。在MySQL中,索引是在存储引擎层工作的。所以没有统一的索引标准,不同的索引工作方式是不同的,也不是所有的引擎都支持所有的索引类型。以下五种是很常用的索引类型,我们就来分析他们的优点和缺点。

索引优化应该是对查询性能优化最有效的手段了。

B-Tree索引

当人们讨论索引的时候,如果没有特殊说明指的就是B-Tree索引,大多数MySQL引擎都是支持这种索引的。不同的MySQL引擎实现B-Tree索引的方法略有不同,各有优势。例如,MyISAM使用前缀压缩技术使得索引的空间更小,但InnoDB则是按照原数据进行存储。再比如MyISAM索引是通过数据的物理地址引用被索引的行,而InnoDB则是通过主键字段引用被索引的行。

B-Tree索引能大大加快查询的效率。因为查询过程不需要要全表扫描所有的行,只需要从根节点向下搜索,比改节点小的就向左子节点查询,否则就向右子节点查询,最终就能快速的找到要查询的值。

假设有如下数据表:

CREATE TABLE People(
  last_name varchar(20) not null,
  first_name varchar(20) not null,
  dob date null,
  gender enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

对于表中的每一行数据,索引包含了last_name, first_name, dob列的值,如下是该索引的存储结构图。

索引树中部分条目示例
索引树中部分条目示例

需要注意的是对于多列索引,是根据CREATE TABLE时定义索引的顺序来决定的,例如我们上面创建的索引,在节点上是按照last_name然后first_name最后dob字段来存储的,上图右下角当last_namefirst_name相等时,是按照生日的日期来排序的。

可以使用B-Tree索引的查询类型

  • 全值查询

    全值查询是指匹配所有的索引列。例如前面提到的索引可以用于查找姓名为Cuba Allen、出生于1960-01-01的人。

  • 匹配最左前缀查询

    匹配最左前缀查询是指按照从左到右的顺序匹配索引字段,例如前面提到的索引用于查询姓为Allen的人。

  • 匹配列前缀查询

    匹配列前缀,就是匹配某个列最开头的部分。例如前面提到的索引可用于查找所有以Ja开头的姓。具体查询语句。

    SELECT * FROM People WHERE last_name Like 'Ja%';
    
  • 匹配范围查询

    范围查询顾名思义就是查询一段范围,例如前面提到的索引可用于查询姓AllenAstaire之间的所有人。

  • 匹配某一列并范围匹配到另一列

    前面提到的索引也可以查找所有姓为Allen,并且名字以字母K开头(例如Kim、Karl等)。即第一列lsit_name全匹配,第二列first_name列前缀匹配。

  • 只访问索引的查询

    B-Tree支持“只访问索引的查询”,即查询的时候只需要访问索引,无需访问数据行。这种查询方式叫“覆盖索引”,简单来说覆盖索引避免了回表查询数据行的开销,提高了查询效率,感兴趣的同学可以自行搜索“覆盖索引“的相关知识。

关于B-Tree索引使用的一些限制

  • B-Tree索引如果不是从最左边的列开始查询,则无法使用索引。例如前面例子中索引无法查询名字为Kim的人,以及也无法查询某个特定生日的人,因为这两列不是最左列。类似也无法查找姓氏以某个字母结尾的人。

  • 不能跳过索引的列,前面提到的索引无法查询姓为Allen且生日为1960-01-01的所有人,如果是这样的查询语句,只能用到索引的第一列。

  • 如果查询中有某个列用到了范围查询,则其右边的所有列均不能用到索引优化查询。例如这个查询语句:

    SELECT * FROM People WHERE last_name='Allen' AND first_name like 'K%' AND dob='1930-07-12';
    

上面的查询语句只能用到索引的前两列,因为第二个查询条件是一个范围查询。如果范围查询的条件是有限的,可以通过多个等值条件查询来代替。

小结

通过上面一系列的讲解,我们可以发现,创建索引的顺序简直太重要了,查询条件的顺序也是非常的重要。我们在工作中创建MySQL表的时候一定要注意避开这些坑。我们会发现,由于业务的变化,我们其实需要回头对原来创建表的结构进行优化,这些优化就包括对索引结构的优化,

哈希索引

哈希索引(hash index)是基于哈希表实现的,只有精确等值匹配索引所有列的查询才是有效的。在MySQL中只有Memory引擎支持哈希索引,这也是Memory引擎默认的索引类型,下面看一个例子。假设有如下表:

CREATE TABLE testhash(
fname varchar(50) not null,
lname varchar(50) not null,
KEY USING HASH(fname)
)ENGINE=MEMORY;

插入数据:

INSERT INTO testhash(fname, lname) values('Arjen', 'Lentz'),('Baron', 'Schwartz'),('Peter', 'Zitsevo'), ('Vadim', 'Tachenkoe');

表数据如下

fnamelname
ArjenLentz
BaronSchwartz
PeterZaitsev
VadimTkachenko

假设有哈希函数f(),他返回如下数据(哈希值都是示例数据):

f(‘Arjen’) = 2098
f(‘Baron’) = 4920
f(‘Peter’) = 8372
f(‘Vadim’) = 5293

哈希索引的存储结构如下:

槽(Slot)值(value)
2098指向第1行
4920指向第2行
5293指向第4行
8372指向第3行

现在我们来看如下查询:

SELECT * FROM testhash WHERE fname='Peter';

上面的查询语句MySQL先计算’Peter’的哈希值,并使用该值寻找到对应的指针记录,最后一步就是要比较查询的结果是否为’Peter’。哈希索引只需要存储对应的哈希值,所以哈希索引结构比较紧凑,这也使得哈希索引的查询速度非常的快。然而哈希索引也有一些限制:

  • 哈希索引存储的是行指针,而没有存储行数据,所以每次查询无法避免读取行。
  • 哈希索引不是按照索引顺序存储的,所以无法用于排序。
  • 哈希索引不支持部分索引列匹配查询,因为哈希索引存储是全部索引列来计算哈希值的。
  • 哈希索引只能用于等值查询,不支持任何的范围查询。
  • 如果哈希冲突很多的话,一些索引的维护成本就会很高。

因为以上这些限制,哈希索引只适用于某些特定的应用场景,而一旦使用哈希索引,则它带来的性能提升是非常明显的。

空间数据索引(R-Tree)

MyISAM表支持空间索引,可以用于地理数据的存储。和B-Tree索引不同,这类索引无需前缀查询。空间索引会从各个维度来索引数据。查询时,可以有效的使用任意维度的组合来查询。必须使用MySQL的GIS相关的的函数,如MBRCONTAINS()等来维护数据。MySQL的GIS函数还不够完善,所以大部分人都不会使用这个特性。开源数据库中对GIS的解决方案做的比较好的是PostgreSQL的PostGIS。

全文索引

全文索引是一种特殊的索引方式,他查找的是文本中的关键词,一般用户关键词检索的业务场景,全文索引更像是类似于搜索引擎做的事。全文索引在实际工作中用的非常的少,一般数据量稍大一点的关键词检索都用elasticsearch这种专门提供搜索引擎的框架。

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页