Mysql优化问题有哪些

共计 5005 个字符，预计需要花费 13 分钟才能阅读完成。

这篇文章主要介绍了 Mysql 优化问题有哪些的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇 Mysql 优化问题有哪些文章都会有所收获，下面我们一起来看看吧。

开发过程如何排查 SQL？排查思路

对于大部分程序员来说，在开发过程中排查 SQL 基本是空白。但随着行业的内卷，对一开发过程越来越重视和专业，其中一项就是开发过程中尽可能解决掉 SQL 问题，避免生产才暴露 SQL 问题。那么在开发过程中如何方便的进行程序的 SQL 排查呢？

其思路还是使用 Mysql 的慢日志来实现：

首先在开发过程中也需要开启数据库 Mysql 的慢查询

SET GLOBAL slow_query_log= on

其次设置慢 SQL 的最小时间

注意：这里时间单位是 s 秒但是有 6 位小数因此可以表示到微妙的时间力度，一般单表 SQL 执行时间在 20ms 之内为宜，反之理解就是在开发过程中，如果你执行的 sql 语句超过了 20ms 则你需要去关注它。

SET GLOBAL long_query_time=0.02;

为方便操作可以把慢 SQL 记录到表中而不是文件中

SET GLOBAL log_output= TABLE

最后通过 mysql.slow_log 表就可以查询到记录的慢 SQL

使用工具

在勇哥给大家开发的软件中，也提供了图形化的界面来一键帮助大家快速实现上述功能。

生产环境 SQL 问题如何排查？排查思路

生成 SQL 问题的排查就相对复杂一点点，但是整体的思路还是通过慢 SQL 来排查，具体思路如下：

首先开启数据库 Mysql 的慢查询

SET GLOBAL slow_query_log= on

其次设置慢 SQL 的最小时间

SET GLOBAL long_query_time=0.02;

一般生成时把慢 SQL 放到文件

SET GLOBAL log_output= FILE

下载慢 SQL 日志文件到本地

最后关闭数据库 Mysql 的慢查询

着重注意：生产的慢 SQL 最好在使用时，才去开启，用完后关闭，避免日志记录影响到业务性能

SET GLOBAL slow_query_log= off

SQL 怎么调优？

SQL 调优融合多方面的知识，总体来说常见从表结构、表索引、两方面来优化。

表结构优化 1、合理的使用字段类及长度

举个例子来理解：就一个性别字段，用 tinyint(1)存储占用 1 字节，用 int(1)存储占用 4 个字节，如果有 100W 条记录，那么用 int 存储的表就比 tinyint 存储的表文件大小多 2.8M 左右，因此在读取 int 类型存储的表时文件大，读速度相比读 tinyint 的慢。这其实就是为什么说要合理使用字段类型长度的本质：就是减少存储的文件大小，以提供读性能。

当然有的朋友就可能说 2.8M 并不影响大局，因此可以忽略。对于此想法勇哥要补充一嘴：一个表假设有 10 个字段，你的系统一共有 30 个表，那么再看一下多出的文件大小是多少？（2.8Mx10x30=840M，840M 你用迅雷超级下载也要花好几秒，这个时间在计算机里面算是很慢了 …）

2、合理的使用冗余设计

2.1、冗余设计背景——临时表

Mysql 内部存在一种特殊且轻量级的临时表，它是被 Mysql 自动创建和删除的。主要在 SQL 的执行过程中使用临时表来存储某些操作的中间结果，该过程由 MySQL 自动完成，用户无法手工干预，且这种内部表对用户来说是不可见的。

内部临时表在 SQL 语句的优化过程中非常重要，MySQL 中的很多操作都要依赖于内部临时表来进行优化操作。但是使用内部临时表需要创建表以及中间数据的存取代价，所以在写 SQL 语句的时候应该尽量去避免使用临时表。

那么场景的那些场景 Mysql 内部会使用临时表呢？

多表关联查询 (JOIN) 中，order by 或 group by 使用的列不是第一个表的列

group by 的列不是索引列时

distinct 和 group by 联合使用

order by 语句中使用了 distinct 关键字

group by 的列时索引列，但数据量过大时

2.2、如何查看是否使用内部临时表？

通过 Explain 关键字或者工具的功能按钮，查看 SQL 的执行过程，在结果中的 Extra 列中如果出现 Using temporary 关键字，则说明你的 SQL 语句在执行时使用了临时表。

如下图，角色 Role 表和角色组 Role_Group 是多对 1 的关系，在关联查询的时候，排序使用 role_group 的 id 排序则会使用临时表（见下图 1），如果排序使用 role 的 id 则不会使用临时表（见图 2）。

2.3、如何解决不使用内部临时表？

这个问题解决有两个方案，一是调整 SQL 语句避免使用临时表，另外一个方案就是在表中冗余存储。比如 2.2 中的图一例子如果一定要按照 role_group 的 id 排序，则可以按照 role 表中的 group_id 排序，而这列正是冗余存储的 role_group 表中 id 列值。

3、合理的使用分库分表

分库分表不仅用于大数量情况下的优化，其中垂直分表还可以使用到 SQL 调优下。（这里我就不去解释垂直和水平分表了，感兴趣的私信我）

例如：一个文章表一般设计不会包括文章内容这个大字段。

文章内容这个大字段是单独放置到一张表中

为什么文章表要采用以上设计而不把字段合并到一表中呢？

我们先来计算一道数学题，假设一篇文章总共 1M 大小，其中文章内容，824KB，其余字段 200KB，这样的文章一共有 100W 条，则：

方案一，如果用一个表存储，则这个表大小是 100W*1M=100WM

方案二，如果用垂直分表存储，则基本表时 200KBx100W，内容表 824KBx100W

我们在前端有文章列表和文章详情两个页面，分别要直接从数据库中查询相关内容，则：

方案一，文章列表和文章详情的查询都会从 100WM 数据中查询

方案二，文章列表会从 200KBx100W 中查询，文章详情会从 824KBx100W 中查询（当前也可能还需要从 200KBx100W 中查询）

说到这里，相信大家心中应该有一个清晰的答案了吧！垂直拆表可以让不同业务场景的查询的数据量不同，常常这个数据量往往小于总表数据量，这就比从固定很大小的量中查询更灵活和高效率。

表索引优化 1、合理的添加索引列

大多数人对应索引的理解层次都在“索引可以加快查询的速度”，然而这句话勇哥要补充下半句“索引可以加快查询的速度，也可以减慢数据插入或修改的速度”。

如果一个表有 5 个索引，那么可以简单的把一个索引当成一个表，则这就会有 1 张表 + 6 张索引表 = 相当于有 6 张表，那么这 6 张表在什么时候会操作呢？我们来计算一下：

insert 操作，数据插入后，需要去对 5 张索引表插入索引数据

delete 操作，数据删除后，需要去把 5 张索引表中的索引删除

update 操作

如果修改了索引列的数据，则先修改数据，还需要修改索引表中的索引

如果没有修改索引列的数据，则只修改数据表

select 操作

如果命中查询索引，则先查询索引，再查数据表

如果没命中查询索引，则直接查数据表

通过以上的计算，你会神奇的发现，索引个数越多，对于 insert、delete、update 操作是有影响的，而且是负影响。所以对于索引竟可能评估其带来的影响小于查询的收益，才去添加，而不是盲目的添加。

2、合理的调配复合索引列个数和顺序

复合索引指的是包括有多个列的索引，它能有效的减少表的索引个数，平衡了多个字段需要多个索引直接的性能平衡，但是再使用复合索引的时候，需要注意索引列个数和顺序的问题。

先说列个数的问题，指的是一个复合索引中包括的列字段太多影响性能的问题，主要是对 update 操作的性能影响，如下红字：

如果修改了索引列的数据，则先修改数据，还需要修改索引表中的索引，如果索引列个数越多则修改该索引的概率越大

如果没有修改索引列的数据，则只修改数据表

再说复合索引中列顺序的问题，是指索引的最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，这个比较容易理解，就不多做阐述。

那些情况索引会失效？

索引无法存储 null 值，当使用 is null 或 is not nulli 时会全表扫描

like 查询以 % 开头

对于复合索引，查询条件中没有给出索引中第一列的值时

mysql 内部评估全表扫描比索引快时

or、!=、、in、not in 等查询也可能引起索引失效

表设计有那些规范？建表规约

表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型为 unsigned tinyint。说明：任何字段如果为非负数，则必须是 unsigned。

字段允许适当冗余，以提高查询性能，但必须考虑数据一致。e.g. 商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，

避免关联查询

。冗余字段遵循：

不是频繁修改的字段；

不是 varchar 超长字段，更不能是 text 字段。

索引规约

在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

页面搜索严禁左模糊或者全模糊，如果需要请通过搜索引擎来解决。说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

正例：where a=? and b=? order by c; 索引: a_b_c。

反例：索引中有范围查找，那么索引有序性无法利用，如 WHERE a 10 ORDER BY b; 索引 a_b 无法排序。

利用延迟关联或者子查询优化超多分页场景。说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 的行，返回 N 行。当 offset 特别大的时候，效率会非常的低下，要么控制返回的总页数，要么对超过阈值的页数进行 SQL 改写。

建组合索引的时候，区分度最高的在最左边。

SQL 性能优化的目标，至少要达到 range 级别，要求是 ref 级别，最好是 consts。

SQL 语句

不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语句，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。

count(distinct column) 计算该列除 NULL 外的不重复行数。注意，count(distinct column1,column2) 如果其中一列全为 NULL，那么即使另一列用不同的值，也返回为 0。

当某一列的值全为 NULL 时，count(column) 的返回结果为 0，但 sum(column) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。可以使用如下方式来避免 sum 的 NPE 问题。

SELECT IF(ISNULL(SUM(g), 0, SUM(g))) FROM table;

使用 ISNULL() 来判断是否为 NULL 值。说明：NULL 与任何值的直接比较都为 NULL。

不得使用外键与级联，一切外键概念必须在应用层解决。说明：以学生和成绩的关系为例，学生表的 student_id 是主键，成绩表的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

禁止使用存储过程。存储过程难以调试和扩展，更没有移植性。

in 操作能避免则避免。若实在避免不了，需要仔细评估 in 后面的集合元素数量，控制在 1000 个之内。

ORM 映射

POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行字段与属性的映射。

sql.xml 配置参数使用：#{}, #param#，不要使用 ${}，此种方式容易出现 SQL 注入。

@Transactional 事务不要滥用。事务会影响数据库的 QPS。另外，使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

关于“Mysql 优化问题有哪些”这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对“Mysql 优化问题有哪些”知识都有一定的了解，大家如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道。