索引分析

1. 类型及相关基本概念

primary key(一般用自增id作为主键), unique(唯一索引,用的少,不建议靠数据库做唯一性校验), index(普通索引、常用,包括单个索引和符合索引), fulltext全文索引

复合索引: 如果我们创建了(area, age, salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

索引数据结构:B树,它的索引树存储是物理连续的,对磁盘读取友好。

索引字段的索引,是一个有序排列,通过O(log n)复杂度找到索引的值,然后再顺序读取出磁盘后续索引值。

建立索引思路

建立索引的字段的值越分散,索引效果越好。对于sql的查询条件,不可能把所有where字段都加上索引,索引太多会影响插入性能,所以索引字段也需要慎重选择。

例如:查询子句where a=’A’ and gender=’男’,如果字段a的数据很分散,那么a就合适做索引字段。而gender字段只有男女和null值,所以gender不合适做索引字段。

索引不会包括NULL值的索引,复合索引只要一行中索引值有一个是NULL,也失效。

mysql一个查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。但是应该可以复合索引覆盖排序列。

Like只支持前缀索引,不要在列上使用函数进行计算,否则索引失效。

不使用NOT IN和<>操作 NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id<>3则可使用id>3 or id<3来代替。同时需要注意的是,如果sql中有in操作,而in中的数据比较多且分散时,磁盘需要对每个值进行寻址,每个值花费的时间可能只有10ms不多,但是大量的值将导致整个in操作变慢。

查询条件是OR的,OR后面的条件并没有走索引,所以有时候就得拆成union来做。

2. 索引分析

使用explain select语句或 desc select语句可以,执行后拿到查询分析结果:

id

执行顺序,需要越大越早执行。有嵌套查询时id才不同,join时id都是1

type

值从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题

key

实际选中的索引

rows

可能扫描的行数,这个数字大也不一定代表有问题,小也不一定有问题例如select * t_test where id>100 limit 100 其中id是自增主键,查询速度很快,但是rows很高,此时type是range

extra

using index(索引覆盖):这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。

using where(回表):是使用上了where限制,表示MySQL服务器在存储引擎受到记录后进行“后过滤”(Post-filter),如果查询未能使用索引,Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。impossible where 表示用不着where,一般就是没查出来啥。

Using filesort:(MySQL中无法利用索引完成的排序操作称为“文件排序”)当我们试图对一个没有索引的字段进行排序时,就是filesoft。它跟文件没有任何关系,实际上是内部的一个快速排序。

Using temporary:(表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询),使用filesort和temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

索引类型的详细值

索引类型 说明
ALL 扫描全表
index 扫描全部索引树
range 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
ref 非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
const, system 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。
NULL MySQL在优化过程中分解语句,执行时甚至不用访问表或索引

const, system额外说明:如将主键置于where列表中,MySQL就能将该查询转换为一个常量。system是const类型的特例,当查询的表只有一行的情况下,使用systemconst表示索引直接命中,只需要一次查询(唯一index)

显示一个表的所有索引show index from <表名>,结果分析:

字段 说明
Non_unique 如果是唯一索引,则为0;否则是1
Key_name 索引名称
Seq_in_index 索引中的顺序,注意复合索引是展示为多行
Column_name 索引的字段
Collaction 索引方式,A表示升序,NULL表示无分类
Cardinality 重要,正比于索引字段的不同值的个数这个值越大,索引效果越好,该索引被选中的可能性越高
Sub_part 如果整列索引则为NULL,像varchar太长只索引255个字符,那么就是255
Null 如果列含有NULL,则含有YES。如果没有,则该列含有NO。
Index_type BTREE, FULLTEXT, HASH, RTREE

3. 数据库查询过程描述(关于回表、索引覆盖)

数据库的查询过程其实很自然:

  1. 首先mysql拿到查询语句后会解析出where、order、group等相关字段,然后根据这些字段的索引、查询条件(是=还是>=还是like)(但是这个确定过程和值无关,因为值都是不同的,所以mysql未必是选上最合适的索引,所以可以用force index来强制指定索引)和索引的Cardinality(分散程度)选中索引。

  2. mysql一个子查询只会选中一个索引(这个之前有这个印象,实际上不一定,有index_merge类型,例如A or B时,mysql会拿出满足A的索引加上满足B的索引来union),然后mysql会根据sql拿出匹配上的行id,然后如果需要回表过滤,则需要回去读表再过滤;如果不需要回表过滤,则拿到的行id就是要的了,最后limit取足够的行数。如果碰上order by,order在索引里就用得上,如果没有就只能全部行拿出来再排序,再取排序后limit到的行。

复合索引时,where要先于order,先排好序再来看where并没有什么意义。

一些场景的过程描述:

有where,没有order

有where,有order

  • 如果where和order都索引到,这不用回表。得用复合索引,where条件在的索引在前,order索引按顺序在后一般有order的同时order的数据量很大时,都要求这样,不然会很慢。如果where找到的数据量不大时,mysql不会用order的索引
  • 如果where索引上而order没有索引上,那么就要找出所有where满足的行,再来排序,再limit,数据量大时排序会很慢很慢
  • 如果where没有索引上而order索引上,那就先用上order索引,按order的顺序回表找到满足where的行数,知道满足limit,当where所匹配的条件迟迟找不到,或者limit的offset很大时,就会很慢

关于join

假设表A join 表B,查询条件是A表为主,A表where条件有索引,A表joinB表条件有索引:mysql会根据where条件找到A表的index,如果A表索引已经覆盖了where的条件,那么就不用回表A。找到数据之后,根据join条件找到B表,如果where中有B表条件,则B表要回表,直到知道满足了limit行数的数据。// 这里可以复杂很多,有时间再深入

关于limit

mysql有没有索引都要全部找到limit offset + size 行数才行,所以offset很大时是没有办法怎么优化的,当offset在百万级别时就很慢了一种变通的limit方式使用where先知道索引位置,再从这个位置开始limit,遍历的数据就少了

关于where条件是否满足索引的过程描述

  1. where没有索引上

    顺序全表扫描,不断匹配where条件,直到找到limit offset要求的条数

  2. where部分条件索引上

    先根据索引找到这个条件满足的索引位置,然后开始遍历索引,一条一条记录回表匹配where的其它条件,直到找到limit offset要求的条数

  3. where全部条件索引上

    根据索引找到满足条件的索引位置,再遍历索引找到limit offset要求的条数,不用回表

  4. where A or B两条件

    如果A和B都有索引,那么会用index_merge,利用上两个索引找到主键,再merge,再看要不要回表,顺序拿到满足limit offset的数量

  5. where A and B 两条件

    如果A和B都有索引,一般会找到索引效果好的那个来,只用上一个就行(用上另外一个意义不大)

评估查询时间:索引是B树索引,命中上索引的一般是顺序读取,所以io时间较短。回表时一般是跳跃的获取若干个数据,所以每个回表都是N行次跳跃查询。join表也是跳跃查询。一次跳跃查询的时间和索引io的时间等同级别。索引能减少回表是更佳的。

文档更新时间: 2018-11-10 17:03   作者:nick