Kira's Blog
文章 标签 分类
Kira's Blog

SQL 执行顺序

 收录于  类别 数据库
     约 2187 字   预计阅读 5 分钟 
警告
本文最后更新于 2023-03-09，文中内容可能已过时。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 创建 t1 表，表结构与 t2 一样
CREATE TABLE t1 LIKE t2;

-- 修改表的存储引擎
ALTER TABLE t1 ENGINE = InnoDB;

-- 创建临时表
CREATE TEMPORARY TABLE temp_t LIKE t1;

-- 插入数据
INSERT INTO students (id, name, grade, age)
VALUES (1, 'Alice', 'A', 20),
       (2, 'Bob', 'C', 22),
       (3, 'Charlie', 'B', 19),
       (4, 'David', 'D', 24);

1
2
3
4
5
6
7
8
9

SELECT column_a, column_b
FROM t1
         JOIN t2
              ON t1.column_a = t2.column_a
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC
LIMIT count;
  • SELECT：用于选择想要从表中检索的列，从而形成结果集。
  • FROM：指定查询所涉及的数据表。
  • JOIN：用于连接两个或多个表并根据关联条件返回相匹配的行。
  • ON：指定进行连接的条件（连接的列）。
  • WHERE：用于定义查询条件，限制从表中检索的行数。
  • GROUP BY：用于分组聚合操作，并为结果集中的行分组。
  • HAVING：过滤分组后的记录。
  • ORDER BY：用于排序结果集以指定一定的顺序显示结果。
  • LIMIT：用于限制最终结果集中的行数。

JOIN..ON…是关联查询两个不同表中的字段。STRAIGHT_JOIN 允许您指定在 JOIN 操作时 SELECT 语句中列出的表的连接顺序，而 JOIN 则不允许。

1
2

-- t1 是驱动表，t2 是被驱动表，驱动表是走全表扫描，而被驱动表是走树搜索（需要索引）
SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.a = t2.a;

1
2

-- 由于t2.b没有索引，需要走全表扫描
SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.a = t2.b;

尽量使用可以利用索引的 Join 语句；尽量不要使用 Block Nested-Loop Join 算法；尽量让小表做驱动表。这样做将有助于提升性能，并减少索引开销和查询时间。

1
2

UPDATE table_name SET column1 = value1, column2 = value2 WHERE id=100;
UPDATE tab1, tab2 SET tab1.column1 = value1, tab1.column2 = value2 WHERE tab1.id = tab2.id;

1

DELETE FROM t1 WHERE id = 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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;

sort_buffer_size 是 MySQL 为排序操作开辟的内存空间大小。

  • 如果要排序的数据量小于该参数，MySQL 就会在内存中完成排序；
  • 如果要排序的数据量过大，内存放不下时，MySQL 则会使用磁盘上的临时文件来协助完成排序。
1
2

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

查找过程：city索引 -> 主键索引 -> (city,name,age)放入sort_buffer排序 -> 返回结果

在 MySQL 中，max_length_for_sort_data 是一个参数，表示用于排序的最大字符串长度。它通常用于 ORDER BY 子句，因为它确定 MySQL 内部准备排序所需的最大大小。默认情况下，它的值为 1024，但是您可以将其修改为所需的长度。

1

SET max_length_for_sort_data = 16;

查找过程：city索引 -> 主键索引 -> (name,ID)放入sort_buffer排序 -> 主键索引获取数据(city,name,age) -> 返回结果

查找过程：(city,name)联合索引 -> 主键索引 -> 返回结果

查找过程：(city,name,age) 联合索引 -> 返回结果。使用到了覆盖索引。

这个 SQL 语句是否需要排序？有什么方案可以避免排序？

1
2
3
4
5
6

explain SELECT * FROM t WHERE city IN ('杭州', '苏州') ORDER BY name LIMIT 100;
+----+-------------+-------+-------+---------------+------+---------+--------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref    | rows | Extra                                 |
+----+-------------+-------+-------+---------------+------+---------+--------+------+---------------------------------------+
| 1  | SIMPLE      | t     | range | city          | city | 66      | <null> | 1    | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+--------+------+---------------------------------------+

在这条 SQL 语句中，由于不仅查 杭州和苏州的数据，而且查询的是一个城市内的单个 name 的值，所以查出来的结果不是递增的，需要进行排序。

1
2
3
4
5
6
7
8

-- 避免排序的解决方式是：改成两条SQL
SELECT * FROM t WHERE city=‘杭州’ ORDER BY name LIMIT 100;
SELECT * FROM t WHERE city=‘苏州’ ORDER BY name LIMIT 100;
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+
| 1  | SIMPLE      | t     | ref  | city          | city | 66      | const | 1    | Using index condition; Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

CREATE TABLE `words` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`word` VARCHAR(64) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB;

delimiter ;;
CREATE PROCEDURE idata()
BEGIN
	DECLARE i INT;
	SET i = 0;
	while i < 10000 DO
		INSERT INTO words(word) VALUES(concat(CHAR(97 + (i DIV 1000)), CHAR(97 + (i % 1000 DIV 100)), CHAR(97 + (i % 100 DIV 10)), CHAR(97 + (i % 10))));
		SET i = i + 1;
	END while;
END;;
delimiter ;

CALL idata();

1
2
3
4
5
6

explain SELECT word FROM words ORDER BY rand() LIMIT 3;
+----+-------------+-------+------+---------------+--------+---------+--------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref    | rows | Extra                           |
+----+-------------+-------+------+---------------+--------+---------+--------+------+---------------------------------+
| 1  | SIMPLE      | words | ALL  | <null>        | <null> | <null>  | <null> | 9980 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+--------+---------+--------+------+---------------------------------+

查询过程

  1. 原表
  2. 内存临时表：调用 rand() 函数生成一个大于 0 小于 1 的随机小数，将这个小数和 word 写入临时表。 长度为 6 字节的 rowid 来作为主键（数组的下标）。
  3. sort-buffer
  4. 结果
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SET tmp_table_size = 1024;
SET sort_buffer_size = 32768;
SET max_length_for_sort_data = 16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace = 'enabled=on';

/* 执行语句 */
SELECT word FROM words ORDER BY rand() LIMIT 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

这个 SQL 语句采用的是 MySQL 5.6 引入的新排序算法：优先队列排序算法（堆排序）。与临时文件的归并排序算法不同，优先队列排序算法在不使用临时文件的情况下也能进行排序。

1
2

-- 排序缓冲区大小设置为 1000 行 (name, rowid)，维护的堆过大，所以必须使用归并排序算法来实现排序功能。
SELECT city, name, age FROM t WHERE city = '杭州' ORDER BY name LIMIT 1000;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 如果数据有空洞，不是真正的随机
SELECT max(id), min(id) INTO @M, @N FROM t;
SET @X = floor((@M - @N + 1) * rand() + @N);
SELECT * FROM t WHERE id >= @X LIMIT 1;

--
SELECT count(*) INTO @C FROM t;
SET @Y = floor(@C * rand());
SET @sql = concat('select * from t limit ', @Y, ',1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

--
SELECT count(*) INTO @C FROM t;
SET @Y1 = floor(@C * rand());
SET @Y2 = floor(@C * rand());
SET @Y3 = floor(@C * rand());
SELECT * FROM t LIMIT @Y1, 1；
SELECT * FROM t LIMIT @Y2, 1；
SELECT * FROM t LIMIT @Y3, 1；

使用 order by rand() 语句需要 Using temporary 和 Using filesort可能会带来较大的查询执行代价，因此在设计时最好避免这类语法。此外，也应当将业务逻辑写到应用代码中，而不是直接写在数据库中，这种方法也很常见。

相关内容

MySQL索引

MySQL的Explain

数据库的事务

更新于 2023-03-09
阅读原始文档
 MySQL
返回 | 主页