PALE13

MySQL性能优化

count(*) 和 count(1) 有什么区别？哪个性能最好？

count(1) 执行过程是怎样的？

用下面这条语句作为例子：

select count(1) from t_order;

如果表里只有主键索引，没有二级索引时

那么，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1，包括NULL

可以看到，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

同理，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。

count(*) 执行过程是怎样的？

看到 * 这个字符的时候，是不是大家觉得是读取记录中的所有字段值？

对于 selete * 这条语句来说是这个意思，但是在 count(*) 中并不是这个意思。

**count(*) 其实等于 count(0)**，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。

在 MySQL 5.7 的官方手册中有这么一句话：

InnoDB handles SELECT COUNT(\*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

翻译：InnoDB以相同的方式处理SELECT COUNT（\*）和SELECT COUNT（1）操作，没有性能差异。

而且 MySQL 会对 count(*) 和 count(1) 有个优化，如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描。

只有当没有二级索引的时候，才会采用主键索引来进行统计

count(主键字段) 执行过程是怎样的？

在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。

server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。

InnoDB 是通过 B+ 树来保存记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。

用下面这条语句作为例子：

//id 为主键值
select count(id) from t_order;

如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。

但是，如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。

这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。

count(字段) 执行过程是怎样的？

如果该字段没有建立索引，走的是全表扫描，查询效率最低

count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。

用下面这条语句作为例子：

// name不是索引，普通字段
select count(name) from t_order;

对于这个查询来说，会采用全表扫描的方式来计数，所以它的执行效率是比较差的。

小结

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。

慢查询问题

怎么定位慢查询

我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题

导致MySQL慢查询的原因

1. 没有索引，或者索引失效。
2. 单表数据量太大
3. 查询使用了临时表
4. join 或者子查询过多
5. in元素过多。如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行。
6. limit深度分页问题

大表查询慢常见优化措施？

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

对SQL优化

• 查询时只返回必要的列，用具体的字段列表代替 select * 语句，因为要尽量用聚集索引防止回表查询
• SQL语句要避免造成索引失效的写法
• 尽量用union all代替union，union会多一次一次去重操作，效率低
• 避免在where子句中对字段进行表达式操作
• Join优化，能用inner join 就不用left join，right join，如必须使用一定要以小表为驱动（ 内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序）因为被驱动表会用到索引
• 使用varchar代替char(因为可变常字段存储空间小，可节省空间)
• 将多次插入换成批量Insert插入

建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能

利用缓存。利用Redis等缓存热点数据，提高查询效率

限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内

读写分离。经典的数据库拆分方案，主库负责写，从库负责读

分库分表：通过分库分表的方式进行优化，主要有垂直拆分和水平拆分

• 垂直分割表：将表根据不同的功能、访问模式分为多个表，避免查询全部字段和频繁更新次数相同的字段会造成索引磁盘更新、查询等性能问题。
• 水平分割表：将表根据数据量分为多个表。在处理超大表（如百万甚至千万级别）时，不仅能有效提高增删改查效率，还可以减少因锁表造成的程序阻塞。

调整数据库服务器参数
除了SQL语句优化和数据库结构优化之外，还可以通过调整数据库服务器参数进一步优化系统性能：

增加内存：MySQL常驻内存较大，如果服务器内存充足，则处理速度能够得到极大的提升。

增加并发连接数：MySQL默认并发连接数是100个，过高会增大服务器负担。

调整InnoDB缓存：InnoDB是MySQL5.5版本后的默认存储引擎，适用于大量在线事务和高并发访问，其缓存参数对于系统性能具有重要作用。可以通过修改以下两个参数来调整InnoDB缓存大小：

innodb_buffer_pool_size：为InnoDB分配的内存大小，默认为8M，可适当增加该值提高性能。

innodb_log_file_size：设置InnoDB redo日志文件大小，默认是5M。如果更新操作比较频繁，则应适当增加文本的大小，避免频繁写入磁盘造成性能瓶颈。

深度分页优化建议

深度分页原因

查询偏移量过大的场景我们称为深度分页，这会导致查询性能较低，例如：

select * from account where update_time> '2020-09-19' limit 100000,10;

我们先来看下这个SQL的执行流程：

1. 通过普通二级索引树idx_update_time，过滤update_time条件，找到满足条件的记录ID。
2. 通过ID，回到主键索引树，找到满足记录的行，然后取出展示的列（回表）
3. 扫描满足条件的100010行，然后扔掉前100000行，返回。

SQL变慢原因有两个：

1. limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
2. 例如limit 100000,10 扫描100010行数，然后再截取后10条，也意味着回表了100010。

子查询

我们先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会更快一些。

# 通过子查询来获取 id 的起始值，把 limit 1000000 的条件转移到子查询
select * FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10;

子查询 table a查询是用到了idx_update_time索引。首先在索引上拿到了聚集索引的主键ID,省去了回表操作，然后第二查询直接根据第一个查询的 ID往后再去查10个就可以了!

不过，子查询的结果会产生一张新表，会影响性能，应该尽量避免大量使用子查询。并且，这种方法只适用于 ID 是正序的。在复杂分页场景，往往需要通过过滤条件，筛选到符合条件的 ID，此时的 ID 是离散且不连续的。

当然，我们也可以利用子查询先去获取目标分页的 ID 集合，然后再根据 ID 集合获取内容，但这种写法非常繁琐，不如使用 INNER JOIN 延迟关联。

延迟关联

延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，减少回表的次数。不同点是，延迟关联使用了 INNER JOIN（内连接） 包含子查询。

SELECT t1.* FROM t_order t1
INNER JOIN (SELECT id FROM t_order limit 1000000, 10) t2
ON t1.id = t2.id;

除了使用 INNER JOIN 之外，还可以使用逗号连接子查询。

SELECT t1.* FROM t_order t1,
(SELECT id FROM t_order limit 1000000, 10) t2
WHERE t1.id = t2.id;

范围查询

当可以保证 ID 的连续性时，根据 ID 范围进行分页是比较好的解决方案：

# 查询指定 ID 范围的数据
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询：
SELECT * FROM t_order WHERE id > xxxx LIMIT 10

这种优化方式限制比较大，且一般项目的 ID 也没办法保证完全连续。

覆盖索引

索引中已经包含了所有需要获取的字段的查询方式称为覆盖索引。

覆盖索引的好处：

• 避免 InnoDB 表进行索引的二次查询，也就是回表操作: InnoDB 是以聚集索引的顺序来存储的，对于 InnoDB 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询（回表），减少了 IO 操作，提升了查询效率。
• 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

# 如果只需要查询 id, code, type 这三列，可建立 code 和 type 的覆盖索引
SELECT id, code, type FROM t_order
ORDER BY code
LIMIT 1000000, 10;

不过，当查询的结果集占表的总行数的很大一部分时，可能就不会走索引了，自动转换为全表扫描。当然了，也可以通过 FORCE INDEX 来强制查询优化器走索引，但这种提升效果一般不明显

慢SQL的优化分析思路？

1.查看慢查询日志记录，分析慢SQL

通过慢查询日志slow log，定位那些执行效率较低的SQL语句，重点关注分析

2.explain查看分析SQL的执行计划

当定位出查询效率低的SQL后，可以使用explain查看SQL的执行计划。

比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，

第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，

第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

3.profile 分析执行耗时

explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling。开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化

4.Optimizer Trace分析详情

profile只能查看到SQL的执行耗时，但是无法看到SQL真正执行的过程信息，即不知道MySQL优化器是如何选择执行计划。这时候，我们可以使用Optimizer Trace，它可以跟踪执行语句的解析优化执行的全过程。

大家可以查看分析其执行树，会包括三个阶段：

• join_preparation：准备阶段
• join_optimization：分析阶段
• join_execution：执行阶段

5.确定问题并采用相应的措施

最后确认问题，就采取对应的措施。

• 多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，我们可以优化索引。
• 我们还可以优化SQL语句，比如一些in元素过多问题（分批），深分页问题（基于上一次数据过滤等），进行时间分段查询
• SQL没办法很好优化，可以改用ES的方式，或者数仓。
• 如果单表数据量过大导致慢查询，则可以考虑分库分表
• 如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数，跟DBA讨论优化方案
• 如果存量数据量太大，考虑是否可以让部分数据归档

EXPLAIN指令

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句，我们一般分析 SELECT 查询较多。

我们这里简单来演示一下 EXPLAIN 的使用。

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

id

SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELELCT 语句的顺序。

id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。

select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

• SIMPLE：简单查询，不包含 UNION 或者子查询。
• PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
• SUBQUERY：子查询中的第一个 SELECT。
• UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
• DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
• UNION RESULT：UNION 查询的结果。

table

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

• <unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；
• <derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
• <subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

type（重要）

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

• system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
• const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
• eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
• ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
• index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
• range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
• index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
• ALL：全表扫描。

key（重要）

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

key_len

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

rows

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要）

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

• Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
• Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
• Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
• Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
• **Using join buffer (Block Nested Loop)**：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

MySQL性能监控工具

MySQL Enterprise Monitor

MySQL Enterprise Monitor 是 Oracle 提供的一款专业级监控工具，主要功能包括：

• 实时性能监控
• 可视化性能图表
• 自动化告警
• 性能优化建议

它是 MySQL Enterprise Edition 的一部分，提供全面的监控和管理功能。

Percona Monitoring and Management (PMM)

PMM 是 Percona 提供的开源监控和管理工具，适用于 MySQL、MariaDB、MongoDB 等数据库。主要功能包括：

• 实时性能监控
• 全面的性能图表和仪表盘
• 长期性能趋势分析
• 配置审计和安全监控

PMM 基于 Prometheus 和 Grafana 构建，具有高度可定制性和强大的数据收集能力。

MySQL Performance Schema 和 sys Schema

MySQL 自带的 Performance Schema 和 sys Schema 提供了一些基础的性能监控功能：

• Performance Schema：提供详细的低级别运行时性能数据，适用于高级性能分析。
• sys Schema：基于 Performance Schema 提供了一组易于使用的视图和存储过程，简化了性能数据的查询和分析。

通过这些内置工具，您可以直接在 MySQL 内部获取性能数据，进行初步的性能分析和调优。

死锁优化

如何排查死锁？

查询最近一次死锁日志

show engine innodb status;

这条命令的输出包含大量的信息，包括最近的死锁检测。如果最近发生过死锁，会在输出的某个部分显示死锁相关的信息。

使用information_schema数据库

查看当前正在进行的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查看进程列表

使用以下命令可以查看当前正在运行的进程列表：

SHOW PROCESSLIST;

在结果中，你可以查看每个进程的状态、执行的SQL语句以及锁的信息。特别是，如果某个进程的状态为Locked，那么它可能涉及死锁。

错误日志

配置 innodb_print_all_deadlocks 选项，将死锁信息记录到错误日志中，便于后续分析

innodb_print_all_deadlocks=1

性能监控工具

使用性能监控工具（如Percona Monitoring and Management, PMM, 或者MySQL Enterprise Monitor）可以帮助你实时监控数据库的性能和锁的情况，从而更容易地发现和解决死锁问题。

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

  当发生超时后，就出现下面这个提示：

• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

  当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。