Mysql优化问题有哪些

2024-07-24 12:34:19

字体：大中小

来源：转载

供稿：网友

　　开发过程如何排查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的日志文件是什么

下一篇：mysql索引有哪些优点