MySQL调优之索引匹配方式及索引种类

索引匹配方式

下面举例皆在索引 idx（name，age，pos）建立前提下

全值匹配

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

匹配最左前缀

只匹配前面的几列

匹配列前缀

可以匹配某一列的值的开头部分

比如：select * from staffs where name like ‘J%’;
这个语句可以利用到用name建立的索引进行查找。但是如果是 select * from staffs where name like ‘%J%’;就无法用到。

匹配范围值

可以查找某一个范围的数据

比如：explain select * from staffs where name > ‘Mary’;

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

可以查询第一列的全部和第二列的部分

比如：explain select * from staffs where name = ‘July’ and age > 25;

只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

哈希索引

哈希索引是memory存储引擎使用的索引，memory的数据文件存储在内存中，这是它效率很高的原因，但正因如此memory不支持持久化，数据易丢失。

特点：

• 哈希索引基于哈希表的实现，只有精确匹配索引所有列的查询才有效（不只是范围查询）
• 在mysql中，只有memory的存储引擎显式支持哈希索引
• 哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快

限制/缺点：

• 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
• 哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
• 哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
• 哈希索引支持等值比较查询，但不支持任何范围查询
• 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的