mysql可以使用row constructor来做compound in 查询:select * from t where (col1,col2) in ((colt1,colt2),(colt11,colt22)).这种查询在批量查询的时候就会显得比较方便:业务上可以批量操作,提高效率。但是explain之后却发现,这种sql却使用了全表扫描。
首先看一下mysql是怎么处理in查询的:mysql处理 in(value list)查询时会对value list做一次quick-sort,但是如果value list里的元素是tuple就无法进行排序(按哪个col排序)。bug#16081对value list中只有一个tuple情况,做了优化:将select * from t where (col1,col2) in ((colt1,colt2))转换为select * from t where col1=colt1 and col2=colt2,explain的结果也也可以看到:(count(*)是可以使用到覆盖索引的)
mysql> show create table t7\G *************************** 1. row *************************** Table: t7 Create Table: CREATE TABLE `t7` ( `a` int(11) DEFAULT NULL, `b` varchar(20) DEFAULT NULL, `c` varchar(20) DEFAULT 'ccccc', KEY `idx_a_b` (`a`,`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from t7 where (a,b) in ((1,'kangaroo'),(2,'dolphin')); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t7 | ALL | NULL | NULL | NULL | NULL | 16 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from t7 where (a,b) in ((1,'kangaroo')); +----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+ | 1 | SIMPLE | t7 | ref | idx_a_b | idx_a_b | 68 | const,const | 4 | Using where | +----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from t7 where a=1 and b='kangaroo'; +----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+ | 1 | SIMPLE | t7 | ref | idx_a_b | idx_a_b | 68 | const,const | 4 | Using where | +----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from t7 where (a=1 and b='kangaroo') or (a=2 and b='dolphin'); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t7 | ALL | idx_a_b | NULL | NULL | NULL | 16 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select count(*) from t7 where (a,b) in ((1,'kangaroo'),(2,'dolphin')); +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | t7 | index | NULL | idx_a_b | 68 | NULL | 16 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
在这种需求下,如果一定要做批量查询,那就只能改成select * from t where (col1=colt1 and col2=colt2) or (col1=colt11 and col2=colt2).
参考链接:
http://www.facebook.com/note.php?note_id=243134480932
http://dev.mysql.com/doc/refman/5.5/en/row-subqueries.html
http://bugs.mysql.com/bug.php?id=16081
http://bugs.mysql.com/bug.php?id=31188
http://bugs.mysql.com/bug.php?id=35819
http://bugs.mysql.com/bug.php?id=16247
http://www.mysqlperformanceblog.com/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_in
相关推荐
The Investigations of the Compound Lens in Optimizing the Irradiation Uniformity in Longitudinally Pump,李汉明,李英骏,无摘要
复合文件结构查看工具的代码,内含复合文件格式定义。
Persymmetric adaptive detection of distributed targets in compound-Gaussian sea clutter with Gamma texture
可以用于复杂碰撞边缘的生成,更加精确。
compound words
Soft compound
“too many terms in compound SELECT” 去Stackoverflow上查了一下,发现有人回答这个问题:链接 原来一次性向数据库里插入数据的条数不能太多,上限是500条。超出会报错。 解决方案就是只好分多次插入数据库了。 ...
This powerful, practical book, based on years of proven and profi table experience, shows ... The Compound Effect is a treasure chest of ideas for achieving greater success than you ever thought possible
复合文档豪华版,支持复杂路径解析,多流打开的compound file,安全高效地检索复合文档中的流。支持路径名打开,如绝对路径 \Video\Data\Video.mp4 相对路径 ..\GUID\GUID.txt 当前路径 .\GUID.txt
具有随机收入和周期分红策略的复合泊松风险模型,王乾乾,刘朝林,本文对具有随机收入和周期分红策略的复合泊松风险模型进行了研究。通常,在分红决策时间序列中,保险公司通过分析盈余水平来决定红�
32Compound Option V3.xls
人参皂苷在不同溶剂中的溶解度校正和预测,李润妍,闫浩,采用静态分析法对人参皂苷在纯溶剂和二元混合溶剂的溶解度进行了测定。利用van't Hoff方程,改良的Apelblat方程,λh (Buchowski) 方程,Wilso
The two-dimensional (2D) compound parabolic concentrator's (CPC) characteristics are analyzed. It is shown that CPC's height is taller and its light collecting ability is stronger with the CPC's field...
// in Node.js const cUsdtAddress = Compound . util . getAddress ( Compound . cUSDT ) ; ( async function ( ) { let supplyRatePerBlock = await Compound . eth . read ( cUsdtAddress , 'function ...
DC motor construction working principal types of dc motor series shunt compound
复合几何卷积的完全单调性及其在风险理论中的应用,CHIU Sung-Nok,尹传存,本文证明了完全单调性在混合几何复合下是保持的,并证明了如果Sparre Andersen 模型的索赔分布具有完全单调的密度,则其破产概率,破产时�
其它源代码:compound_file 关键字:compound_file,其它
付红利的复合二项模型的某些破产量的显式表示,谭激扬,杨向群,研究付红利的带随机决策的复合二项模型,见谭和杨的论文2006b。当盈余大于或等于一个非负的整数x时,保险公司以概率q0付红利1. 我们�
The SegmentMerger class combines two or more Segments, represented by an IndexReader ({@link #add}, ... If the compoundFile flag is set, then the segments will be merged into a compound file for Linux.
discovery.zip,发现服务器发现服务器