MySQL之select in 子查询优化的实现

下面的演示基于MySQL5.7.27版本

一、关于MySQL子查询的优化策略介绍:

子查询优化策略

对于不同类型的子查询,优化器会选择不同的策略。

1. 对于 IN、=ANY 子查询,优化器有如下策略选择:

  • semijoin
  • Materialization
  • exists

2. 对于 NOT IN、<>ALL 子查询,优化器有如下策略选择:

  • Materialization
  • exists

3. 对于 derived 派生表,优化器有如下策略选择:
derived_merge,将派生表合并到外部查询中(5.7 引入 );
将派生表物化为内部临时表,再用于外部查询。
注意:update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略

二、创建数据进行模拟演示

为了方便分析问题先建两张表并插入模拟数据:

CREATE TABLE `test02` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
 declare i int;
 set i=1;
 while(i<=10000)do
  insert into test02 values(i, i, i);
  set i=i+1;
 end while;
end;;
delimiter ;
call idata();

create table test01 like test02;
insert into test01 (select * from test02 where id<=1000)

三、举例分析SQL实例

子查询示例:

SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10)

大部分人可定会简单的认为这个 SQL 会这样执行:

SELECT test02.b FROM test02 WHERE id < 10

结果:1,2,3,4,5,6,7,8,9

SELECT * FROM test01 WHERE test01.a IN (1,2,3,4,5,6,7,8,9);

但实际上 MySQL 并不是这样做的。MySQL 会将相关的外层表压到子查询中,优化器认为这样效率更高。也就是说,优化器会将上面的 SQL 改写成这样:

select * from test01 where exists(select b from test02 where id < 10 and test01.a=test02.b);

提示: 针对mysql5.5以及之前的版本

查看执行计划如下,发现这条SQL对表test01进行了全表扫描1000,效率低下:

root@localhost [dbtest01]>desc select * from test01 where exists(select b from test02 where id < 10 and test01.a=test02.b);
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type    | table | partitions | type | possible_keys | key   | key_len | ref | rows  | filtered | Extra    |
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | PRIMARY      | test01 | NULL    | ALL  | NULL     | NULL  | NULL  | NULL | 1000  |  100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | test02 | NULL    | range | PRIMARY    | PRIMARY | 4    | NULL |   9 |  10.00 | Using where |
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

但是此时实际执行下面的SQL,发现也不慢啊,这不是自相矛盾嘛,别急,咱们继续往下分析:

SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10)

查看此条SQL的执行计划如下:

root@localhost [dbtest01]>desc SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10);
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key   | key_len | ref      | rows | filtered | Extra    |
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE    | <subquery2> | NULL    | ALL  | NULL     | NULL  | NULL  | NULL     | NULL |  100.00 | Using where |
| 1 | SIMPLE    | test01   | NULL    | ref  | a       | a    | 5    | <subquery2>.b |  1 |  100.00 | NULL    |
| 2 | MATERIALIZED | test02   | NULL    | range | PRIMARY    | PRIMARY | 4    | NULL     |  9 |  100.00 | Using where |
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

发现优化器使用到了策略MATERIALIZED。于是对此策略进行了资料查询和学习。
https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

原因是从MySQL5.6版本之后包括MySQL5.6版本,优化器引入了新的优化策略:materialization=[off|on],semijoin=[off|on],(off代表关闭此策略,on代表开启此策略)
可以采用show variables like 'optimizer_switch'; 来查看MySQL采用的优化器策略。当然这些策略都是可以在线进行动态修改的
set global optimizer_switch='materialization=on,semijoin=on';代表开启优化策略materialization和semijoin

MySQL5.7.27默认的优化器策略:

root@localhost [dbtest01]>show variables like 'optimizer_switch';
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name  | Value                                                                                                                                                                                                      |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

所以在MySQL5.6及以上版本时

执行下面的SQL是不会慢的。因为MySQL的优化器策略materialization和semijoin 对此SQL进行了优化

SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10)

然而咱们把mysql的优化器策略materialization和semijoin 关闭掉测试,发现SQL确实对test01进行了全表的扫描(1000):

set global optimizer_switch='materialization=off,semijoin=off';

执行计划如下test01表确实进行了全表扫描:

root@localhost [dbtest01]>desc SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10);
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type    | table | partitions | type | possible_keys | key   | key_len | ref | rows  | filtered | Extra    |
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | PRIMARY      | test01 | NULL    | ALL  | NULL     | NULL  | NULL  | NULL | 1000  |  100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | test02 | NULL    | range | PRIMARY    | PRIMARY | 4    | NULL |   9 |  10.00 | Using where |
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

下面咱们分析下这个执行计划:

!!!!再次提示:如果是mysql5.5以及之前的版本,或者是mysql5.6以及之后的版本关闭掉优化器策略materialization=off,semijoin=off,得到的SQL执行计划和下面的是相同的

root@localhost [dbtest01]>desc select * from test01 where exists(select b from test02 where id < 10 and test01.a=test02.b);
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type    | table | partitions | type | possible_keys | key   | key_len | ref | rows | filtered | Extra    |
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | PRIMARY      | test01 | NULL    | ALL  | NULL     | NULL  | NULL  | NULL | 1000 |  100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | test02 | NULL    | range | PRIMARY    | PRIMARY | 4    | NULL |  9 |  10.00 | Using where |
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

不相关子查询变成了关联子查询(select_type:DEPENDENT SUBQUERY),子查询需要根据 b 来关联外表 test01,因为需要外表的 test01 字段,所以子查询是没法先执行的。执行流程为:

  1. 扫描 test01,从 test01 取出一行数据 R;
  2. 从数据行 R 中,取出字段 a 执行子查询,如果得到结果为 TRUE,则把这行数据 R 放到结果集;
  3. 重复 1、2 直到结束。

总的扫描行数为 1000+1000*9=10000(这是理论值,但是实际值比10000还少,怎么来的一直没想明白,看规律是子查询结果集每多一行,总扫描行数就会少几行)。

Semi-join优化器:

这样会有个问题,如果外层表是一个非常大的表,对于外层查询的每一行,子查询都得执行一次,这个查询的性能会非常差。我们很容易想到将其改写成 join 来提升效率:

select test01.* from test01 join test02 on test01.a=test02.b and test02.id<10;

# 查看此SQL的执行计划:

desc select test01.* from test01 join test02 on test01.a=test02.b and test02.id<10;

root@localhost [dbtest01]>EXPLAIN extended select test01.* from test01 join test02 on test01.a=test02.b and test02.id<10;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref        | rows | filtered | Extra    |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE   | test02 | NULL    | range | PRIMARY    | PRIMARY | 4    | NULL       |  9 |  100.00 | Using where |
| 1 | SIMPLE   | test01 | NULL    | ref  | a       | a    | 5    | dbtest01.test02.b |  1 |  100.00 | NULL    |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

这样优化可以让 t2 表做驱动表,t1 表关联字段有索引,查找效率非常高。

但这里会有个问题,join 是有可能得到重复结果的,而 in(select ...) 子查询语义则不会得到重复值。
而 semijoin 正是解决重复值问题的一种特殊联接。
在子查询中,优化器可以识别出 in 子句中每组只需要返回一个值,在这种情况下,可以使用 semijoin 来优化子查询,提升查询效率。
这是 MySQL 5.6 加入的新特性,MySQL 5.6 以前优化器只有 exists 一种策略来“优化”子查询。

经过 semijoin 优化后的 SQL 和执行计划分为:

root@localhost [dbtest01]>desc SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10);
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key   | key_len | ref      | rows | filtered | Extra    |
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE    | <subquery2> | NULL    | ALL  | NULL     | NULL  | NULL  | NULL     | NULL |  100.00 | Using where |
| 1 | SIMPLE    | test01   | NULL    | ref  | a       | a    | 5    | <subquery2>.b |  1 |  100.00 | NULL    |
| 2 | MATERIALIZED | test02   | NULL    | range | PRIMARY    | PRIMARY | 4    | NULL     |  9 |  100.00 | Using where |
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
select
  `test01`.`id`,`test01`.`a`,`test01`.`b`
from `test01` semi join `test02`
where
  ((`test01`.`a` = `<subquery2>`.`b`)
  and (`test02`.`id` < 10));

##注意这是优化器改写的SQL,客户端上是不能用 semi join 语法的

semijoin 优化实现比较复杂,其中又分 FirstMatch、Materialize 等策略,上面的执行计划中 select_type=MATERIALIZED 就是代表使用了 Materialize 策略来实现的 semijoin
这里 semijoin 优化后的执行流程为:

先执行子查询,把结果保存到一个临时表中,这个临时表有个主键用来去重;
从临时表中取出一行数据 R;
从数据行 R 中,取出字段 b 到被驱动表 t1 中去查找,满足条件则放到结果集;
重复执行 2、3,直到结束。
这样一来,子查询结果有 9 行,即临时表也有 9 行(这里没有重复值),总的扫描行数为 9+9+9*1=27 行,比原来的 10000 行少了很多。

MySQL 5.6 版本中加入的另一种优化特性 materialization,就是把子查询结果物化成临时表,然后代入到外查询中进行查找,来加快查询的执行速度。内存临时表包含主键(hash 索引),消除重复行,使表更小。
如果子查询结果太大,超过 tmp_table_size 大小,会退化成磁盘临时表。这样子查询只需要执行一次,而不是对于外层查询的每一行都得执行一遍。
不过要注意的是,这样外查询依旧无法通过索引快速查找到符合条件的数据,只能通过全表扫描或者全索引扫描,

semijoin 和 materialization 的开启是通过 optimizer_switch 参数中的 semijoin={on|off}、materialization={on|off} 标志来控制的。
上文中不同的执行计划就是对 semijoin 和 materialization 进行开/关产生的
总的来说对于子查询,先检查是否满足各种优化策略的条件(比如子查询中有 union 则无法使用 semijoin 优化)
然后优化器会按成本进行选择,实在没得选就会用 exists 策略来“优化”子查询,exists 策略是没有参数来开启或者关闭的。

下面举一个delete相关的子查询例子:

把上面的2张测试表分别填充350万数据和50万数据来测试delete语句

root@localhost [dbtest01]>select count(*) from test02;
+----------+
| count(*) |
+----------+
| 3532986 |
+----------+
1 row in set (0.64 sec)
root@localhost [dbtest01]>create table test01 like test02;
Query OK, 0 rows affected (0.01 sec)

root@localhost [dbtest01]>insert into test01 (select * from test02 where id<=500000)

root@localhost [dbtest01]>select count(*) from test01;
+----------+
| count(*) |
+----------+
|  500000 |

执行delete删除语句执行了4s

root@localhost [dbtest01]>delete FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10);
Query OK, 9 rows affected (4.86 sec)

查看 执行计划,对test01表进行了几乎全表扫描:

root@localhost [dbtest01]>desc delete FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10);
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type    | table | partitions | type | possible_keys | key   | key_len | ref | rows  | filtered | Extra    |
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | DELETE       | test01 | NULL    | ALL  | NULL     | NULL  | NULL  | NULL | 499343 |  100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | test02 | NULL    | range | PRIMARY    | PRIMARY | 4    | NULL |   9 |  10.00 | Using where |
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set (0.00 sec)

于是修改上面的delete SQL语句伪join语句

root@localhost [dbtest01]>desc delete test01.* from test01 join test02 on test01.a=test02.b and test02.id<10;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref        | rows | filtered | Extra    |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE   | test02 | NULL    | range | PRIMARY    | PRIMARY | 4    | NULL       |  9 |  100.00 | Using where |
| 1 | DELETE   | test01 | NULL    | ref  | a       | a    | 5    | dbtest01.test02.b |  1 |  100.00 | NULL    |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
2 rows in set (0.01 sec)

执行非常的快
root@localhost [dbtest01]>delete test01.* from test01 join test02 on test01.a=test02.b and test02.id<10;
Query OK, 9 rows affected (0.01 sec)

root@localhost [dbtest01]>select test01.* from test01 join test02 on test01.a=test02.b and test02.id<10;
Empty set (0.00 sec)

下面的这个表执行要全表扫描,非常慢,基本对表test01进行了全表扫描:

root@lcalhost [dbtest01]>desc delete FROM test01 WHERE id IN (SELECT id FROM test02 WHERE id='350000');
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+
| id | select_type    | table | partitions | type | possible_keys | key   | key_len | ref  | rows  | filtered | Extra    |
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+
| 1 | DELETE       | test01 | NULL    | ALL  | NULL     | NULL  | NULL  | NULL | 499343 |  100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | test02 | NULL    | const | PRIMARY    | PRIMARY | 4    | const |   1 |  100.00 | Using index |
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+
2 rows in set (0.00 sec)

然而采用join的话,效率非常的高:

root@localhost [dbtest01]>desc delete test01.* FROM test01 inner join test02 WHERE test01.id=test02.id and test02.id=350000 ;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref  | rows | filtered | Extra    |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | DELETE   | test01 | NULL    | const | PRIMARY    | PRIMARY | 4    | const |  1 |  100.00 | NULL    |
| 1 | SIMPLE   | test02 | NULL    | const | PRIMARY    | PRIMARY | 4    | const |  1 |  100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set (0.01 sec)

root@localhost [dbtest01]> desc delete test01.* from test01 join test02 on test01.a=test02.b and test02.id=350000;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE   | test02 | NULL    | const | PRIMARY    | PRIMARY | 4    | const |  1 |  100.00 | NULL |
| 1 | DELETE   | test01 | NULL    | ref  | a       | a    | 5    | const |  1 |  100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set (0.00 sec)

参考文档:

https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html
https://www.jianshu.com/p/3989222f7084
https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

到此这篇关于MySQL之select in 子查询优化的实现的文章就介绍到这了,更多相关MySQL select in 子查询优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql中insert与select的嵌套使用解决组合字段插入问题

    如何在mysql从多个表中组合字段然后插入到一个新表中,通过一条sql语句实现.具体情形是:有三张表a.b.c,现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段.对于这种情况,我们可以使用如下的语句来实现: INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name 当然,上面的语句比较适合两个表的数据互插,如果多个表就不适应了.对于多个表,我们可以先将需要查询的字段join起来,然后组成一个视图后再

  • 解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated

    原因: MySQL 5.7.5和up实现了对功能依赖的检测.如果启用了only_full_group_by SQL模式(在默认情况下是这样),那么MySQL就会拒绝选择列表.条件或顺序列表引用的查询,这些查询将引用组中未命名的非聚合列,而不是在功能上依赖于它们.(在5.7.5之前,MySQL没有检测到功能依赖项,only_full_group_by在默认情况下是不启用的.关于前5.7.5行为的描述,请参阅MySQL 5.6参考手册.) 执行以下个命令,可以查看 sql_mode 的内容: mys

  • 用SELECT... INTO OUTFILE语句导出MySQL数据的教程

    表数据导出到一个文本文件最简单的方法是使用SELECT... INTO OUTFILE语句的查询结果直接导出到一个文件在服务器主机上. 导出数据的SELECT...INTO OUTFILE声明: 这句话的语法结合了常规的SELECT INTO OUTFILE文件名的末尾.默认的输出格式是相同的LOAD DATA,所以下面的语句导出tutorials_tbl的表制表符分隔的,换行结尾的文件到/tmp/tutorials.txt: mysql> SELECT * FROM tutorials_tbl

  • Mysql select in 按id排序实现方法

    表结构如下: mysql> select * from test; +----+-------+ | id | name | +----+-------+ | 1 | test1 | | 2 | test2 | | 3 | test3 | | 4 | test4 | | 5 | test5 | +----+-------+ 执行以下SQL: mysql> select * from test where id in(3,1,5); +----+-------+ | id | name | +-

  • 解析MySQL中INSERT INTO SELECT的使用

    1. 语法介绍有三张表a.b.c,现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段.对于这种情况,可以使用如下的语句来实现:INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name 上面的语句比较适合两个表的数据互插,如果多个表就不适应了.对于多个表,可以先将需要查询的字段JOIN起来,然后组成一个视图后再SELECT FROM就可以了: INSERT INTO a (field1,field2)

  • MySQL select、insert、update批量操作语句代码实例

    项目中经常的需要使用批量操作语句对数据进行操作,使用批量语句的时候需要注意MySQL数据库的条件限制,这里主要是数据的大小限制,这里对批量插入.批量查找及批量更新的基础操作进行下简单的汇总. 1.批量insert插入语句,如下的语句在测试环境尝试过1W+的批量插入,具体数量跟表结构及字段内容有关系. <insert id="addTextPushList"paramerterType = "java.util.List"> INSERT INTO SYS

  • UCenter info: MySQL Query Error SQL:SELECT value FROM [Table]vars WHERE noteexists

    大家先看下数据库权限问题,然后再进行如下操作. SQL:SELECT value FROM [Table]vars WHERE name='noteexists2′ UCenter info: MySQL Query Error SQL:SELECT value FROM [Table]vars WHERE name='noteexists2′ Error:SELECT command denied to user '数据库'@'IP地址' for table 'pre_ucenter_vars

  • mysql中insert与select的嵌套使用方法

    本文讲述了mysql中insert与select的嵌套使用的方法,对于初学MySQL的朋友有一定的借鉴价值. 这里需要实现在mysql从多个表中组合字段然后插入到一个新表中,通过一条sql语句实现该功能需求.具体情形是:有三张表a.b.c,现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段.对于这种情况,我们可以使用如下的语句来实现: INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name 当然,上

  • MySQL之select in 子查询优化的实现

    下面的演示基于MySQL5.7.27版本 一.关于MySQL子查询的优化策略介绍: 子查询优化策略 对于不同类型的子查询,优化器会选择不同的策略. 1. 对于 IN.=ANY 子查询,优化器有如下策略选择: semijoin Materialization exists 2. 对于 NOT IN.<>ALL 子查询,优化器有如下策略选择: Materialization exists 3. 对于 derived 派生表,优化器有如下策略选择: derived_merge,将派生表合并到外部查询

  • MySQL百万级数据分页查询优化方案

    当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询.对于数据库分页查询,也有很多种方法和优化的点.下面简单说一下我知道的一些方法. 准备工作 为了对下面列举的一些优化进行测试,下面针对已有的一张表进行说明. 表名:order_history 描述:某个业务的订单历史表 主要字段:unsigned int id,tinyint(4) int type 字段情况:该表一共37个字段,不包含text等大型数组,最大为varcha

  • MySQL SQL语句分析与查询优化详解

    如何获取有性能问题的SQL 1.通过用户反馈获取存在性能问题的SQL 2.通过慢查询日志获取性能问题的SQL 3.实时获取存在性能问题的SQL 使用慢查询日志获取有性能问题的SQL 首先介绍下慢查询相关的参数 1.slow_query_log 启动定制记录慢查询日志 设置的方法,可以通过MySQL命令行设置set global slow_query_log=on 或者修改/etc/my.cnf文件,添加slow_query_log=on 2.slow_query_log_file 指定慢查询日志

  • Oracle数据库中基本的查询优化与子查询优化讲解

    1. 查询条件合理排序 Oracle采用自下而上的顺序解析WHERE字据,从优化性能角度考虑,建议将那些可以过滤掉大量记录行的条件写在WHERE子句的末尾,而将表 之间的连接条件置于其他WHERE子句之前,即对易排查的条件先做判断处理,这样在过滤掉尽可能多的记录后再进行等值连接,可以提高检索效率. 例如: SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno

  • MySql数据库中的子查询与高级应用浅析

    MySql数据库中的子查询: 子查询:在一条select查询语句中嵌套另一条select语句,其主要作用是充当查询条件或确定数据源. 代码案例如下: 例1. 查询大于平均年龄的学生: select * from students where age > (select avg(age) from students); 例2. 查询学生在班的所有班级名字: select name from classes where id in (select cls_id from students where

  • MySQL 使用自定义变量进行查询优化

    优化排序查询 自定义变量的一个重要特性是你可以同时将该变量的数学计算后的结果再赋值给该变量,类似于我们的 i = i + 1这种方式.下面是一个用于计算数据表行号的例子: SET @rownum := 0; SELECT actor_id, @rownum := @rownum + 1 AS rownum FROM sakila.actor LIMIT 3; actor_id rownum 1 1 2 2 3 3 得到的结果也许看起来没什么意义,这是因为主键是从1自增的,因此行号和主键值是一样的

  • MySQL嵌套查询实现子查询的方法

    目录 一个查询语句嵌套在另一个查询语句内部的查询 1.带ANY(SOME)关键字的子查询 2.带ALL关键字的子查询 3.带EXISTS关键字的子查询 4.带IN关键字的子查询 5.带比较运算符的子查询 一个查询语句嵌套在另一个查询语句内部的查询 常用操作符:ANY(SOME), ALL, IN, EXISTS 比较运算符:>, >=, <, <=, != 等 创建两个表,表明分别是tb1,tb2,并向其各插入了数据. 1.带ANY(SOME)关键字的子查询 这里any和some是

  • mysql详细分析讲解子查询的使用

    出现在其他语句中的 select 语句,称为子查询或内查询:外部的查询语句,称为主查询或 外查询 . -- 子查询 -- 查询的条件来自于另一查询的结果 SELECT * FROM t_user WHERE number=(SELECT number FROM t_user WHERE NAME='张三') 当然子查询也有类型,分为以下几种 : 标量子查询(结果集只有一行一列) 列子查询(结果集只有一列多行) 行子查询(结果集有一行多列)(较少) 表子查询(结果集一般为多行多列) 这里我们以新建

  • MySQL中select语句使用order按行排序

    本文介绍MySQL数据库中执行select查询语句,并对查询的结果使用order by 子句进行排序. 再来回顾一下SQL语句中的select语句的语法: Select 语句的基本语法: Select <列的集合> from <表名> where <条件> order by <排序字段和方式> 如果要对查询结果按某个字段排序,则要使用order by 子句,如下: select * from <表名> order by <字段名称>

  • mysql 导出select语句结果到excel文件遇到问题及解决方法

    一.导出数据外部 1)mysql连接+将查询结果输出到文件.在命令行中执行(windows的cmd命令行,mac的终端) mysql -hxx -uxx -pxx -e "query statement" db > file -h:后面跟的是链接的host(主机) -u:后面跟的是用户名 -p:后面跟的是密码 db:你要查询的数据库 file:你要写入的文件,绝对路径 例如: 下面将 sql语句 select * from edu_iclass_areas 的查询结果输出到了 /

随机推荐