如大家所知道的,Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。
那么,这几种索引有什么功能和性能上的不同呢?
FULLTEXT
即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE INDEX创建FULLTEXT索引,要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。
全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前,这样一个查询语句是要进行遍历数据表操作的,可见,在数据量较大时是极其的耗时的,如果没有异步IO处理,进程将被挟持,很浪费时间,当然这里不对异步IO作进一步讲解,想了解的童鞋,自行谷哥。
全文索引的使用方法并不复杂:
创建ALTER TABLE table ADD INDEX `FULLINDEX` USING FULLTEXT(`cname1`[,cname2…]);
使用SELECT * FROM table WHERE MATCH(cname1[,cname2…]) AGAINST ('word' MODE );
其中, MODE为搜寻方式(IN BOOLEAN MODE ,IN NATURAL LANGUAGE MODE ,IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION)。
关于这三种搜寻方式,愚安在这里也不多做交代,简单地说,就是,布尔模式,允许word里含一些特殊字符用于标记一些具体的要求,如+表示一定要有,-表示一定没有,*表示通用匹配符,是不是想起了正则,类似吧;自然语言模式,就是简单的单词匹配;含表达式的自然语言模式,就是先用自然语言模式处理,对返回的结果,再进行表达式匹配。
对搜索引擎稍微有点了解的同学,肯定知道分词这个概念,FULLTEXT索引也是按照分词原理建立索引的。西文中,大部分为字母文字,分词可以很方便的按照空格进行分割。但很明显,中文不能按照这种方式进行分词。那又怎么办呢?这个向大家介绍一个Mysql的中文分词插件Mysqlcft,有了它,就可以对中文进行分词,想了解的同学请移步Mysqlcft,当然还有其他的分词插件可以使用。
HASH
Hash这个词,可以说,自打我们开始码的那一天起,就开始不停地见到和使用到了。其实,hash就是一种(key=>value)形式的键值对,如数学中的函数映射,允许多个key对应相同的value,但不允许一个key对应多个value。正是由于这个特性,hash很适合做索引,为某一列或几列建立hash索引,就会利用这一列或几列的值通过一定的算法计算出一个hash值,对应一行或几行数据(这里在概念上和函数映射有区别,不要混淆)。在java语言中,每个类都有自己的hashcode()方法,没有显示定义的都继承自object类,该方法使得每一个对象都是唯一的,在进行对象间equal比较,和序列化传输中起到了很重要的作用。hash的生成方法有很多种,足可以保证hash码的唯一性,例如在MongoDB中,每一个document都有系统为其生成的唯一的objectID(包含时间戳,主机散列值,进程PID,和自增ID)也是一种hash的表现。额,我好像扯远了-_-!
由于hash索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。那为什么还需要其他的树形索引呢?
在这里愚安就不自己总结了。引用下园子里其他大神的文章:来自 14的路 的MySQL的btree索引和hash索引的区别
(1)Hash 索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。
(2)Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;
(3)Hash 索引不能利用部分索引键查询。
对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。
(4)Hash 索引在任何时候都不能避免表扫描。
前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash 索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
(5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。
愚安我稍作补充,讲一下HASH索引的过程,顺便解释下上面的第4,5条:
当我们为某一列或某几列建立hash索引时(目前就只有MEMORY引擎显式地支持这种索引),会在硬盘上生成类似如下的文件:
hash值 | 存储地址 |
1db54bc745a1 | 77#45b5 |
4bca452157d4 | 76#4556,77#45cc… |
…
hash值即为通过特定算法由指定列数据计算出来,磁盘地址即为所在数据行存储在硬盘上的地址(也有可能是其他存储地址,其实MEMORY会将hash表导入内存)。
这样,当我们进行WHERE age = 18 时,会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。
所以,每次查询时都要遍历hash表,直到找到对应的hash值,如(4),数据量大了之后,hash表也会变得庞大起来,性能下降,遍历耗时增加,如(5)。
BTREE
BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中,相信学过数据结构的童鞋都对当初学习二叉树这种数据结构的经历记忆犹新,反正愚安我当时为了软考可是被这玩意儿好好地折腾了一番,不过那次考试好像没怎么考这个。如二叉树一样,每次查询都是从树的入口root开始,依次遍历node,获取leaf。
BTREE在MyISAM里的形式和Innodb稍有不同
在 Innodb里,有两种形态:一是primary key形态,其leaf node里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据。二是secondary index,其leaf node和普通的BTREE差不多,只是还存放了指向主键的信息.
而在MyISAM里,主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里,leaf node里存放的不是主键的信息,而是指向数据文件里的对应数据行的信息.
RTREE
RTREE在mysql很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE,RTREE的优势在于范围查找.
各种索引的使用情况
(1)对于BTREE这种Mysql默认的索引类型,具有普遍的适用性
(2)由于FULLTEXT对中文支持不是很好,在没有插件的情况下,最好不要使用。其实,一些小的博客应用,只需要在数据采集时,为其建立关键字列表,通过关键字索引,也是一个不错的方法,至少愚安我是经常这么做的。
(3)对于一些搜索引擎级别的应用来说,FULLTEXT同样不是一个好的处理方法,Mysql的全文索引建立的文件还是比较大的,而且效率不是很高,即便是使用了中文分词插件,对中文分词支持也只是一般。真要碰到这种问题,Apache的Lucene或许是你的选择。
(4)正是因为hash表在处理较小数据量时具有无可比拟的素的优势,所以hash索引很适合做缓存(内存数据库)。如mysql数据库的内存版本Memsql,使用量很广泛的缓存工具Mencached,NoSql数据库redis等,都使用了hash索引这种形式。当然,不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。
(5)至于RTREE,愚安我至今还没有使用过,它具体怎么样,我就不知道了。有RTREE使用经历的同学,到时可以交流下!
相关推荐
在深入探讨MySQL Innodb索引之前,我们先了解几种基本的树形数据结构,包括二叉搜索树、B树、B+树以及B*树。 ##### 1.1 搜索二叉树(Binary Search Tree) 搜索二叉树是一种特殊的二叉树,每个节点至多有两个子...
B-Tree索引支持以下几种操作: - **等值查找**:查找所有键值为特定值的行。 - **范围查找**:查找所有键值大于某个值或介于两个值之间的行。 - **前缀查找**:通过使用`LIKE 'abc%'`这样的模式进行查找。 但是,B...
接下来,我们需要了解MySQL支持的几种主要索引类型: 1. **主键索引**:主键是一个唯一的标识符,确保每行数据的唯一性,不允许有重复且不能为NULL。 2. **唯一索引**:与主键类似,也保证列的唯一性,但允许NULL值...
在MySQL中,索引主要分为以下几种类型: 1. 主键索引(Primary Key Index):每个表只能有一个主键,不允许有重复值,且不能为NULL。 2. 唯一索引(Unique Index):与主键类似,唯一索引也确保列中的值是唯一的,...
MySQL中的索引是一种提高查询效率的关键技术,它利用特定的数据结构,如B-Tree,对数据库表中的某一列或多列进行排序,使得存储引擎能够快速定位到数据行。索引优化是数据库性能优化的核心策略之一。 单列索引是...
在MyISAM存储引擎中,默认的索引类型是BTREE,适用于大多数情况。普通索引允许字段中的值重复,主要目的是加速查询。 2. 唯一索引(Unique):它确保索引的每一项都是唯一的,不允许重复。这种索引可以用于确保...
下面将详细探讨几种常见的索引失效情况及其原因。 首先,索引不存储`NULL`值。在MySQL中,单列索引不会存储`NULL`值,而复合索引则不存储所有列都是`NULL`的值。这是因为`NULL`值在比较操作中具有特殊性,不适用于...
在实际应用中,选择合适的索引类型(如主键索引、唯一索引、普通索引、全文索引等)、索引字段(选择区分度高的字段)、以及索引策略(如复合索引、覆盖索引等)都是优化查询性能的重要手段。同时,过度使用索引也...
为了减少磁盘I/O操作的数量,MySQL采用了B-Tree和B+Tree这两种索引来优化查询效率。 ##### B-Tree(B树) - **定义**:B树是一种平衡多路查找树,适用于存储大量数据的情况。 - **特性**: - 每个节点最多含有m个...
MySQL中的索引主要有以下几种类型: 1. 单值索引:索引包含单个列。 2. 唯一索引:索引列的值必须唯一,可以有空值,通常用于主键。 3. 复合索引:索引包含多个列,适用于多列查询条件的情况。 索引的结构主要包括...
可以使用以下几种方式创建普通索引: - **创建索引**:`CREATE INDEX indexName ON tableName (tableColumns(length));` - 对于`CHAR`和`VARCHAR`类型字段,可以指定`length`参数,但不强制;对于`BLOB`和`TEXT`...
首先,索引可以分为几种基本类型。普通索引是最基础的索引形式,没有任何限制,允许在查询时快速定位到特定数据。创建普通索引的方式有多种,包括在创建表时指定、修改表结构时添加,或者单独创建索引。例如,可以...
本文将深入探讨MySQL中的几种主要索引类型及其相关概念。 首先,从数据结构的角度来看,MySQL索引主要包括以下三种: 1. **B+树索引**:这是最常见的索引类型,InnoDB存储引擎默认使用B+树来构建索引。B+树的特点...
综上所述,理解并合理应用各种索引类型和创建原则,对于提升MySQL数据库的查询性能至关重要。同时,结合业务需求和查询模式,持续监控和优化索引策略,可以有效减少查询延迟,提高数据库系统的整体效率。
2. **B+树索引**:这是最常见的索引类型,特别是InnoDB存储引擎。B+树的特点是所有叶子节点通过指针连接,非叶子节点不存储数据,适合于范围查询和排序。B+树的高度较低,减少了磁盘I/O次数,优化了查询效率。 3. *...
本文主要聚焦于BTree索引,这是MySQL中最常见的一种索引类型,尤其是在日常使用中。BTree索引(包括B+Tree)是大多数数据库系统和文件系统选择的索引结构,因为它能够适应大容量数据并提供高效的查找性能。 首先,...
在MySQL中,可以为表的列创建索引,常见的索引类型有B-Tree、Hash、Full-text和R-tree。B-Tree索引是最常用的,适用于大部分查询场景;Hash索引则在等值查询时速度极快,但不支持范围查询;Full-text索引用于全文...
本文将详细介绍几种主要的MySQL索引类型及其应用。 首先,普通索引是最基本的索引类型,没有特殊限制。可以通过`CREATE INDEX`、`ALTER TABLE`或在创建表时直接定义来创建。例如,为`username`字段创建普通索引,...
在MySQL中,B+树是最常见的索引类型之一。 **B+树**的特点在于所有实际数据都存储在叶子节点中,而非叶子节点仅包含指向叶子节点的指针。这种结构使得B+树相比于传统的B树具有以下几个优势: 1. **更多的存储地址*...