MYSQLINNODB中表數(shù)據(jù)的返回順序問題
接上一篇：
http://blog.itpub.net/7728585/viewspace-2126344/
如何證明INNODB輔助索引葉子結(jié)點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ù)
返回的順序及性能比較。
首先給出猜測的結(jié)論：
1、在使用USING INDEX B列索引的時候，返回的順序應(yīng)該是和B列上輔助索引的返回順序一致，也就是程序跑出的結(jié)果，在這里需要注意一點
熟悉ORACLE的朋友如果DUMP過索引塊，會看到索引的數(shù)據(jù)實際上INDEX KEY+ROWID，那么這種情況下肯定不能使用索引覆蓋掃描(INDEX FAST FULL SCAN)，
因為索引中壓根不包含A值，但是INNODB卻不同，他包含是PRIMARY KEY，所以使用到了USING INDEX.
2、在不使用任何索引，僅僅使用全表掃描，其實全表掃描也是按鏈表順序掃描聚族索引B+樹的葉子結(jié)點，所以我們可以推斷他的順序是和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索引
看看結(jié)果：
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 |
+----+------+
是不是和程序按照鏈表結(jié)構(gòu)跑出來的一模一樣
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-->
這樣結(jié)論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 |
+----+------+
可以看到確實如果結(jié)論2得到驗證。
當然這個結(jié)論不光適合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列輔助索引的葉子結(jié)點查詢出然后進行書簽試查找主鍵回到的聚集索引，得出的
順序當然是輔助索引B中B列的排序方式。而查詢2當然也就是直接訪問聚集索引過濾的條件，當然也就是主鍵的順序。
然后我們討論一下性能問題，雖然都是按照B+樹的葉子結(jié)點進行順序返回，但是聚集索引卻要比輔助索引上的信息多，
也許要說這里聚集索引也是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，
那么性能應(yīng)該也會更好。
結(jié)論：
1、如果發(fā)現(xiàn)使用不同索引返回數(shù)據(jù)的順序不一樣，不要吃驚，不一樣是正常，如果一樣才要吃驚，INNODB全表掃描
能夠保證返回數(shù)據(jù)的順序是主鍵的排序(雖然我們只驗證單葉子結(jié)點情況，但是B+樹的葉子結(jié)點是有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)給出。
http://blog.itpub.net/7728585/viewspace-2126344/
如何證明INNODB輔助索引葉子結(jié)點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ù)
返回的順序及性能比較。
首先給出猜測的結(jié)論：
1、在使用USING INDEX B列索引的時候，返回的順序應(yīng)該是和B列上輔助索引的返回順序一致，也就是程序跑出的結(jié)果，在這里需要注意一點
熟悉ORACLE的朋友如果DUMP過索引塊，會看到索引的數(shù)據(jù)實際上INDEX KEY+ROWID，那么這種情況下肯定不能使用索引覆蓋掃描(INDEX FAST FULL SCAN)，
因為索引中壓根不包含A值，但是INNODB卻不同，他包含是PRIMARY KEY，所以使用到了USING INDEX.
2、在不使用任何索引，僅僅使用全表掃描，其實全表掃描也是按鏈表順序掃描聚族索引B+樹的葉子結(jié)點，所以我們可以推斷他的順序是和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索引
看看結(jié)果：
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 |
+----+------+
是不是和程序按照鏈表結(jié)構(gòu)跑出來的一模一樣
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-->
這樣結(jié)論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 |
+----+------+
可以看到確實如果結(jié)論2得到驗證。
當然這個結(jié)論不光適合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列輔助索引的葉子結(jié)點查詢出然后進行書簽試查找主鍵回到的聚集索引，得出的
順序當然是輔助索引B中B列的排序方式。而查詢2當然也就是直接訪問聚集索引過濾的條件，當然也就是主鍵的順序。
然后我們討論一下性能問題，雖然都是按照B+樹的葉子結(jié)點進行順序返回，但是聚集索引卻要比輔助索引上的信息多，
也許要說這里聚集索引也是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，
那么性能應(yīng)該也會更好。
結(jié)論：
1、如果發(fā)現(xiàn)使用不同索引返回數(shù)據(jù)的順序不一樣，不要吃驚，不一樣是正常，如果一樣才要吃驚，INNODB全表掃描
能夠保證返回數(shù)據(jù)的順序是主鍵的排序(雖然我們只驗證單葉子結(jié)點情況，但是B+樹的葉子結(jié)點是有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)給出。
新聞名稱：MYSQLINNODB中表數(shù)據(jù)的返回順序問題
標題鏈接：http://www.muchs.cn/article30/jpjeso.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供面包屑導(dǎo)航、網(wǎng)站設(shè)計、用戶體驗、搜索引擎優(yōu)化、網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 網(wǎng)站建設(shè)中的面包屑導(dǎo)航有什么作用？ 2016-10-01
- 【南充網(wǎng)站推廣】網(wǎng)站建設(shè)的過程中怎么合理使用面包屑導(dǎo)航 2014-12-01
- 網(wǎng)站面包屑導(dǎo)航應(yīng)該復(fù)雜還是簡化？ 2022-07-26
- 詳細介紹下頁面中面包屑導(dǎo)航的作用 2023-03-02
- 網(wǎng)站面包屑導(dǎo)航設(shè)計注意事項 2017-02-17
- 淺談網(wǎng)站建設(shè)中的面包屑導(dǎo)航 2016-08-26
- 網(wǎng)站設(shè)計中面包屑導(dǎo)航應(yīng)注意什么？ 2016-11-08
- 成都網(wǎng)絡(luò)SEO要怎么做面包屑導(dǎo)航? 2023-04-07
- 解析面包屑導(dǎo)航對SEO有什么作用呢 2021-11-30
- 網(wǎng)站建設(shè)技術(shù)發(fā)展到今天 面包屑導(dǎo)航應(yīng)該復(fù)雜還是簡化 2016-05-02
- 網(wǎng)站面包屑導(dǎo)航部署的四大注意事項 2022-08-15
- 面包屑導(dǎo)航在網(wǎng)站優(yōu)化中有用嗎？ 2022-09-26