先limit再join
SELECT a.prj_id,a.pre_doc_id,a.out_time, a.inactive_user, a.inactive_time,
u.use_nm, GROUP_CONCAT(Distinct d.job_no) jb_nos,ifnull(sum(b.qty),0) pre_qty,
ifnull(sum(c.qty),0) qty, GROUP_CONCAT(Distinct e.rq_id) rq_ids FROM prepare_doc a
LEFT JOIN pre_doc_item b ON a.pre_doc_id=b.pre_doc_id
LEFT JOIN assign_item c ON b.pre_item_id= c.pre_item_id
LEFT JOIN out_job_item d ON c.job_item_id=d.job_item_id
LEFT JOIN ref_rq_pre e ON a.pre_doc_id= e.pre_doc_id
LEFT JOIN user u ON a.inactive_user= u.use_id where true AND a.prj_id ='1' AND a.inactive is null
group by a.pre_doc_id order by a.out_time desc limit 0, 15用了0.6s
select a.*, u.use_nm, GROUP_CONCAT(Distinct d.job_no) jb_nos,ifnull(sum(b.qty),0) pre_qty,
ifnull(sum(c.qty),0) qty, GROUP_CONCAT(Distinct e.rq_id) rq_ids
from (SELECT a.prj_id,a.pre_doc_id,a.out_time, a.inactive_user, a.inactive_time FROM prepare_doc a
where true AND a.prj_id ='1' AND a.inactive is null order by a.out_time desc limit 0, 15) a
LEFT JOIN pre_doc_item b ON a.pre_doc_id=b.pre_doc_id
LEFT JOIN assign_item c ON b.pre_item_id= c.pre_item_id
LEFT JOIN out_job_item d ON c.job_item_id=d.job_item_id
LEFT JOIN ref_rq_pre e ON a.pre_doc_id= e.pre_doc_id
LEFT JOIN user u ON a.inactive_user= u.use_id group by a.pre_doc_id order by a.out_time desc用了0.07s性能差10倍
添加索引优化器更高效率地执行语句
假设我们有两个数据表t1和t2，每个有1000行，包含的值从1到1000。下面的查询查找出两个表中值相同的数据行：
|
两个表都没有索引的时候，EXPLAIN产生下面的结果：
|
类型列中的ALL表明要进行检查所有数据行的全表扫描。可能键列中的NULL表明没有找到用于提高查询速度的备选索引（键、键长度和参考列都是NULL也是因为缺少合适的索引）。Using where表明使用WHERE子句中的信息来识别合格的数据行。
这段信息告诉我们，优化器没有为提高执行查询的效率找到任何有用的信息：
· 它将对t1表进行全表扫描。
· 对于t1中的每一行，它将执行t2的全表扫描，使用WHERE子句中的信息识别出合格的行。
行数值显示了优化器估计的每个阶段查询需要检查的行数。T1的估计值是1000，因为 1000可以完成全表扫描。相似地，t2的估计值也是1000，但是这个值是对于t1的每一行的。换句话说，优化器所估计的处理该查询所需要检查的数据行 组合的数量是1000×1000，也就是一百万。这会造成很大的浪费
为了使这个查询的效率更高，给其中一个联结列添加索引并重新执行EXPLAIN语句：
|
我们可以看到性能提高了。T1的输出没有改变（表明还是需要进行全表扫描），但是优化器处理t2的方式就有所不同了：
· 类型从ALL改变为ref，意味着可以使用参考值（来自t1的值）来执行索引查找，定位t2中合格的数据行。
· 参考值在参考（ref）字段中给出了：sampdb.t1.i1。
· 行数值从1000降低到了10，显示出优化器相信对于t1中的每一行，它只需要检查t2中的10行（这是一个悲观的估计值。实际上，在t2中只有一行与 t1中数据行匹配。我们在后面会看到如何帮助优化器改善这个估计值）。数据行组合的全部估计值使1000×10＝10000。它比前面的没有索引的时候估计出来的一百万好多了。
对t1进行索引有价值吗？实际上，对于这个特定的联结操作，扫描一张表是必要的，因此没有必要对t1建立索引。如果你想看到效果，可以索引t1.i1并再次运行EXPLAIN：
|
上面的输出与前面的EXPLAIN的输出相似，但是添加索引对t1的输出有一些改变。类型从NULL改成了index，附加（Extra）从空的改成了 Using index。这些改变表明，尽管对索引的值仍然需要执行全表扫描，但是优化器还是可以直接从索引文件中读取值，根据不需要使用数据文件。你可以从 MyISAM表中看到这类结果，在这种情况下，优化器知道自己只询问索引文件就能够得到所有需要的信息。对于InnoDB 和BDB表也有这样的结果，在这种情况下优化器可以单独使用索引中的信息而不用搜索数据行。
我们可以运行ANALYZE TABLE使优化器进一步优化估计值。这会引起服务器生成键值的静态分布。分析上面的表并再次运行EXPLAIN得到了更好的估计值：
|
在这种情况下，优化器估计在t2中与t1的每个值匹配的数据行只有一个。
mysql > explain select A . id , A . title , B . title from jos_content A , jos_categories B where A . catid = B . id ;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
| 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 46585 | |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
2 rows in set ( 0.00 sec ) 这个是我们经常使用的一种查询方式，对B表的联接类型使用了eq_ref，索引使用了PRIMARY，但是对于A表，却没有使用任何索引，这可能不是我们想要的。
查看以上SQL语句，我们可能会想到，有必要给A.catid加个索引了。
mysql
>
alter
table
jos_content
add
index
idx_catid
(
`
catid
`
)
;
Query
OK
,
46585
rows
affected
(
0.75
sec
)
Records
:
46585
Duplicates
:
0
Warnings
:
0
mysql
>
explain
select
A
.
id
,
A
.
title
,
B
.
title
from
jos_content
A
,
jos_categories
B
where
A
.
catid
=
B
.
id
;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
|
id
|
select_type
|
table
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
Extra
|
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
|
1
|
SIMPLE
|
A
|
ALL
|
idx_catid
|
NULL
|
NULL
|
NULL
|
46585
| |
|
1
|
SIMPLE
|
B
|
eq_ref
|
PRIMARY
|
PRIMARY
|
4
|
joomla_test
.
A
.
catid
|
1
|
Using
where
|
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
2
rows
in
set
(
0.00
sec
)
这样表A便使用了idx_catid索引。下面我们做一次三个表的联合查询
mysql
>
explain
select
A
.
id
,
A
.
title
,
B
.
title
from
jos_content
A
,
jos_categories
B
,
jos_sections
C
where
A
.
catid
=
B
.
id
and
A
.
sectionid
=
C
.
id
;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
|
id
|
select_type
|
table
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
Extra
|
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
|
1
|
SIMPLE
|
C
|
index
|
PRIMARY
|
PRIMARY
|
4
|
NULL
|
2
|
Using
index
|
|
1
|
SIMPLE
|
A
|
ALL
|
idx_catid
|
NULL
|
NULL
|
NULL
|
46585
|
Using
where
;
Using
join
buffer
|
|
1
|
SIMPLE
|
B
|
eq_ref
|
PRIMARY
|
PRIMARY
|
4
|
joomla_test
.
A
.
catid
|
1
|
Using
where
|
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
3
rows
in
set
(
0.00
sec
)
这里显示了Mysql先将C表读入查询，并使用PRIMARY索引，然后联合A表进行查询，这时候type显示的是ALL，可以用的索引有idx_catid，但是实际没有用。
原因非常明显，因为使用的连接条件是A.sectionid=C.id，所以我们给A.sectionid加个索引先。
mysql
>
alter
table
jos_content
add
index
idx_section
(
`
sectionid
`
)
;
Query
OK
,
46585
rows
affected
(
0.89
sec
)
Records
:
46585
Duplicates
:
0
Warnings
:
0
mysql
>
explain
select
A
.
id
,
A
.
title
,
B
.
title
from
jos_content
A
,
jos_categories
B
,
jos_sections
C
where
A
.
catid
=
B
.
id
and
A
.
sectionid
=
C
.
id
;
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
|
id
|
select_type
|
table
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
Extra
|
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
|
1
|
SIMPLE
|
C
|
index
|
PRIMARY
|
PRIMARY
|
4
|
NULL
|
2
|
Using
index
|
|
1
|
SIMPLE
|
A
|
ref
|
idx_catid
,
idx_section
|
idx_section
|
4
|
joomla_test
.
C
.
id
|
23293
|
Using
where
|
|
1
|
SIMPLE
|
B
|
eq_ref
|
PRIMARY
|
PRIMARY
|
4
|
joomla_test
.
A
.
catid
|
1
|
Using
where
|
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
3
rows
in
set
(
0.00
sec
)
这时候显示结果告诉我们，效果很明显，在连接A表时type变成了ref，索引使用了idx_section，如果我们注意看后两列，对A表的查询结果后一次明显少了一半左右，而且没有用到join buffer。
这个表读入的顺序是Mysql优化器帮我们做的，可以得知，用记录数少的表做为基础表进行联合，将会得到更高的效率。
对于上面的语句，我们换一种写法
mysql
>
explain
select
A
.
id
,
A
.
title
,
B
.
title
from
jos_content
A
left
join
jos_categories
B
on
A
.
catid
=
B
.
id
left
join
jos_sections
C
on
A
.
sectionid
=
C
.
id
;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
|
id
|
select_type
|
table
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
Extra
|
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
|
1
|
SIMPLE
|
A
|
ALL
|
NULL
|
NULL
|
NULL
|
NULL
|
46585
| |
|
1
|
SIMPLE
|
B
|
eq_ref
|
PRIMARY
|
PRIMARY
|
4
|
joomla_test
.
A
.
catid
|
1
| |
|
1
|
SIMPLE
|
C
|
eq_ref
|
PRIMARY
|
PRIMARY
|
4
|
joomla_test
.
A
.
sectionid
|
1
|
Using
index
|
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
3
rows
in
set
(
0.00
sec
)
Mysql读入表的顺序被改变了，这意味着，如果我们用left join来做连接查询，Mysql会按SQL语句中表出现的顺序读入，还有一个有变化的地方是联接B和C的type都变成了eq_ref，前边我们说过，这样说明Mysql可以找到唯一的行，这个效率是比ref要高的。
