Percona MySQL 5.6 HINT介紹
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)
會強制將查詢結果放入一張臨時表中。當消耗很長時間來講結果集傳送到客戶端時，這有助於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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2123778/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 5.6 global read lock 介紹MySql
- MySQL5.6 windows msi安裝介紹MySqlWindows
- Oracle 常用HINT介紹Oracle
- percona之tcprstat 介紹TCP
- MySQL 5.6授權表的內容用途介紹MySql
- MySQL5.6版本的新特性介紹MySql
- Percona MySQL 5.6 RPM包方式安裝流程MySql
- percona之複製工具介紹
- Percona MySQL 5.6 WHERE 條件中 OR 的索引測試MySql索引
- MySQL5.6中的常用函式詳細用法介紹MySql函式
- MySQL 5.6 Table cache 簡介MySql
- Percona MySQL 5.6 配置InnoDB優化器永久統計資訊MySql優化
- Sql最佳化(五) hint（提示）介紹SQL
- percona-toolkit效能類工具的使用介紹
- percona之資料庫開發工具介紹資料庫
- percona server5.6新功能記錄Server
- percona 實用工具之pt-kill使用介紹
- percona之資料庫資訊統計工具介紹資料庫
- MySQL MVCC介紹MySqlMVC
- mysql MVCC 介紹MySqlMVC
- MySQL索引介紹MySql索引
- MySQL MHA介紹MySql
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- Percona 5.6 thread pool說明及使用thread
- PXC(Percona XtraDB Cluster)特性和優缺點介紹
- LAMP架構介紹、MYSQL介紹、安裝LAMP架構MySql
- LAMP架構（LAMP介紹，mysql/mariaDB介紹，Mysql安裝）LAMP架構MySql
- MySQL Percona XtraBackupMySql
- MySQL檢視介紹MySql
- MySQL 安裝介紹MySql
- MySQL Binlog 介紹MySql
- MySQL分支版本介紹MySql
- MySQL group replication介紹MySql
- 【MySQL】mydumper工具介紹MySql
- 【Mysql】index extensions介紹MySqlIndex
- MySQL分割槽介紹MySql
- MySQL SQL模式介紹MySql模式
- MySQL 5.5 mysqlimport介紹MySqlImport