使用 SQL 提示

使用 SQL 提示

​ 强制使用临时表 SQL_BUFFER_RESULT ，当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT。选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。代价是服务器端需要更多的内存。

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE … 

​ 1、未指定索引

mysql> desc select count(*) from rental \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_staff_id
      key_len: 1
          ref: NULL
         rows: 16070
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

​ 2、在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其它可用的索引，但不是一定使用该索引。

mysql> desc select count(*) from rental use index(idx_rental_date) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_rental_date
      key_len: 10
          ref: NULL
         rows: 16070
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

​ 3、如果想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。

mysql> desc select count(*) from rental ignore index(idx_fk_staff_id) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 16070
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

​ 4、强制 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
   partitions: NULL
         type: ALL
possible_keys: idx_fk_inventory_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16070
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

#尝试使用 use index , MySQL 还是选择走全表扫描。
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
   partitions: NULL
         type: ALL
possible_keys: idx_fk_inventory_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16070
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)


#使用 FORCE INDEX 进行提示时，即便使用索引的效率不是最高， MySQL 还是选择使用了索引。
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
   partitions: NULL
         type: range
possible_keys: idx_fk_inventory_id
          key: idx_fk_inventory_id
      key_len: 3
          ref: NULL
         rows: 8035
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)