在MySQL中索引是在存储引擎层实现的而不是在服务层实现。因此,没有统一的索引标准:不同存储引擎的索引工作方式不同。
一、索引类型
B-tree索引:
- 基本信息:是最常使用的索引,提到索引时没有特别说明,那多半说的是B-tree索引。底层的存储引擎也可以使用不同的存储结构,InnoDB实际使用的B+tree,B+tree是B-tree的一种变种。
- 比较:存储引擎以不同的方式使用B-tree索引,性能也各有不同,各有优劣。例如:MyISAM使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行。
- 优势:B-tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,而是从索引的根节点开始进行搜索。
- 多值查询:索引可以对多值进行排序,依据是CREATE TABLE语句中定义索引时列的顺序;
- 使用范围: B-tree适用于全键值、键值范围、键前缀查找(最左前缀查找);
- 索引的限制
- 如果不是按照索引的最左列开始查询,则无法使用索引;
- 不能跳过索引中的列;
- 如果查询中某个列的范围查询,则其右边所有列都无法使用索引优化查找;
哈希索引
- 基本信息:哈希索引基于哈希表实现。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。
- 优点:索引自身只需存储对应的哈希值,所有索引的结构十分紧凑,这让哈希索引查找速度非常快。
- 索引的限制:
- 只能进行等值比较查询,而不能进行范围查询;
- 并不是按照索引顺序存储的,因此不能进行排序;
二、索引的优点
- 索引大大减少了服务器需要扫描的数据量;
- 索引可以帮助服务器避免排序和临时表;
- 索引可以将随机I/O变为顺序I/O;
三、索引的优化策略
- 独立的列:索引不能是表达式的一部分,也不能是函数的参数;因为查询中的列不是独立的,则MySQL就不会使用索引;