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的性能在大多数情况下都要优于全表扫描(聚集索引)，原因也已经给出。