「YashanDB个人版体验」探测100%自我研发的YashanDB | 10点总结
站长
· 阅读数 5
前文
数据库一直是高科技领域的产品，YashanDB自称是100%研发。笔者非常好奇 ，国内大部分都是基于PG或者MySQL的基础上进行开发的，即使是群众中耳熟能详的TiDB、OceanBase以及openGauss。TiDB的存储引擎Tikv主要使用了rocksDB，而openGauss基于pg9.2.4开发，openGauss理解消化了pg9.2.4的核心代码，自我创新一套新的存储引擎、事务管理等。 OceanBase没有参与PG或MySQL,自称从零开始的数据库，里面的材质也是使用了sst作为持久化文件 。所以YashanDB的100%的自我研发，带 着这样的好奇之心调研实践了YashanDB。
调研中， YashanDB目前有个人版、标准版和企业版，支持x86、ARM、龙芯，支持单机布署、分布式布置、共享集群布置多种方式。居然同时支持Oracle模式和MySQL模式。下面以个人版的单机布署为入口，安装在X86机器，管中窥豹，领略一番。
安装
最理想的安装方式 yum install 软件包，yashandb目前没有到达这一步。
笔者尝试，首选是创建一个 yashan的普通用户，yashan用户无密钥ssh打通，打开一个WEB 服务，如下
./yasom --web --listen 192.168.153.128:9001
浏览器上输入http://192.168.153.128:9001
点击快署也可能快速完成安装布署 ，布署完成如下
设置SYS密码
yasboot cluster password set -n XXXX1234$ -c yashandb
命令登录yashandb
yasql sys/"xxxx1234$"@192.168.153.128:1688
卸装方面也简单，需要把两个目录删除，目录如下。
/home/yashan/.yasboot/
/home/yashan/yasdb_home
Oracle模式
开发者体验
//创建表空间
SQL> create tablespace tbs_test datafile '/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/data/tbs_test.dbf' size 10m autoextend on;
Succeed.
//创建普通表
SQL> CREATE TABLE t_yashan(
stuNo CHAR(4),
name VARCHAR2(20),
age NUMBER(3),
birthday DATE,
sex VARCHAR2(5)
) tablespace tbs_test;
SQL> desc t_yashan;
NAME NULL? DATATYPE
---------------------------------------------------------------- --------- ---------------------------------
STUNO CHAR(4)
NAME VARCHAR(20)
AGE NUMBER(3)
BIRTHDAY DATE
SEX VARCHAR(5)
查看执行SQL的执行计划
ALTER SESSION SET statistics_level=ALL;
explain select * from t_yashan;
SQL> create index idx_stuNo_yashan on t_yashan(stuNo);
Succeed.
SQL> select stuno from t_yashan where stuno=6;
Execution Plan
----------------------------------------------------------------
SQL hash value: 3536985664
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | | | | | | |
|* 1 | INDEX FAST FULL SCAN | IDX_STUNO_YASHAN | SYS | 100| | 92( 0)| | | | | |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : filter("T_YASHAN"."STUNO" = 6)
Statistics
----------------------------------------------------------------------------------------------------
14 rows fetched.
SQL> set autotrace traceonly;
SQL> select /*+ full(t_yashan) */ stuno from t_yashan where stuno=6;
Execution Plan
----------------------------------------------------------------
SQL hash value: 1130584602
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | | | | | | |
|* 1 | TABLE ACCESS FULL | T_YASHAN | SYS | 100| | 152( 0)| | | | | |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : filter("T_YASHAN"."STUNO" = 6)
Statistics
----------------------------------------------------------------------------------------------------
14 rows fetched.
SQL> select /*+ no_index(t_yashan) */ stuno from t_yashan where stuno=6;
Execution Plan
----------------------------------------------------------------
SQL hash value: 3678947624
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | | | | | | |
|* 1 | TABLE ACCESS FULL | T_YASHAN | SYS | 100| | 152( 0)| | | | | |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : filter("T_YASHAN"."STUNO" = 6)
Statistics
----------------------------------------------------------------------------------------------------
14 rows fetched.
SQL> create index idx_name_yashan on t_yashan(stuNo,name);
Succeed.
SQL> create index idx_age_yashan on t_yashan(age desc);
Succeed.
//查看表结构
SQL> SELECT dbms_metadata.get_ddl('TABLE', 'T_YASHAN') FROM dual;
DBMS_METADATA.GET_DD
----------------------------------------------------------------
CREATE TABLE "SYS"."T_YASHAN"
("STUNO" CHAR(4),
"NAME" VARCHAR(20),
"AGE" NUMBER(3, 0),
"BIRTHDAY" DATE,
"SEX" VARCHAR(5)
) PCTFREE 8 INITRANS 2 MAXTRANS 255
LOGGING
TABLESPACE "SYSTEM"
SEGMENT CREATION DEFERRED
ORGANIZATION HEAP
1 row fetched.
//创建普通表加分区
CREATE TABLE t_yashan_date(
stuNo CHAR(4),
name VARCHAR2(20),
age NUMBER(3),
birthday DATE,
sex VARCHAR2(5),
ttime date
)partition by range(ttime)
(
partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')),
partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')),
partition p3 values less than(to_date('2022-01-01', 'YYYY-MM-DD'))
);
//查看表结构
SQL> SELECT dbms_metadata.get_ddl('TABLE', 'T_YASHAN_DATE') FROM dual;
DBMS_METADATA.GET_DD
----------------------------------------------------------------
CREATE TABLE "SYS"."T_YASHAN_DATE"
("STUNO" CHAR(4),
"NAME" VARCHAR(20),
"AGE" NUMBER(3, 0),
"BIRTHDAY" DATE,
"SEX" VARCHAR(5),
"TTIME" DATE
) PCTFREE 8 INITRANS 2 MAXTRANS 255
LOGGING
TABLESPACE "SYSTEM"
SEGMENT CREATION DEFERRED
PARTITION BY RANGE ("TTIME")
(PARTITION "P1" VALUES LESS THAN (to_date('2020-01-01', 'YYYY-MM-DD'))
PCTFREE 8 INITRANS 2 MAXTRANS 255
TABLESPACE "SYSTEM",
PARTITION "P2" VALUES LESS THAN (to_date('2021-01-01', 'YYYY-MM-DD'))
PCTFREE 8 INITRANS 2 MAXTRANS 255
TABLESPACE "SYSTEM",
PARTITION "P3" VALUES LESS THAN (to_date('2022-01-01', 'YYYY-MM-DD'))
PCTFREE 8 INITRANS 2 MAXTRANS 255
TABLESPACE "SYSTEM")
ORGANIZATION HEAP
1 row fetched.
//查询所有的表名
SELECT dbms_metadata.get_ddl('TABLE', table_name) FROM user_tables;
//增加列名
ALTER TABLE t_yashan ADD tel VARCHAR2(20);
//修改列名
ALTER TABLE t_yashan MODIFY tel VARCHAR2(30);
//删除列名
ALTER TABLE t_yashan DROP COLUMN tel;
//更改列名
ALTER TABLE t_yashan RENAME COLUMN name TO yashan_name;
CREATE USER henley IDENTIFIED BY henley;
ALTER USER henley IDENTIFIED BY "henley1234";
GRANT connect to henley;
GRANT resource to henley;
GRANT dba to henley;
功能视图
-- 序列
SQL> create sequence test_seq increment by 1 start with 50;
Succeed.
--同义词
SQL> create or replace synonym test_syn for user_tables;
Succeed.
--触发器
SQL> create table test_trigger(a int,b int);
Succeed.
--角色类型
select * from dba_roles;
每日归档量查询
SELECT SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 AS "Size(M)", TRUNC(completion_time) FROM v$archived_log GROUP BY TRUNC(completion_time);
--segment查看
SQL> select segment_name,segment_type,tablespace_name,blocks,extents,bytes/1024/1024 from user_segments where segment_name= 'T_YASHAN';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS EXTENTS BYTES/1024/1024
---------------------------------------------------------------- --------------------- ---------------------------------------------------------------- --------------------- --------------------- ---------------
T_YASHAN TABLE TBS_TEST 2560 35 20
1 row fetched.
--线程查看
SQL> select name, thread_id,status from v$process;
NAME THREAD_ID STATUS
--------------------------------- --------------------- ---------------------------------
TIMER 9182 Working
BUFFER_POOL 9183 Working
PRELOADER 9185 Working
PRELOADER 9186 Working
SMON 9187 Working
CKPT 9188 Working
DBWR 9189 Working
DBWR 9190 Working
SCHD_TIMER 9191 Working
TCP_LSNR 9192 Working
LISTENER_LOG 9194 Working
TCP_LSNR 9195 Working
TCP_LSNR 9196 Working
MMON 9197 Working
JOB_QUEUE 9198 Working
RD_ARCH 9214 Working
ARCH_DATA 9215 Working
HEALTH_MONITOR 9222 Working
PARAL_WORKER_0 9235 Working
PARAL_WORKER_1 9236 Working
LOGW 9238 Working
XFMR 9239 Working
XFMR_WORKER_0 9240 Working
XFMR_WORKER_1 9241 Working
XFMR_WORKER_2 9242 Working
XFMR_WORKER_3 9243 Working
XFMR_WORKER_4 9244 Working
XFMR_WORKER_5 9245 Working
XFMR_WORKER_6 9246 Working
XFMR_WORKER_7 9247 Working
XFMR_WORKER_8 9248 Working
XFMR_WORKER_9 9249 Working
XFMR_WORKER_10 9250 Working
XFMR_WORKER_11 9251 Working
XFMR_WORKER_12 9252 Working
XFMR_WORKER_13 9253 Working
XFMR_WORKER_14 9254 Working
XFMR_WORKER_15 9255 Working
XFMR_WORKER_16 9256 Working
XFMR_WORKER_17 9257 Working
XFMR_WORKER_18 9258 Working
XFMR_WORKER_19 9259 Working
XFMR_WORKER_20 9260 Working
XFMR_WORKER_21 9261 Working
XFMR_WORKER_22 9262 Working
XFMR_WORKER_23 9263 Working
XFMR_WORKER_24 9264 Working
XFMR_WORKER_25 9266 Working
XFMR_WORKER_26 9267 Working
XFMR_WORKER_27 9268 Working
XFMR_WORKER_28 9269 Working
XFMR_WORKER_29 9270 Working
XFMR_WORKER_30 9271 Working
XFMR_WORKER_31 9272 Working
WORKER 25340 Working
55 rows fetched.
-- 包与存储过程
SQL> create package my_package as
procedure my_procedure (
p_input_param in integer,
p_output_param out integer
);
end my_package;
/
Succeed.
create package body my_package as
procedure my_procedure (
p_input_param in integer,
p_output_param out integer
) as
begin
p_output_param := p_input_param * 2;
end my_procedure;
end my_package;
/
Succeed.
SQL> set serveroutput on
SQL> declare
v_output_param integer;
begin
my_package.my_procedure(10, v_output_param);
dbms_output.put_line('output parameter value: ' || v_output_param);
end;
PL/SQL Succeed.
--表空间
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
----------------------------------------------------------------
SYSTEM
SYSAUX
TEMP
SWAP
USERS
UNDO
--表统计
SQL> SELECT table_name,num_rows,blocks,avg_row_len,sample_size from user_tab_statistics;
--列统计
select column_name,num_distinct,low_value,high_value,avg_col_len from user_tab_col_statistics where table_name = 't_yashan';
业务测试
以前有一批Oracle业务表上面的SQL，大约300多个，现在把它们都移动yashandb上面进行测试
#!/bin/sh
for file in `ls /home/yashan/oracle_sql/*`
do
echo $file >>batch.log
yasboot sql -d sys/"gxxx1234$"@192.168.153.128:1688 -f $file>>batch.log
done
由于语句太多，下面没有一一列举，把报错的摘录如下，出错的这些都需要改造
YAS-00004 feature "Window Function NTH_VALUE" has not been implemented yet
YAS-00004 feature "Window Function Percent Rank" has not been implemented yet
YAS-04243 invalid identifier "RATIO_TO_REPORT"
YAS-02012 sequence does not exist
YAS-02012 table or view does not exist
YAS-04243 invalid identifier "UNISTR"
YAS-04209 unexpected word by
YAS-00207 end of text expected but b found
YAS-04115 "RETURN" expected but missing
YAS-04115 "IS|AS" expected but missing
系统架构
YashanDB相比mysql、pg的内存占用对比如何？ 默认初始化的数据库，显然YashanDB占的内存更多。
从系统底层查看yashandb约有56个工作线程 ，具体线程名字结构与Oracle的名称差不多。
LGWR,DBWR,SMON,CKPT都是Oracle的后台进程名称，从线程名字可以知道 这些线程是干什么的。
进入到/home/yashan/yasdb_home/yashandb/23.1.1.100/lib目录，找出yas相关的so文件
nm -D libyas_infra.so.1.1.100，nm命令主要是用来列出目标文件中函数和全局变量
ldd -v libyas_infra.so.1.1.100
lsof -K | grep yashan
readelf -l libyas_infra.so.1.1.100用于显示ELF（Executable and Linkable Format）格式的可执行文件、共享库和目标文件的信息，包括头部信息、节区信息、符号表等等。
网络采集数据包，观察数据包里面有没有mysql协议或者pg相关的协议
tcpdump -i ens33 tcp and port 1688 -w test1688.cap
tcpdump: listening on ens33, link-type EN10MB (Ethernet), capture size 262144 bytes
152 packets captured
153 packets received by filter
0 packets dropped by kernel
结果没有发现mysql协议或者pg相关的协议
日志
[root@server128 yashandb]# find /home/yashan/ -name "*log"
/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/log
/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/log/start.log
/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/log/listener/listener.log
/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/log/alert/alert.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/log
/home/yashan/yasdb_home/yashandb/23.1.1.100/log/monit.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/log/yashandb/db-1-1/run/run.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/log/yashandb/db-1-1/slow/slow.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/ext/monit/log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasom-start.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasagent-panic.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasagent-start.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasom-panic.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasagent.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasom.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yasboot.log
/home/yashan/yashan/om/yasboot.log
start.log日志与实例启动的相关信息有关
listen.log监听日志可以记录用户名登录成功或失败
alert.log记录警告相关的信息。 monit.log记录yashandb程序跟踪的信。 run.log记录yashandb程序运行的信息。 slow.log记录yashandb慢日志相关的信息。
总结
明亡之后无华夏，崖山之后无中国，有人喜欢明朝那些事，没人知道崖山海战君民一心的悲壮，对yashandb的归纳总结如下。
- YashanDB个人版是一个单进程多线程的数据库，多线程 的工作结构类似Oracle，Oracle是多进程组织的，
- 目前YashanDB具有两种模式，oracle模式与mysql模式，明显当前YashanDB的精力支持oracle模式多一点，对oracle的DBA以及开发者友善，虽然很多视图和功能在开发中。mysql模式不如oracle模式。
- yashandb已经建立产品级的运维体系结构，例如产品 YAS-错误编码，无论是协助使用者排错还是使产品升级迭代，都大有脾益。
- 从线程 和占用内存以及目录结构来说，YashanDB没有站在PG或者MySQL的肩膀上进行开发的痕迹。
- 从内陷库文件来查看，YashanDB有很多自己定义的yas库函数，这些都是力证100%代码自主可控的证据。
- YashanDB的存储引擎默认创建的表是堆表组织形式，这里笔者嘴碎一下。ORACEL也是默认堆表,堆表更擅长写多读少的场景。官方称HEAP表类型，属于段页式结构，逻辑存储上与Oracle一致，因此保留Oracle原有的tablespace表空间、segment段、block数据块,extends数据区结构特色，更擅长TP，默认隔离水平是RC,DATA_BUFFER_SIZE是缓冲区容量设置。如果业务要用数据分析等需求，建议使用TAC表类型和LSC表类型。
- 通过show parameter查看，YashanDB一共有177个参数，而oracle12.2.0.1.0一共有500个参数，参数多少不能说明谁强大， 但是从侧明说明功能丰富程度和调整的选择空间。
- 从开发者体验来看，yashandb有自己的jdbc，社区指导有批量提交的api demo代码，已经能够满足应用开发者的需求进行WEB应用的开发。
- 从功能视图来看，日常oracle的常规运维命令，yashandb都能够支持。
- 从业务测试来看，YashanDB的小目标是与Oracle做到100%的兼容 ，现在来看没有Oracle所有的函数和视图，有些业务还是需要做一些YashanDB的适配。
最后
YashanDB没有选择开源，把它的存储引擎、SQL解析、并发控制和事务管理技术细节共享，但是我们可以管中窥豹，领略一番。从产品线的类型来看，从首选兼容ORACLE模式来看，从产品闭源的角度来看， YashanDB与达梦多处重合较相似。
数据库是长久的一份事业，数据库是一个世界的事业，2023年11月共283个数据库参与排名，在风云变幻的数据库市场，只有不断的研发和创新，才能提升产品竞争力。YashanDB作为少数不多有自主研究可控实力的厂商，要做大做强做精，认真务实是致胜法宝。