MySql的索引数据结构
适当的索引会大大提高查询速度,但同时会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引。
MySQL主要有两种索引结构:Hash索引和B+树索引。
Hash索引是将索引字段转化为hashcode,仅支持Memory引擎。
B+ 树是一种多路平衡查询树,是多叉树。对比普通二叉树,可以减少磁盘IO,提高检索速度。InnoDB和MyISAM的索引都是采用B+树的结构。
哈希结构,B+树结构对比:
1.哈希索引没办法利用索引完成排序
2.B+树范围查询的时候不需要做全表扫描
3.哈希索引不支持联合索引的最左匹配规则
4.哈希索引查找时,如果是指定值查找,时间复杂度只要O(1)
InnoDB和MyISAM索引结构的区别
- InnoDB使用的是聚集索引,即实际的数据行和相关的键值保存在一起。如下图所示。所以如果是主键索引查找的话,是非常高效的。如果是非主键索引查找,就可能产生回表。即先通过非主键索引找到对应的主键,然后再按照主键索引找到相应的数据记录。但是,如果需要查找的数据本身已经在索引中,就无需回表,这叫覆盖索引(covering index)。
例子:
表covering_index_sample中有一个普通索引idx_key1_key2(key1,key2)。
当我们通过SQL语句:select key2 from covering_index_sample where key1 = ‘keytest’;的时候,就可以通过覆盖索引查询,无需回表。
- MyISAM使用的是非聚集索引,因为MyISAM的索引文件和数据文件是分开的。所以,MyISAM中索引检索的算法为,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
补充知识点
== Index Condition Pushdown(索引下推)==
MySQL 5.6引入了索引下推优化,默认开启,使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下:
people表中(zipcode,lastname,firstname)构成一个索引
SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;
如果没有使用索引下推技术,则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。
如果使用了索引下推技术,则MYSQL首先会返回符合zipcode='95054’的索引,然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。
设计索引时,应该如何考虑
- 索引不在多,而在于合适。
- 在记录较多条的表中,对经常用作where条件的字段设置索引(表的主键,外键会自动创建索引)
- 假如多个条件经常同时出现,可以考虑多个字段建立联合索引,否则,建立多个单独索引。
- 频繁进行写入操作的表,要注意不能建立太多索引。
- 如果有多个字段,需要做联合索引,在做联合索引的时候,要把识别率最高的字段放在最左边,因为MySQL索引查询会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。
- 索引字段,尽量避免NULL,应该指定列为NOT
NULL,除非你就想存储NULL。在MySQL中含有空值的列很难进行查询优化。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值,这是设计表的时候要考虑的问题。