概述
前段时间崖山数据库做推广活动，参加了yca的考试，为啥参加这个数据库厂商的学习呢，因为目前这个数据库最像Oracle，又听公司的前辈说呢，这个公司的前身是华为人出来做的数据库，华为人的精神呢，我是比较信服的，是一帮子做事的人。所以呢就开始了学习，也初步体验一下，大概就是体验到能安装完成一个单机个人版的学习环境，然后创建测试表，再然后看看执行计划，建立个索引这些基础的体验项目。
安装过程，请参见YashanDB服务端个人版安装部署-CSDN博客，这个博客我看着算是比较完成的安装过程。我这里就赘述了。
基础运维操作
部署实例
cd /home/yashan/install
./bin/yasboot cluster deploy -t yashandb.tomlyashandb.toml 实例文件的配置文件，规定了哪个data 目录，以及实例名字等。[yashan@Kylin69 install]$ cat yashandb.toml
cluster = "yashandb"
create_simple_schema = false
uuid = "666ef6a05088db684227fee09dbb3a1e"
yas_type = "SE"[[group]]group_type = "db"name = "dbg1"[group.config]CHARACTER_SET = "utf8"ISARCHIVELOG = trueREDO_FILE_NUM = 4REDO_FILE_SIZE = "128M"[[group.node]]data_path = "/data/yashan/yasdb_data"hostid = "host0001"role = 1[group.node.config]CGROUP_ROOT_DIR = "/sys/fs/cgroup"LISTEN_ADDR = "10.203.13.69:1688"REPLICATION_ADDR = "10.203.13.69:1689"RUN_LOG_FILE_PATH = "/data/yashan/yasdb_home/yashandb/23.2.4.100/log/yashandb/db-1-1/run"RUN_LOG_LEVEL = "INFO"SLOW_LOG_FILE_PATH = "/data/yashan/yasdb_home/yashandb/23.2.4.100/log/yashandb/db-1-1/slow"
[yashan@Kylin69 install]$
删除实例
yasboot cluster clean --cluster yashandb --purge
重新创建实例
pwd
/home/yashan/install./bin/yasboot cluster deploy -t yashandb.toml--查看实例状态
yasboot cluster status -c yashandb
--停止实例
yasboot cluster stop -c yashandb
--重新启动实例
yasboot process yasom start -c yashandb
yasboot process yasagent start -c yashandb
yasboot cluster start -c yashandb环境变量设置
./bin/yasboot cluster deploy -t yashandb.toml
cd /data/yashan/yasdb_home/yashandb/23.2.4.100/conf
cat yashandb.bashrc >> ~/.bashrc
source ~/.bashrc修改管理员密码
yasboot cluster password set -n Swg_202408 -c yashandb
yasboot cluster status -c yashandb
yasql sys/Swg_202408解锁普通用户
yasql sys/Swg_202408
--解锁MDSYS用户，并将该用户的密码设置为Swg_202408
alter user MDSYS account unlock identified by Swg_202408;
--授权角色给用户MDSYS
grant create session to MDSYS;
grant dba to MDSYS;
--在yasql状态下切换用户到MDSYS
conn MDSYS/Swg_202408
这dba_users 视图对于从Oracle转换过dba 算是比较友好的。使用上面的命令就可以切换到MDSYS用户。
创建应用表
我们还是创建最常见的Oracle的scott三个表，部门表dept,员工信息表emp,职级表salgrade
相关的DDL代码如下
CREATE TABLE dept(deptno INT PRIMARY KEY,dname VARCHAR(14),loc VARCHAR(13)
);CREATE TABLE emp
(EMPNO INT(4) PRIMARY KEY,ENAME VARCHAR(10),JOB VARCHAR(9),MGR INT(4),HIREDATE DATE,SAL DOUBLE,COMM DOUBLE,deptno INT,
FOREIGN KEY (deptno) REFERENCES dept(deptno));CREATE TABLE salgrade (grade INT PRIMARY KEY,losal INT,hisal INT);INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
--yasql 自动提交默认是关闭的
commit;
INSERT INTO salgrade VALUES(1,700,1200);
INSERT INTO salgrade VALUES(2,1201,1400);
INSERT INTO salgrade VALUES(3,1401,2000);
INSERT INTO salgrade VALUES(4,2001,3000);
INSERT INTO salgrade VALUES(5,3001,9999);
--yasql 自动提交默认是关闭的
commit;
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,DATE('1980-12-17'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,DATE('1981-2-20'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,DATE('1981-2-22'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,DATE('1981-4-2'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,DATE('1981-9-28'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,DATE('1981-5-1'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,DATE('1981-6-9'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,DATE('1987-4-19'),3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,DATE('1981-11-17'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,DATE('1981-9-8'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,DATE('1987-5-23'),1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,DATE('1981-12-3'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,DATE('1981-12-3'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,DATE('1982-1-23'),1300,NULL,10);
--yasql 自动提交默认是关闭的
commit;--清楚数据的
delete from emp;
delete from salgrade;
delete from dept;
--查看数据
select * from emp;
select * from salgrade;
select * from dept;简单进行了SQL语句的执行计划的查看
SQL> explain select e.EMPNO,e.ENAME,e.JOB from dept d,emp e where d.DEPTNO=e.DEPTNO;PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 3510891856
Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED INDEX LOOPS INNER | | | 100000| 165( 0)| |
| 2 | TABLE ACCESS FULL | EMP | MDSYS | 100000| 126( 0)| |
|* 3 | INDEX UNIQUE SCAN | SYS_C_23 | MDSYS | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):
---------------------------------------------------3 - Predicate : access("D"."DEPTNO" = "E"."DEPTNO")16 rows fetched.SQL>
通过hint影响SQL的执行计划
LEADING
LEADING语法能够改变Join两边表的顺序，常见做法是通过LEADING调整大小表，减少扫描数量；或通过LEADING调整表连接顺序，增加选择率，减少执行负担。
SQL> create table t1(t1_id int,t1_name varchar(50));Succeed.SQL> create table t2(t2_id int,t2_name varchar(50));Succeed.SQL> explain select * from t1,t2 where t1.t1_id=t2.t2_id;PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 1636752117
Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN INNER | | | 100000| 354( 0)| |
| 2 | TABLE ACCESS FULL | T1 | MDSYS | 100000| 121( 0)| |
| 3 | TABLE ACCESS FULL | T2 | MDSYS | 100000| 121( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):
---------------------------------------------------1 - Predicate : access("T1"."T1_ID" = "T2"."T2_ID")16 rows fetched.SQL> explain select /*+LEADING(t1,t2)*/ * from t1,t2 where t1.t1_id=t2.t2_id;PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 3417037079
Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN INNER | | | 100000| 354( 0)| |
| 2 | TABLE ACCESS FULL | T1 | MDSYS | 100000| 121( 0)| |
| 3 | TABLE ACCESS FULL | T2 | MDSYS | 100000| 121( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):
---------------------------------------------------1 - Predicate : access("T1"."T1_ID" = "T2"."T2_ID")16 rows fetched.SQL> explain select /*+LEADING(t2,t1)*/ * from t1,t2 where t1.t1_id=t2.t2_id;PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2731071767
Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN INNER | | | 100000| 354( 0)| |
| 2 | TABLE ACCESS FULL | T2 | MDSYS | 100000| 121( 0)| |
| 3 | TABLE ACCESS FULL | T1 | MDSYS | 100000| 121( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):
---------------------------------------------------1 - Predicate : access("T2"."T2_ID" = "T1"."T1_ID")16 rows fetched.SQL>
SQL> explain select * from t1,t2 where t1.t1_id=t2.t2_id;PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 1636752117
Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN INNER | | | 100000| 354( 0)| |
| 2 | TABLE ACCESS FULL | T1 | MDSYS | 100000| 121( 0)| |
| 3 | TABLE ACCESS FULL | T2 | MDSYS | 100000| 121( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):
---------------------------------------------------1 - Predicate : access("T1"."T1_ID" = "T2"."T2_ID")16 rows fetched.SQL>
后记
如果各位有兴趣，可以参加一下崖山yca认证，通过官方的体系培训，跟价深入的去了解这款比较新的国产化数据库。以下是官网yca的链接。YashanDB|崖山数据库系统YashanDB学习中心-YCA认证详情
ps:今天就先体验到这里了，等以后再有时间再续写这篇博客。
