Mysql表關聯欄位未建索引導致查詢慢,優化後查詢效率顯著提升
今天收到使用者反饋前端頁面開啟很慢。資料庫伺服器負載也告警了。
登入伺服器查詢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毫秒。提升速度上千倍
登入伺服器查詢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毫秒。提升速度上千倍
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24486203/viewspace-2144605/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL索引原理及慢查詢優化MySql索引優化
- mysql關聯查詢優化MySql優化
- MySQL 慢查詢優化MySql優化
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- MySQL 優化五(關聯查詢子查詢以及 in 的效率問題)(高階篇)MySql優化
- [Mysql 查詢語句]——查詢欄位MySql
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- Mysql索引型別建立錯誤導致SQL查詢緩慢MySql索引型別
- MySQL索引與查詢優化MySql索引優化
- MySQL:查詢欄位數量多少對查詢效率的影響MySql
- Mysql 慢查詢優化實踐MySql優化
- 多表關聯查詢中,關聯欄位都應該建立索引嗎?索引
- mysql 三表關聯查詢MySql
- mysql三表關聯查詢MySql
- MySQL索引原理及慢查詢最佳化MySql索引
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- mysql 表資料量大量查詢慢如何優化MySql優化
- 關聯查詢子查詢效率簡單比照
- 查詢oracle表的資訊(表,欄位,約束,索引)Oracle索引
- mysql update join優化update in查詢效率MySql優化
- mysql三張表關聯查詢MySql
- MySQL-效能優化-索引和查詢優化MySql優化索引
- MySQL 索引及查詢優化總結MySql索引優化
- mysql查詢效率慢的SQL語句MySql
- MySQL 慢查詢MySql
- MySQL慢查詢MySql
- 查詢表上的索引及對應的欄位索引
- [Mysql]慢查詢最佳化MySql
- MySQL查詢優化MySql優化
- MYSQL A、B表陣列關聯查詢MySql陣列
- mysql三表關聯查詢練習MySql
- MySQL:RR模式下insert也可能導致查詢慢MySql模式
- Mongodb 關聯表查詢MongoDB
- Mysql優化_慢查詢開啟說明及Mysql慢查詢分析工具mysqldumpslow用法講解MySql優化
- Laravel ORM 中,根據關聯查詢的欄位值,對主查詢排名LaravelORM
- MySQL多表關聯查詢MySql
- MongoDB慢查詢與索引MongoDB索引
- Laravel 對於 Mysql 欄位string型別查詢,當使用數字對這個欄位進行查詢,PHP弱型別語言導致索引失效LaravelMySql型別PHP索引