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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PXC(Percona XtraDB Cluster)的缺點介紹
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- percona 實用工具之pt-kill使用介紹
- MySql介紹MySql
- Unity3D 5.6 剛體的介紹,---rigidbodyUnity3D
- MySQL Binlog 介紹MySql
- MySQL MVCC介紹MySqlMVC
- mysql MVCC 介紹MySqlMVC
- MySQL索引介紹MySql索引
- percona-toolkit安裝方法和主要工具用途彙總介紹
- MySQL group replication介紹MySql
- MySQL 安裝介紹MySql
- MySQL檢視介紹MySql
- MySQL 官方工具utilities介紹MySql
- MySQL 8.0 目錄介紹MySql
- MySQL MRR和ICP介紹MySql
- MySQL 8.0 新增特性介紹MySql
- MySQL觸發器介紹MySql觸發器
- MySql主從同步介紹MySql主從同步
- MySQL角色(role)功能介紹MySql
- MySQL 教程基礎介紹MySql
- MySQL資料庫鎖介紹MySql資料庫
- MySQL 延遲從庫介紹MySql
- mysql中SQL的概念介紹MySql
- MySQL審計外掛介紹MySql
- MySQL：5.6 升級 5.7MySql
- CentOS 安裝 mysql 5.6CentOSMySql
- Windows 安裝 MySQL 5.6WindowsMySql
- mysql，mariaDB，Percona Server，MongoDB，Redis，RocksDBMySqlServerMongoDBRedis
- 【MySQL】二、Innodb 恢復工具介紹MySql
- MySQL版本發展歷史介紹MySql
- MySQL Undo Log和Redo Log介紹MySql
- MySQL儲存引擎入門介紹MySql儲存引擎
- MySQL連線控制外掛介紹MySql
- MySQL中undo log介紹及清理MySql
- 重新整理 mysql 基礎篇————— 介紹mysql[一]MySql
- Linux安裝Mysql5.6LinuxMySql
- MySQL 5.6 GTID 原理以及使用MySql
- MySQL 5.6的表壓縮MySql