5表联查sql语句优化

Question

本帖最后由 heirychen 于 2010-09-16 20:44:19 编辑

Answer 1

贴出你的 explain select ...
还有所有表的 show index from ...这样别人可以进行分析。

Answer 2

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  b  const  PRIMARY  PRIMARY  32  const  1  Using temporary; Using filesort
1  SIMPLE  w  ALL  NULL  NULL  NULL   NULL  20600   
1  SIMPLE  r  eq_ref  PRIMARY  PRIMARY  4  lgd.w.rooms  1  Using where
1  SIMPLE  c  ALL  NULL  NULL  NULL  NULL  16936   
1  SIMPLE  a  eq_ref  PRIMARY  PRIMARY  4  lgd.w.attendance  1  Using where

Answer 3

id   select_type   table   type         possible_keys     key          key_len         ref             rows      Extra
1    SIMPLE          b     const         PRIMARY         PRIMARY          32          const             1         Using temporary; Using filesort
1    SIMPLE          w     ALL           NULL            NULL            NULL          NULL           20600
1    SIMPLE          r     eq_ref        PRIMARY         PRIMARY          4        lgd.w.rooms          1         Using where
1    SIMPLE          c     ALL           NULL            NULL            NULL          NULL           16936
1    SIMPLE          a     eq_ref        PRIMARY         PRIMARY          4       lgd.w.attendance      1         Using where

Answer 4

explain select r.date, a.code, b.name as bname, r.number, r.master, c.name, c.contact
 from
 baseinfo b inner join attendance a on b.code=a.code
 inner join works w on a.id=w.attendance
 inner join rooms r on w.rooms=r.id
 left join contact c on w.id=c.works
 where r.date between '2010-05-01' and '2010-05-30' and a.code in ('699')
 order by r.date, convert(r.master using gbk), convert(r.number using gbk), a.code+0id   select_type   table   type         possible_keys     key          key_len         ref             rows      Extra
1    SIMPLE          b     const         PRIMARY         PRIMARY          32          const             1         Using temporary; Using filesort
1    SIMPLE          w     ALL           NULL            NULL            NULL          NULL           20600
1    SIMPLE          r     eq_ref        PRIMARY         PRIMARY          4        lgd.w.rooms          1         Using where
1    SIMPLE          c     ALL           NULL            NULL            NULL          NULL           16936
1    SIMPLE          a     eq_ref        PRIMARY         PRIMARY          4       lgd.w.attendance      1         Using whereTable       Non_unique   Key_name   Seq_in_index   Column_name   Collation   Cardinality   Sub_part   Packed   Null   Index_type   Comment
baseinfo      0          PRIMARY        1            code           A           644          NULL     NULL             BTREE
attendance    0          PRIMARY        1             id            A          25014         NULL     NULL             BTREE
works         0          PRIMARY        1             id            A          20600         NULL     NULL             BTREE
rooms         0          PRIMARY        1             id            A           5234         NULL     NULL             BTREE
contact       0          PRIMARY        1             id            A          16936         NULL     NULL             BTREE

Answer 5

那先创建个 rooms (date) 的索引吧
另外再建个 attendance （code以）的索引

Answer 6

现在的 show index 是这样的，但还是很慢+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| baseinfo |          0 | PRIMARY  |            1 | code        | A         |      644 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------++------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| attendance |          0 | PRIMARY  |            1 | id          | A         |      25014 |     NULL | NULL   |      | BTREE      |         |
| attendance |          1 | code     |            1 | code        | A         |        595 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------++-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| works |          0 | PRIMARY  |            1 | id          | A         | 20600 |     NULL | NULL   |      | BTREE      |         |
| works |          1 | rooms    |            1 | rooms       | A         |  5150 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------++-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| rooms |          0 | PRIMARY  |            1 | id          | A         |  5234 |     NULL | NULL   |      | BTREE      |         |
| rooms |          1 | date     |            1 | date        | A         |   275 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------++---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| contact |          0 | PRIMARY  |            1 | id          | A         |   16936 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 7

感谢老大帮助，我已经领悟了，现在已经快多了~~~我也会用 explain 分析了，谢谢~~~！