Shulou(Shulou.com)05/31 Report--

What is Percona MySQL 5.6HINT? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

SQL_BUFFER_RESULT

The query results are forced into a temporary table. This helps MySQL release the table lock as soon as possible when it takes a long time for the result set to be sent to the client. This hint applies only to the outermost SELECT statement, not to subqueries or UNION statements.

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

Forces the optimizer to join in the order of the tables that follow the FROM. If the optimizer joins the tables in the wrong order, you can use this hint to speed up the query. The STRAIGHT_JOIN hint does not apply to tables of type const or system in the execution plan.

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

Tells MySQL to use the specified index. This prompt is useful when MySQL uses the wrong index.

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

Tells MySQL not to use the specified index. This prompt is useful when MySQL uses the wrong index.

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

Similar to USE INDEX. This prompt keeps the query using the index unless the query condition of the table cannot use the index in the table.

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)

After reading the above, have you mastered what Percona MySQL 5.6HINT is? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.