mysql索引探索

一、 测试表脚本

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

二、索引最左原则

1.最左索引失效现象

explain select * from single_table where key_part2 = "test2" and key_part3 = "test3"

发现上述查询导致的结果是type=ALL,查询时进行了全表扫描 2.最左匹配后生效

explain select * from single_table where key_part1 = "test1" and key_part3 = "test3"

索引匹配最左边key_part1,生效 3.查询器优化，仍生效

explain select * from single_table where key_part2 = "test2" and common_field = "a" and key_part1="test1"