mysql索引的概述_MySQL索引(1)——概述、分类

索引是在存储引擎层实现的而不是在服务器层。

MyISAM存储引擎使用前缀压缩技术存储表记录，InnoDB存储引擎用原数据的格式进行存储。

MyISAM存储引擎通过数据的物理位置引用被索引的行，InnoDB存储引擎通过主键引用被索引的行。

B-Tree索引和hash索引：

B-Tree上所有的值都按照顺序存储，并且每个叶子节点到跟的距离相同。

Hash索引用hash表来实现，只有精确匹配索引的所有列的查询才有效。

在MySQL中只有Memory存储引擎支持hash索引。

可使用B-Tree索引的查询：

.全值查询:对索引中的所有列进行匹配。

.匹配最左前缀

.匹配列前缀：只匹配某一列的开头部分。

.匹配范围查询

.精确匹配最左前缀，并范围匹配下一列

.只访问索引的查询：在查询中只需要访问索引，而不需要访问表数据行

B-Tree索引的限制：

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

.不能跳过索引中的列

.如果查询中有某个列的范围查询，其要右边的所有列都无法使用索引优化查询(所以如果说范围查询列值是有限的，可以使用多个等于条件来代替范围查询)

hash索引的查询计划

alter table testhash add index using hash(fname);

select *from testhash where fname ='Peter';

首先，MySQL先计算'Peter'的hash值，并使用该值查找对应的记录指针。假设f('Peter')=8784，在hash表中找到槽为8784时对应的记录指针，最后比较这一指针所指向的数据行中的fname字段值是否为'Peter'。

hash索引的限制

.hash索引只包含hash值和行指针，而不存储字段值，所以不能使用索引中的值来回避读取行。也就是说hash索引一定要读取行数据。

.hash索引不是按照索引值顺序存储的，所以不能用于排序。

.hash值不支持部分索引的匹配查找，因为hash值是按照索引中所有列的全部内容来计算hash值的。

.hash索引只支持等值比较查询。

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

.如果hash冲突较多的话，索引维护的代价也会很高。

我们直到MySQL中只有Memory存储引擎支持hash索引，但是我们可以在InnoDB存储引擎下，创建自定义的hash索引。也就是模仿InnoDB存储引擎的自适应hash索引。

思路：

在B-Tree基础上创建一个伪hash索引，还是在使用B-Tree进行查找，只是，使用hash值而不是键本身进行索引查找，只需要在查询的where子句中手动指定使用hash函数。适用与对键是很长的字符串的情景下，使用hash索引可以将比较慢的字符串索引优化为快速额整数索引。

例如：

CREATE TABLE `pseudohash` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `url` varchar(255) NOT NULL, `url_crc` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `url_crc` (`url_crc`) USING HASH ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 delimiter // create trigger pseudohash_crc_ins before insert on pseudohash for each row begin set new.url_crc=crc32(new.url);

end// create trigger pseudohash_crc_upd before update on pseudohash for each row begin set new.url_crc=crc32(new.url);

end// delimiter ;

insert into pseudohash(url) select 'http://www.mysql.com';

结果：

但是上面的方法会存在hashchongtu,比如crc32(‘gnu’)和crc32(‘codding’)结果相同。所以在查询时最后是加上对url的限定条件，比如：

索引的优点:

索引大大减少来服务器查询数据需要扫描的数据量。

索引可以帮助服务器避免排序和临时表。

索引可以将随机IO转变成顺序IO。