MySQL Shell：03 每个MySQL都有颗JSON的心

众所周知，MongoDB是一款十分优秀的schema-less文档数据库。

 DB-Engines 数据库排行榜上一直稳坐前5，曾一度在2013、2014被评为年度数据库，是文档数据库中当之无愧的带头大哥。

MySQL最近几年发展十分迅猛，5.7版本开始支持JSON，可以将MySQL用作文档存储。

但是这项功能最初的时候其实并不好用，基本还是按照SQL的方式来使用JSON。比如这种：

SELECT * FROM lnmp WHERE category = CAST('{"id": 1, "name": "lnmp.cn"}' as JSON);
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)

后来MySQL提供了X插件支持，使MySQL Server可以使用X协议与客户端进行通信。

从MySQL 8.0开始，X插件在MySQL Server中默认是启用的:

X协议支持CRUD和SQL操作，通过SASL进行身份验证，可以通过MySQL Shell或MySQL 8.0 Connectors操作数据库，为MySQL开发人员提供了MongoDB这样的行业标准支持。MySQL Shell中的X DevAPI通过JavaScript或Python作为客户端实现。

那么今天就测试下去年被评为2019年度数据库的MySQL在文档功能使用方面能否完全对标MongoDB。

---

1. 环境准备

这里我们使用官方提供的一个测试数据集，称为world_x

下载：

wget http://downloads.mysql.com/docs/world_x-db.zip 

导入world_x.sql文件到数据库

\source /caihao/world_x.sql

可以看到数据库world_x已建立

MySQL 8.0.19 localhost:3306 ssl  world_x  SQL > show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| caihao                        |
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| world_x                       |
+-------------------------------+
7 rows in set (0.0011 sec)

world_x模式包含以下对象：

• JSON格式的集合(Collection)表：

• • countryinfo：世界各国的信息表。

• 关系数据库表：

• • country：世界各国的简要信息。

  • city：国家/地区中某些城市的信息。

  • countrylanguage：每个国家/地区使用的语言。

---

2. 功能测试

注意，这里连接数据库不是用默认的3306端口，而要使用MySQL Shell的X协议33060端口连接到数据库，这样才可以使用基于X协议的文档存储功能。

X协议的端口可以在数据库参数中mysqlx_port定义：

下面主要针对各个数据库的常用命令，对比MongoDB进行简单测试

测试的数据库版本：

• MySQL 8.0.19    

• MongoDB 4.1.3

 

# 库表操作对比：

1. 切换到 world_x 数据库 

• MySQL Shell：\use world_x

• MongoDB:  use world_x

test@bj79> use world_x
switched to db world_x

2. 查看当前数据库 

• MySQL Shell：db

• MongoDB:  db

world_x@bj79> db
world_x

3. 创建 Collection 

• MySQL Shell：

  db.createCollection("caihao")

• MongoDB:  

  db.createCollection('caihao');

world_x@bj79> db.createCollection('caihao');
{
        "ok" : 1,
        "$clusterTime" : {
                "clusterTime" : Timestamp(1582518383, 2),
                "signature" : {
                        "hash" : BinData(0,"m6mUHxLgwK7JWkVPHHWnOtcisTY="),
                        "keyId" : NumberLong("6777356701147332609")
                }
        },
        "operationTime" : Timestamp(1582518383, 2)
}

4. 列出 Collection：

• MySQL Shell：db.getCollections()

• MongoDB:  show collections 

world_x@bj79> show collections
caihao

5. 删除 Collection：

• MySQL Shell：

  db.dropCollection("caihao")

• MongoDB:  db.caihao.drop();  

world_x@bj79> db.caihao.drop();  
true

# CURD操作对比：

1. 数据插入

• MySQL Shell：  db.countryinfo.add

MySQL 8.0.19 world_x JS > db.countryinfo.add(
                       ->  {
                       ->     GNP: .6,
                       ->     IndepYear: 1967,
                       ->     Name: "Sealand",
                       ->     Code: "SEA",
                       ->     demographics: {
                       ->         LifeExpectancy: 79,
                       ->         Population: 27
                       ->     },
                       ->     geography: {
                       ->         Continent: "Europe",
                       ->         Region: "British Islands",
                       ->         SurfaceArea: 193
                       ->     },
                       ->     government: {
                       ->         GovernmentForm: "Monarchy",
                       ->         HeadOfState: "Michael Bates"
                       ->     }
                       ->   }
                       -> )
                       -> 
Query OK, 1 item affected (0.0066 sec)

• MongoDB：db.countryinfo.insert

world_x@bj79> db.countryinfo.insert(
...  {
...     GNP: .6,
...     IndepYear: 1967,
...     Name: "Sealand",
...     Code: "SEA",
...     demographics: {
...         LifeExpectancy: 79,
...         Population: 27
...     },
...     geography: {
...         Continent: "Europe",
...         Region: "British Islands",
...         SurfaceArea: 193
...     },
...     government: {
...         GovernmentForm: "Monarchy",
...         HeadOfState: "Michael Bates"
...     }
...   }
... );
WriteResult({ "nInserted" : 1 })

2. 数据查询：

MySQL Shell的find语法支持大多数的条件，比如：

# 查询Australia的数据
db.countryinfo.find("Name = 'Australia'")

# 查询GNP超过5000亿美元的所有国家
db.countryinfo.find("GNP > 500000")

# 多条件查询
db.countryinfo.find("GNP > 500000 and demographics.Population < 100000000")

# 在查询字段加运算
db.countryinfo.find("GNP*1000000/demographics.Population > 30000")

详细参考下：

https://dev.mysql.com/doc/refman/8.0/en/mysql-shell-tutorial-javascript-documents-find.html

• MySQL Shell：db.countryinfo.find()

MySQL 8.0.19 world_x JS > db.countryinfo.find("Name = 'Sealand'")
{
    "GNP": 0.6,
    "_id": "00005e2988730000000000000001",
    "Code": "SEA",
    "Name": "Sealand",
    "IndepYear": 1967,
    "geography": {
        "Region": "British Islands",
        "Continent": "Europe",
        "SurfaceArea": 193
    },
    "government": {
        "HeadOfState": "Michael Bates",
        "GovernmentForm": "Monarchy"
    },
    "demographics": {
        "Population": 27,
        "LifeExpectancy": 79
    }
}
1 document in set (0.0008 sec)

• MongoDB：db.countryinfo.find()

world_x@bj79> db.countryinfo.find({ "Name" : "Sealand" }).pretty()
{
        "_id" : ObjectId("5e535401e034a078264a0bdf"),
        "GNP" : 0.6,
        "IndepYear" : 1967,
        "Name" : "Sealand",
        "Code" : "SEA",
        "demographics" : {
                "LifeExpectancy" : 79,
                "Population" : 27
        },
        "geography" : {
                "Continent" : "Europe",
                "Region" : "British Islands",
                "SurfaceArea" : 193
        },
        "government" : {
                "GovernmentForm" : "Monarchy",
                "HeadOfState" : "Michael Bates"
        }
}

3. 数据修改：

• MySQL Shell：db.countryinfo.modify()

MySQL 8.0.19 world_x JS > db.countryinfo.modify("Name = 'Sealand'").set(
                       -> "demographics", {"LifeExpectancy": 999, "Population": 999})
                       -> 
Query OK, 1 item affected (0.0066 sec)

MySQL 8.0.19 world_x JS >  db.countryinfo.find("Name = 'Sealand'")
{
    "GNP": 0.6,
    "_id": "00005e2988730000000000000001",
    "Code": "SEA",
    "Name": "Sealand",
    "IndepYear": 1967,
    "geography": {
        "Region": "British Islands",
        "Continent": "Europe",
        "SurfaceArea": 193
    },
    "government": {
        "HeadOfState": "Michael Bates",
        "GovernmentForm": "Monarchy"
    },
    "demographics": {
        "Population": 999,
        "LifeExpectancy": 999
    }
}
1 document in set (0.0008 sec)

• MongoDB：db.countryinfo.update()

world_x@bj79> db.countryinfo.update(
...     {"Name":"Sealand"},
...     {"$set":{"demographics.LifeExpectancy":"999","demographics.Population":"999"}}
... )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

world_x@bj79> db.countryinfo.find({ "Name" : "Sealand" }).pretty()
{
        "_id" : ObjectId("5e535401e034a078264a0bdf"),
        "GNP" : 0.6,
        "IndepYear" : 1967,
        "Name" : "Sealand",
        "Code" : "SEA",
        "demographics" : {
                "LifeExpectancy" : "999",
                "Population" : "999"
        },
        "geography" : {
                "Continent" : "Europe",
                "Region" : "British Islands",
                "SurfaceArea" : 193
        },
        "government" : {
                "GovernmentForm" : "Monarchy",
                "HeadOfState" : "Michael Bates"
        }
}

4. 数据删除：

• MySQL Shell：

  db.countryinfo.remove（）

MySQL 8.0.19 world_x JS > db.countryinfo.remove("Name = 'Sealand'")
Query OK, 1 item affected (0.0060 sec)
MySQL 8.0.19 world_x JS >  db.countryinfo.find("Name = 'Sealand'")
Empty set (0.0008 sec)

• MongoDB：

  db.countryinfo.remove（）

world_x@bj79> db.countryinfo.remove({ "Name" : "Sealand" }) 
WriteResult({ "nRemoved" : 1 })
world_x@bj79> db.countryinfo.find({ "Name" : "Sealand" }).pretty()
world_x@bj79> 

X DevAPI还可以用来处理关系型表，比如innodb建立的普通表。

# 查看当前数据所有的关系型表：
mysql-js> db.getTables()
{
    "city": <Table:city>,
    "country": <Table:country>,
    "countrylanguage": <Table:countrylanguage>
}

# 插入数据：
db.city.insert("ID", "Name", "CountryCode", "District", "Info").values(
None, "Olympia", "USA", "Washington", '{"Population": 5000}')

# 查询数据：
db.city.select(["Name", "CountryCode"]).where("Name like 'Z%'")

+-----------------+-------------+
| Name            | CountryCode |
+-----------------+-------------+
| Zaanstad        | NLD         |
| Zoetermeer      | NLD         |
| Zwolle          | NLD         |
| Zenica          | BIH         |
| Zagazig         | EGY         |
| Zaragoza        | ESP         |
...
| Zeleznogorsk    | RUS         |
| Zukovski        | RUS         |
| Zeleznogorsk    | RUS         |
+-----------------+-------------+
59 rows in set (0.0019 sec)

# 修改数据
db.city.update().set("Name", "Beijing").where("Name = 'Peking'")

---

---

3. 性能对比

MySQL中的文档数据，底层仍然是使用innodb存储引擎，而MongoDB以BSON的二进制编码格式表示JSON文档，存储引擎方面的设计也是针对文档数据进行了专门的优化。

这里我自己没有做测试，引用某专家针对一百万个文档做不同操作请求的测试结果，在性能上还是MongoDB有一些优势的。

https://severalnines.com/database-blog/mongodb-vs-mysql-nosql-why-mongo-better

查询与更新对比

数据插入对比

---

目前来看MySQL 在JSON 的支持方面，从功能上看已经十分完备，加上本身"最流行关系型数据库"的长项，各种短板在慢慢补齐中，估计未来完全替换掉MongoDB也不是不可能。

MongoDB目前来看仍具备一定优势：

• 分布式数据库

• 性能目前看还是很高的

• 分片sharding扩展性强

• 查询功能更灵活
  

无论MongoDB还是MySQL，它们都是十分优秀的开源数据库，这些年相爱相杀，你中有我、我中有你，比如MongoDB 4.0开始支持事务，MySQL 也搞了JSON。多年前杀气腾腾的nosql慢慢都支持SQL，Oracle现在也高举NoSQL + SQL = MySQL的大旗，屠龙骚年最终都变成了恶龙，未来如何发展，吃瓜的我们拭目以待吧。

---

    在这个史上最漫长的全民远程办公时期，经过指导的小万能修已经基本可以胜任我的大部分工作了，尤其是下面这个看家本领，他玩儿的贼6。

---

＃历史文章摘要

• GitHub都在用的高可用工具Orch：

    协调器：01基础篇

    协调器：02高可用方案VIP篇

    协调器：03高可用方案ProxySQL篇

    协调器：04高可用方式部署

• Percona全力打造的监控平台PMM：

    监控利器PMM2.0X GA版本发布！

    PMM监控的重新配置

    PMM的Ansible部署与重点指标

    在PMM中添加Redis和ES