暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片
首页 / MySQL Index Hints

MySQL Index Hints

原创 CuiHulong 2023-02-17
1111

Index Hint是向优化器提供有关在查询处理期间如何选择索引。这里单存的索引选择上，不更改优化器策略。合理的索引可以加快数据检索操作，常用的索引Hint简单方式，使用USE（参考使用），IGNORE（忽略），FORCE（强制）三种方式。

  • 但其语法上看，应该更精准的使用 JOIN，ORDER，GROUP 等场景。
index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

  • 索引Hint应用于SELECT和UPDATE语句。还可以用于多表DELETE语句，但不能用于单表DELETE。

  • 索引hint需要的是索引名，而不是列名，主键使用PRIMARY，其他的可以通过SHOW index语句或Information Schema STATISTICS查看。

image.png

常用方式

下面看下常用的方式。索引选择在干扰的情况下，执行计划的选择。

#1.优化器执行计划选择idx_fname索引
mysql> EXPLAIN SELECT  * FROM employees 
       WHERE  emp_no>10001 AND  birth_date>'1965-01-01' AND   first_name LIKE  'Ym%';
+----+-------------+-----------+------------+-------+--------------------------------+-----------+---------+------+------+----------+------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys                  | key       | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-----------+------------+-------+--------------------------------+-----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY,idx_fname,idx_dt_birth | idx_fname | 62      | NULL |  260 |     0.31 | Using index condition; Using where |
+----+-------------+-----------+------------+-------+--------------------------------+-----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

#2.指定PRIMARY
mysql> EXPLAIN SELECT  * FROM employees 
       USE INDEX (PRIMARY) 
       WHERE  emp_no>10001 AND  birth_date>'1965-01-01' AND   first_name LIKE  'Ym%';
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 149756 |     0.07 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

#3.忽略idx_fname索引
mysql> EXPLAIN SELECT  * FROM employees 
       IGNORE  INDEX (idx_fname) 
       WHERE  emp_no>10001 AND  birth_date>'1965-01-01' AND   first_name LIKE  'Ym%';
+----+-------------+-----------+------------+-------+----------------------+--------------+---------+------+------+----------+------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys        | key          | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-----------+------------+-------+----------------------+--------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY,idx_dt_birth | idx_dt_birth | 3       | NULL | 1879 |     5.56 | Using index condition; Using where |
+----+-------------+-----------+------------+-------+----------------------+--------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

#4.强制idx_dt_birth索引
mysql> EXPLAIN SELECT  * FROM employees 
       FORCE  INDEX (idx_dt_birth) 
       WHERE  emp_no>10001 AND  birth_date>'1965-01-01' AND   first_name LIKE  'Ym%';
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_dt_birth  | idx_dt_birth | 3       | NULL | 1879 |     3.70 | Using index condition; Using where |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

备注：可以说都按照干扰情况，选择了不同的索引。

多种组合使用场景

多个索引指定，排序，Join 指定。

#提示多个索引
mysql> EXPLAIN SELECT  * FROM employees 
       USE  INDEX (PRIMARY,idx_dt_birth)   
       WHERE  emp_no>10001 AND  birth_date>'1965-01-01' AND   first_name LIKE  'Ym%' ;
+----+-------------+-----------+------------+-------+----------------------+--------------+---------+------+------+----------+------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys        | key          | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-----------+------------+-------+----------------------+--------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY,idx_dt_birth | idx_dt_birth | 3       | NULL | 1879 |     5.56 | Using index condition; Using where |
+----+-------------+-----------+------------+-------+----------------------+--------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

#JOIN提示
mysql>EXPLAIN SELECT  a.* 
      FROM employees   as  a  USE INDEX FOR JOIN (PRIMARY),     
      dept_emp  as b USE INDEX FOR JOIN (idx_dept)   
      WHERE a.emp_no=b.emp_no;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | b     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               |   12 |   100.00 | NULL  |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.b.emp_no |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+

#ORDER BY指示
mysql> explain SELECT  a.* FROM employees   as  a  FORCE  INDEX FOR JOIN (PRIMARY)   IGNORE  INDEX FOR ORDER BY(idx_fname),     
    -> dept_emp  as b USE INDEX FOR JOIN (idx_emp)   
    -> WHERE a.emp_no=b.emp_no ORDER BY a.first_name;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | b     | NULL       | index  | idx_emp       | idx_emp | 4       | NULL               |   12 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.b.emp_no |    1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

USE&FORCE不允许同时使用

对于同一个张表来说 USE和FORCE 不能同时出现。

mysql> EXPLAIN SELECT  a.* FROM employees   as  a  
       FORCE  INDEX FOR JOIN (PRIMARY)   
       USE  INDEX FOR ORDER BY(idx_fname);
ERROR 1221 (HY000): Incorrect usage of USE INDEX and FORCE INDEX

对于FULLTEXT索引

对于全文索引提示支持，但带有For ORDER BY或For GROUP BY的索引提示被默默忽略。

mysql> EXPLAIN SELECT * 
       FROM articles USE INDEX FOR  ORDER BY (title)  IGNORE  index(PRIMARY)         
       WHERE MATCHH (title,body)  AGAINST ('database' IN NATURAL LANGUAGE MODE) 
       ORDER BY title;
+----+-------------+----------+------------+----------+---------------+-------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key   | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+-------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | articles | NULL       | fulltext | title         | title | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+-------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.00 sec)

对于DELETE语句

索引hint对于delete 语句也有要求，单表不支持，多表支持。

#单表操作不支持
mysql> EXPLAIN DELETE  FROM employees  USE INDEX (PRIMARY) 
       WHERE  emp_no=10001;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE INDEX (PRIMARY)   WHERE  emp_no=10001' at line 1

#多表操作支持
mysql> EXPLAIN DELETE  a
       FROM employees   as  a  USE INDEX FOR JOIN (PRIMARY),     
       dept_emp  as b USE INDEX FOR JOIN (idx_dept)   
       WHERE a.emp_no=b.emp_no;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | b     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               |   12 |   100.00 | NULL  |
|  1 | DELETE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.b.emp_no |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

总结

因为MySQL是索引组织表，索引优化是SQL语句常用的主要必要手段。普遍情况下执行计划都是能合理选择最优的索引，进行检索。但也有例外。

在官方Note提示后期就会弃掉，融合到Optimizer Hints里。要是在代码中使用索引Hint就要注意了。

说明：
USE INDEX、FORCE INDEX和IGNORE INDEX将在MySQL的未来版本中被弃用，并在之后的某个时间被完全删除。因为从MySQL 8.0.20开始，服务器支持索引级优化器提示JOIN_INDEX、GROUP_INDEX、ORDER_INDEX和INDEX，它们等价于并打算取代FORCE INDEX索引提示，以及NO_JOIN_INDEX、NO_GROUP_INDEX、NO_ORDER_INDEX和NO_INDEX优化器提示，它们等价于并打算取代IGNORE INDEX索引提示。

image.png

参考

https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

墨力计划mysql
最后修改时间：2023-02-17 18:06:03
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

专家专栏 | 崔虎龙（共172篇）
云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。

评论