mysql index hint 在index不存在时的处理

关于index_hint

在mysql查询语句中可以通过指定index_hint来告诉优化器如何使用索引，详细可以参考这里

index_hint:

    USE {INDEX|KEY}

      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

  | IGNORE {INDEX|KEY}

      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

  | FORCE {INDEX|KEY}

      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

问题

查询语句中使用index_hint时，如果index_hint指定的索引不存在，那么服务器会返回错误ERROR 1176 (42000)。可以看下面的例子

drop table t1;

create table t1(id int auto_increment, a char(2), primary key (id), key idx1(a)) engine=innodb;

insert into t1 values (1,'ab');

select * from t1 force index (idx1) where a='ab';

+----+------+

| id | a    |

+----+------+

|  1 | ab   |

+----+------+

1 row in set (0.00 sec)

alter table t1 drop key idx1;

select * from t1 force index (idx1) where a='ab';

ERROR 1176 (42000): Key 'idx1' doesn't exist in table 't1'

在实际应用中，如果查询语句使用index_hint指定了索引，随着业务的发展，这个索引能变得多余了，但删除这个索引会导致应用报错。于是要么保留着个无用的索引，要么修改sql重新发布应用。这两个做法都不太友好。

改进

如果index_hint指定的索引不存在，服务器不返回错误，而是选择报warining,那么删除这个索引就不会导致应用报错。

改进：

新增sql_mode的类型INDEX_HINE_ERROR;

当sql_mode设置了INDEX_HINE_ERROR类型，如果index_hint指定的索引不存在，服务器返回错误。

当sql_mode没有设置INDEX_HINE_ERROR类型，如果index_hint指定的索引不存在，服务器不返回错误，而是选择报warining

看下面的例子：

1）sql_mode没有设置INDEX_HINE_ERROR类型

set sql_mode='';

select * from t1 force index (idx1) where a='ab';

+----+------+

| id | a    |

+----+------+

|  1 | ab   |

+----+------+

1 row in set, 1 warning (0.00 sec)

show warnings;

+---------+------+----------------------------------------+

| Level   | Code | Message                                |

+---------+------+----------------------------------------+

| Warning | 1176 | Key 'idx1' doesn't exist in table 't1' |

+---------+------+----------------------------------------+

1 row in set (0.00 sec)

explain select * from t1 force index (idx1) where a='ab';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set, 1 warning (0.00 sec)

show warnings;

+---------+------+----------------------------------------+

| Level   | Code | Message                                |

+---------+------+----------------------------------------+

| Warning | 1176 | Key 'idx1' doesn't exist in table 't1' |

+---------+------+----------------------------------------+

1 row in set (0.00 sec)

2）sql_mode设置了INDEX_HINE_ERROR类型

set sql_mode='index_hint_error';

select * from t1 force index (idx1) where a='ab';

ERROR 1176 (42000): Key 'idx1' doesn't exist in table 't1'

explain select * from t1 force index (idx1) where a='ab';

ERROR 1176 (42000): Key 'idx1' doesn't exist in table 't1'