mysql in 查询优化

2014年11月29日21:01:01

场景：有的时候查询数据库的select in 语句中会有非常多不连续的数值，会很影响查询效率

方法：将select in 查询转换成多个select between and 语句

代码：核心代码(id要排过顺序)

 1     public function group()
 2     {
 3         // $a = array(1,2,3,5,7,8,9);
 4         $a = array(1,3,4,5,7,8,9);
 5         $len = count($a);
 6         $cur = 0; //当前遍历元素的下标
 7         $pre = $a[0]; //前一个元素的值
 8 
 9         $new = array('0' => array($a[0]));
10         for ($i = 1; $i < $len; $i++) {
11             if (($a[$i] - $pre) == 1 ) {
12                 $new[$cur][] = $a[$i];
13             } else {
14                 $cur = $i;
15                 $new[$cur][] = $a[$i];
16             }
17             $pre = $a[$i];
18         }
19 
20         print_r($new);
21     }

//结果

 1 Array
 2 (
 3     [0] => Array
 4         (
 5             [0] => 1
 6         )
 7 
 8     [1] => Array
 9         (
10             [0] => 3
11             [1] => 4
12             [2] => 5
13         )
14 
15     [4] => Array
16         (
17             [0] => 7
18             [1] => 8
19             [2] => 9
20         )
21 
22 )

 完整代码:

 1 //select in
 2     //arrData 整数数组，最好是整数
 3     public function select_in($key, $arrData, $fields='')
 4     {
 5         $fields = $fields ? $fields : '*';
 6         sort($arrData);
 7         $len = count($arrData);
 8         $cur = 0;
 9         $pre = $arrData[0];
10 
11         $new = array('0' => array($arrData[0]));
12         for ($i = 1; $i < $len; $i++) {
13             if (($arrData[$i] - $pre) == 1 ) {
14                 $new[$cur][] = $arrData[$i];
15             } else {
16                 $cur = $i;
17                 $new[$cur][] = $arrData[$i];
18             }
19             $pre = $arrData[$i];
20         }
21 
22         $arrSql = array();
23         foreach ($new as $v) {
24             $len = count($v) - 1;
25             if ($len) {
26                 $s = $v[0];
27                 $e = end($v);
28                 $sql = "(select $fields from {$this->_dt} where $key between $s and $e)";
29             } else {
30                 $s = $v[0];
31                 $sql = "(select $fields from {$this->_dt} where $key = $s)";
32             }
33 
34             $arrSql[] = $sql;
35         }
36         
37         $strUnion = implode(' UNION ALL ', $arrSql);
38         $res = $this->query($strUnion);
39         return $this->rstoarray($res);
40     }

 

Finger PHP 框架

posted @ 2014-11-29 21:06  myD  阅读(3980)  评论(0)  编辑  收藏  举报