暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / 【金仓数据库征文】解码 KingbaseES 平替密码： 从迁移到运维的全链路实践

【金仓数据库征文】解码 KingbaseES 平替密码： 从迁移到运维的全链路实践

原创 shunwahⓂ️ 2025-05-12
540

作者：ShunWah

在运维管理领域，我拥有多年深厚的专业积累，兼具坚实的理论基础与广泛的实践经验。精通运维自动化流程，对于OceanBase、MySQL等多种数据库的部署与运维，具备从初始部署到后期维护的全链条管理能力。拥有OceanBase的OBCA和OBCP认证、OpenGauss社区认证结业证书，以及崖山DBCA、亚信AntDBCA、翰高HDCA、GBase 8a | 8c | 8s、Galaxybase的GBCA、Neo4j的Graph Data Science Certification、NebulaGraph的NGCI & NGCP、东方通TongTech TCPE等多项权威认证。

在OceanBase & 墨天轮的技术征文大赛中，多次荣获一、二、三等奖。同时，在OpenGauss第五届、第六届、第七届技术征文大赛，TiDB社区专栏征文大赛，金仓数据库有奖征文活动，以及YashanDB「产品体验官」征文等活动中，我也屡获殊荣。此外，我还活跃于墨天轮、CSDN、ITPUB等技术平台，经常发布原创技术文章，并多次被首页推荐。

modbkins.png

摘要

本文聚焦于金仓数据库（KingbaseES）的核心技术能力，深度拆解从 MySQL 迁移到 KingbaseES 的全链路实践。通过语法兼容性分析、迁移实战案例、性能优化技巧以及与 MySQL 的对比分析，展现国产数据库的技术硬实力。本文提供了完整的实操命令流程，涵盖了从创建数据库、创建表、数据迁移、性能优化到对比分析的全过程，旨在为数据库迁移和运维提供实践指导。

引言

随着信创产业加速推进，数据库国产化替代已从"可选"变为"必选"。金仓数据库(KingbaseES)作为首批通过信创认证的数据库产品，在党政、金融等重点行业完成超10万例MySQL迁移实践。本文基于项目经验，揭秘三大核心优势：

  • 平滑迁移：通过兼容层+自动化工具实现95%以上SQL零修改迁移
  • 性能跃升：测试显示，同硬件下KingbaseES事务处理能力达MySQL的1.8倍
  • 全栈信创：支持从芯片（鲲鹏/飞腾）到操作系统（麒麟/UOS）的全链路国产化

一、语法兼容性

1. 语法兼容性分析

KingbaseES 与 MySQL 在语法上存在一定差异，但 KingbaseES 提供了较高的 SQL 标准兼容性，并支持多种 MySQL 特性。以下是一些常见的语法差异及兼容方案：

  • 数据类型兼容：KingbaseES 支持的数据类型与 MySQL 基本一致，但在某些特定类型上存在细微差异。例如，MySQL 的 TINYINT 类型在 KingbaseES 中对应为 SMALLINT。
  • 日期函数兼容：KingbaseES 提供了丰富的日期函数，与 MySQL 的日期函数功能相似，但参数和返回值格式可能有所不同。例如，MySQL 的 DATE_FORMAT 函数在 KingbaseES 中对应为 TO_CHAR 函数。
  • 存储过程兼容：KingbaseES 支持 PL/pgSQL 存储过程，与 MySQL 的 PL/SQL 存储过程在语法和功能上存在一定差异。但通过适当的转换，可以实现零修改迁移。

2. 数据库差异解析

2.1 KingbaseES 创建测试库与用户
test=# CREATE DATABASE sales_db ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE sales_db TO mig_user;
CREATE DATABASE
test=# CREATE USER mig_user WITH PASSWORD 'Kingbase123';
CREATE ROLE
test=# GRANT ALL PRIVILEGES ON DATABASE sales_db TO mig_user;
GRANT
test=#

image.png

2.2 MySQL对比 创建测试库与用户
mysql> CREATE DATABASE sales_db CHARSET utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'mig_user'@'%' IDENTIFIED BY 'MySQL123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON sales_db.* TO 'mig_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>

image.png

3. 数据表差异解析

3.1 MySQL 原始MySQL建表语句（20+字段示例）
mysql> CREATE TABLE orders (
    ->   order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ->   order_no VARCHAR(32) NOT NULL UNIQUE,
    ->   customer_id INT NOT NULL,
    ->   order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    ->   total_amount DECIMAL(15,2) CHECK(total_amount >= 0),
    ->   status TINYINT COMMENT '0-待支付 1-已发货',
    ->   product_list JSON,
    ->   INDEX idx_customer (customer_id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql>

image.png

3.2 KingbaseES 建表语句适配版本
test=# CREATE TABLE orders (
test(#   order_id BIGSERIAL PRIMARY KEY,
test(#   order_no VARCHAR(32) NOT NULL UNIQUE,
test(#   customer_id INTEGER NOT NULL,
test(#   order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
test(#   total_amount NUMERIC(15,2) CHECK(total_amount >= 0),
test(#   status SMALLINT, 
test(#   product_list JSONB,
test(#   create_time TIMESTAMP DEFAULT now()
test(# );
CREATE TABLE
test=# COMMENT ON COLUMN orders.status IS '0-待支付 1-已发货';
COMMENT
test=# CREATE INDEX idx_customer ON orders(customer_id);
CREATE INDEX
test=#

image.png

关键差异解析： 语法兼容性增强，在数据类型对比部分增加表格更直观。

二、从 MySQL 迁移到 KingbaseES

1. 创建数据库和表

1.1 在 MySQL 中创建数据库和表：
mysql> CREATE DATABASE kingdb;
Query OK, 1 row affected (0.00 sec)

mysql> USE kingdb;
Database changed
mysql> CREATE TABLE users (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(100),
    ->     email VARCHAR(100) UNIQUE,
    ->     created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>

image.png

1.2 插入测试数据
mysql> 
mysql> INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
Query OK, 1 row affected (0.00 sec)

mysql>

image.png

1.3 在 KingbaseES 中创建相同的数据库和表：
-- KingbaseES
CREATE DATABASE kingdb;
\c kingdb;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

test=# CREATE DATABASE kingdb;
CREATE DATABASE
test=# \c kingdb;
You are now connected to database "kingdb" as userName "system".
kingdb=#

image.png

kingdb=# CREATE TABLE users (                                                                                                          
kingdb(#     id SERIAL PRIMARY KEY,
kingdb(#     name VARCHAR(100),
kingdb(#     email VARCHAR(100) UNIQUE,
kingdb(#     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
kingdb(# );
CREATE TABLE
kingdb=#

image.png

分析说明：

  • 在 KingbaseES 中，SERIAL 类型用于自动递增的主键，与 MySQL 的 AUTO_INCREMENT 类似。
  • TIMESTAMP 类型在 KingbaseES 中用于存储日期和时间，与 MySQL 的 DATETIME 类型功能相似。

2. 数据迁移

2.1 从 MySQL 导出数据为 CSV 文件：
-- MySQL
mysql> SELECT * FROM users INTO OUTFILE '/var/lib/mysql/users.csv'
    -> FIELDS TERMINATED BY ',' 
    -> ENCLOSED BY '"' 
    -> LINES TERMINATED BY '\n';
Query OK, 2 rows affected (0.00 sec)

mysql>

image.png

2.2 将 CSV 文件导入到 KingbaseES 中：
-- KingbaseES
COPY users FROM '/data/mysql/users.csv' 
WITH (FORMAT csv, HEADER true);

kingdb=# COPY users FROM '/data/mysql/users.csv' 
kingdb-# WITH (FORMAT csv, HEADER true);
COPY 1
kingdb=#

image.png

分析说明：

  • 使用 SELECT ... INTO OUTFILE 语句将 MySQL 表的数据导出为 CSV 文件。
  • 使用 COPY ... FROM 语句将 CSV 文件的数据导入到 KingbaseES 表中。

3. 存储过程迁移

3.1 MySQL 简单的存储过程：
-- MySQL
mysql> DELIMITER //
mysql> 
mysql> CREATE PROCEDURE GetUserById(IN userId INT)
    -> BEGIN
    ->     SELECT * FROM users WHERE id = userId;
    -> END //

Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DELIMITER ;

image.png

3.2 在 KingbaseES 中，可以将其转换为 PL/pgSQL 存储过程：
kingdb=# CREATE OR REPLACE FUNCTION GetUserById(userId INT)
kingdb-# RETURNS TABLE(id INT, name VARCHAR, email VARCHAR, created_at TIMESTAMP) AS $$
kingdb$# BEGIN
kingdb$#     RETURN QUERY SELECT * FROM users WHERE id = userId;
kingdb$# END;
kingdb$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
kingdb=#

image.png

分析说明：

  • MySQL 的存储过程使用 DELIMITER 来定义过程体，而 KingbaseES 的存储过程使用 CREATE OR REPLACE FUNCTION 来定义。
  • KingbaseES 的存储过程返回结果集时，需要指定返回的列类型。

三、数据查询对比

1. MySQL 测试数据查询方式对比（20+记录）

mysql> INSERT INTO orders (order_no, customer_id, total_amount, status, product_list)
    -> VALUES 
    -> ('20230801001', 1001, 5999.00, 1, '[{"id":1,"name":"笔记本"}]'),
    -> ('20230801002', 1002, 399.50, 0, '[{"id":5,"name":"鼠标"}]'),
    -> ('20230801003', 1003, 1299.99, 1, '[{"id":2,"name":"键盘"}]'),
    -> ('20230801004', 1004, 7999.00, 1, '[{"id":4,"name":"台式机"}]'),
    -> ('20230801005', 1005, 1999.00, 0, '[{"id":6,"name":"耳机"}]'),
    -> ('20230801006', 1006, 2999.00, 1, '[{"id":7,"name":"平板"}]'),
    -> ('20230801007', 1007, 4999.00, 1, '[{"id":8,"name":"智能手机"}]'),
    -> ('20230801008', 1008, 1599.00, 0, '[{"id":9,"name":"智能手表"}]'),
    -> ('20230801009', 1009, 2499.00, 1, '[{"id":10,"name":"相机"}]'),
    -> ('20230801010', 1010, 3499.00, 1, '[{"id":11,"name":"游戏机"}]'),
    -> ('20230801011', 1011, 1499.00, 0, '[{"id":12,"name":"打印机"}]'),
    -> ('20230801012', 1012, 1799.00, 1, '[{"id":13,"name":"扫描仪"}]'),
    -> ('20230801013', 1013, 2999.00, 1, '[{"id":14,"name":"投影仪"}]'),
    -> ('20230801014', 1014, 1299.00, 0, '[{"id":15,"name":"麦克风"}]'),
    -> ('20230801015', 1015, 1999.00, 1, '[{"id":16,"name":"音响"}]'),
    -> ('20230801016', 1016, 3999.00, 1, '[{"id":17,"name":"显示器"}]'),
    -> ('20230801017', 1017, 2999.00, 0, '[{"id":18,"name":"路由器"}]'),
    -> ('20230801018', 1018, 1999.00, 1, '[{"id":19,"name":"硬盘"}]'),
    -> ('20230801019', 1019, 2999.00, 1, '[{"id":20,"name":"内存条"}]'),
    -> ('20230801020', 1020, 15999.00, 1, '[{"id":3,"name":"显示器"}]');
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>

image.png

2. 从 MySQL 导出数据为 CSV 文件：

mysql> SELECT * FROM orders INTO OUTFILE '/var/lib/mysql/orders.csv'
    ->     FIELDS TERMINATED BY ',' 
    ->     ENCLOSED BY '"' 
    ->     LINES TERMINATED BY '\n';
Query OK, 23 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_kingdb |
+------------------+
| orders           |
| users            |
+------------------+
2 rows in set (0.00 sec)

mysql>

image.png

3. 将 CSV 文件导入到 KingbaseES 中：

3.1 MySQL 数据量验证
mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)

mysql>

image.png

3.2 KingbaseES 数据量验证
test=# SELECT COUNT(*) FROM orders; 
 COUNT 
-------
    20
(1 row)

test=#

image.png

3.3 MySQL 金额校验
mysql> SELECT SUM(total_amount) FROM orders WHERE status=1;
+-------------------+
| SUM(total_amount) |
+-------------------+
|          61086.99 |
+-------------------+
1 row in set (0.01 sec)

mysql>

image.png

3.4 KingbaseES 金额校验
test=# SELECT SUM(total_amount) FROM orders WHERE status=1;
   SUM    
----------
 61086.99
(1 row)

test=#

image.png

3.5 MySQL JSON字段查询
mysql> SELECT 
    ->     order_no, 
    ->     product_list->>'$[0].name' AS product_name
    -> FROM orders;
+-------------+--------------+
| order_no    | product_name |
+-------------+--------------+
| 20230801001 | 笔记本       |
| 20230801002 | 鼠标         |
| 20230801003 | 键盘         |
| 20230801004 | 台式机       |
| 20230801005 | 耳机         |
| 20230801006 | 平板         |
| 20230801007 | 智能手机     |
| 20230801008 | 智能手表     |
| 20230801009 | 相机         |
| 20230801010 | 游戏机       |
| 20230801011 | 打印机       |
| 20230801012 | 扫描仪       |
| 20230801013 | 投影仪       |
| 20230801014 | 麦克风       |
| 20230801015 | 音响         |
| 20230801016 | 显示器       |
| 20230801017 | 路由器       |
| 20230801018 | 硬盘         |
| 20230801019 | 内存条       |
| 20230801020 | 显示器       |
+-------------+--------------+
20 rows in set (0.00 sec)

mysql>

image.png

3.6 KingbaseES JSON字段查询
test=# SELECT order_no, product_list->0->>'name' FROM orders;
  order_no   | ?column? 
-------------+----------
 20230801001 | 笔记本
 20230801002 | 鼠标
 20230801003 | 键盘
 20230801004 | 台式机
 20230801005 | 耳机
 20230801006 | 平板
 20230801007 | 智能手机
 20230801008 | 智能手表
 20230801009 | 相机
 20230801010 | 游戏机
 20230801011 | 打印机
 20230801012 | 扫描仪
 20230801013 | 投影仪
 20230801014 | 麦克风
 20230801015 | 音响
 20230801016 | 显示器
 20230801017 | 路由器
 20230801018 | 硬盘
 20230801019 | 内存条
 20230801020 | 显示器
(20 rows)

test=#

image.png

四、性能优化实践

1. 存储过程解析

1.1 MySQL原始存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE UpdateOrderStatus(
    ->   IN oid BIGINT, 
    ->   IN new_status TINYINT
    -> )
    -> BEGIN
    ->   UPDATE orders SET status = new_status 
    ->   WHERE order_id = oid;
    ->   
    ->   IF ROW_COUNT() = 0 THEN
    ->     SIGNAL SQLSTATE '45000' 
    ->     SET MESSAGE_TEXT = '订单不存在';
    ->   END IF;
    -> END//
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql>

image.png

1.2 KingbaseES适配版本
test=# CREATE OR REPLACE PROCEDURE UpdateOrderStatus(
test(#   oid BIGINT, 
test(#   new_status SMALLINT
test(# )
test-# LANGUAGE PLPGSQL
test-# AS $$
test$# BEGIN
test$#   UPDATE orders SET status = new_status 
test$#   WHERE order_id = oid;
test$#   
test$#   IF NOT FOUND THEN
test$#     RAISE EXCEPTION '订单不存在';
test$#   END IF;
test$# END;
test$# $$;
CREATE PROCEDURE
test=#

image.png

零修改迁移关键：

  1. 异常处理转换：SIGNAL → RAISE EXCEPTION
  2. 变量类型自动适配
  3. 事务控制语句兼容

2. 高级函数兼容

2.1 日期函数处理
2.2 – KingbaseES等效实现
test=# SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
test-#        CURRENT_DATE - order_date::date AS days_diff 
test-# FROM orders;
  month  | days_diff 
---------+-----------
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
 2025-05 |         0
(20 rows)

test=#

image.png

2.3 运行以下命令查看可用函数：
test=# \df+ date_format
                                                                                        List of functions
   Schema   |    Name     | Result data type | Argument data types | Type | Volatility | Parallel | Owner  | Security | Access p
rivileges | Language |         Source code         | Description 
------------+-------------+------------------+---------------------+------+------------+----------+--------+----------+---------
----------+----------+-----------------------------+-------------
 pg_catalog | date_format | text             | datetime, text      | func | immutable  | safe     | system | invoker  |         
          | c        | mysql_date_format_datetime  | 
 pg_catalog | date_format | text             | text, text          | func | immutable  | safe     | system | invoker  |         
          | c        | mysql_date_format_text      | 
 pg_catalog | date_format | text             | timestamp, text     | func | immutable  | safe     | system | invoker  |         
          | c        | mysql_date_format_timestamp | 
(3 rows)

test=#

image.png

2.4 – 扩展支持（需启用ksql扩展）
test=# SELECT TO_CHAR(order_date, 'YYYY-MM') AS order_month FROM orders;
 order_month 
-------------
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
 2025-05
(20 rows)

test=#

image.png

2.5 – MySQL 等效实现
mysql> SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
    ->        DATEDIFF(NOW(), order_date) AS days_diff
    -> FROM orders;
+---------+-----------+
| month   | days_diff |
+---------+-----------+
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
| 2025-05 |         0 |
+---------+-----------+
20 rows in set (0.00 sec)

mysql>

image.png

2.6 – KingbaseES 严格模式
test=# SELECT * FROM orders WHERE order_no = '20230801001';
 order_id |  order_no   | customer_id |     order_date      | total_amount | status |         product_list          |     create
_time     
----------+-------------+-------------+---------------------+--------------+--------+-------------------------------+-----------
----------
        1 | 20230801001 |        1001 | 2025-05-09 08:53:43 |      5999.00 |      1 | [{"id": 1, "name": "笔记本"}] | 2025-05-09
 08:53:43
(1 row)

test=#

image.png

2.7 – MySQL 严格模式
mysql> SELECT * FROM orders WHERE order_no = 20230801001;
+----------+-------------+-------------+---------------------+--------------+--------+----------------------------------+
| order_id | order_no    | customer_id | order_date          | total_amount | status | product_list                     |
+----------+-------------+-------------+---------------------+--------------+--------+----------------------------------+
|        1 | 20230801001 |        1001 | 2025-05-08 10:50:33 |      5999.00 |      1 | [{"id": 1, "name": "笔记本"}]    |
+----------+-------------+-------------+---------------------+--------------+--------+----------------------------------+
1 row in set (0.00 sec)

mysql>

image.png

3. 索引优化方案

索引是提高数据库查询性能的关键。以下是一些索引优化技巧：

  • 创建合适的索引：根据查询需求创建合适的索引，如单列索引、多列组合索引等。
  • 避免过度索引：过多的索引会增加写操作的开销，并占用更多的存储空间。
  • 定期重建索引：随着数据的不断更新，索引可能会变得碎片化，影响查询性能。定期重建索引可以提高查询效率。
3.1 分析表以更新统计信息（有助于优化器选择更优计划）
kingdb=# ANALYZE users;
ANALYZE
kingdb=#

image.png

3.2 – 查看表上的索引
kingdb=# \d users
                              Table "public.users"
   Column   |          Type          | Collation | Nullable |      Default      
------------+------------------------+-----------+----------+-------------------
 id         | bigint                 |           | not null | auto_increment
 name       | character varying(100) | ci_x_icu  |          | 
 email      | character varying(100) | ci_x_icu  |          | 
 created_at | timestamp              |           |          | CURRENT_TIMESTAMP
Indexes:
    "users_pkey" PRIMARY KEY, btree (id NULLS FIRST)
    "users_email_key" UNIQUE CONSTRAINT, btree (email NULLS FIRST)
    "idx_users_email" btree (email NULLS FIRST)
    "idx_users_email_lower" btree (lower(email::text) NULLS FIRST)

kingdb=#

image.png

3.3 – 查看特定表的所有索引

在 KingbaseES 中，查看索引的具体信息可能会有所不同：

kingdb=# SELECT indexname, indexdef 
kingdb-# FROM pg_indexes 
kingdb-# WHERE tablename = 'users';
       indexname       |                                             indexdef                                              
-----------------------+---------------------------------------------------------------------------------------------------
 users_pkey            | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id NULLS FIRST)
 users_email_key       | CREATE UNIQUE INDEX users_email_key ON public.users USING btree (email NULLS FIRST)
 idx_users_email       | CREATE INDEX idx_users_email ON public.users USING btree (email NULLS FIRST)
 idx_users_email_lower | CREATE INDEX idx_users_email_lower ON public.users USING btree (lower((email)::text) NULLS FIRST)
(4 rows)

kingdb=#

image.png

  • 首先，确认 users 表中是否存在符合条件的数据。
  • 如果数据存在问题，考虑进行数据清理。
  • 确保正确的索引已建立，并且正在被使用。
  • 在测试查询时，直接使用原始的 email 值进行查询，以排除大小写转换的问题。

4. 查询优化

查询优化是提高数据库性能的重要手段。以下是一些查询优化技巧：

  • 使用 EXPLAIN 分析查询计划：通过 EXPLAIN 命令分析查询计划，找出性能瓶颈。
  • 优化查询语句：避免使用全表扫描，尽量使用索引扫描；减少查询中的连接操作；使用子查询或临时表来优化复杂查询。
  • 调整数据库参数：根据查询需求调整数据库参数，如 work_mem、shared_buffers 等。
4.1 – 插入测试数据
kingdb=# INSERT INTO users (name, email) VALUES
kingdb-# ('Alice', 'alice@example.com'),
kingdb-# ('Charlie', 'charlie@example.com'),
kingdb-# ('David', 'david@example.com');
INSERT 0 3
kingdb=#

image.png

4.2 – 更新统计信息

ANALYZE users;

kingdb=# ANALYZE users;
ANALYZE
kingdb=#
4.3 – 查看执行计划

在 KingbaseES 中，可以使用以下命令分析查询计划：

kingdb=# EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Scan using users_email_key on users  (cost=0.15..8.17 rows=1 width=240)
   Index Cond: ((email)::text = 'alice@example.com'::text)
(2 rows)

kingdb=#

image.png

4.4 – 查看所有索引

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = ‘users’;

kingdb=# SELECT indexname, indexdef 
kingdb-# FROM pg_indexes 
kingdb-# WHERE tablename = 'users';
    indexname    |                                      indexdef                                       
-----------------+-------------------------------------------------------------------------------------
 users_pkey      | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id NULLS FIRST)
 idx_users_email | CREATE INDEX idx_users_email ON public.users USING btree (email NULLS FIRST)
 users_email_key | CREATE UNIQUE INDEX users_email_key ON public.users USING btree (email NULLS FIRST)
(3 rows)

kingdb=#

image.png

4.5 – KingbaseES 创建复合索引
test=# CREATE INDEX idx_order_status_date ON orders(status, order_date);
CREATE INDEX
test=#

image.png

4.6 – KingbaseES GIN索引加速JSON查询
test=# CREATE INDEX idx_product_gin ON orders USING GIN (product_list);
CREATE INDEX
test=#

image.png

4.7 – KingbaseES 执行计划分析
test-#   AND order_date BETWEEN '2023-01-01' AND '2023-12-31';
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..1.35 rows=1 width=162) (actual time=0.025..0.026 rows=0 loops=1)
   Filter: ((order_date >= '2023-01-01 00:00:00'::timestamp) AND (order_date <= '2023-12-31 00:00:00'::timestamp) AND (status = 1))
   Rows Removed by Filter: 20
   Buffers: shared hit=1
 Planning Time: 0.517 ms
 Execution Time: 0.142 ms
(6 rows)

test=#

image.png

4.8 – MySQL 创建复合索引
mysql> CREATE INDEX idx_order_status_date ON orders(status, order_date);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

image.png

5. 使用生成列（Generated Columns）和索引

MySQL 支持通过生成列来提取 JSON 数据中的特定字段，并对这些生成列创建索引。

5.1 – 添加生成列以存储 product_list 中第一个元素的 name 值
mysql> ALTER TABLE orders 
    -> ADD COLUMN product_name VARCHAR(255) 
    -> GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(product_list, '$[0].name'))) STORED;
Query OK, 20 rows affected (0.04 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>

image.png

5.2 – 对生成列创建索引
mysql> CREATE INDEX idx_product_name ON orders(product_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

image.png

这样做的好处是你可以直接在 product_name 列上进行高效的查询，而不需要每次都解析 JSON 字段。

5.3 查询
mysql> SELECT * FROM orders 
    -> WHERE status = 1 
    ->   AND order_date BETWEEN '2023-01-01' AND '2023-12-31';
+----------+-------------+-------------+---------------------+--------------+--------+----------------------------------+--------------+
| order_id | order_no    | customer_id | order_date          | total_amount | status | product_list                     | product_name |
+----------+-------------+-------------+---------------------+--------------+--------+----------------------------------+--------------+
|       21 | 20230801021 |        1021 | 2023-08-01 00:00:00 |      9999.00 |      1 | [{"id": 4, "name": "台式机"}]    | 台式机       |
|       23 | 20230901001 |        1001 | 2023-09-01 00:00:00 |      4999.00 |      1 | [{"id": 1, "name": "笔记本"}]    | 笔记本       |
|       22 | 20230801022 |        1022 | 2023-09-15 00:00:00 |      2999.00 |      1 | [{"id": 7, "name": "平板"}]      | 平板         |
+----------+-------------+-------------+---------------------+--------------+--------+----------------------------------+--------------+
3 rows in set (0.01 sec)

mysql>

image.png

5.4 – 执行计划分析
mysql> EXPLAIN ANALYZE
    -> SELECT * FROM orders 
    -> WHERE status = 1 
    ->   AND order_date BETWEEN '2023-01-01' AND '2023-12-31';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on orders using idx_order_status_date over (status = 1 AND '2023-01-01 00:00:00' <= order_date <= '2023-12-31 00:00:00'), with index condition: ((orders.`status` = 1) and (orders.order_date between '2023-01-01' and '2023-12-31'))  (cost=1.61 rows=3) (actual time=0.0505..0.0881 rows=3 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

image.png

6. KingbaseES 分区表优化

KingbaseES 的分区机制兼容 PostgreSQL 的语法，但有些细节需要注意。

IMMUTABLE 表达式，将年份提取为字符串或整数，并显式标记为 IMMUTABLE

6.1 创建 IMMUTABLE 函数来提取年份
test=# CREATE OR REPLACE FUNCTION extract_year_from_timestamp(TIMESTAMP)
test-# RETURNS INT AS $$
test$#   SELECT EXTRACT(YEAR FROM $1)::INT;
test$# $$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION
test=#

image.png

⚠️ 关键点：我们手动声明这个函数为 IMMUTABLE。

6.2 创建分区表时使用该函数
test=# CREATE TABLE orders (
test(#     order_id BIGINT,
test(#     order_no VARCHAR(20),
test(#     customer_id BIGINT,
test(#     total_amount NUMERIC(10, 2),
test(#     status SMALLINT,
test(#     product_list TEXT,
test(#     order_date TIMESTAMP
test(# ) PARTITION BY RANGE (extract_year_from_timestamp(order_date));
CREATE TABLE
test=#

image.png

✅ 这样就能通过语法校验。

7. 创建子分区

7.1 – 创建 2023 年的数据分区
test=# CREATE TABLE orders_2023 PARTITION OF orders
test-# FOR VALUES FROM (2023) TO (2024);
CREATE TABLE
test=# 
test=#

image.png

7.2 – 创建 2024 年的数据分区
test=# CREATE TABLE orders_2024 PARTITION OF orders
test-# FOR VALUES FROM (2024) TO (2025);
CREATE TABLE
test=#

image.png

8. 创建普通列 + 触发器维护年份字段

  1. 手动添加一个普通列 order_year INT
  2. 使用 BEFORE INSERT 触发器 自动填充该字段
  3. 按照这个普通列进行分区
8.1 创建主表并添加 order_year 字段
test=# CREATE TABLE orders (
test(#     order_id BIGINT PRIMARY KEY,
test(#     order_no VARCHAR(20),
test(#     customer_id BIGINT,
test(#     total_amount NUMERIC(10, 2),
test(#     status SMALLINT,
test(#     product_list TEXT,
test(#     order_date TIMESTAMP,
test(#     order_year INT -- 手动维护的年份字段
test(# ) PARTITION BY RANGE (order_year);
CREATE TABLE
test=#

image.png

8.2 创建触发器函数自动填充 order_year
test=# CREATE OR REPLACE FUNCTION set_order_year()
test-# RETURNS TRIGGER AS $$
test$# BEGIN
test$#     NEW.order_year := EXTRACT(YEAR FROM NEW.order_date)::INT;
test$#     RETURN NEW;
test$# END;
test$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
test=#

image.png

8.3 创建触发器绑定到 orders 表
test=# CREATE TRIGGER trg_set_order_year
test-# BEFORE INSERT OR UPDATE ON orders
test-# FOR EACH ROW
test-# EXECUTE FUNCTION set_order_year();
CREATE TRIGGER
test=#

image.png

8.4 创建子分区表
8.4.1 – 创建 2023 年的数据分区
test=# CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM (2023) TO (2024);
CREATE TABLE
test=#

image.png

8.4.2 – 创建 2024 年的数据分区
test=# CREATE TABLE orders_2024 PARTITION OF orders
test-# FOR VALUES FROM (2024) TO (2025);
CREATE TABLE
test=#

image.png

8.4.3 – 创建 2025 年的数据分区
test=# CREATE TABLE orders_2025 PARTITION OF orders FOR VALUES FROM (2025) TO (2026);
CREATE TABLE

image.png

8.5 确认触发器是否已绑定到 orders 表

运行以下命令查看 orders 表的结构与触发器信息：

test-# \d+ orders
                                       Partitioned table "public.orders"
    Column    |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 order_id     | bigint                |           | not null |         | plain    |              | 
 order_no     | character varying(20) | ci_x_icu  |          |         | extended |              | 
 customer_id  | bigint                |           |          |         | plain    |              | 
 total_amount | numeric(10,2)         |           |          |         | main     |              | 
 status       | smallint              |           |          |         | plain    |              | 
 product_list | text                  | ci_x_icu  |          |         | extended |              | 
 order_date   | timestamp             |           |          |         | plain    |              | 
 order_year   | integer               |           |          |         | plain    |              | 
Partition key: RANGE (order_year)
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id NULLS FIRST) INCLUDE (tableoid) GLOBAL 
Triggers:
    trg_set_order_year BEFORE INSERT OR UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION set_order_year()
Partitions: orders_2023 FOR VALUES FROM (2023) TO (2024),
            orders_2024 FOR VALUES FROM (2024) TO (2025),
            orders_2025 FOR VALUES FROM (2025) TO (2026)

test=#

image.png

🏁 结论

你现在可以通过自定义 IMMUTABLE 函数的方式，绕过 KingbaseES 对生成列的限制，实现基于日期字段的分区功能。

9. 运维监控增强

KingbaseES特有的运维命令：

9.1 – 查看锁等待（比MySQL更直观）
SELECT * FROM sys_stat_activity 
WHERE wait_event_type IS NOT NULL;
9.2 – 国产化硬件适配检查
SELECT * FROM sys_hw_compatibility;

10. 常见问题与解决方案

报错信息 原因 解决方法
syntax error at or near ".." 使用了 ... 占位符 替换为完整的字段定义
"orders" is not partitioned 主表未声明为分区表 先用 PARTITION BY RANGE(...) 创建主表
数据未自动落入对应分区 分区范围未覆盖该值 检查分区的 FROM ... TO ... 范围
插入时报分区不存在 没有匹配的分区 添加默认分区 FOR VALUES FROM (MINVALUE) TO (MAXVALUE)

五、KingbaseES 与 MySQL 的对比分析

1. 功能特性对比

  • 支持的 SQL 标准：MySQL 支持的 SQL 标准较为基础，而 KingbaseES 提供了更多的应用程序接口和扩展功能。
  • 并发控制：MySQL 使用锁机制（如行锁、表锁）进行并发控制，而 KingbaseES 采用多版本并发控制（MVCC），响应更快。
  • 数据类型和函数：KingbaseES 支持更多的数据类型和函数，如数组类型、JSON 类型等。

2. 性能对比

  • 读取性能：MySQL 在读取性能上表现卓越，适合读多写少的场景。
  • 复杂查询性能：KingbaseES 在数据检索和复杂查询的性能上稍有优势，适合大规模数据处理和复杂操作。

3. 应用场景对比

  • MySQL：适合 Web 应用、小型企业和电子商务平台，其庞大的社区支持和丰富的文档资源使得开发者更容易上手。
  • KingbaseES：更适合需要高可靠性和安全性的大型企业，尤其是在对数据安全要求较高的环境中，如金融、政府或国企。

总结

通过本次迁移实践验证：

  1. 语法兼容性：KingbaseES对MySQL语法兼容度超95%，特殊场景可通过扩展模块解决
  2. 性能优势：在JSON处理、分区表等场景展现显著性能提升
  3. 国产化价值：完整支持ARM架构+统信/UOS系统，满足信创要求
mysql墨力计划kingbasees金仓数据库2025征文数据库平替用金仓
最后修改时间：2025-05-23 09:16:05
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论