多表连接的优化问题

goodtime 2009-11-29 03:28:28
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
如何优化
...全文
153 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
goodtime 2009-11-29
  • 打赏
  • 举报
 回复
不能留个qq或者mSN么，非常感谢你的回答
ACMAIN_CHM 2009-11-29
  • 打赏
  • 举报
 回复
创建如下索引。

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);
goodtime 2009-11-29
  • 打赏
  • 举报
 回复
非常感谢楼上，请问有其他的联系方式吗？好好跟你学学


+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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 | |
ACMAIN_CHM 2009-11-29
  • 打赏
  • 举报
 回复
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;


把这些信息也贴出来。不会连主键也没有吧。
goodtime 2009-11-29
  • 打赏
  • 举报
 回复
+----+-------------+--------------+------+---------------+------+---------+------+------+----------------------------------------------+
| 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
ACMAIN_CHM 2009-11-29
  • 打赏
  • 举报
 回复
如果需要进一步分析，则需要提供

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

这些信息。
ACMAIN_CHM 2009-11-29
  • 打赏
  • 举报
 回复
先把 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）索引。
其它表上同样创建基于联接字段的索引。

JDBC优化之手写数据库连接池技术
对JDBC编程之数据库连接进行优化，采用读取配置文件加载连接参数，手工模拟连接池技术编程进行优化。
多表连接查询如何优化?
多表连接查询优化方案
oracle连接多张表优化,oracle 多表查询优化
ORACLE中的高速缓冲用于保存查询过的sql语句，单只对单表查询起作用单表查询优化：写查询语句时尽量统一规范，只要有一点不一样，都不能使用高速缓冲。多表优化：(1)FROM子句后面的表顺序ORACLE在解析sql语句的时候对FROM子句后面的表名是从右往左解析的，在对多表查询中，一定要把小表写在最右边，例如tableA有100w条记录 ，tableB有1w条记录select count(*) f...
MySQL调优--05---多表查询优化、子查询优化、 ORDER BY优化、GROUP BY优化、分页查询优化
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录多表查询优化驱动表结论1.外连接左外连接: 左表为驱动表,右边为被驱动表2.内连接对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有==索引==的字段所在的表会被作为==被驱动表==出现。对于内连接来说，在两个表的连接条件==都存在索引==的情况下，会选择小表作为驱动表。“==小表驱动大表==”join语句原理 多表查询优化 驱动表结论 1.外连接 左外连接: 左表为驱动表,右边为被驱动表 2.内连接 对于内连接来
多表连接问题
前端时间有同行找到我让我给他看一段sql,说要优化,呢是个多表连接的sql,我看了下,就简单对sql语句进行了优化,对此说下多表连接优化思路: 按照正常的逻辑假如,ABC三个表有关系,一般都是select ... from a left join b ... left join c ... where ...一般都是这样的结构,其实,我们这样的sql稍微换一下顺序效率就有明显提升 我们分析上面
MySQL

56,678

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章