MySQL索引优化

基础表结构

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在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描