Chinaunix

标题: 请教mysql查询时间十几秒的优化 [打印本页]

作者: mmxcq 时间: 2010-03-06 18:26
标题: 请教mysql查询时间十几秒的优化
现在情况是这样
业务表有a,b两个表
a表中有一千多万数据
b表有接近三千万数据

为了简单我就写关键的字段

a表有如下字段
id int 主键
tid int 索引

b表中有如下字段
id int主键

select a.id,b.id from a,b where a.id=b.id and a.tid in(1,3,4);
mysql查询要十几秒

大家帮忙看看我这个该怎么优化啊谢谢

作者: Coolriver 时间: 2010-03-06 19:34
请提供一下：
select version();

explain select a.id,b.id from a,b where a.id=b.id and a.tid in(1,3,4);

表类型？

作者: cenalulu 时间: 2010-03-06 21:36
提供explain结果，还有符合a.tid in(1,3,4)一共有多少行

作者: liyihongcug 时间: 2010-03-07 09:56
vd add index in each table

作者: mmxcq 时间: 2010-03-08 09:30
楼上的各位朋友不好意思  星期天没有上网所以没有回复
mysql> show create table p1;
+-------+------------------------------------------------------------------
---------------------------------------------------------------------------
----------------------------+
| Table | Create Table

                        |
+-------+------------------------------------------------------------------
---------------------------------------------------------------------------
----------------------------+
| p1 | CREATE TABLE `p1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11167266 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------
---------------------------------------------------------------------------
----------------------------+
1 row in set (0.00 sec)

mysql> show create table p1gl;
+-------+------------------------------------------------------------------
---------------------------------------------------------------------------
-------------------------------+
| Table | Create Table

                           |
+-------+------------------------------------------------------------------
---------------------------------------------------------------------------
-------------------------------+
| p1gl  | CREATE TABLE `p1gl` (
  `pid` int(11) NOT NULL,
  `tid` int(11) NOT NULL,
  KEY `pid` (`pid`),
  KEY `tid` (`tid`),
  KEY `pid_2` (`pid`,`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------
---------------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from `p1`,`p1gl` where `p1`.`id`=`p1gl`.`pid` and
`p1gl`.tid in(1,2,3);
+----+-------------+-------+--------+---------------+---------+---------+-------
--------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref
      | rows    | Extra                   |
+----+-------------+-------+--------+---------------+---------+---------+-------
--------+----------+--------------------------+
|  1 | SIMPLE    | p1gl  | index  | pid,tid,pid_2 | pid_2 | 8    | NULL
      | 11167260 | Using where; Using index |
|  1 | SIMPLE    | p1 | eq_ref | PRIMARY    | PRIMARY | 4    | test.p
1gl.pid |       1 | Using index             |
+----+-------------+-------+--------+---------------+---------+---------+-------
--------+----------+--------------------------+
2 rows in set (0.00 sec)
mysql> explain select count(*) from `p1`,`p1gl` where `p1`.`id`=`p1gl`.`pid` and
`p1gl`.tid in(1,2,3)\G;
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: p1gl
      type: index
possible_keys: pid,tid,pid_2
      key: pid_2
   key_len: 8
      ref: NULL
      rows: 11167260
      Extra: Using where; Using index
*************************** 2. row ***************************
         id: 1
  select_type: SIMPLE
      table: p1
      type: eq_ref
possible_keys: PRIMARY
      key: PRIMARY
   key_len: 4
      ref: test.p1gl.pid
      rows: 1
      Extra: Using index
2 rows in set (0.00 sec)

mysql> select count(*) from `p1`,`p1gl` where `p1`.`id`=`p1gl`.`pid` and `p1gl`.
tid in(1,2,3);
+----------+
| count(*) |
+----------+
|  3350466 |
+----------+
1 row in set (15.14 sec)

Server version: 5.1.40-community MySQL Community Server (GPL)

这个就是我的大概情况请问各位朋友这个该怎么优化啊  执行个查询要几十秒

作者: Coolriver 时间: 2010-03-08 12:48
应该属于结果集太大了。
加一个Limit限制一下结果集。

作者: cenalulu 时间: 2010-03-08 13:10
语句没问题，索引也没问题。可以从业务角度去做优化。

从你的表结构来猜测，tid就是type-id的含义吧？
假如说你的目的是想取出：某几个类型的用户的总量。
可以把tid直接做到p1 这张表里面，并且做一个 (tid,id) 的复合索引。这样避免表链接，会有很大性能上的提升。

作者: mmxcq 时间: 2010-03-08 16:49

应该属于结果集太大了。
加一个Limit限制一下结果集。
Coolriver 发表于 2010-03-08 12:48

能否举个例子？谢谢

作者: mmxcq 时间: 2010-03-08 16:51

语句没问题，索引也没问题。可以从业务角度去做优化。

从你的表结构来猜测，tid就是type-id的含义吧？
...
cenalulu 发表于 2010-03-08 13:10

关键是一个pid可能会对应n个tid 所以单表不行除非用字符串可以用单表不过应该性能很弱的
所以想请教各位除了分区分表还有什么sql查询能很快的查询出来

作者: taluyi 时间: 2010-03-08 17:22
回复 11# mmxcq

select a.id,b.id from a,b where  a.id <1000000  and  a.id=b.id and a.tid in(1,3,4);
select a.id,b.id from a,b where 1000000 <a.id <2000000  and  a.id=b.id and a.tid in(1,3,4);
...  就是把一个查询分开了，呵呵  不过如果数据库的表在创建时就分表的话就更好了

作者: mmxcq 时间: 2010-03-08 23:50

回复 mmxcq

select a.id,b.id from a,b where a.id
taluyi 发表于 2010-03-08 17:22

然后取这两个查询的合集？明天回公司去做个测试啊看看查询是否变快

作者: mmxcq 时间: 2010-03-09 09:29

回复 mmxcq

select a.id,b.id from a,b where a.id
taluyi 发表于 2010-03-08 17:22

mysql> select count(*) from p1,p1gl where  p1.id <1000000  and  p1.id=p1gl.pid a
nd p1gl.tid in(1,3,4);
+----------+
| count(*) |
+----------+
| 300044 |
+----------+
1 row in set (1.33 sec)

mysql> select  count(*) from p1,p1gl where 1000000 <p1.id <2000000  and  p1.id
=p1gl.pid and p1gl.tid in(1,3,4);
+----------+
| count(*) |
+----------+
|  3350340 |
+----------+
1 row in set (27.61 sec)

mysql> select  count(*) from p1,p1gl where p1.id>1000000 and  p1.id=p1gl.pi
d and p1gl.tid in(1,3,4);
+----------+
| count(*) |
+----------+
|  3050295 |
+----------+
1 row in set (13.33 sec)

mysql>

查询分开了查然后合并结果集也差不多哦  好像总体时间还慢些

作者: mmxcq 时间: 2010-03-09 14:35
各位大大们帮个忙啊

作者: james.liu 时间: 2010-03-09 17:18
加个表

int id, int tid

这个id 是满足a.id=b.id，当这两者相等的情况下才插入这个表。

id 做主键索引就够了把。

作者: mmxcq 时间: 2010-03-10 00:17

加个表

int id, int tid

这个id 是满足a.id=b.id，当这两者相等的情况下才插入这个表。

id 做主键 ...
james.liu 发表于 2010-03-09 17:18

典型的空间换时间？
然后a表直接和这个新表连接查询得结果

作者: mmxcq 时间: 2010-03-10 13:02
期待各位高手帮忙优化一下sql查询（在不分表和不分区的情况下）

作者: mmxcq 时间: 2010-03-10 13:03
感谢各位了帮忙看看啊帮忙分析一下该怎么优化这个查询执行才能变短啊

作者: mmxcq 时间: 2010-03-17 00:10
感谢各位了帮忙看看啊

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)