1. 执行计划

引入CLASS和STUDENT表。 布局： class.sql

DROP TABLE IF EXISTS `CLASS`;

CREATE TABLE `CLASS`
(
    `ID`   INT,
    `NAME` VARCHAR(50) NOT NULL,
    `LOC`  VARCHAR(50),
    PRIMARY KEY (ID),
    UNIQUE (NAME)
) ENGINE = INNODB
  DEFAULT CHARSET = UTF8MB4;

INSERT INTO `CLASS`
VALUES (1, 'J500', '北京'),
       (2, 'J501', '上海'),
       (3, 'J502', '广州'),
       (4, 'J503', '深圳');
COMMIT;

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'CLASS'

布局： student.sql

DROP TABLE IF EXISTS `STUDENT`;
CREATE TABLE `STUDENT`
(
    `ID`       INT,
    `NAME`     VARCHAR(50) NOT NULL,
    `GENDER`   INT,
    `AGE`      INT,
    `INFO`     VARCHAR(100),
    `CLASS_ID` INT,
    PRIMARY KEY (ID),
    UNIQUE (NAME),
    FOREIGN KEY (CLASS_ID) REFERENCES CLASS (ID)
) ENGINE = INNODB
  DEFAULT CHARSET = UTF8MB4;

INSERT INTO `STUDENT`
VALUES (1, '赵四', 1, 50, '亚洲舞王', 1),
       (2, '刘能', 1, 52, '58同镇', 2),
       (3, '广坤', 1, 54, '山货', 3),
       (4, '大脚', 2, 55, '大脚超市', 4);
COMMIT;

CREATE INDEX I_U_GENDER ON `STUDENT` (GENDER);

SHOW INDEX FROM `STUDENT`;

概念： 执行计划是一条查询语句的执行过程或者访问路径的描述，通过在查询语句的最前方增加 EXPLAIN 关键字来完成：

ID：执行的优先级：
- 如果 ID 值相同，语句会从上到下执行。
- 如果 ID 值不同，先执行 ID 值大的那条语句，然后再执行小的。
SELECT_TYPE：查询类型：
- SIMPLE：简单的查询，不包含子查询或UNION查询。
- PRIMARY：包含子查询的最外层查询。
- SUBQUERY：在SELECT后，或者WHERE后包含了子查询。
- DEPENDENT SUBQUERY：关联子查询。
- UNION：当第二个SELECT出现在UNION之后，则标记位UNION。
- UNION RESULT：使用UNION集合的结果。
TABLE：显示这一行数据是关于哪张表。
PARTITIONS：对于分区表显示分区编号，null表示未分区。
TYPE：表示本次查询所使用的方式，除null外，效率从上到下依次降低，最好将SQL提升至RANGE以上：
- null表示未访问任何一个表。
- SYSTEM 是 CONST 的一个特例，前提是你的表中只有一行数据，效率比 CONST 更快。
- CONST：表示通过索引一次就找到了结果，相当于读了常量，一般可能出现于唯一约束的定值查询中。
- EQ_REF：表示在连表查询中，关联字段均为唯一约束，且查询的字段是主键或关联字段。
- REF：按照索引查找，且这个索引列是不唯一的。
- REF_OR_NULL：类似于 REF，但是附加了对null值列的查询。
- RANGE：范围索引扫描，当你对索引列进行范围查找时，如 BETWEEN、>、<、IN 等这样的查询条件。
- INDEX：全索引扫描，当你查询的字段是索引列时。
- ALL：全数据扫描，当你查询的字段是非索引列时。性能最差。
POSSIBLE_KEYS：查询所涉及的字段上如果有索引，则都会被列出，但不一定会被使用，这里展示的都是索引的名字。
KEY：实际使用到的索引名，这个值会从 POSSIBLE_KEYS 中选取：
- 如果为null，则表示mysql没有使用索引。
KEY_LEN：表示索引字段的最大可能长度，key_len的长度由字段定义计算而来，并非数据的实际长度，在不损失准确性的前提下，越小越好，单位字节。
REF：关联信息，当连接类型为 CONST、EQ_REF 或 REF 时的关联信息，
- 如：条件 WHERE ID = 4，4是常量，那么 REF 的值是 CONST。
- 如：WHERE A.AGE = B.AGE，REF 的值就是 B.AGE。
ROWS：表示mysql认为它在找到正确结果之前必须扫描的行数，最理想的结果是1，ROWS的值的大小是个抽样统计的结果，并不十分准确。
FILTERED：利用率，表示返回结果的行数占需要读取行数的百分比，利用率越大越好。
EXTRA：备注，这里的大多数选项会对查询产生负面影响：
- DISTINCT：表示使用了优化 DISTINCT 操作。
- NO TABLES：表示查询中没有 FROM 表的语句或者 FROM DUAL。
- NOT EXISTS：表示在某些 LEFT JOIN 的查询中，mysql使用了更优化的查询。
- USING WHERE：表示我们不是全查，而是条件查询。
- USING INDEX：表示要查的数据在索引中就可以获取，不用去表中查询。
- USING TEMPORARY：表示mysql必须使用一个中间表来查询。
- USING FILESORT：表示查询中有 ORDER BY，但是无法使用索引完成排序，它只能使用相应的排序算法来排序。

无法使用执行计划对触发器和存储过程进行查看。

源码： optimization/执行计划.sql

# SELECT_TYPE = SIMPLE
EXPLAIN
SELECT *
FROM `STUDENT`;

# SELECT_TYPE = PRIMARY
# SELECT_TYPE = SUBQUERY
EXPLAIN
SELECT *
FROM `STUDENT`
WHERE ID < ALL (SELECT ID FROM `CLASS`);

# SELECT_TYPE = DEPENDENT SUBQUERY
EXPLAIN
SELECT *
FROM `STUDENT`
WHERE EXISTS(SELECT 1 FROM `CLASS` WHERE `STUDENT`.CLASS_ID = `CLASS`.ID);

# SELECT_TYPE = UNION
# SELECT_TYPE = UNION RESULT
EXPLAIN
SELECT `ID`
FROM `STUDENT`
UNION
SELECT ID
FROM `CLASS`;

# TYPE=CONST
EXPLAIN
SELECT *
FROM `STUDENT`
WHERE ID = 1;

# TYPE = EQ_REF
EXPLAIN
SELECT STUDENT.NAME
FROM `STUDENT`
         JOIN `CLASS` ON CLASS.ID = STUDENT.CLASS_ID;

# TYPE=REF
EXPLAIN
SELECT *
FROM `STUDENT`
WHERE GENDER = 0;

# RANGE
EXPLAIN
SELECT *
FROM `STUDENT`
WHERE ID BETWEEN 2 AND 3;

# INDEX
EXPLAIN
SELECT GENDER
FROM `STUDENT`;

# ALL
EXPLAIN
SELECT `INFO`
FROM `STUDENT`;

2. 慢查询

概念： 分析mysql语句查询性能的方法除了查看执行计划外，还可以让mysql记录下超过指定时间的语句，我们将超过指定时间的SQL查询称为慢查询：

查看慢查询的时间定义：
- SHOW VARIABLES LIKE 'LONG%'
设置慢查询时间定义为0.01秒：
- SET LONG_QUERY_TIME = 0.00001
查看慢查询配置信息：结果集中都是全局变量，不属于某张表所有：
- SHOW VARIABLES LIKE 'SLOW%'
将慢查询日志存放到自己指定的位置：只有慢查询的语句会纪录在这个日志中
- SET GLOBAL SLOW_QUERY_LOG_FILE = 'D:SLOW.LOG'
- 需要使用root权限来进行操作。

前提需要将show_query_log设置为on状态。

源码： optimization/慢查询.sql

SHOW VARIABLES LIKE 'LONG%';

SET LONG_QUERY_TIME = 0.0001;

SHOW VARIABLES LIKE 'SLOW%';

SET GLOBAL SLOW_QUERY_LOG_FILE = 'D:SLOW.LOG';

SELECT * FROM EMP;

3. 优化细则

在经常查询的字段上建立索引。
多使用SQL的内置函数。
SQL中的关键字都大写，因为数据库总是先解析SQL语句，把小写的字母转换成大写的再执行。
只要有可能，在程序中尽量多使用 COMMIT，因为它可以释放一些资源：
- 包括回滚段上用于恢复数据的信息。
- 包括被程序语句获得的锁。
- 包括redo log buffer中的空间。
- 包括数据库为管理上述3种资源中的内部花费。
在索引列上使用NOT会导致索引失效。
避免触发隐式转换，如果字段是字符类型，就不要用数组类型的值，且如果恰好是索引列，索引会失效。
把大筛选量的WHERE往后排，因为数据库采用自后而前的顺序解析WHERE条件，根据这个原理，那些可以过滤掉最大数量记录的WHERE条件尽量写在所有WHERE条件的末尾。
全查中避免使用 *，mysql在解析的过程中，会将 * 依次转换成所有的列名，这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间，虽然这个时间几乎可以忽略，但是 * 的代码可读性仍然很差。
删表记录时，用 TRUNCATE 替代 DELETE，当删除表中的记录时，在通常情况下，回滚段(rollback segments) 用来存放可以被恢复的信息，如果你没有COMMIT事务，数据库会将数据恢复到删除之前的状态，准确地说是恢复到执行删除命令之前的状态，而当运用 TRUNCATE 时, 回滚段不再存放任何可被恢复的信息，当命令运行后，数据不能被恢复，因此很少的资源被调用，执行时间也会很短。
多使用表的别名，当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个字段上，这样一来，就可以减少解析的时间并减少那些由列名歧义引起的语法错误。
用 NOT EXISTS 替代 NOT IN，在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接，在这种情况下，使用 EXISTS 或 NOT EXISTS 通常将提高查询的效率。在写成外连子查询中，NOT IN 子句将执行一个内部的排序和合并，无论在哪种情况下，NOT IN 都是最低效的，因为它对子查询中的表执行了一个全表遍历。
用 EXISTS 替换 DISTINCT，当提交一个包含一对多表信息的查询时，避免在SELECT子句中使用 DISTINCT，一般可以考虑用 EXIST 替换，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。
避免在索引列上进行计算，避免在索引列上使用计算，会导致索引失效。
多使用 >= 替代 >，两者的区别在于，>3 首先定位到 ID=3 的记录并且向后扫描到第一个ID大于3的记录，而 >=3 将直接跳到第一个 ID=3 的记录。
用UNION替换OR (适用于索引列)，通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果，对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效，如果字段没有被索引，查询效率可能会因为你没有选择OR而降低。
避免在索引列上使用 IS NULL 和 IS NOT NULL，避免在索引中使用任何可以为空的列，数据库将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。举例：如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A，B 值为(123，null)，数据库将不接受下一条具有相同A，B值（123，null）的记录(插入)。若所有的索引列都为空，数据库将认为整个键值为空而空不等于空。因此你可以插入1000条具有相同键值的记录，当然它们都是空，因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使索引失效。
需要当心的WHERE子句，某些SELECT语句中的WHERE子句会让索引失效，这里有一些例子：
- != 将不使用索引，记住，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。
- || 是字符连接函数，就象其他函数那样，会让索引失效。
- + 是数学函数，就象其他数学函数那样，会让索引失效。
- 相同的索引列不能互相比较，这将会启用全表扫描。
优化 GROUP BY，通过将不需要的记录在 GROUP BY 之前过滤掉，可以提高 GROUP BY 语句的效率。

源码： optimization/优化细则.sql

# 在索引列上使用NOT会导致索引失效。

# 低效
EXPLAIN
SELECT *
FROM EMP
WHERE EMPNO IS NOT NULL;

# 高效
EXPLAIN
SELECT *
FROM EMP
WHERE EMPNO = 7369;

# 避免触发隐式转换，如果字段是字符类型，就不要用数组类型的值
# 且如果恰好是索引列，索引会失效。CREATE INDEX I_ENAME ON EMP(ENAME);
CREATE INDEX I_ENAME ON EMP (ENAME);

# 低效
EXPLAIN
SELECT *
FROM EMP
WHERE ENAME = 7369;

# 高效
EXPLAIN
SELECT *
FROM EMP
WHERE ENAME = 'SMITH';

# 把大筛选量的WHERE往后排，因为数据库采用自后而前的顺序解析WHERE条件
# 根据这个原理，那些可以过滤掉最大数量记录的WHERE条件尽量写在所有WHERE条件的末尾。

# 低效
SELECT *
FROM EMP
WHERE SAL > 500
  AND ENAME IN ('SMITH', 'WORD');

# 高效
SELECT *
FROM EMP
WHERE ENAME IN ('SMITH', 'WORD')
  AND SAL > 500;

# 全查中避免使用 `*`，mysql在解析的过程中，会将 `*` 依次转换成所有的列名
# 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间，虽然这个时间几乎可以忽略
# 但是 `*` 的代码可读性仍然很差。

# 低效
SELECT *
FROM EMP;

# 高效
SELECT EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
FROM EMP;

# 删表记录时，用 `TRUNCATE` 替代 `DELETE`，当删除表中的记录时
# 在通常情况下，回滚段(rollback segments) 用来存放可以被恢复的信息
# 如果你没有COMMIT事务，数据库会将数据恢复到删除之前的状态
# 准确地说是恢复到执行删除命令之前的状态，而当运用 `TRUNCATE` 时
# 回滚段不再存放任何可被恢复的信息，当命令运行后，数据不能被恢复
# 因此很少的资源被调用，执行时间也会很短。

# 低效
DELETE
FROM EMP;

# 高效
TRUNCATE TABLE EMP;

# 多使用表的别名，当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个字段上
# 这样一来，就可以减少解析的时间并减少那些由列名歧义引起的语法错误。

# 低效
SELECT *
FROM EMP
         JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

# 高效
SELECT E.ENAME, D.DEPTNO
FROM EMP E
         JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

# 用 `NOT EXISTS` 替代 `NOT IN`，在许多基于基础表的查询中
# 为了满足一个条件，往往需要对另一个表进行联接，在这种情况下
# 使用 `EXISTS` 或 `NOT EXISTS` 通常将提高查询的效率。在写成外连子查询中
# `NOT IN` 子句将执行一个内部的排序和合并，无论在哪种情况下，`NOT IN` 都是最低效的
# 因为它对子查询中的表执行了一个全表遍历。

# 低效
SELECT *
FROM DEPT
WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);

# 高效
SELECT *
FROM DEPT
WHERE NOT EXISTS(SELECT 1 FROM EMP WHERE DEPT.DEPTNO = EMP.DEPTNO);

# 用 `EXISTS` 替换 `DISTINCT`，当提交一个包含一对多表信息的查询时
# 避免在SELECT子句中使用 `DISTINCT`，一般可以考虑用 `EXIST` 替换
# 因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

# 低效
SELECT DISTINCT(D.DEPTNO)
FROM DEPT D
         JOIN EMP E ON D.DEPTNO = E.DEPTNO;

# 高效
SELECT DEPTNO
FROM DEPT D
WHERE EXISTS(
              SELECT 1 FROM EMP E WHERE D.DEPTNO = E.DEPTNO
          );

# 避免在索引列上进行计算，避免在索引列上使用计算，会导致索引失效。

# 低效
EXPLAIN
SELECT *
FROM EMP
WHERE EMPNO * 12 > 25000;

# 高效
EXPLAIN
SELECT *
FROM EMP
WHERE EMPNO > 25000 / 12;

# 多使用 `>=` 替代 `>`，两者的区别在于，`>3` 首先定位到 `ID=3` 的记录并且向后扫描到第一个ID大于3的记录
# 而 `>=3` 将直接跳到第一个DEPT等于3的记录。

# 低效
SELECT *
FROM EMP
WHERE DEPTNO > 2;

# 高效
SELECT *
FROM EMP
WHERE DEPTNO >= 3;

# 用UNION替换OR (适用于索引列)，通常情况下
# 用UNION替换WHERE子句中的OR将会起到较好的效果
# 对索引列使用OR将造成全表扫描。
# 注意，以上规则只针对多个索引列有效，如果字段没有被索引
# 查询效率可能会因为你没有选择OR而降低。
# 假设在下面的例子中，EMPNO和ENAME上都建有索引

CREATE INDEX I_ENAME ON EMP (ENAME);

# 低效
EXPLAIN
SELECT *
FROM EMP
WHERE EMPNO = 7933
   OR ENAME = 'SMITH';

# 高效
EXPLAIN
SELECT *
FROM EMP
WHERE EMPNO = 7933
UNION
SELECT *
FROM EMP
WHERE ENAME = 'SMITH';

# 避免在索引列上使用 `IS NULL` 和 `IS NOT NULL`

# 低效
EXPLAIN
SELECT *
FROM EMP
WHERE EMPNO IS NOT NULL;

# 高效
EXPLAIN
SELECT *
FROM EMP
WHERE EMPNO >= 0;

# 优化 `GROUP BY`，通过将不需要的记录在 `GROUP BY` 之前过滤掉，可以提高 `GROUP BY` 语句的效率。

# 低效
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB = 'PRESIDENT'
    OR 'JOB' = 'MANAGER';

# 高效
SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB = 'PRESIDENT'
   OR JOB = 'MANAGER'
GROUP BY JOB;

MySQL (6) 优化查询

1. 执行计划

2. 慢查询

3. 优化细则