# 数据库

🔝 返回：最全导引

🏀 数据库工具	KeyDB 项目是从 redis fork 出来的分支
redis	oracle
Mysql	Mybatis
达梦	Mpp
TIDB	/
MongoDB	ClickHouse 到底有什么本事
函数算法	常见问题

# 关于null

select 1 from null=null;
select 1 from null!=null;
-- 上述均返回null

1
2
3

# 各种join

类型	含义	实例语句
(inner) join	交集	`select * from table_a A inner join table_b B on A.key=B.key`
left (outer) join	左表	`select * from table_a A left join table_b B on A.key=B.key` `select * from table_a A left join table_b B on A.key=B.key where B.key is null`
right (outer) join	右表	`select * from table_a A right join table_b B on A.key=B.key` `select * from table_a A right join table_b B on A.key=B.key where A.key is null`
full (outer) join	并集，未匹配的的字段以`null`作为其值	`select * from table_a A full join table_b B on A.key=B.key` `select * from table_a A full join table_b B on A.key=B.key where B.key is null or A.key is null`

# 分页

1、达梦分页既支持mysql分页limit，也支持oracle分页rownum 2、oracle分页使用rownum 3、mysql分页使用limit

# 唯一约束和唯一索引

约束：完整行约束，确保数据的准确性和一致性
索引：为了提高查询效率
唯一约束：保证一个字段或者一组字段与表中其他行数据不同，和主键约束不同，唯一约束允许为null，只不过只能有一行
唯一索引：不允许具有索引值相同的行

# 高级sql用法

# 一、ORDER BY FIELD() 自定义排序逻辑

mysql 中的排序 ORDER BY 除了可以用 ASC 和 DESC，还可以通过 Order by FIELD(column_name,str1,...) 自定义字符串/数字来实现排序。这里用 order_diy 表举例，结构以及表数据展示：

ORDER BY FIELD(column_name,str1,...)自定义排序SQL如下：

SELECT * from order_diy ORDER BY FIELD(title,'九阴真经', 
'降龙十八掌','九阴白骨爪','双手互博','桃花岛主',
'全真内功心法','蛤蟆功','销魂掌','灵白山少主');

1
2
3

# 二、CASE 表达式

case when then else end表达式功能非常强大可以帮助我们解决 if elseif else 这种问题，这里继续用 order_diy 表举例，假如我们想在 order_diy 表加一列 level 列，根据money 判断大于60就是高级，大于30就是中级，其余显示低级，sql 如下：

SELECT *, 
case when money > 60 then '高级' 
when money > 30 then '中级' 
else '低级' END level 
from order_diy;

1
2
3
4
5

需要注意的就是 case when then 语句不匹配如果没有写 else end 会返回 null，影响数据展示。

# 三、exists 用法

我猜大家在日常开发中，应该都对关键词 exists 用的比较少，估计使用 in 查询偏多。这里给大家介绍一下 exists 用法，引用官网文档:
可知 exists 后面是跟着一个子查询语句，它的作用是根据主查询的数据，每一行都放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE），TRUE的话该行数据就会保留，下面用 emp 表和 dept 表进行举例，表结构以及数据展示：

计入我们现在想找到 emp 表中 dept_name 与 dept表中 dept_name 对应不上员工数据，sql 如下：

SELECT * from emp e where exists (
SELECT * from dept p where e.dept_id = p.dept_id 
and e.dept_name != p.dept_name
)

1
2
3
4

我们通过 exists 语法将外层 emp 表全部数据放到子查询中与一一与 dept 表全部数据进行比较，只要有一行记录返回true。画个图展示主查询所有记录与子查询交互如下：

第一条记录与子查询比较时，全部返回 false，所以第一行不展示。
第二行记录与子查询比较时，发现销售部门与 dept 表第二行销售部对应不上，返回 true，所以主查询该行记录会返回。
第二行以后记录执行结果同第一条。

# 四、GROUP_CONCAT(expr) 组连接函数

GROUP_CONCAT(expr) 组连接函数可以返回分组后指定字段的字符串连接形式，并且可以指定排序逻辑，以及连接字符串，默认为英文逗号连接。这里继续用 order_diy 表举例：sql 如下：

SELECT name, GROUP_CONCAT(title ORDER BY id desc  SEPARATOR '-') 
from order_diy GROUP BY name ORDER BY NULL;

1
2

如上我们通过 GROUP_CONCAT(title ORDER BY id desc SEPARATOR '-') 语句，指定分组连接 title 字段并按照 id 排序，设置连接字符串为-。

# 五、自连接查询

自连接查询是 sql 语法里常用的一种写法，掌握了自连接的用法我们可以在 sql 层面轻松解决很多问题。这里用 tree 表举例，结构以及表数据展示：

tree 表中通过 pid 字段与 id 字段进行父子关联，假如现在有一个需求，我们想按照父子层级将 tree 表数据转换成一级职位二级职位三级职位三个列名进行展示，sql 如下：

SELECT t1.job_name '一级职位', t2.job_name '二级职位', t3.job_name '三级职位' 
from tree t1 join tree t2 on t1.id = t2.pid left join tree t3 on t2.id = t3.pid 
where t1.pid = 0;

1
2
3

我们通过 tree t1 join tree t2 on t1.id = t2.pid 自连接展示一级职位二级职位，再用 left join tree t3 on t2.id = t3.pid 自连接展示二级职位三级职位，最后通过where 条件 t1.pid = 0过滤掉非一级职位的展示，完成这个需求。

# 六、更新 emp 表和 dept 表关联数据

这里继续使用上文提到的 emp 表和 dept 表，数据如下：

可以看到上述 emp 表中 jack 的部门名称与 dept 表实际不符合，现在我们想将 jack 的部门名称更新成 dept 表的正确数据，sql 如下：

update emp, dept set emp.dept_name = dept.dept_name
where emp.dept_id = dept.dept_id;

1
2

我们可以直接关联 emp 表和 dept 表并设置关联条件，然后更新 emp 表的 dept_name 为 dept 表的 dept_name。

# 七、ORDER BY 空值 NULL 排序

ORDER BY 字句中可以跟我们要排序的字段名称，但是当字段中存在 null 值时，会对我们的排序结果造成影响。我们可以通过 ORDER BY IF(ISNULL(title), 1, 0) 语法将 null 值转换成0或1，来达到将 null 值放到前面还是后面进行排序的效果。这里继续用 order_diy 表举例，sql 如下：

sql复制代码SELECT * FROM order_diy ORDER BY IF(ISNULL(title), 0, 1), money; 查询结果：

# 八、with rollup 分组统计数据的基础上再进行统计汇总

MySql 中可以使用 with rollup 在分组统计数据的基础上再进行统计汇总，即用来得到 group by 的汇总信息。这里继续用order_diy 表举例，sql 如下：

SELECT name, SUM(money) as money 
FROM order_diy GROUP BY name WITH ROLLUP;

1
2

可以看到通过 GROUP BY name WITH ROLLUP 语句，查询结果最后一列显示了分组统计的汇总结果。但是 name 字段最后显示为 null，我们可以通过 coalesce(val1, val2, ...) 函数，这个函数会返回参数列表中的第一个非空参数。

SELECT coalesce(name, '总金额') name, SUM(money) as money 
FROM order_diy GROUP BY name WITH ROLLUP;

1
2

# 九、with as 提取临时表别名

with as 语法需要 MySql 8.0以上版本，它有一个别名叫做 CTE，官方对它的说明如下

公用表表达式 (CTE) 是一个命名的临时结果集，它存在于单个语句的范围内，稍后可以在该语句中引用，可以多次引用。

它的作用主要是提取子查询，方便后续共用，更多情况下会用在数据分析的场景上。

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用 with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用。这里继续用 order_diy 表举例，这里使用 with as 给出 sql 如下：

with t1 as (SELECT * from order_diy where money > 30),
t2 as (SELECT * from order_diy where money > 60)
SELECT * from t1 
where t1.id not in (SELECT id from  t2) and t1.name = '周伯通';

1
2
3
4

这个 sql 查询了 order_diy 表中 money 大于30且小于等于60之间并且 name 是周伯通的记录。可以看到使用 CTE 语法后，sql写起来会简洁很多。

# 10、存在就更新，不存在就插入

MySql 中通过on duplicate key update语法来实现存在就更新，不存在就插入的逻辑。插入或者更新时，它会根据表中主键索引或者唯一索引进行判断，如果主键索引或者唯一索引有冲突，就会执行on duplicate key update后面的赋值语句。这里通过 news 表举例，表结构和说数据展示，其中 news_code 字段有唯一索引：

-- 第一次执行添加语句
INSERT INTO `news` (`news_title`, `news_auth`, `news_code`) 
VALUES ('新闻3', '小花', 'wx-0003') 
on duplicate key update news_title = '新闻3';
-- 第二次执行修改语句
INSERT INTO `news` (`news_title`, `news_auth`, `news_code`) 
VALUES ('新闻4', '小花', 'wx-0003') 
on duplicate key update news_title = '新闻4';

1
2
3
4
5
6
7
8

让你的SQL语句更加优雅高效：

插入数据时的SQL优化
主键优化，同时会讲到主键的设计原则
order by 语句的优化
group by 语句的优化

# Insert 优化

提到插入数据，我们自然是通过Insert语句插入数据，而我们在往数据库中插入数据的时候，通常就是一条一条Insert语句去执行，而插入多条数据呢，我们可以有哪些实现方式呢？

# ⛏批量插入

因为每一次Insert都需要与数据库建立连接和网络传输，因此对于多条数据，批量插入的效率优于普通的一条插入。

同时，批量插入的数据也不要太多，最好维持在500-1000条，如果有超过这么多条的，最好是分多次批量插入。

# ⚒ 手动提交事务

在MySQL中，事务提交方式默认为自动提交，也就是说一条Insert语句后，就会自动提交事务，再次执行一条Insert就会再次开启事务，然后又提交，这样就会有事务频繁的开启与提交，因此最好是在插入之前开启事务，然后所有插入结束后提交事务。

start transaction; 开始事务
	insert ……; 插入数据1
	insert ……; 插入数据2
	insert ……; 插入数据3
	commit; 提交事务

1
2
3
4
5

# 主键顺序插入

主键插入，要么乱序插入，要么顺序插入，实际上顺序插入的性能更高，这取决于MySQL数据组织结构的，我们在后面的主键优化中会详细讲解。

顺序插入是指ID以升序的方式进行插入，比如1,2,3,4,5,6……

如果现在我们要导入百万级的数据，此时我们使用Insert语句的性能就不高了，我们应该怎么做呢？

# ⚔ 大批量插入数据

如果需要一次性插入大批量数据，使用Insert语句插入的性能很低，此时我们可以使用MySQL数据库提供给我们的指令load进行插入，例如：

而我们如何使用这个功能呢，它有三部曲：

客户端连接服务端的时候，加上参数--local-infile
shell复制代码mysql --local-infile -u root -proot
设置全局参数local_infile = 1，开启从本机加载文件导入数据的开关
mysql复制代码set global local_infile = 1
执行load指令，把准备好的数据加载到表结构中
mysql复制代码load data local infile
到表结构中
mysql复制代码load data local infile `/root/sql1.log` into table `tb_user` fields terminated by ',' lines terminated by '\n';

1
2
3
4
5
6
7
8

# 主键优化

在讲解插入数据时的优化的时候，我们有提到主键插入的时候顺序插入的性能高于乱序插入的性能，至于为什么呢，我们将在这一小节详细讲解它的底层原理：

在此之前，我们首先需要了解InnoDB存储引擎的数据组织方式：在InnoDB存储引擎中，表数据是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

在前文讲索引的时候我们有讲解过聚集索引，它的选取规则是在有主键的情况下，默认选择主键为聚集索引，也就是说，行数据存储在主键构成的索引的B+Tree中，如下图所示：

而索引构成的B+Tree在叶子结点顺序存放，也就是说：表数据是根据主键顺序组织存放的

此处，每一个结点，不管是分支结点还是叶子结点都是存储在一个页中，也就是我们之前提到的逻辑存储结构：

那么插入一条数据，在逻辑存储结构中是什么样的流程呢：

# 页分裂

# 顺序插入

对于顺序插入，依次去写数据即可，如果一页的空间不足就去申请下一页的空间，并用双向指针连接，这就是主键顺序插入

主键顺序插入基本上就是如果当前的最后一页有空间则填入，如果空间不足就放在下一页的空间使用双向链表连接保证它的有序

# 乱序插入

现在有两页写满了的数据：

如果我们不是顺序插入，此时插入了一条主键为50的数据，此时就会出现页分裂：找到第一页的后半部分23,47申请新的一页放进去，然后把50放在新的一页的最后：

然后重新排列三页的位置：1#page <==> 3#page <==> 2#page

所以，其实我们可以发现，相较于顺序插入，乱序插入的过程更加复杂、繁琐，这种现象我们称之为页分裂，因此在插入的时候最好是按照主键顺序插入

# 页合并

有页分裂，自然也有页合并，否则，对于固定大小的一页来说，如果一页的空间利用率不高，将会造成冗余的内存占用，那么什么时候会出现业合并呢？

首先谈谈删除记录时，页中数据的变化，其实它有点类似于逻辑删除，在删除一条记录的时候，在实际上没有被物理删除，只是记录了被标记为删除，并且它的空间被允许其他记录声明使用，但是什么时候会被真正物理删除了，那就是业合并的时候：

当页中的记录达到MERGE_THRESHOLD（默认为50%）的时候，InnoDB存储引擎会开始寻找两边的页，是否能够达到合并的条件，如果能就会产生业合并，以达到优化空间的目的：

对于这种情况：

第二页的数据删除超过了一半，而在比对的时候，与前一页无法合并，但是能和第二页合并，于是就合并了两页的数据：

其中MERGE_THRESHOLD（默认为50%）是可以自行设置的

# 主键设计原则

有了上面的前置知识，我们能总结出四点主键设计的原则，帮助我们在设计主键时避坑

# 尽量降低主键的长度

对于一张表来说，主键索引只有一个，但是会有很多个二级索引，如果说索引的长度比较长，二级索引比较多，那么在二级索引的叶子结点处会占用大量的磁盘空间，而且在搜索的时候会耗费更多的磁盘IO

# 尽量顺序插入

插入数据的时候尽量选择顺序插入，并选用AUTO_INCREMENT这种自增主键，这个我们在页分裂中有详细讲解

# 尽量不要使用自然主键

尽量不要使用UUID做主键或者其他自然主键，比如身份证号这种，我们每次生成的UUID都是无序的，那么就会导致在插入的时候产生乱序的插入，同样，UUID也比较长

# 尽量不要修改主键

业务操作的时候，尽量少修改主键，因为会伴随着索引结构的修改，不过在一般的业务里面，也没人改主键吧

# Order by 优化

对于MySQL中的排序有两种方式，那可能有朋友就疑惑了，那排序不就Order by嘛，哪来的两种方式，实际上这里的两种方式指的是底层实现的两种方式：

# Using filesort

通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

# Using index

通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

我们不妨来猜一猜一下几组情况的索引使用情况，已知存在联合索引age,phone，问下面哪些SQL查询使用到了索引，哪些没有使用到：

Explain SELECT id,age,phone from tb_user order by age
EXPLAIN SELECT id,age,phone from tb_user order by age,phone
EXPLAIN SELECT id,age,phone from tb_user order by age desc ,phone desc
EXPLAIN SELECT id,age,phone from tb_user order by phone,age
EXPLAIN SELECT id,age,phone from tb_user order by age asc ,phone desc

1
2
3
4
5

第一个，按照最左前缀法则，联合索引的最左边是age，因此使用了索引排序
第二个，使用联合索引进行排序，毋庸置疑
第三个，如果二者都查倒序排序，此时尽管还是使用了索引，但是多了一个Backward index scan，也就是说从索引的最后面开始扫描
- 我们只需要从最后那个叶子结点往回扫描即可
第四个，如果说联合索引的顺序反了，我们在讲最左前缀法则的时候，尽管顺序反了，索引也是能够使用了，是因为那个是 where 条件，没有先后顺序，只有书写顺序，但是对于order，书写顺序就代表了先后顺序，先对哪个进行排序。
第五个，在创建索引的时候，默认按照升序往后走，也就是说先按照age进行升序排列，再按照phone进行升序排列，而此处查找的时候，按照age升序排序，按照phone降序排序，因此就会不完全使用索引。

这张图其实能帮助我们很好的理解为什么不能一个升序一个降序，索引无法按照这个去排列

这里也说了，在创建索引的时候我们默认按照升序排序，那我们按照降序排序创建索引应该怎么创建呢？其实只需要在字段的后面加上asc / desc即可：

CREATE INDEX idx_user_age_phone_ad on tb_user(age asc, phone desc)

在创建结束后，我们再次去查询，得到的就是使用索引排序了：

# Order by 优化总结

根据排序字段建立合适的索引，多字段排序的时候，也遵循最左前缀法则。尽量使用覆盖索引，减少回表查询多字段排序的时候，注意联合索引在创建时的规则如果不可避免出现FileSort，我们可以适当增加排序缓冲区的大小sort_buffer_size，因为如果缓冲区满了，它就会去磁盘空间进行排序，效率很低

# Group by 优化

与Order by相比，其实二者很像，因为二者都是需要排序，但是Group by多了一个分组

它使用索引的条件是，也就是按照索引进行分组的时候，但是有一个情况是和联合索引有一些出入的：

对于这种情况，我们通过Explain分析可以发现，这个居然使用到了索引，原因是这里有使用到profession作为查询条件，然后根据年龄进行分组

# 5 个免费的在线 SQL 数据库环境

# SQL Fiddle

SQL Fiddle

提供了 MySQL、Oracle、PostgreSQL、SQLite 以及 SQL Server 数据库环境，使用时无需注册。

http://sqlfiddle.com/#!9/a6c585/1此为范例

# DB Fiddle

DB Fiddle

提供了 MySQL、PostgreSQL 以及 SQLite 数据库的最新版本，使用时无需注册，同时也提供了付费版本。

https://www.db-fiddle.com/f/83V6zUSzX42ZpzrbX1txd7/1此为范例

# db<>fiddle

db<>fiddle

提供了 MySQL、MariaDB、Oracle、PostgreSQL、DB2、Firebird、SQLite 以及 SQL Server 数据库的各种版本，使用时无需注册。

# SQL Online

SQL Online

提供了 MariaDB、PostgreSQL、SQLite 以及 SQL Server 数据库环境，Oracle 数据库正在计划中。

https://sqliteonline.com/#fiddle=b10c1ad462ac37386ac200341b7bd05758a7059321bd675ecb6c2ed7aa563f38此为例子

# Oracle Live SQL

Oracle Live SQL 是 Oracle 官方提供的在线 SQL 学习和分享环境，需要注册一个免费账号。

# SQL 自动检查神器，吊炸天的功能

https://gitee.com/cookieYe/Yearning

SQL 自动检查神器，吊炸天的功能

# explain 关键字指南

在我们所执行的 SQL 前面加上 explain 关键字，MySQL 就不会真正去执行这条语句，而是模拟优化器执行 SQL 查询语句，最后会输出一系列的指标告诉我们这条语句的性能如何，如下图所示。

mysql> explain select * from student where id = 1 \G
******************************************************
           id: 1
  select_type: SIMPLE
        table: subject
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
******************************************************

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

总的来说，explain 关键字可以告诉我们下面这么多信息：

表的读取顺序如何
数据读取操作有哪些操作类型
哪些索引可以使用
哪些索引被实际使用
表之间是如何引用
每张表有多少行被优化器查询
......

今天，我们就来介绍 explain 关键字的各个指标的含义。系好安全带，准备发车了！

为了方便讲解，这里新建了几张表，并初始化了一些数据（建表语句见附录）。这些表的关系如下：

一共有老师、学生、课程三个实体，分别为：teacher、student、course。三个实体间的关系分别为：老师教学生的关系（teacher_student）、学生的课程分数（student_course）。

# ID 字段

ID 字段的值及其排列顺序，表明 MySQL 执行时从各表取数据的顺序。一般情况下遵循下面两个原则：

ID 相同的组，其执行优先级按照其顺序由上到下。
ID 越大的组，其执行优先级越高。

对于下面这个例子：

EXPLAIN SELECT
	teacher.* 
FROM
	teacher,
	teacher_student 
WHERE
	teacher_student.student_name = 's001' 
	AND teacher.NAME = teacher_student.teacher_name

1
2
3
4
5
6
7
8

上面的输出一共有 2 条记录，其 ID 都为 1，这表示其归为一组。对于 ID 相同的组，MySQL 按照顺序从上到下执行，即：先拿 teacher_student 表的数据，再拿 teacher 表的数据。

再来看下面这个例子：

EXPLAIN SELECT
	* 
FROM
	teacher 
WHERE
	NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = 'S002' )

1
2
3
4
5
6

上面的输出一共有 3 条记录，其中第 1、2 条的 ID 相同，第 3 条 ID 不同。那么其执行顺序就是 ID 值越大，其越早执行。ID 相同的，按顺序执行。上面的例子，最早拿 teacher_student 表的数据，之后是一个子查询组成的表，最后拿 teacher 表的数据。结合 SQL 分析，这也符合我们的常识。因为我们必须先把子查询的值算出来，因此需要先把 teacher_student 表里的数据拿出来，之后才可以拿去 teacher 表里查询。

# select_type 字段

select_type 字段表示该 SQL 是什么查询类型，一共有以下 6 种：

SIMPLE：简单查询，不包含子查询或 union 查询
PRIMARY：主键查询
SUBQUERY：在 select 或 where 中包含子查询
DERIVED：from 中包含子查询
UNION：
UNION RESULT

# SIMPLE

简单查询，不包含子查询或 union 查询。

-- 查询T001老师都教了哪些学生
EXPLAIN SELECT
	student.* 
FROM
	teacher,
	teacher_student,
	student 
WHERE
	teacher.NAME = 'T001' 
	AND teacher.NAME = teacher_student.teacher_name 
	AND teacher_student.student_name = student.NAME

1
2
3
4
5
6
7
8
9
10
11

可以看出其 3 个查询都是简单（SIMPLE）查询。因为 ID 相同，所以其查询顺序是按顺序来的。首先从 teacher 表中取出数据，之后从 student 表取出数据，最后 teacher_student 表取数据。

# PRIMARY

一般情况下，如果查询中包含了任何复杂的子查询，那么最外层查询会被标记为主查询。

-- PRIMARY 查询哪些老师教授了选修数学课的学生
EXPLAIN SELECT
	* 
FROM
	teacher 
WHERE
	NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = ( SELECT student_name FROM student_course WHERE course_name = 'shuxue' ) )

1
2
3
4
5
6
7

在上面的查询中，首先是执行 ID 为 3 的查询，即去 student_course 表取出选修了数学课的学生名字，之后再去进行最外层的查询。可以看到最外层查询的 select_type 为 PRIMARY。

# SUBQUERY

在 select 或 where 中包含子查询，那么 select_type 会被标记为 SUBQUERY。以上面的查询为例：

-- PRIMARY 查询哪些老师教授了选修数学课的学生
EXPLAIN SELECT
	* 
FROM
	teacher 
WHERE
	NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = ( SELECT student_name FROM student_course WHERE course_name = 'shuxue' ) )

1
2
3
4
5
6
7

在该查询中，where 中包含了子查询，因此在 explain 中有一个 ID 为 3 的查询被标记为 SUBQUERY。

# DERIVED

在 FROM 中包含子查询，那么 select_type 会被标记为 SUBQUERY。

# UNION

类似包含 union 关键字的会被标记成 UNION 类型，这种查询方式比较少，这里不做深入讲解。

# UNION RESULT

类似包含 union 关键字的会被标记成 UNION RESULT 类型，这种查询方式比较少，这里不做深入讲解。

# type 字段

type 字段表示访问情况，通常用来衡量 SQL 的查询效率。其值的查询效率从最好到最差分别为：

NULL
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

1
2
3
4
5
6
7
8
9
10
11
12
13

# NULL

NULL 表示 MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

explain select max(id) from teacher

# system

表只有一行记录（等于系统表），这是const类型的特列。

出现的情况较少，这里不深入介绍。

# const

const 表示该表最多有一个匹配记录。

通常情况下是SQL 中出现了主键索引或唯一索引。

explain select * from teacher where name = 'T002'

上面例子中，teacher.name 字段为唯一索引字段，所以通过该字段只能唯一找到一条记录，因此其 type 类型为 const。

# eq_ref

eq_ref 表示主键索引或唯一索引的所有部分被连接使用，最多只会返回一条符合条件的记录。

与 const 类型非常相似，唯一的区别是 eq_ef 通常出现在联表的情况下，而 const 通常出现在单表情况下。

EXPLAIN SELECT
	* 
FROM
	teacher,
	teacher_student 
WHERE
	teacher.NAME = teacher_student.teacher_name

1
2
3
4
5
6
7

从上面的执行结果可以看出，其首先全表扫描了 teacher_student 表，之后使用 teacher.name 唯一索引去将联合 teacher 表的每一条记录。

要注意的是，eq_ref 这种情况重点在于：读取本表中和关联表表中的每行组合成的一行。如果并没有关联表中每行这个概念，那么就不会出现 eq_ref 这种类型。例如我在上面的 SQL 中加上 age 为 24 这个条件，即 SQL 为：

EXPLAIN SELECT
	* 
FROM
	teacher,
	teacher_student 
WHERE
	teacher.NAME = teacher_student.teacher_name and teacher.age = 24

1
2
3
4
5
6
7

会看到 type 类型都变为 ref 了，eq_ref 消失了。

# ref

ref 表示使用了非唯一索引扫描，会返回匹配某个单独值的所有行。

与 const 非常类似，只不过 ref 会匹配到多个记录，而 const 则只会匹配到单个记录。

explain select * from teacher where age = 24

age 为普通索引，表中有 2 条记录。

# ref_or_null

类似ref，但是可以搜索值为NULL的行。

explain select * from teacher where age = 24 or age is null

当我们增加 age is null 查询条件后，其 type 字段就变成了 ref_or_null。

# index_merge

表示使用了索引合并的优化方法。

索引合并指的是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。

EXPLAIN SELECT * from teacher where id = 1 or age = 24

可以看到使用了 index_merge 的查询类型。在 teacher 表中 id 和 age 都是索引，其将两个字段的索引结果进行合并了。

# range

range 表示检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引。

一般就是在你的where 语句中出现 between、<>、in 等的范围查询。

EXPLAIN SELECT * FROM TEACHER where age between 10 and 20

上面语句中，我们使用 between 进行范围查询，因此 type 类型为 range。

# index

index 表示只遍历索引树，且只从索引树中获取数据。

EXPLAIN SELECT id, age FROM TEACHER

上面 SQL 中的 id、age 都是索引字段，可以直接从索引树中读取。因此其 type 字段为 index，表示此次查询数据可以直接从索引树获取到。但是如果查询的字段不在索引树中，那么就是全表扫描了。例如：

EXPLAIN SELECT id, enter_time FROM TEACHER

查询 SQL 的 enter_time 字段不是索引，所以上面的查询就变成了全表查询（ALL）。

# ALL

ALL 表示该查询将遍历全表以找到匹配行，这是最糟糕的一种查询方式。

# table 字段

表示数据来自哪张表

# possible_keys 字段

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用。

# key 字段

实际使用到的索引，如果为NULL，则没有使用索引。

查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

select * from teacher where name = 'T001'

上面这个查询中，key 字段显示使用了 udx_name 这个索引，也就是 name 这个字段作为索引。

# key_len 字段

这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

mysql> explain select * from film_actor where film_id = 2;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | film_actor | ref  | idx_film_actor_id | idx_film_actor_id | 4       | const |    1 | Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+

1
2
3
4
5
6

key_len计算规则如下：

字符串
- char(n)：n字节长度
- varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型
- tinyint：1字节
- smallint：2字节
- int：4字节
- bigint：8字节
时间类型
- date：3字节
- timestamp：4字节
- datetime：8字节
其他
- 如果字段允许为 NULL，需要1字节记录是否为 NULL

# ref 字段

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）。

# rows 列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

# Extra 列

这一列展示的是额外信息。

# distinct

一旦mysql找到了与行相联合匹配的行，就不再搜索了。

mysql> explain select distinct name from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra                        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
|  1 | SIMPLE      | film       | index | idx_name          | idx_name          | 33      | NULL         |    3 | Using index; Using temporary |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index; Distinct        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+

1
2
3
4
5
6
7

# Using index

这表示查找某个表的时候，所需要的信息直接从索引就可以拿到，而不需要再访问行记录。

mysql> explain select id from film order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | PRIMARY | 4       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

1
2
3
4
5
6

上面例子中，我只是选择了 id 列，这个列本身是索引，其信息直接在索引树中就可以拿到，因此不需要再访问行记录。

# Using where

mysql 服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

mysql> explain select * from film where id > 1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | film  | index | PRIMARY       | idx_name | 33      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

1
2
3
4
5
6

# Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

actor.name没有索引，此时创建了张临时表来distinct

mysql> explain select distinct name from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+

1
2
3
4
5
6

film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

mysql> explain select distinct name from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | idx_name      | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

1
2
3
4
5
6

# Using filesort

MySQL 中无法利用索引完成的排序操作称为「文件排序」。

在MySQL中的ORDER BY有两种排序实现方式：

# 利用有序索引获取有序数据

文件排序在explain中分析查询的时候，利用有序索引获取有序数据显示 Using index ，文件排序显示 Using filesort。至于什么时候使用索引排序，什么时候使用文件排序，这个问题太过于复杂，这里不做深入介绍。

actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

mysql> explain select * from actor order by name;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

1
2
3
4
5
6

film.name建立了idx_name索引,此时查询时extra是using index

mysql> explain select * from film order by name;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

1
2
3
4
5
6