【SequoiaDB】6 巨杉数据库SequoiaDB操作数据

Sequoia DB巨杉数据库作为分布式数据库，由数据库存储引擎与数据库实例两大模块组成。本篇分别从存储引擎和实例层面分别演示对数据的操作。

1 存储引擎上的数据操作

1.1 查看节点启动列表

[sdbadmin@sdbserver1 ~]$ sdblist -l -m local

Name SvcName Role PID GID NID PRY GroupName StartTime DBPath

sequoiadb 11810 coord 2294 2 2 Y SYSCoord 2020-03-31-20.11.27 /opt/sequoiadb/database/coord/11810/

sequoiadb 11830 data 2300 1001 1001 Y group2 2020-03-31-20.11.27 /opt/sequoiadb/database/data/11830/

sequoiadb 11820 data 2288 1000 1000 Y group1 2020-03-31-20.11.27 /opt/sequoiadb/database/data/11820/

sequoiadb 11800 catalog 2291 1 1 Y SYSCatalogGroup 2020-03-31-20.11.27 /opt/sequoiadb/database/catalog/11800/

sequoiadb 11840 data 2297 1002 1002 Y group3 2020-03-31-20.11.27 /opt/sequoiadb/database/data/11840/

Total: 5

1.2 查看集群拓扑结构

[sdbadmin@sdbserver1 ~]$ sdb

Welcome to SequoiaDB shell!

help() for help, Ctrl+c or quit to exit

> var db=new Sdb('localhost',11810)

Takes 0.006729s.

> db.list(SDB_LIST_GROUPS)

{

"Group": [

{

"dbpath": "/opt/sequoiadb/database/catalog/11800",

"HostName": "sdbserver1",

"Service": [

{

"Type": 0,

"Name": "11800"

},

{

"Type": 1,

"Name": "11801"

},

{

"Type": 2,

"Name": "11802"

},

{

"Type": 3,

"Name": "11803"

}

],

"NodeID": 1,

"Status": 1

}

],

"GroupID": 1,

"GroupName": "SYSCatalogGroup",

"PrimaryNode": 1,

"Role": 2,

"SecretID": 1732453446,

"Status": 1,

"Version": 1,

"_id": {

"$oid": "5e7eb555890e5fae02e44942"

}

}

{

"Group": [

{

"HostName": "sdbserver1",

"Status": 1,

"dbpath": "/opt/sequoiadb/database/coord/11810/",

"Service": [

{

"Type": 0,

"Name": "11810"

},

{

"Type": 1,

"Name": "11811"

},

{

"Type": 2,

"Name": "11812"

}

],

"NodeID": 2

}

],

"GroupID": 2,

"GroupName": "SYSCoord",

"Role": 1,

"SecretID": 73712140,

"Status": 1,

"Version": 2,

"_id": {

"$oid": "5e7eb55b890e5fae02e44946"

}

}

{

"Group": [

{

"HostName": "sdbserver1",

"Status": 1,

"dbpath": "/opt/sequoiadb/database/data/11820/",

"Service": [

{

"Type": 0,

"Name": "11820"

},

{

"Type": 1,

"Name": "11821"

},

{

"Type": 2,

"Name": "11822"

}

],

"NodeID": 1000

}

],

"GroupID": 1000,

"GroupName": "group1",

"PrimaryNode": 1000,

"Role": 0,

"SecretID": 875250555,

"Status": 1,

"Version": 3,

"_id": {

"$oid": "5e7eb55e890e5fae02e44947"

}

}

{

"Group": [

{

"HostName": "sdbserver1",

"Status": 1,

"dbpath": "/opt/sequoiadb/database/data/11830/",

"Service": [

{

"Type": 0,

"Name": "11830"

},

{

"Type": 1,

"Name": "11831"

},

{

"Type": 2,

"Name": "11832"

}

],

"NodeID": 1001

}

],

"GroupID": 1001,

"GroupName": "group2",

"PrimaryNode": 1001,

"Role": 0,

"SecretID": 1173688836,

"Status": 1,

"Version": 3,

"_id": {

"$oid": "5e7eb55e890e5fae02e44948"

}

}

{

"Group": [

{

"HostName": "sdbserver1",

"Status": 1,

"dbpath": "/opt/sequoiadb/database/data/11840/",

"Service": [

{

"Type": 0,

"Name": "11840"

},

{

"Type": 1,

"Name": "11841"

},

{

"Type": 2,

"Name": "11842"

}

],

"NodeID": 1002

}

],

"GroupID": 1002,

"GroupName": "group3",

"PrimaryNode": 1002,

"Role": 0,

"SecretID": 1939044547,

"Status": 1,

"Version": 3,

"_id": {

"$oid": "5e7eb55e890e5fae02e44949"

}

}

Return 5 row(s).

Takes 0.011667s.

1.3 创建集合空间和集合

1）创建company_domain数据域

[sdbadmin@sdbserver1 ~]$ sdb

Welcome to SequoiaDB shell!

help() for help, Ctrl+c or quit to exit

> var db=new Sdb('localhost',11810)

Takes 0.005848s.

> db.createDomain('company_domain',['group1','group2','group3'],{AutoSplit:true})

company_domain

Takes 0.105844s.

域（Domain）是由若干个复制组（ReplicaGroup）组成的逻辑单元。每个域都可以根据定义好的策略自动管理所属数据，如数据切片和数据隔离等

2）创建company集合空间

> db.createCS('company',{Domain:'company_domain'})

localhost:11810.company

Takes 0.001753s.

3）创建employee集合

> db.company.createCL('employee',{ShardingKey:{_id:1},ShardingType:'hash',ReplSize:-1,Compressed:true,CompressionType:'lzw',AutoSplit:true,EnsureShardingIndex:false})

localhost:11810.company.employee

Takes 1.344415s.

1.4 集合数据操作

1）插入数据

> db.company.employee.insert({empno:10001,ename:'Jacky',age:32})

{

"InsertedNum": 1,

"DuplicatedNum": 0

}

Takes 0.318599s.

> db.company.employee.insert({empno:10002,ename:'Alen',age:25})

{

"InsertedNum": 1,

"DuplicatedNum": 0

}

Takes 0.001000s.

> db.company.employee.insert({empno:10003,ename:'Lucy',age:29})

{

"InsertedNum": 1,

"DuplicatedNum": 0

}

Takes 0.000681s.

> db.company.employee.insert({empno:10004,ename:'Emma',age:24})

{

"InsertedNum": 1,

"DuplicatedNum": 0

}

Takes 0.000641s.

2）查询数据

查询全部数据：

> db.company.employee.find()

{

"_id": {

"$oid": "5e833a7736a995debb8f63ab"

},

"empno": 10003,

"ename": "Lucy",

"age": 29

}

{

"_id": {

"$oid": "5e833a8436a995debb8f63ac"

},

"empno": 10004,

"ename": "Emma",

"age": 24

}

{

"_id": {

"$oid": "5e833a5d36a995debb8f63a9"

},

"empno": 10001,

"ename": "Jacky",

"age": 32

}

{

"_id": {

"$oid": "5e833a6936a995debb8f63aa"

},

"empno": 10002,

"ename": "Alen",

"age": 25

}

Return 4 row(s).

Takes 0.002246s.

查询满足某一条件的数据：

> db.company.employee.find({ename:'Alen'})

{

"_id": {

"$oid": "5e833a6936a995debb8f63aa"

},

"empno": 10002,

"ename": "Alen",

"age": 25

}

Return 1 row(s).

Takes 0.001250s.

显示某些列的数据：

> db.company.employee.find({ename:'Alen'},{empno:1,age:1})

{

"age": 30,

"empno": 10002

}

Return 1 row(s).

Takes 0.001127s.

3）更新数据

> db.company.employee.update({$set:{age:30}},{ename:'Alen'})

{

"UpdatedNum": 1,

"ModifiedNum": 1,

"InsertedNum": 0

}

Takes 0.001118s.

> db.company.employee.find({ename:'Alen'})

{

"_id": {

"$oid": "5e833a6936a995debb8f63aa"

},

"age": 30,

"empno": 10002,

"ename": "Alen"

}

Return 1 row(s).

Takes 0.001158s.

4）删除数据

> db.company.employee.remove({ename:'Alen'})

{

"DeletedNum": 1

}

Takes 0.001051s.

> db.company.employee.find({ename:'Alen'})

Return 0 row(s).

Takes 0.000849s.

1.5 索引使用

1）创建索引

> db.company.employee.createIndex('idx_employee_ename',{ename:1},false)

Takes 0.002874s.

2）查看索引

> db.company.employee.listIndexes()

{

"IndexDef": {

"name": "$id",

"_id": {

"$oid": "5e8339c874d57aafa190cbb0"

},

"key": {

"_id": 1

},

"v": 0,

"unique": true,

"dropDups": false,

"enforced": true,

"NotNull": false

},

"IndexFlag": "Normal",

"Type": "Positive"

}

{

"IndexDef": {

"name": "idx_employee_ename",

"_id": {

"$oid": "5e833c4874d57aafa190cbb1"

},

"key": {

"ename": 1

},

"v": 0,

"unique": false,

"dropDups": false,

"enforced": false,

"NotNull": false

},

"IndexFlag": "Normal",

"Type": "Positive"

}

Return 2 row(s).

Takes 0.002012s.

3）查看执行计划

> db.company.employee.find({ename:'Jacky'}).explain()

{

"NodeName": "sdbserver1:11820",

"GroupName": "group1",

"Role": "data",

"Name": "company.employee",

"ScanType": "ixscan",

"IndexName": "idx_employee_ename",

"UseExtSort": false,

"Query": {

"$and": [

{

"ename": {

"$et": "Jacky"

}

}

]

},

"IXBound": {

"ename": [

[

"Jacky",

"Jacky"

]

]

},

"NeedMatch": false,

"ReturnNum": 0,

"ElapsedTime": 0.000037,

"DataRead": 0,

"IndexRead": 0,

"UserCPU": 0,

"SysCPU": 0

}

{

"NodeName": "sdbserver1:11830",

"GroupName": "group2",

"Role": "data",

"Name": "company.employee",

"ScanType": "ixscan",

"IndexName": "idx_employee_ename",

"UseExtSort": false,

"Query": {

"$and": [

{

"ename": {

"$et": "Jacky"

}

}

]

},

"IXBound": {

"ename": [

[

"Jacky",

"Jacky"

]

]

},

"NeedMatch": false,

"ReturnNum": 0,

"ElapsedTime": 0.000037,

"DataRead": 0,

"IndexRead": 0,

"UserCPU": 0,

"SysCPU": 0

}

{

"NodeName": "sdbserver1:11840",

"GroupName": "group3",

"Role": "data",

"Name": "company.employee",

"ScanType": "ixscan",

"IndexName": "idx_employee_ename",

"UseExtSort": false,

"Query": {

"$and": [

{

"ename": {

"$et": "Jacky"

}

}

]

},

"IXBound": {

"ename": [

[

"Jacky",

"Jacky"

]

]

},

"NeedMatch": false,

"ReturnNum": 0,

"ElapsedTime": 0.000092,

"DataRead": 0,

"IndexRead": 0,

"UserCPU": 0,

"SysCPU": 0

}

Return 3 row(s).

Takes 0.002878s.

1.6 内置SQL使用

> db.exec('select * from company.employee where ename="Jacky"');

{

"_id": {

"$oid": "5e833a5d36a995debb8f63a9"

},

"empno": 10001,

"ename": "Jacky",

"age": 32

}

Return 1 row(s).

Takes 0.001650s.

内置SQL可以执行任何SQL操作，包括增、删、改、查。

1.7 退出SequoiaDB Shell

> quit

2 MySQL实例上的数据操作

2.1 查看MySQL实例状态

[sdbadmin@sdbserver1 ~]$ sdb_sql_ctl status

INSTANCE PID SVCNAME SQLDATA SQLLOG

myinst 2792 3306 /opt/sequoiasql/mysql/database/3306 /opt/sequoiasql/mysql/myinst.log

Total: 1; Run: 1

[sdbadmin@sdbserver1 ~]$ sdb_sql_ctl listinst

NAME SQLDATA SQLLOG

myinst /opt/sequoiasql/mysql/database/3306 /opt/sequoiasql/mysql/myinst.log

Total: 1

2.2 连接MySQL实例

[sdbadmin@sdbserver1 ~]$ mysql -h 127.0.0.1 -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 13

Server version: 5.7.25 Source distribution



Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.



mysql>

2.3 查看MySQL引擎

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

| SequoiaDB | DEFAULT | SequoiaDB storage engine(Community). Plugin: 5e22fa9, Driver: 3.4, BuildTime: No | YES | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

10 rows in set (0.00 sec)

由上面的结果可知：MySQL实例默认使用SequoiaDB存储引擎。

2.4 创建数据库和表

mysql> create database company;

Query OK, 1 row affected (0.00 sec)

mysql> use company;

Database changed

mysql> create table employee( empno int , ename varchar(128), age int);

Query OK, 0 rows affected (0.01 sec)

2.5 查询数据

mysql> select * from employee;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 10001 | Jacky | 32 |

| 10002 | Alen | 25 |

| 10004 | Emma | 24 |

| 10003 | Lucy | 29 |

+-------+-------+------+

4 rows in set (0.00 sec)

创建完数据库和表后，底层数据会自动映射到表。

2.6 查看MySQL实例层文件

[sdbadmin@sdbserver1 ~]$ ll /opt/sequoiasql/mysql/database/3306/company/

total 16

-rw-r----- 1 sdbadmin sdbadmin_group 60 Mar 31 21:25 db.opt

-rw-r----- 1 sdbadmin sdbadmin_group 8622 Mar 31 21:43 employee.frm

可以看出，数据是存储在底层，即数据库存储引擎层。