查询语句的优化

Question

测试中有100万条数据, 就一个表:create table user
       (idx int ,
        id varchar(63) not null,
        passwd varchar(63) not null, primary key(idx),
unique(id));测试遍历查询一次所有的数据(据 idx 来查), 如 select * from user where idx = 2; 现在所需时间是 120 秒左右. 为什么时间会这么长呢? 我看了下, 数据库的大小只有 50M 左右. 我尝试把 user 表分成 10 个,20 个表, (据 idx % 10, idx % 20 来分), 发现几乎没什么效果. 还有其它优化方法不?

Answer 1

alter table user modify idx int not null primary key然后贴出explain select * from user where idx=2;
的结果。

Answer 2

idx 已经是 primary key 了呀:mysql> desc user0
    -> ;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| idx    | int(11)     | NO   | PRI | 0       |       | 
| id     | varchar(63) | NO   | UNI |         |       | 
| passwd | varchar(63) | NO   |     |         |       | 
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)所以那条 alter 语句会出错, 说定义多个主键.mysql> explain select * from user0 where idx=0;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user0 | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)mysql> 

Answer 3

explain   select   *   from   user0   where   idx=0; 
这条数据执行要120秒？看你explain的结果可能0.00sec就查出来了。只检索了一行啊。

Answer 4

看我第一贴: "测试遍历查询一次所有的数据".即 1000000 条 select.

Answer 5

有索引的话根本就不会检索全表的，你explain出来的结果的rows说明它只查一行就可以得出结果。

Answer 6

如国你的where的条件是没有索引的password，那个explain的rows会显示1000000多的。

Answer 7

索引应该是最快的了，我觉得像select * from user0 where idx=0;   
不用执行那么久吧。如果要那么久还有人用mysql？一百多万数据很多系统都有的。
你的explain结果，是最优的查询了，type也是const的。

Answer 8

我的实际需求是这样子的:for(...)
  if(!exists()) // select
    insert();要求让这个循环尽可能快的结束. 看来 exists() 那边好像是没什么方法优化了. 如果考虑 insert 语句,将它先写到文件里, 最后一块从文件从入到数据库, 效果应该不错. 只是有一个问题就是; 如何保证 exists() 调用的时候, 除了查数据库名, 还得查文件 cache. 看起来有些吃力不讨好? 有什么好的建议不?

Answer 9

你直接在mysql只执行一条select   *   from   user   where   idx   =   2的时间是多久啊？就是你说的120多秒，不可能吧？说清楚点。

Answer 10

lz，要是是因为你程序的循环使得执行速度慢，那是你程序的问题，和sql的优化一点关系都没有。你优化的应该是程序吧，随便执行个要有100万次的查询都要百秒以上啦。你有几十万记录录入？你直接在mysql只执行一条select       *       from       user       where       idx       =       2的时间是多久啊？ 
你的需求我实在搞不懂。

Answer 11

XFliangwh: 呵呵. 单条语句 select 很快的, mysql 里显示是 0.00 秒. 从我的程序里算大约是 0.0013 秒.这个需求别人跟我提的, 跟他摊牌了, 就这样子好了. :P