❝ 开头还是介绍一下群,如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题,有需求都可以加群群内有各大数据库行业大咖,可以解决你的问题。加群请联系 liuaustin3 ,(共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9)(1 2 3 4 5 6 7群均已爆满,开8群近400 9群 200+,开10群PolarDB专业学习群100+)
上一篇帖子关于SEEKDB在线下终端市场的布局,做了一个预期,那么我们必须针对线下单机市场中的对于SEEKDB 在一些复杂SQL的运算进行测试,看看SEEKDB可以充当替换MYSQL线下单机市场的国产首选吗? (一些同学看我上一篇帖子,说要试试SEEKDB )
测试会建立四张表,然后针对这些表进行复杂的查询并查看相关的执行计划,进行分析。
[root@seekdb ~] # obclient -h 127.0.0.1 -P 2881 -u root@sys Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221488705 Server version: OceanBase 4.3.5.3 SeekDB (r1.0.0.0) (Built 100000262025111218-5343637512e28c346f938516af53b7879d4d5974 Nov 12 2025) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help . Type '\c' to clear the current input statement. obclient(root@sys)[(none)]> use test Database changed obclient(root@sys)[ test ]> use ob_test ERROR 1049 (42000): Unknown database obclient(root@sys)[ test ]> create database ob_test; Query OK, 1 row affected (0.062 sec) obclient(root@sys)[ test ]> use ob_test; Database changed obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE TABLE customers ( -> customer_id BIGINT PRIMARY KEY, -> region VARCHAR(32), -> level INT, -> created_at DATE -> ); Query OK, 0 rows affected (0.144 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE INDEX idx_customers_region_level -> ON customers(region, level); Query OK, 0 rows affected (0.643 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE TABLE products ( -> product_id BIGINT PRIMARY KEY, -> category VARCHAR(32), -> price DECIMAL(10,2), -> status CHAR(1) -> ); Query OK, 0 rows affected (0.059 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE INDEX idx_products_category_status -> ON products(category, status); Query OK, 0 rows affected (0.494 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE TABLE orders ( -> order_id BIGINT PRIMARY KEY, -> customer_id BIGINT, -> order_date DATE, -> order_status CHAR(1), -> total_amount DECIMAL(12,2) -> ); Query OK, 0 rows affected (0.072 sec) obclient(root@sys)[ob_test]>
obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE INDEX idx_orders_customer_date -> ON orders(customer_id, order_date); Query OK, 0 rows affected (0.614 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE INDEX idx_orders_status -> ON orders(order_status); Query OK, 0 rows affected (0.399 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE TABLE order_items ( -> item_id BIGINT PRIMARY KEY, -> order_id BIGINT, -> product_id BIGINT, -> quantity INT, -> pay_amount DECIMAL(12,2) -> ); Query OK, 0 rows affected (0.074 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE INDEX idx_items_order -> ON order_items(order_id); Query OK, 0 rows affected (0.390 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE INDEX idx_items_product -> ON order_items(product_id); Query OK, 0 rows affected (0.500 sec) obclient(root@sys)[ob_test]> DELIMITER $$ obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE PROCEDURE sp_insert_customers(IN cnt INT) -> BEGIN -> DECLARE i INT DEFAULT 1; -> DECLARE v_region VARCHAR(32); -> DECLARE v_level INT; -> -> WHILE i <= cnt DO -> SET v_region = CASE MOD(i, 4) -> WHEN 0 THEN 'EAST' -> WHEN 1 THEN 'WEST' -> WHEN 2 THEN 'SOUTH' -> ELSE 'NORTH' -> END; -> -> SET v_level = MOD(i, 5) + 1; -> -> INSERT INTO customers(customer_id, region, level, created_at) -> VALUES ( -> i, -> v_region, -> v_level, -> DATE_SUB(CURDATE(), INTERVAL MOD(i, 365) DAY) -> ); -> -> SET i = i + 1; -> END WHILE; -> END$$ Query OK, 0 rows affected (0.090 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> DELIMITER ; obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CALL sp_insert_customers(10000); Query OK, 1 row affected (14.091 sec) obclient(root@sys)[ob_test]> DELIMITER $$ obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE PROCEDURE sp_insert_products(IN cnt INT) -> BEGIN -> DECLARE i INT DEFAULT 1; -> DECLARE v_category VARCHAR(32); -> DECLARE v_status CHAR(1); -> -> WHILE i <= cnt DO -> SET v_category = CASE MOD(i, 5) -> WHEN 0 THEN 'ELECTRONICS' -> WHEN 1 THEN 'BOOK' -> WHEN 2 THEN 'CLOTHING' -> WHEN 3 THEN 'FOOD' -> ELSE 'OTHER' -> END; -> -> SET v_status = IF(MOD(i, 10) < 8, 'A' , 'I' ); -- 80% 在售 -> -> INSERT INTO products(product_id, category, price, status) -> VALUES ( -> i, -> v_category, -> ROUND(50 + MOD(i, 5000) * 0.1, 2), -> v_status -> ); -> -> SET i = i + 1; -> END WHILE; -> END$$ Query OK, 0 rows affected (0.036 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> DELIMITER ; obclient(root@sys)[ob_test]> CALL sp_insert_products(5000); Query OK, 1 row affected (8.025 sec) obclient(root@sys)[ob_test]> DELIMITER $$ obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE PROCEDURE sp_insert_orders(IN cnt INT) -> BEGIN -> DECLARE i INT DEFAULT 1; -> DECLARE v_customer BIGINT; -> DECLARE v_status CHAR(1); -> -> WHILE i <= cnt DO
-> SET v_customer = MOD(i, 10000) + 1; -> SET v_status = IF(MOD(i, 10) < 7, 'S' , 'C' ); -- 70% 成功 -> -> INSERT INTO orders(order_id, customer_id, order_date, order_status, total_amount) -> VALUES ( -> i, -> v_customer, -> DATE_SUB( '2024-12-31' , INTERVAL MOD(i, 365) DAY), -> v_status, -> 0 -> ); -> -> SET i = i + 1; -> END WHILE; -> END$$ Query OK, 0 rows affected (0.045 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> DELIMITER ; obclient(root@sys)[ob_test]> CALL sp_insert_orders(50000); Query OK, 1 row affected (1 min 2.733 sec) obclient(root@sys)[ob_test]> DELIMITER $$ obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> CREATE PROCEDURE sp_insert_order_items(IN cnt INT) -> BEGIN -> DECLARE i INT DEFAULT 1; -> DECLARE v_order BIGINT; -> DECLARE v_product BIGINT; -> DECLARE v_qty INT; -> DECLARE v_pay DECIMAL(12,2); -> -> WHILE i <= cnt DO -> SET v_order = MOD(i, 50000) + 1; -> SET v_product = MOD(i, 5000) + 1; -> SET v_qty = MOD(i, 5) + 1; -> SET v_pay = v_qty * (50 + MOD(i, 200) * 1.5); -> -> INSERT INTO order_items(item_id, order_id, product_id, quantity, pay_amount) -> VALUES ( -> i, -> v_order, -> v_product, -> v_qty, -> v_pay -> ); -> -> -- 回写订单金额(制造真实聚合场景) -> UPDATE orders -> SET total_amount = total_amount + v_pay -> WHERE order_id = v_order; -> -> SET i = i + 1; -> END WHILE; -> END$$ Query OK, 0 rows affected (0.050 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> DELIMITER ; obclient(root@sys)[ob_test]> CALL sp_insert_order_items(100000); Query OK, 1 row affected (4 min 41.857 sec) obclient(root@sys)[ob_test]> ANALYZE TABLE customers; Query OK, 0 rows affected (0.195 sec) obclient(root@sys)[ob_test]> ANALYZE TABLE products; Query OK, 0 rows affected (0.078 sec) obclient(root@sys)[ob_test]> ANALYZE TABLE orders; Query OK, 0 rows affected (0.237 sec) obclient(root@sys)[ob_test]> ANALYZE TABLE order_items; Query OK, 0 rows affected (0.147 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> SELECT -> (SELECT COUNT(*) FROM customers) AS customers, -> (SELECT COUNT(*) FROM products) AS products, -> (SELECT COUNT(*) FROM orders) AS orders, -> (SELECT COUNT(*) FROM order_items) AS items; +-----------+----------+--------+--------+ | customers | products | orders | items | +-----------+----------+--------+--------+ | 10000 | 5000 | 50000 | 100000 | +-----------+----------+--------+--------+ 1 row in set (0.046 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> SELECT -> (SELECT COUNT(*) FROM customers) AS customers, -> (SELECT COUNT(*) FROM products) AS products, -> (SELECT COUNT(*) FROM orders) AS orders, -> (SELECT COUNT(*) FROM order_items) AS items; +-----------+----------+--------+--------+ | customers | products | orders | items | +-----------+----------+--------+--------+ | 10000 | 5000 | 50000 | 100000 | +-----------+----------+--------+--------+ 1 row in set (0.046 sec) obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> obclient(root@sys)[ob_test]> explain SELECT -> o.order_id, -> COUNT(DISTINCT oi.product_id) AS product_cnt,
-> SUM(oi.pay_amount) AS total_pay -> FROM orders o -> JOIN order_items oi -> ON o.order_id = oi.order_id -> WHERE o.order_status = 'S' -> GROUP BY o.order_id -> HAVING COUNT(DISTINCT oi.product_id) >= 3 -> AND SUM(oi.pay_amount) > 10000; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------------ | | |0 |HASH GROUP BY | |392 |65992 | | | |1 |└─SUBPLAN SCAN |VIEW1 |69255 |49429 | | | |2 | └─HASH GROUP BY | |69255 |49245 | | | |3 | └─HASH JOIN | |69262 |24937 | | | |4 | ├─TABLE RANGE SCAN|o(idx_orders_status)|35000 |915 | | | |5 | └─TABLE FULL SCAN |oi |100005 |6499 | | | ======================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([VIEW1.o.order_id], [T_FUN_COUNT(VIEW1.oi.product_id)], [cast(T_FUN_SUM(VIEW1.T_FUN_SUM(oi.pay_amount)), DECIMAL_INT(34, 2))]), filter([T_FUN_COUNT(VIEW1.oi.product_id) | | >= 3], [cast(T_FUN_SUM(VIEW1.T_FUN_SUM(oi.pay_amount)), DECIMAL_INT(34, 2)) > cast(10000, DECIMAL_INT(34, 2))]), rowset=256 | | group([VIEW1.o.order_id]), agg_func([T_FUN_SUM(VIEW1.T_FUN_SUM(oi.pay_amount))], [T_FUN_COUNT(VIEW1.oi.product_id)]) | | 1 - output([VIEW1.T_FUN_SUM(oi.pay_amount)], [VIEW1.o.order_id], [VIEW1.oi.product_id]), filter(nil), rowset=256 | | access([VIEW1.T_FUN_SUM(oi.pay_amount)], [VIEW1.o.order_id], [VIEW1.oi.product_id]) | | 2 - output([T_FUN_SUM(oi.pay_amount)], [o.order_id], [oi.product_id]), filter(nil), rowset=256 | | group([o.order_id], [oi.product_id]), agg_func([T_FUN_SUM(oi.pay_amount)]) | | 3 - output([o.order_id], [oi.product_id], [oi.pay_amount]), filter(nil), rowset=256 | | equal_conds([o.order_id = oi.order_id]), other_conds(nil) | | 4 - output([o.order_id]), filter(nil), rowset=256 | | access([o.order_id]), partitions(p0) | | is_index_back= false , is_global_index= false , | | range_key([o.order_status], [o.order_id]), range(S,MIN ; S,MAX), | | range_cond([o.order_status = 'S' ]) | | 5 - output([oi.order_id], [oi.product_id], [oi.pay_amount]), filter(nil), rowset=256 | | access([oi.order_id], [oi.product_id], [oi.pay_amount]), partitions(p0) | | is_index_back= false , is_global_index= false , | | range_key([oi.item_id]), range(MIN ; MAX)always true | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 30 rows in set (0.017 sec) obclient(root@sys)[ob_test]> 我们先分析上面的SQL如何进行处理, SELECT
-> o.order_id,
-> COUNT(DISTINCT oi.product_id) AS product_cnt, -> SUM(oi.pay_amount) AS total_pay -> FROM orders o -> JOIN order_items oi -> ON o.order_id = oi.order_id -> WHERE o.order_status = 'S' -> GROUP BY o.order_id -> HAVING COUNT(DISTINCT oi.product_id) >= 3 -> AND SUM(oi.pay_amount) > 10000; 在SEEKDB 中首先我们一句话来分析执行计划中,先扫描订单,然后扫描明细表,通过hash join 将数据进行join后再进行聚合,having 过滤。这里我们添加推卸索引,优化后,再进行查询计划的分析。
obclient(root@sys)[ob_test]> explain SELECTo.order_id, -> COUNT(DISTINCT oi.product_id) AS product_cnt, -> SUM(oi.pay_amount) AS total_pay -> FROM orders o -> JOIN order_items oi -> ON o.order_id = oi.order_id -> WHERE o.order_status = 'S' -> GROUP BY o.order_id -> HAVING COUNT(DISTINCT oi.product_id) >= 3 -> AND SUM(oi.pay_amount) > 10000; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ', COUNT(DISTINCT oi.product_id) AS product_cnt, SUM(oi.pay_amount) AS to' at line 1 obclient(root@sys)[ob_test]> explain SELECT o.order_id, -> COUNT(DISTINCT oi.product_id) AS product_cnt, -> SUM(oi.pay_amount) AS total_pay -> FROM orders o -> JOIN order_items oi -> ON o.order_id = oi.order_id -> WHERE o.order_status = 'S' -> GROUP BY o.order_id -> HAVING COUNT(DISTINCT oi.product_id) >= 3 AND SUM(oi.pay_amount) > 10000; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------------------------- | | |0 |MERGE GROUP BY | |392 |39687 | | | |1 |└─SUBPLAN SCAN |VIEW1 |69251 |32516 | | | |2 | └─MERGE GROUP BY | |69251 |32332 | | | |3 | └─MERGE JOIN | |69258 |22145 | | | |4 | ├─TABLE FULL SCAN |oi(idx_items_order_product_pay)|100000 |6498 | | | |5 | └─TABLE RANGE SCAN|o(idx_orders_status) |35000 |915 | | | =================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([VIEW1.o.order_id], [T_FUN_COUNT(VIEW1.oi.product_id)], [cast(T_FUN_SUM(VIEW1.T_FUN_SUM(oi.pay_amount)), DECIMAL_INT(34, 2))]), filter([T_FUN_COUNT(VIEW1.oi.product_id) | | >= 3], [cast(T_FUN_SUM(VIEW1.T_FUN_SUM(oi.pay_amount)), DECIMAL_INT(34, 2)) > cast(10000, DECIMAL_INT(34, 2))]), rowset=256 | | group([VIEW1.o.order_id]), agg_func([T_FUN_SUM(VIEW1.T_FUN_SUM(oi.pay_amount))], [T_FUN_COUNT(VIEW1.oi.product_id)]) | | 1 - output([VIEW1.T_FUN_SUM(oi.pay_amount)], [VIEW1.o.order_id], [VIEW1.oi.product_id]), filter(nil), rowset=256 |
| access([VIEW1.T_FUN_SUM(oi.pay_amount)], [VIEW1.o.order_id], [VIEW1.oi.product_id]) | | 2 - output([T_FUN_SUM(oi.pay_amount)], [o.order_id], [oi.product_id]), filter(nil), rowset=256 | | group([o.order_id], [oi.product_id]), agg_func([T_FUN_SUM(oi.pay_amount)]) | | 3 - output([o.order_id], [oi.product_id], [oi.pay_amount]), filter(nil), rowset=256 | | equal_conds([o.order_id = oi.order_id]), other_conds(nil) | | merge_directions([ASC]) | | 4 - output([oi.order_id], [oi.product_id], [oi.pay_amount]), filter(nil), rowset=256 | | access([oi.order_id], [oi.product_id], [oi.pay_amount]), partitions(p0) | | is_index_back= false , is_global_index= false , | | range_key([oi.order_id], [oi.product_id], [oi.pay_amount], [oi.item_id]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true | | 5 - output([o.order_id]), filter(nil), rowset=256 | | access([o.order_id]), partitions(p0) | | is_index_back= false , is_global_index= false , | | range_key([o.order_status], [o.order_id]), range(S,MIN ; S,MAX), | | range_cond([o.order_status = 'S' ]) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 31 rows in set (0.013 sec) 优化前的执行计划
优化后的执行计划
优化后的执行计划 从先后两个执行的执行计划看,整体执行计划有了较大的变化
1 Hash 变为 merge 那么这样有什么好处
首先这里的数据是有序的,在有对应的索引后,并不需要构建hash表,不需要更大的内存来进行数据的重构,在使用中边扫描边用相关的数据。
同时我们注意虽然还有table full scan 但是这里扫描的不是表扫描的是新加的索引,这里在顺序的扫描b+tree 速度比堆表要快的多。
2 优化后的执行顺序
2.1 oi(idx_items_order_product_pay) (order_id, product_id, pay_amount)
2.2 idx_orders_status (order_status='S', order_id)
2.3 o.order_id = oi.order_id
2.4 GROUP BY (order_id, product_id)
2.5 GROUP BY (order_id) HAVING ...
优化并后并不只是速度的快慢变化,而是对于内存的需求的大小的变化,有利于在有限的资源提供更好的SQL执行计划,这才是客户端,线下产品要的。
但是如果是MYSQL会怎么样,俩字 凉拌 !!
1 线下客户端SQL的执行方式和服务器端完全不一样,没有足够的内存,CPU提供,没有专人去维护,我们需要的是皮实耐用,且通过简单优化就可以达到良好优化效果的数据库,而不是娇滴滴的,官宦小姐,没有专业的人去伺候她。
2 兼容MYSQL的语法,安装简单,同时优化器强悍,这就是线下客户端要的数据库产品
(别拿服务器端说事,要的是客户终端产品,简单批示耐用,才是王道)SeekDB需要仅仅是人气,和时间, 和纪老师的坚持,不信我们就走着瞧!!