精选归纳15个Mysql优化问题

本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于SQL优化的相关问题，包括了在开发过程中是怎么排查SQL语句、怎么排查生产环境SQL问题等等内容，下面一起来看一下，希望对大家有帮助。

精选归纳15个Mysql优化问题

推荐学习：mysql视频教程

开发过程如何排查SQL？

排查思路

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

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

首先在开发过程中也需要开启数据库Mysql的慢查询
```
SET GLOBAL slow_query_log=&#39;on&#39;;
```
其次设置慢SQL的最小时间
注意：这里时间单位是s秒但是有6位小数因此可以表示到微妙的时间力度，一般单表SQL执行时间在20ms之内为宜，反之理解就是在开发过程中，如果你执行的sql语句超过了20ms则你需要去关注它。
```
SET GLOBAL long_query_time=0.02;
```
为方便操作可以把慢SQL记录到表中而不是文件中
```
SET GLOBAL log_output=&#39;TABLE&#39;;
```
最后通过mysql.slow_log表就可以查询到记录的慢SQL

使用工具

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

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

排查思路

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

首先开启数据库Mysql的慢查询
```
SET GLOBAL slow_query_log=&#39;on&#39;;
```
其次设置慢SQL的最小时间
```
SET GLOBAL long_query_time=0.02;
```
一般生成时把慢SQL放到文件
```
SET GLOBAL log_output=&#39;FILE&#39;;
```
下载慢SQL日志文件到本地
最后关闭数据库Mysql的慢查询
着重注意：生产的慢SQL最好在使用时，才去开启，用完后关闭，避免日志记录影响到业务性能
```
SET GLOBAL slow_query_log=&#39;off&#39;;
```

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视频教程

以上就是精选归纳15个Mysql优化问题的详细内容，更多请关注其它相关文章！