多表连接的优化问题

Question

select charge.sintrue_key,
       charge.sintrue_charge_id,
       charge.sintrue_charge_memo,
       charge.sintrue_charge_pay,
       charge.sintrue_charge_date,
       customer.sintrue_customer_name,
       customer.sintrue_customer_building,
       customer.sintrue_customer_unit,
       customer.sintrue_customer_room,
       district.sintrue_district_text,
       customertype.sintrue_customer_type_text,
       adm.sintrue_admin_name,ifnull(unit.sintrue_unit_text,'') sintrue_unit_text,
       charge.sintrue_charge_serial 
 from sintrue_charge charge  
 join sintrue_customer customer on charge.sintrue_customer_id=customer.sintrue_customer_id  
 join sintrue_district district on customer.sintrue_district_id=district.sintrue_district_id
 join sintrue_customer_type customertype on customer.sintrue_customer_type_id=customertype.sintrue_customer_type_id  
 join sintrue_year_charge yearcharge on charge.sintrue_year_id=yearcharge.sintrue_year_id and customer.sintrue_customer_type_id=yearcharge.sintrue_customer_type_id  
 join sintrue_admin adm on adm.sintrue_admin_login=charge.sintrue_admin_id  
 left join sintrue_unit unit on unit.sintrue_unit_id=customer.sintrue_unit_id  
 where 1=1  and date(charge.sintrue_charge_date)>='2009-10-01'  and date(charge.sintrue_charge_date)<='2009-11-29'  order by charge.sintrue_charge_date  limit 15,15
如何优化

Answer 1

先把 where 1=1  and date(charge.sintrue_charge_date)>='2009-10-01'  and date(charge.sintrue_charge_date) <='2009-11-29'  
改成
 where 1=1  
  and charge.sintrue_charge_date>='2009-10-01'  
  and charge.sintrue_charge_date<='2009-11-29'  没有必要进行date()运算。 这样可以利用  sintrue_charge_date 索引。创建 sintrue_charge （sintrue_charge_date）索引。
其它表上同样创建基于联接字段的索引。

Answer 2

如果需要进一步分析，则需要提供show index from sintrue_charge;
show index from sintrue_district;
show index from sintrue_customer_type;
show index from sintrue_year_charge;
show index from sintrue_admin;
show index from sintrue_unit;和 
explain select charge.sintrue_key,
   charge.sintrue_charge_id,
   charge.sintrue_charge_memo,
   charge.sintrue_charge_pay,
   charge.sintrue_charge_date,
   customer.sintrue_customer_name,
   customer.sintrue_customer_building,
   customer.sintrue_customer_unit,
   customer.sintrue_customer_room,
   district.sintrue_district_text,
   customertype.sintrue_customer_type_text,
   adm.sintrue_admin_name,ifnull(unit.sintrue_unit_text,'') sintrue_unit_text,
   charge.sintrue_charge_serial 
 from sintrue_charge charge  
  join sintrue_customer customer on charge.sintrue_customer_id=customer.sintrue_customer_id  
  join sintrue_district district on customer.sintrue_district_id=district.sintrue_district_id
  join sintrue_customer_type customertype on customer.sintrue_customer_type_id=customertype.sintrue_customer_type_id  
  join sintrue_year_charge yearcharge on charge.sintrue_year_id=yearcharge.sintrue_year_id and customer.sintrue_customer_type_id=yearcharge.sintrue_customer_type_id  
  join sintrue_admin adm on adm.sintrue_admin_login=charge.sintrue_admin_id  
  left join sintrue_unit unit on unit.sintrue_unit_id=customer.sintrue_unit_id  
 where 1=1  
  and charge.sintrue_charge_date>='2009-10-01'  
  and charge.sintrue_charge_date<='2009-11-29'  
order by charge.sintrue_charge_date  limit 15,15这些信息。

Answer 3

+----+-------------+--------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | charge       | ALL  | NULL          | NULL | NULL    | NULL | 1198 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | customertype | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                              |
|  1 | SIMPLE      | yearcharge   | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where                                  |
|  1 | SIMPLE      | adm          | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where                                  |
|  1 | SIMPLE      | district     | ALL  | NULL          | NULL | NULL    | NULL |   17 |                                              |
|  1 | SIMPLE      | customer     | ALL  | NULL          | NULL | NULL    | NULL | 3102 | Using where                                  |
|  1 | SIMPLE      | unit         | ALL  | NULL          | NULL | NULL    | NULL |   42 |                                              |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------------------------------------------+
才1000条数据，没有创建索引，查询时间90s

Answer 4

show index from sintrue_charge;
show index from sintrue_district;
show index from sintrue_customer_type;
show index from sintrue_year_charge;
show index from sintrue_admin;
show index from sintrue_unit;
把这些信息也贴出来。不会连主键也没有吧。

Answer 5

非常感谢楼上，请问有其他的联系方式吗？好好跟你学学
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sintrue_charge |          0 | PRIMARY  |            1 | sintrue_key | A         |        1255 | NULL     | NULL   |      | BTREE      |         |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sintrue_district |          0 | PRIMARY  |            1 | sintrue_key | A         |          17 | NULL     | NULL   |      | BTREE      |         |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                 | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sintrue_customer_type |          0 | PRIMARY  |            1 | sintrue_key | A         |           2 | NULL     | NULL   |      | BTREE      |         |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table               | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sintrue_year_charge |          0 | PRIMARY  |            1 | sintrue_key | A         |           4 | NULL     | NULL   |      | BTREE      |         |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sintrue_admin |          0 | PRIMARY  |            1 | sintrue_key | A         |           5 | NULL     | NULL   |      | BTREE      |         |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sintrue_unit |          0 | PRIMARY  |            1 | sintrue_key | A         |          42 | NULL     | NULL   |      | BTREE      |         |

Answer 6

创建如下索引。CREATE INDEX idx1 ON sintrue_charge (sintrue_charge_date);
CREATE INDEX idx2 ON sintrue_district (sintrue_district_id);
CREATE INDEX idx3 ON sintrue_customer_type (sintrue_customer_type_id);
CREATE INDEX idx4 ON sintrue_year_charge (sintrue_year_id,sintrue_customer_type_id);
CREATE INDEX idx5 ON sintrue_admin (sintrue_admin_id);
CREATE INDEX idx6 ON sintrue_unit (sintrue_unit_id);

Answer 7

不能留个qq或者mSN么，非常感谢你的回答