MySQL 使用SQL提示

最新推荐文章于 2024-04-02 17:27:30 发布

weixin_34148340

最新推荐文章于 2024-04-02 17:27:30 发布

阅读量163

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/lvhuizhenblog/blog/553002

版权

2019独角兽企业重金招聘Python工程师标准>>>

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》

转载于:https://my.oschina.net/lvhuizhenblog/blog/553002