但在PolarDB for MySQL中则不是,完全可以利用多个从节点,将select 的语句分流到只读节点上,因为PolarDB for MySQL可以做到读写分离中的读节点和写节点的数据是一致的,这是通过软硬兼施的方案,实现的功能。PolarDB 会智能的评判那个从节点性能更好,将SQL打入到从节点本身。
那么在掌握了MySQL的基本SQL优化手段这个咱们不提了,咱们今天只说PolarDB for MySQL 自己的优化方式。
DELIMITER $$ CREATE DEFINER=`polar_admin`@`%` PROCEDURE `sp_load_test_data`() BEGIN -- ====== 必须先声明变量(DECLARE 必须在 BEGIN 后最前面) ====== DECLARE v_users INT DEFAULT 100000; -- 最小表 100k DECLARE v_products INT DEFAULT 200000; -- 产品 200k DECLARE v_customers INT DEFAULT 500000; -- customers 500k DECLARE v_order_items INT DEFAULT 300000; -- order_items 300k DECLARE v_orders INT DEFAULT 10000000; -- orders 10M (最大表) -- ====== 会话级超时设置,防止客户端/连接断开 ====== SET SESSION net_write_timeout = 600; SET SESSION net_read_timeout = 600; SET SESSION wait_timeout = 28800; SET SESSION interactive_timeout = 28800; SET SESSION MAX_EXECUTION_TIME = 0; -- ====== 清理旧表(先删 seq_numbers 再建,避免重复) ====== DROP TABLE IF EXISTS seq_numbers; DROP TABLE IF EXISTS order_items; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS users; -- ====== 建表:5 张表(无外键),含索引与 AUTO_INCREMENT ====== CREATE TABLE users ( user_id BIGINT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(100), email VARCHAR(150), country VARCHAR(50), INDEX idx_email (email), INDEX idx_country (country) ) ENGINE=InnoDB; CREATE TABLE products ( product_id BIGINT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(200), category VARCHAR(100), price DECIMAL(10,2), INDEX idx_category (category), INDEX idx_price (price) ) ENGINE=InnoDB; CREATE TABLE customers ( customer_id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(200), city VARCHAR(100), phone VARCHAR(50), INDEX idx_city (city) ) ENGINE=InnoDB; CREATE TABLE orders ( order_id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT, order_date DATETIME, status VARCHAR(50), INDEX idx_customer (customer_id), INDEX idx_order_date (order_date) ) ENGINE=InnoDB; CREATE TABLE order_items ( item_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_id BIGINT, product_id BIGINT, quantity INT, price DECIMAL(10,2), INDEX idx_order (order_id), INDEX idx_product (product_id) ) ENGINE=InnoDB; -- ====== 生成序列表 seq_numbers(1..10,000,000) ====== -- 说明:用 7 次 digits CROSS JOIN 生成 10^7 行,再 LIMIT 10000000 CREATE TABLE seq_numbers (n INT PRIMARY KEY) ENGINE=InnoDB; /* 初始化用户变量并填充 seq_numbers 到 10,000,000(一次性生成) 注意:此语句会产生大量行并占用资源,执行时间视实例规格而定 */ INSERT INTO seq_numbers (n) SELECT @row := @row + 1 AS n FROM (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) f CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) g CROSS JOIN (SELECT @row := 0) r LIMIT 10000000; -- 固定生成 10,000,000 个序号 -- ====== 批量插入:从 seq_numbers 中取前 N 条,避免逐行插入 ====== -- users 100,000 INSERT INTO users (user_name, email, country) SELECT CONCAT('user_', n), CONCAT('user_', n, '@example.com'), ELT(1 + FLOOR(RAND()*5), 'China','USA'
,'UK','Germany','India') FROM seq_numbers WHERE n <= 100000; -- products 200,000 INSERT INTO products (product_name, category, price) SELECT CONCAT('product_', n), ELT(1 + FLOOR(RAND()*8), 'Electronics','Books','Clothing','Food','Sports','Home','Beauty','Toys'), ROUND(RAND()*1000, 2) FROM seq_numbers WHERE n <= 200000; -- customers 500,000 INSERT INTO customers (customer_name, city, phone) SELECT CONCAT('customer_', n), ELT(1 + FLOOR(RAND()*6), 'Beijing','Shanghai','Shenzhen','Guangzhou','NewYork','London'), CONCAT('1', LPAD(FLOOR(RAND()*1000000000), 10, '0')) FROM seq_numbers WHERE n <= 500000; -- orders 10,000,000 (可能耗时,依赖 seq_numbers 生成的大序列) INSERT INTO orders (customer_id, order_date, status) SELECT 1 + FLOOR(RAND() * 500000), -- random customer id within 1..500k DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*3650) DAY), -- 随机近10年日期 ELT(1 + FLOOR(RAND()*4), 'PENDING','PAID','SHIPPED','CANCELLED') FROM seq_numbers WHERE n <= 10000000; -- order_items 300,000 INSERT INTO order_items (order_id, product_id, quantity, price) SELECT 1 + FLOOR(RAND() * 10000000), -- random order id within 1..10M 1 + FLOOR(RAND() * 200000), -- random product id within 1..200k 1 + FLOOR(RAND() * 10), ROUND(RAND()*500, 2) FROM seq_numbers WHERE n <= 300000; -- ====== 可选:删除 seq_numbers(若希望释放空间) ====== -- DROP TABLE IF EXISTS seq_numbers; END$$ DELIMITER ;
通过存储过程,我们产生测试表和数据。
通过执行固定的SQL语句,在不添加任何优化措施的情况下,我们查看通过并行打开后的结果差
分别是不打开并行,打开4个并行,打开2个并行
打开并行
从上图,开启4个并行后,执行时间比不开启并行速度要快 32%,开启两个并行快 29%。
这里可以得出,开启并行后,的确比不开要快,但开启过多的并行并未得到更快线性的速度提升。
EXPLAIN ANALYZE SELECT /*+ PARALLEL(4) */ u.user_id, u.user_name, COUNT(o.order_id) AS total_orders, SUM(oi.quantity * p.price) AS total_amount FROM users u JOIN orders o ON u.user_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id GROUP BY u.user_id, u.user_name ORDER BY total_amount DESC LIMIT 10;
--优化前 SELECT * FROM ( SELECT * FROM sample_table.tb_order WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE) AND product_type IN (2, 4) ) o LEFT JOIN ( SELECT * FROM sample_table.tb_order_detailed WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE) ) od
ON o.order_id = od.order_id LEFT JOIN ( SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn FROM sample_table.tb_order_sku t WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE) AND coalesce(product_type, '0') <> '5' ) os ON od.id = os.detail_id; ---优化后 SELECT * FROM ( SELECT * FROM db_order.tb_order WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE) AND product_type IN (2, 4) ) o LEFT JOIN ( SELECT * FROM db_order.tb_order_detailed WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE) ) od ON o.order_id = od.order_id LEFT JOIN LATERAL(( SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn FROM db_order.tb_order_sku t WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE) AND coalesce(product_type, '0') <> '5' AND od.id = detail_id )) os;
上面的查询已经演示了,如果开启上面的查询特殊的功能后, AND od.id = detail_id 将推入到本身OS查询的内部,来提前过滤数据。SQL的执行效率会大大提升。