MySQL索引提示

MySQL索引提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。MySQL数据库支持索引提示(INDEX HINT)显式的告诉优化器使用了哪个索引。有以下几种情况可能用到索引提示：

1、MySQL数据库的优化器错误的选择了某个索引，导致SQL运行很慢。这个在情况比较少见。优化器在绝大部分情况下工作的非常有效和正确。

2、某些SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。例如优化器分析Range查询本身就是比较耗时的操作。这时DBA或开发人员分析最优的索引选择，通过index hint来强制使优化器不进行各个路径的成本分析直接选择指定的索引来完成查询。

index hint语法

tbl_name [[AS] alias] [index_hint_list]
index_hint_list:
index_hint [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_list:
index_name [, index_name] ..

 index hint种类

MySql共有三种索引提示，分别是：USE INDEX、IGNORE INDEX和FORCE INDEX，他们之间的区别是

1、use index：use index告诉MySql用列表中的其中一个索引去做本次查询，就可以让MySQL不再考虑其他可用的索引建议MySQL用这些索引，但是MySQL不一定会用。

2、ignore index：ignore index告诉mysql不要使用某些索引去做本次查询

3、force index：强制MySQL使用一个特定的索引

4、上边三种提示也可以合理地混合使用

index hint用途

可以在索引提示的后边使用FOR语句指定提示的范围，索引提示共有三种适用范围，分别是FOR JOIN、FOR ORDER BY、FOR GROUP BY：

1、FOR JOIN：索引提示用于查找行或者用于表的连接

2、FOR ORDER BY：索引提示用于排序

3、FOR GROUP BY：索引提示用于分组

注意，如果在mySQL 5.0版本及以下，如果不指定FOR语句，那么mySQL只会用它来查找行；而在新版本的mySQL，如果不指定FOR语句，那么mySQL会把索引用于所有用途。

本文测试用到的sql

CREATE TABLE `emp` (
    `id` INT ( 11 ) NOT NULL,
    `name` VARCHAR ( 10 ) DEFAULT NULL,
    `address` VARCHAR ( 30 ) DEFAULT NULL,
    `password` VARCHAR ( 30 ) DEFAULT NULL,
    `city` VARCHAR ( 10 ) DEFAULT NULL,
    PRIMARY KEY ( `id` ),
    KEY `idx_emp_name` ( `name` ),
    KEY `idx_emp_addr` ( `address` ),
    KEY `idx_emp_passwd` ( `password` ),
KEY `idx_emp_name_addr_passwd` ( `name`, `address`, `password` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `emp` VALUES (1, '马云', 'mayun@ali.com', 'dsklcml@#12334235', '北京');
INSERT INTO `emp` VALUES (2, '马化腾', 'mahuateg@qq.com', 'dwdewif@4325345', '杭州');
INSERT INTO `emp` VALUES (3, '张一鸣', 'zhangyiming@bytedance.com', 'csdc#dwq11452Wew', '北京');
INSERT INTO `emp` VALUES (4, '李彦宏', 'liyanhong@baidu.com', 'wcvcwf3532sd2ddfas', '北京');
INSERT INTO `emp` VALUES (5, '任正非', 'renzhengfeui@huawei.com', 'cacrete*&2s2qdwq2', '北京');