大家好
我有一個奇怪的查詢。假設我有一個包含復合主鍵（2 列）的表。
CREATE TABLE `testtable` (
`ifk1` INT(10) NOT NULL,
`ifk2` INT(10) NOT NULL,
`data1` VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (`ifk1`,`ifk2`),
UNIQUE KEY `keyName` (`data1`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4
讓我們添加一些基本資料
INSERT INTO testtable(ifk1 , ifk2 , data1)
VALUES (1 , 2 , 'a') , (5 , 2 , 'b') , (2 , 4 , 'c') , (5 , 8 , 'd') , (2 , 2 , 'e') , (2 , 5 , 'f');
讓我們做一個簡單的 SELECT 來查看資料的輸出順序：
ifk1 ifk2 data1
1 2 a
2 2 e
2 4 c
2 5 f
5 2 b
5 8 d
現在，如果我想撰寫一些代碼來遍歷表，一次獲取 X 條記錄怎么辦。使用一小組資料，這很簡單：
SELECT * FROM testtable LIMIT 0 , 2;
SELECT * FROM testtable LIMIT 2 , 2;
SELECT * FROM testtable LIMIT 4 , 2;
隨著表變大，這會遇到一些問題，因為它沒有使用 WHERE 子句，因此也沒有使用 INDEX。如何使用 WHERE 子句復制上述 SELECTS？
SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2; -- this will work
第一個很容易，但是其他的呢？有沒有辦法做到這一點？
uj5u.com熱心網友回復：
我不確定我是否理解您問題的索引部分。但一般來說，如果你想迭代一個更大的結果集，你可以使用游標，如下所述：
https://www.mysqltutorial.org/mysql-cursor/
這將用于存盤程序，但其他語言的資料庫驅動程式將公開類似的功能。
uj5u.com熱心網友回復：
如果你的要求是使用索引： 邏輯是一樣的：
mysql> SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
------ ------ -------
| ifk1 | ifk2 | data1 |
------ ------ -------
| 2 | 4 | c |
| 5 | 8 | d |
------ ------ -------
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- --------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- --------------------------
| 1 | SIMPLE | testtable | NULL | index | PRIMARY | keyName | 43 | NULL | 6 | 33.33 | Using where; Using index |
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- --------------------------
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 4,2;
------ ------ -------
| ifk1 | ifk2 | data1 |
------ ------ -------
| 2 | 2 | e |
| 2 | 5 | f |
------ ------ -------
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 4,2;
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- --------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- --------------------------
| 1 | SIMPLE | testtable | NULL | index | PRIMARY | keyName | 43 | NULL | 6 | 33.33 | Using where; Using index |
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- --------------------------
1 row in set, 1 warning (0.00 sec)
以上使用KeyName索引：也許你應該停用或洗掉KeyName索引，并啟用查詢使用PK復合索引：請按照以下步驟實作它：
首先洗掉 keyName 索引：
mysql> ALTER TABLE testtable
-> DROP INDEX keyName;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次運行查詢以查看查詢中使用了 PK 復合鍵，我認為這會使查詢更快：
mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------
| 1 | SIMPLE | testtable | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 6 | 33.33 | Using where |
---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------
1 row in set, 1 warning (0.00 sec)
uj5u.com熱心網友回復：
LIMIT沒有子句的子句ORDER BY是任意的。您顯示的所有三個查詢：
SELECT * FROM testtable LIMIT 0 , 2;
SELECT * FROM testtable LIMIT 2 , 2;
SELECT * FROM testtable LIMIT 4 , 2;
可以回傳完全相同的兩行。因此，您必須添加一個ORDER BY子句以使其可靠地作業：ORDER BY ifk1, ifk2。
但是，是的，每次訪問都必須一次又一次地對資料進行排序可能會花費很多時間。這就是為什么我們盡量避免使用偏移量并使用鍵來代替：
SELECT *
FROM testtable
WHERE ifk1 > @last_ifk1 OR (ifk1 = @last_ifk1 AND ifk2 > @last_ifk2)
ORDER BY ifk1, ifk2
LIMIT 2;
分頁幾乎總是很慢。但是這種訪問方式可以使用主鍵在(ifk1, ifk2) 上的唯一索引，非常快速地訪問接下來的兩行。這取決于 MySQL 中的實作及其版本有多快。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/535892.html
標籤：数据库数据库