数据库索引优化和避免索引失效（mysql和sqlite）

数据库索引就好比一本字典前面的目录，它是方便快速查询操作的，能提高查询效率。一方面，我们在编写sql语句时，要争取写出高质量的sql语句，以便减小数据库底层查询优化器组件的工作，提升性能（参见前面一篇介绍sql查询过程的文章https://blog.csdn.net/talkingmute/article/details/104127163）；另一方面，要防止索引失效的情况。

我们创建索引的场景，要和查询语句中条件字段的使用频率相关，比如某几个字段经常被查询，就创建这几个字段的复合索引。如某个字段经常被查询，就创建这个字段的单独索引。

mysql数据库创建索引：mysql> alter table 表名 add index 索引名(某个字段,某个字段,某个字段...);

sqlite数据库创建索引：sqlite> create index 索引名 on 表名(某个字段,某个字段,某个字段...);

mysql数据库里可以用explain select ...查询语句;来查看这条语句的查询性能，是否高质量。比如：explain select id from department where name='技术部';这里我们在部门表里根据name字段，查询id，因为name字段没有索引，所以现在性能是最低，参数type为all，就表示是全表查找。

当我们在name字段创建了索引后，参数type就变成ref，表示性能已经很高了。后面的key就是使用的索引，可以看到是我们创建的name_index。

上边是单独索引，就是只一个字段的。如我们创建的是复合索引，比如

这里我们是在员工表里有name,age两个字段的复合索引，这时sql查询后的type也是ref，性能很高。在key里使用的是我们创建的复合索引na_index。

上边是mysql数据库里使用索引的一些简单步骤，下面转到sqlite数据库。

比如我们还是有一个员工表employee，在sqlite命令里输入.schema 表名就可以看到该表的结构信息。

sqlite> .schema employee
CREATE TABLE employee(id integer primary key autoincrement,name text not null,dept_id int, age int,foreign key(dept_id) references department(id));
CREATE INDEX name_age_index on employee(name,age);
CREATE INDEX age_index on employee(age);

可以看到里面有2个索引，一个是name和age复合索引，一个是age的单独索引。

在sqlite中有一个命令叫做explain query plan，可以查看sqlite是如何执行查找操作的。不用索引时，使用的是“SCAN”这个词，即全表扫描。使用索引时，使用的是“SEARCH”这个词。

比如下面两条命令：

sqlite> explain query plan select id,name,age from employee where name='aa' and age=23;

sqlite> explain query plan select id,name,age from employee where dept_id=3;

可以看到dept_id字段没有索引，是scan table employee，表示是全表扫描。

而对于复合索引来说，我们要按照创建时的字段顺序来进行条件查询，不能中间有跳过。比如复合索引是a,b,c这样字段顺序，在写sql语句时，可以用复合索引的就是a,ab,abc这样字段条件。如把前面的字段跳过，只判断后面字段，将不会使用复合索引。还有，中间的查询条件字段不能有算术运算（加减乘除等），不能有!=和<>判断大小运算符，这些都是会让索引部分失效的情况。

这里，我们先删除age_index这个单独索引，然后测试name_age_index这个复合索引：

这里的条件语句where name>'aa' and age>23;因为name字段后出现>判断大小运算符，所以会导致复合索引部分失效，下面是(name>?)丢失了后面的age索引字段。

这里的条件语句where age>23;因为前面的name字段没有，所以复合索引完全失效，一个也没用上，是全表扫描。

这里的条件语句where name!='aa' and age>23;因为name字段出现!=判断运算符，会导致第一个字段的索引就无法使用，继而后面字段的索引也没法用，是全表扫描。

还有，关于in（某几个值）或者between and（在。。。范围）这样的运算指令，in是会让索引继续有效的，建议可以多使用它。而between and的效果类似于><大于小于运算符。

总结来说，对于字段的判断查询：

1、=等于和in（某几个值）是会保持索引字段有效。

2、><这样大于小于和between and（区间判断）虽然不会让该索引字段失效，但是会导致后续的其他索引字段失效。

3、算术运算（加减乘除）和!=不等于一样，都是严重让索引字段完全失效的，包括后续的其他索引字段也失效。

比如：

关于模糊查询like %这样sql语句，看下面这个例子，找出年龄在30-39之间的员工。

当使用sqlite> select id,name,age from employee where age like '3%';是全表扫描，

改成sqlite> explain query plan select id,name,age from employee where age >=30 and age <=39;就是使用索引了。