基础表结构
CREATE TABLE IF NOT EXISTS employees
(
id serial PRIMARY KEY NOT NULL,
name character varying(24) NOT NULL DEFAULT '',
age int NOT NULL,
position character varying(20) NOT NULL,
hire_name timestamp NOT NULL default current_timestamp
)
索引
CREATE INDEX IF NOT EXISTS idx_name_age_position ON employees(id, name, position)
数据
(一)联合索引第一个字段用范围不会走索引
explain select * from employees where name > 'LiLei' and age = 22
第一个where条件如果是范围查找则不会走索引,mysql内部觉得第一个就用范围,查询范围会增大,不如就全表扫描。
(二)强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
即使写强制走索引的SQL语句,但最后效率不一定比全表扫描更高。
(三)覆盖索引优化 - 将查询的字段具体化
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
(四)IN 和OR在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描