多表連接配接索引的問題

mysql> select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name as name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id;

+----+-------+----+------+----+------+

| id | name  | id | name | id | name |

|  1 | a     |  1 | aaa  |  1 | aa   |

|  2 | b     |  2 | bbb  |  2 | bb   |

|  3 | ????  |  3 | ccc  |  3 | cc   |

|  4 | s??2s |  4 | ddd  |  4 | dd   |

|  5 | e     |  5 | eee  |  5 | ee   |

|  6 | f     |  6 | fff  |  6 | ff   |

6 rows in set (0.00 sec)

mysql> explain select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name as name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id;                    

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+

| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra |

|  1 | SIMPLE      | test1 | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    6 |       |

|  1 | SIMPLE      | test2 | eq_ref | PRIMARY       | PRIMARY | 4       | test.test1.id |    1 |       |

|  1 | SIMPLE      | test3 | eq_ref | PRIMARY       | PRIMARY | 4       | test.test1.id |    1 |       |

3 rows in set (0.00 sec)

因為查詢裡對左表test1沒有條件，是全部輸出的，是以不會用上test1的索引，同時由于需要在test2表裡用id關聯test1表，是以就用上了test2表的索引。一旦對test1表增加條件，就會使用test1表的索引。

mysql> explain select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name as name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id order by test1.id;    

|  1 | SIMPLE      | test1 | index  | PRIMARY       | PRIMARY | 4       | NULL          |    6 |       |

3 rows in set (0.01 sec)

全部用上索引。

======================================================================

查詢出id不等于5的輸出：

mysql> explain select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name as name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id where test1.tid <> 5;  

+----+-------------+-------+--------+----------------+---------+---------+---------------+------+-------------+

| id | select_type | table | type   | possible_keys  | key     | key_len | ref           | rows | Extra       |

|  1 | SIMPLE      | test1 | ALL    | PRIMARY,IX_tid | NULL    | NULL    | NULL          |    6 | Using where |

|  1 | SIMPLE      | test2 | eq_ref | PRIMARY        | PRIMARY | 4       | test.test1.id |    1 |             |

|  1 | SIMPLE      | test3 | eq_ref | PRIMARY        | PRIMARY | 4       | test.test1.id |    1 |             |

<>不等于在MYSQL裡是不能使用索引的。

mysql> explain select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name as name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id where test1.tid < 5 or test1.tid > 5;   

|  1 | SIMPLE      | test1 | ALL    | PRIMARY,IX_tid | NULL    | NULL    | NULL          |    6 | Using where |

使用or同樣不能使用索引。

再改之：

mysql> explain select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id where test1.tid < 5

    -> 

    -> union all

    -> select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id where test1.tid > 5;

+----+--------------+------------+--------+----------------+---------+---------+---------------+------+-------------+

| id | select_type  | table      | type   | possible_keys  | key     | key_len | ref           | rows | Extra       |

|  1 | PRIMARY      | test1      | range  | PRIMARY,IX_tid | IX_tid  | 5       | NULL          |    1 | Using where |

|  1 | PRIMARY      | test2      | eq_ref | PRIMARY        | PRIMARY | 4       | test.test1.id |    1 |             |

|  1 | PRIMARY      | test3      | eq_ref | PRIMARY        | PRIMARY | 4       | test.test1.id |    1 |             |

|  2 | UNION        | test1      | range  | PRIMARY,IX_tid | IX_tid  | 5       | NULL          |    1 | Using where |

|  2 | UNION        | test2      | eq_ref | PRIMARY        | PRIMARY | 4       | test.test1.id |    1 |             |

|  2 | UNION        | test3      | eq_ref | PRIMARY        | PRIMARY | 4       | test.test1.id |    1 |             |

| NULL | UNION RESULT | <union1,2> | ALL    | NULL           | NULL    | NULL    | NULL          | NULL |             |

7 rows in set (0.01 sec)

使用到了索引。

name字段裡含有??特殊字元的不顯示出來：

mysql> explain select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id where test1.name not like '%?%'; 

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |

|  1 | SIMPLE      | test1 | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    6 | Using where |

|  1 | SIMPLE      | test2 | eq_ref | PRIMARY       | PRIMARY | 4       | test.test1.id |    1 |             |

|  1 | SIMPLE      | test3 | eq_ref | PRIMARY       | PRIMARY | 4       | test.test1.id |    1 |             |

mysql> 

mysql> explain select test1.id,test1.name,test2.id,test2.name,test3.id,test3.name from test1 join test2 on test1.id=test2.id join test3 on test1.id=test3.id where test1.name not like '?%'; 

not like在MYSQL裡是不能使用索引的。

沒辦法隻能這樣變通一下

mysql> select test1.id,(case when test1.name like '%?%' then '' else test1.name end) as name,test2.id,test2.name,test3.id,test3.name from test1 left join test2 on test1.id=test2.id left join test3 on test1.id=test3.id order by test1.id;

+----+------+------+------+------+------+

| id | name | id   | name | id   | name |

|  1 | a    |    1 | aaa  |    1 | aa   |

|  2 | b    |    2 | bbb  |    2 | bb   |

|  3 |      |    3 | ccc  |    3 | cc   |

|  4 |      |    4 | ddd  |    4 | dd   |

|  5 | e    |    5 | eee  |    5 | ee   |

|  6 | f    |    6 | fff  |    6 | ff   |

6 rows in set (0.01 sec)

mysql> explain select test1.id,(case when test1.name like '%?%' then '' else test1.name end) as name,test2.id,test2.name,test3.id,test3.name from test1 left join test2 on test1.id=test2.id left join test3 on test1.id=test3.id order by test1.id;

|  1 | SIMPLE      | test1 | index  | NULL          | PRIMARY | 4       | NULL          |    6 |       |

 本文轉自 liang3391 51CTO部落格，原文連結:http://blog.51cto.com/liang3391/822676