开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第15天，点击查看活动详情
1.3.索引优化
- 下文高效索引中做出较为详细的叙述，这里不再赘述。
1.4.性能分析
1.4.1.explain
explain select * from user where name like "张三%"
-
在 select 语句前加上 explain 关键字，即可查询该语句的性能情况。
explain 结果说明：
-
id：select 识别符。这是 select 的查询序列号。
-
select_type：select 类型。
- simple： 简单 select (不使用 union 或子查询)
- primiry： 最外面的 select
- union：union 中的第二个或后面的 select 语句
- dependent union：union 中的第二个或后面的SELECT语句，取决于外面的查询
- union result：union 的结果
- subquery：子查询中的第一个 select
- dependent subquery：子查询中的第一个 select，取决于外面的查询
- derived：导出表的 select (from 子句的子查询)
-
table：表名
-
type：联接类型。是 SQL 性能的非常重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说要保证查询至少达到 range 级别。
- system：表仅有一行(=系统表)。这是 const 联接类型的一个特例。
- const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const 用于用常数值比较PRIMARY KEY 或 UNIQUE 索引的所有部分时。
- eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了 const 类型。它用在一个索引的所有部分被联接使用并且索引是 UNIQUE 或 PRIMARY KEY。eq_ref 可以用于使用 = 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
- ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是 UNIQUE 或PRIMARY KEY (换句话说，如果联接不能基于关键字选择单个行的话)，则使用 ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref 可以用于使用 = 或 <=> (MySQL特有) 操作符的带索引的列。
- ref_or_null：该联接类型如同 ref，但是添加了 MySQL 可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该联接类型的优化。
- index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key 列包含了使用的索引的清单，key_len 包含了使用的索引的最长的关键元素。
- unique_subquery：该类型替换了下面形式的 IN 子查询的 ref：value IN (SELECT primary_key FROM single_table WHERE some_expr); unique_subquery 是一个索引查找函数，可以完全替换子查询，效率更高。
- index_subquery：该联接类型类似于 unique_subquery。可以替换 IN 子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
- range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引。key_len 包含所使用索引的最长关键元素。在该类型中 ref 列为NULL。当使用=、!=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比较关键字列时，可以使用 range。
- index：该联接类型与 ALL 相同，除了只有索引树被扫描。这通常比 ALL 快，因为索引文件通常比数据文件小。
- all：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记 const 的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用 ALL，使得行能基于前面的表中的常数值或列值被检索出。
-
possible_keys：possible_keys 列指出 MySQL 能使用哪个索引在该表中找到行。注意，该列完全独立于 EXPLAIN 输出所示的表的次序。这意味着在possible_keys 中的某些键实际上不能按生成的表次序使用。
-
key：key 列显示 MySQL 实际决定使用的键 (索引) 。如果没有选择索引，键是 NULL 。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX 。
-
key_len：key_len 列显示 MySQL 决定使用的键长度。如果键是 NULL ，则长度为 NULL 。注意通过 key_len 值我们可以确定 MySQL 将实际使用一个多部关键字的几个部分。
-
ref：ref 列显示使用哪个列或常数与 key 一起从表中选择行。
-
rows：rows 列显示 MySQL 认为它执行查询时必须检查的行数。
-
Extra：该列包含 MySQL 解决查询的详细信息。
- Distinct：MySQL 发现第 1 个匹配行后，停止为当前的行组合搜索更多的行。
- Not exists：MySQL 能够对查询进行 LEFT JOIN 优化，发现 1 个匹配 LEFT JOIN 标准的行后，不再为前面的的行组合在该表内检查更多的行。
- range checked for each record (index map: #)：MySQL 没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL 检查是否可以使用 range 或 index_merge 访问方法来索取行。
- Using filesort：MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
- Using index：只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略 (可以理解为覆盖索引)。
- Using temporary：为了解决查询，MySQL 需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的 GROUP BY 和 ORDER BY 子句时。
- Using where：WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果 Extra 值不为 Using where 并且表联接类型为 ALL 或 index ，查询可能会有一些错误。
- Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为 index_merge 联接类型合并索引扫描。
- Using index for group-by：类似于访问表的 Using index 方式，Using index for group-by 表示 MySQL 发现了一个索引，可以用来查询 GROUP BY 或 DISTINCT 查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。
-
1.4.2.show profile
-
show profile 语句用于追踪当前会话中所执行的语句的资源消耗信息，常用于性能分析，方便后续 SQL 调优。语法如下：
SHOW PROFILE [type [, type] ... ] [FOR QUERY Query_ID] [LIMIT row_count [OFFSET offset]] type: { ALL：显示所有信息 | BLOCK IO：显示块输入输出操作的次数 | CONTEXT SWITCHES：显示上下文切换次数 | CPU：显示用户和系统 CPU 使用时间 | IPC：显示发送和接收消息的次数 | MEMORY：暂未实现 | PAGE FAULTS：显示缺页次数 | SOURCE：显示源文件中的函数名以及函数在文件中的行号 | SWAPS：显示 swap 次数 }如果后面未衔接 FOR QUERY Query_ID 语句，输出会包含最近执行过的多条语句；如果衔接 FOR QUERY Query_ID 语句，SHOW PROFILE 只会显示该 Query_ID 的语句的信息；Query_ID 值可通过 SHOW PROFILES 查看。默认情况下，SHOW PROFILE 只显示 Status 和 Duration 列。
SHOW PROFILES 显示最近发送给 MySQL Server 的语句列表，列表大小由 profiling_history_size 变量控制，默认为 15 条，最大为 100 条，设置为 0 则实际关闭该选项。
profiling 默认关闭，如需使用，需手动开启，开启命令如下：
select @@profiling; -- 查看 profiling 是否开启 set profiling = 1; -- 开启 profilingprofiling 信息是与 session 关联的，当 session 结束时, 它的 profiling 信息就会被丢弃。profiling 信息也可从 INFORMATION_SCHEMA 的 PROFILING 表中查到。SHOW PROFILE 和 SHOW PROFILES 语句本身不会被统计，但是非法的或错误的语句会被统计。
注释：
- 在 MySQL 5.7 的官方文档中指出，show profile 和 show profiles 已经弃用，将在未来的版本中删除。应该改用 Performance Schema。原文档内容如下：The SHOW PROFILE and SHOW PROFILES statements are deprecated; expect them to be removed in a future MySQL release. Use the Performance Schema instead; see Section 25.19.1, Query Profiling Using Performance Schema”.
1.4.3.slow log& show processlist
-
查询慢查询日志相关参数：
show variables like '%slow_query%';相关参数同时记录了慢查询日志的本地存储位置，慢查询日志记录了包含所有执行时间超过参数 long_query_time (单位：秒) 所设置值的 SQL 语句的日志，在 MySQL 上用命令可以查看，如下：
show variables like 'long%';同时，这个参数是可以修改的，也可以设置成小数，更精细的控制。
-
查询表状态：
show processlist可以查询到各个表的执行状态以及是否等待某些流程。
1.4.4.表空间
-
表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以 .frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，在这里主要讨论的是表数据。
innodb_file_per_table参数：
- OFF：表的数据放在系统共享表空间，也就是跟数据字典放在一起；
- ON：每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的，因此建议将这个参数一直设置为 ON。下列叙述均为在此基础上的讨论。
-
空间复用：
- delete 删除某一条数据只会删除该记录，而不会删除表空间，因此会造成数据空洞，此时空间可以在下次插入数据的时候复用，但只限于符合范围条件的数据。如果删除整个表，那么整个数据页都可以复用，且可以复用到任何位置，但是磁盘上的文件不会变小。
- insert 插入某一条数据也会造成数据空洞，如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。
- update 更新某一条数据也会造成数据空洞，可以理解为删除一个旧的值，再插入一个新值。
经过大量增删改的表，都是可能是存在空洞的。所以如果能够把这些空洞去掉，就能达到收缩表空间的目的。重建表即可。
alter table A engine=InnoDB在 MySQL 5.5 版本之 前，上述命令相当于新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中，再将表名修改回去。MySQL 会自动完成转存数据、交换表名、删除旧表的操作；而 MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。
- 建立一个临时文件，扫描表 A 主键的所有数据页；
- 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
- 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
- 用临时文件替换表 A 的数据文件。
alter 语句在启动的时候需要获取 MDL 写锁，为了实现 Online，MDL 读锁不会阻塞增删改操作，这个写锁在真正拷贝数据之前会退化成读锁。
1.4.5.重建表
-
在 MySQL5.6 版本之后，对于部分 alter table 重建表命令，加入新的执行算法，可以进行 DDL 时并行现有业务 (DML 操作)，可以通过 alter table 时添加 ALGORITHM 参数控制使用算法。目前可以支持的主流算法有三种：
- Copy：MySQL 5.6 之前非 Online，默认的方式
- Inplace：MySQL 5.6 支持
- Instant：MySQL 8.0.12 支持
DDL 操作在执行时，都会经历三个阶段：准备阶段 prepare、执行阶段 DDL、提交阶段 commit，不同之处是在三个阶段中分别作了不同的处理，对开发者而言，关心 Online 与否，通常只与一个问题有关：是否允许并发 DML。Copy 算法执行的 DDL 一定不是 Online 的，Inplace 算法执行的 DDL 不一定是 Online 的
-
Copy：指 DDL 时，会生成 (临时) 新表，将原表数据逐行拷贝到新表中，在此期间会阻塞 DML，该操作是 offline (离线的而非在线的) 的，全程无法并行DML。
准备：
- 对表加元数据共享锁，读取 frm 元数据 (此时DDL不能并行，DML可以并行)
- 共享锁升级为排他锁 (此时 DDL、DML 都不能并行，容易造成数据库堵塞)
- 在 Server 层通过 create like 语句，创建临时表，Engine 层也生成对应 ibd、frm 文件 (8.0之后没有 frm 文件)
执行(offline)：
- 修改临时表元数据 (加列)
- 拷贝原表数据到临时表 (最耗时，所有数据需要按行copy)
- 删除原表及文件
- 重命令临时表及文件
提交：
- 提交事务，释放锁
-
Inplace：在 MySQL5.6 出现的新算法，无需拷贝全表数据到新表，但可能还是需要 IN-PLACE 方式 (原地，无需生成新的临时表) 重建整表。这种情况下，在DDL 的初始准备和最后结束两个阶段时通常需要加排他 MDL 锁 (metadata lock，元数据锁)，除此外，DDL 期间不会阻塞 DML。
准备:
- 对表加元数据共享升级锁，并升级为排他锁 (此时DML不能并行)；
- 判断使用 inplace 算法；
- 判断语句是 rebuild table 还是 no-rebuild，rebuild 在原表所在的路径下创建 .frm 和 .ibd 临时中转文件 (在引擎层克隆，而不是像 copy 那样在 server 层创建(create like)，注意加列的操作需要 rebuild table 生成中转文件表，而 no-rebuild 除创建二级索引外只创建 .frm 文件，其中添加二级索引操作最为特殊，该操作属于 no-rebuild 不会生成 .ibd，但实际上对 .ibd 文件却做了修改，该操作会在参数 tmpdir 指定路径下生成临时文件，用于存储索引排序结果，然后再合并到 .ibd 文件中；
- 申请 row log 空间，用于存放 DDL 执行阶段产生的 DML 操作 (no-rebuild不需要，此操作在 innodb_sort_buffer 块中)。
执行(online)：
- 释放排他锁，保留元数据共享升级锁 (此时 DML 可以并行)；
- 扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中 (在引擎层扫描，最耗时)；
- 将所有对原表的 DML 操作记录在日志文件 row log 中，并回放部分 row log 。
提交阶段:
- 升级元数据，共享锁升级，产生排他锁锁表 (此时 DML 不能并行)；
- 重做 row log 中的内容 (no- rebuild不需要)；
- 重命名原表文件，将临时文件改名为原表文件名，删除原表文件；
- 提交事务，变更完成。
在 DDL 期间产生的数据，会按照正常操作一样，写入原表，记 redolog、undolog、binlog，并同步到从库去执行，只是额外会记录在 row log 中，并且写入row log 的操作本身也会记录 redo log，而在提交阶段才进行 row log 重做，此阶段会锁表，此时主库 (新表空间 + row log) 和从库 (表空间) 数据是一致的，在主库 DDL 操作执行完成并提交，这个 DDL 才会写入 binlog 传到从库执行，在从库执行该 DDL 时，这个 DDL 对于从库本地来讲仍然是 online 的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产生 row log。但是对于主库同步过来 DML，此时会被阻塞，是 offline 的，DDL 是排他锁的在复制线程中也是一样，所以不只会阻塞该表，而是后续所有从主库同步过来的操作 (主要是在复制线程并行时会排他，同一时间只有他自己在执行) 。所以大表的 DDL 操作，会造成同步延迟。
-
Instant：只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他 MDL 锁，原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的，也不会阻塞 DML 。唯一会阻塞只读的时机是在清理旧表结构和表定义缓存时。
新特性是 8.0.12 引入的 (腾讯DBA团队贡献) 。执行 DDL 操作时，ALGORITHM 选项可以不指定，这时候 MySQL 按 INSTANT、INPLACE、COPY 的顺序自动选择合适的模式。也可以指定 ALGORITHM=DEFAULT，也是同样的效果。如果指定了 ALGORITHM 选项，但不支持的话，会直接报错。目前只支持添加列等少量操作（还不是太成熟，企业中一般都是 5.6、5.7 版本），利用 8.0 新的表结构设计，可以直接修改表的 metadata 数据，省掉了 rebuild 的过程，极大的缩短了 DDL 语句的执行时间。
注释：optimize table、analyze table 和 alter table 的区别
- alter table (即recreate)：MySQL 5.5 及之前，使用 Copy 方式重建表，MySQL 5.6 版本开始，默认为 Inplace 方式；
- analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
- optimize table t 等于 recreate + analyze 的命令之和
1.4.6.Order by
-
全字段排序：对于 order by 而言，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。示例语句如下：
select city, name, age from t where city='杭州' order by name limit 1000;通常情况下，这个语句执行流程如下所示：
- 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
- 从索引 city 找到第一个满足 city='杭州' 条件的主键 id，也就是图中的 ID_X；
- 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
- 从索引 city 取下一个记录的主键 id；
- 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
- 对 sort_buffer 中的数据按照字段 name 做快速排序；
- 按照排序结果取前 1000 行返回给客户端。
上述流程可以称之为全字段排序。按 name 排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size，即 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存容量不够，则不得不利用磁盘临时文件辅助排序。
-
rowid排序：如果查询要返回的字段很多，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。 所以如果单行很大，全字段排序方法效率不够好。修改 max_length_for_sort_data 参数
SET max_length_for_sort_data = 16;max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。 city、name、age 这三个字段的定义总长度是 36，将 max_length_for_sort_data 设置为 16，新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。
- 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
- 从索引 city 找到第一个满足 city='杭州’ 条件的主键 id，也就是图中的 ID_X；
- 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
- 从索引 city 取下一个记录的主键 id；
- 重复步骤 3、4 直到不满足 city='杭州’ 条件为止，也就是图中的 ID_Y；
- 对 sort_buffer 中的数据按照字段 name 进行排序；
- 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
如果内存足够大，还是优先选择全字段排序，这样避免再根据主键 id 从磁盘中取出数据。
-
order by rand()：在一个单词表里随机取出3个值并显示，但是随着表越来越大，响应速度会越来越慢，设 words 表中有 1w 条数据，语句示例如下：
select word from words order by rand() limit 3;使用 explain 分析该语句，Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。即表示需要临时表，并且需要在临时表上排序。对于 InnoDB 表来说，执行全字段排序会减少磁盘访问， 因此会被优先选择。而对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。临时表大多情况为内存表，优化器没有了这一层顾虑，用于排序的行越小越好，所以，MySQL 这时就会选择 rowid 排序。当然，tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表，磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。
上述语句执行步骤如下：
- 创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
- 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 1w。
- 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
- 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
- 从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位 置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 1w，变成了 2w。
- 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会 增加扫描行数。
- 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。
MySQL 5.6 版本引入了优先队列排序算法，对于只取3条数据这种特殊场景，只需维护一个大小为3的堆，不断更新三个值即可。但是无论使用哪种类型的临时表，order by rand() 这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。通常，常见的做法是通过id最大值与id最小值之间生成三个随机数，取不小于这个随机数的id值即可，这个方法效率很高，因为取 max(id) 和 min(id) 都是不需要扫描索引的，而 select 也可以用索引快速定位，可以认为就只扫描了 3 行，但是id中间可能会有空洞，造成伪随机，因此为了严格随机，可以执行如下步骤：
- 取得整个表的行数，并记为 C。
- 取得 Y = floor(C * rand()) (floor 函数在这里表示取整数部分)。
- 再用 limit Y,1 取得一行。
- 重复执行 2,3 三次即可。
MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要 扫描 C+Y+1 行，执行代价比上述伪随机算法的代价要高，不过要远低于 rand() 函数。
