数据库

:top: 返回：最全导引

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

关于字段默认值

int类型会有默认值0

关于null

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

各种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,'九阴真经', 
'降龙十八掌','九阴白骨爪','双手互博','桃花岛主',
'全真内功心法','蛤蟆功','销魂掌','灵白山少主');

二、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;

需要注意的就是 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
)

我们通过 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;

如上我们通过 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;

我们通过 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;

我们可以直接关联 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;

可以看到通过 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;

九、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 = '周伯通';

这个 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';

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

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

Insert 优化

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

⛏批量插入

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

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

⚒ 手动提交事务

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

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

主键顺序插入

主键插入，要么乱序插入，要么顺序插入，实际上顺序插入的性能更高，这取决于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';

主键优化

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

在此之前，我们首先需要了解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

第一个，按照最左前缀法则，联合索引的最左边是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 自动检查神器，吊炸天的功能

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
******************************************************

总的来说，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

上面的输出一共有 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' )

上面的输出一共有 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

可以看出其 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' ) )

在上面的查询中，首先是执行 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' ) )

在该查询中，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

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

从上面的执行结果可以看出，其首先全表扫描了 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

会看到 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 |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+

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        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+

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 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

上面例子中，我只是选择了 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 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+

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 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

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 |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

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 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

Keys	Action
`?`	Open this help
`n`	Next page
`p`	Previous page
`s`	Search

数据库

关于字段默认值

关于null

各种join

分页

唯一约束和唯一索引

高级sql用法

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

二、CASE 表达式

三、exists 用法

四、GROUP_CONCAT(expr) 组连接函数

五、自连接查询

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

七、ORDER BY 空值 NULL 排序

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

九、with as 提取临时表别名

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

Insert 优化

⛏批量插入

⚒ 手动提交事务

主键顺序插入

⚔ 大批量插入数据

主键优化

页分裂

顺序插入

乱序插入

页合并

主键设计原则

尽量降低主键的长度

尽量顺序插入

尽量不要使用自然主键

尽量不要修改主键

Order by 优化

Using filesort

Using index

Order by 优化总结

Group by 优化

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

SQL Fiddle

DB Fiddle

db<>fiddle

SQL Online

Oracle Live SQL

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

explain 关键字指南

ID 字段

select_type 字段

SIMPLE

PRIMARY

SUBQUERY

DERIVED

UNION

UNION RESULT

type 字段

NULL

system

const

eq_ref

ref

ref_or_null

index_merge

range

index

ALL

table 字段

possible_keys 字段

key 字段

key_len 字段

ref 字段

rows 列

Extra 列

distinct

Using index

Using where

Using temporary

Using filesort

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