热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

Mysql目前主要有以下几种索引方式

Mysql目前主要有以下几种索引方式:FULLTEXT,HASH,BTREE,RTREE。那么,这几种索引有什

Mysql目前主要有以下几种索引方式:FULLTEXT,HASH,BTREE,RTREE

那么,这几种索引有什么功能和性能上的不同呢?

FULLTEXT

即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHARVARCHAR ,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的路 MySQLbtree索引和hash索引的区别

&#xff08;1&#xff09;Hash 索引仅仅能满足"&#61;","IN""<&#61;>"查询&#xff0c;不能使用范围查询。 
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值&#xff0c;所以它只能用于等值的过滤&#xff0c;不能用于基于范围的过滤&#xff0c;因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系&#xff0c;并不能保证和Hash运算前完全一样。 
&#xff08;2&#xff09;Hash 索引无法被用来避免数据的排序操作。 
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值&#xff0c;而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样&#xff0c;所以数据库无法利用索引的数据来避免任何排序运算&#xff1b; 
&#xff08;3&#xff09;Hash 索引不能利用部分索引键查询。 
对于组合索引&#xff0c;Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值&#xff0c;而不是单独计算Hash 值&#xff0c;所以通过组合索引的前面一个或几个索引键进行查询的时候&#xff0c;Hash 索引也无法被利用。 
&#xff08;4&#xff09;Hash 索引在任何时候都不能避免表扫描。 
前面已经知道&#xff0c;Hash 索引是将索引键通过 Hash 运算之后&#xff0c;将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中&#xff0c;由于不同索引键存在相同 Hash 值&#xff0c;所以即使取满足某个 Hash 键值的数据的记录条数&#xff0c;也无法从 Hash 索引中直接完成查询&#xff0c;还是要通过访问表中的实际数据进行相应的比较&#xff0c;并得到相应的结果。 
&#xff08;5&#xff09;Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 
对于选择性比较低的索引键&#xff0c;如果创建 Hash 索引&#xff0c;那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦&#xff0c;会浪费多次表数据的访问&#xff0c;而造成整体性能低下。

 

我稍作补充&#xff0c;讲一下HASH索引的过程&#xff0c;顺便解释下上面的第4,5条&#xff1a;

当我们为某一列或某几列建立hash索引时&#xff08;目前就只有MEMORY引擎显式地支持这种索引&#xff09;&#xff0c;会在硬盘上生成类似如下的文件&#xff1a;


hash 

存储地址    

1db54bc745a1

77#45b5 

4bca452157d4

76#4556,77#45cc…

hash值即为通过特定算法由指定列数据计算出来&#xff0c;磁盘地址即为所在数据行存储在硬盘上的地址&#xff08;也有可能是其他存储地址&#xff0c;其实MEMORY会将hash表导入内存&#xff09;。

这样&#xff0c;当我们进行WHERE age &#61; 18 时&#xff0c;会将18通过相同的算法计算出一个hash&#61;&#61;>hash表中找到对应的储存地址&#61;&#61;>根据存储地址取得数据。

所以&#xff0c;每次查询时都要遍历hash表&#xff0c;直到找到对应的hash值&#xff0c;如&#xff08;4&#xff09;&#xff0c;数据量大了之后&#xff0c;hash表也会变得庞大起来&#xff0c;性能下降&#xff0c;遍历耗时增加&#xff0c;如&#xff08;5&#xff09;。

BTREE

BTREE索引就是一种将索引值按一定的算法&#xff0c;存入一个树形的数据结构中&#xff0c;相信学过数据结构的童鞋都对当初学习二叉树这种数据结构的经历记忆犹新&#xff0c;反正我当时为了软考可是被这玩意儿好好地折腾了一番&#xff0c;不过那次考试好像没怎么考这个。如二叉树一样&#xff0c;每次查询都是从树的入口root开始&#xff0c;依次遍历node&#xff0c;获取leaf

BTREEMyISAM里的形式和Innodb稍有不同

 Innodb里&#xff0c;有两种形态&#xff1a;一是primary key形态&#xff0c;其leaf node里存放的是数据&#xff0c;而且不仅存放了索引键的数据&#xff0c;还存放了其他字段的数据。二是secondary index&#xff0c;其leaf node和普通的BTREE差不多&#xff0c;只是还存放了指向主键的信息.

而在MyISAM里&#xff0c;主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里&#xff0c;leaf node里存放的不是主键的信息&#xff0c;而是指向数据文件里的对应数据行的信息.

RTREE

RTREEmysql很少使用&#xff0c;仅支持geometry数据类型&#xff0c;支持该类型的存储引擎只有MyISAMBDbInnoDbNDbArchive几种。

相对于BTREE&#xff0c;RTREE的优势在于范围查找.

各种索引的使用情况

&#xff08;1&#xff09;对于BTREE这种Mysql默认的索引方式&#xff0c;具有普遍的适用性

&#xff08;2&#xff09;由于FULLTEXT对中文支持不是很好&#xff0c;在没有插件的情况下&#xff0c;最好不要使用。其实&#xff0c;一些小的博客应用&#xff0c;只需要在数据采集时&#xff0c;为其建立关键字列表&#xff0c;通过关键字索引&#xff0c;也是一个不错的方法&#xff0c;至少我是经常这么做的。

&#xff08;3&#xff09;对于一些搜索引擎级别的应用来说&#xff0c;FULLTEXT同样不是一个好的处理方法&#xff0c;Mysql的全文索引建立的文件还是比较大的&#xff0c;而且效率不是很高&#xff0c;即便是使用了中文分词插件&#xff0c;对中文分词支持也只是一般。真要碰到这种问题&#xff0c;ApacheLucene或许是你的选择。

&#xff08;4&#xff09;正是因为hash表在处理较小数据量时具有无可比拟的素的优势&#xff0c;所以hash索引很适合做缓存&#xff08;内存数据库&#xff09;。如mysql数据库的内存版本Memsql&#xff0c;使用量很广泛的缓存工具Mencached&#xff0c;NoSql数据库redis等&#xff0c;都使用了hash索引这种形式。当然&#xff0c;不想学习这些东西的话MysqlMEMORY引擎也是可以满足这种需求的。

 

&#xff08;5&#xff09;至于RTREE&#xff0c;至今还没有使用过&#xff0c;它具体怎么样&#xff0c;我就不知道了。有RTREE使用经历的同学&#xff0c;到时可以交流下&#xff01;


推荐阅读
  • 本文介绍了在 MySQL 中如何使用正则表达式来提高查询效率,通过具体示例展示了如何筛选包含中文字符的记录,并详细解释了正则表达式的各种特殊字符和结构。 ... [详细]
  • 如何在Java中使用DButils类
    这期内容当中小编将会给大家带来有关如何在Java中使用DButils类,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。D ... [详细]
  • 本文详细介绍了 PHP 中对象的生命周期、内存管理和魔术方法的使用,包括对象的自动销毁、析构函数的作用以及各种魔术方法的具体应用场景。 ... [详细]
  • php更新数据库字段的函数是,php更新数据库字段的函数是 ... [详细]
  • 数据类型和操作数据表2.1MySQL类型之整型2.2MySQL数据类型之浮点型2.3日期时间型DATE1支持时间:1000年1月1日~9999年12月31日DATETIME ... [详细]
  • 本文详细介绍了MySQL数据库的基础语法与核心操作,涵盖从基础概念到具体应用的多个方面。首先,文章从基础知识入手,逐步深入到创建和修改数据表的操作。接着,详细讲解了如何进行数据的插入、更新与删除。在查询部分,不仅介绍了DISTINCT和LIMIT的使用方法,还探讨了排序、过滤和通配符的应用。此外,文章还涵盖了计算字段以及多种函数的使用,包括文本处理、日期和时间处理及数值处理等。通过这些内容,读者可以全面掌握MySQL数据库的核心操作技巧。 ... [详细]
  • 在处理数据库中所有用户表的彻底清除时,目前尚未发现单一命令能够实现这一目标。因此,需要采用一种较为繁琐的方法来逐个删除相关表及其结构。具体操作可以通过编写PL/SQL脚本来实现,该脚本将动态生成并执行删除表的SQL语句。尽管这种方法相对复杂,但在缺乏更简便手段的情况下,仍是一种有效的解决方案。未来或许可以通过数据库管理工具或更高版本的数据库系统提供更简洁的处理方式。 ... [详细]
  • DAO(Data Access Object)模式是一种用于抽象和封装所有对数据库或其他持久化机制访问的方法,它通过提供一个统一的接口来隐藏底层数据访问的复杂性。 ... [详细]
  • IOS Run loop详解
    为什么80%的码农都做不了架构师?转自http:blog.csdn.netztp800201articledetails9240913感谢作者分享Objecti ... [详细]
  • 如何在Linux服务器上配置MySQL和Tomcat的开机自动启动
    在Linux服务器上部署Web项目时,通常需要确保MySQL和Tomcat服务能够随系统启动而自动运行。本文将详细介绍如何在Linux环境中配置MySQL和Tomcat的开机自启动,以确保服务的稳定性和可靠性。通过合理的配置,可以有效避免因服务未启动而导致的项目故障。 ... [详细]
  • MySQL Decimal 类型的最大值解析及其在数据处理中的应用艺术
    在关系型数据库中,表的设计与SQL语句的编写对性能的影响至关重要,甚至可占到90%以上。本文将重点探讨MySQL中Decimal类型的最大值及其在数据处理中的应用技巧,通过实例分析和优化建议,帮助读者深入理解并掌握这一重要知识点。 ... [详细]
  • 在使用达梦数据库时,管理员可能会遇到连接频繁中断或特定SQL语句语法错误的问题。这些问题通常源于开发人员在创建对象时的不规范操作。为了解决这些问题,建议对数据库配置进行优化,并确保所有SQL语句符合达梦数据库的标准语法。此外,定期检查和维护数据库连接参数,以及对异常日志进行详细分析,也有助于及时发现并解决问题。 ... [详细]
  • 如何在PHP中获取数组中特定元素的索引位置
    在PHP中获取数组中特定元素的索引位置有多种方法。首先,可以使用 `array_search()` 函数,其语法为 `array_search(目标值, $array)`,该函数将返回匹配元素的第一个键名(即下标)。其次,也可以利用 `array_keys()` 函数,通过 `array_keys($array, 目标值)` 语法来获取所有匹配元素的键名列表。这两种方法都能有效解决数组元素定位的问题,具体选择取决于实际需求和性能考虑。 ... [详细]
  • MySQL的查询执行流程涉及多个关键组件,包括连接器、查询缓存、分析器和优化器。在服务层,连接器负责建立与客户端的连接,查询缓存用于存储和检索常用查询结果,以提高性能。分析器则解析SQL语句,生成语法树,而优化器负责选择最优的查询执行计划。这一流程确保了MySQL能够高效地处理各种复杂的查询请求。 ... [详细]
  • 在尝试对 QQmlPropertyMap 类进行测试驱动开发时,发现其派生类中无法正常调用槽函数或 Q_INVOKABLE 方法。这可能是由于 QQmlPropertyMap 的内部实现机制导致的,需要进一步研究以找到解决方案。 ... [详细]
author-avatar
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有