MySQL short circuit evaluate


今天分析一个特殊的案例, 非常有意思

之前也碰到过类似的问题,但是没有专有名词,今天看书的过程中看到了

所以在这进行,案例分享

如题所示,这叫 short circuit evaluate 

简单来说  如果where 条件中有两个条件 

这时候 如果where 条件更近的条件过滤性更好那速度就会更快

但前提条件是全表扫描而不能是索引扫描 

root@mysql3306.sock>[employees]>show create table salaries3 \G
*************************** 1. row ***************************
       Table: salaries3
Create Table: CREATE TABLE `salaries3` (
  `emp_no` int NOT NULL,
  `salary` int NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`from_date`,`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

我们看下如下SQL 

root@mysql3306.sock>[employees]>desc select * from salaries3
    -> where salary>1000
    -> and to_date>'9999-12-31' ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | salaries3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2838426 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这个SQL 中有两个条件,

1 salary>1000

2 to_date>’9999-12-31′



root@mysql3306.sock>[employees]>select count(1)  from salaries3
    -> where 1=1
    -> and  salary>1000;
+----------+
| count(1) |
+----------+
|  2844047 |
+----------+
1 row in set (0.47 sec)

root@mysql3306.sock>[employees]>select *  from salaries3
    -> where 1=1
    -> and to_date>'9999-12-31';
Empty set (0.61 sec)


如上所示 ,第一个条件返回大量的数据

第二个条件返回0 

那今天要讨论的问题是,这两个条件中如果换下顺序是否对SQL有影响

第一种情况

把过滤条件不好的放在where 条件第一行 我们运行了3次


root@mysql3306.sock>[employees]>desc select count(1) from salaries3
    -> where salary>1000
    -> and to_date>'9999-12-31' ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | salaries3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2838426 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@mysql3306.sock>[employees]>select *  from salaries3
    -> where salary>1000
    -> and to_date>'9999-12-31';
Empty set (0.70 sec)

root@mysql3306.sock>[employees]>select *  from salaries3
    -> where salary>1000
    -> and to_date>'9999-12-31';
Empty set (0.72 sec)

root@mysql3306.sock>[employees]>select *  from salaries3
    -> where salary>1000
    -> and to_date>'9999-12-31';
Empty set (0.67 sec)

第二种,我们把过滤性好的条件放在第一行 我们也运行三次

root@mysql3306.sock>[employees]>desc select *  from salaries3
    -> where 1=1
    -> and to_date>'9999-12-31'
    -> and  salary>1000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | salaries3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2838426 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@mysql3306.sock>[employees]>select *  from salaries3
    -> where 1=1
    -> and to_date>'9999-12-31'
    -> and  salary>1000;
Empty set (0.59 sec)

root@mysql3306.sock>[employees]>select *  from salaries3
    -> where 1=1
    -> and to_date>'9999-12-31'
    -> and  salary>1000;
Empty set (0.64 sec)

root@mysql3306.sock>[employees]>select *  from salaries3
    -> where 1=1
    -> and to_date>'9999-12-31'
    -> and  salary>1000;
Empty set (0.59 sec)

结果所示把过滤性好的放在where 条件第一行,效果更好些,但是因为只相差0.1~0.2s 所以还不能说明

那我们现在进行一次,极端的实验 我们用exists 用来实验,

因为我们用exists 依赖性特性,如果是这样我们通过这个调用次数来大幅度扩大这个理论是否成立 

root@mysql3306.sock>[employees]>desc select straight_join *  from salaries3 a
    -> where 1=1
    -> and  salary>1000
    -> and a.to_date>'9999-12-31'
    -> and exists (
    -> select 1 from salaries s
    -> where a.emp_no=s.emp_no
    -> and s.to_date>'9999-12-31'
    -> );
+----+--------------------+-------+------------+------+-----------------------+---------+---------+--------------------+---------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys         | key     | key_len | ref                | rows    | filtered | Extra       |
+----+--------------------+-------+------------+------+-----------------------+---------+---------+--------------------+---------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL                  | NULL    | NULL    | NULL               | 2838426 |    11.11 | Using where |
|  2 | DEPENDENT SUBQUERY | s     | NULL       | ref  | PRIMARY,emp_no,idx_t1 | PRIMARY | 4       | employees.a.emp_no |       9 |    33.33 | Using where |
+----+--------------------+-------+------------+------+-----------------------+---------+---------+--------------------+---------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

如上所示 ,如果按之前的理论的话,调用exists 次数为0 所以整体应该很快


root@mysql3306.sock>[employees]>flush status ;
Query OK, 0 rows affected (0.00 sec)


root@mysql3306.sock>[employees]>select straight_join *  from salaries3 a
    -> where 1=1
    -> and  salary>1000
    -> and a.to_date>'9999-12-31'
    -> and exists (
    -> select 1 from salaries s
    -> where a.emp_no=s.emp_no
    -> )
    -> ;
Empty set (0.67 sec)

root@mysql3306.sock>[employees]>show status like '%handle%';
+---------------------------------------+---------+
| Variable_name                         | Value   |
+---------------------------------------+---------+
| Handler_commit                        | 1       |
| Handler_delete                        | 0       |
| Handler_discover                      | 0       |
| Handler_external_lock                 | 4       |
| Handler_mrr_init                      | 0       |
| Handler_prepare                       | 0       |
| Handler_read_first                    | 1       |
| Handler_read_key                      | 1       |
| Handler_read_last                     | 0       |
| Handler_read_next                     | 0       |
| Handler_read_prev                     | 0       |
| Handler_read_rnd                      | 0       |
| Handler_read_rnd_next                 | 2844048 |
| Handler_rollback                      | 0       |
| Handler_savepoint                     | 0       |
| Handler_savepoint_rollback            | 0       |
| Handler_update                        | 0       |
| Handler_write                         | 0       |
| Performance_schema_file_handles_lost  | 0       |
| Performance_schema_table_handles_lost | 0       |
+---------------------------------------+---------+
20 rows in set (0.01 sec)

如上所示,确实很快

那把其中的最关键的 and a.to_date>’9999-12-31′ 调到 exists 下面呢 

select straight_join *  from salaries3 a
where 1=1  
and  salary>1000 
and exists (
select 1 from salaries s 
where a.emp_no=s.emp_no 
)
and a.to_date>'9999-12-31'
+----+--------------------+-------+------------+------+-----------------------+---------+---------+--------------------+---------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys         | key     | key_len | ref                | rows    | filtered | Extra       |
+----+--------------------+-------+------------+------+-----------------------+---------+---------+--------------------+---------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL                  | NULL    | NULL    | NULL               | 2838426 |    11.11 | Using where |
|  2 | DEPENDENT SUBQUERY | s     | NULL       | ref  | PRIMARY,emp_no,idx_t1 | PRIMARY | 4       | employees.a.emp_no |       9 |    33.33 | Using where |
+----+--------------------+-------+------------+------+-----------------------+---------+---------+--------------------+---------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

如果上述理论的话,因为salary>1000

满足条件非常多,那么调用exists 次数就会非常多  



root@mysql3306.sock>[employees]>select straight_join *  from salaries3 a
    -> where 1=1
    -> and  salary>1000
    -> and exists (
    -> select 1 from salaries s
    -> where a.emp_no=s.emp_no
    -> )
    -> and a.to_date>'9999-12-31' ;
Empty set (10.11 sec)


root@mysql3306.sock>[employees]>show status like '%handle%';
+---------------------------------------+---------+
| Variable_name                         | Value   |
+---------------------------------------+---------+
| Handler_commit                        | 1       |
| Handler_delete                        | 0       |
| Handler_discover                      | 0       |
| Handler_external_lock                 | 6       |
| Handler_mrr_init                      | 0       |
| Handler_prepare                       | 0       |
| Handler_read_first                    | 1       |
| Handler_read_key                      | 2844048 |
| Handler_read_last                     | 0       |
| Handler_read_next                     | 0       |
| Handler_read_prev                     | 0       |
| Handler_read_rnd                      | 0       |
| Handler_read_rnd_next                 | 2844690 |
| Handler_rollback                      | 0       |
| Handler_savepoint                     | 0       |
| Handler_savepoint_rollback            | 0       |
| Handler_update                        | 0       |
| Handler_write                         | 2       |
| Performance_schema_file_handles_lost  | 0       |
| Performance_schema_table_handles_lost | 0       |
+---------------------------------------+---------+


跟我们预测一样

| Handler_read_key                      | 2844048 |

这部分就是exists 调用次数 

结果发现时间变成10s 多 

这个版本是 如下 

root@mysql3306.sock>[employees]>\s
--------------
/usr/local/mysql/bin/mysql  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          30858
Current database:       employees
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.26 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /tmp/mysql3306.sock
Binary data as:         Hexadecimal
Uptime:                 20 days 3 hours 2 min 39 sec

Threads: 1  Questions: 1267  Slow queries: 11  Opens: 823  Flush tables: 3  Open tables: 721  Queries per second avg: 0.000


我们在学习的过程中,碰到很多稀奇古怪的问题,但是我们还是从原理出发

一步一步 慢慢的分析并且掌握

我是知数堂SQL 优化班老师~ ^^

最新一期SQL优化课,在12月份开始。

如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群 有叶金荣,吴炳锡 两位大神坐镇 :579036588

欢迎加入 知数堂大家庭。

我的微信公众号:SQL开发与优化(sqlturning)

发表评论

登录后才能评论
网站客服
网站客服
申请收录 侵权处理
分享本页
返回顶部