MYSQL INNODB中表數(shù)據(jù)的返回順序問題

發(fā)布時間：2020-08-08 08:22:06 來源：ITPUB博客閱讀：140 作者：gaopengtttt 欄目：MySQL數(shù)據(jù)庫

接上一篇：
http://blog.itpub.net/7728585/viewspace-2126344/
如何證明INNODB輔助索引葉子結點KEY值相同的按照PRIMARY KEY排序

我們在上一篇中建立了表
mysql> create table test (a int,b int,primary key(a),key(b));
Query OK, 0 rows affected (0.08 sec)
并且插入了數(shù)據(jù)
mysql> insert into test values(1,1);
Query OK, 1 row affected (0.08 sec)
mysql> insert into test values(5,1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into test values(3,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(4,2);
Query OK, 1 row affected (0.59 sec)
mysql> insert into test values(10,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(7,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(8,5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(11,5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(20,6);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(21,6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(19,7);
Query OK, 1 row affected (0.03 sec)
mysql> insert into test values(16,7);
Query OK, 1 row affected (0.01 sec)

通過分析和程序跑出了在輔助索引列b中的存儲順序如下：
[root@ora12ctest test]# ./a.out test.ibd 4
Index_no is:42
find first one record!
B:1,A:1-->
B:1,A:3-->
B:1,A:5-->
B:2,A:4-->
B:4,A:7-->
B:4,A:10-->
B:5,A:8-->
B:5,A:11-->
B:6,A:20-->
B:6,A:21-->
B:7,A:16-->
B:7,A:19-->

這里我們討論一下SELECT * FROM 使用 USING INDEX 索引覆蓋掃描B列的情況下和不使用索引使用索引而使用表本生的聚族索引的情況下數(shù)據(jù)
返回的順序及性能比較。
首先給出猜測的結論：
1、在使用USING INDEX B列索引的時候，返回的順序應該是和B列上輔助索引的返回順序一致，也就是程序跑出的結果，在這里需要注意一點
熟悉ORACLE的朋友如果DUMP過索引塊，會看到索引的數(shù)據(jù)實際上INDEX KEY+ROWID，那么這種情況下肯定不能使用索引覆蓋掃描(INDEX FAST FULL SCAN)，
因為索引中壓根不包含A值，但是INNODB卻不同，他包含是PRIMARY KEY，所以使用到了USING INDEX.
2、在不使用任何索引，僅僅使用全表掃描，其實全表掃描也是按鏈表順序掃描聚族索引B+樹的葉子結點，所以我們可以推斷他的順序是和A列
主鍵的排序一致的。
下面來證明這兩點：
1、
mysql> explain select * from test force index(b);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | index | NULL | b | 5 | NULL | 12 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
明顯是Using index B索引
看看結果：
mysql> select * from test force index(b);
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 3 | 1 |
| 5 | 1 |
| 4 | 2 |
| 7 | 4 |
| 10 | 4 |
| 8 | 5 |
| 11 | 5 |
| 20 | 6 |
| 21 | 6 |
| 16 | 7 |
| 19 | 7 |
+----+------+
是不是和程序按照鏈表結構跑出來的一模一樣
B:1,A:1-->
B:1,A:3-->
B:1,A:5-->
B:2,A:4-->
B:4,A:7-->
B:4,A:10-->
B:5,A:8-->
B:5,A:11-->
B:6,A:20-->
B:6,A:21-->
B:7,A:16-->
B:7,A:19-->
這樣結論1得到了驗證

2、
mysql> explain select * from test force index(primary);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
明顯沒有使用索引，那么我們可以斷定他是使用了表本生也就是聚集索引的，按照聚集索引本生的鏈表進行返回，也就是按照主鍵
列A的順序返回，因為是主鍵這個順序也就自然固定了不用看B列的值了。來看看
mysql> select * from test force index(primary);
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 7 | 4 |
| 8 | 5 |
| 10 | 4 |
| 11 | 5 |
| 16 | 7 |
| 19 | 7 |
| 20 | 6 |
| 21 | 6 |
+----+------+

可以看到確實如果結論2得到驗證。

當然這個結論不光適合SELECT 全索引掃描的情況，為了證明這一點我增加了一列
C

mysql> alter table test add column c int;
Query OK, 0 rows affected (1.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> update test set c=100;
Query OK, 12 rows affected (0.11 sec)
Rows matched: 12 Changed: 12 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

目的在于不然MYSQL使用Using index這個索引覆蓋掃描的方式：
1、
mysql> explain select * from test force index(b) where b in(4,5,7);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | range | b | b | 5 | NULL | 6 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test force index(b) where b in(4,5,7);
+----+------+------+
| a | b | c |
+----+------+------+
| 7 | 4 | 100 |
| 10 | 4 | 100 |
| 8 | 5 | 100 |
| 11 | 5 | 100 |
| 16 | 7 | 100 |
| 19 | 7 | 100 |
+----+------+------+
6 rows in set (0.01 sec)

2、
mysql> explain select * from test force index(primary) where b in(4,5,7);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 30.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test force index(primary) where b in(4,5,7);
+----+------+------+
| a | b | c |
+----+------+------+
| 7 | 4 | 100 |
| 8 | 5 | 100 |
| 10 | 4 | 100 |
| 11 | 5 | 100 |
| 16 | 7 | 100 |
| 19 | 7 | 100 |
+----+------+------+
6 rows in set (0.00 sec)

可以清楚的看到他們的區(qū)別，也就是查詢1是通過B列輔助索引的葉子結點查詢出然后進行書簽試查找主鍵回到的聚集索引，得出的
順序當然是輔助索引B中B列的排序方式。而查詢2當然也就是直接訪問聚集索引過濾的條件，當然也就是主鍵的順序。

然后我們討論一下性能問題，雖然都是按照B+樹的葉子結點進行順序返回，但是聚集索引卻要比輔助索引上的信息多，
也許要說這里聚集索引也是A,B列的值，輔助索引也是A,B列的值，
但是從前文看出：
./bcview test.ibd 16 126 30|more
current block:00000003--Offset:00126--cnt bytes:21--data is:80000001000000000707a70000011b011080000001
current block:00000004--Offset:00126--cnt bytes:21--data is:8000000180000001
在聚集索引中有
000000000707a70000011b0110這樣的信息實際上就是transaction id 和roll pointer
那么我們可以直觀的判斷出在同樣的數(shù)據(jù)量下輔助索引的葉子PAGE會少于聚集索引的PAGE，
那么性能應該也會更好。

結論：
1、如果發(fā)現(xiàn)使用不同索引返回數(shù)據(jù)的順序不一樣，不要吃驚，不一樣是正常，如果一樣才要吃驚，INNODB全表掃描
能夠保證返回數(shù)據(jù)的順序是主鍵的排序(雖然我們只驗證單葉子結點情況，但是B+樹的葉子結點是有PAGE和PAGE之間
的指針的),這一點ORACLE中卻不行，我曾經(jīng)在ORACLE的書上看到，如果要保證排序只能用ORDER BY，但是這一點視乎
在INNODB中并不適用，當然如果保險加上ORDER BY也是可以的，因為SORT的操作會被優(yōu)化器忽略，這樣以防萬一。
其實索引在INNODB和ORACLE中的另外一個功能就是避免排序。
2、create table test (a int,b int,primary key(a),key(b));這種方式如果where b= 在INNODB中可以使用索引覆蓋掃描
但是在ORACLE中不行，原因前面給出了。
3、在性能方面INNODB unsing index的性能在大多數(shù)情況下都要優(yōu)于全表掃描(聚集索引)，原因也已經(jīng)給出。

向AI問一下細節(jié)

MYSQL INNODB中表數(shù)據(jù)的返回順序問題

猜你喜歡

最新資訊

相關推薦

相關標簽