在日常的web应用开发过程中,一般会涉及到数据库方面的操作,其中查询又是占绝大部分的。我们不仅要会写查询,最好能系统的学习下与查询相关的知识点,这篇随笔我们就来一起看看MySQL查询知识相关的树是什么样的。
MySQL查询知识树:
一、查询的方式
二、查询的原理
三、查询的应用场景
四、查询的效率比较
五、查询的优化
一、查询的方式
MySQL的查询可以分为交叉连接、内连接、外连接、自然连接。
下述对于连接查询的学习,会用到以下两张表:
create table t_commodity_type(
`id` BIGINT(20) not null auto_increment comment '商品类别ID',
`time` TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP comment '入库时间',
`name` VARCHAR(32) not null DEFAULT '' comment '名称',
`is_use` bit(1) not null DEFAULT b'0' comment '是否上架',
primary key (`id`)
)engine=innodb DEFAULT CHARSET=utf8 comment '商品类型表';
create table t_commodity(
`id` BIGINT(20) not null auto_increment comment '商品ID',
`commodity_type_id` BIGINT(20) not null DEFAULT 0 COMMENT '商户所属类别ID',
`time` TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP comment '入库时间',
`name` varchar(64) not null DEFAULT '' comment '商品名称',
`price` DECIMAL(20,2) not null DEFAULT 0.00 comment '价格',
`is_use` bit(1) not null DEFAULT b'0' comment '是否上架',
PRIMARY key (`id`),
key `com_typ_id` (`commodity_type_id`) using BTREE
)engine=innodb DEFAULT charset=utf8 COMMENT '商品表';
CREATE TABLE `t_user_collect` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',
`commodity_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id',
`name` varchar(64) NOT NULL DEFAULT '' COMMENT '商品名称',
`price` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '商品价格',
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING BTREE,
KEY `index_commodity_id` (`commodity_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户收藏表';
CREATE TABLE `t_user_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户订单id',
`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',
`commodity_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id',
`name` varchar(64) NOT NULL DEFAULT '' COMMENT '商品名称',
`price` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '商品价格',
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING BTREE,
KEY `index_commodity_id` (`commodity_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户订单表';
①交叉连接(cross join)
对两表做笛卡尔积,若表A有m行数据,表B有n行数据,则cross join将返回m*n行数据。需要注意的是交叉连接本质就是想得到两表的笛卡尔积,虽然它也可以配合on子句来使用,但实际当我们拿到两表的笛卡尔积后再去使用on,则从行为上来说我们是想得到两表关联的数据,那么就符合了内连接(inner join)的使用条件,因此在这种情况下,我们更应该去选择内连接(inner join)。
那么cross join在实际中有哪些应用呢?例如快速生成测试数据,如下SQL:
insert into t_user_order(user_id,commodity_id,`name`,price)
select uc.user_id as user_id, c.id as commodity_id, uc.`name` as `name`, uc.price as price
from t_commodity c cross join t_user_collect uc;
额外知识点:
inner join后不跟on子句,也可以通过MySQL的语法解析,这时inner join就等价于cross join了,因为对于MySQL来说cross join和inner join就是同义词关系。
②内连接通过on子句来匹配两表的记录,查询出来的数据是两表的交集
拿上面两张表来举例子,从表结构上看t_commodity表通过commodity_type_id列和t_commodity_type表的id列产生了关联,我们通过一个SQL来表现内连接:
select c.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time
from t_commodity_type ct inner join t_commodity c on ct.id=c.commodity_type_id;
查询结果如下:
以上SQL可以通过另外几种形式来实现,如下:
1)省略inner关键字的实现
select c.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time
from t_commodity_type ct join t_commodity c on ct.id=c.commodity_type_id;
2)不接on子句,通过逗号分隔表来实现
select c.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time
from t_commodity_type ct, t_commodity c where ct.id=c.commodity_type_id;
3)通过cross join和on子句来实现
select c.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time
from t_commodity_type ct cross join t_commodity c on ct.id=c.commodity_type_id;
额外知识点:
为什么可以通过第2)种形式来实现呢?
其实对于两表之间使用逗号分隔,且无on子句的SQL书写是ANSI SQL 89的规范,因为ANSI SQL 89在当时不支持join和on子句,而是直到ANSI SQL 92才引入的。MySQL对两者都是完全兼容的,两种书写方式无所谓好坏与否,因为优化器会为两者生成相同的执行计划,因此效率上、结果上是一致的,至于在实际项目中使用哪种则取决于你公司的SQL规范。
③外连接(outer join)有两种连接的方式,第一种是左外连接(left outer join),第二种是右外连接(right outer join)。
这种连接也是通过on子句来匹配两表的记录,但它与inner join不同的是,若是左外连接,则左表的记录会被全部获取,即便左表中的列无法通过on子句与右表发生匹配,这部分无法产生匹配关系但依然被获取出来的记录,我们称之为保留行
我们通过一个SQL来进行说明:
select ct.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time
from t_commodity_type ct left join t_commodity c on ct.id=c.commodity_type_id;
查询结果如下(只做了局部截图):
这个查询通过t_commodity_type表的id和t_commodity表的commodity_type_id进行匹配,由于有些商品类型在商品表中无对应关系,但又需要保留这部分行显示出来,于是可以看到被筛选出的不属于t_commodity_type表的列值被填充为Null。
而对于右外连接,它的原理与左外连接一致,上面那个SQL用右外连接来实现就是:
select ct.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time
from t_commodity c right join t_commodity_type ct on c.commodity_type_id=ct.id;
可以看到我的左外连接和右外连接都没有加outer关键字,因为它是可以省略的,这使语法看起来更为简洁,而左外连接、右外连接也可以简称为左连接、右连接,但需要注意的是你得知道它俩都属于outer join。
另外outer join必须配合on子句来使用,否则MySQL会将其认定为语法错误。
④自然连接(natural join),它会将两表中具有相同名称的列进行匹配
我们以t_user_collect表和t_user_order表来做说明,t_user_collect表中的数据如下,
t_user_order表的数据如下,
对两表使用自然连接的SQL:select * from t_user_order natural join t_user_collect; 我们可以看到以下数据被筛选出来,
怎么理解?由于两表的每列名称都相同,因此只有每列下的值都一致,才会匹配,而观察两表数据,仅有id为2的每列数据是一致的,假如我们将t_user_collect表中的几个列名称做修改,仅保留commodity_id,再看看会有怎样的匹配效果,修改后的t_user_collect表如下,
我们再执行select * from t_user_order natural join t_user_collec; 会得到以下数据,
可以看到有4行数据,因为commodity_id列在两表中是能产生4行数据匹配的,看到这里大家应该对natural join的使用效果较为明白了,接下来我们再探讨几个问题,
1)相同名称但数据类型不同的列会产生匹配吗?
2)是否能以其它的连接方式来实现natural join的效果?
关于第1)个问题,我们可以做如下实验,对t_user_collect表的commodity_id列修改其数据类型,再执行查询得到结果如下,
我们可以看到查询出的4行结果与之前的4行结果一致,这说明同名称的列虽然数据类型不同,但不会对natural join的匹配产生影响。
关于第2)个问题,就拿刚刚那个查询SQL:select * from t_user_order natural join t_user_collec; 来说,它可以改写成如下形式:select * from t_user_order inner join t_user_collect using(commodity_id); 实际上natural join就等同于inner join和using的组合。
二、查询的原理
接下来我们来探讨一下查询的实现原理,首先我们要知道对于查询我们可以将其分为逻辑查询和物理查询,逻辑查询表示执行查询应该得到什么样的结果,可以理解为当我们在写出一个SQL时,我们对这个SQL所预期的结果;物理查询是指MySQL如何得到查询结果,即在执行SQL语句的过程中MySQL做了什么。
在这里我们需要了解下一个查询中可能包含有哪些内容:
1、select,2、distinct,3、join,4、on,5、from,6、where,7、having,8、group by,9、order by,10、limit
1)无论我们的查询是inner join还是left join,from操作总是最先执行的,form操作会得到关联表的笛卡尔积;
2)接着on操作会进行筛选,只有符合on后条件的记录才会被筛选出来;
3)join需要注意如果是left join或right join,则会保留表中未匹配的行(也称之为添加外部行),这是我们做两表联查时能理解的过程,其实做3表、5表联查过程也是一样的,将两表联查得到的记录与表3重复form、on、join的过程,完成后再对表4、表5,以此类推;
这里详细对多表联查的过程做下说明,假设我们联查表A、B、C、D
先是表A、B做笛卡尔积,即两表记录数相乘,然后通过on来筛掉不符合的记录,如果是left join或right join这种方式的联查,则还需要保留外部行,怎么理解保留外部行,就是保留表中被过滤条件过滤掉的数据,这样就得到了表A、B的联查数据,接下来再将得到的数据和表C做笛卡尔积,并重复这一过程,直到最后一张表完成这一过程,最后就得到了A、B、C、D四表联查的数据;
4)通过上述操作得到记录后,where会对记录进行过滤,只有满足where后条件的记录才会被筛选出来;
另外需要注意的是对于left join和right join的过滤,on过滤完之后还会添加保留表中被on条件过滤掉的记录,而被where条件过滤的记录则是永久过滤。
5)group by会对刚刚经过了where的记录进行分组;
6)having需要和group by配合使用,因为having使用的前提是group by已经对记录完成了分组,而having就是来对分组的记录再进行筛选,这里需要说明的是因为在分组前执行了where,因此若分组后的记录没有达到我们的预期,就需要使用having;
需要注意的是,若在left join和right join查询中,对select子句后使用count(1)或count(*),可能会把添加的行统计入内从而导致查询结果与预期结果不符合,对于这样的查询最好是count()具体列。
7)select在这里是将需要返回的列筛选出来,可以看到select的优先级并不高,是在最后几步才做的;
需要注意的是列的别名不能在select中的其他别名表达式中使用;
8)如果查询语句中带有了去重子句distinct,则会执行去掉重复记录的操作;
去掉重复记录的操作原理是对进行distinct操作的列增加一个唯一索引,如若SQL中使用了group by,则distinct是无效的,因为已经进行了分组,不会移除任何行。
9)接下来是order by,在我们得到了预期的记录后,就需要对记录进行排序,以方便阅读;
另外在order by中还可以指定select中列的序列号,通过指定序列号就能达到指定列的排序效果。
例如SQL:select id,commodity_type_id from t_commodity order by commodity_type_id,id;
等价于:select id,commodity_type_id from t_commodity order by 2,1;
需要注意的是若不使用order by则查询出来的数据是无序的,并非是按照主键有序排列,这是因为关系型数据库是基于数学来实现的,关系对应数学中的集合,集合本身是无序的,因此在不使用order by的情况下从集合中取数据无法保证是按顺序排列的。
还有在对列进行排序时,若列没有索引,则排序会造成一定的开销。
10)最后执行limit,拿到从指定位置开始(不包含指定位置)的指定行记录,limit常和order by一起使用,其使用方式是limit n,m,表示拿到从n行开始(不包含n行)的m行数据。
另外在大数据量下使用limit来分页效率是比较低的,因为需要在这么多数据量下去定位位置(即定位n),更好的解决方案是在应用层面使用缓存。
额外知识点:
在where子句后书写过滤条件时,有两种过滤情况是不允许发生的。
①数据没有分组前或者说在group by没有执行前,在where子句中不能使用分组函数,例如max()、min()、count()、sum()等,正因为这个限制的存在就能理解having子句存在的意义了,因为我们还存在对分组后的数据进行统计的需要;
错误的SQL:
select id from t_commodity c where count(commodity_type_id)>=5;
MySQL提示:
②为select子句后的列取别名,并在where子句里直接使用列别名,是不被允许的。因为where的执行顺序是要高于select的,因为在列还没有被选取的情况下,就开始使用列别名明显是不行的。
错误的SQL:
select `name` as n from t_commodity where n='精进';
MySQL提示:
额外知识点:
每张表的行中都有可能存在null值,但并不是每个null值都是一样的,或者说你将两个null值进行比较返回的会是0、1、null三种结果中的一种,毕竟关系型数据库中的null值比较和编程语言中的null值比较是不同的,但是有两种情况下我们可以认为两个null值是相等的:
①被group by子句分到同一组的null值;
②被order by子句排列在一起的null值,另外null值在order by中被认为是最小的,若按照升序排列则null值会排在最前面,例如我们将前面写的一个left join添加order by:
select ct.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time
from t_commodity_type ct left join t_commodity c on ct.id=c.commodity_type_id order by `name` asc;
得到如下结果:
之前有提到查询分为逻辑查询和物理查询,虽然MySQL执行查询会遵从上面我们提到的步骤,但这并不是全部,因为MySQL除了通过SQL分析器完成对SQL语句的分析外,还会通过SQL优化器做SQL语句的优化,以最优的方式来选取数据,当然最终结果和逻辑查询是一致的。
因此上面所讲的步骤只是帮助我们去理解一个SQL语句的执行过程,在除了拿到我们所需要的数据外,我们也希望这个SQL的代价是非常低的,所以通过建立适当的索引会极大的提高查询效率,例如可以避免两表产生笛卡尔积,这是因为物理查询会根据索引来优化SQL的执行过程。
关于索引的详细讲解后面会写一篇“MySQL知识树--索引的设计和使用”的随笔。
--------------------未完,待更新--------------------