我有一個簡單的 spring JPA 選擇查詢，執行起來需要太多時間。
表包含約 3400 萬條記錄。
Query : select * from temp where cust_id=211313131;
taking time >1sec
SLow Query logs : Query_time: 0.990815 Lock_time: 0.000034 Rows_sent: 0 Rows_examined: 3273885 Rows_affected: 0
Table structure :
mysql> desc temp;
------------- -------------- ------ ----- ------------------- -----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------- -------------- ------ ----- ------------------- -----------------------------------------------
| id | bigint | NO | PRI | NULL | auto_increment |
| cust_id | varchar(100) | YES | MUL | NULL | |
| amount | double(11,3) | YES | | NULL | |
| is_enabled | int | NO | MUL | 1 | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | NO | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
------------- -------------- ------ ----- ------------------- -----------------------------------------------
6 rows in set (0.05 sec)
表索引：
mysql> show index from temp;
------------- ------------ ---------------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
------------- ------------ ---------------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
| temp | 0 | PRIMARY | 1 | id | A | 285 | NULL | NULL | | BTREE | | | YES | NULL |
| temp | 1 | idx_subscribers_cust_id | 1 | cust_id | A | 281 | NULL | NULL | YES | BTREE | | | YES | NULL |
| temp | 1 | idx_subscribers_is_enabled | 1 | is_enabled | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| temp | 1 | idx_subscribers_updated_at | 1 | updated_at | A | 264 | NULL | NULL | | BTREE | | | YES | NULL |
------------- ------------ ---------------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
4 rows in set (0.21 sec)
我已經檢查了解釋，解釋結果顯示它應該立即執行。
mysql> explain select * from temp where cust_id="31231234343";
---- ------------- ------------- ------------ ------ ------------------------- ------------------------- --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ------------- ------------ ------ ------------------------- ------------------------- --------- ------- ------ ---------- -------
| 1 | SIMPLE | temp | NULL | ref | idx_subscribers_cust_id | idx_subscribers_cust_id | 403 | const | 1 | 100.00 | NULL |
---- ------------- ------------- ------------ ------ ------------------------- ------------------------- --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)
任何人都可以幫助我找出為什么即使該列上存在索引也需要> 1秒才能獲取記錄。
uj5u.com熱心網友回復：
一種懷疑與隱式型別轉換有關。
如果查詢看起來像這樣：
select * from temp where cust_id = 1，則 mysql 嘗試將列轉換為適合引數的型別。內部查詢是這樣執行的：
select * from temp where cast(cust_id as UNSIGNED) =1
我不確定 MYSQL 轉換為的確切數字型別，但可以肯定的是，對于這種情況，不會使用現有密鑰
uj5u.com熱心網友回復：
WHERE cust_id=211313131
要么更改cust_id為數字（例如 `INT UNSIGNED），要么將測驗更改為字串：
WHERE cust_id = "211313131"
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/495514.html