mysql 偶尔入库两次,数据库的一些小常识，有时候问题就产生的细节中

今天，我们就来老生常谈一下，说说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都有一个共通的问题，也是我们在实际工作中经常会出现的错误使用方式，我想大家应该都看出来了。