Mysql版本：5.7
表引擎：Innodb
表结构：
创建索引：
alter table USER_INFO add index userindex (USER_NAME)
查询：
①
explain select id from USER_INFO where USER_NAME = 'wuzihan'
②
explain select * from USER_INFO WHERE `PASSWORD` = 'qtwzh1999' and USER_NAME = 'wuzihan'
③
explain select * from USER_INFO WHERE USER_NAME = 'wuzihan' or `PASSWORD` = 'qtwzh1999'
④
explain select * from USER_INFO where USER_NAME = 'wuzihan' AND ID='1' （id为主键）
⑤
explain select * from USER_INFO where USER_NAME = 'wuzihan' or ID='1'
结论：
若where条件有索引则会使用索引
若where条件中的AND两边都有索引则会使用先创建的索引
若where条件中有主键则会优先使用主键索引
若where条件中有or且or两边有一边没有建立索引则不会使用索引
若where条件中有or且or两边都建立索引则会使用索引合并（index_merge）
增加索引：
alter table USER_INFO add index password (`PASSWORD`)
①
explain select USER_NAME from USER_INFO where `PASSWORD` = 'qtwzh1999'
②
explain select password from USER_INFO where `PASSWORD` = 'qtwzh1999' AND USER_NAME = 'wuzihan'
③
explain select * from USER_INFO WHERE `PASSWORD` = 'qtwzh1999' or USER_NAME = 'wuzihan'
④
explain select * from USER_INFO WHERE `PASSWORD` = 'qtwzh1999' AND ID = '1' and USER_NAME='wuzihan'
若索引字段参与计算：
增加索引：
alter table USER_INFO ADD INDEX status (`STATUS`)
①explain select * from USER_INFO WHERE id BETWEEN 1 and 10
②explain select * from USER_INFO WHERE status BETWEEN 1 and 10
③explain select * from USER_INFO WHERE LENGTH(id)>1
④explain select * from USER_INFO WHERE status BETWEEN 1 and 10 and `status` = 1
⑤explain select * from USER_INFO WHERE status BETWEEN 1 and 10 and `password` = 1
⑥explain select * from USER_INFO WHERE status BETWEEN 1 and 10 and `id` = 1
⑦explain select * from USER_INFO WHERE status BETWEEN 1 and 10 or `id` = 1
结论：
若非主键索引参与计算则不会使用索引①，
若主键参与计算则有可能使用索引②③，
若索引参与计算且用and连接非主键索引字段则不会使用索引⑤，
若索引参与计算且用and连接主键索引则会使用主键索引⑥，
若索引参与计算且用or连接则不会使用索引⑦
删除所有索引，建立联合索引：
alter table USER_INFO ADD INDEX CONBATINDEX(STATUS,ROLE)
①
explain select USER_NAME from USER_INFO WHERE STATUS = 1
②
explain select USER_NAME from USER_INFO WHERE role = 1
③
explain select USER_NAME from USER_INFO WHERE role = 1 and status = 1
④
explain select USER_NAME from USER_INFO WHERE role = 1 or status = 1
⑤
explain select USER_NAME from USER_INFO WHERE status = 1 or role = 1
索引下推：
