MySQL索引入门

 

目录

索引的类型

B+Tree索引

哈希索引

---

索引是存储引擎用于快速找到记录的一种数据结构。索引优化应该是对查询优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，创建一个最优的索引经常需要重写查询。索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀。创建一个包含两个列的索引，和创建两个包含只包含一列的索引是大不相同的。

索引的类型

B+Tree索引

B+Tree索引是使用B+Tree数据结构来存储数据。存储引擎以不同的方式使用B+Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。B-Tree索引之所以能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。B+Tree对索引列是按照顺序组织存储的，所以适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查找效率会非常。索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。B+Tree索引适应于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。B+Tree索引对如下类型的查询有效。

全值匹配

         全值匹配指的是和索引中所有的列进行匹配

匹配最左前缀

         指的是和索引中第一列进行匹配

匹配列前缀

         指的是只匹配某一列的值的开头部分

匹配范围值

         指的是匹配某一列的范围

精确匹配某一列并范围匹配另外一列

         精确匹配是指某一列的值已知，再范围匹配另一列

只访问索引的查询

         B+Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无需访问数据行。这种又称为“覆盖索引”。

 

因为索引树中的节点是有序的，所以除了按值查找以外，所以还有可以拥有查询中ORDER BY操作。

 

下面是一些关于B+Tree索引的限制：

·如果不是按照索引的最左列开始查找，则无法使用索引。

·不能跳过索引的列。

·如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。

索引的顺序非常重要，这些限制都和索引的顺序有关。在性能优化的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

哈希索引

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

 

在MySQL中，只有memory引擎唯一支持哈希索引。这也是memory引擎表的默认索引类型，memory引擎同时也支持B-Tree索引。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

因为索引本身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

哈希索引的限制：

·哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

·哈希索引数据并不是按照索引值顺序存储的，所以也叫无法用于排序。

·哈希索引也不支持部分索引列匹配查找。

·哈希索引只支持等值比较查询，包括=、IN()、<=>。也不支持任何范围查询。

·访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，进行比较，直到找到符合条件的行。

·如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

 

因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将会非常显著。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

创建自定义哈希索引。如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引，这就可以享受一些哈希索引的便利，例如只需要很小的索引就可以为超长的键创建索引。

思路很简单:在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。

举一个查找URL例子：

select id from url where url=”http://www.mysql.com”;

 若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

Select id from url where url=”http://www.mysql.com”  
and url_crc=CRC32(“http://www.mysql.com”);

MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。即使有多个记录相同的索引值，查找仍然会很快，只需要根据哈希值做快速的整数比较就能找到索引条目，然后一一比较返回对应的行。这样实现的缺陷是需要维护哈希值，维护哈希值有手动维护和触发器实现。

 

几种常见的哈希函数，CRC32()函数、SHA1()和MD5()作为哈希函数。SHA1()和MD5()这两个函数计算出来的结果是非常长的字符串，会浪费大量的空间，比较时也会更慢。SHA1()和MD5()作为强加密函数，设计目标是最大限度消除冲突。而如果使用使用CRC32()函数在数据表非常大的情况会出现大量的哈希冲突，可以考虑自己实现一个简单的64位哈希函数。这个自定义的函数要返回整数，而不是字符串。可以使用MD5()函数返回值的一部分来作为自定义哈希函数，代码如下：

SELECT CONG(RIGHT(MD5(‘http://www.mysql.com/’),16),16,10)  AS HASH64;

最后要注意在WHERE条件中带入哈希值和对应列值。