MySQL社区

标题: 合并索引-MySQL5.5与MySQL5.6中索引的区别 [打印本页]
作者: squall    时间: 2012-12-19 11:17
标题: 合并索引-MySQL5.5与MySQL5.6中索引的区别
本帖最后由 squall 于 2012-12-19 11:19 编辑

用个例子说明一下，表的结构：
  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)

  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)

  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)

复制代码
在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)

  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的索引及性能问题。






欢迎光临 MySQL社区 (http://www.mysqlpub.com/) Powered by Discuz! X3.2