先limit再join
SELECT a.prj_id,a.pre_doc_id,a.out_time, a.inactive_user, a.inactive_time,
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,
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 user u ON a.inactive_user= u.use_id group by a.pre_doc_id
用了0.07s性能差10倍
添加索引优化器更高效率地执行语句
假设我们有两个数据表t1和t2,每个有1000行,包含的值从1到1000。下面的查询查找出两个表中值相同的数据行:
mysql> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2; +------+------+ | i1 | i2 | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | ... |
两个表都没有索引的时候,EXPLAIN产生下面的结果:
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using whe |
类型列中的ALL表明要进行检查所有数据行的全表扫描。可能键列中的NULL表明没有找到用于提高查询速度的备选索引(键、键长度和参考列都是NULL也是因为缺少合适的索引)。Using where表明使用WHERE子句中的信息来识别合格的数据行。
这段信息告诉我们,优化器没有为提高执行查询的效率找到任何有用的信息:
· 它将对t1表进行全表扫描。
· 对于t1中的每一行,它将执行t2的全表扫描,使用WHERE子句中的信息识别出合格的行。
行数值显示了优化器估计的每个阶段查询需要检查的行数。T1的估计值是1000,因为1000可以完成全表扫描。相似地,t2的估计值也是1000,但是这个值是对于t1的每一行的。换句话说,优化器所估计的处理该查询所需要检查的数据行组合的数量是1000×1000,也就是一百万。这会造成很大的浪费 ,因为实际上只有1000个组合符合WHERE子句的条件。
为了使这个查询的效率更高,给其中一个联结列添加索引并重新执行EXPLAIN语句:
mysql> ALTER TABLE t2 ADD INDEX (i2); mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index |
我们可以看到性能提高了。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:
mysql> ALTER TABLE t1 ADD INDEX (i1); mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index |
上面的输出与前面的EXPLAIN的输出相似,但是添加索引对t1的输出有一些改变。类型从NULL改成了index,附加(Extra)从空的改成了 Using index。这些改变表明,尽管对索引的值仍然需要执行全表扫描,但是优化器还是可以直接从索引文件中读取值,根据不需要使用数据文件。你可以从 MyISAM表中看到这类结果,在这种情况下,优化器知道自己只询问索引文件就能够得到所有需要的信息。对于InnoDB 和BDB表也有这样的结果,在这种情况下优化器可以单独使用索引中的信息而不用搜索数据行。
我们可以运行ANALYZE TABLE使优化器进一步优化估计值。这会引起服务器生成键值的静态分布。分析上面的表并再次运行EXPLAIN得到了更好的估计值:
mysql> ANALYZE TABLE t1, t2; mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 1 Extra: Using where; Using inde |
在这种情况下,优化器估计在t2中与t1的每个值匹配的数据行只有一个。
mysql > explain select A . id , A . title , B . title from jos_content A , jos_categories B whereA . catid = B . id ;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
| id | select_type | table | type
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
|
|
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
2 rows in set ( 0.00 sec )
这个是我们经常使用的一种查询方式,对B表的联接类型使用了eq_ref,索引使用了PRIMARY,但是对于A表,却没有使用任何索引,这可能不是我们想要的。
查看以上SQL语句,我们可能会想到,有必要给A.catid加个索引了。
Query OK , 46585 rows affected ( 0.75 sec )
Records : 46585
mysql > explain select A . id , A . title , B . title from jos_content A , jos_categories B whereA . catid = B . id ;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
| id | select_type | table | type
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
|
|
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
2 rows in set ( 0.00 sec )
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
| id | select_type | table | type
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
|
|
|
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
3 rows in set ( 0.00 sec )
原因非常明显,因为使用的连接条件是A.sectionid=C.id,所以我们给A.sectionid加个索引先。
Query OK , 46585 rows affected ( 0.89 sec )
Records : 46585
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
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
|
|
|
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
3 rows in set ( 0.00 sec )
这个表读入的顺序是Mysql优化器帮我们做的,可以得知,用记录数少的表做为基础表进行联合,将会得到更高的效率。
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
| id | select_type | table | type
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
|
|
|
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
3 rows in set ( 0.00 sec )