库里数据刷新（也许你也踩过吧）

今天，我们就来老生常谈一下，说说mysql数据库的索引和一些SQL小陷阱。

在互联网日益发展的今天，一个系统的数据量越来越大，对于数据库查询速度的要求也越来越高，假如我们在查询时没有使用索引或者不能有效的命中索引，个中滋味真的是谁用谁知道。但是，SQL语法中有很多的小坑，假如我们这么写了SQL，那么就和索引say bye bye了。

OK，下面我们就整理一下这些经验，希望大家能够有所收获。

常见的SQL知识点

我们先创建一个用户表user：

CREATE TABLE user ( id int NOT NULL, name varchar(50) NOT NULL, sex int NOT NULL, level int NULL, registtime datetime NOT NULL, phone varchar(20) NOT NULL )

sex字段的值包括：1=male，0=female；

level字段的值包括：0=level0，1=level1，2=level2，3=level3。

1.假设，我们现在要查询除了level 1和level 2以外所有的用户怎么办？

可能很多同学们就会这样写SQL：

select * from user where level != 1 and level != 2;

或者：

select * from user where level not in (1, 2); select * from user where level not exists (1, 2);

这时，我们会发现，当我们的数据量较多时查询速度会大幅度下降，而导致这个情况出现的原因就是负向查询（!=/not in/not exists）。

所以，最好的写法应该是：

select * from user where level in (0, 3);

2. 假设，我们需要查找所有的男性用户呢？

我们通常会这样写SQL：

select * from user where sex = 1;

SQL本身是没有问题的，但由于这个字段存储的值只会是0和1，如果我们在这个字段上加上索引，那么可能会让查询的效率变低。

通常意义上来说，字段中的值越多，越建议使用索引，这样我们能够通过索引过去掉大部分的数据，如果最多过滤的数据没有超过80%的话，是不建议使用索引的。

3.假设，我们要查找2019年所有注册的用户数据呢？

可能会有同学这样写SQL：

select * from user where YEAR(registtime) = 2019;

这时，即使registdate上有索引，这段SQL也不会命中索引的，因为有所在属性上进行计算的SQL都无法命中索引。

建议修改为：

select * from user where registtime >= 2019-1-1 and registtime < 2020-1-1

我曾经就有同学在查询条件中使用的concat，导致一个查询拖垮一个系统，查询条件中做计算的危害是非常大的，所以我们对于SQL的要求也是，别把计算放在SQL中。

4.假设，我们需要通过某个用户的名字进行模糊查找？

可能有同学会这样写SQL：

select * from user where name like %xxx%;

其实，前导模糊查询是不能使用索引的，所以这样的查找速度会比较慢，如果我们把SQL改为：

select * from user where name like xxx%;

只要是非前导模糊查询，就能够使用索引了。不过这和业务的要求有关，所以前导模糊查询是否使用，这个看实际情况。如果数据量大但又必须使用前导模糊查询，那就应该使用其他的解决方案。

不算常见的SQL知识点

1. 如果你的字段允许为null，那么要多多警惕

因为，你的SQL查询出的结果可能并不是你真正想要的。

由于索引不会存null值（单列索引不存null值，符合索引不存全为null的值），所以，如果你要查找的结果是level < 3的所有用户（包括null），level列添加了索引，SQL为：

select * from user where level < 3;

但实际的结果并不会包含null值的数据。

所以，将这样的字段定义为not null，并给予默认值是比较好的解决办法。

我们在定义user表的时候，建立了一个(name, level)的复合索引，那么，我的查询条件顺序是不是要和复合索引的顺序保持一致呢？

mysql中要使用索引，有一个最左前缀原则，也就是说，你的查询中必须要先用到左边的索引，才能使用下一个索引。但是，查询条件中的顺序并不重要，只是需要“用到”。

所以，如果我们的SQL这样写：

select * from user where name = 大爷 and level = 0; /*能够命中索引*/ select * from user where level = 0 and name = 大爷; /*能够命中索引*/ select * from user where name = 大爷; /*能够命中索引*/ select * from user where level = 0; /*不能命中索引，不满足复合索引最左前缀*/

2.使用enum需要多注意

在mysql中定义enum（枚举）的优缺点就不多说了，当我们定义了enum后，如果insert的值不在枚举范围内，就会报错。

但是，enum保存的是tinyint，如果你定义字符串，那么就会有一张映射关系表进行字符串和tinyint的转换。而这会导致查询的效率低，占用的空间又大。

不算常见但有用的SQL知识点

1.如果你确定你查询的结果只会是一条数据时，加上limit 1能够有效的提高你的查询效率。

例如，系统的业务逻辑规定了，user表中不能出现同样name的用户，那么如果我们使用：

select * from user where name = xxx;

进行查找，就只会返回一条数据。但是如果使用上面这个SQL，游标即使检索到了数据也不会停止，知道检索完所有的数据。如果我们把SQL改为：

select * from user where name = xxx limit 1;

那么游标就会在找到一条数据后停止移动返回结果，这样就很大的提高了查询的效率。

2.mysql的强制类型转换并不是好东西，它并不会使用索引。

例如：

select * from user where phone = 13888888888;

虽然我们的phone上有索引，但是这里的where条件相当于进行了一次运算，所以并不会命中索引。因此，千万不要去偷这点懒。

最后，上面所有的SQL都有一个共通的问题，也是我们在实际工作中经常会出现的错误使用方式，我想大家应该都看出来了。

,