sexplain的语法与用法,一条慢查询sql的的分析
分类:数据库

NOT IN、JOIN、IS NULL、NOT EXISTS作用相比较
语句一:select count(*) from A where A.a not in (select a from B)

mysql not in、left join、IS NULL、NOT EXISTS 功能难题记录,joinexists

mysql not in、left join、IS NULL、NOT EXISTS 效用难点记录,供给的朋友能够参见下。

 

NOT IN、JOIN、IS NULL、NOT EXISTS效用相比
语句一:select count(*) from A where A.a not in (select a from B)

语句二:select count(*) from A left join B on A.a = B.a where B.a is null

语句三:select count(*) from A where not exists (select a from B where A.a = B.a)

知情以上三条语句的实效是大同小异的已经相当久了,可是一向未曾探究其间的作用比较。平昔以为上语句二是最快的。
明天专门的职业上因为要对四个数千万行数据的库开始展览数量清除,必要删掉三千多万行数据。多量的选用了上述三条语句所要落成的机能。本来用的是语句一,不过结果是推行速度1个钟头32分,日志文件占用21GB。时间上纵然尚可,然则对硬盘空间的挤占确是个难题。由此将兼具的语句一都换到语句二。本以为会更加快。没 想到实践40多分钟后,第一群四千0行都未有删掉,反而让SQL SEEnclaveVEPRADO崩溃掉了,结果让人惊异。试了试单独实践那条语句,查询近一千万行的表,语句一用了4秒,语句二却用了18秒,大相径庭。语句三的功用与语句 一接近。

第二种写法是避讳,应该尽量防止。第一种和第二种写法本质上差不离同样。

要是buffer pool丰富大,写法二相对于写法一来讲存在以下几点不足:
(1)left join本身更耗财富(必要越来越多财富来处理产生的中游结果集)
(2)left join的中等结果集的范畴不会比表A小
(3)写法二还亟需对left join发生的中等结果做is null的标准筛选,而写法一则在五个集结join的还要到位了筛选,那有的付出是外加的

那三点综合起来,在拍卖海量数据时就会生出相比较领悟的不一致(首就算内存和CPU上的费用)。小编思疑楼主在测量试验时buffer pool大概早已处在饱和状态,这样的话,写法二的那多个额外费用不得不凭借磁盘上的设想内部存款和储蓄器,在SQL Server做换页时,由于涉及到很慢的I/O操作因而这种差异会愈发鲜明。

有关日志文件过大,那也是例行的,因为除去的笔录多嘛。能够依赖数据库的用处思考将回涨模型设为simple,只怕在剔除甘休后将日志truncate掉并把公文shrink下来。

因 为从前曾经作过七个对这几个库开始展览义务诊疗删除的剧本,正是要删减数据量不小的表中的有所数据,不过因为客户必要,不能够选用truncate table,怕破坏已有个别库结构。所以不得不用delete删,当时也赶过了日记文件过大的主题材料,当时使用的办法是分批删除,在SQL2K中用set rowcount @chunk,在SQL2K5中用delete top @chunk。那样的操作不止使删除时间大大降低,并且让日志量大大裁减,只增进了1G左右。
可是此次清除数据的劳作急需加上条件,就是delete A from A where ....后边有法规的。再度行使分批删除的点子,却已经没效果了。
不知你知道还是不知道道那是干什么。

mysql not in 和 left join 功用难题记录
率先表明该条sql的意义是查询集结a不在集结b的数码。
not in的写法

复制代码 代码如下:
select add_tb.RUID
from (select distinct RUID
from UserMsg
where SubjectID =12
and CreateTime>'2009-8-14 15:30:00'
and CreateTime<='2009-8-17 16:00:00'
) add_tb
where add_tb.RUID
not in (select distinct RUID
from UserMsg
where SubjectID =12
and CreateTime<'2009-8-14 15:30:00'
)

归来444行记录用时 0.07sec
explain 结果
---- -------------------- ------------ ---------------- --------------------------- ------------ --------- ------ ------ --

----------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |

Extra |
sexplain的语法与用法,一条慢查询sql的的分析。 ---- -------------------- ------------ ---------------- --------------------------- ------------ --------- ------ ------ --

----------------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 452 |

Using where |
| 3 | DEPENDENT SUBQUERY | UserMsg | index_subquery | RUID,SubjectID,CreateTime | RUID | 96 | func | 2 |

Using index; Using where |
| 2 | DERIVED | UserMsg | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1857 |

Using where; Using temporary |
---- -------------------- ------------ ---------------- --------------------------- ------------ --------- ------ ------ --

----------------------------
解析:该条查询速度快原因为id=2的sql查询出来的结果很少,所以id=1sql所以运行速度相当的慢,id=2的使用了不经常表,不明了这年是或不是利用索引?
中间一种left join

复制代码 代码如下:
select a.ruid,b.ruid
from(select distinct RUID
from UserMsg
where SubjectID =12
and CreateTime >= '2009-8-14 15:30:00'
and CreateTime<='2009-8-17 16:00:00'
) a left join (
select distinct RUID
from UserMsg
where SubjectID =12 and CreateTime< '2009-8-14 15:30:00'
) b on a.ruid = b.ruid
where b.ruid is null

回到444行记录用时 0.39sec
explain 结果
---- ------------- ------------ ------- ---------------------- ------------ --------- ------ ------ -----------------------

-------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

|
---- ------------- ------------ ------- ---------------------- ------------ --------- ------ ------ -----------------------

-------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 452 |

|
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 1112 | Using where; Not exists

|
| 3 | DERIVED | UserMsg | ref | SubjectID,CreateTime | SubjectID | 5 | | 6667 | Using where; Using

temporary |
| 2 | DERIVED | UserMsg | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1838 | Using where; Using

temporary |
---- ------------- ------------ ------- ---------------------- ------------ --------- ------ ------ -----------------------

-------
浅析:使用了三个不经常表,並且三个有的时候表做了笛Carl积,导致不能利用索引况且数据量相当的大
其他一种left join

复制代码 代码如下:
select distinct a.RUID
from UserMsg a
left join UserMsg b
on a.ruid = b.ruid
and b.subjectID =12 and b.createTime < '2009-8-14 15:30:00'
where a.subjectID =12
and a.createTime >= '2009-8-14 15:30:00'
and a.createtime <='2009-8-17 16:00:00'
and b.ruid is null;

回到444行记录用时 0.07sec
explain 结果
---- ------------- ------- ------- --------------------------- ------------ --------- -------------- ------ ---------------

--------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

|
---- ------------- ------- ------- --------------------------- ------------ --------- -------------- ------ ---------------

--------------------
| 1 | SIMPLE | a | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1839 | Using where;

Using temporary |
| 1 | SIMPLE | b | ref | RUID,SubjectID,CreateTime | RUID | 96 | dream.a.RUID | 2 | Using where;

Not exists; Distinct |
---- ------------- ------- ------- --------------------------- ------------ --------- -------------- ------ ---------------

--------------------
深入分析:一遍询问都以用上了目录,并且询问时同一时候展开的,所以查询成效应该非常高
使用not exists的sql

复制代码 代码如下:
select distinct a.ruid
from UserMsg a
where a.subjectID =12
and a.createTime >= '2009-8-14 15:30:00'
and a.createTime <='2009-8-17 16:00:00'
and not exists (
select distinct RUID
from UserMsg
where subjectID =12 and createTime < '2009-8-14 15:30:00'
and ruid=a.ruid
)

回到444行记录用时 0.08sec
explain 结果
---- -------------------- --------- ------- --------------------------- ------------ --------- -------------- ------ ------

------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

|
---- -------------------- --------- ------- --------------------------- ------------ --------- -------------- ------ ------

------------------------
| 1 | PRIMARY | a | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1839 | Using

where; Using temporary |
| 2 | DEPENDENT SUBQUERY | UserMsg | ref | RUID,SubjectID,CreateTime | RUID | 96 | dream.a.RUID | 2 | Using

where |
---- -------------------- --------- ------- --------------------------- ------------ --------- -------------- ------ ------

------------------------
浅析:同上基本上是同一的,只是表达了2个查询顺序实行,查询作用低于第1个

为了证实数据查询作用,将上述查询中的subjectID =12的范围规范去掉,结果计算查询时间如下
0.20s
21.31s
0.25s
0.43s

laserhe协助深入分析难题计算

复制代码 代码如下:
select a.ruid,b.ruid
from( select distinct RUID
from UserMsg
where CreateTime >= '2009-8-14 15:30:00'
and CreateTime<='2009-8-17 16:00:00'
) a left join UserMsg b
on a.ruid = b.ruid
and b.createTime < '2009-8-14 15:30:00'
where b.ruid is null;

实施时间0.13s
---- ------------- ------------ ------- ----------------- ------------ --------- -------- ------ --------------------------

----
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

|
---- ------------- ------------ ------- ----------------- ------------ --------- -------- ------ --------------------------

----
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1248 |

|
| 1 | PRIMARY | b | ref | RUID,CreateTime | RUID | 96 | a.RUID | 2 | Using where; Not exists

sexplain的语法与用法,一条慢查询sql的的分析。|
| 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3553 | Using where; Using

temporary |
---- ------------- ------------ ------- ----------------- ------------ --------- -------- ------ --------------------------

----
进行功用类似与not in的成效

数据库优化的主干条件:让笛Carl积产生在尽量小的集结之间,mysql在join的时候可以直接通过索引来围观,而放手到子查询里头,查询规

划器就不知底用适合的目录了。
贰个SQL在数据Curry是如此优化的:首先SQL会深入分析成一批深入分析树,多个树状数据结构,然后在那个数据结构里,查询规划器会查找有未有方便

的目录,然后根据具体情状做三个排列组合,然后总计那么些排列组合中的各类的付出(类似explain的输出的微管理器可读版本),然后相比里

面费用非常的小的,接纳并进行之。那么:
explain select a.ruid,b.ruid from(select distinct RUID from UserMsg where CreateTime >= '2009-8-14 15:30:00'

and CreateTime<='2009-8-17 16:00:00' ) a left join UserMsg b on a.ruid = b.ruid and b.createTime < '2009-8-14 15:30:00'

澳门新萄京,where b.ruid is null;

explain select add_tb.RUID
-> from (select distinct RUID
-> from UserMsg
-> where CreateTime>'2009-8-14 15:30:00'
-> and CreateTime<='2009-8-17 16:00:00'
-> ) add_tb
-> where add_tb.RUID
-> not in (select distinct RUID
-> from UserMsg
-> where CreateTime<'2009-8-14 15:30:00'
-> );
explain
---- -------------------- ------------ ---------------- ----------------- ------------ --------- ------ ------ ------------

------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

|
---- -------------------- ------------ ---------------- ----------------- ------------ --------- ------ ------ ------------

------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1248 | Using where

|
| 3 | DEPENDENT SUBQUERY | UserMsg | index_subquery | RUID,CreateTime | RUID | 96 | func | 2 | Using index;

Using where |
| 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3509 | Using where;

Using temporary |
---- -------------------- ------------ ---------------- ----------------- ------------ --------- ------ ------ ------------

------------------
付出是全然同样的,花费能够从 rows 那几个字段得出(基本上是rows这一个字段各样行的数值的乘积,约等于笛Carl积)
可是呢:上边那个:
explain select a.ruid,b.ruid from(select distinct RUID from UserMsg where CreateTime >= '2009-8-14 15:30:00'

and CreateTime<='2009-8-17 16:00:00' ) a left join ( select distinct RUID from UserMsg where createTime < '2009-8-14

15:30:00' ) b on a.ruid = b.ruid where b.ruid is null;
试行时间21.31s
---- ------------- ------------ ------- --------------- ------------ --------- ------ ------- -----------------------------

-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

|
---- ------------- ------------ ------- --------------- ------------ --------- ------ ------- -----------------------------

-
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1248 |

|
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 30308 | Using where; Not exists

|
| 3 | DERIVED | UserMsg | ALL | CreateTime | NULL | NULL | NULL | 69366 | Using where; Using temporary

|
| 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3510 | Using where; Using temporary

|
---- ------------- ------------ ------- --------------- ------------ --------- ------ ------- -----------------------------

-
自个儿就有一点点不通晓
怎么是四行
何况中间两行巨大无比
按理说
询问规划器应该能把这一个查询优化得面前边的八个同样的
(至少在自家驾驭的pgsql数据Curry本人有信念是完全一样的)
但mysql里头不是
因而自个儿感觉查询规划器里头可能依然糙了点
本人后边说过优化的中坚尺度就是,让笛Carl积产生在玩命小的联谊之间
那便是说地点最终一种写法至少未有违反那几个准绳
就算如此b 表因为符合条件的不行多,基本上不会用索引
只是并不该妨碍查询优化器看到外面的join on条件,进而和前面两个SQL一样,选取主键举行join
唯独自个儿前面说过查询规划器的作用
答辩上来说
遍历三次全体一点都不小可能率,总结一下支出
是合情的
自家以为这里最终一种写法未有遍历完整全部极大大概
也许的原故是子查询的兑现依然相比轻便?
子查询对数据库的确是个挑衅
因为基本都以递归的事物
为此在那么些环节有一些毛病并不奇异
其实你细心绪忖,最后一种写法唯有是大家率先种写法的叁个变种,关键在表b的where 条件放在哪个地方
位于中间,就不会用索引去join
投身外边就可以
其一自个儿便是排列组合的一个或者

not in、left join、IS NULL、NOT EXISTS 功效难题记录,joinexists mysql not in、left join、IS NULL、NOT EXISTS 功能难点记录,供给的相爱的人能够参照他事他说加以考察下。...

mysql教程 sexplain的语法与用法

一条慢查询sql的的剖判

先交由数据表table结构

 

mysql> show create table tt G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(16) NOT NULL DEFAULT '',
  `rule_id` int(10) unsigned NOT NULL DEFAULT '0',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ttx` (`name`,`rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1176504 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

 

此时此刻数据库中多少差不离有3000W条,之后恐怕还会到处拉长,以后想要查询的是:从表中找寻同样姓名,一样准则ID,何况status = 1对应的最后一条记下

 

写个程序随机进入数据

<?php

mysql_connect('10.0.0.234','root','123456');                                                                                                                                        
mysql_select_db('testdb');
mysql_query("set names utf8");

$strs ='';
$start = 'insert into tt values ';
for($i=0;$i<3000000;$i  ){

    $k = range(a,z);
    shuffle($k);
    $str = implode('',$k);
    $num = mt_rand(8,16);
    $in = substr($str,0,$num);

    $strs .= " (NULL,'$in',$num,1),";
    if($i000 == 0){
        echo $i."n";
        $sql = trim($start.$strs,',');
        mysql_query($sql);
        $strs ='';
    }  
}

 

 

这么300W数据就有了

 

方今的询问语句是如此的

 

SELECT * FROM tt WHERE id IN (

SELECT max(id) AS max_id FROM tt GROUP BY name, rule_id)

AND status = 1 

 

看一下询问安排

 

mysql> explain SELECT * FROM tt WHERE id IN ( SELECT max(id) AS max_id FROM tt GROUP BY name, rule_id) and status=1;

---- -------------------- ------- ------- --------------- ------ --------- ------ --------- -------------

| id | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |

---- -------------------- ------- ------- --------------- ------ --------- ------ --------- -------------

|  1 | PRIMARY            | tt    | ALL   | NULL          | NULL | NULL    | NULL | 1176818 | Using where |

|  2 | DEPENDENT SUBQUERY | tt    | index | NULL          | ttx  | 52      | NULL |       1 | Using index |

---- -------------------- ------- ------- --------------- ------ --------- ------ --------- -------------

 

索援用上了,也远非filesort,那是或不是就快快了,然后施行查询,就卡主了,卡主了,最后只能被Ctrl C了

 

那么那些可怜,就换二个,这里作者想到了,子查询换来join看一看效果

 

SELECT a.id,b.name,b.rule_id  FROM (select max(id) as id from tt   group by name, rule_id) as a left join tt b on a.id = b.id

 

看一下查询计划

 

mysql> explain SELECT a.id,b.name,b.rule_id  FROM (select max(id) as id from tt   group by name, rule_id) as a left join tt b on a.id = b.id;

---- ------------- ------------ -------- --------------- --------- --------- ------ --------- -------------

| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows    | Extra       |

---- ------------- ------------ -------- --------------- --------- --------- ------ --------- -------------

|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL | 1176503 |             |

|  1 | PRIMARY     | b          | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |       1 |             |

|  2 | DERIVED     | tt         | index  | NULL          | ttx     | 52      | NULL | 1176818 | Using index |

---- ------------- ------------ -------- --------------- --------- --------- ------ --------- -------------

 

实施结果  1.77579775 sec

 

mysql> show profiles;

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

| Query_ID | Duration   | Query                                                                                                                         |

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

|        6 | 1.77579775 | SELECT a.id,b.name,b.rule_id  FROM (select max(id) as id from tt   group by name, rule_id) as a left join tt b on a.id = b.id |

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

7 rows in set (0.00 sec)

 

看一看实践 进度

mysql> show profile for query  6;    
 ------------------------------ ---------- 
| Status                       | Duration |
 ------------------------------ ---------- 
| Sending data                 | 0.000596 |
| Waiting for query cache lock | 0.000002 |
| Sending data                 | 0.000579 |
| Waiting for query cache lock | 0.000002 |
| Sending data                 | 0.000534 |
| Waiting for query cache lock | 0.000002 |
| Sending data                 | 1.101490 |
| end                          | 0.000010 |
| query end                    | 0.000004 |
| closing tables               | 0.000003 |
| removing tmp table           | 0.001369 |
| closing tables               | 0.000010 |
| freeing items                | 0.000024 |
| logging slow query           | 0.000002 |
| logging slow query           | 0.000035 |
| cleaning up                  | 0.000003 |
 ------------------------------ ---------- 
100 rows in set (0.00 sec)

 

 

太多的查询缓存lock,关掉它,再看一下询问结果

mysql> show profile for query  10;  
 --------------------------- ---------- 
| Status                    | Duration |
 --------------------------- ---------- 
| starting                  | 0.000154 |
| checking permissions      | 0.000006 |
| checking permissions      | 0.000005 |
| Opening tables            | 0.000124 |
| System lock               | 0.000122 |
| optimizing                | 0.000007 |
| statistics                | 0.000035 |
| preparing                 | 0.000018 |
| executing                 | 0.000012 |
| Sorting result            | 0.000006 |
| Sending data              | 0.518897 |
| converting HEAP to MyISAM | 0.070147 |
| Sending data              | 0.067123 |
| init                      | 0.000033 |
| optimizing                | 0.000003 |
| statistics                | 0.000020 |
| preparing                 | 0.000009 |
| executing                 | 0.000001 |
| Sending data              | 1.193679 |
| end                       | 0.000011 |
| query end                 | 0.000010 |
| closing tables            | 0.000002 |
| removing tmp table        | 0.001491 |
| closing tables            | 0.000011 |
| freeing items             | 0.000020 |
| logging slow query        | 0.000002 |
| logging slow query        | 0.000050 |
| cleaning up               | 0.000003 |
 --------------------------- ---------- 

 

 

重复深入分析,大家发掘 converting HEAP to MyISAM 那些很耗费时间

 

mysql> select @@max_heap_table_size/1024/1024;
 --------------------------------- 
| @@max_heap_table_size/1024/1024 |
 --------------------------------- 
|                     16.00000000 |
 --------------------------------- 

mysql> set max_heap_table_size = 16777216*4;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_heap_table_size/1024/1024;
 --------------------------------- 
| @@max_heap_table_size/1024/1024 |
 --------------------------------- 
|                     64.00000000 |
 --------------------------------- 

 

 

再看看实践结果  1.77579775 > 1.68962725 ,照旧有功用的

 

mysql> show profiles;

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

| Query_ID | Duration   | Query                                                                                                                         |

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

|       17 | 1.68962725 | SELECT a.id,b.name,b.rule_id  FROM (select max(id) as id from tt   group by name, rule_id) as a left join tt b on a.id = b.id |

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

分析

mysql> show profile for query  17; 
 ---------------------- ---------- 
| Status               | Duration |
 ---------------------- ---------- 
| starting             | 0.000120 |
| checking permissions | 0.000004 |
| checking permissions | 0.000003 |
| Opening tables       | 0.000106 |
| System lock          | 0.000113 |
| optimizing           | 0.000007 |
| statistics           | 0.000044 |
| preparing            | 0.000011 |
| executing            | 0.000006 |
| Sorting result       | 0.000002 |
| Sending data         | 0.567858 |
| init                 | 0.000032 |
| optimizing           | 0.000004 |
| statistics           | 0.000017 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 1.120159 |
| end                  | 0.000011 |
| query end            | 0.000005 |
| closing tables       | 0.000002 |
| removing tmp table   | 0.001020 |
| closing tables       | 0.000011 |
| freeing items        | 0.000018 |
| logging slow query   | 0.000002 |
| logging slow query   | 0.000056 |
| cleaning up          | 0.000004 |
 ---------------------- ---------- 

 

 

美观多了,耗费时间的地方都在 Sending data 上了,假如硬盘换来PCI-SSD 揣摸又能加强广大

 

再有未有其余方式吗,当然有,那么换一种写法

 

先看查询布署

 

mysql> explain select max(concat_ws(' ',lpad(id,5,' '),status)) as res, name, rule_id from tt group by name, rule_id ;

---- ------------- ------- ------- --------------- ------ --------- ------ --------- -------

| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra |

---- ------------- ------- ------- --------------- ------ --------- ------ --------- -------

|  1 | SIMPLE      | tt    | index | NULL          | ttx  | 52      | NULL | 1176818 |       |

---- ------------- ------- ------- --------------- ------ --------- ------ --------- -------

再看看实践结果

 

mysql> show profiles;

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

| Query_ID | Duration   | Query                                                                                                                         |

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

|       22 | 1.82505025 | select max(concat_ws(' ',lpad(id,5,' '),status)) as res, name, rule_id from tt group by name, rule_id                         |

---------- ------------ -------------------------------------------------------------------------------------------------------------------------------

恍如更加慢了 1.82505025 > 1.68962725

 

浅析一下

 

mysql> show profile for query  22;
 ---------------------- ---------- 
| Status               | Duration |
 ---------------------- ---------- 
| starting             | 0.000157 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000019 |
| System lock          | 0.000020 |
| init                 | 0.000032 |
| optimizing           | 0.000005 |
| statistics           | 0.000016 |
| preparing            | 0.000012 |
| executing            | 0.000008 |
| Sorting result       | 0.000003 |
| Sending data         | 1.824677 |
| end                  | 0.000012 |
| query end            | 0.000005 |
| closing tables       | 0.000009 |
| freeing items        | 0.000016 |
| logging slow query   | 0.000002 |
| logging slow query   | 0.000049 |
| cleaning up          | 0.000004 |
 ---------------------- ---------- 

 

 

日子大约都耗费在send data了

 

此间说一下,max group by 完全正是八个坑,借使有三个字段要回到数据无法这么写

 

select  max(id) ,type ,name from table  where type=xx group by name

 

因为group by暗许再次回到第一条记下

 

设若像上面那样写,断定会境遇坑的

 

select max(id) as res, name, rule_id from tt group by name, rule_id ;

 

如此那般也能够,何况快多了,可是只要有别的字段如何做

 

这么的语句基本上并未有啥样能够再优化了,只可以换换别的艺术了,比如:换SSD raid10 ,分区/分表/分库 中间件了

先给出数据表table结构 mysql show create table tt G*************************** 1. row *************************** Table: ttCreate Table: CREA...

语句二:select count(*) from A left join B on A.a = B.a where B.a is null

explain [extended] select … from … where …

语句三:select count(*) from A where not exists (select a from B where A.a = B.a)

假如选取了extended,那么在实践完explain语句后,能够动用show warnings语句询问相应

驾驭以上三条语句的实效是同一的已经非常久了,不过一向尚未追究其间的功能比较。一直感觉上语句二是最快的。
前些天做事上因为要对三个数千万行数据的库实行数量清除,须求删掉3000多万行数据。多量的使用了以上三条语句所要完毕的效果。本来用的是语句一,不过结果是实践进程1个时辰32分,日志文件占用21GB。时间上即便能够承受,不过对硬盘空间的据有确是个难点。因而将享有的语句一都换到语句二。本感觉会越来越快。没悟出施行40多分钟后,第一堆伍仟0行都未有删掉,反而让SQL SEGL450VE冠道崩溃掉了,结果令人惊愕。试了试单独试行这条语句,查询近一千万行的表,语句一用了4秒,语句二却用了18秒,风马牛不相及。语句三的频率与语句一邻近。

的优化新闻。

第三种写法是禁忌,应该尽量防止。第一种和第二种写法本质上大致一致。

比方说我们实施  select uid from user where uname=’sco田野同志’ order by uid  推行结

一经buffer pool丰硕大,写法二相对于写法一的话存在以下几点不足:
(1)left join自己更耗电源(供给更加多财富来管理爆发的中级结果集)
(2)left join的高级中学级结果集的层面不会比表A小
(3)写法二还须要对left join发生的中间结果做is null的标准化筛选,而写法一则在多少个会集join的还要做到了筛选,那部分支付是相当的

果会有

那三点综合起来,在管理海量数据时就能够发生相比较鲜明的区分(主要是内部存储器和CPU上的付出)。作者疑心楼主在测量试验时buffer pool恐怕曾经处在饱和状态,那样的话,写法二的那多少个额外开支不得不信赖磁盘上的虚构内部存款和储蓄器,在SQL Server做换页时,由于涉及到非常慢的I/O操作由此这种差异会愈加明朗。

 | id | select_type | table | type  | possible_keys     | key     | key_len |

关于日志文件过大,那也是正规的,因为除去的笔录多嘛。能够依据数据库的用处思索将苏醒模型设为simple,只怕在剔除甘休后将日志truncate掉并把文件shrink下来。

ref   | rows | Extra |

因为在此以前曾经作过三个对这么些库举办无条件删除的本子,便是要刨除数据量异常的大的表中的持有数据,不过因为客户需要,无法选择truncate table,怕破坏已部分库结构。所以只能用delete删,当时也遇上了日志文件过大的问题,当时接纳的方法是分批删除,在SQL2K中用set rowcount @chunk,在SQL2K5中用delete top @chunk。那样的操作不止使删除时间大大减少,而且让日志量大大减少,只升高了1G左右。
只是此番清除数据的办事索要充分条件,就是delete A from A where ....前面有标准化的。再一次利用分批删除的法子,却一度没意义了。
不知你知不知道道那是为什么。

那么些事物。

mysql not in 和 left join 功效难题记录
首先表达该条sql的意义是询问集合a不在集结b的数量。
not in的写法

其间 table 表示是哪个表的数额。

复制代码 代码如下:

type比较根本。表示链接的品类。链接类型由好到坏的,依次是    system > const >

select add_tb.RUID
from (select distinct RUID
from UserMsg
where SubjectID =12
and CreateTime>'2009-8-14 15:30:00'
and CreateTime<='2009-8-17 16:00:00'
) add_tb
where add_tb.RUID
not in (select distinct RUID
from UserMsg
where SubjectID =12
and CreateTime<'2009-8-14 15:30:00'
)

eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >

归来444行记录用时 0.07sec
explain 结果
---- -------------------- ------------ ---------------- --------------------------- ------------ --------- ------ ------ --

index_subquery > range > index > ALL

----------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |

诚如景观,至少要达到 range 等级,最佳是 ref 级别。否则可能会有总体性难点。

Extra |
---- -------------------- ------------ ---------------- --------------------------- ------------ --------- ------ ------ --

possible_keys 是指能够使用到该表的目录,假诺为NULL则从未。

----------------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 452 |

key 是指利用的目录。

Using where |
| 3 | DEPENDENT SUBQUERY | UserMsg | index_subquery | RUID,SubjectID,CreateTime | RUID | 96 | func | 2 |

key_len 是索引的长度,在不影响查询精度的事态下,值越小越好。

Using index; Using where |
| 2 | DERIVED | UserMsg | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1857 |

ref 是指索引的那一列被利用了。一般会是个常数。

Using where; Using temporary |
---- -------------------- ------------ ---------------- --------------------------- ------------ --------- ------ ------ --

rows 是指有微微行。

----------------------------
分析:该条查询速度快原因为id=2的sql查询出来的结果很少,所以id=1sql所以运营速度十分的快,id=2的利用了有时表,不理解这一年是不是选取索引?
个中一种left join

extra 是指额外的消息。也是相比较关键的。倘使值为 distinct ,表明mysql 找到了域行联

复制代码 代码如下:

合相称的行,就不再查找了。

select a.ruid,b.ruid
from(select distinct RUID
from UserMsg
where SubjectID =12
and CreateTime >= '2009-8-14 15:30:00'
and CreateTime<='2009-8-17 16:00:00'
) a left join (
select distinct RUID
from UserMsg
where SubjectID =12 and CreateTime< '2009-8-14 15:30:00'
) b on a.ruid = b.ruid
where b.ruid is null

设若值为 not exits  :  mysql优化了 left join ,一旦找到了 left join 相称的行,便

再次来到444行记录用时 0.39sec
explain 结果
---- ------------- ------------ ------- ---------------------- ------------ --------- ------ ------ -----------------------

不再举办检索了。

-------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

比如值为 rang checked for each :  未有找到完美的目录。

|
---- ------------- ------------ ------- ---------------------- ------------ --------- ------ ------ -----------------------

一经为 using filesort ,则需求改革sql了。那表明 mysql推行 要求文件排序。那是比

-------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 452 |

较影响功能的。

|
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 1112 | Using where; Not exists

设若为  using  temporary , 那是使用了 一时表。 这种情景也相比较影响效用,sql供给改

|
| 3 | DERIVED | UserMsg | ref | SubjectID,CreateTime | SubjectID | 5 | | 6667 | Using where; Using

进。可能从应用层实行校勘。

temporary |
| 2 | DERIVED | UserMsg | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1838 | Using where; Using

倘若为 where used 表明使用了where语句。要是 type为 all 或然 index ,一般会现出那

temporary |
---- ------------- ------------ ------- ---------------------- ------------ --------- ------ ------ -----------------------

样的结果。那样的主题材料,一般是查询供给创新。

-------
解析:使用了七个不时表,而且多少个有时表做了笛Carl积,导致不能应用索引並且数据量十分的大
别的一种left join

在形似稍大的系统中,基本尽大概的回降 join ,子查询 等等。mysql就采纳最简便的查询

复制代码 代码如下:

,那样功效最高。至于 join 等,能够放在应用层去化解

select distinct a.RUID
from UserMsg a
left join UserMsg b
on a.ruid = b.ruid
and b.subjectID =12 and b.createTime < '2009-8-14 15:30:00'
where a.subjectID =12
and a.createTime >= '2009-8-14 15:30:00'
and a.createtime <='2009-8-17 16:00:00'
and b.ruid is null;

二.explain输出解释

回来444行记录用时 0.07sec
explain 结果
---- ------------- ------- ------- --------------------------- ------------ --------- -------------- ------ ---------------

   ---- ------------- ------- ------- ------------------- --------- ---------

--------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

------- ------ -------

|
---- ------------- ------- ------- --------------------------- ------------ --------- -------------- ------ ---------------

  | id | select_type | table | type  | possible_keys     | key     | key_len |

--------------------
| 1 | SIMPLE | a | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1839 | Using where;

ref   | rows | Extra |

Using temporary |
| 1 | SIMPLE | b | ref | RUID,SubjectID,CreateTime | RUID | 96 | dream.a.RUID | 2 | Using where;

   ---- ------------- ------- ------- ------------------- --------- ---------

Not exists; Distinct |
---- ------------- ------- ------- --------------------------- ------------ --------- -------------- ------ ---------------

------- ------ -------

--------------------
浅析:四回查询都以用上了目录,并且询问时还要拓展的,所以查询成效应该异常高
使用not exists的sql

  1.id

复制代码 代码如下:

  作者的通晓是SQL实践的顺畅的标记,SQL从大到小的试行.

select distinct a.ruid
from UserMsg a
where a.subjectID =12
and a.createTime >= '2009-8-14 15:30:00'
and a.createTime <='2009-8-17 16:00:00'
and not exists (
select distinct RUID
from UserMsg
where subjectID =12 and createTime < '2009-8-14 15:30:00'
and ruid=a.ruid
)

  例如:

回到444行记录用时 0.08sec
explain 结果
---- -------------------- --------- ------- --------------------------- ------------ --------- -------------- ------ ------

  mysql> explain select * from (select * from ( select * from t3 where

------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

id=3952602) a) b;

|
---- -------------------- --------- ------- --------------------------- ------------ --------- -------------- ------ ------

   ---- ------------- ------------ -------- ------------------- --------- ---

------------------------
| 1 | PRIMARY | a | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1839 | Using

------ ------ ------ -------

where; Using temporary |
| 2 | DEPENDENT SUBQUERY | UserMsg | ref | RUID,SubjectID,CreateTime | RUID | 96 | dream.a.RUID | 2 | Using

  | id | select_type | table      | type   | possible_keys     | key     |

where |
---- -------------------- --------- ------- --------------------------- ------------ --------- -------------- ------ ------

key_len | ref  | rows | Extra |

------------------------
浅析:同上海南大学学都以平等的,只是表达了2个查询顺序试行,查询作用低于第二个

   ---- ------------- ------------ -------- ------------------- --------- ---

为了求证数据查询功效,将上述查询中的subjectID =12的界定条件去掉,结果总括查询时间如下
0.20s
21.31s
0.25s
0.43s

------ ------ ------ -------

laserhe帮助剖判难题总括

  |  1 | PRIMARY     | <derived2> | system | NULL              | NULL    |

复制代码 代码如下:

NULL    | NULL |    1 |       |

select a.ruid,b.ruid
from( select distinct RUID
from UserMsg
where CreateTime >= '2009-8-14 15:30:00'
and CreateTime<='2009-8-17 16:00:00'
) a left join UserMsg b
on a.ruid = b.ruid
and b.createTime < '2009-8-14 15:30:00'
where b.ruid is null;

  |  2 | DERIVED     | <derived3> | system | NULL              | NULL    |

实行时间0.13s
---- ------------- ------------ ------- ----------------- ------------ --------- -------- ------ --------------------------

NULL    | NULL |    1 |       |

----
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

  |  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4  

|
---- ------------- ------------ ------- ----------------- ------------ --------- -------- ------ --------------------------

    |      |    1 |       |

----
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1248 |

   ---- ------------- ------------ -------- ------------------- --------- ---

|
| 1 | PRIMARY | b | ref | RUID,CreateTime | RUID | 96 | a.RUID | 2 | Using where; Not exists

------ ------ ------ -------

|
| 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3553 | Using where; Using

  很醒目那条SQL是从里向外的施行,正是从id=3 向上试行.

temporary |
---- ------------- ------------ ------- ----------------- ------------ --------- -------- ------ --------------------------

  2. select_type

----
试行功用类似与not in的频率

  正是select类型,能够有以下三种

数据库优化的主导条件:让笛Carl积发生在玩命小的集结之间,mysql在join的时候能够平昔通过索引来围观,而放置到子查询里头,查询规

  (1) SIMPLE

划器就不知情用十分的目录了。
多个SQL在数据库里是这么优化的:首先SQL会剖析成一批剖判树,一个树状数据结构,然后在这几个数据结构里,查询规划器会查找有未有方便

  轻巧SELECT(不选择UNION或子查询等) 比如:

的目录,然后依据具体意况做三个排列组合,然后总括这几个排列组合中的每一样的开销(类似explain的出口的管理器可读版本),然后比较里

  mysql> explain select * from t3 where id=3952602;

面开支一点都不大的,选择并实践之。那么:
explain select a.ruid,b.ruid from(select distinct RUID from UserMsg where CreateTime >= '2009-8-14 15:30:00'

   ---- ------------- ------- ------- ------------------- --------- ---------

and CreateTime<='2009-8-17 16:00:00' ) a left join UserMsg b on a.ruid = b.ruid and b.createTime < '2009-8-14 15:30:00'

------- ------ -------

where b.ruid is null;

explain select add_tb.RUID
-> from (select distinct RUID
-> from UserMsg
-> where CreateTime>'2009-8-14 15:30:00'
-> and CreateTime<='2009-8-17 16:00:00'
-> ) add_tb
-> where add_tb.RUID
-> not in (select distinct RUID
-> from UserMsg
-> where CreateTime<'2009-8-14 15:30:00'
-> );
explain
---- -------------------- ------------ ---------------- ----------------- ------------ --------- ------ ------ ------------

  | id | select_type | table | type  | possible_keys     | key     | key_len |

------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

ref   | rows | Extra |

|
---- -------------------- ------------ ---------------- ----------------- ------------ --------- ------ ------ ------------

   ---- ------------- ------- ------- ------------------- --------- ---------

------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1248 | Using where

------- ------ -------

|
| 3 | DEPENDENT SUBQUERY | UserMsg | index_subquery | RUID,CreateTime | RUID | 96 | func | 2 | Using index;

  |  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       |

Using where |
| 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3509 | Using where;

const |    1 |       |

Using temporary |
---- -------------------- ------------ ---------------- ----------------- ------------ --------- ------ ------ ------------

   ---- ------------- ------- ------- ------------------- --------- ---------

------------------
支付是截然一致的,费用能够从 rows 那多少个字段得出(基本上是rows那么些字段种种行的数值的乘积,约等于笛Carl积)
只是呢:下边那个:
explain select a.ruid,b.ruid from(select distinct RUID from UserMsg where CreateTime >= '2009-8-14 15:30:00'

------- ------ -------

and CreateTime<='2009-8-17 16:00:00' ) a left join ( select distinct RUID from UserMsg where createTime < '2009-8-14

  (2). PRIMARY

15:30:00' ) b on a.ruid = b.ruid where b.ruid is null;
试行时间21.31s
---- ------------- ------------ ------- --------------- ------------ --------- ------ ------- -----------------------------

  笔者的敞亮是最外层的select.比方:

-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

  mysql> explain select * from (select * from t3 where id=3952602) a ;

|
---- ------------- ------------ ------- --------------- ------------ --------- ------ ------- -----------------------------

   ---- ------------- ------------ -------- ------------------- --------- ---

-
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1248 |

------ ------ ------ -------

|
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 30308 | Using where; Not exists

  | id | select_type | table      | type   | possible_keys     | key     |

|
| 3 | DERIVED | UserMsg | ALL | CreateTime | NULL | NULL | NULL | 69366 | Using where; Using temporary

key_len | ref  | rows | Extra |

|
| 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3510 | Using where; Using temporary

   ---- ------------- ------------ -------- ------------------- --------- ---

|
---- ------------- ------------ ------- --------------- ------------ --------- ------ ------- -----------------------------

------ ------ ------ -------

-
自身就多少不晓得
干什么是四行
还要中间两行巨大无比
按理说
查询规划器应该能把这一个查询优化得眼前边的七个同样的
(至少在本身熟习的pgsql数据Curry本人有信念是均等的)
但mysql里头不是
所以笔者以为查询规划器里头恐怕还是糙了点
本身日前说过优化的主干尺度正是,让笛Carl积发生在玩命小的成团之间
那么地点最终一种写法至少未有背离那几个规范
就算b 表因为符合条件的要命多,基本上不会用索引
而是并不该妨碍查询优化器看到外面的join on条件,进而和近年来多少个SQL同样,选择主键进行join
可是本身前面说过查询规划器的效应
力排众议上来说
遍历一回全体望,总计一下支出
是客观的
作者备感这里最终一种写法未有遍历完整全数比相当大希望
唯恐的源委是子查询的落到实处依旧比较简单?
子查询对数据库的确是个挑衅
因为基本都以递归的事物
就此在这么些环节有一点毛病并不奇异
实在你留神情考,最后一种写法独有是我们先是种写法的叁个变种,关键在表b的where 条件放在哪里
座落中间,就不会用索引去join
位于外边就能够
本条小编正是排列组合的二个可能

  |  1 | PRIMARY     | <derived2> | system | NULL              | NULL    |

IN、JOIN、IS NULL、NOT EXISTS成效相比语句一:select count(*) from A where A.a not in (select a from B) 语句二:select count(*) from A left join B on A.a = B.a w...

NULL    | NULL |    1 |       |

  |  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4  

    |      |    1 |       |

   ---- ------------- ------------ -------- ------------------- --------- ---

------ ------ ------ -------

  (3).UNION

  UNION中的第一个或前面包车型客车SELECT语句.举个例子

  mysql> explain select * from t3 where id=3952602 union all select * from t3

;

   ---- -------------- ------------ ------- ------------------- --------- ---

------ ------- ------ -------

  | id | select_type  | table      | type  | possible_keys     | key     |

key_len | ref   | rows | Extra |

   ---- -------------- ------------ ------- ------------------- --------- ---

------ ------- ------ -------

  |  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY | 4  

    | const |    1 |       |

  |  2 | UNION        | t3         | ALL   | NULL              | NULL    |

NULL    | NULL  | 1000 |       |

  |NULL | UNION RESULT | <union1,2> | ALL   | NULL              | NULL    |

NULL    | NULL  | NULL |       |

   ---- -------------- ------------ ------- ------------------- --------- ---

------ ------- ------ -------

  (4).DEPENDENT UNION

  UNION中的第三个或前边的SELECT语句,取决于外面包车型大巴查询

  mysql> explain select * from t3 where id in (select id from t3 where

id=3952602 union all select id from t3)  ;

   ---- -------------------- ------------ -------- ------------------- ------

--- --------- ------- ------ --------------------------

  | id | select_type        | table      | type   | possible_keys     | key   

 | key_len | ref   | rows | Extra                    |

   ---- -------------------- ------------ -------- ------------------- ------

--- --------- ------- ------ --------------------------

  |  1 | PRIMARY            | t3         | ALL    | NULL              | NULL  

 | NULL    | NULL  | 1000 | Using where              |

  |  2 | DEPENDENT SUBQUERY | t3         | const  | PRIMARY,idx_t3_id |

PRIMARY | 4       | const |    1 | Using index              |

  |  3 | DEPENDENT UNION    | t3         | eq_ref | PRIMARY,idx_t3_id |

PRIMARY | 4       | func  |    1 | Using where; Using index |

  |NULL | UNION RESULT       | <union2,3> | ALL    | NULL              | NULL 

  | NULL    | NULL  | NULL |                          |

   ---- -------------------- ------------ -------- ------------------- ------

--- --------- ------- ------ --------------------------

sexplain的语法与用法 explain [extended] select from where 假使应用了extended,那么在实施完explain语句后,可以利用show warnings语句询问相应...

本文由澳门新萄京发布于数据库,转载请注明出处:sexplain的语法与用法,一条慢查询sql的的分析

上一篇:MySQL单向同步实现,不同服务器上的mysql数据库如 下一篇:没有了
猜你喜欢
热门排行
精彩图文