多表连接索引的问题

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;    
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
|  1 | SIMPLE      | test1 | index  | PRIMARY       | PRIMARY | 4       | 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.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 |             |
+----+-------------+-------+--------+----------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

<>不等于在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;   
+----+-------------+-------+--------+----------------+---------+---------+---------------+------+-------------+
| 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 |             |
+----+-------------+-------+--------+----------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

使用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)

使用到了索引。

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

+----+-------+----+------+----+------+
| 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   |
+----+-------+----+------+----+------+

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 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

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 '?%'; 
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| 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 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

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;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
|  1 | SIMPLE      | test1 | index  | NULL          | PRIMARY | 4       | 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)

 

 本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/822676