登录服务器查询Mysql占用CPU过高,很直接打开show full process 跟慢查询发现很多以下sql都是在10S以上
# User@Host: gyw[gwy] @ [x.x.x.x] Id: 19513
# Query_time: 11.326904 Lock_time: 0.000327 Rows_sent: 69 Rows_examined: 1417696
SET timestamp=1504507662;
SELECT odet.seller AS sellerId,
odet.agreementprice_id AS agreementpriceId,
odet.customer_id AS customerId,
(SELECT realname
FROM sys_user suser
WHERE suser.id = odet.seller)
AS sellerName,
odet.pkgticket_id AS pkgId,
odet.pkgticket_price AS pkgPrice,
DATE_FORMAT(odet.sell_time, '%Y-%m-%d') AS sellTime,
sum(oct.tourist_number-IFNULL(ort.tourist_remain,0)) as totalPeople,
sum((oct.tourist_number-IFNULL(ort.tourist_remain,0))*odet.pkgticket_price) as totalMoney,
(SELECT name
FROM scenic_pkgticket spkg
WHERE spkg.id = odet.pkgticket_id)
AS pkgticketName
FROM order_detail odet,order_checkticket oct
LEFT JOIN order_refundticket ort
on oct.id = ort.id
WHERE odet.id=oct.order_detail_id
and odet.scenic_id = 215
and odet.sell_time >= '2017-09-04 00:00:00'
and odet.sell_time <= '2017-09-04 23:59:59'
GROUP BY sellerId, sellTime, pkgId, pkgPrice
WITH ROLLUP;
手动查看一下执行计划发现,使用Using temporary; Using filesort使用到了临时表,这样效率是最差的
explain SELECT odet.seller AS sellerId,
-> odet.agreementprice_id AS agreementpriceId,
-> odet.customer_id AS customerId,
-> (SELECT realname FROM sys_user suser WHERE suser.id = odet.seller) AS sellerName,
-> odet.pkgticket_id AS pkgId,
-> odet.pkgticket_price AS pkgPrice,
-> DATE_FORMAT(odet.sell_time, '%Y-%m-%d') AS sellTime,
-> sum(oct.tourist_number - IFNULL(ort.tourist_remain, 0)) as totalPeople,
-> sum((oct.tourist_number - IFNULL(ort.tourist_remain, 0)) *
-> odet.pkgticket_price) as totalMoney,
-> (SELECT name
-> FROM scenic_pkgticket spkg
-> WHERE spkg.id = odet.pkgticket_id) AS pkgticketName
-> FROM order_detail odet, order_checkticket oct
-> LEFT JOIN order_refundticket ort
-> on oct.id = ort.id
-> WHERE odet.id = oct.order_detail_id
-> and odet.scenic_id = 215
-> and odet.sell_time >= '2017-09-04 00:00:00'
-> and odet.sell_time <= '2017-09-04 23:59:59'
-> GROUP BY sellerId, sellTime, pkgId, pkgPrice WITH ROLLUP;
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+--------+---------------------------------+
| 1 | PRIMARY | oct | ALL | NULL | NULL | NULL | NULL | 414589 | Using temporary; Using filesort |
| 1 | PRIMARY | ort | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.id | 1 | NULL |
| 1 | PRIMARY | odet | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.order_detail_id | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | spkg | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |
| 2 | DEPENDENT SUBQUERY | suser | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+--------+---------------------------------+
尝试在在order_detail 上加一个复合索引(scenic_id,sell_time),但是依然如此未走索引,仔细检查发现order_checkticket order_detail_id未建索引。加上索引后执行计划如下
+----+--------------------+-------+--------+-------------------------+-----------------+---------+----------------+------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-------------------------+-----------------+---------+----------------+------+--------------------------------------------------------+
| 1 | PRIMARY | odet | range | PRIMARY,idx_od_si_stime | idx_od_si_stime | 14 | NULL | 183 | Using index condition; Using temporary; Using filesort |
| 1 | PRIMARY | oct | ref | idx_oct_odi | idx_oct_odi | 8 | sd_ets.odet.id | 1 | NULL |
| 1 | PRIMARY | ort | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.id | 1 | NULL |
| 3 | DEPENDENT SUBQUERY | spkg | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |
| 2 | DEPENDENT SUBQUERY | suser | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |
+----+--------------------+-------+--------+-------------------------+-----------------+---------+----------------+------+--------------------------------------------------------+
5 rows in set (0.00 sec)
我们看key已经走了索引使用idx_od_si_stime
查询速度只要0.01毫秒。提升速度上千倍
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领
- 历年真题答案解析
- 备考技巧名师总结
- 高频考点精准押题
- 资料下载
- 历年真题
193.9 KB下载数265
191.63 KB下载数245
143.91 KB下载数1148
183.71 KB下载数642
644.84 KB下载数2756
相关文章
发现更多好内容- Java泛型中的 extends 操作符对性能有哪些影响?(Java泛型extends的性能影响如何)
- 如何掌握 Java 图形化界面设计原则?(java图形化界面设计原则)
- 在 Java 中如何获取栈顶元素?(java怎么获取栈顶元素)
- 软考高级考试可以自己选批次吗?
- Java 中如何获取字符数组中的字符?(java字符数组怎么获取字符)
- 在 Java 中,JLabel 的文本是否能够实现旋转?(java中jlabel的文本能否实现旋转)
- Java编程中 abstract 类和方法的详细解析与应用指南(java编程abstract类和方法详解)
- 如何判断 Java 数组中是否存在重复元素?(Java怎么判断数组是否有重复元素)
- Java 中魔法值究竟是什么含义?(java魔法值是什么意思)
- 在 Java 开发中,Javase 究竟扮演着怎样的角色?(Javase在Java开发中扮演什么角色)
猜你喜欢
AI推送时光机Mysql表关联字段未建索引导致查询慢,优化后查询效率显著提升
数据库2024-04-02
咦!没有更多了?去看看其它编程学习网 内容吧