网站设计团队发展,wordpress的ico怎么更换,制作网站的最大公司,招聘网站建设的意义当使用MySQL时#xff0c;我们不可避免地会遇到许多与慢查询相关的问题。
为了解决这些慢SQL的问题#xff0c;我们通常需要投入大量的精力去研究执行计划、考虑合适的索引策略、精心改写SQL语句#xff0c;甚至可能需要调整程序逻辑。然而#xff0c;针对特定SQL的优化往…当使用MySQL时我们不可避免地会遇到许多与慢查询相关的问题。
为了解决这些慢SQL的问题我们通常需要投入大量的精力去研究执行计划、考虑合适的索引策略、精心改写SQL语句甚至可能需要调整程序逻辑。然而针对特定SQL的优化往往既复杂又具有挑战性而且存在许多原因即使经过多次尝试和优化也可能无法取得显著的效果。
慢查询产生的典型场景
下面是2个典型的慢查询场景大家在日常写SQL和优化SQL的过程中应该会有感受
场景一大表
这是MySQL的经典问题。MySQL早期有单表数据量不能超过2000W的说法虽然有点夸张不过对于MyQL中千万级乃至过亿的表哪怕对这种表的查询建了索引索引的B树也会很深查询速度确实很受影响从而形成慢SQL。
一些有经验的开发会考虑给这张表加分区通过合理拆分来降低每个分区对应B树索引的深度从而提升执行性能。但MySQL毕竟是一个单机库各个分区共享的还是一份硬件资源而如果想突破单机的限制就要考虑做分库而那又是一个更复杂的问题涉及到整个数据库用法和结构的大调整。
场景二多表连接。这也是让很多研发头痛的问题类似下面这类SQL
select ...
from t1 --100行
left join t2 --100000行
on t1.c1t2.c1
left join t3 --100行
on t1.c1t3.c1
left join t4 --100行
on t1.c1t4.c1
order by t1.c1 limit 1000;
在这个例子中 4 张表做连接 T1、T2、T3、T4其中 T2 是大表其它都是小表如果严格按照连接次序来做T1 跟 T2 连接再跟 T3、T4 连接最大的表T2就过早进行了合并导致结果集特别大整个SQL开销相对较高。
如果要优化慢查询的话就要改写SQL把T2挪到最后来连接这样整个SQL的代价就会小很多了但很多时候这是跟我们业务开发的逻辑是违背的。很多场景下修改SQL都是非常麻烦的一件事。
其实自适应调整表连接顺序应该是数据库能做到的这步一般被称为“SQL改写”但因为MySQL当前的优化器更多是基于规则的模型所以通常情况是做不到这点的。
为什么MySQL的大SQL能力比较差
因为MySQL社区主要还是将MySQL定位为一个纯粹OLTPOnline Transaction Processing联机交易处理型的开源数据库。这个定位使得MySQL的迭代发版侧重于提升单核性能、加强事务处理能力等而对于大数据量、复杂查询类的偏OLAPOnline Analytical Processing联机事务处理场景MySQL发展缓慢。
举个例子在MySQL 8.0.14前MySQL是没有并行执行能力的。也就是说MySQL对每条SQL最多只能使用CPU的一个核来处理。而并行执行可以将一个 SQL 查询任务分成多个子任务并允许这些子任务在多个处理器上同时运行以提高整个查询任务的执行效率。对于上文的慢查询场景一而言并行执行就可以极快加速这种场景的查询效率。虽然MySQL自8.0.14版本开始支持并行执行但目前仅在select count(*)等有限场景下生效且必须手动配置参数。因此目前MySQL还不支持大部分场景的并行执行从根本上解决复杂查询的问题。 用分布式数据库解决慢查询问题
针对这些挑战OceanBase作为一款原生分布式的HTAPHybrid Transactional/Analytical Processing数据库对大规模查询场景的处理上进行了重点的优化。如果您也在寻求解决慢SQL查询的方案不妨尝试一下用分布式数据库OceanBase从根本上来解决这个问题。
OceanBase在大查询场景的主要技术点有
1、并行执行
OceanBase有非常成熟的并行执行能力可以对普通查询、DDL、DML操作都进行并行执行并且可以自动/手动灵活选取并行度。这可以使得开发者通过少量CPU的代价简单、直接的使原本运行较慢的SQL性能快上几倍对于大SQL问题有立竿见影的解决效果。
可以通过下面两个参数来开启并设置自动并行
#开启并行
set global parallel_degree_policy AUTO;
#设置基表最大扫描时间单位为ms默认为1000ms您可以视情况调大或调小这个值这里把这个参数改为100ms即基表扫描时间超过100ms则开启并行执行。
set global parallel_min_scan_time_threshold 100; 2、分库分表
如同前文MySQL的场景一中所提到的解决大数据量表查询性能问题的一个解法是分库分表但MySQL分库分表有很高的改造成本。而OceanBase是一款原生的分布式数据库OceanBase中的分区是一个独立的存储、高可用、事务的单位这意味着OceanBase同一张表的不同分区可以分布在不同的服务器上从而利用多机性能大大加快大表查询速度。同时OB的原生分布式能力也可以使应用程序像使用一个单机数据库一样使用分布式的OB没有业务改造成本。 3、多表连接次序优化
对于前文的场景二业务上是比较难判断怎么调整表连接的顺序更好的而OceanBase 实现了一套完备的连接枚举算法整体优化逻辑是基于代价的可以灵活调整内连接和外连接的次序可以调整外连接、反连接、半连接甚至还可以改变一个外连接的连接类型把它变成内连接或反连接等可以做到场景自动优化。像场景二的例子在OB中自然就会优化成t1跟t3连接再跟t4连接最后再跟t2连接从而使得SQL执行性能快了近百倍。很多在MySQL中需要反复改写、调优的SQL在OB中自然跑出来就是最优的执行计划。 4、子查询场景优化
子查询也是很常见的性能问题并且不同于多表连接子查询通常比较难改写往往会造成执行很慢。OceanBase 查询改写模块实现了丰富的子查询优化策略只要不是嵌套非常深的子查询都可以把它变成连接变成连接后就成了连接枚举的问题可以采取不同的连接算法去优化它。 5、大表聚合场景优化
大表聚合也是一个经典的场景比如我们要汇总一年的营业额数据就有可能用到下面的SQL
SELECT year(sold_date) AS yearDate, code, name, SUM(value0) as total_value FROM t where sold_date2023-01-01 and sold_date2023-12-31 GROUP BY year(sold_date), code, name;
针对这种场景OB有一种优化能力称作预聚合就是把大表的数据拆成多份每个线程分别做分组聚合然后线程交换数据继续聚合全部聚合完后再做一次汇总。比如把示例中t表分为100组每组按year(sold_date), code, name分别聚合再最后汇总。
预聚合配合并行执行往往可以取得几倍的性能提升。但这种优化并不一定是好效果的假如一共10亿条数据不同的year(sold_date), code, name租户就有5亿种那么区分度就太低了预聚合甚至是一个反优化。
OceanBase 面对大表聚合场景是让执行引擎变得更聪明不在优化器层面去做决策而是交给执行层执行根据计算过程中的实际情况去决定做不做预聚合优化。
当前OceanBase 对各种场景基本都支持了这个优化包括分组、去重、窗口函数都可以灵活判断是否做预聚合。 6、TP 与 AP隔离避免互相影响
对于数据库来说相比于单条复杂大查询让大量的 DML 和短查询尽快返回更有意义。为了避免一条大查询阻塞大量简单请求而导致系统吞吐量暴跌避开分库分表的查询性能局限当大查询和短请求同时争抢 CPU 时OceanBase 会限制大查询的 CPU 使用。当一个线程执行的 SQL 查询耗时太长这条查询就会被判定为大查询一旦判定为大查询就会进入大查询队列然后执行大查询的线程会等在一个 Pthread Condition 上为其它的租户工作线程让出 CPU 资源。
通过这些技术能力可以使得原本在MySQL中执行时间超过1s的慢SQL在OceanBase中获得显著的性能提升。
如果大家希望体验分布式数据库OceanBase在复查查询的性能可以在OceanBase官网开通365天的免费试用。1核4G的OceanBase租户实例就可以体验查询性能如果还需要体验OceanBase的其他特性也可以申请企业版的集群实例。产品中有相关的新手教程引导也请注意通过上文中的方法打开并行执行。
