MYSQL INNODB中表資料的返回順序問題
接上一篇：
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)
並且插入了資料
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列的情況下和不使用索引使用索引而使用表本生的聚族索引的情況下資料
返回的順序及效能比較。
首先給出猜測的結論：
1、在使用USING INDEX B列索引的時候，返回的順序應該是和B列上輔助索引的返回順序一致，也就是程式跑出的結果，在這裡需要注意一點
熟悉ORACLE的朋友如果DUMP過索引塊，會看到索引的資料實際上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)
可以清楚的看到他們的區別，也就是查詢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
那麼我們可以直觀的判斷出在同樣的資料量下輔助索引的葉子PAGE會少於聚集索引的PAGE，
那麼效能應該也會更好。
結論：
1、如果發現使用不同索引返回資料的順序不一樣，不要吃驚，不一樣是正常，如果一樣才要吃驚，INNODB全表掃描
能夠保證返回資料的順序是主鍵的排序(雖然我們只驗證單葉子結點情況，但是B+樹的葉子結點是有PAGE和PAGE之間
的指標的),這一點ORACLE中卻不行，我曾經在ORACLE的書上看到，如果要保證排序只能用ORDER BY，但是這一點視乎
在INNODB中並不適用，當然如果保險加上ORDER BY也是可以的，因為SORT的操作會被最佳化器忽略，這樣以防萬一。
其實索引在INNODB和ORACLE中的另外一個功能就是避免排序。
2、create table test (a int,b int,primary key(a),key(b));這種方式如果where b= 在INNODB中可以使用索引覆蓋掃描
但是在ORACLE中不行，原因前面給出了。
3、在效能方面INNODB unsing index的效能在大多數情況下都要優於全表掃描(聚集索引)，原因也已經給出。
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)
並且插入了資料
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列的情況下和不使用索引使用索引而使用表本生的聚族索引的情況下資料
返回的順序及效能比較。
首先給出猜測的結論：
1、在使用USING INDEX B列索引的時候，返回的順序應該是和B列上輔助索引的返回順序一致，也就是程式跑出的結果，在這裡需要注意一點
熟悉ORACLE的朋友如果DUMP過索引塊，會看到索引的資料實際上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)
可以清楚的看到他們的區別，也就是查詢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
那麼我們可以直觀的判斷出在同樣的資料量下輔助索引的葉子PAGE會少於聚集索引的PAGE，
那麼效能應該也會更好。
結論：
1、如果發現使用不同索引返回資料的順序不一樣，不要吃驚，不一樣是正常，如果一樣才要吃驚，INNODB全表掃描
能夠保證返回資料的順序是主鍵的排序(雖然我們只驗證單葉子結點情況，但是B+樹的葉子結點是有PAGE和PAGE之間
的指標的),這一點ORACLE中卻不行，我曾經在ORACLE的書上看到，如果要保證排序只能用ORDER BY，但是這一點視乎
在INNODB中並不適用，當然如果保險加上ORDER BY也是可以的，因為SORT的操作會被最佳化器忽略，這樣以防萬一。
其實索引在INNODB和ORACLE中的另外一個功能就是避免排序。
2、create table test (a int,b int,primary key(a),key(b));這種方式如果where b= 在INNODB中可以使用索引覆蓋掃描
但是在ORACLE中不行，原因前面給出了。
3、在效能方面INNODB unsing index的效能在大多數情況下都要優於全表掃描(聚集索引)，原因也已經給出。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2126470/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql order by 和 group by 順序問題MySql
- PG 中表示式的計算順序
- MySQL 按指定 ID 順序返回結果MySql
- Mysql按指定 ID 順序返回結果MySql
- MySQL：讀取my.cnf的順序問題MySql
- 如何克服 Apache Kafka中的資料順序問題 - DATAVERSITYApacheKafka
- MySQL資料庫診斷：InnoDB關機問題MySql資料庫
- Git diff hash順序的問題Git
- 請教資料的返回問題
- Jetpack Compose的Modifier順序問題Jetpack
- 【資料庫】解決Mysql資料庫提示innodb表不存在的問題！資料庫MySql
- [學習筆記]分組資料以及on/where/having的順序問題筆記
- 資料結構實驗一：順序表的建立與操作實現、順序表實現約瑟夫環問題資料結構
- Java 修飾符順序問題Java 修飾符
- 無順序約束的字串匹配問題字串匹配
- sql中的or與and的執行順序問題SQL
- 先有雞or先有蛋？淺談資料拆分與特徵縮放的順序問題特徵
- 關於defer執行順序問題
- Laravel 集合 where 返回的資料格式問題Laravel
- SQL語句中的AND和OR執行順序問題SQL
- 順序表有序插入資料
- UITableView停止載入中的動畫的順序問題UIView動畫
- 優化資料庫的合理順序優化資料庫
- FrameLayout裡有CardView造成的顯示順序問題View
- 關於CSS樣式的優先順序問題CSS
- SQL Server資料庫調整表中列的順序操作方法及遇到問題SQLServer資料庫
- mysql 語句的執行順序MySql
- SQL中rownum和order by的執行順序的問題SQL
- 資料型別優先順序資料型別
- php與資料庫連線如何實現資料的順序和倒序PHP資料庫
- 5-順序表查詢及插入問題
- scala隱式轉換優先順序問題
- C++輸出流cout的執行順序問題C++
- 【Oracle】where條件執行順序(上篇的問題延伸)Oracle
- 再次明確Oracle插入與讀取的順序問題Oracle
- linux下多網路卡識別順序的問題Linux
- GROUPBY 和開窗函式執行順序的問題函式
- Oracle優化案例-分頁語句返回資料順序不一致（十一）Oracle優化