共计 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= offSQL 怎么调优？
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 行业资讯频道。
