常见SQL语句关键字的执行

不是二向箔
214 阅读1分钟

单表访问

查询语句本质上只是一种声明式的语法，只是告诉MySQL要获取的数据符合哪些规则，至于MySQL底层里是怎么把查询结果查询出来的那是MySQL执行器实现

实现方式

全表扫描查询

索引查询

  • 针对主键或唯一二级索引的等值查询
  • 针对普通二级索引的等值查询
  • 针对索引列的范围查询
  • 直接扫描整个索引

MySQL中的单表访问类型

const（常数）

聚簇索引或唯一二级索引的等值查询

联合索引需要每一列都等值

ref

二级索引不唯一时，可能有多个查询结果，按多个结果都去回表聚簇索引查询

ref_or_null

在ref多个结果的基础上还查询了索引值为null的结果

range

范围查询，使用二级索引 + 回表的方式执行

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件：

  • 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。
  • 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = 'abc'这个条件是否成立，把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引

JOIN连接

In a word，尽量在业务层进行关联处理，而非取而代之 join

如果可以使用被驱动表的索引，join 还是有其优势的

此外，单表查询便于向后兼容，比如分库分表，如果 join，一旦分库，原来的 sql 都需要改动

使用

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。所以我们把t1和t2两个表连接起来的过程如下图所示：

image_1cql4ae7flug1itskat1ojgi7g3m.png-67.4kB

这个过程看起来就是把t1表的记录和t2的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。因为表t1中有3条记录，表t2中也有3条记录，所以这两个表连接之后的笛卡尔积就有3×3=9行记录。

在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

  • 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。

  • 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

    在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：

    • 左外连接

      选取左侧的表为驱动表。

    • 右外连接

      选取右侧的表为驱动表。

原理

内连接步骤

  • 步骤1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
  • 步骤2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

嵌套循环连接（Nested-Loop Join）

这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join），这是最简单，也是最笨拙的一种连接查询算法。

使用索引加快连接速度

被驱动表会被多次单表访问，如果能利用索引可以有效加快查询速度。

COUNT

出于 MVCC 的原因，InnoDB 的总行数在并发查询下是无法确定的，因此，InnoDB 执行 count() 的语义为

  1. 把数据一行一行地从引擎里面读出来

  2. 逐行判断

    1. 可见性
    2. 参数是否为 NULL，否则不计数

  3. 累积计数

由于遍历哪个索引树得到的结果逻辑上都是一样的，因此，MySQL 优化器会找到最小的那棵树来遍历，以尽量减少扫描的数据量

# 1.并不会把全部字段取出来，而是不取值，肯定非 null，按行累加
SELECT COUNT(*) from t;
# 2.1 InnoDB 引擎遍历整张表，但不取值
# 2.2 server 对于返回的每一行， 放一个数字1进去，判断是不可能为空的，按行累加
SELECT COUNT(1) from t;
# 3.1 InnoDB 引擎会遍历整张表取出每一行的 id，返回给 server
# 3.2 server 层拿到 id 后，判断是不可能为空的，就按行累加
SELECT COUNT(primary_key) from t;
# 4.1 如果这个字段定义为 not null，逐行地从记录里面读出该字段，判断不 能为 null，按行累加
# 4.2 否则，执行的时候，判断到有可能是 null，还要把值取出来再判断一下，非 null 才累加
SELECT COUNT(mul_key) from t;

count(1) 执行得要比 count(primary_key) 快，因为从引擎返回 id 会涉及到解析数据行 & 拷贝字段值的操作

因此，上方查询方式的效率自上而下递减，当然第二种效率接近 *

ORDERBY

MySQL 会给每个启用 orderby 的线程分配 sort_buffer，通过 sort_buffer_size 配置大小，后者存储需要排序的索引，即构建临时表

如果数据量溢出，则需要 swapping，即利用磁盘临时文件辅助排序

显然，能够保证从 city 这个索引上取出来的行，天然就是按照 name 递增排序的话，就无需再排序，如下

CREATE TABLE `t` (
  `id` int(11) NOT NULL,`city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,`age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,PRIMARY KEY (`id`),
  KEY `city` (`city`) ) ENGINE=InnoDB;
  
select city,name,age from t where city='杭州' order by name limit 1000 ; # 需要建立联合索引 (city, name)

MySQL 会因地制宜，采用两种排序算法

  • 全字段排序
  • rowid 排序 - 会要求回表多造成磁盘读，因此不会被优先选择

LIMIT

在Server层接收到数据后，在发送给客户端之前才会检验是否符合LIMIT的要求，server层维护了一个称作limit_count的变量用于统计已经跳过了多少条记录，此时就应该将limit_count设置为1。

深度分页

如下语句执行过程为

  1. 通过普通二级索引树 a，过滤 a 条件，找到满足条件的主键
  2. 回表
  3. 扫描满足条件的 100010 行，然后扔掉前 100000 行，返回
select * from t where a>1 limit 100000,10;

典型的分页场景，其余存储，如 ES 同样会出现该场景，对于 MySQL，解决思路如下

# 1.主键连续，假设上一次查询最大主键为 preMaxId
select * from t where a>1 and id>preMaxId limit 10;
# 2.主键非连续，利用覆盖索引，不建议采用 join or 子查询
select id from t where a>1 limit 100000,10;
select * from t where id in (...);

此外，当 LIMIT OFFSET 过深时，会使 ORDER BY 普通索引失效

avatar
文章
阅读
粉丝