库里数据刷新(也许你也踩过吧)

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

库里数据刷新(也许你也踩过吧)(1)

在互联网日益发展的今天,一个系统的数据量越来越大,对于数据库查询速度的要求也越来越高,假如我们在查询时没有使用索引或者不能有效的命中索引,个中滋味真的是谁用谁知道。但是,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%的话,是不建议使用索引的。

库里数据刷新(也许你也踩过吧)(2)

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

库里数据刷新(也许你也踩过吧)(3)

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。