请教mysql查询时间十几秒的优化

mmxcq

现在情况是这样
业务表有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

请提供一下：
select version();

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

表类型？

cenalulu

提供explain结果，还有符合a.tid in(1,3,4)一共有多少行

liyihongcug

vd  add index in each table

mmxcq

楼上的各位朋友 不好意思  星期天没有上网 所以没有回复
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

应该属于结果集太大了。
加一个Limit限制一下结果集。

cenalulu

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

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

mmxcq

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

能否举个例子？ 谢谢

mmxcq

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

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

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

taluyi

回复 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);
...  就是把一个查询分开了，呵呵  不过如果数据库的表在创建时就分表的话就更好了