mysql索引问题大全

创建索引
ALTER TABLE staffs ADD INDEX idx (NAME, age)
查看索引
SHOW INDEX FROM staffs
删除索引
DROP INDEX idx ON staffs
查看mysql版本
select version()

检查优化器开关配置命令:
show global variables like 'optimizer_switch' \G
或者
show global variables like 'optimizer_switch'

关于使用or索引,有很多争议,自己亲测,带有or索引就会失效

测试版本信息：mysql版本5.6.34-log,查询优化器开启了index_merge_union=on,环境:navicat
测试所创建索引字段：ALTER TABLE staffs ADD INDEX idx_name (name, age, pos);
索引正常
EXPLAIN SELECT * FROM staffs WHERE name = 'July'
索引失效
EXPLAIN SELECT * FROM staffs WHERE name = 'July' OR age = 25
索引失效
EXPLAIN SELECT * FROM staffs WHERE name = 'July' OR age = 25 or pos = '实习生'

为两个字段单独创建索引：ALTER TABLE staffs ADD INDEX idx_name (anme);
为两个字段单独创建索引：ALTER TABLE staffs ADD INDEX idx_age (age);
索引正常
EXPLAIN SELECT * FROM staffs WHERE name = 'July'
索引失效
EXPLAIN SELECT * FROM staffs WHERE name = 'July' OR age = 25
索引失效
EXPLAIN SELECT * FROM staffs WHERE name = 'July' OR age = 25 or pos = '实习生'



EXPLAIN 得到各字段含义参考：
https://blog.csdn.net/why15732625998/article/details/80388236



全面详细解释参考：https://blog.csdn.net/why15732625998/article/details/80409164




备注：or能够进行索引的，欢迎留言，谢谢