MariaDB社区

标题: 合并索引-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)

  8. mysql> explain select * from t where a=1 or b=10;
  9. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  10. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  11. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  12. | 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 40 | Using where |
  13. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  14. 1 row in set (0.00 sec)

  15. 可优化成:
  16. mysql> explain select * from t where a=1 union all select * from t where b=10;
  17. +----+--------------+------------+------+---------------+-------+---------+-------+------+---------+
  18. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  19. +----+--------------+------------+------+---------------+-------+---------+-------+------+---------+
  20. | 1 | PRIMARY | t | ref | i_t_a | i_t_a | 5 | const | 8 | Using where |
  21. | 2 | UNION | t | ref | i_t_b | i_t_b | 5 | const | 3 | Using where |
  22. | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
  23. +----+--------------+------------+------+---------------+-------+---------+-------+------+------+
  24. 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)

  8. mysql> explain select * from t where a=1 or b=10;
  9. +----+-------------+-------+-------------+---------------+---------+---------+------+------+-----+
  10. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  11. +----+-------------+-------+-------------+--------+-------------+---------+------+------+---------+
  12. | 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 |
  13. +----+-------------+-------+-------------+-------+-------------+---------+------+------+-----+
  14. 1 row in set (0.03 sec)
复制代码
结论,升级到5.6的时候要考虑SQL的索引及性能问题。







欢迎光临 MariaDB社区 (http://123.56.88.72/) Powered by Discuz! X3.2