MySQL index hints 使用

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29773961/viewspace-2057752/