Mysql 学习（八）单表查询方法一

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

单表访问方法

前面几节我们了解 innodb 的底层数据结构的设计究其本源我们其实是为了更好的理解如何查询并且如何使得查询语句更加快速的问题这节我们就来好好讲一讲
首先我们先来创建一个表

CREATE TABLE index_value_table (
    id INT NOT NULL AUTO_INCREMENT,
    value1 VARCHAR(100),
    value2 INT,
    value3 VARCHAR(100),
    value_part1 VARCHAR(100),
    value_part2 VARCHAR(100),
    value_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (value1),
    UNIQUE KEY idx_key2 (value2),
    KEY idx_key3 (value3),
    KEY idx_key_part(value_part1, value_part2, value_part3)
) Engine=InnoDB CHARSET=utf8;

讲一下这个表
- id 列创建了聚簇索引
- value1 列创建了idx_key1 二级索引
- value2 列创建了idx_key2 二级索引且唯一
- value3 列创建了idx_key3 二级索引
- value_part1value_part2value_part3 列创建了idx_key_part 二级索引是一个联合索引
现在假设这张表里面插入了100000条数据

访问方法的概念

我们通常把执行查询语句的方式称之为访问方法
我们查询执行的方案主要是有两种分类
- 使用全表扫描进行查询
- 使用索引进行查询使用索引进行查询也会有各种方式具体我们可以分为
  - 针对主键或唯一二级索引的等值查询 Const
  - 针对普通二级索引的等值查询 ref
  - 针对索引列的范围查询 range
  - 直接扫描整个索引 index
下面我具体来说明这几个访问方法

Const针对主键或唯一二级索引的等值查询

我们通常认为通过主键和唯一二级索引列与常数的等值比较来定位一条记录是非常快的所以我们也称这种访问方法为 Const常数级别的。
举个例子看这个语句SELECT * FROM index_value_table WHERE id = 1438;
因为条件筛选是聚簇索引我们知道底层是B+树B+树叶子节点中的记录是按照索引列排序的我可以通过二分查找快速的找到对应的ID列的记录并且找到的时候刚好是叶子节点所以可以快速地把记录拉出来执行过程图
在举一个二级索引的例子看这条语句SELECT * FROM index_value_table WHERE value2 = 3841;
二级索引跟聚簇索引的差别是多一层查询我们需要先二分查找找到对应的值和他的主键再通过主键去聚簇索引的B+树中找到对应的数据所以应该也是很快执行图如下

Ref针对普通二级索引的等值查询

因为聚簇索引和唯一的二级索引的等值查询查询到的记录只会有一条但是普通的二级索引可能会有很多条所以当我们找那些等值索引的主键时我们会回表的消耗会比唯一的二级索引消耗大但是跟全表搜索比性能还是要高很多的所以我们通常把这种搜索条件为二级索引列与常数等值比较采用二级索引来执行查询的访问方法称为ref
举个例子SELECT * FROM index_value_table WHERE value1 = 'abc';
这个查询的查询过程
- 先从idx_key1 的索引树用二分查找定位到value1 中为abc 的记录因为时连续的然后找到这些记录对应的主键值
- 从聚簇索引树中通过这些主键定位到完整的记录
但这种还是会有一些特殊情况
- 二级索引存在NULL的情况不论是普通的二级索引还是唯一二级索引它们的索引列对包含NULL值的数量并不限制所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法而不是const的访问方法。
- 对于某个包含多个索引列的二级索引来说只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法比如下面几个
  - SELECT * FROM index_value_table WHERE key_part1 = 'abc';
  - SELECT * FROM index_value_table WHERE key_part1 = 'abc' AND key_part2 = 'def';
  - SELECT * FROM index_value_table WHERE key_part1 = 'abc' AND key_part2 = 'def' AND key_part3 = 'ghi';
- 不仅想找出某个二级索引列的值等于某个常数的记录还想把该列的值为NULL的记录也找出来sql如下 SELECT * FROM index_value_table WHERE value1 = 'abc' OR value1 IS NULL;这种时候的访问方法也是ref但有点特殊叫 ref_or_null 执行过程如下
  - 先从idx_key1 的索引树用二分查找定位到value1 中为abc 和 value1 是 null 的记录然后找到这些记录对应的主键值
- 从聚簇索引树中通过这些主键定位到完整的记录

Range针对索引列的范围查询

我们查询当然不止是等值查询还会有范围查询当我们是范围查询这种更加复杂的搜索条件的时候怎么办呢
举个例子SELECT * FROM index_value_table WHERE value2 IN (1438, 6328) OR (value2 >= 38 AND value2 <= 79);
这种情况我们查询可以有两种一个是全表查询一个是通过二级索引+回表的方式执行当然是采用第二个啦我们来看看如何查询
- 因为条件复杂所以这个时候不再是简单的等值查询而是通过value2列的值匹配下面三个范围中任何一个算在内
  - 值等于 1438
  - 值等于 6328
  - 值在 38 和 79 之间
- 然后通过遍历索引树找到对应的主键进行回表
这种通过范围查询的访问方法称之为 range

Index直接扫描整个索引

这个访问方法通过例子来说明比较简单看下面的sql SELECT value_part1, value_part2, value_part3 FROM index_value_table WHERE value_part2 = 'abc';
这个查询有一个问题就是搜索条件不符合最左匹配原则所以无法触发ref 和 range 访问那他会不会触发索引查询呢
答案是会主要有两个原因
- 查询字段里面只有三个列这三个列还都是联合索引 idx_key_part 中的字段
- 搜索条件的value_part2 也是这个联合索引的字段
索引我们可以直接通过遍历 idx_key_part 这个索引树来获取数据这肯定比全表查询要快而我们通常把这种遍历二级索引树的执行方法称之为 index