索引,是存储引擎用于快速找到记录的一种数据结构。尤其是在表中的数据量越来越大时,索引对于性能的提升非常关键。在MySQL中,存储引擎在使用索引时,会先在索引中找到对应值,然后根据所匹配的索引记录找到对应的数据行。
大多数的MySQL引擎都支持B-Tree索引,它底层使用的是B+Tree这种数据结构来存储数据的。B-Tree索引是一个平衡查找树,叶子到根部的节点距离相等。所有的记录都是按照键值的大小排列,叶子结点由指针连接。
B-Tree索引的特点
1、B-tree索引可以加快数据的查询速度
存储引擎不需要进行全表扫描来获得需要的数据,取而代之的是从索引的根节点开始进行搜索。然后根据指针逐层向下查找,通过比较节点页的值和有目标值就可以找到合适的指针进入下层节点,而这些指针实际上定义了子节点页中值的上限和下限。
2、B-tree索引更适合进行范围查询
因为B-tree对索引是顺序组织存储的,所以就很适合进行查找范围数据。
B-tree索引的使用场景
1、全值匹配的查询
指的是和索引中的所有列进行匹配,比如查询字段 name = ‘tom’;
2、匹配最左前缀的查询
比如为a列和b列设置联合索引,只要联合索引的第一列(a列)符合查询条件,索引就会被用到,若只是第二列(b列)符合条件则不会被用到该索引。
3、匹配列前缀的查询
只匹配某一列的值的开头部分
4、匹配范围值
5、精准匹配某一列并范围匹配另外一列
6、只访问索引的查询
在这里指的就是覆盖索引,即只需要访问索引,而无需访问数据行
7、用于查询中的order by 操作
索引树中的节点是有序的。一般来说,若B-Tree可以按照某种方式查找到该值,那么也可以用这种方式用于排序。所以,如果 order by 子句中满足前面列出的几种查询类型,则这个索引也可以满足对应的排序需求。
B-Tree索引的限制
1、若不是按照索引的最左列开始查找,则无法使用该索引
比如建立联合索引(name 、phone_num),若搜索phone_num则无法使用该索引
2、使用索引时,不能跳过索引中的列
比如建立联合索引(name 、phone_num 、addr),若搜索name和addr 则无法使用该索引只能使用那么过滤
3、not in 和 <> 操作无法使用该索引
4、若查询中有某个列的范围查询,则其右边的所有列都无法使用索引
注意:
存储引擎用不同的方式使用B-Tree索引,性能也各有不同,各有优劣。例如,MyISAM使用前缀压缩的技术使得索引更小,但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据逐渐引用被索引的行
HASH索引
在MySQL的存储引擎中,MyISAM不支持哈希索引,而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的,后面会对其做具体说明。hash索引的特点
1、hash索引是基于hash表实现的,只有查询条件精确匹配hash索引中的所有列的时候,才能用到hash索引。
2、对于hash索引中的所有列,存储引擎都会为每一行计算一个hash码,hash索引中存储的就是hash码。
3、hash索引包括键值、hash码和指针 。
因为hash索引本身只需要存储对应的hash值,所以索引的结构十分紧凑,这也让hash索引查找的速度非常快。然而,hash索引也是存在其限制的
hash索引的限制
1、Hash索引必须进行二次查找
使用哈市索引两次查找,第一次找到相应的行,第二次读取数据,但是被频繁访问到的行一般会缓存在内存中,这点对数据库性能的影响不大。
2、hash索引不能用于外排序
hash索引存储的是hash码而不是键值,所以无法用于外排序
3、hash索引不支持部分索引查找也不支持范围查找
只能用到等值查询,不能范围和模糊查询
4、hash索引中的hash码的计算可能存在hash冲突
当出现hash冲突的时候,存储引擎必须遍历整个链表中的所有行指针,逐行比较,直到找到所有的符合条件的行,若hash冲突很多的话,一些索引的维护代价机会很高,所以说hash索引不适用于选择性很差的列上(重复值很多)。姓名、性别、身份证(合适)