explain SELECT COUNT(*) AS think_count FROM `traffic_vehicle_pass` WHERE `PASS_TIME` BETWEEN '1613835713' AND '1616427713' AND `CROSSING_ID` IN ('321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354')\G;
同樣的陳述句在mysql5.7下顯示
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: traffic_vehicle_pass
partitions: NULL
type: range
possible_keys: INX_VEHICLE_CROSSID,INX_VEHICLE_PASSTIME
key: INX_VEHICLE_PASSTIME
key_len: 5
ref: NULL
rows: 3497
filtered: 50.19
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
由于機器出現問題后來換了資料庫Server version: 10.4.17-MariaDB 查了一下對應版本相當于mysql 8.0
但是上面的陳述句執行就出現了問題
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: traffic_vehicle_pass
type: ALL
possible_keys: INX_VEHICLE_CROSSID,INX_VEHICLE_PASSTIME
key: NULL
key_len: NULL
ref: NULL
rows: 45359005
Extra: Using where
1 row in set (0.114 sec)
無法中索引,導致掃描全表,請問一下這是哪里出現 了問題,現在庫也沒法恢復到以前的版本,還是哪里設定好,請指點一下!
執行時間達到了15秒,到50秒之間,已經無法使用.
sql陳述句中將IN部分去掉,像以下就正常了
explain SELECT COUNT(*) AS think_count FROM `traffic_vehicle_pass` WHERE `PASS_TIME` BETWEEN '1613835713' AND '1616427713';
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/269544.html
標籤：MySQL
上一篇：關聯查詢的組合索引問題
下一篇：MySQL安裝問題