一切福田，不離方寸，從心而覓，感無不通。

MySQL 查询优化

索引

索引是什么

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
可以简单理解为排好序的快速查找数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

优缺点

优点

  • 提高数据检索的效率，降低数据库的 IO 成本。
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。

缺点

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为 更新所带来的键值变化后的索引信息。
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间 的。

B-Tree 和 B+Tree

区别

  1. B-Tree 的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+Tree 的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
  2. 在 B-Tree 中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+Tree 中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 B-Tree 的性能好像要比 B+Tree 好，而在实际应用中却是 B+Tree 的性能要好些。因为 B+Tree 的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B-Tree 多，树高比 B-Tree 小，这样带来的好处是减少磁盘访问次数。尽管 B+Tree 找到一个记录所需的比较次数要比 B-Tree 多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+Tree 的性能可能还会好些，而且 B+Tree 的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+Tree 的缘故。

为什么 B+Tree 比 B-Tree 更适合实际应用中操作系统的文件索引和数据库索引？

  1. B+Tree 的磁盘读写代价更低

B+Tree 的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B-Tree 更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

  1. B+Tree 的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

索引分类

  1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
    • 建表时，加上 key(列名) 指定
    • 单独创建，create index 索引名 on 表名(列名)
    • 单独创建，alter table 表名 add index 索引名(列名)
  2. 唯一索引：索引列的值必须唯一，但允许有 null 且 null 可以出现多次
    • 建表时，加上 unique(列名) 指定
    • 单独创建，create unique index idx_表名_列名 on 表名(列名)
    • 单独创建，alter table 表名 add unique 索引名(列名)
  3. 主键索引：设定为主键后数据库会自动建立索引，innodb 为聚簇索引，值必须唯一且不能为 null
    • 建表时，加上 primary key(列名) 指定
  4. 复合索引：即一个索引包含多个列
    • 建表时，加上 key(列名列表) 指定
    • 单独创建，create index 索引名 on 表名(列名列表)
    • 单独创建，alter table 表名 add index 索引名(列名列表)

索引语法

  1. 创建，语法如上
  2. 删除
    • 主键索引：alter table 表名 drop primary key
    • 非主键索引：drop index 索引名 on 表名
  3. 查看
    • show index from 表名

适合创建索引的情况

  • 主键自动建立唯一索引；
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题，组合索引性价比更高
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段

不适合创建索引的情况

  • 表记录太少
  • 经常增删改的表或者字段
  • where 条件里用不到的字段不创建索引
  • 过滤性不好的不适合建索引

Explain 性能分析

简介

是什么

查看执行计划：使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或是表结构的性能瓶颈。

能干嘛

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

怎么玩

Explain + SQL 语句。
Explain 执行后返回的信息：

各字段解释

  1. id：select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。
    • id 相同，执行顺序由上至下
    • id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
    • id 有相同也有不同：id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

    id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。

  2. select_type：代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询，取值范围如下：
    • simple：简单的 select 查询，查询中不包含子查询或者 UNION
    • primary：查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
    • derived：在 FROM 列表中包含的子查询被标记为 DERIVED (衍生)，MySQL 会递归执行这些子查询, 把结果放在临时表里。
    • subquery：在 SELECT 或 WHERE 列表中包含了子查询
    • depedent subquery：在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层
    • uncacheable subquery：无法使用缓存的子查询
    • union：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为：DERIVED
    • union result：从 UNION 表获取结果的 SELECT
  3. table：这个数据是基于哪张表的。
  4. type：是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

    只需要记住：system > const > eq_ref > ref > range > index > ALL 就行了，其他的不常见。

    • system：表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计。
    • const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
    • range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
    • index：出现 index 是 sql 使用了索引但是没用索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。
    • all：将遍历全表以找到匹配的行。

    其他 type 如下：

    • index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 关键字的 sql 中。
    • ref_or_null：对于某个字段既需要过滤条件，也需要 null 值的情况下。查询优化器会选择用 ref_or_null 连接查询。
    • index_subquery：利用索引来关联子查询，不再全表扫描。
    • unique_subquery：该联接类型类似于 index_subquery。子查询中的唯一索引。
  5. possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
  6. key：实际使用的索引。如果为 NULL，则没有使用索引。
  7. key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。如何计算 key_len？
    • 先看索引上字段的类型 + 长度，比如：int=4; varchar(20)=20; char(20)=20
    • 如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3，GBK 要乘 2
    • varchar 这种动态字符串要加 2 个字节
    • 允许为空的字段要加 1 个字节
  8. ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
  9. rows：显示 MySQL 认为它执行查询时必须检查的行数。越少越好！
  10. Extra：其他的额外重要的信息。
    • Using filesort：说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。排序字段若通过索引去访问将大大提高排序速度。
    • Using temporary：使用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
    • Using index：表示相应的 select 操作中使用了覆盖索引 (Covering Index)，避免访问了表的数据行，效率不错！如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。
    • Using where：表明使用了 where 过滤。
    • Using join buffer：使用了连接缓存。
    • impossible where：where 子句的值总是 false，不能用来获取任何数据。
    • select tables optimized away：在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    • distinct：优化 distinct 操作，在找到第一匹配的元祖后即停止找同样值的动作。

单表使用索引常见的索引失效

以下内容中 Mysql 的版本为 5.6。

user 表创建 SQL：

1
2
3
4
5
6
7
8
9
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `age` int(3) NOT NULL COMMENT '年龄',
  `address` varchar(255) NOT NULL COMMENT '地址',
  `email` varchar(255) NOT NULL COMMENT '邮件',
  `mobile` varchar(255) NOT NULL COMMENT '手机号码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

 

复合索引创建 SQL：

1
create index idx_user_nameAgeEmail on user(name,age,email);

 

全值匹配我最爱

全职匹配我最爱指的是：查询的字段按照顺序在索引中都可以匹配到！

可以发现全部用到索引了，改变顺序后，是否还成立吗？

SQL 中查询字段的顺序，跟使用索引中字段的顺序没有关系。优化器会在不影响 SQL 执行结果的前提下，自动地优化。

最佳左前缀法则


查询字段与索引字段顺序的不同会导致索引无法充分使用，甚至索引失效！
原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

不要在索引列上做任何计算


不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。

索引列上不能有范围查询


建议：将可能做范围查询的字段的索引顺序放在最后

尽量使用覆盖索引


即查询列和索引列一致，不要写 select *！

使用不等于(!= 或者 <>)的时候


在使用不等于(!= 或者 <>)时，会无法使用索引会导致全表扫描。

is not null 和 is null 有时索引失效

添加一个可为 null 的字段，并创建单值索引：

1
2
alter table user add column remark varchar(255) comment '备注';
create index idx_user_remark on user(remark);

 


remark 字段填充数据之前，执行结果如下：

remark 字段填充数据，只留一行为 null，结果如下：

remark 字段填充数据之后，执行结果如下：

不可控，减少使用 is null 和 is not null！

like 的前后模糊匹配


通配符尽量放右边！否则索引失效。

减少使用 or


使用 or 会导致索引失效，使用 union all 或者 union 来替代。

练习

假设 index(a, b, c)

where 语句 索引是否被使用
where a=3 Y，使用到 a
where a=3 and b=5 Y，使用到 a，b
where a=3 and b=5 and c=4 Y，使用到 a，b，c
where b=3 或者 where b=3 and c=4 或者 where c=4 N，没有 a
where a=5 and c=3 Y，使用到 a，但是 c 不可以，中间 b 断了
where a=3 and b>4 and c=5 Y，使用到 a 和 b，但是 c 不可以，b 是范围
where a=3 and b like 'kk%' and c=4 Y，使用到 a，b，c
where a=3 and b like '%kk' and c=4 Y，使用到 a
where a=3 and b like '%kk%' and c=4 Y，使用到 a
where a=3 and b like 'k%kk%' and c=4 Y，使用到 a，b，c

口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写 *；
不等空值还有 OR，索引失效要少用；
VAR 引号不可丢，SQL 优化有诀窍！

关联查询优化

7 种 join

left join

1
explain select * from class left join book on class.card=book.card;

 

此时 class 和 book 两张表都没有建立索引，为了优化查询，应该在 class 还是 book 上建立索引？

分别在 class 和 book 中建立 card 字段的索引，最终发现：

  1. 在优化关联查询时，只有在被驱动表上建立索引才有效！
  2. left join 时，左侧的为驱动表，右侧为被驱动表！

所以应该在 book 表上建立索引。

right join

和 left join 基本一致，只是驱动表和被驱动表位置互换了，应该在 class 表上建立索引！

inner join

1
2
3
explain select * from a inner join b on a.card=b.card;
 
explain select * from b inner join a on b.card=a.card;

 

将 a 和 b 的位置调换，发现没有影响，得出如下结论：inner join 时，mysql 会自己帮你把小结果集的表选为驱动表。

小结

  1. 尽可能减少 join 语句中的 NestedLoop 的循环总次数：“永远用小结果集驱动大的结果集”。
  2. 优先优化 NestedLoop 的内层循环。
  3. 保证 join 语句中被驱动表上的 join 条件字段已经被索引。
  4. 当无法保证被驱动表的 join 条件字段被索引且内层资源充足的前提下，不要太吝啬 JoinBuffer 的设置。

SQL 优化

大批量插入数据

当使用 load 命令导入数据的时候，适当的设置可以提高导入的效率。

  1. 主键顺序插入：因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。
  2. 关闭唯一性校验：在导入数据前执行 SET UNIQUE_CHECKS=0 关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1 恢

复唯一性校验，可以提高导入的效率。 3. 手动提交事务：如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0 关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1 打开自动提交，也可以提高导入的效率。

优化 insert 语句

  1. 同时插入很多行数据：应该尽量使用多个值表的 insert 语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个 insert 语句快。

1
2
3
4
5
6
# 原始方式
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
# 优化
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

 

  1. 在事务中插入数据

1
2
3
4
5
start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

 

  1. 数据有序插入

1
2
3
4
5
6
7
8
9
10
11
12
# 原始方式
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
# 优化
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

 

优化 order by 语句

两种排序方式

  1. 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  2. 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

优化目标

了解了 MySQL 的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。

  1. where 条件和 order by 使用相同的索引；
  2. 并且 order by 的顺序和索引顺序相同；
  3. 并且 order by 的字段都是升序，或者都是降序。

否则肯定需要额外的操作，这样就会出现 FileSort。比如下方测试，其中 user 表包含一个复合索引，顺序为 name,age,email：

  1. where 中使用了索引，但是 order by 中的 remark 字段并没有索引，所以 filesort
  2. where 中使用了索引，并且 order by 中的 age 字段在索引中紧跟 name 后面，所以不是 filesort
  3. where 中使用了索引，并且 order by 中的 email 字段在索引中紧跟 age 后面，但是 age 是范围查询，导致后面的索引失效，所以是 filesort（更通俗的说法：查询到姓名为 Jack 年龄大于 10 的数据，此时年龄是有很多的，单独看一个年龄中的邮箱是有序的，但是那么多年龄合并在一起邮箱就不会是有序的，所以无法直接使用索引完成排序功能。复合索引就是先按照 name 排序，接着按照 age 排序，最后按照 email 排序）
  4. 印证 3，发现按照 age 排序就不是 filesort 了

  1. 再次印证 3
  2. where 中使用了索引，并且 order by 中的 age 和 email 字段在索引中分别紧跟 name 和 age 后面，但是因为一个升序一个降序，导致 filesort
  3. where 中使用了索引，但是 order by 中的字段顺序错乱，导致 filesort

  1. 新建 remark 单值索引，因为 where 中使用的索引和 order by 中的索引不一致，所以 filesort

优化 filesort

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让 Filesort 消失，那就需要加快 Filesort 的排序操作。对于Filesort，MySQL 有两种排序算法：

  1. 两次扫描算法：MySQL 4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果 sort buffer 不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机 I/O 操作。
  2. 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和 Query 语句取出的字段总大小，来判定使用哪种排序算法，如果 max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

优化 group by 语句

由于 GROUP BY 实际上也同样会进行排序操作，而且与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
group by 使用索引的原则几乎跟 order by 一致，唯一区别是 group by 即使没有过滤条件用到索引，也可以直接使用索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行 order by null 禁止排序。

优化嵌套查询

Mysql 4.1 版本之后，开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

连接(Join)查询之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
在范围判断时，尽量不要使用 not in 和 not exists，使用 left join on xxx is null 代替。

优化 or 条件

对于包含 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。
建议使用 union 代替 or。

优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000001 – 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

1
select * from tbl_item limit 2000000,10;

 

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

1
select * from tbl_item a join (select id from tbl_item limit 2000000,10) b on a.id=b.id;

 

优化思路二

该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询。

1
select * from tbl_item where id>=2000001 limit 10;

 

使用 SQL 提示

SQL 提示是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

use index

ignore index

force index

其他优化

  1. 避免使用 select *，用到几列就选择几列
  2. 避免使用 in、not in
  3. 尽量使用数字型字段
  4. 能用 between 就不要用 in
  5. 能用 distinct 就不用 group by
  6. 能用 union all 就不要用 union
  7. 字段数据类型优化：避免使用 null；尽可能使用更小的字段；优先使用定长型
  8. 避免多表关联

慢查询日志

生产环境出现问题，如何调优？

  1. 开启慢查询日志，并捕获慢查询 SQL
  2. 使用 explain + 慢 SQL 分析
  3. show profiles 查询 SQL 在 MySQL 服务器里面的执行细节和生命周期情况
  4. MySQL 数据库参数调优

什么是慢查询日志？

  1. MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL 会被记录到慢查询日志中。
  2. long_query_time 的默认值为 10，意思是运行 10 秒以上的语句会被记录到慢查询日志中。
  3. 通过慢查询日志来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 sql 执行超过 5 秒钟就算慢 SQL，希望能收集超过 5 秒的 sql，结合之前 explain 进行全面分析。

怎么用？

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数。因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
临时生效，当数据库重启后将会失效，建议使用这种方式，并且分析完毕后需要及时关闭慢查询日志功能，方法如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
# 查看慢查询日志是否开启，默认为 OFF
show variables like '%slow_query_log%';
# 开启慢查询日志，1 开启；0 关闭
set global slow_query_log=1;
# 设置慢查询日志保存路径
set global slow_query_log_file='/var/lib/mysql/test-slow.log';
# 查看慢查询设定阈值，单位：秒
show variables like '%long_query_time%';
# 设定慢查询阈值，单位：秒
set global long_query_time=5;
# long_query_time 设置完成后直接用上面的方式查看，是看不到的，有两种方式可以看到设置的效果
# 1. show global variables like '%long_query_time%';
# 2. 重新打开一个会话，就可以看到了

 

永久生效，需修改 my.cnf 中 [mysqld] 下配置，如下：

1
2
3
4
5
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/test-slow.log
long_query_time=5
log_output=FILE

 

等待系统运行一段时间，即可查看日志文件中的慢 SQL，然后调优！

1
2
# 查看慢查询 SQL 数量
show global status like '%Slow_queries%';

 

日志分析工具

人工查看慢查询日志显然很耗费时间，是个体力活，MySQL 提供了日志分析工具 mysqldumpslow。
查看 mysqldumpslow 的帮助信息，在 linux 服务器执行 mysqldumpslow --help 命令，提示如下：

重要的参数罗列如下：

  1. -s ORDER：按照何种方式排序，其中 ORDER 默认 at，可选如下：
    • al：平均锁定时间
    • ar：平均返回记录数
    • at：平均查询时间
    • c：访问次数
    • l：锁定时间
    • r：返回记录
    • t：查询时间
  2. -t NUM：返回前面 NUM 条数据
  3. -g PATTERN：匹配正则表达式，大小写不敏感

常见的日志分析语句：

1
2
3
4
5
6
7
8
# 得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/test-slow.log
# 得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/test-slow.log
# 得到按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/test-slow.log
# 另外建议在使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/test-slow.log | more

 

show profile

是什么？

mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于 SQL 的调优测量，官网说明。
默认情况下，参数处于关闭状态，并默认保存最近 15 次的运行结果。

怎么用？

  1. 查看当前 MySQL 版本是否支持：show variables like '%profiling%';

1
2
3
4
5
6
7
8
9
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   | # 支持
| profiling              | OFF   | # 功能默认关闭
| profiling_history_size | 15    | # 默认记录最近 15 条 SQL
+------------------------+-------+
2 rows in set (0.02 sec)

 

  1. 开启功能，默认关闭，使用前需要开启：set profiling=1;
  2. 运行几个 SQL
  3. 查看结果：show profiles;

1
2
3
4
5
6
7
8
9
mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00074700 | show variables like '%profiling%' |
|        2 | 0.00059550 | select * from user                |
|        3 | 0.02182150 | select now()                      |
+----------+------------+-----------------------------------+
3 rows in set (0.04 sec)

 

  1. 诊断 SQL：show profile cpu,block io for query Query_ID，Query_ID 即上一步查询出来的结果
    1. 所有可以显示的信息
      • ALL：所有的开销信息
      • BLOCK IO：块 IO 操作相关开销信息
      • CONTEXT SWITCHES：上下文切换相关开销信息
      • CPU：CPU 相关开销信息
      • IPC：发送和接收相关开销信息
      • MEMORY：内存相关开销信息
      • PAGE FAULTS：页面错误相关开销信息
      • SOURCE：Source_function、Source_file、Source_line 相关开销信息
      • SWAPS：交换次数相关开销信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000066 | 0.000036 | 0.000024   |            0 |             0 |
| checking permissions | 0.000018 | 0.000011 | 0.000006   |            0 |             0 |
| Opening tables       | 0.000026 | 0.000016 | 0.000011   |            0 |             0 |
| init                 | 0.000027 | 0.000000 | 0.000039   |            0 |             0 |
| System lock          | 0.000032 | 0.000000 | 0.000019   |            0 |             0 |
| optimizing           | 0.000016 | 0.000000 | 0.000016   |            0 |             0 |
| statistics           | 0.000028 | 0.000000 | 0.000028   |            0 |             0 |
| preparing            | 0.000022 | 0.000000 | 0.000021   |            0 |             0 |
| executing            | 0.000015 | 0.000000 | 0.000015   |            0 |             0 |
| Sending data         | 0.000175 | 0.000000 | 0.000176   |            0 |             0 |
| end                  | 0.000017 | 0.000000 | 0.000016   |            0 |             0 |
| query end            | 0.000017 | 0.000000 | 0.000017   |            0 |             0 |
| closing tables       | 0.000020 | 0.000000 | 0.000020   |            0 |             0 |
| freeing items        | 0.000049 | 0.000000 | 0.000049   |            0 |             0 |
| cleaning up          | 0.000070 | 0.000000 | 0.000070   |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set (0.06 sec)

 

  1. 需要注意的 Status 结论（坏的）：
    • converting HEAP to MyISAM：查询结果太大，内存不够用，往磁盘上搬了
    • Creating tmp table：创建临时表，势必会导致拷贝数据到临时表，用完再删除，效率低
    • Copying to tmp table on disk：把内存中的临时表复制到磁盘，十分危险！！！
    • locked：

大致的查询流程

mysql 客户端通过协议与 mysql 服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储 SELECT 语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理：首先 mysql 通过关键字将 SQL 语句进行解析，并生成一颗对应的“解析树”。mysql 解析器将使用 mysql 语法规则验证和解析查询；预处理器则根据一些 mysql 规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
然后，mysql 默认使用的 B+TREE 索引，并且一个大致方向是：无论怎么折腾 sql，至少在目前来说，mysql 最多只用到表中的一个索引。

SQL 的执行顺序

手写的顺序：

1
2
3
4
5
6
7
8
select distinct <select_list>
from <left_table>
<join_type> join <right_table> on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
order by <order_by_condition>
limit <limit_number>

 

真正执行的顺序，随着 Mysql 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：

1
2
3
4
5
6
7
8
9
10
from <left_table>
on <join_condition>
<join_type> join <right_table>
where <where_condition>
group by <group_by_list>
having <having_condition>
select
distinct <select_list>
order by <order_by_condition>
limit <limit_number>

 

全局查询日志

配置启动

在 mysql 的 my.cnf 中配置如下：

1
2
3
4
5
6
# 开启全局查询日志
general_log=1
# 记录日志文件的路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE

 

编码启用

1
2
set global general_log=1;
set global log_output='TABLE';

 

此后，所有 SQL 语句将会记录到 mysql 库里面的 general_log 表中，可以使用 select * from mysql.general_log; 命令查看

永远不要在生产环境中使用！

以上部分内容取自 尚硅谷 MySQL 高级 和 黑马程序员 MySQL 高级

作者：阿牛9513
链接：https://juejin.cn/post/6920438860606504974
来源：掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

Copyright © 2024 Longsheng All rights reserved