- 一、MySQL 操作集
- 二、MySQL 数据类型
- 三、MySQL 函数
- 四、MySQL Explain
- 五、MySQL 索引优化一
- 六、MySQL 索引优化二
- 七、SQL 优化
- 八、MySQL 锁与事务
- 九、MySQL 索引数据结构
- 十、MySQL 外键
一、Explain 介绍
- 使用 Explain 关键字。
可以 模拟优化器 执行 SQL语句，分析你的 查询语句 或是 结构 的性能瓶颈。- 在 select 语句之前，增加 explain 关键字。
- MySQL 会在查询上设置一个标记。
- 执行查询 会 返回执行计划 的信息，而不是 执行这条SQL。
- 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。
二、Explain 示例
1. 创建数据库
SHOW DATABASES;CREATE DATABASE learn_mysql;USE learn_mysql;
2. 创建数据表
- 男演员。
DROP TABLE IF EXISTS `actor`;CREATE TABLE `actor` (`id` int(11) NOT NULL,`name` varchar(45) DEFAULT NULL,`update_time` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 插入三条数据
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES
(1, 'a', '2017-12-22 15:27:18'),
(2, 'b', '2017-12-22 15:27:18'),
(3, 'c', '2018-12-22 15:27:18');
- 电影。
DROP TABLE IF EXISTS `film`;CREATE TABLE `film` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film` (`id`, `name`) VALUES
(3, 'film0'),
(1, 'film1'),
(2, 'film2');
- 电影演员。
DROP TABLE IF EXISTS `film_actor`;CREATE TABLE `film_actor` (`id` int(11) NOT NULL,`film_id` int(11) NOT NULL,`actor_id` int(11) NOT NULL,`remark` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1);
3. explain
explain select * from actor;EXPLAIN format=json select * from actor;
- 在查询中的每个表会输出一行。
- 如果有两个表通过
join连接查询，那么会输出两行。
三、Explain 扩展
1. explain extended 扩展
explain extended。
- 在 explain 的基础上，额外提供一些 查询优化的信息。
- 紧随其后通过 show warnings 命令，可以得到优化后的查询语句。
- 额外有
filtered列，是一个百分比的值。
rows * filtered / 100可以估算出，将要和 explain 中前一个表进行 连接的行数。
前一个表指 explain 中的 id值 比当前表 id值 小的表。
explain extended select * from film where id = 1;
show warnings。
可以得到优化后的查询语句，从而看出优化器优化了什么。
explain extended select * from film where id = 1;
show warnings;
2. explain partitions 分区
explain partitions。
- 相比 explain 多了个 partitions（分区字段）。
- 如果查询是基于分区表的话，会显示查询 将访问的分区。
四、Explain 中的列
1. id 执行顺序
- id列 是 select 的序列号（执行顺序的标识）：
表示有几个 select 查询，并且 id列 是按 select 出现的顺序增长的。
- id列 越大，执行优先级越高。
- id列 相同，则从上往下执行。
- id列 为 NULL，最后执行。
2. select_type 查询类型（6种）
- select_type列 表示 查询类型：
- SIMPLE 简单查询。
- PRIMARY 主查询。
- SUBQUERY 子查询（select）。
- DERIVED 子查询（from）。
- UNION 并集查询（union）。
- UNION RESULT。
2.1 SIMPLE 简单查询
- 简单 select 查询。
- 查询不包含 子查询、联合查询 或 union。
explain select * from film where id = 2;
2.2 PRIMARY 主查询
- 最外层的 select。
- 如果有子查询的话，最外层的查询会被标记为 PRIMARY。
2.3 SUBQUERY 子查询（select）
- 包含在 select 中的 子查询，不在 from 子句中。
-- 子查询。
explain select (select 1 from actor where id = 1)
-- 复杂查询，
from (select * from film where id = 1) der;
2.4 DERIVED 子查询（from）
- 包含在 from 子句中的 子查询。
- 生成的临时表的查询语句，会被标记为 DERIVED。
# 关闭`MySQL-5.7`新特性，对衍生表的合并优化。
set session optimizer_switch='derived_merge=off';explain select (select 1 from actor where id = 1)
from (select * from film where id = 1) der;# 还原默认配置。
set session optimizer_switch='derived_merge=on';
- MySQL 会将结果存放在一个临时表中，也称为 派生表（derived 的英文含义）。
2.5 UNION 并集查询（union）
- 在 union 中 第二个 和 随后的select。
explain select 1
union all select 1;
2.6 UNION RESULT
- 从 UNION 语句中获取结果。
EXPLAIN SELECT * FROM table1
UNION SELECT * FROM table2;
3. table 查询的表（3种）
- 表示 explain 时，正在访问的表：
- 当 from子句 中有 子查询 时，table列是
<derivenN>格式。
表示 当前查询 依赖 id=N 的查询结果，于是先执行 id=N 的查询。- 当 union 查询时，union 结果的 table列 值为
<union1,2>。
1 和 2 分别表示参与的 select 行id。
- 需求：在索引列中取最小值。
可以 单独查找索引 来完成，不需要在执行时 访问表。
explain select min(id) from film;
table = NULL。
MySQL 能够在优化阶段 分解查询语句，在 执行阶段 用不着再 访问表 或 索引。
4. type 访问类型（7种）【重要】
- SQL 性能的基本情况，表示 关联类型 或 访问类型。
即 MySQL 决定 如何查找表中的行，查找 数据行记录 的大概范围。
- 性能从最优到最差分别为：
system>const>eq_ref> ref > range >index>ALL。- 正常保证查询达到 range级，最好可以达到 ref级。
4.1 system 级
- system 是 const 的特例。
- 表中只有一条记录匹配时，为 system。
4.2 const 级
- 通过索引一次就找到了数据。
一般使用了 primary key列 或 unique key列，条件是与 常量 比较时（字符串、数字）。- 表中最多有一个匹配行，只读取 1次 速度比较快。
explain extended
select * from (select * from film where id = 1) tmp;
- MySQL 能对 查询的某部分 进行优化，并将其转化成一个常量。
可以看show warnings的结果。
show warnings
explain extended select * from (select * from film where id = 1) tmp;
show warnings;
4.3 eq_ref 级
- 使用了主键索引，或者非空唯一索引。
在表中 只有一条记录 与 索引键相匹配，匹配条件是某个表的列（需要转义替换才能拿到值）。- 用于 primary key列 或 unique key列 的所有部分。
被连接使用时，最多只会返回一条符合条件的记录。
const 和 eq_ref 的区别
- eq_ref 是在 const 之外最好的连接类型了，简单的 select 查询不会出现。
- const 和 eq_ref 两个都是在用到了 主键索引 或 唯一索引 的情况下出现。
- 不同的是 const 的 where 条件是常量。
- eq_ref 的 where 条件是其他表的某个列，需要对这个列进行转义才能拿到匹配条件的值。
- 也可以简单的理解为，eq_ref 一般为关联查询。
explain select * from film_actor
-- `eq_ref`级。
left join film on film_actor.film_id = film.id;
4.4 ref 级
- 非唯一性索引扫描。
和 eq_ref 不同的是 eq_ref 匹配的是唯一索引。
ref 它返回所有匹配某个单独值的行，它可能会找到多个符合条件的行。- 相比 eq_ref 不使用 唯一索引。
而是使用 普通索引 或 唯一索引的部分前缀。
- 索引要和某个值相比较，可能会找到多个符合条件的行。
- 简单 select 查询，name 是普通索引（非唯一索引）。
explain select * from film where name = 'film1';
- 关联表查询。
explain select film_id from film
left join film_actor on film.id = film_actor.film_id;
- idx_film_actor_id 是 film_id 和 actor_id 的联合索引。
- 这里使用到了 film_actor 的 左边前缀 film_id 部分。
4.5 range 级，范围查询
- 通常出现在
in、between ... and ...、>、<、>=等操作中。- 使用一个索引，来检索给定 范围的行。
explain select * from actor where id > 1;
4.6 index 级，扫描全表索引
- 全索引扫描，通过扫描整棵索引树获取到结果。
- 通常比 ALL 快一些。
explain select * from film;
4.7 ALL 级，即全表扫描
- 意味着 MySQL 需要 从头到尾 去查找所需要的行。
- 通常这种情况下，需要增加索引来进行优化了。
explain select * from actor;
5. possible_keys 可能使用到的索引【重要】
- 查询时 可能使用到的索引：
- explain 查询时，possible_keys列 有值，而 key列 显示 NULL 的情况。
这种情况是因为表中数据不多，MySQL 认为索引对此查询帮助不大。
选择了全表查询。- 如果该列是 NULL，则没有可用到的索引。
在这种情况下，可以检查 where 子句。
看是否可以创建一个适当的索引，来提高查询性能，然后用 explain 查看效果。
6. key 使用到的索引【重要】
- 查询时 实际采用到的索引：
- 如果没有使用索引，则该列是 NULL。
- 如果想强制 MySQL 使用 或 忽视 possible_keys列 中的索引。
在查询中使用force index、ignore index。
7. key_len 使用到的索引长度
- 使用到的索引 key 的长度：
- 显示 MySQL 在索引里，使用到的字节数。
根据这个值可以计算出，具体使用到了 索引中的哪些列。- 如果为 联合索引，则显示已命中的联合索引长度之和。
如：联合索引为a+b+c ，如果索引命中了a+b，那么长度就为a+b的索引长度。- 通常可以通过 key_len列，来分析联合索引所命中的情况。
explain select * from film_actor where film_id = 2;
- 通过结果中的
key_len=4可推断出。
查询使用了 film_id列 来执行索引查找。
- 例如：film_actor 的联合索引 idx_film_actor_id。
由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是 4字节。
7.1 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。
- 索引 最大长度是 768 个字节：
- 当字符串过长时，MySQL 会做一个类似左前缀索引的处理。
- 将前半部分的字符，提取出来做索引。
7.2 关于 possible_keys 和 key 的三种关系场景
- possible_keys != null && key !=null，这是正常使用到了索引的情况。
- possible_keys !=null && key == null，这种情况一般说明通过索引并不能提升多少效率。
一般而言是表的数据量很少，或者是索引的字段离散性不高，执行计划发现用索引和扫表差不多。- possible_keys == null && key !=null，这种情况一般为 where 条件没有命中索引。
但是查询的列是索引字段，也就是查询的列命中覆盖索引情况。
8. ref 查询的列或常量
- 显示在 key列 记录的索引中，表查询时所用到的 列 或 常量。
- 常见的有：const（常量）、字段名（例：film.id）。
- 实际用到的索引是哪个表的列，const 代表常量。
9. rows 扫描的行数
- 显示 MySQL 预计要读取并检索的行数。
- 注意这个不是结果集里的行数。
- 一般来说扫描的数据行数越少，性能越好。
10. Extra 扩展信息【重要】
- Extra 是额外信息：对整个 SQL做了一个概括性的总结
包括使用了 什么索引、排序方式、使用了临时表，主要不适合在其他列显示，但是十分重要的信息。
Using index：使用覆盖索引。Using where：使用 where 条件语句来处理结果。Using index condition：查询的列不完全被索引覆盖。Using temporary：MySQL 需要创建一张 临时表 来处理查询。Using filesort：使用外部文件排序，而不是索引排序。Select tables optimized away：使用某些聚合函数。Using index sort：使用索引排序。
通常情况下这是一种好现象，索引天然有序，所以避免了通过 sort buffer 来排序的流程。Using join buffer：使用了 join buffer 缓存。
这种情况关注一下关联查询的字段，是不是没有建索引。
10.1 Using index 使用覆盖索引
- 通常情况下这是一种好现象，意味着查询的数据直接在二级索引返回了，从而减少了回表的过程。
explain
select film_id from film_actor where film_id = 1;
10.2 Using where 使用 where 语句来处理结果
- 查询的列未被索引覆盖。
explain select * from actor where name = 'a';
10.3 Using index condition 查询的列不完全被索引覆盖
- where 条件中是一个前导列的范围。
explain select * from film_actor where film_id > 1;
10.4 Using temporary MySQL 需要创建一张 临时表 来处理查询
- 出现这种情况，一般是要进行优化的，首先考虑使用索引来优化。
- 常见于 group by、order by。
explain
select distinct name from actor;
- actor.name 没有索引。
此时创建了张 临时表 来 distinct 去重。
explain select distinct name from film;
- film.name 有 idx_name 索引。
此时查询时，Extra 是using index没有用临时表。
10.5 Using filesort 使用外部文件排序，而不是索引排序
- 数据较小时在 内存排序，否则需要在 磁盘完成排序。
这种情况，也是 需要考虑使用索引来优化的。- 文件排序，就是使用了 非索引的字段 进行排序。
explain
select * from actor order by name;
- actor.name 没有索引。
会检索整个 actor 表，保存排序关键字 name 和 对应的行id。
然后 排序name 并检索行记录。
explain
select * from film order by name;
- film.name 有 idx_name 索引。
此时查询时，Extra 是using index（索引排序）。
10.6 Select tables optimized away 使用某些聚合函数
- 如：max、min 来访问 存在索引的某个字段。
explain select min(id) from film;
五、MySQL 慢查询日志
- MySQL 慢查询日志 默认不开启。
- 开启后 MySQL 会把【查询时间 大于 设置时间的SQL记录下来】，并保存到一个专门的文件中。
1. 配置——慢查询日志
- 在 MySQL 配置文件中（/etc/my.conf）进行配置，修改后重启 MySQL 生效。
# `开启或关闭`慢查询日志。
slow_query_log=ON
# 指定生成慢查询日志路径（未设置则默认和数据文件放一起）。
slow_query_log_file=/opt/soft/mysql/log/slow.log
# 慢查询记录时间阈值，SQL执行超过此时间则会被记录到日志（单位秒，默认10秒）。
long_query_time=5
# 是否记录未使用到索引的SQL（尽管这条SQL语句有可能执行得挺快）。
log_queries_not_using_indexes=on
show VARIABLES like '%query%';# 0_OFF、1_ON
set global slow_query_log=1;
# 1秒。
set long_query_time=1;
2. 分析——慢查询日志
- MySQL 提供了【分析慢查询日志的工具】mysqldumpslow。
# 查询【返回结果最多】的10条SQL。
mysqldumpslow -s r -t 10 /usr/local/mysql/data/localhost_slow.log# 查询【耗时最长】的10条SQL。
mysqldumpslow -s t -t 10 /usr/local/mysql/data/localhost_slow.log
| 参数 | 描述 |
|---|---|
| -s | 是 sort 的意思，表示按照何种方式排序。 c 访问次数、l 锁定时间、r 返回记录数、t 查询时间 ac 平均访问次数、al 平均锁定时间、ar 平均返回记录数、at 平均查询时间。 |
| -t | 是 top ‘n’ 的意思，即为返回前面多少条的数据。 |
| -g | 是 grep 的意思，后边可以写一个正则匹配模式，大小写不敏感的。 |
3. 其他语句——慢查询日志
- 默认情况下 管理语句 是不会被记录到 慢查询日志 中。
- 管理语句包括 ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE 和 REPAIR TABLE 等。
show VARIABLES like '%log_slow_admin_statements%';set global log_slow_admin_statements=ON;
- 默认情况下，不使用索引的语句，也是不会被记录的。
show VARIABLES like '%log_queries_not_using_indexes%';set global log_queries_not_using_indexes=ON;
