在我们日常的工作中,索引可谓无处不在,如果不懂索引,就会给人一种盲人摸象的印象。下面主要以mysql为基准来系统的介绍。
目录
1. 为何需要索引,索引是什么?
索引是对数据库表中一列或多列的值进行排序的一种结构。一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系,为了方便查找书中的内容,通过对内容建立索引形成目录。索引是一个文件,它是要占据物理空间的。
2. 有哪些分类?
2.1. B+数索引
- 主键索引:数据列不允许重复,不允许为NULL.一个表只能有一个主键。
- 唯一索引:数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引,如:ALTER TABLE table_name ADD UNIQUE (column);
- 唯一组合索引:顾名思义多列唯一,如:ALTER TABLE table_name ADD UNIQUE (column1,column2);
- 普通索引:基本的索引类型,没有唯一性的限制,允许为NULL值。如:ALTER TABLE table_name ADD INDEX index_name (column);
- 前缀索引:超过20 个长度的字符串,请考虑创建前缀索引,因为较小的索引涉及的磁盘 I/O 较少,更为重要的是,对
于较短的键值,索引高速缓存中的块能容纳更多的键值。前缀索引的长度,一般略大于本列的平均长度,
例如 colname的平均长度为 23,那么前缀取稍大一点(如24)就可以,或者根据选择性和实际访问量。前
缀索引兼顾索引大小和查询速度,但是其缺点是不能用于 ORDER BY 和GROUP BY操作,也不能用于覆盖索引。如:alter table tabname add key(colname(24)); - 组合索引:ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);
注:如果你对B+不了解,在另一章中专门介绍
2.2.hash索引
InnoDB在内部利用哈希索引来实现其自适应哈希索引功能,我们干预不了。哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,而节点逐级查找带来的问题就是多次的IO访问,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。
通过SHOW VARIABLES LIKE '%ap%hash_index';查看是否支持,具体情况看官方支持,MySQL5.6中的体系结构更改使其比早期版本更适合禁用自适应哈希索引功能。
适用场景:对于一些比较长的的字段,比如存储 url 的字段,这时可以使用 crc32做哈希,再在这个 hash值上建索引,性能会大幅提升。如当字段url=http://www.baidu.com时,这时可以取crc32(“http://www.baidu.com”)=110365867(建索引而非url上直接建)。
2.3. 全文索引
全文索引是搜索引擎使用的一种关键技术。如:ALTER TABLE table_name ADD FULLTEXT (column);
2.4 B+树索引和哈希索引的区别
- 如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据。
- 如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索。
- 哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询)。
- 哈希索引也不支持多列联合索引的最左匹配规则
- B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题。
3. 适合及不适合创建的索引?
适合的场景:
- where后面使用频繁的搜索列,但不一定是所有where 后面的列都要建索引;
- 虽然数据库上没有维护主外关系,但如果该字段相当于某张表的外键,一般要在该字段加索引;
- 两表join时,所匹配 on和where 的字段应建立合适的索引;
- group by, order by 后面字段,要考虑建索引,但不一定是全部建索引;
- 索引要建在选择性高,重复记录少的字段上(当索引列有大量数据重复时,SQL优化可能不会去利用索引);
- 索引尽量建在小的字段上,如果碰到大的字段(超过20字符),考虑使用前缀索引。
- 尽量避免NULL:应该指定列为NOT NULL,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。
不适合的场景:
- 唯一性太差的字段不适合建立索引,如标识性字段 is_delete,或者表示状态类型等一类的字段;
- 只出现在select中,不会出现在 where 或者order by 条件中的字段不要建索引;
- 一般不要出现,同一字段既是单字段索引,又出现在复合索引中;
- 同一张表,不要建过多复合索引;
- '%xxx'或者'%xxx%'一类的模糊查询,不要创建普通索引,可以考虑建全文索引,但'xxx%'可以用到索引;
- 不要过度索引,索引会影响 DML(insert、delete、update)的性能,对于 DML 操作频繁的表,一般一张表
- 不要超过6个索引,具体还需视表的大小和业务情况适当调整;
- 尽量避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序。
4. 最左匹配原则
更多参阅另一篇认识mysql组合索引的最左匹配原则
6. 如何分析及优化SQL语句的性能问题
对于低性能的SQL语句的定位,最重要也是最有效的方法就是使用执行计划。
ID:
- id相同执行顺序由上至下。
- id不同,id值越大优先级越高,越先被执行。
- id为null时表示一个结果集,不需要使用它查询,常出现在包含union等查询语句中
select_type:
SIMPLE: 不包含任何子查询或union等查询 |
PRIMARY: 包含子查询最外层查询就显示为 PRIMARY |
SUBQUERY: 在select或 where字句中包含的查询 |
DERIVED: from字句中包含的查询 |
UNION: 出现在union后的查询语句中 |
UNION RESULT: 从UNION中获取结果集 |
table: 查询的数据表
partitions: 执行计划表分区、表创建的时候可以指定通过那个列进行表分区
type:
CREATE TABLE `employee` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`no` VARCHAR(10) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`position` VARCHAR(20) NOT NULL,
`age` VARCHAR(2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
ALL | 这便是所谓的“全表扫描”,非常的耗时而且低效。 |
index | 另外一种形式的全表扫描,只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据,和all相比,他们都是取得了全表的数据,而且index要先读索引而且要回表随机取数据,因此index不可能会比all快(取同一个表数据)但为什么官方的手册将它的效率说的比all好,唯一可能的原因在于,按照索引扫描全表的数据是有序的。
|
range | 有范围的索引扫描,相对于index的全索引扫描,它有范围限制,因此要优于index。关于range比较容易理解,需要记住的是出现了range,则一定是基于索引的。同时除了显而易见的between,and以及'>','<'外,in和or也是索引范围扫描。 |
ref | 查找条件列使用非唯一性索引或者唯一索引的前缀扫描。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。
|
eq_ref | ref_eq 与 ref相比牛的地方是,它知道这种类型的查找结果集只有一个?什么情况下结果集只有一个呢!那便是使用了主键或者唯一性索引进行查找的情况,比如根据学号查找某一学校的一名同学,在没有查找前我们就知道结果一定只有一个,所以当我们首次查找到这个学号,便立即停止了查询。这种连接类型每次都进行着精确查询,无需过多的扫描,因此查找效率更高,当然列的唯一性是需要根据实际情况决定的。 |
ref_or_null | 类似REF,只是搜索条件包括:连接字段的值可以为NULL的情况,比如 where col = 2 or col is null |
fulltext | 使用全文索引 |
index_subquery | 在子查询中使用基于除唯一索引之外的索引进行扫描 |
unique_subquery | 在子查询中基于唯一索引进行扫描( eq_ref) |
possible_keys: 可能使用的索引,注意不一定会使用。查询涉及到的字段上若存在索引,则该索引将被列出来。
key: 实际使用的索引,若没有使用索引,显示为NULL
key_length: 索引长度
ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows: 返回估算的结果集数目,并不是一个准确的值。
extra: 见覆盖索引
7. 常见优化
理论上sql优化,应尽量避免全表扫描、文件排序等,尤其是要警惕以下用法(会导致索引失效):
- where子句中使用!=或<>操作符
- where 进行 null 值判断(where num is null),它的问题已有补充mysql中Null 与空值的区别
- where 子句中使用 or 来连接条件,可以使用union all方案代替
- where 中in 和 not in 也要慎用,能用 between 就不要用 in
- where like 不能前置百分号
- where 子句中使用参数(where num=@num)
- where 子句中对字段进行表达式操作(where num/2=100)
- where子句中对字段进行函数操作(where substring(name,1,3)=’abc’)
- order by 涉及的列无索引
当然了,上面都是属于比较初中级,基于Mysql对查询SQL的自动优化来提高性能的。但mysql还提供了更高级的东西,打破这些,主要有:
我们将两个或多个表连接起来进行查询时,我们并不用关心MySQL先连哪个表,后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估,最后得出的一个连接顺序决定的。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就需要人为地干预连接的顺序,直接使用STRAIGHT_JOIN关键字。通常是用小表驱动大表
限制使用索引的范围
有时我们在数据表里建立了很多索引,当MySQL对索引进行选择时,我们希望MySQL只考虑几个索引,而不是全部的索引,就需要用到USE INDEX对查询语句进行设置。
限制不使用索引的范围
如果我们要考虑的索引很多,而不被使用的索引又很少时,可以使用IGNORE INDEX进行反向选取(选择不被考虑的索引)。
强迫使用某一个索引
有时我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引,因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。
当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT,这个选项一般用于分组或DISTINCT关键字,这个选项通知MySQL,如果有必要,就将查询结果放到临时表中,甚至在临时表中进行排序。
SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1
- STRAIGHT_JOIN:强制连接顺序
- 干预索引使用
- 使用临时表
也可以通过查询缓冲提高查询速度,它已超出我们本章节谈论的范围,暂时不深入研究了。
8. in 和exists 优化专题
mysql中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高,这种说法其实是不准确的。这个是要区分环境的。
- 如果查询的两个表大小相当,那么用in和exists差别不大。
- 如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。
- not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
最后,不总结了,上面的基本上都是用法的,后面会再补充一遍原理《mysql索引底层原理分析》