原创

MySQL索引

索引概览

索引的定义:是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
索引的使用就是用空间换时间。

我们知道,数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear
search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

索引优势

  1. 可以提高数据检索的效率,降低数据库的IO成本。 --检索
  2. 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。 --排序
    • 被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些。
    • 如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多。
    • where索引列时,在存储引擎层处理索引下推 ICP
    • 索引覆盖,索引中包含要查询的所有数据,就不用再回表数据了

索引劣势

  1. 索引会占据磁盘空间
  2. 索引虽然会提高查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件。

索引分类

  1. 单列索引
  2. 组合索引
  3. 全文索引
  4. 空间索引
  5. 位图索引 Oracle

索引的原理

索引的存储结构

索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引。

  • MyISAM和InnoDB存储引擎:只支持B+ TREE索引,也就是说默认使用B+ TREE,不能够更换。
  • MEMORY/HEAP存储引擎:支持HASH和B+ TREE索引。

二叉查找树示例
图中展示了一种可能的索引方式。左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

虽然这是一个货真价实的索引,但是实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树(red-black tree)实现的,原因会在下文介绍。

B树和B+树

数据结构示例网站:
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B树是为了磁盘或其它存储设备而设计的一种多介(下面你会看到,相对于二叉,B树每个内结点有多个分支,即多叉)平衡查找树。
B树示例

B树

B树规则:

  1. 排序方式:所有节点关键字是按递增次序排列,并遵循左小右大原则。
  2. 子节点数:非叶节点的子节点数>1,且<=M,且M>=2,空树除外(注:M阶代表一个树节点最多有多少个查找路径,M=M路,当M=2则是2叉树,M=3则是3叉)。
  3. 关键字数:枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个(注:ceil()是个朝正无穷方向取整的函数如ceil(1.1)结果为2)。
  4. 所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子。

B树查找示例

由于B-Tree的特性,在B-Tree中按key检索数据的算法非常直观:首先从根节点进行二分查找,如果找到则返回对应节点的data,否则对相应区间的指针指向的节点递归进行查找,直到找到节点或找到null指针,前者查找成功,后者查找失败。B-Tree上查找算法的伪代码如下:

BTree_Search(node, key) {
    if(node == null) 
        return null; 

    foreach(node.key) { 
        if(node.key[i] == key) 
            return node.data[i]; 

        if(node.key[i] > key) 
            return BTree_Search(point[i]->node); 
    }
    return BTree_Search(point[i+1]->node); 
}
data = BTree_Search(root, my_key);

由于插入删除新的数据记录会破坏B-Tree的性质,因此在插入删除时,需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质,在数据结构篇有专门的的B树和B+树讲解。

B+树

MySQL中对B+树的定义与B树对比主要有4点不同:

  1. B+树的非叶子节点不保存关键字记录的指针,只进行数据索引,这样使得B+树每个非叶子节点所能保存的关键字大大增加。
  2. B+树叶子节点保存了父节点的所有关键字记录的指针,所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样。
  3. B+树叶子节点的关键字从小到大有序排列,左边结尾数据都会保存右边节点开始数据的指针。叶子节点完全实现了数据的有序性。
  4. 非叶子节点的子节点数(指针数)=关键字数

B+树查找示例

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能,例如图4中如果要查询key为从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。

区别
  1. B+树的层级更少:相较于B树B+每个非叶子节点存储的关键字数更多,树的层级更少所以查询数据更快。
  2. B+树查询速度更稳定:B+所有关键字数据地址都存在叶子节点上,所以每次查找的次数都相同所以查询速度要比B树更稳定。
  3. B+树天然具备排序功能:B+树所有的叶子节点数据构成了一个有序链表,范围查询更方便,数据紧密性很高,缓存的命中率也会比B树高。
  4. B+树全节点遍历更快:B+树遍历整棵树只需要遍历所有的叶子节点即可,而不需要像B树一样需要对每一层进行遍历,这有利于数据库做全表扫描。

页、块、扇区之间的关系和区别

  1. 在MySQL中可以通过Innodb_page_size设置大小,一般设置为16K。
  2. 内存以这个单位与操作系统进行IO数据交换,一般大小为4K
  3. 操作系统以这个逻辑单位去操作磁盘,常见为4K。
  4. 磁盘以扇区这个物理最小磁盘单位去存储数据,常见为512Byte。

  5. 页大小查看: getconf PAGE_SIZE,常见为4K;

  6. 磁盘块大小查看: stat /boot/|grep "IO Block",常见为4K;
  7. 扇区大小查看: fdisk -l,常见为512Byte;

因为B+树中每个关键字会关联一个指向下一级的指针,一个指针是6bit,如果key为bigint的话,为8bit,那么一个索引的话为8+6=14bit,而如果是字符串的话,每个字符占3字节(utf8),那么,内存从树种读取索引时每页读到的内容就要少很多。

通过以上可总结: 主键尽量以数字类型,这样内存读取磁盘中索引每页可尽量的多,提高查询效率。

聚集索引和非聚集索引

非聚集索引

数据和索引不在一起的索引,MyIsam存储引擎使用的就是非聚集索引(索引文件是.myi,数据文件是.myd),InnoDB的次级索引也是非聚集索引。

非聚集索引的主键索引和次要索引的叶子结点都会保存数据文件中查询数据的地址。

非聚集索引主键索引

非聚集索引次要索引

聚集索引

索引和数据保存在一起,InnoDB中的主键索引使用的就是聚集索引(索引与数据都存储在.ibd文件中,次级索引是非聚集)

如果表主键不存在,InnoDB引擎会自动找到表中的Unique字段作为主键索引,如果没有Unique字段会自动生成伪列当主键。

InnoDB主键索引解析

InnoDB次要索引解析

聚集索引的主键索引的叶子结点直接保存数据,次要索引叶子结点保存的是主键索引的值。所以,聚集索引中次要索引除非索引覆盖,否则需要两次搜索索引树才能查询到数据。

索引覆盖是利用组合索引完成在次要索引树的遍历,不回表。

如下图:
索引覆盖介绍
我们当前有个组合索引index_name_age,当执行SQL-SELECT age FROM table WHERE name='Alice',此时在次要索引中根据组合索引中的name即可得到age的范围,所以不需要再回表查询,但要注意组合索引的最左原则。

索引的使用场景

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引 where
  3. 多表关联查询中,关联字段应该创建索引on两边都要创建索引select * from user left join order on user.id = order.userid
  4. 查询中排序的字段,应该创建索引 B+ tree 有顺序
  5. 覆盖索引,好处是不需要回表组合索引
    # user表 组合索引(name,age)
    select * from user; ---- 全表扫描,没有使用索引
    select name,age from user; ---- 索引覆盖,不需要回表。
    
  6. 统计或者分组字段,应该创建索引

不使用索引的场景

  1. 表记录太少 索引是要有存储的开销
  2. 频繁更新 索引要维护
  3. 查询字段使用频率不高

组合索引

由多个字段组成的索引,使用顺序就是创建的顺序。

在一颗索引树上有多个字段。

示例

首先,表T1有字段a,b,c,d,e,其中a是主键,除e为varchar其余为int类型,并创建了一个联合索引idx_t1_bcd(b,c,d),然后b、c、d三列作为联合索引,在B+树上的结构正如下图所示。联合索引的所有索引列都出现在索引数上,并依次比较三列的大小。下面是假设的表数据以及我对其联合索引在B+树上的结构图的改进。
PS:基于InnoDB存储引擎。

组合索引示例1
图1
组合索引示例2
图2

图1中的数据在索引树上的形态如图2。

组合索引查找

当我们的SQL语言可以应用到索引的时候,比如

select * from T1 where b = 12 and c = 14 and d = 3;

也就是T1表中a列为4的这条记录。存储引擎首先从根节点(一般常驻内存)开始查找,第一个索引的第一个索引列为1,12大于1,第二个索引的第一个索引列为56,12小于56,于是从这俩索引的中间读到下一个节点的磁盘文件地址,从磁盘上Load这个节点,通常伴随一次磁盘IO,然后在内存里去查找。当Load叶子节点的第二个节点时又是一次磁盘IO,比较第一个元素,b=12,c=14,d=3完全符合,于是找到该索引下的data元素即ID值,再从主键索引树上找到最终数据。

优势

效率高、省空间(相当于创建了a,ab,abc,abcd四个索引树)、容易形成覆盖索引

最左前缀原则

使用规则:遵循最左前缀原则

  • 前缀索引: like '常量%' -> 使用索引; like '%常量' -> 不使用索引
  • 最左前缀: 从左向右匹配直到遇到范围查询> < between会导致后面的列使用不到索引

之所以会有最左前缀匹配原则和组合索引的索引构建方式及存储结构是有关系的

首先我们创建的idx_t1_bcd(b,c,d)索引,相当于创建了(b)、(b、c)(b、c、d)三个索引,看完下面你就知道为什么相当于创建了三个索引。

我们看,联合索引是首先使用多列索引的第一列构建的索引树,用上面idx_t1_bcd(b,c,d)的例子就是优先使用b列构建,当b列值相等时再以c列排序,若c列的值也相等则以d列排序。我们可以取出索引树的叶子节点看一下。

索引的第一列也就是b列可以说是从左到右单调递增的,但我们看c列和d列并没有这个特性,它们只能在b列值相等的情况下这个小范围内递增,如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。

由于联合索引是上述那样的索引构建方式及存储结构,所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如(c,d)、(c)、(d)是无法应用缓存的,以及跨列也是无法完全用到索引如(b,d),只会用到b列索引。

MySQL对于组合索引的优化
explain select * from t1 where a=1 and b=1 and d=1 and  c>1 ;
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | t1    | range  | idx_a_b_c_d   | idx_a_b_c_d | 15    |const,const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+

此处Mysql5.7已经优化执行计划显示执行时将c>1前移,仍然使用索引,在c字段处使用range类型。

执行计划

explain返回字段有11个

  1. id: SELECT查询的标识符,每个SELECT都会自动分配一个唯一的标识符.
  2. select_type: SELECT查询的类型。
  3. table: 查询的是哪个表。
  4. partitions: 匹配的分区。
  5. type: 类型,ALL类型没有使用索引。
  6. possible_keys: 此次查询中可能选用的索引。
  7. key: 此次查询中确切使用到的索引。
  8. ref: 哪个字段或常数与 key 一起被使用。
  9. rows: 显示此查询一共扫描了多少行,这个是一个估计值。
  10. filtered: 表示此查询条件所过滤的数据的百分比。
  11. extra: 额外的信息。

id

每个单位查询的SELECT语句都会自动分配的一个唯一标识符,表示查询中操作表的顺序,有四种情况:

  • id相同:执行顺序由上到下
  • id不同:如果是子查询,id号会自增,id越大,优先级越高。
  • id相同的不同的同时存在
  • id列为null的就表示这是一个结果集,不需要使用它来进行查询。

select_type(重要)

单位查询的查询类型,比如:普通查询、联合查询(union、union all)、子查询等复杂查询。

simple

表示不需要union操作或者不包含子查询的简单select查询。连接查询也是simple且链接查询会分解成两个id相同的simple

primary

一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary。且只有一个

union

union连接的两个select查询,除了第一个表外(第一个视情况而定,primary、dervied...),第二个以后的表select_type都是union

dependent union

与union一样,出现在union或union all语句中,但是这个查询要受到外部查询的影响
举例:

explain select * from t_a a where exists (select 1 from t_b b1 union slect 2 from t_b b2 where b1.id=a.id)

此处外层查询select_type为primary,内层第一个查询select_type为dependent subquery,内层第二个查询select_type为dependent union

subquery

除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery

dependent subquery

与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
举例:如4.2.4中子查询中的第一个查询select_type就是dependent subquery

derived

from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select

table

显示的单位查询的表名,有如下几种情况:

  • 如果查询使用了别名,那么这里显示的是别名
  • 如果不涉及对数据表的操作,那么这显示为null
  • 如果显示为尖括号括起来的就表示这个是临时表,后边的数字就是执行计划中的id,表示结果来自于这个查询产生。
  • 如果是尖括号括起来的,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。

type(重要)

显示的是单位查询的连接类型或者理解为访问类型,访问性能依次从好到差:

  1. system
  2. const
  3. eq_ref
  4. ref
  5. fulltext
  6. ref_or_null
  7. unique_subquery
  8. index_subquery
  9. range
  10. index_merge
  11. index
  12. ALL

注意事项:

  • 除了all之外,其他的type都可以使用到索引
  • 除了index_merge之外,其他的type只可以用到一个索引
  • 最少要使用到range级别

system

表中只有一行数据或者是空表。

const(重要)

使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描。(=后面是个常量,此处对比eq_ref)

eq_ref(重要) 多表关联

条件:

  1. 多表关联
  2. 等值连接
  3. 等值连接的两个表的列是唯一索引列或者主键列,此类型通常出现在多表的join查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果。并且查询的比较操作通常是等于号,查询效率较高。

ref(重要)

条件:

  1. 等值查询,单表查询时就是等值查询非唯一索引列。
  2. 如果是多表关联,那么等值连接的两个表的列是非唯一索引列针对非唯一性索引,使用等值(=)查询。或者是使用了最左前缀规则索引的查询。
explain select biz_article.id from biz_article join sys_user on sys_user.id = biz_article.user_id;
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  biz_article     index   idx_biz_article_user_id idx_biz_article_user_id 8       2   100.00  Using index
1   SIMPLE  sys_user        eq_ref  PRIMARY PRIMARY 8   dblog.biz_article.user_id   1   100.00  Using index
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+

explain select biz_article.id from biz_article join sys_user on sys_user.id = biz_article.user_id where biz_article.user_id = 1;
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  sys_user        const   PRIMARY PRIMARY 8   const   1   100.00  Using index
1   SIMPLE  biz_article     ref idx_biz_article_user_id idx_biz_article_user_id 8   const   2   100.00  Using index
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+

这里展示了eq_refref,为什么两行使用的type不一样,下面这个sql中增加了where判断,mysql优化器认为将join的两个表执行顺序对换(因为后面的表会使用到const类型的索引,只会有一条记录),所以实际上第一个sql使用的视sys_user.id做的关联,使用的是eq_ref,而下面的sql使用的是biz_article.user_id,这个索引是非唯一索引,所以查询biz_article表时使用的是ref。

fulltext

全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引

ref_or_null

与ref方法类似,只是增加了null值的比较。实际用的不多。

unique_subquery

用于where中的in形式子查询,子查询返回不重复值唯一值。

index_subquery

用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。

explain select a1.* from t_a a1 where a1.full_name in (select full_name from t_a a2 where a2.last_name = '2000');
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  <subquery2>     ALL                     100.00  Using where
1   SIMPLE  a1      ref full_name   full_name   403 <subquery2>.full_name   1   100.00
2   MATERIALIZED    a2      ref full_name,idx_t_a   idx_t_a 203 const   1   100.00
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+--------------------------+

从中可以看到,type已经改成ref,解析逻辑:

  1. 执行last_name索引查询a2,并生成一个物化视图,并指名
  2. 全量扫描该物化视图
  3. 使用full_name索引查询a1(这里用的in查询,不一定使用索引)

range(重要)

索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index_merge

表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所个索引,性能可能大部分时间都不如range。

index(重要)

select结果列中使用到了索引,type会显示为index。这种情况下并不会优化查询,只是不需要再去查看表信息,索引直接返回查询内容。如果是在Extra中Using Index,则是即会使用其他type优化查询,又可以使用索引覆盖索引直接返回结果。

全部索引扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

all(重要)

这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。

possible_keys

此次查询中可能选用的索引,一个或多个

key

查询真正使用到的索引,type为index_merge时,这里可能出现两个以上的索引,其他的type这里只会出现一个。

key_len

用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是组合索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。

另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引也不会计算到key_len里面

ref

  1. 如果是使用的常数等值查询,这里会显示const
  2. 如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段
  3. 如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func

rows

这里是执行计划中估算的扫描行数,不是精确值(InnoDB不是精确的值,MyISAM是精确的值,主要原 因是InnoDB里面使用了MVCC并发机制)

extra(重要)

这个列包含不适合在其他列中显示单十分重要的额外的信息,这个列可以显示的信息非常多,有几十种,常用的有:

using filesort(重要)

  1. 排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中
  2. 说明MySQL会使用一个外部的索引排序,而不是按照索引顺序进行读取。
  3. MySQL中无法利用索引完成的排序操作称为"文件排序"
排序优化与索引使用

为了优化SQL语句的排序性能,最好的情况是避免排序,合理利用索引是一个不错的方法。因为索引本身也是有序的,如果在需要排序的字段上面建立了合适的索引,那么就可以跳过排序的过程,提高SQL的查询速度

可以使用索引优化排序的情况

# 假设t1表存在索引key1(key_part1,key_part2),key2(key2)
SELECT * FROM t1 ORDER BY key_part1, key_part2;
SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1=constant1 AND key_part2>constant2 ORDER BY key_part2;

不能使用索引优化排序的情况

# 排序字段在多个索引中,无法使用索引排序
SELECT * FROM t1 ORDER BY key_part1, key_part2, key2;
# 排序键顺序与索引中列顺序不一致,无法使用索引排序
SELECT * FROM t1 ORDER BY key_part2, key_part1;
# 升降序不一致,无法使用索引排序
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
# key_part1是范围查询,key_part2无法使用索引排序
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part2;
排序实现的算法

当出现using filesort时,不一定就是文件排序,这个还要看mysql_sort_buffer_size参数的设置。Mysql目前有3种排序方式,分别为:

  • 常规排序
  • 优化排序的
  • 优先队列排序

其中用到的排序算法有快速排序,归并排序和堆排序。使用场景:

CREATE TABLE t1(
    id int,
    col1 varchar(64),
    col2 varchar(64),
    col3 varchar(64),
    PRIMARY KEY(id),key(col1,col2)
);
SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;
  • 常规排序

    1. 从表t1中获取满足WHERE条件的记录
    2. 对于每条记录,将记录的主键+排序键(id,col2)取出放入sort buffer
    3. 如果sort buffer可以存放所有满足条件的(id,col2)对,则进行排序;否则sort buffer满后,进行排序并固化到临时文件中。(排序算法采用的是快速排序算法)
    4. 若排序中产生了临时文件(同一条查询sql会放到一个临时文件中),需要利用归并排序算法,保证临时文件中记录是有序的
    5. 循环执行上述过程,直到所有满足条件的记录全部参与排序
    6. 扫描排好序的(id,col2)对,并利用id去捞取SELECT需要返回的列(col1,col2,col3)
    7. 将获取的结果集返回给用户

    从上述流程来看,是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)对,这个buffer的大小由sort_buffer_size参数控制。此外一次排序需要两次IO,一次是捞(id,col2),第二次是捞 (col1,col2,col3),由于返回的结果集是按col2排序,因此id是乱序的,通过乱序的id去捞(col1,col2,col3) 时会产生大量的随机IO。对于第二次MySQL本身一个优化,即在捞之前首先将id排序,并放入缓冲区,这个缓存区大小由参数read_rnd_buffer_size控制,然后有序去捞记录,将随机IO转为顺序IO。对于全表输出排序数据,Mysql是使用这种形式排序。

  • 优化排序

    优化排序和常规排序相比,就是在sort_buffer进行排序时并不是放入(id,col2)数据对,而是直接写入需要返回的数据,这样来就不需要第二次IO,提高速度。但是相对的,这样对sort_buffer空间要求就会加大,如果sort_buffer_size设置不够大,本来不需要写入临时文件的排序也要写入临时文件,导致额外IO。MySQL提供了参数max_length_for_sort_data,只有当排序元组小于max_length_for_sort_data时,才能利用优化排序方式,否则只能用常规排序方式。

  • 优先队列排序

    Mysql针对于sort by xx limit m,n这种情况,采用堆排序的方式实现优先队列排序,和优化排序一样,优先队列排序也是将查询的字段都放到sort_buffer中,但是只需要M+N个元组的sort buffer空间即可,对于M,N很小的场景,基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序,采用大顶堆,最终堆中的元素组成了最小的N个元素,对于降序,采用小顶堆,最终堆中的元素组成了最大的N的元素。

从排序可见,日常写SQL时尽量返回字段不要写 *

using index(重要)

索引覆盖,查询时不需要回表查询,直接通过索引就可以获取查询的数据。

  1. 表示相应的SELECT查询中使用到了覆盖索引(Covering Index),避免访问表的数据行。
  2. 如果同时出现Using Where,说明索引同时用来查询和覆盖。
  3. 如果没有同时出现Using Where,表明索引用来读取数据而非执行查找动作。(优化查询只能使用一个索引)

using index condition(ICP)(重要)

Using index condition会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用WHERE子句中的其他条件去过滤这些数据行。

首先了解
  1. Mysql架构有server层和存储引擎层
  2. ICP是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
原理
  1. index key:也就是查询中使用的索引(包含起始indexFirstKey和终止indexLastKey),也就是说根据索引确定扫描数据的范围
  2. index filter:同一个索引中,由index_key确定好的范围内,过滤掉一些包含索引字段却又没有使用到索引的数据
  3. table filter:由index_keyindex_filter过滤之后的数据,where语句后包含的没有索引的字段的过滤,此处是因为存储引擎层维护的是索引和数据的关系,没有索引的字段逻辑判断由server层处理。
不使用ICP过程

storage层:

只将满足index key条件的索引记录对应的整行记录取出,返回给server层。

server层:

对返回的数据,使用后面的where条件过滤,直至返回最后一行。

MySQL5.6之前,没有ICP,只能通过index key进行匹配过滤,确定索引对应的主键-数据范围,然后将这些数据都返回到Server层,Server层进行table filterusing where)。

不使用索引下推1
这种方式会导致有部分不必要数据没有被过滤掉,而是都要回表再做一次查询,并且返回给Server端。
不使用索引下推2

使用ICP扫描的过程

storage层:

  1. 首先将index key条件满足的索引记录区间确定,然后在索引上使用index filter进行过滤
  2. 将满足的index filter条件的索引记录才去回表取出整行记录返回server层
  3. 不满足index filter条件的索引记录丢弃,不回表、也不会返回server层,减少回表(查询主键索引)的数据量。

server层:

对返回的数据,使用table filter条件做最后的过滤。

使用索引下推1
这样在回表查询之前先经历过一次index filter过滤掉部分不必要数据,可以减少回表查询的数量,最终减少存储引擎返回给Server的数据量。
使用索引下推2

使用前后的成本差别

使用ICP前,存储层多返回了需要被index filter过滤掉的整行记录。

使用ICP后,直接就去掉了不满足index filter条件的记录,省去了他们回表和传递到server层的成本。

ICP的使用条件
  1. 只能用于二级索引(secondary index)。
  2. explain显示的执行计划中type值为range、ref、eq_ref或者ref_or_null。
  3. 查询需要访问表的整行数据,即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
  4. 对于InnnoDB表,ICP仅用于二级索引。(ICP的目的是减少全行读取的次数,从而减少IO操作),对于innodb聚集索引,完整的记录已被读入到innodb缓冲区,在这种情况下,ICP不会减少io。
  5. ICP可以用于MyISAM和InnnoDB存储引擎

using temporary

表示使用了临时表存储中间结果。

MySQL在对查询结果order bygroup by时使用临时表。
临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status变量,used_tmp_tableused_tmp_disk_table才能看出来。

distinct

在select部分使用了distinct关键字

using where(重要)

表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server层进行过滤。

此处简单理解就是从MySQL分层上入手,存储引擎层处理的是数据和索引之间的关系,如果SQL查询中包含未被索引的列,那么在存储引擎层无法做到完全过滤(因为该未被索引的列的过滤还没做),那么剩下的工作就交给Server层继续过滤(table filter),这样就会使用到using where

索引失效

优化总结口诀

全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
like百分写最右,覆盖索引不写*;
不等空值还有or,索引失效要少用;

全值匹配我最爱

条件与索引一一对应

最佳左前缀法则

用于组合索引,带头索引不能死,中间索引不能断
如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且不跳过索引中的列。

不要在索引列上做计算

不要进行这些操作:计算、函数、自动/手动类型转换,不然会导致索引失效而转向全表扫描。

范围条件右边的列失效

不能继续使用索引中范围条件(bettween、<、>、in等)右边的列。如下例:sex索引没有使用,只是因为索引下推做了index_filter

explain select * from tuser where name='asd' and age>20 and sex='1'; +----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | tuser | range | idx_name_age_sex | idx_name_age_sex | 308 | NULL | 1 | Using index condition | +----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+

尽量使用覆盖索引

尽量使用覆盖索引(只查询索引的列),也就是索引列和查询列一致,减少select *。

索引字段上不要使用不等、in或者or

索引字段上使用(!=、<>、in、or)时,会导致索引失效而转向全表扫描。

注:主键索引会使用范围索引,辅助索引会失效。

主键索引字段上不可以判断null

MySQL 5.7实测,索引字段上使用is null,判断时,是正常使用索引,索引类型为ref(正常),而使用is not null时,则会使用类型为range的索引,理解为范围查询。

索引字段使用like不以通配符开头

索引字段使用like以通配符开头('%字符串')时,会导致索引失效而转向全表扫描。

like以通配符结束('字符串%')相当于范围查找,索引不会失效。

与范围条件(bettween、<、>、in 等)不同的是:不会导致右边的索引失效。

问题:解决like '%字符串%'时,索引失效问题的方法?

使用覆盖索引可以解决。

索引字段字符串要加单引号

索引字段是字符串,但查询时不加单引号,会导致索引失效而转向全表扫描。

正文到此结束