ITPub博客

首页 > 数据库 > MySQL > MySQL index hints 使用

MySQL index hints 使用

原创 MySQL 作者:神谕丶 时间:2016-03-15 17:11:31 0 删除 编辑
index hints可以人为的告诉mysql优化器要如何完成select,比如强制走某(些)索引或忽略某(些)索引。
其中,被指定的索引必须要有索引名。

官方提供的基础语法:
index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)


其中index_list为索引名。


测试用表:
  1. mysql> CREATE TABLE hints_test(col1 int, 
  2.    -> col2 int, 
  3.    -> col3 int, 
  4.    -> KEY idx_1(col1),
  5.    -> KEY idx_2(col2),
  6.    -> KEY idx_3(col3)); 
  7. Query OK, 0 rows affected (0.09 sec)


随机插入一些数据:
  1. mysql> SELECT * FROM hints_test;
  2. +------+------+------+
  3. | col1 | col2 | col3 |
  4. +------+------+------+
  5. |    1 |    2 |    3 |
  6. |    2 |    2 |    3 |
  7. |    2 |    3 |    3 |
  8. |    3 |    3 |    5 |
  9. |    3 |    1 |    2 |
  10. |    2 |    1 |    1 |
  11. |    2 |    3 |    3 |
  12. |    4 |    4 |    4 |
  13. |    6 |    5 |    3 |
  14. +------+------+------+
  15. 9 rows in set (0.00 sec)

正常select(注,explain结果部分省略,下同)
  1. mysql> EXPLAIN SELECT col1, col2, col3 
  2.     -> FROM hints_test 
  3.     -> WHERE col1=1 AND col2=2\G
  4. *************************** 1. row ***************************
  5.         table: hints_test
  6.          type: ref
  7. possible_keys: idx_1,idx_2
  8.           key: idx_1
  9.       key_len: 5
  10.           ref: const


加一个复合索引
  1. mysql> ALTER TABLE hints_test ADD INDEX idx_1_2(col1,col2);
  2. Query OK, 0 rows affected (0.57 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0


①指定使用idx_1_2索引
  1. mysql> EXPLAIN SELECT col1, col2, col3 
  2.     -> FROM hints_test 
  3.     -> USE INDEX (idx_1_2) 
  4.     -> WHERE col1=1 AND col2=2\G
  5. *************************** 1. row ***************************
  6.         table: hints_test
  7.          type: ref
  8. possible_keys: idx_1_2
  9.           key: idx_1_2
  10.       key_len: 10
  11.           ref: const,const


②忽略目前表中的三个索引
  1. mysql> EXPLAIN SELECT col1, col2, col3 
  2.     -> FROM hints_test 
  3.     -> IGNORE INDEX (idx_1_2,idx_1,idx_2) 
  4.     -> WHERE col1=1 AND col2=2\G  
  5. *************************** 1. row ***************************
  6.           table: hints_test
  7.            type: ALL
  8.   possible_keys: NULL
  9.             key: NULL
  10.         key_len: NULL
  11.             ref: NULL


再次正常select:
此时col2走了索引,而order by col3没有走索引。

  1. mysql> EXPLAIN SELECT col1, col2, col3  
  2.     -> FROM hints_test 
  3.     -> WHERE col2=2 ORDER BY col3\G        
  4. *************************** 1. row ***************************
  5.           table: hints_test
  6.            type: ref
  7.   possible_keys: idx_2
  8.             key: idx_2
  9.         key_len: 5
  10.             ref: const

③忽略idx_2索引,此时全表扫描:
  1. mysql> EXPLAIN SELECT col1, col2, col3 
  2.     -> FROM hints_test 
  3.     -> IGNORE INDEX (idx_2) 
  4.     -> WHERE col2=2 
  5.     -> ORDER BY col3\G                            
  6. *************************** 1. row ***************************
  7.          table: hints_test
  8.           type: ALL
  9.  possible_keys: NULL
  10.            key: NULL
  11.        key_len: NULL
  12.            ref: NULL


④强制对order by语句使用idx_3索引:
  1. mysql> EXPLAIN SELECT col1, col2, col3 FROM hints_test 
  2.     -> FORCE INDEX FOR ORDER BY (idx_3) 
  3.     -> IGNORE INDEX (idx_2) 
  4.     -> WHERE col2=2 
  5.     -> ORDER BY col3\G
  6. *************************** 1. row ***************************
  7.           table: hints_test
  8.            type: index
  9.   possible_keys: NULL
  10.             key: idx_3
  11.         key_len: 5
  12.             ref: NULL


同理,除了WHERE和ORDER BY,可以同样对GROUP BY、JOIN操作进行USE、IGNORE、FORCE三种HINTS。
写法为:
(USE, FORCE, IGNORE) and by scope (FOR JOIN, FOR ORDER BY, FOR GROUP BY). 



参考文档:
MySQL 5.6 Reference Manual 13.2.9.3 Index Hint Syntax




作者微信公众号(持续更新)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29773961/viewspace-2057752/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
微信公众号:fantasy-life-

注册时间:2014-07-28

  • 博文量
    159
  • 访问量
    976193