MySQL索引类型细讲汇总.docx

MySQL索引类型细讲汇总.docx

《MySQL索引类型细讲汇总.docx》由会员分享，可在线阅读，更多相关《MySQL索引类型细讲汇总.docx（19页珍藏版）》请在冰豆网上搜索。

MySQL索引类型细讲汇总

索引是快速搜索的关键。

MySQL索引的建立对于MySQL的高效运行是很重要的。

下面介绍几种常见的MySQL索引类型。

在数据库表中，对字段建立索引可以大大提高查询速度。

假如我们创建了一个mytable表：

CREATETABLEmytable（  IDINTNOTNULL,   usernameVARCHAR（16）NOTNULL ）;  我们随机向里面插入了10000条记录，其中有一条：

5555,admin。

在查找username="admin"的记录SELECT*FROMmytableWHEREusername='admin';时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。

相反，MySQL会扫描所有记录，即要查询10000条记录。

索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索包含多个列。

MySQL索引类型包括：

（1）普通索引

这是最基本的索引，它没有任何限制。

它有以下几种创建方式：

◆创建索引

CREATEINDEXindexNameONmytable（username（length））;如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length，下同。

◆修改表结构

ALTERmytableADDINDEX[indexName]ON（username（length））◆创建表的时候直接指定

CREATETABLEmytable（  IDINTNOTNULL,   usernameVARCHAR（16）NOTNULL,  INDEX[indexName]（username（length））  ）; 删除索引的语法：

DROPINDEX[indexName]ONmytable;

（2）唯一索引

它与前面的普通索引类似，不同的就是：

索引列的值必须唯一，但允许有空值。

如果是组合索引，则列值的组合必须唯一。

它有以下几种创建方式：

◆创建索引

CREATEUNIQUEINDEXindexNameONmytable（username（length））◆修改表结构

ALTERmytableADDUNIQUE[indexName]ON（username（length））◆创建表的时候直接指定

CREATETABLEmytable（  IDINTNOTNULL,   usernameVARCHAR（16）NOTNULL,  UNIQUE[indexName]（username（length））  ）; 

（3）主键索引

它是一种特殊的唯一索引，不允许有空值。

一般是在建表的时候同时创建主键索引：

CREATETABLEmytable（  IDINTNOTNULL,   usernameVARCHAR（16）NOTNULL,  PRIMARYKEY（ID）  ）; 当然也可以用ALTER命令。

记住：

一个表只能有一个主键。

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATETABLEmytable（  IDINTNOTNULL,   usernameVARCHAR（16）NOTNULL,  cityVARCHAR（50）NOTNULL,  ageINTNOTNULL ）; 为了进一步榨取MySQL的效率，就要考虑建立组合索引。

就是将name,city,age建到一个索引里：

ALTERTABLEmytableADDINDEXname_city_age（name（10）,city,age）;建表时，usernname长度为16，这里用10。

这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。

虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age  usernname,city  usernname 为什么没有city，age这样的组合索引呢？

这是因为MySQL组合索引“最左前缀”的结果。

简单的理解就是只从最左面的开始组合。

并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT*FROMmytableWHREEusername="admin"ANDcity="郑州" SELECT*FROMmytableWHREEusername="admin"而下面几个则不会用到：

SELECT*FROMmytableWHREEage=20ANDcity="郑州" SELECT*FROMmytableWHREEcity="郑州"

（5）建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。

例如：

SELECTt.Name FROMmytabletLEFTJOINmytablem   ONt.Name=m.usernameWHEREm.age=20ANDm.city='郑州'此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。

因为在以通配符%和_开头作查询时，MySQL不会使用索引。

例如下句会使用索引：

SELECT*FROMmytableWHEREusernamelike'admin%'而下句就不会使用：

SELECT*FROMmytableWHEREtNamelike'%admin'因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。

因此索引也会有它的缺点：

◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。

一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（7）使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。

所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。

例如，如果有一个CHAR（255）的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。

短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么orderby中的列是不会使用索引的。

因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。

like“%aaa%”不会使用索引而like“aaa%”可以使用索引。

◆不要在列上进行运算

select*fromuserswhereYEAR（adddate）<2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select*fromuserswhereadddate<‘2007-01-01’;

◆不使用NOTIN和<>操作

以上，就对其中MySQL索引类型进行了介绍。

如大家所知道的，Mysql目前主要有以下几种索引类型：

FULLTEXT，HASH，BTREE，RTREE。

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

FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。

其可以在CREATETABLE，ALTERTABLE，CREATEINDEX使用，不过目前只有CHAR、VARCHAR，TEXT列上可以创建全文索引。

值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATEINDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHEREnameLIKE“%word%"这类针对文本的模糊查询效率较低的问题。

在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的，如果没有异步IO处理，进程将被挟持，很浪费时间，当然这里不对异步IO作进一步讲解，想了解的童鞋，自行谷哥。

全文索引的使用方法并不复杂：

创建ALTERTABLEtableADDINDEX`FULLINDEX`USINGFULLTEXT（`cname1`[,cname2…]）;

使用SELECT*FROMtableWHEREMATCH（cname1[,cname2…]）AGAINST（'word'MODE）;

其中，MODE为搜寻方式（INBOOLEANMODE，INNATURALLANGUAGEMODE，INNATURALLANGUAGEMODEWITHQUERYEXPANSION/WITHQUERYEXPANSION）。

关于这三种搜寻方式，愚安在这里也不多做交代，简单地说，就是，布尔模式，允许word里含一些特殊字符用于标记一些具体的要求，如+表示一定要有，-表示一定没有，*表示通用匹配符，是不是想起了正则，类似吧；自然语言模式，就是简单的单词匹配；含表达式的自然语言模式，就是先用自然语言模式处理，对返回的结果，再进行表达式匹配。

对搜索引擎稍微有点了解的同学，肯定知道分词这个概念，FULLTEXT索引也是按照分词原理建立索引的。

西文中，大部分为字母文字，分词可以很方便的按照空格进行分割。

但很明显，中文不能按照这种方式进行分词。

那又怎么办呢？

这个向大家介绍一个Mysql的中文分词插件Mysqlcft，有了它，就可以对中文进行分词，想了解的同学请移步Mysqlcft，当然还有其他的分词插件可以使用。

HASH

MySQL中，只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。

Memory存储引擎支持非唯一hash索引，这在数据库领域是罕见的，如果多个值有相同的hashcode，索引把它们的行指针用链表保存到同一个hash表项中。

假设创建如下一个表：

CREATETABLEtesthash（

fnameVARCHAR（50）NOTNULL,

lnameVARCHAR（50）NOTNULL,

KEYUSINGHASH（fname）

）ENGINE=MEMORY;

包含的数据如下：

假设索引使用hash函数f（），如下：

f（'Arjen'）=2323

f（'Baron'）=7437

f（'Peter'）=8784

f（'Vadim'）=2458

此时，索引的结构大概如下：

Slots是有序的，但是记录不是有序的。

当你执行

mysql>SELECTlnameFROMtesthashWHEREfname='Peter';

MySQL会计算’Peter’的hash值，然后通过它来查询索引的行指针。

因为f（'Peter'）=8784，MySQL会在索引中查找8784，得到指向记录3的指针。

因为索引自己仅仅存储很短的值，所以，索引非常紧凑。

Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。

Hash索引有以下一些限制：

（1）由于索引仅包含hashcode和记录指针，所以，MySQL不能通过使用索引避免读取记录。

但是访问内存中的记录是非常迅速的，不会对性能造成太大的影响。

（2）不能使用hash索引排序。

（3）Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。

（4）Hash索引只支持等值比较，例如使用=，IN（）和<=>。

对于WHEREprice>100并不能加速查询。

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表导入内存）。

这样，当我们进行WHEREage=18时，会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。

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

BTREE（空间索引）

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，相信学过数据结构的童鞋都对当初学习二叉树这种数据结构的经历记忆犹新，反正愚安我当时为了软考可是被这玩意儿好好地折腾了一番，不过那次考试好像没怎么考这个。

如二叉树一样，每次查询都是从树的入口root开始，依次遍历node，获取leaf。

BTREE在MyISAM里的形式和Innodb稍有不同

在Innodb里，有两种形态：

一是primarykey形态，其leafnode里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。

二是secondaryindex，其leafnode和普通的BTREE差不多，只是还存放了指向主键的信息.

而在MyISAM里，主键和其他的并没有太大区别。

不过和Innodb不太一样的地方是在MyISAM里，leafnode里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.

假设有如下一个表：

CREATETABLEPeople（

   last_namevarchar（50）   notnull,

   first_namevarchar（50）   notnull,

   dob       date          notnull,

   gender    enum（'m','f'）notnull,

   key（last_name,first_name,dob）

）;

其索引包含表中每一行的last_name、first_name和dob列。

其结构大致如下：

索引存储的值按索引列中的顺序排列。

可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀（leftmostprefixoftheindex）来进行查询。

（1）匹配全值（Matchthefullvalue）：

对索引中的所有列都指定具体的值。

例如，上图中索引可以帮助你查找出生于1960-01-01的CubaAllen。

（2）匹配最左前缀（Matchaleftmostprefix）：

你可以利用索引查找lastname为Allen的人，仅仅使用索引中的第1列。

（3）匹配列前缀（Matchacolumnprefix）：

例如，你可以利用索引查找lastname以J开始的人，这仅仅使用索引中的第1列。

（4）匹配值的范围查询（Matcharangeofvalues）：

可以利用索引查找lastname在Allen和Barrymore之间的人，仅仅使用索引中第1列。

（5）匹配部分精确而其它部分进行范围匹配（Matchonepartexactlyandmatcharangeonanotherpart）：

可以利用索引查找lastname为Allen，而firstname以字母K开始的人。

（6）仅对索引进行查询（Index-onlyqueries）：

如果查询的列都位于索引中，则不需要读取元组的值。

由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找（找某些值），也可以对查询结果进行ORDERBY。

当然，使用B-tree索引有以下一些限制：

（1）查询必须从索引的最左边的列开始。

关于这点已经提了很多遍了。

例如你不能利用索引查找在某一天出生的人。

（2）不能跳过某一索引列。

例如，你不能利用索引查找lastname为Smith且出生于某一天的人。

（3）存储引擎不能使用索引中范围条件右边的列。

例如，如果你的查询语句为WHERElast_name="Smith"ANDfirst_nameLIKE'J%'ANDdob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

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使用经历的同学，到时可以交流下！

索引的优化：

1、选择索引的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。

通常来说，可以遵循以下一些指导原则：

（1）越小的数据类型通常更好：

越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

（2）简单的数据类型更好：

整型数据比起字符，处理开销更小，因为字符串的比较更复杂。

在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

（3）尽量避免NULL：

应该指定列为NOTNULL，除非你想存储NULL。

在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算