mysql 求一条高效SQl

Question

MYsqlA表（主表）
ID clm1 clm2 date
1  A    A    2012-01-01
2  B    B    2012-01-01
3  C    C    2012-01-02
4  D    D    2012-01-03B
ID A_ID   day          com1  com2
1   1    20120101       c1    c2
2   1    20120102       d1    d2
3   1    20120105       e1    e2
4   2    20120108       f1    f2
5   2    20120107       g1    g2
6   3    20120101       h1    h2
7   3    20120102       i1    i2A表 ID是主键 date是索引
B表 ID是主键
A的ID和B表的A_ID是关联键
我想得到的结果
A.ID A.clm1 A.clm2  B.ID     B.day   B.com1  B.com2
1     A      A      3        20120105 e1      e2
2     B      B      4        20120108 f1      f2
3     C      C      7        20120102 i1      i2
4     D      D      NULL       NULL   NUll    NULL
就是取A表和B表中day最大的数据和A表联起来。请高手写一个效率最高的SQL。因为我的数据库用两个表有百万的数据。谢谢

Answer 1

select * from a left join 
(select * from b b1 where not exists(select 1 from b where b1.A_ID=A_ID and b1.day<day)) b2
on a.id=b2.A_ID

Answer 2

SELECT A.ID,A.clm1, A.clm2,B.ID,max(B.day),B.com1,B.com2
from A表,B表
where A.ID=B.A_ID
group by A.ID,A.clm1,A.clm2,B.ID,B.com1,B.com2
或
 SELECT A.ID,A.clm1, A.clm2,B.ID,B.day,B.com1,B.com2
from A表 A,
(select B.ID,MAX(B.day),B.com1,B.com2 from B表 group by B.ID,B.com1,B.com2)B
WHERE A.ID=B.A_ID

Answer 3

哥，我弱弱的问一句，你这个GroupBy后面跟了一堆字段，效率还能高吗？

Answer 4

select A.ID,A.clm1,A.clm2,B.ID,B.day,B.com1,B.com2
from A left join (select * from B t where not exists (select 1 from B where A_ID=t.A_ID and ID>t.ID)) x on A.id=x.A_ID

Answer 5

ID>t.ID这个写法不对吧，应该是日期，这个是一楼的翻版吧。呵呵。
不过还是很慢的，现在我的测试数据，A表12W，B表9W，查询时间40夺秒。。

Answer 6

索引建立 没有？A_ID 、day

Answer 7

上真实数据。
业务表和例子表的说明：TAcceptDataMst -》A表 主表
AcceptId -》ID
AcceptDate -》date部分字段+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| AcceptId         | int(11)      | NO   | PRI | NULL    | auto_increment |
| AcceptDate       | datetime     | YES  | MUL | NULL    |                |
| AcceptUserId     | int(11)      | YES  |     | NULL    |                |
| WorkDate         | date         | YES  | MUL | NULL    |                |
| WorkTime_F       | varchar(5)   | YES  |     | NULL    |                |
| WorkTime_T       | varchar(5)   | YES  |     | NULL    |                |
| Customer_K_Sei   | varchar(50)  | YES  |     | NULL    |                |
| Customer_K_Mei   | varchar(50)  | YES  |     | NULL    |                |
| Customer_F_Sei   | varchar(50)  | YES  | MUL | NULL    |                |
| Customer_F_Mei   | varchar(50)  | YES  | MUL | NULL    |                |
| CustCompany      | varchar(100) | YES  |     | NULL    |                |
| TelNo1           | varchar(16)  | YES  | MUL | NULL    |                |
| TelNo2           | varchar(16)  | YES  |     | NULL    |                |
| Called           | int(1)       | YES  |     | 0       |                |
| NoTel            | int(1)       | YES  |     | 0       |                |
| TelBefore        | int(11)      | YES  |     | 0       |                |
| TelArriveBefore  | int(1)       | YES  |     | 0       |                |
| ZipCode          | varchar(8)   | YES  |     | NULL    |                |
| Pref             | varchar(20)  | YES  |     | NULL    |                |
| City             | varchar(100) | YES  |     | NULL    |                |
| City_F           | varchar(200) | YES  |     | NULL    |                |
| Street           | varchar(200) | YES  |     | NULL    |                |
| Street_F         | varchar(200) | YES  |     | NULL    |                |
| Address          | varchar(50)  | YES  |     | NULL    |                |
| BuildName        | varchar(50)  | YES  |     | NULL    |                |
| InsertDate       | datetime     | NO   |     | NULL    |                |
| UpdateDate       | datetime     | NO   |     | NULL    |                |
| SakuseiSyaId     | int(11)      | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+Show Index
+----------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| TAcceptDataMst |          0 | PRIMARY        |            1 | AcceptId       | A         |      167612 | NULL     | NULL   |      | BTREE      |         |               |
| TAcceptDataMst |          1 | AcceptId       |            1 | AcceptId       | A         |      167612 | NULL     | NULL   |      | BTREE      |         |               |
| TAcceptDataMst |          1 | AcceptDate     |            1 | AcceptDate     | A         |      167612 | NULL     | NULL   | YES  | BTREE      |         |               |
| TAcceptDataMst |          1 | WorkDate       |            1 | WorkDate       | A         |         490 | NULL     | NULL   | YES  | BTREE      |         |               |
| TAcceptDataMst |          1 | Customer_F_Sei |            1 | Customer_F_Sei | A         |       15237 | NULL     | NULL   | YES  | BTREE      |         |               |
| TAcceptDataMst |          1 | Customer_F_Mei |            1 | Customer_F_Mei | A         |       13967 | NULL     | NULL   | YES  | BTREE      |         |               |
| TAcceptDataMst |          1 | TelNo1         |            1 | TelNo1         | A         |      167612 | NULL     | NULL   | YES  | BTREE      |         |               |
+----------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+mysql> select count(1) From TAcceptDataMst;
+----------+
| count(1) |
+----------+
|   164142 |
+----------+
TRootBookApp--》B表
BookId--》ID
AcceptId--》A.ID
PlanDate--》day 部分字段mysql> desc TRootBookApp;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| BookId          | int(11)       | NO   | PRI | NULL    | auto_increment |
| AcceptId        | int(11)       | YES  | MUL | 0       |                |
| BookRootId      | int(11)       | YES  | MUL | 0       |                |
| WorkUserId      | int(11)       | YES  |     | 0       |                |
| PlanDate        | date          | YES  | MUL | NULL    |                |
| BoxCnt          | int(11)       | YES  |     | 0       |                |
| BoxGumCnt       | int(11)       | YES  |     | 0       |                |
| WareHouse       | int(11)       | YES  |     | 0       |                |
| ConfirmUserId1  | int(11)       | YES  |     | 0       |                |
| ConfirmUserId2  | int(11)       | YES  |     | 0       |                |
| ConfirmUserId3  | int(11)       | YES  |     | 0       |                |
| ParentBookId    | int(11)       | YES  | MUL | 0       |                |
| UpdateUserId    | int(11)       | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+mysql> show index from TRootBookApp
;
+--------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| TRootBookApp |          0 | PRIMARY      |            1 | BookId       | A         |      115506 | NULL     | NULL   |      | BTREE      |         |               |
| TRootBookApp |          1 | AcceptId     |            1 | AcceptId     | A         |      115506 | NULL     | NULL   | YES  | BTREE      |         |               |
| TRootBookApp |          1 | BookRootId   |            1 | BookRootId   | A         |       57753 | NULL     | NULL   | YES  | BTREE      |         |               |
| TRootBookApp |          1 | PlanDate     |            1 | PlanDate     | A         |         563 | NULL     | NULL   | YES  | BTREE      |         |               |
| TRootBookApp |          1 | ParentBookId |            1 | ParentBookId | A         |       16500 | NULL     | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> select count(1) From TRootBookApp;
+----------+
| count(1) |
+----------+
|   114740 |
+----------+

Answer 8

查询分析如下：子查询分析mysql> EXPLAIN
SELECT
T1.AcceptId
From TRootBookApp T1 
where not EXISTS (SELECT 1 from TRootBookApp T2 where T2.AcceptId = T1.AcceptId And T2.PlanDate < T1.PlanDate);
+----+--------------------+-------+------+-------------------+----------+---------+-----------------+--------+-------------+
| id | select_type        | table | type | possible_keys     | key      | key_len | ref             | rows   | Extra       |
+----+--------------------+-------+------+-------------------+----------+---------+-----------------+--------+-------------+
|  1 | PRIMARY            | T1    | ALL  | NULL              | NULL     | NULL    | NULL            | 115508 | Using where |
|  2 | DEPENDENT SUBQUERY | T2    | ref  | AcceptId,PlanDate | AcceptId | 5       | eco.T1.AcceptId |      1 | Using where |
+----+--------------------+-------+------+-------------------+----------+---------+-----------------+--------+-------------+这个SQL文执行是，（去掉EXPLAIN）花费1.907s 结果96305行 效率应该还不错！最后的SQL文分析mysql> EXPLAIN
SELECT
T.AcceptId
From TAcceptDataMst T
LEFT JOIN (
SELECT
T1.AcceptId
From TRootBookApp T1 
where not EXISTS (SELECT 1 from TRootBookApp T2 where T2.AcceptId = T1.AcceptId And T2.PlanDate < T1.PlanDate)
) Ts
on Ts.AcceptId = T.AcceptId;+----+--------------------+------------+-------+-------------------+----------+---------+-----------------+--------+-------------+
| id | select_type        | table      | type  | possible_keys     | key      | key_len | ref             | rows   | Extra       |
+----+--------------------+------------+-------+-------------------+----------+---------+-----------------+--------+-------------+
|  1 | PRIMARY            | T          | index | NULL              | AcceptId | 4       | NULL            | 167617 | Using index |
|  1 | PRIMARY            | <derived2> | ALL   | NULL              | NULL     | NULL    | NULL            |  96307 |             |
|  2 | DERIVED            | T1         | ALL   | NULL              | NULL     | NULL    | NULL            | 115512 | Using where |
|  3 | DEPENDENT SUBQUERY | T2         | ref   | AcceptId,PlanDate | AcceptId | 5       | eco.T1.AcceptId |      1 | Using where |
+----+--------------------+------------+-------+-------------------+----------+---------+-----------------+--------+-------------+去掉Explan的执行结果为，执行了40多秒后还没有返回结果求高手指点，瓶颈在哪？咋办？谢谢！

Answer 9

TAcceptDataMst有多少记录，LEFT JOIN->INNER JOIN

Answer 10

mysql> select count(1) From TAcceptDataMst;
+----------+
| count(1) |
+----------+
|   164142 |
+----------+
INNER JOIN，在业务上就不对了，TAcceptDataMst是主表。
我大概知道了，慢就慢在子查询上了，Mysql的子查询能力很差，如果我把SQL文该成这样，速度很快。SELECT
T.AcceptId
From TAcceptDataMst T
LEFT JOIN TRootBookApp Tr
on Tr.AcceptId = T.AcceptId
And not EXISTS (SELECT 1 from TRootBookApp  where AcceptId = Tr.AcceptId And PlanDate < Tr.PlanDate)这个和上面的比，把子查询直接改成LEFT JOIN，速度大大提升。。看分析结果
mysql> EXPLAIN
    -> SELECT
T.AcceptId
From TAcceptDataMst T
LEFT JOIN TRootBookApp Tr
on Tr.AcceptId = T.AcceptId
And not EXISTS (SELECT 1 from TRootBookApp  where AcceptId = Tr.AcceptId And PlanDate < Tr.PlanDate);
+----+--------------------+--------------+-------+-------------------+----------+---------+-----------------+--------+-------------+
| id | select_type        | table        | type  | possible_keys     | key      | key_len | ref             | rows   | Extra       |
+----+--------------------+--------------+-------+-------------------+----------+---------+-----------------+--------+-------------+
|  1 | PRIMARY            | T            | index | NULL              | AcceptId | 4       | NULL            | 167659 | Using index |
|  1 | PRIMARY            | Tr           | ref   | AcceptId          | AcceptId | 5       | eco.T.AcceptId  |      1 |             |
|  2 | DEPENDENT SUBQUERY | TRootBookApp | ref   | AcceptId,PlanDate | AcceptId | 5       | eco.Tr.AcceptId |      1 | Using where |
+----+--------------------+--------------+-------+-------------------+----------+---------+-----------------+--------+-------------+呵呵，但现在有个新问题，就是TRootBookApp 表中，如果在一个AcceptID下，有两个日期(PlanDate)一样的数据,只查询not EXISTS (SELECT 1 from TRootBookApp T2 where T2.AcceptId = T1.AcceptId And T2.PlanDate < T1.PlanDate)就会有两条结果，那么整体返回的数据集就不对了，有冗余了，这个问题在解决呀？Distinct只对一个字段起作用，多个字段是就无效了，有没有什么好的解决办法？呵呵

Answer 11

表中是否还有其它唯一标识的字段，如id,如果有，And (T2.PlanDate < T1.PlanDate
or (t2.PlanDate=t1.PlanDate and t2.id>t1.id))