mysql优化-使用sql提示

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