乐趣区

关于mysql优化:MySQL优化学习手札四-单表访问方法

jiezi

本篇是介绍 MySQL 执行打算的铺垫，明天终于想好了该如何组织这部分内容，先是大抵介绍查问的实现，再由此引出执行打算。

概述

咱们日常的查问，根本能够分为三类:

  1. 单表查问
  2. 子查问
  3. 连贯查问

这三种能够组合，也能够离开，下面的程序也是咱们学习 SQL 的程序，咱们上面介绍其实现，也是依照下面这种程序。看本篇之前倡议先看这个本篇的前几篇:

  • MySQL 优化学习手札(一)
  • MySQL 优化学习手札(二)
  • MySQL 优化学习手札(三)

当然如果你对 MySQL 的 B + 树索引比拟相熟也能够不看。

单表拜访办法

咱们写了一个单表查问的语句，MySQL 是如何获取咱们查问语句所对应的记录的呢：

SELECT * FROM Student WHERE ID = 1;

咱们疏忽语法解析、连贯建设这些步骤，这些都搞定了，那么 MySQL 该如何定位记录呢？MySQL 中执行查问的形式一共有以下两种:

  • 全表扫描(一条记录一条记录的去比拟)

  • 应用索引进行查问，索引也有不同的类型，所以就算是应用索引进行查问，也分为几种不同的状况:

    • 针对主键或惟一二级索引的等值查问
    • 针对一般二级索引的等值查问
    • 针对索引列的范畴查问
    • 间接扫描整个索引

在 MySQL 中执行查问语句的形式称之为拜访办法或者拜访类型。

通过主键列等值匹配来定位记录 -const

SELECT * FROM Student WHERE ID = 1; ID 是主键

Id 是 Student 这张表的主键，这里让咱们在回顾一下 MySQL 存储数据的根本构造:

  • InnoDB 将数据划分为若干页，以页作为磁盘和内存之间交互的根本单位，InnoDB 中页的大小个别为 16KB
  • InnoDB 存储引擎会主动为主键 (如果没有它会主动帮咱们增加) 建设聚簇索引，聚簇索引的叶子结点蕴含残缺的用户记录。
  • 每个索引都对应一颗 B + 树，B+ 树分为好多层，最下边一层是叶子结点，其余的是内结点。所有的用户记录都存储在 B + 树的叶子结点。所有的目录项记录都存储在内结点
  • 咱们也能够为本人感兴趣的列建设二级索引，二级索引的叶子结点蕴含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找残缺的用户记录的话，须要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找残缺的用户记录。

  • B+ 树的每层结点都是依照索引列值的从小达到的程序排序而组成了双向链表，而每个页内的记录 (不论是用户记录还是目录项记录) 都是依照索引列的值从小达到的程序而造成了一个单链表。如果是联结索引的话，则页面和记录先依照联结索引前边的列排序，如果该列的值雷同，再依照联结索引后边的列进行排序。

    也就是 ID 这一列是聚簇索引，同时按 ID 进行排序，所以这个相当快，能够先用定位到目录项地行为这一列位于哪个数据页，定位到之后，再用二分查找定位这条记录在哪个地位。当初让咱们为 Student 再加上一列 name，并为该列建设惟一索引，咱们去执行如下查问:

select  * from student where name ='aa'

也同聚簇索引相似，然而 name 列因为是非聚簇索引列，叶子结点没有残缺的记录，定位到 name=‘aa’这条记录后，还用用这条记录对应的主键去主键索引列去查残缺的记录。即使有回表的代价，MySQL 的开发人员依然认为这种查问形式是十分快的，将这种拜访办法定义为: const, 也就是常数级别。但如果查问 NULL 值，状况就又有所不同:

select * from  student where name is null

因为惟一索引并不限度 NULL 值的数量，所以下面的查问语句可能会拜访到多条记录。

ref

用惟一索引列去查找 NULL 值，会查问到多行，这种状况和用非惟一索引列去匹配记录相似，查问步骤和用惟一索引列去查问是一样的，定位这条记录在哪个数据页，而后到具体的页外面去匹配记录，因为咱们是 select * , 所以还要回表查问。如果匹配的记录比拟少，回表的代价还是比拟低的，MySQL 就更偏向于应用索引 + 回表的形式来查找，采纳二级索引进行等值查问记录的形式，MySQL 将其定义为 ref。

但对于某个蕴含多个索引列的二级索引列来说，只有最右边的间断索引列是与常数的等值比拟就可能采纳 ref 的拜访办法。至于为什么是可能起因在于还是老本的掂量，如果你是 select *，这就要回表。如果查问的记录比拟多，让 MySQL 感觉与其索引列 + 回表还不如间接扫描全表的话。

当初让咱们再为 Student 再加: age, sex，同时为 age、sex 建一个一般的索引。如果咱们查问的语句写成了上面这样:

select * from student where age = '18' and sex >  '女'

这种拜访办法在 MySQL 中的查问级别就不是 ref，起因在于对 sex 这一列应用的是范畴查问。

ref_or_null

依据一般索引进行匹配，但同时查找该索引列为 null 的值，像上面这样:

select * from  student where name = 'aa' and  name is  null

这种查问级别在 MySQL 中咱们称之为 ref_or_null.

range- 范畴查问

咱们日常的开发中范畴查问也是高频呈现，像上面这样:

select * from student where age in ('96','18') OR (age >= 28 and key <= 79);

对于 MySQL 来说执行这个查问有两种抉择，省事速度慢天然是全表扫描，当然也能够应用二级索引 + 回表的形式。下面的条件是一个搜寻范畴，在 MySQL 中将这种查问级别定义为 range。

index

select sex,age from student where sex = '男'

age 和 sex 组成的联结索引中，age 在前，sex 和 age 上都有索引，那对于 MySQL 来说就有两种抉择，一种是用 sex 上的索引 + 回表查出 age。另一种是遍历 sex 和 age 对应的联结索引，这种效率事实上更高，因为非主键索引只存储了主键列和索引列，数据页会更小，也不须要回表，代价更小。这种查问级别在 MySQL 中被定义为 index。

all

如其名，扫描全表。

该如何回表

后面咱们唠叨了回表这个词，事实上行对于不同的查问场景也有不同的回表策略。

  • 状况一, 查问用到了两个索引列，该如何回表:
select  *  from student where age > '50'  and name = '张三'

age 和 name 都是索引列, 该应用哪个索引呢，MySQL 优化器个别会依据 Student 的统计数据来判断到底应用哪个条件对应的二级索引中查问扫描的行数会更少。而后将从二级所以呢中查问的后果通过回表失去残缺的用户记录，再依据另一个条件过滤记录。个别状况来说，等值查找比范畴匹配须要扫描的范畴更少，这里假如查问优化器应用 name 列进行查找。所以对于下面的语句，查问步骤如下:

依据 name =‘张三’去 name 对应的索引下来查找对应二级索引的记录。

而后回表二级索引对应的主键去聚簇索引中找到对应的残缺的用户记录，再依据 age >‘50’进行过滤。

  • 状况三: 有的搜寻条件无奈应用索引

咱们再为 Student 增加一个 card 的字段，而后执行上面的查问:

select * from student where age > 30 and card = '001'

对于这种状况，card 下面没有索引，MySQL 会偏向于应用 age 上的索引而后回表查出记录，再用 card =‘001’进行过滤。

那如果是 or 呢？

select * from student where age > 30 or card = '001'

这种状况就没有方法用到 age 上的索引，因为 age 索引上就只有索引列和主键，MySQL 就可能会进行扫描。

索引合并

MySQL 在个别状况下执行一个查问是最多只会用到单个二级所以呢，有个别就会有非凡状况，在一些非凡状况可能在一个查问中应用到多个二级索引，应用到多个二级索引的这种状况，在 MySQL 中被称为：index merge.

Intersection 合并

上面的探讨中临时移除 age 身上的独自索引列，仅保留 age、sex 的联结索引。

Intersection 意为交加，简略的了解就是一个查问能够应用多个二级索引，将多个二级索引中查问到的后果取交加:

select * from  Student  where age = '18'and sex = '男' and name = '张三'

age 和 name 上都有索引，那么执行下面的语句，就有两种执行计划供 MySQL 所抉择(不要提全表):

  • 抉择一个条件去对应的索引列上去查找记录，而后回表，用另一个条件过滤。
  • age 索引列去查找记录，name 索引列查找记录。因为所以存储的有主键列，这两个后果集求交加。

那哪种拜访形式老本比拟低呢，个别状况下 MySQL 更偏向于抉择读取多个二级索引的形式，因为读取二级索引是程序 I /O, 回表是随机 I /O.

所以如果只读取一个二级索引时须要回表的记录数特地多，而读取多个二级索引之后取交加的记录数非常少，这种状况回表的损耗可能就要比拜访多个索引更高。

上面两个查问就不能应用 Intersection 索引合并:

select * from Student where  age = '18' and sex = '男' and name > 'zhangsan'

咱们剖析一下为什么这种状况为什么就不能应用索引合并求交加，起因在于工夫复杂度的问题，age 是范畴匹配(age 和 sex 建设联结索引,)，扫描到的主键列未必有序，有序汇合求交加的工夫复杂度 O(n)，无序汇合求交加的工夫复杂度为 O(n^2).

上面的查问是一样的起因，不能用到索引合并:

select * from Student where  age = '18'  and name =  'zhangsan'

联结索引 age 雷同，依照 sex 进行排序，然而仅依据 age 这一列失去的记录主键可能还是无序的，所以也无奈用到索引合并。

主键列能够是范畴匹配的查问:

select  * from Student where id > 1 and name = '张三'

下面不是说了范畴匹配无奈用到索引合并吗？然而索引存储了索引列和主键，咱们甚至能够认为这个只用到了 name 这一列，而后再回表。如个只用 name 列再回表的代价大于主键列和 name 列应用索引列合并的代价，那么 MySQL 就可能偏向于应用 id 列和 name 进行索引合并。

Union 合并

有求交加，就有求并集，这是一对双生子。MySQL 在某些特定去年高考下才可能会应用到 Union 索引合并:

  • 状况一: 二级索引列是等值匹配的状况，对于联结索引来说，在联结索引中的每个列都必须等值匹配，不能呈现只匹配局部列的状况。
select * from student where name = 'a' or (age = '18' and sex = '男')

上面两个查问就不能进行 Union 索引

select * from student where name > 'a' or (age = '18' and sex = '男')
select * from student where name = 'a' or age = '18'

起因还是和排序无关，两个汇合进行排序。

  • 状况二: 主键列能够是范畴匹配
  • 状况三：应用 Intersection 索引合并的搜寻条件

能够了解为两个交加的并。

Sort-Union 合并

Union 索引合并的应用条件有点刻薄，必须保障各个二级索引列再进行等值匹配的条件下才可能被用到。

select * from Student  where name > 'a' and age < '25'

下面这个 SQL 语句就无奈用到 Union 排序，起因在于从 name 和 age 这两个列查到的主键值不是排好序的，但如果排序的代价不高呢，MySQL 在代价不高的状况下会如下执行:

  • 先依据条件 name >‘a’取值，而后依据主键进行排序
  • 再依据 age <‘25’取值，而后依据主键进行排序。

拍好序剩下的操作就和 Union 索引合并形式就一样的。

索引合并的注意事项

咱们先为 card 属性增加一个一般索引：

select * from student where name = '张三' and card = '001'

这个查问之所以可能用到索引合并的起因在于，name 和 card 是两个索引，两个列要是一个索引就不必 MySQL 来合并了。这样就不必读 B + 树了。

写在最初

本篇其实也是看本文的参考资料，用本人的思路梳理了一下，做的学习笔记。

参考资料

  • 《MySQL 是怎么运行的：从根儿上了解 MySQL》小孩子煮
退出移动版