?

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)

?

?

目前 数据库中数据大概有2000W条，之后可能还会不断增长，现在想要查询的是：从表中找出相同姓名，相同规则ID，并且status = 1对应的最后一条记录

?

写个程序随机加入数据

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($i%10000 == 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 ? ? | | 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 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+