在一次又一次的失败中, 我总结了这份万字的《MySQL 性能调优笔记》

MySQL 性能调优学习导图》概要

 

说明：本文篇幅有限，故只展示部分内容，《MySQL 性能调优学习导图》资料已整理成文档，需要获取的小伙伴可以直接转发+关注后私信（学习）即可获取哦

一、性能监控

1. 使用 show profile 查询剖析工具，可以指定具体的 type

此工具默认是禁用的，可以通过服务器变量在绘画级别动态的修改

set profiling=1;

当设置完成之后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。

select * from emp;

在 mysql 的命令行模式下只能显示两位小数的时间，可以使用如下命令查看具体的执行时间

show profiles;

执行如下命令可以查看详细的每个步骤的时间：

show profile for query 1;

2. 使用 performance schema 来更加容易的监控 mysql

MYSQL performance schema 详解

• performance_schema 的介绍

• performance schema 入门

• performance_schema 表的分类

• performance_schema 的简单配置与使用

• 常用配置项的参数说明

• 重要配置表的相关说明

• performance_schema 实践操作

3. 使用 show processlist 查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征

• id 表示 session id

• user 表示操作的用户

• host 表示操作的主机

• db 表示操作的数据库

• command 表示当前状态

• info 表示详细的 sq1 语向

• time 表示相应命令执行时间

• state 表示命令执行状态

二、schema 与数据类型优化

1. 数据类型的优化

更小的通常更好：应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型

简单就好：简单数据类型的操作通常需要更少的 CPU 周期，例如，①、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂；②、使用 mysql 自建类型而不是字符串来存储日期和时间；③、用整型存储 IP 地址

尽量避免 null：如果查询中包含可为 NULL 的列，对 mysql 来说很难优化，因为可为 null 的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下 null 的列改为 not null 带来的性能提升比较小，所有没有必要将所有的表的 schema 进行修改，但是应该尽量避免设计成可为 null 的列

实际细则：

2. 合理使用范式和反范式

①、范式

优点：

• 范式化的更新通常比反范式要快

• 当数据较好的范式化后，很少或者没有重复的数据

• 范式化的数据比较小，可以放在内存中，操作比较快

缺点：

• 通常需要进行关联

②、反范式

优点

• 所有的数据都在同一张表中，可以避免关联

• 可以设计有效的索引；

缺点

• 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

③、注意

3. 主键的选择

代理主键：与业务无关的，无意义的数字序列

自然主键：事物属性中的自然唯一标识

推荐使用代理主键

• 它们不与业务耦合，因此更容易维护

• 一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本

4. 存储引擎的选择

5. 适当的数据冗余

• 被频繁引用且只能通过 Join 2 张(或者更多)大表的方式才能得到的独立小字段。

• 这样的场景由于每次 Join 仅仅只是为了取得某个小字段的值，Join 到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

6. 适当拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR 类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

三、执行计划

四、通过索引进行优化

1. 索引基本知识

• 索引的优点

• 索引的用处

• 索引的分类

• 面试技术名词

• 索引采用的数据结构

• 索引匹配方式

2. 哈希索引

• 基于哈希表的实现，只有精确匹配索引所有列的查询才有效

• 在 mysql 中，只有 memory 的存储引擎显式支持哈希索引

• 哈希索引自身只需存储对应的 hash 值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快

• 自由主题

3. 组合索引

当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要

4. 聚簇索引与非聚簇索引

• 聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起

• 非聚簇索引：数据文件跟索引文件分开存放

5. 覆盖索引

• 基本介绍

• 优势

6. 优化小细节

• 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

• 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询

• 使用前缀索引

• 使用索引扫描来排序

• union all,in,or 都能够使用索引，但是推荐使用 in

• 范围列可以用到索引

• 强制类型转换会全表扫描

• 更新十分频繁，数据区分度不高的字段上不宜建立索引

• 创建索引的列，不允许为 null，可能会得到不符合预期的结果

• 当需要进行表连接的时候，最好不要超过三张表，因为需要 join 的字段，数据类型必须一致

• 能使用 limit 的时候尽量使用 limit

• 单表索引建议控制在 5 个以内

• 单索引字段数不允许超过 5 个（组合索引）

• 创建索引的时候应该避免以下错误概念

7. 索引监控

五、查询优化

1. 查询慢的原因

• 网络

• CPU

• IO

• 上下文切换

• 系统调用

• 生成统计信息

• 锁等待时间

2. 优化数据访问

查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化

是否向数据库请求了不需要的数据

• 查询不需要的记录

• 多表关联时返回全部列

• 总是取出全部列

• 重复查询相同的数据

3. 执行过程的优化

查询缓存：在解析一个查询语句之前，如果查询缓存是打开的，那么 mysql 会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么 mysql 会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端

查询优化处理：mysql 查询完缓存之后会经过以下几个步骤：解析 SQL、预处理、优化 SQL 执行计划，这几个步骤出现任何的错误，都可能会终止查询

4. 优化特定类型的查询

• 优化 count()查询

• 优化关联查询

• 优化子查询

• 优化 limit 分页

• 优化 union 查询

• 推荐使用用户自定义变量

六、分区表

1. 分区表的应用场景

• 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据

• 分区表的数据更容易维护

• 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

• 可以使用分区表来避免某些特殊的瓶颈

• 可以备份和恢复独立的分区

2. 分区表的限制

• 一个表最多只能有 1024 个分区，在 5.7 版本的时候可以支持 8196 个分区

• 在早期的 mysql 中，分区表达式必须是整数或者是返回整数的表达式，在 mysql5.5 中，某些场景可以直接使用列来进行分区

• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

• 分区表无法使用外键约束

3. 分区表的原理

4. 分区表的类型

• 范围分区

• 列表分区

• 列分区

• hash 分区

• key 分区

• 子分区

5. 如何使用分区表

如果需要从非常大的表中查询出某一段时间的记录，而这张表中包含很多年的历史数据，数据是按照时间排序的，此时应该如何查询数据呢？

因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引，即使使用索引，会发现会产生大量的碎片，还会产生大量的随机 IO，但是当数据量超大的时候，索引也就无法起作用了，此时可以考虑使用分区来进行解决

6. 在使用分区表的时候需要注意的问题

• nul1 值会使分区过滤无效

• 分区列和索引列不匹配，会导致查询无法进行分区过滤

• 选择分区的成本可能很高

• 打开并锁住所有底层表的成本可能很高

• 维护分区的成本可能很高

七、服务器参数设置

1. general

2. character

3. connection

4. log

5. cache

6. INNODB

学习感言

成功不是将来才有的，而是从决定去做的那一刻起，持续累积而成。