在mysql中使用元组比较是否有效? [英] Using tuple comparison in mysql is it efficient?

问题描述

我有一张桌子:

CREATE TABLE `books` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nameOfBook` VARCHAR(32),
    `releaseDate` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `Index 2` (`releaseDate`, `id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB

AUTO_INCREMENT = 33029692;

AUTO_INCREMENT=33029692;

我比较了两个SQL请求，以对releaseDate进行排序.这两个请求都返回相同的结果.

I compared two SQL requests to do a pagiation with sort on releaseDate. Both of theses request return the same result.

(简单的一个)

select SQL_NO_CACHE  id,name, releaseDate  
from books  
where releaseDate <= '2016-11-07'  
AND (releaseDate<'2016-11-07' OR id <    3338191)  
ORDER  by releaseDate DESC, id DESC limit 50;

和

(元组比较或行比较)

select SQL_NO_CACHE  id,name, releaseDate 
from books 
where (releaseDate ,id) < ('2016-11-07',3338191) 
ORDER  by releaseDate DESC, id DESC limit 50;

我在解释请求时得到了

简单的一个:

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"books";"range";"PRIMARY,Index 2";"Index 2";"9";"";"1015876";"Using where; Using index"

我们可以看到它正在解析"1015876"行

We can see it is parsing "1015876" of rows

元组比较的说明:

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"books";"index";"";"Index 2";"13";"";"50";"Using where; Using index"

我们可以看到它正在解析"50"行.

We can see it is parsing "50" of rows.

但是，如果我检查执行时间，那是简单的时间:

But if I checked the exectution time the simple one :

/* Affected rows: 0  Lignes trouvées: 50  Avertissements: 0  Durée pour 1 query: 0,031 sec. */

和元组一个:

/* Affected rows: 0  Lignes trouvées: 50  Avertissements: 0  Durée pour 1 query: 3,682 sec. */

我不理解为什么根据说明，元组比较更好，但是执行时间却很差?

I don't understant why according to the explain the tuple comparison is better but the execution time is badly worse?

推荐答案

多年来，我对此一直感到恼火.尽管WHERE (a,b) > (1,2)很容易转化为其他配方，但从未进行过优化.甚至几年前，其他格式的优化效果也很差.

I've been irritated by this for years. WHERE (a,b) > (1,2) has never been optimized, in spite of it being easily transformed into the other formulation. Even the other format was poorly optimized until a few years ago.

使用EXPLAIN FORMAT=JSON SELECT ...可能会为您提供一些更好的线索.

Using EXPLAIN FORMAT=JSON SELECT ... might give you some better clues.

同时，EXPLAIN忽略了LIMIT并建议使用1015876.在许多情况下，EXPLAIN提供了不错的"行估计，但没有一个.

Meanwhile, EXPLAIN ignored the LIMIT and suggested 1015876. On many cases, EXPLAIN provides a "decent" Row estimate, but not either of these.

随时提交错误报告: http://bugs.mysql.com (并发布链接到这里).

Feel free to file a bug report: http://bugs.mysql.com (and post the link here).

尽管OR在历史上是不可优化的，但最近对另一种配方进行了优化.

Another formulation was recently optimized, in spite of OR being historically un-optimizable.

where  releaseDate <  '2016-11-07'  
   OR (releaseDate  = '2016-11-07' AND id < 3338191)  

对于衡量查询优化，我喜欢这样做:

For measuring query optimizations, I like to do:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

较小的值(例如您的情况为"50")表示优化效果良好；较大的值(1M)表示已扫描.处理程序编号是准确的；与EXPLAIN中的 estimates 不同.

Small values, such as '50' for your case, indicate good optimization; large value (1M) indicate a scan. The Handler numbers are exact; unlike the estimates in EXPLAIN.

更新 5.7.3 改进了对元组(也称为行构造器")的处理

Update 5.7.3 has improved handling of tuples, aka "row constructors"

这篇关于在mysql中使用元组比较是否有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！