mysql sql提示_MySQL 使用SQL提示

SQL提示(SQL HINT)是优化数据库的一种重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

下面是一个使用SQL提示的例子：

SELECT   SQL_BUFFER_RESULTS * FROM ……

这个语句将强制MySQL 生成一个临时结果集。只要临时结果集生成后，所有表上的锁定均被释放。这能在遇到锁问题时或者更长时间将结果传给客户端时有所帮助，因为可以尽快释放锁资源。

下面是一些在MySQL中常用的SQL提示。

USE INDEX

在查询语句中表名的后面，添加USE INDEX 来提供希望MySQL 去参考的索引列表，就可以让MySQL 不在考虑其他可用的索引。

mysql> show index from rental;

+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table  | Non_unique | Key_name            | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| rental |          0 | PRIMARY             |            1 | rental_id    | A         |       16008 |     NULL | NULL   |      | BTREE      |         |               |

| rental |          0 | rental_date         |            1 | rental_date  | A         |       16008 |     NULL | NULL   |      | BTREE      |         |               |

| rental |          0 | rental_date         |            2 | inventory_id | A         |       16008 |     NULL | NULL   |      | BTREE      |         |               |

| rental |          0 | rental_date         |            3 | customer_id  | A         |       16008 |     NULL | NULL   |      | BTREE      |         |               |

| rental |          1 | idx_fk_inventory_id |            1 | inventory_id | A         |       16008 |     NULL | NULL   |      | BTREE      |         |               |

| rental |          1 | idx_fk_customer_id  |            1 | customer_id  | A         |        1231 |     NULL | NULL   |      | BTREE      |         |               |

| rental |          1 | idx_fk_staff_id     |            1 | staff_id     | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |

+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

7 rows in set (0.00 sec)

mysql>

mysql> explain select count(*) from rental use index(rental_date);

+----+-------------+--------+-------+---------------+-------------+---------+------+-------+-------------+

| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows  | Extra       |

+----+-------------+--------+-------+---------------+-------------+---------+------+-------+-------------+

|  1 | SIMPLE      | rental | index | NULL          | rental_date | 10      | NULL | 16008 | Using index |

+----+-------------+--------+-------+---------------+-------------+---------+------+-------+-------------+

1 row in set (0.00 sec)

2.  IGNORE INDEX

如果用户只是单独地想让MySQL忽略一个或者多个索引，则可以用IGNORE INDEX 作为 HINT。

mysql> explain select count(*) from rental;

+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+-------------+

| id | select_type | table  | type  | possible_keys | key             | key_len | ref  | rows  | Extra       |

+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+-------------+

|  1 | SIMPLE      | rental | index | NULL          | idx_fk_staff_id | 1       | NULL | 16008 | Using index |

+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+-------------+

1 row in set (0.00 sec)

mysql> explain select count(*) from rental ignore index (idx_fk_staff_id);

+----+-------------+--------+-------+---------------+--------------------+---------+------+-------+-------------+

| id | select_type | table  | type  | possible_keys | key                | key_len | ref  | rows  | Extra       |

+----+-------------+--------+-------+---------------+--------------------+---------+------+-------+-------------+

|  1 | SIMPLE      | rental | index | NULL          | idx_fk_customer_id | 2       | NULL | 16008 | Using index |

+----+-------------+--------+-------+---------------+--------------------+---------+------+-------+-------------+

1 row in set (0.00 sec)

从执行计划可以看出，系统忽略了指定的索引idx_fk_staff_id, 使用索引idx_fk_customer_id

3. FORCE INDEX

为强制MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为HINT。

例如，当不强制使用索引的时候，因为大部分库存inventory_id 的值都是大于1的，因此MySQL会默认使用全表扫描，而 不 使用索引，如下所示：

mysql> explain select * from rental where inventory_id >1\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: rental

type: ALL

possible_keys: idx_fk_inventory_id

key: NULL

key_len: NULL

ref: NULL

rows: 16008

Extra: Using where

1 row in set (0.00 sec)

尝试使用use index 的hint 看看：

mysql> explain select * from rental use index(idx_fk_inventory_id) where inventory_id >1\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: rental

type: ALL

possible_keys: idx_fk_inventory_id

key: NULL

key_len: NULL

ref: NULL

rows: 16008

Extra: Using where

1 row in set (0.00 sec)

发现仍然不行，MySQL还是选择走全表扫描。但是，当使用FORCE INDEX进行提示时，即使使用索引的效率不是很高，MySQL 还是选择使用索引，这是MySQL留给用户的一个自行选择执行计划的权利。

加入FORCE INDEX 提示后再次执行上面的SQL：

mysql> explain select * from rental force index(idx_fk_inventory_id) where inventory_id >1\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: rental

type: range

possible_keys: idx_fk_inventory_id

key: idx_fk_inventory_id

key_len: 3

ref: NULL

rows: 8004

Extra: Using index condition

1 row in set (0.00 sec)

学习自《深入浅出mysql》