肥宅之家  ⁝︎  登录
MySQL查询索引试验
有一个数据表,列和索引结构是cond(varchar)-val(int)-id(int),在此表中存储数据并进行查询。
我最初设想的查询语法是:
SELECT * FROM `cross` WHERE `cond` IN ([cond1],[cond2],[cond3]) AND `val`<=[val] AND `id`<=[id] ORDER BY `val` DESC, `id` DESC LIMIT [qnty];
后来发现查询结果中存在 Using filesort ,且查询时间长达0.012毫秒。经过一系列排查发现如果让 cond 使用 = 查询,且后面条件句中不同时使用两个大/小于号则全部走索引。
即语法为:
SELECT * FROM `cross` WHERE `cond` = [cond1] AND `val`<=[val] ORDER BY `val` DESC, `id` DESC LIMIT [qnty];
如果val带大/小于号则后面不要接 id 。
SELECT * FROM `cross` WHERE `cond` = [cond1] AND `val`=[val] AND `id`<=[id] ORDER BY `val` DESC, `id` DESC LIMIT [qnty];
再单独查询 val 等于某值且 id 大/小于某值的情况。
然后再查询其他cond的结果,最后使用 UNION ALL 合并所有结果,再进行排序筛选即可。
最终函数如下:
function getcross($cond,$val,$id,$q){
global $pdo;
$needn=$q+1;$needl=$q;$condq=count($cond);
if(!$val){$samen=array();$samel=array();}
else{
$sql='';
if($condq>1){$sql.='SELECT * FROM (';}
$crc=$condq;
foreach($cond as $row){
$crc--;
$sql.='(SELECT * FROM `cross` WHERE `cond`='.$row.' AND `val`='.$val.' '.($id?'AND `id`<='.$id:'').' ORDER BY `val` DESC, `id` DESC LIMIT '.($q+1).')';
if($crc){$sql.=' UNION ALL ';}
}
if($condq>1){
$sql.=') AS `tempn` ORDER BY `val` DESC, `id` DESC LIMIT '.($q+1);
}
$sql.=' ; ';
if($id){
if($condq>1){$sql.='SELECT * FROM (';}
$crc=$condq;
foreach($cond as $row){
$crc--;$sql.='(SELECT * FROM `cross` WHERE `cond`='.$row.' AND `val`='.$val.' AND `id`>'.$id.' ORDER BY `val` ASC, `id` ASC LIMIT '.$q.')';
if($crc){$sql.=' UNION ALL ';}
}
if($condq>1){$sql.=') AS `templ` ORDER BY `val` ASC, `id` ASC LIMIT '.$q;}
}
$sameq=$pdo->query($sql);
$samen=$sameq->fetchAll(PDO::FETCH_ASSOC)?:array();
if($id){$sameq->nextRowset();$samel=$sameq->fetchAll(PDO::FETCH_ASSOC)?:array();}
else{$samel=array();}
}
$needn-=count($samen);$needl-=count($samel);
if(!$needn && !$needl){
$diffn=array();$diffl=array();
}
else{
$sql='';
if($needn){
if($condq>1){$sql.='SELECT * FROM (';}
$crc=$condq;
foreach($cond as $row){
$crc--;
$sql.='(SELECT * FROM `cross` WHERE `cond`='.$row.' '.($val?'AND `val`<'.$val:'').' ORDER BY `val` DESC, `id` DESC LIMIT '.$needn.')';
if($crc){$sql.=' UNION ALL ';}
}
if($condq>1){
$sql.=') AS `tempn` ORDER BY `val` DESC, `id` DESC LIMIT '.$needn;
}
$sql.=' ; ';
}
if($needl && $val){
if($condq>1){$sql.='SELECT * FROM (';}
$crc=$condq;
foreach($cond as $row){
$crc--;$sql.='(SELECT * FROM `cross` WHERE `cond`='.$row.' AND `val`>'.$val.' ORDER BY `val` ASC, `id` ASC LIMIT '.$needl.')';
if($crc){$sql.=' UNION ALL ';}
}
if($condq>1){$sql.=') AS `tempn` ORDER BY `val` ASC, `id` ASC LIMIT '.$needl;
}
}
$diffq=$pdo->query($sql);
$diffn=$needn?($diffq->fetchAll(PDO::FETCH_ASSOC)?:array()):array();
if(!$needl || !$val){$diffl=array();}
else{if($needn){$diffq->nextRowset();}$diffl=$diffq->fetchAll(PDO::FETCH_ASSOC)?:array();}
}
$needn-=count($diffn);$needl-=count($diffl);
$result=array();
$result['main']=array_merge($samen,$diffn);
$result['past']=array_merge($samel,$diffl);
$result['next']=$needn?null:array_pop($result['main']);
$result['last']=($needl==$q)?null:end($result['past']);
return $result;
}
本函数经过10万条数据测试,由最初查询的0.853ms提升到后来的0.002ms。
几个需要注意的问题:
1. 我测试数据库中的 cond 是纯数字,我在查询时 IN (cond1,cond2,cond3) 忘了用引号包裹条件,从而让 cond 被转换为浮点数查询,造成了没有走索引。正确应该使用 IN ("cond1","cond2","cond3")
2. 最早的 ORDER BY 写成了 ORDER BY `val`, `id` DESC ,走索引的排序是 ORDER BY `val` DESC, `id` DESC
C
脑浆物语 霖博客 设计笔记 依萌萌 灰常记忆 乐亦人生 拔剑舞 云破天开 秋日 Yoooo 幻焕 月宅酱 空城 笨猫 云上萝莉 宅区
🍁 秋空动漫社 STATUS TOS