SQL_BUFFER_RESULT
会强制将查询结果放入一张临时表中。当消耗很长时间来讲结果集发送到客户端时，这有助于MySQL尽早释放表锁。这个提示只用在最外层的SELECT语句，而不适用于子查询或UNION语句。

mysql> explain select * from test;
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | test | index | NULL | idx_test_id_name | 23 | NULL | 5 | Using index |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select SQL_BUFFER_RESULT * from test;
+----+-------------+-------+-------+---------------+------------------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+------------------------------+
| 1 | SIMPLE | test | index | NULL | idx_test_id_name | 23 | NULL | 5 | Using index; Using temporary |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+------------------------------+
1 row in set (0.00 sec)


STRAIGHT_JOIN
会强制优化器按照FROM后面表的顺序来做连接。如果优化器以不恰当的顺序来连接表，可以使用这个提示来加速查询的速度。STRAIGHT_JOIN提示不会应用到执行计划中类型为const或system的表。

mysql> explain select e.* from emp e join dept d on e.deptno=d.deptno;
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | d | index | PRIMARY | PRIMARY | 4 | NULL | 5 | Using index |
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> explain select STRAIGHT_JOIN e.* from emp e join dept d on e.deptno=d.deptno;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | test.e.deptno | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
2 rows in set (0.00 sec)


USE INDEX
告诉MySQL使用指定的索引。当MySQL使用了错误的索引时，这个提示会很有用。

mysql> show keys from test;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 1 | idx_test_id_name | 1 | id | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_id_name | 2 | name | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_id | 1 | id | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> explain select count(*) from test;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | test | index | NULL | idx_test_id | 5 | NULL | 5 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from test use index (idx_test_name);
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | test | index | NULL | idx_test_name | 18 | NULL | 5 | Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from test use index (idx_test_id_name);
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | test | index | NULL | idx_test_id_name | 23 | NULL | 5 | Using index |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)


IGNORE INDEX
告诉MySQL不要使用指定的索引。当MySQL使用了错误的索引时，这个提示会很有用。

mysql> show keys from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept | 0 | PRIMARY | 1 | deptno | A | 5 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> explain select deptno from dept;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | dept | index | NULL | PRIMARY | 4 | NULL | 5 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select deptno from dept ignore index (PRIMARY);
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | dept | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)


FORCE INDEX
和USE INDEX相似。这个提示会让查询一直使用索引，除非表的查询条件无法使用表中的索引。

mysql> show keys from buy_log;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buy_log | 1 | userid | 1 | userid | A | 7 | NULL | NULL | | BTREE | | |
| buy_log | 1 | userid_2 | 1 | userid | A | 7 | NULL | NULL | | BTREE | | |
| buy_log | 1 | userid_2 | 2 | buy_date | A | 7 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from buy_log force index(userid) where userid=1;
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------+
| 1 | SIMPLE | buy_log | ref | userid | userid | 4 | const | 4 | NULL |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from buy_log force index(userid_2) where userid=1;
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | buy_log | ref | userid_2 | userid_2 | 4 | const | 4 | Using index |
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> show keys from emp;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | |
| emp | 1 | idx_emp_deptno | 1 | deptno | A | 7 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain select * from emp e force index(PRIMARY) join dept d on e.deptno=d.deptno;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | NULL |
| 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select * from emp e force index(idx_emp_deptno) join dept d on e.deptno=d.deptno;
+----+-------------+-------+------+----------------+----------------+---------+---------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+----------------+---------+---------------+------+-------+
| 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL |
| 1 | SIMPLE | e | ref | idx_emp_deptno | idx_emp_deptno | 5 | test.d.deptno | 2 | NULL |
+----+-------------+-------+------+----------------+----------------+---------+---------------+------+-------+
2 rows in set (0.00 sec)

mysql> show keys from test;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 1 | idx_test_id_name | 1 | id | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_id_name | 2 | name | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_id | 1 | id | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> explain select * from test where id > 20;
+----+-------------+-------+-------+------------------------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------------+------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | test | range | idx_test_id_name,idx_test_id | idx_test_id_name | 5 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+------------------------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from test use index (idx_test_id) where id > 20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | idx_test_id | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test force index (idx_test_id) where id > 20;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | test | range | idx_test_id | idx_test_id | 5 | NULL | 3 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from test force index (idx_test_name) where id > 20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)