SQL的一些基础知识

先给个表结构

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产生的分组进行筛选，可以使用聚集函数