下面将通过实例分析两种排序实现方式及实现图解：
假设有 Table A 和 B 两个表结构分别如下：
| 1 sky@localhost : example 01:48:21> show create table A\G 2 3 *************************** 1. row *************************** 4 5 Table: A 6 7 Create Table: CREATE TABLE `A` ( 8 9 `c1` int(11) NOT NULL default '0', 10 11 `c2` char(2) default NULL, 12 13 `c3` varchar(16) default NULL, 14 15 `c4` datetime default NULL, 16 17 PRIMARY KEY (`c1`) 18 19 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 20 21 sky@localhost : example 01:48:32> show create table B\G 22 23 *************************** 1. row *************************** 24 25 Table: B 26 27 Create Table: CREATE TABLE `B` ( 28 29 `c1` int(11) NOT NULL default '0', 30 31 `c2` char(2) default NULL, 32 33 `c3` varchar(16) default NULL, 34 35 PRIMARY KEY (`c1`), 36 37 KEY `B_c2_ind` (`c2`) 38 39 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
假设我们在Table A 和 B 上执行如下SQL：
| 1 sky@localhost : example 01:44:28> EXPLAIN SELECT A.* FROM A,B 2 3 -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c1\G 4 5 *************************** 1. row *************************** 6 7 id: 1 8 9 select_type: SIMPLE 10 11 table: A 12 13 type: range 14 15 possible_keys: PRIMARY 16 17 key: PRIMARY 18 19 key_len: 4 20 21 ref: NULL 22 23 rows: 3 24 25 Extra: Using where 26 27 *************************** 2. row *************************** 28 29 id: 1 30 31 select_type: SIMPLE 32 33 table: B 34 35 type: ref 36 37 possible_keys: B_c2_ind 38 39 key: B_c2_ind 40 41 key_len: 7 42 43 ref: example.A.c2 44 45 rows: 2 46 47 Extra: Using where; Using index |
