合并索引-MySQL5.5与MySQL5.6中索引的区别

squall

用个例子说明一下，表的结构：

1. mysql> show create table t\G;
   
2. *************************** 1. row ***************************
   
3. Table: t
   
4. Create Table: CREATE TABLE `t` (
   
5. `a` int(10) unsigned DEFAULT NULL,
   
6. `b` int(10) unsigned DEFAULT NULL,
   
7. KEY `i_t_a` (`a`),
   
8. KEY `i_t_b` (`b`)
   
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
   
10. 1 row in set (0.00 sec)
    

复制代码

在MySQL5.5中的表现：

1. mysql> select version();
   
2. +-------------------------------------------+
   
3. | version() |
   
4. +-------------------------------------------+
   
5. | 5.5.20-enterprise-commercial-advanced-log |
   
6. +-------------------------------------------+
   
7. 1 row in set (0.00 sec)
   
8. 
   
9. mysql> explain select * from t where a=1 or b=10;
   
10. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    
11. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    
12. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    
13. | 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 40 | Using where |
    
14. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    
15. 1 row in set (0.00 sec)
    
16. 
    
17. 可优化成：
    
18. mysql> explain select * from t where a=1 union all select * from t where b=10;
    
19. +----+--------------+------------+------+---------------+-------+---------+-------+------+---------+
    
20. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    
21. +----+--------------+------------+------+---------------+-------+---------+-------+------+---------+
    
22. | 1 | PRIMARY | t | ref | i_t_a | i_t_a | 5 | const | 8 | Using where |
    
23. | 2 | UNION | t | ref | i_t_b | i_t_b | 5 | const | 3 | Using where |
    
24. | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
    
25. +----+--------------+------------+------+---------------+-------+---------+-------+------+------+
    
26. 3 rows in set (0.00 sec)
    
27. 
    

复制代码

在MySQL5.6中的表现：

1. mysql> select version();
   
2. +--------------+
   
3. | version() |
   
4. +--------------+
   
5. | 5.6.6-m9-log |
   
6. +--------------+
   
7. 1 row in set (0.02 sec)
   
8. 
   
9. mysql> explain select * from t where a=1 or b=10;
   
10. +----+-------------+-------+-------------+---------------+---------+---------+------+------+-----+
    
11. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    
12. +----+-------------+-------+-------------+--------+-------------+---------+------+------+---------+
    
13. | 1 | SIMPLE | t | index_merge | i_t_a,i_t_b | i_t_a,i_t_b | 5,5 | NULL | 11 | Using union(i_t_a,i_t_b); Using where |
    
14. +----+-------------+-------+-------------+-------+-------------+---------+------+------+-----+
    
15. 1 row in set (0.03 sec)
    

复制代码

结论，升级到5.6的时候要考虑SQL的索引及性能问题。