必会的这15个Mysql优化问题，面试官、DBA都要高看你一眼，速度收藏

前言

关于SQL优化相关的问题，相信很多同学在面试过程中都有被问到过，要么不知道，要么回答不清楚。见于此情况，勇哥今天有空，就和大家聊聊这个相关的话题。

• 你在开发过程中是怎么排查SQL语句的？- 开发排查思路是什么？- 相关慢SQL设置参数是什么？- 有没有便捷工具？
• 你是怎么排查生产环境SQL问题的？- 生成环境排除SQL应当着重注意什么？
• 你知道怎么调优SQL吗？- 怎么设计表或优化表？- 为什么要合理的使用字段的长度？- 为什么要用冗余设计？- 临时表是什么？- 为什么垂直分表可以提升性能？- 怎么优化索引？
• 你知道索引失效有那些情况吗？
• 你们的表设计有那些规范？

PS：以下正文中使用的数据库软件，是勇哥自己开发的，免费好用、可用SQL生成代码，请大家多支持：bg-boom-ui

开发过程如何排查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等查询也可能引起索引失效

表设计有那些规范？

表设计规范，现在行业中著名就是《阿里巴巴Java开发手册》，以下内容就是摘自其中，供参考。

阿里巴巴Java开发手册：https://github.com/alibaba/p3c/blob/master/Java%E5%BC%80%E5%8F%91%E6%89%8B%E5%86%8C%EF%BC%88%E5%B5%A9%E5%B1%B1%E7%89%88%EF%BC%89.pdf

建表规约

1. 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型为 unsigned tinyint。 说明：任何字段如果为非负数，则必须是 unsigned。
2. 字段允许适当冗余，以提高查询性能，但必须考虑数据一致。e.g. 商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。冗余字段遵循：- 不是频繁修改的字段；- 不是 varchar 超长字段，更不能是 text 字段。

索引规约

1. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
2. 页面搜索严禁左模糊或者全模糊，如果需要请通过搜索引擎来解决。 说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
3. 如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。- 正例：where a=? and b=? order by c; 索引: a_b_c。- 反例：索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
4. 利用延迟关联或者子查询优化超多分页场景。 说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 的行，返回 N 行。当 offset 特别大的时候，效率会非常的低下，要么控制返回的总页数，要么对超过阈值的页数进行 SQL 改写。
5. 建组合索引的时候，区分度最高的在最左边。
6. SQL 性能优化的目标，至少要达到 range 级别，要求是 ref 级别，最好是 consts。

SQL 语句

1. 不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语句，跟数据库无关，跟 NULL 和非 NULL 无关。 说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。
2. count(distinct column) 计算该列除 NULL 外的不重复行数。注意，count(distinct column1,column2) 如果其中一列全为 NULL，那么即使另一列用不同的值，也返回为 0。
3. 当某一列的值全为 NULL 时，count(column) 的返回结果为 0，但 sum(column) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。 可以使用如下方式来避免 sum 的 NPE 问题。

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

1. 使用 ISNULL() 来判断是否为 NULL 值。 说明：NULL 与任何值的直接比较都为 NULL。
2. 不得使用外键与级联，一切外键概念必须在应用层解决。 说明：以学生和成绩的关系为例，学生表的 student_id 是主键，成绩表的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
3. 禁止使用存储过程。存储过程难以调试和扩展，更没有移植性。
4. in 操作能避免则避免。若实在避免不了，需要仔细评估 in 后面的集合元素数量，控制在 1000 个之内。

ORM 映射

1. POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行字段与属性的映射。
2. sql.xml 配置参数使用：#{}, #param#，不要使用 ${}，此种方式容易出现 SQL 注入。
3. @Transactional 事务不要滥用。事务会影响数据库的 QPS。另外，使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。