SQL的一些基础知识
先给个表结构
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE account ( id int (11) NOT NULL AUTO_INCREMENT COMMENT '主键' , name varchar (255) DEFAULT NULL COMMENT '账户名' , balance int (11) DEFAULT NULL COMMENT '余额' , create_time datetime NOT NULL COMMENT '创建时间' , update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' , PRIMARY KEY (id), KEY idx_name ( name ), KEY idx_update_time (update_time) ) |
通过explain可以查看sql的执行计划,如下是一个普通查询的例子
id:数字越大越先执行,数字一样大,就从上往下依次执行
select_type:
simple:表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple,且只有一个
primary:一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary。且只有一个
union:union连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表select_type都是union
dependent union:与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响
union result:包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为null
subquery:除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
dependent subquery:与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
derived:from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select
table:表名,或者临时表的别名
type:访问类型,从好到差排列(常见的):system,const,eq_ref,ref,range,index,ALL。除了ALL,都会走索引,但是日常开发中一般至少要达到range级别,最好达到ref。
possible_keys:可能使用到的索引
key:查询真正使用到的索引
key_len:用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意,mysql的ICP特性使用到的索引不会计入其中。另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。
ref:如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
rows:估算的扫描行数
extra:可以有很多信息信息,有几十种,常用的有
using where: 存储引擎返回的记录并不是所有的都满足查询条件,需要过滤
using index: 查询时不需要回表查询,直接通过索引就可以获取查询的数据。
using index condition: 使用索引下推,不满足的直接不取出来
using filesort:排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中
using temporary:使用了临时表存储中间结果
using join buffer(Block Nested Loop): 5.6.x之后的版本优化关联查询的BNL,BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
回表:
inner db中, 如果命中了普通索引(非主键),但是需要select其他列(非主键、非当前索引列),此时就需要回表,根据普通索引中的主键,再次去搜索B+树,得到所需的列。 因为普通索引中只存储了主键,是非聚簇索引。
聚簇索引:
inner db中主键索引是聚簇索引,索引中存储了所有的列
非聚簇索引:
inner db中非普通索引、联合索引、唯一索引等(非主键索引)都是非聚簇索引,索引中值存储了主键,如果需要其他列,需要再根据主键去搜索B+树
SQL的执行顺序:
from -> where -> group by -> having -> select -> order by -> limit
所以limit中offset比较大的时候,直接查询是会丢弃大量的数据,导致性能差
in和exists的异同点:
一些用的比较少的关键词
avg | 平均值 |
min | 最小值 |
max | 最大值 |
sum | 总和 |
count | 计数 |
distinct | 表示将distinct后的属性去重 |
group by | 将在group by上取值相同的信息分在一个组里 |
having | 对group by产生的分组进行筛选,可以使用聚集函数 |
窗口函数:
像排序的几个窗口函数需要MySQL8才支持,常规的聚合函数5.7也支持
聚合函数:SUM、COUNT、MAX、MIN
序号函数:ROW_NUMBER()、RANK()、DENSE_RANK()
分布函数:PERCENT_RANK()、CUME_DIST()
前后函数:LAG()、LEAD()
头尾函数:FIRST_VALUE()、LAST_VALUE()
其它函数:NTH_VALUE()、NTILE()
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单,K8s 太复杂?w7panel 让容器管理更轻松!