mysql - 索引优化

开发中可能会遇到索引失效的情况，使用到了索引字段但是却没有利用索引查询。

建立一个测试用表

DROP TABLE IF EXISTS index_test;
CREATE TABLE index_test(
   t_id INT PRIMARY KEY,
   key1 INT,
   key2 INT,
   key3 INT,
   key4 INT,
   content VARCHAR(20)
);

INSERT INTO index_test VALUES(1,1,1,1,1,'a');
INSERT INTO index_test VALUES(2,1,2,3,4,'b');
INSERT INTO index_test VALUES(3,4,1,2,3,'c');
INSERT INTO index_test VALUES(4,3,4,1,2,'d');
INSERT INTO index_test VALUES(5,2,3,4,1,'e');


CREATE INDEX index_key1_key2_key3_key4 ON index_test(key1,key2,key3,key4);

如何防止索引失效：

1. 最佳左前缀法则：索引为多列时，查询条件最好从索引最左边开始，不跳过中间列。（不过可以插入别的条件，比如key=1，content = ‘a’, key2 = 2 这样，不会影响整体效率）

使用的是查询类型是ref（索引扫描），根据索引进行了常量查询（索引的取值是个常数），查询结果利用率是100%（只扫了符合条件的那一条数据）。

当使用的查询条件没有从索引的第一列开始时，查询类型变成了all（扫描全表），key = null （没有用到索引），ref = null（没有利用索引查询），查询结果利用率只有20%（扫描了5行数据，只有一条符合条件。）

2. 不要在索引列上做任何操作（计算，函数，类型转换，不等于，is null， is not null）

把key = 1 变成了 key +1 = 2 结果一样，但是查询从索引查询变成了全表扫描。

3. 范围查询右边的索引会失效。

此时是一个范围查询，而且效率很高。

当key2 后面加了 key3后，虽然条件更加精确了，但是效率反而低了。因为key3的索引没有被使用，单独为key3进行了全表扫描。

所以这时最好建立索引顺序 key1，key3，key2，然后把范围查询放在最后。

4. 尽量使用索引覆盖，少用 select * 查询

查询内容中有不是索引的列（我这个表里的content不是索引），则mysql会先找到符合条件的行，然后从表中读取数据

查询的列全部是索引内的列（不是同一个索引里的也可以），则mysql会直接从索引中读取数据。

5. 通配符不要作为字段开头，否则会导致索引失效（like '%aaa'）

加一个key5用来测试

ALTER TABLE index_test ADD COLUMN key5 VARCHAR(20);
UPDATE index_test SET key5 = CONCAT('test_',content,key1);
CREATE INDEX index_key5 ON index_test(key5);