MySQL 索引类型

ljhj

B-Tree
B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

• 全值匹配
  
• 匹配最左前缀
  
• 匹配列前缀
  
• 匹配范围值
  
• 精确匹配某一列并范围匹配另外一列
  
• 只访问索引的查询
  
  

因为索引树种的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。
下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
  
• 不能跳过索引中的列。（也就是说，不能越过第一个索引列而使用之后的索引列）
  
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。
  
  

所以索引和列的顺序是有关系的。
哈希索引
哈希索引是基于hash表实现的。因为hash索引自身只需要存储对应的哈希值，所以索引的结构非常紧凑，这样是hash索引速度非常快的原因。然而哈希索引也有它的限制：

• 哈希索引无法用于排序
  
• 哈希索引不支持部分匹配查询
  
• 哈希索引只支持等值查询
  
• 哈希索引速度非常快，除非有很多冲突哈希索引
  
• 如果冲突大的话，一些索引的维护代价也很高
  
  

创建自定义哈希索引
思路很简单：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查询，但是它使用哈希值而不是键本身进行索引查找。你需要做的是在where子句中手动指定使用哈希函数。下面是一个实例，需要存储大量的URL，并需要根据URL进行搜索查询。
正常查询应该是这样：select id from url where url="http://www.iyunv.com/"
若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用这种方式查询：select id from url where url='http://www.iyunv.com/' and url_crc=CRC32('http://www.iyunv.com/')
空间数据索引（R-Tree）
MyISAM表支持空间索引，可以用作地理数据存储。这类索引无须前缀查询。空间索引会从所有维度来索引数据。
全文索引
它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。
索引是最好的解决方案吗？
索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表来说，全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。例如可以使用分区技术。