數據庫的一些小常識，有時候問題就產生的細節中

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

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

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

常见的SQL知识点

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

<code>
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
)/<code>

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

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

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

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

<code>
select
 * 
from
 
user
 
where
 
level
 != 
1
 
and
 
level
 != 
2
;/<code>

或者：

<code>
select
 * 
from
 
user
 
where 
 
level
 
not
 
in
 (
1
, 
2
);
select
 * 
from
 
user
 
where
 
level
 
not
 
exists
 (
1
, 
2
);/<code>

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

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

<code>
select
 * 
from
 
user
 
where
 
level
 
in
 (
0
, 
3 
);/<code>

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

我们通常会这样写SQL：

<code> 
select
 * 
from
 
user
 
where
 sex = 
1
;/<code>

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

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

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

可能会有同学这样写SQL：

<code>
select
 * 
from
 
user
 
where
 
YEAR
(registtime) = 
'2019'
;/<code>

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

建议修改为：

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

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

可能有同学会这样写SQL：

<code>
select
 * 
from
 
user
 
where
 
name
 
like
 
'%xxx%'
;/<code>

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

<code>
select
 * 
from
 
user
 
where
 
name
 
like
 
'xxx%'
;/<code>

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

不算常见的SQL知识点

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

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

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

<code> 
select
 * 
from
 
user
 
where
 
level
 < 
3
;/<code>

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

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

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

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

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

<code>
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
;  /<code>

2.使用enum需要多注意

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

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

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

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

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

<code>
select
 * 
from
 
user
 
where
 
name
 = 
'xxx'
;/<code>

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

<code>
select
 * 
from
 
user
 
where
 
name
 = 
'xxx'
 
limit
 
1
;/<code>

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

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

例如：

<code>
select
 * 
from
 
user
 
where
 phone = 
13888888888
;/<code>

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

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