一个表:33个字段,现有377793条数据。
一个测试SQL执行的结果如下图所示:不加Group by(SUM)执行需要2秒多,得到15条结果数据;
加了Group by就杯具了,要37秒多。索引如下图:
求高手分析一下,为什么这个效率这么低?如何改善?
一个测试SQL执行的结果如下图所示:不加Group by(SUM)执行需要2秒多,得到15条结果数据;
加了Group by就杯具了,要37秒多。索引如下图:
求高手分析一下,为什么这个效率这么低?如何改善?
我用Phpmyadmin测的,如何看执行计划?
要看两个Sql吗?
无Group by: 执行后得到15条数据,耗时2秒多
select to_orderid, to_effectdt, to_state, to_receipt, to_accountid, to_number, to_cost, to_updateby, to_updatets From sl_ticketorder Where (to_package=0 or to_ticketinfoid=to_package) and to_updatets >= '2012-05-30 0:0:0' and to_updatets <= '2012-05-30 23:59:59'加了Group by: 执行后得到4条数据(不是重点),耗时37秒多(重点)让人痛苦啊。
select to_orderid, to_effectdt, to_state, to_receipt, to_accountid, SUM(to_number) as rs, SUM(to_cost) as je,to_updateby, to_updatets From sl_ticketorder Where (to_package=0 or to_ticketinfoid=to_package) and to_updatets >= '2012-05-30 0:0:0' and to_updatets <= '2012-05-30 23:59:59' Group by to_orderid order by to_updatets
加了Group by:
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
| 1 | SIMPLE | stock_info | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.14 sec)mysql>
mysql> explain select to_orderid, to_effectdt, to_state, to_receipt, to_accounti
d, to_number, to_cost, to_updateby, to_updatets From sl_ticketorder Where (to_pa
ckage=0 or to_ticketinfoid=to_package) and to_updatets >= '2012-05-30 0:0:0' and
to_updatets <= '2012-05-30 23:59:59';
+----+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | sl_ticketorder | ALL | NULL | NULL | NULL | NULL | 378220 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.41 sec)mysql>
mysql> explain select to_orderid, to_effectdt, to_state, to_receipt, to_accounti
d, SUM(to_number) as rs, SUM(to_cost) as je,to_updateby, to_updatets From sl_tic
ketorder Where (to_package=0 or to_ticketinfoid=to_package) and to_updatets >= '
2012-05-30 0:0:0' and to_updatets <= '2012-05-30 23:59:59' Group by to_orderid o
rder by to_updatets;
+----+-------------+----------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len| ref | rows | Extra |
+----+-------------+----------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | sl_ticketorder | index | NULL | odindex | 52| NULL | 378220 | Using where; Using temporary; Using filesort |
+----+-------------+----------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)mysql>
+----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| sl_ticketorder | 0 | PRIMARY | 1 | to_ticketid | A | 376939 | NULL | NULL | | BTREE | |
| sl_ticketorder | 1 | odindex | 1 | to_orderid | A | 26924 | NULL | NULL | | BTREE | |
| sl_ticketorder | 1 | odindex | 2 | to_barcode | A | 75387 | NULL | NULL | | BTREE | |
| sl_ticketorder | 1 | to_ticketinfoid | 1 | to_ticketinfoid | A | 17 | NULL | NULL | YES | BTREE | |
| sl_ticketorder | 1 | to_ticketinfoid | 2 | to_package | A | 401 | NULL | NULL | | BTREE | |
| sl_ticketorder | 1 | to_ticketinfoid | 3 | to_accountid | A | 3490 | NULL | NULL | YES | BTREE | |
| sl_ticketorder | 1 | to_ticketinfoid | 4 | to_effectdt | A | 17949 | NULL | NULL | YES | BTREE | |
| sl_ticketorder | 1 | to_ticketinfoid | 5 | to_checkoutdt | A | 19838 | NULL | NULL | YES | BTREE | |
| sl_ticketorder | 1 | to_ticketinfoid | 6 | to_updateby | A | 20941 | NULL | NULL | YES | BTREE | |
| sl_ticketorder | 1 | to_ticketinfoid | 7 | to_updatets | A | 62823 | NULL | NULL | | BTREE | |
| sl_ticketorder | 1 | xxx | 1 | to_orderid | A | 22172 | NULL | NULL | | BTREE | |
| sl_ticketorder | 1 | xxx | 2 | to_updatets | A | 22172 | NULL | NULL | | BTREE | |
+----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
12 rows in set (0.43 sec)mysql>
0、删除原有索引,仅保留了主键。结果提升到3秒左右。1、创建一组int类型字段的索引:
create index tko_num on sl_ticketorder (`to_ticketinfoid`,`to_package`,`to_accountid`);
结果:提升到1.7秒左右。2、创建一组VarChar类型字段的索引:
create index tko_varchar on sl_ticketorder (`to_orderid`,`to_barcode`,`to_updateby`);
结果:搞死,回落到40秒左右。马上删除!!!!3、创建一组data类型字段的索引:
create index tko_date on sl_ticketorder (`to_updatets`,`to_checkoutdt`,`to_effectdt`);
结果:提升到0.0017秒。Good!!!!!惊喜啊!!!
惊喜之与,请高手分析原因。以上尝试过程写下来供大家分享。
我都说的这么清楚了,你还说order拖慢了?