MySQL index hints 使用

神諭丶發表於2016-03-15
MySqlIndex
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章