MYSQL 索引类型

Wesley13
• 阅读 584

  在MYSQL中,索引是在引擎层中而不是服务器层实现的。所以并没有统一的索引标准:不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎

都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同

(1)B-Tree索引

    如果没有特别指明类型的话,那么就代指为B-Tree引擎,它使用B-Tree数据结构来存储数据、大多数MYSQL的引擎都支持这种索引。

Archive引擎是一个例外(Archive在5.1之后在开始支持单个自增列的索引)。

    虽然都是B-Tree索引,但是存储引擎以不同的方式使用B-Tree索引,性能也各有不同,各有优劣。例如:MyISAM使用前缀压缩技术使得索引更小,

但是InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的列

    B-Tree通常以为这所有的值都是按顺序存储的,并且每个叶子页到跟的距离相同。如下图(InnoDB中的索引工作,MYISAM使用的结构有所不同,但是基本思想类似)

MYSQL 索引类型

    B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描,只需要从索引的根节点开始进行搜索。根节点中的保存了指向子节点

的指针,存储引擎根据这些指针开始向下查找,通过比较节点页的值和要查找的值向下搜索。这些指针实际上定义了子节点页中值的上下限,最终引擎必定会找到想用的值,除非这个值不存在

    叶子结点保存的是指向被索引数据的指针,而不是其他的节点页

    B-Tree对索引列是顺序组织存储的,所以很适合查找范围数据。例如,在一个基于文本域的索引树上,按字母顺序传递连续的值进行查找是非常合适的,所以像“找出是有意以1-K开头的名字”这样的查找效率会非常高

    B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

    (1)全值匹配:全值匹配真的是和索引中的所有列进行匹配

    (2)匹配最左前缀:只使用索引的第一列

    (3)匹配列前缀:只匹配某一列的值的开头部分

    (4)匹配范围值:匹配区间内的权值(最好使用与索引的第一列)

    (5)精确匹配某一列并范围匹配另外一列:第一列全匹配,第二列进行范围匹配

    (6)只访问索引的查询:B-Tree通常可以支持“只访问索引的查询”,即查询只需要访问索引,而无需访问数据列。

    因为索引树种的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的order by操作。一般来说如果B-Tree可以按照某种方式查找到的值,那么也可以用按照这种方式用于排序。所以,如果Order by字句满足前面列出的集中查询类型,则这个索引也可以满足对应的排序需求

    下列是一些关于B-Tree索引的限制

    (1)如果不是按照索引的最左列进行查找,那么就无法使用索引

    (2)不能跳过索引中的列。比如你建了一个关于A B C的索引,那么你如果要查询B,那么必须也要查A,不能跳过A去查B,或者说查AC,不查B

    (3)如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找

    通过以上大家应该都知道了索引列顺序的重要性:以上所说的限制都和索引列顺序有关。在优化性能的时候,可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

    (2)哈希索引

    哈希索引是基于哈希表实现的索引。只有精确匹配索引索引列的查询才有效。对于每一行数据,在存储引擎都会对索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每一个数据行的指针

    在MYSQL中,只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型(memory引擎也支持B-Tree索引)

值得一提的是,Memory引擎支持的是非唯一哈希索引(如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中)

下面来看一个栗子

create table testhash{
    fname varchar(50) not null,
    lname varchar(50) not null,
    key using hash(fname)
}Engine=MEMORY;

表中数据如下

MYSQL 索引类型

假设索引使用家乡的哈希函数F(),他返回下面的值:

MYSQL 索引类型

则哈希索引的结构如下

MYSQL 索引类型

注意每个槽的编号是顺序的,但是数据行不是

看如下查询

MYSQL 索引类型

    MYSQL会计算'Peter'的哈希值,并使用该值寻找对应的记录指针。因为f('Peter')=8784,所以MYSQL在索引中查找8784,可以找到指向第三行的指针,最后一部是比较第三行的值是否为'Peter',以确保就是要查找的行

    因为索引自身秩序存储对应的的哈希值,所以索引的结构十分紧凑,这也让哈希索引查找的速度非常快。然而哈希索引也有他的限制

    (1)哈希索只包含哈希值和行指针,而不存储字段值。所以不能使用索引中的值来避免读取航。不过访问内存中的行速度很快,所以大部分情况下这一点对性能的影响并不大

    (2)哈希索引数据并不是按照索引顺序存储的,所以不能排序

    (3)哈希索引也不支持部分索引匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值。比如使用了AB来建立哈希索引,但是你查询的只查询A,那么就无法使用该索引

    (4)哈希索引支持等值比较查询,包括=、IN()、<=>(这个不是<>)。也不支持任何范围查询,比如where xxx>xxx

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

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

    因为这些限制,哈希索引只是用与某些特定的场合。而一旦适合哈希索引,则他所带来的性能提升是非常大的,比如数据仓库中的星型schema。

    (3)空间数据索引(R-Tree)   

    MyISAM表支持空间数据索引,可以用作地理数据存储。和B-Tree索引不同,这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时,可以有效的使用任意维度来组合查询。必须使用MySQL的IGS相关函数在维护数据。MySQL的GIS支持并不完善,所以大部分不会使用这个特性

    (4)全文索引

    全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。他有许多需要注意的细节,如停用词、词干和复数、布尔搜索等。全文索引更类似与搜索引擎做的事情,而不是简单的where条件匹配

    在相同的列上同时创建全文索引和基于值的B-Tree索引不会用冲突,全文索引适用于match against操作,而不是普通的where

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
添砖java的啾 添砖java的啾
2年前
distinct效率更高还是group by效率更高?
目录00结论01distinct的使用02groupby的使用03distinct和groupby原理04推荐groupby的原因00结论先说大致的结论(完整结论在文末):在语义相同,有索引的情况下groupby和distinct都能使用索引,效率相同。在语义相同,无索引的情况下:distinct效率高于groupby。原因是di
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Peter20 Peter20
3年前
MYSQL里的索引类型介绍
首先要明白索引(index)是在存储引擎(storageengine)层面实现的,而不是在server层面。不是所有的存储引擎支持有的索引类型。1、BTREE最常见的索引类型,他的思想是所有的值(被索引的列)都是被排过序的,每个叶节点到根节点的距离是相等的,所以适合用来找某一范围内的数据,而且可以直接支持排序(orderby)支持innoDB和MyIS
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
MySQL索引的索引长度问题
MySQL的每个单表中所创建的索引长度是有限制的,且对不同存储引擎下的表有不同的限制。在MyISAM表中,创建组合索引时,创建的索引长度不能超过1000,注意这里索引的长度的计算是根据表字段设定的长度来标量的,例如:createtabletest(idint,name1varchar(300),name2varchar(300),nam
Wesley13 Wesley13
2年前
MySQL索引背后的数据结构及算法原理
摘要本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
4个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这