mysql优化-使用sql提示

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

原创最新推荐文章于 2024-04-02 17:27:30 发布 · 276 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

41 篇文章

订阅专栏

本文详细介绍了如何通过SQL提示如USE INDEX, IGNORE INDEX和FORCE INDEX来优化数据库查询的执行计划，提升查询效率。通过具体示例，展示了这些技巧在实际应用中的效果。

sql提示是优化数据库的值中手段；
就是加入一些人为的提示要达到优化的目的；

原始的执行计划：

mysql> explain select count(1) from  material_data;
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | material_data | NULL       | index | NULL          | DIRES_ID | 5       | NULL | 446372 |      100 | Using index |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
1 row in set

USE index:使用指定索引，不在考虑其他索引

mysql> explain select count(1) from  material_data use index(idx_material_code);
+----+-------------+---------------+------------+-------+---------------+-------------------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key               | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+-------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | material_data | NULL       | index | NULL          | idx_material_code | 99      | NULL | 446372 |      100 | Using index |
+----+-------------+---------------+------------+-------+---------------+-------------------+---------+------+--------+----------+-------------+
1 row in set

IGNORE INDEX:忽略一个，多个索引

mysql> explain select count(1)  from  material_data  IGNORE INDEX(DIRES_ID);
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key        | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | material_data | NULL       | index | NULL          | idx_status | 9       | NULL | 446372 |      100 | Using index |
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
1 row in set

FORCE INDEX:使mysql强制使用指定的索引

mysql> explain select count(1)  from  material_data  FORCE INDEX(idx_material_code);
+----+-------------+---------------+------------+-------+---------------+-------------------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key               | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+-------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | material_data | NULL       | index | NULL          | idx_material_code | 99      | NULL | 446372 |      100 | Using index |
+----+-------------+---------------+------------+-------+---------------+-------------------+---------+------+--------+----------+-------------+
1 row in set