企业官网建设流程全解析

网站流量成本,深圳网站建设公司那家好,网销怎么销售的,网站联合推广方案工作中#xff0c;我们有时会遇到 MySQL join 表太多的情况#xff0c;可能来自两个背景#xff0c;一个是历史老代码#xff0c;一个是去 o(Oracle) 改造#xff0c;从 Oracle 迁移到 MySQL 的 SQL。 多张表的 join 很可能会带来问题#xff0c;引发生产事故#xff0…工作中我们有时会遇到 MySQL join 表太多的情况可能来自两个背景一个是历史老代码一个是去 o(Oracle) 改造从 Oracle 迁移到 MySQL 的 SQL。多张表的 join 很可能会带来问题引发生产事故增加后期维护成本。一个新系统上线时可能测不出问题但随着数据量的增加问题就会逐渐暴露出来了。阿里开发手册中明确规定禁止三个表禁止 join。那对于 MySQL 中 join 表多的 SQL一般该怎么优化呢多个表使用 join 语句的根本原因是业务代码需要整合多张表里面的字段才能完成处理。那具体怎样优化呢先来模拟一个多表 join 的 SQL这里我们创建 5 张表CREATE TABLEtest1 ( idTINYINT(3) NOTNULLCOMMENT主键ID, aVARCHAR(20) DEFAULTNULL, bVARCHAR(20) DEFAULTNULL, cVARCHAR(200) DEFAULTNULL, dTINYINT(3) DEFAULTNULL, create_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT创建时间, update_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT更新时间, PRIMARY KEY (id), KEYa (a), KEYb (b), KEYc (c), KEYd (d) ) ENGINEINNODBDEFAULTCHARSETutf8 CREATETABLE test2 LIKE test1; CREATETABLE test3 LIKE test1; CREATETABLE test3 LIKE test1; CREATETABLE test4 LIKE test1;假如我们有这样一个包括多个表 join 的 SQL:SELECT t1.id ,t1.a,t2.b,t3.c,t4.d FROM test1 t1 JOIN test2 t2 ON t1.at2.a JOIN test3 t3 ON t1.bt3.b AND t3.id 1000 JOIN test4 t4 ON t1.ct4.c;1.拆分 SQL把多张表 join 的 SQL 拆解成多个 join 语句在应用代码中进行组合。比如拆解成 2 个 SQL:SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.at2.a JOIN test3 t3 ON t1.bt3.b; SELECT t1.id ,t1.a,t4.d FROM test1 t1 JOIN test4 t4 ON t1.ct4.c;在业务代码中对两个 SQL 结果进行组合。2.使用临时表在上面的优化中我们使用了 SQL 拆分的方式。如果 test3 表的数据量比较大比如有 100万。但 test3 表使用到的结果集只有 1000 条可以使用临时表CREATE TEMPORARY TABLE temp_t3(id TINYINT PRIMARY KEY, b VARCHAR(20),INDEX(b))ENGINEINNODB; SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.at2.a JOIN temp_t3 t3 ON t1.bt3.b; SELECT t1.id ,t1.a,t4.d FROM test1 t1 JOIN test4 t4 ON t1.ct4.c;3.使用冗余字段比如我们把 test4 表的 d 字段冗余到 test1 表中假定字段名叫 t4c这样就可以减少一个 join当然这样违反范式了。最后只用下面的 SQL 就可以了SELECT t1.id ,t1.a,t2.b,t3.c,t1.t4c FROM test1 t1 JOIN test2 t2 ON t1.at2.a JOIN test3 t3 ON t1.bt3.b AND t3.id 1000;这样需要先在 test1 表中增加新字段 t4c然后把 t4c 字段的值从 test4 表中更新过去。改造需要注意两点一个是评估更新字段的开销第二个是要注意数据一致性每次更新 test4 表中的 d 字段时也需要同步更新 test1 表中的 t4c 字段。4.用好索引join 语句对索引的使用非常重要我们要注意下面几点驱动表MySQL 会选择 where 语句筛选出记录少的表作为驱动表和被驱动表的 join 列都应该有索引如果 join 语句涉及表的多个列可以考虑为这些列建一个复合索引比如下面 SQLSELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a t2.a AND t1.b t2.b AND t1.c t2.c;避免索引失效比如 两端数据类型不同、使用函数、表达式等情况要避免优化 join 顺序如果我们能确定哪个表做驱动表更合适这时我们可以考虑使用 straight_joinSELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 straight_join test2 t2 ON t1.a t2.a AND t1.b t2.b AND t1.c t2.c;order by、limit 使用到的列尽量加上索引通过执行计划查看索引使用情况。5.修改查询语句如果某一个 join 表只是判断数据行是否存在不需要使用表里面的字段时我们可以考虑使用 exists 或 in 语句进行优化。对于下面这个 SQL:SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.at2.a JOIN test3 t3 ON t1.bt3.b AND t3.id 1000 JOIN test4 t4 ON t1.ct4.c;可以优化成如下 SQLSELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.at2.a JOIN test3 t3 ON t1.bt3.b AND t3.id 1000 WHERE EXISTS(SELECT id FROM test4 t4 WHERE t4.dt1.d);6.减少结果集减少结果集也是一种优化手段通过增加 where 条件来让驱动表结果集降到最小限制返回给应用的数据量比如对返回结果做分页对于返回结果的列如果不用则去掉这样对 join_buffer 的使用也会有好处。7.修改数据库配置当然也可以修改数据库一些配置比如 join_buffer_size、tmp_table_size增加 join_buffer 和临时表大小但是数据库参数的修改影响范围太大了尤其是对于老系统坑很多不好做影响分析所以不建议使用。篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面试笔记及答案【点击此处即可/免费获取】​​​8.引入大数据工具如果 join 表的数据量都很大我们也可以考虑引入大数据工具比如 ETL、数据湖将表数据抽取到数据仓库比如 ClickHouse中进行加工后把数据结果提供出来。当然这样存在的问题是数据时效性低。9.汇总表如果查询时效性要求不高可以通过定时任务把查询结果放到一张汇总表查询的时候直接查询这张汇总表。也可以把结果放到缓存从缓存中查询。CREATE TABLEtest_join_result ( idTINYINT(3) NOTNULLCOMMENT主键ID, aVARCHAR(20) DEFAULTNULL, bVARCHAR(20) DEFAULTNULL, cVARCHAR(200) DEFAULTNULL, dTINYINT(3) DEFAULTNULL, eTINYINT(1) DEFAULTNULL, create_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT创建时间, update_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT更新时间, PRIMARY KEY (id) ) ENGINEINNODBDEFAULTCHARSETutf8 --定时任务执行下面 SQL insertinto test_join_result(id,a,b,c,d) SELECT t1.id ,t1.a,t2.b,t3.c,t4.d FROM test1 t1 JOIN test2 t2 ON t1.at2.a JOIN test3 t3 ON t1.bt3.b AND t3.id 1000JOIN test4 t4 ON t1.ct4.c;最后对于新系统、新代码使用多表 join 的情况比较少因为开发规范一般不允许这样做。但是老系统或者做过数据库迁移的系统可能会遇到这种情况。要多个因素综合考虑再下手优化。