查看MySQL 5.7 内部文档存储

最新推荐文章于 2022-01-29 14:25:27 发布

瞠目结舌2

最新推荐文章于 2022-01-29 14:25:27 发布

阅读量444

点赞数

文章标签：数据库文档存储 MySQL

本文链接：https://blog.csdn.net/qq_34043987/article/details/80123484

版权

MySQL5.7.12是一个重要的新版本，因为它包含相当多的新特性：

文档存储和“MongoDB”像NoSQL界面连接到JSON存储
Protocol X/X插件，它可用于异步查询
New MySQL shell

旧的MySQL 5.7版本已经有一个JSON数据类型，和一个能够创建虚拟列的索引。新文档存储的功能是基于JSON数据类型的。那么，什么是文档存储呢？它是一个附加在MySQL表上的JSON字段。接下来，让我们深入了解它，看看它是如何工作的。

首先，使用X插件并安装MySQL；
其次，登录shell：

mysqlsh--uri root@localhost

然后，运行命令（在JavaScript模式中，可以切换到SQL或Python）：

mysqlsh--uri root@localhost

Creating anXSession toroot@localhost:33060

Enter password:

No defaultschema selected.

Welcome toMySQL Shell1.0.3Development Preview

Copyright(c)2016,Oracle and/orits affiliates.All rights reserved.

Oracle isaregistered trademark of Oracle Corporation and/orits

affiliates.Other names may be trademarks of their respective

owners.

Type'help','h'or'?'forhelp.

Currently inJavaScript mode.Usesql toswitchtoSQL mode andexecute queries.

mysql-js>db=session.getSchema('world_x')<Schema:world_x>

mysql-js>db.getCollections()

"CountryInfo":<Collection:CountryInfo>

现在，如何要使文档存储的集合不同于一个正常的表？为了找到答案，我们连接到了一个正常的MySQL：

mysqlworld_x

Readingtableinformationforcompletionoftableandcolumnnames

Youcanturnoffthisfeaturetogetaquickerstartupwith-A

WelcometotheMySQLmonitor.Commandsendwith;org.

YourMySQLconnectionidis2396

Serverversion:5.7.12MySQLCommunityServer(GPL)

Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheirrespective

owners.

Type'help;'or'h'forhelp.Type'c'toclearthecurrentinputstatement.

mysql>showcreatetableCountryInfo

***************************1.row***************************

Table:CountryInfo

CreateTable:CREATETABLE`CountryInfo`(

`doc`jsonDEFAULTNULL,

`_id`varchar(32)GENERATEDALWAYSAS(json_unquote(json_extract(`doc`,'$._id')))STOREDNOT NULL,

PRIMARY KEY(`_id`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8

1rowinset(0.00sec)

mysql>showtables;

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

|Tables_in_world_x|

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

|City|

|Country|

|CountryInfo|

|CountryLanguage|

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

4rowsinset(0.00sec)

所以文档存储实际上是一个InnoDB表与一个字段：doc json+主键，这是一个生成的列。

我们也可以看到，在world_x数据库中有四个表，但db.getCollections()只显示了一个。MySQL是如何区分一个“正常”表和“文档存储”表？为了找到答案，我们可以启用通用查询日志，看到正在执行的查询︰

$mysql-e'set global general_log=1'

$tail/var/log/general.log

2016-05-17T20:53:12.772114Z186Query  SELECT table_name,COUNT(table_name)cFROM information_schema.columnsWHERE((column_name='doc'anddata_type='json')OR(column_name='_id'andgeneration_expression='json_unquote(json_extract(`doc`,''$._id''))'))ANDtable_schema='world_x'GROUP BY table_name HAVINGc=2

2016-05-17T20:53:12.773834Z186Query  SHOW FULL TABLES FROM`world_x

正如你所看到的，每个表都有一个特定的结构(doc JSON或者特定generation_expression)。它被认为是一个JSON存储。现在，MySQL是如何转化成.find 或 .add来构造实际的MySQL查询？让我们运行一个示例查询：

mysql-js>db.getCollection("CountryInfo").find('Name= "United States"').limit(1)

"GNP":8510700,

"IndepYear":1776,

"Name":"United States",

"_id":"USA",

"demographics":{

"LifeExpectancy":77.0999984741211,

"Population":278357000

"geography":{

"Continent":"North America",

"Region":"North America",

"SurfaceArea":9363520

"government":{

"GovernmentForm":"Federal Republic",

"HeadOfState":"George W. Bush",

"HeadOfState_title":"President"

1documentinset(0.02sec)

现在，再看看慢速查询日志:

2016-05-17T21:02:21.213899Z186Query  SELECT doc FROM`world_x`.`CountryInfo`WHERE(JSON_EXTRACT(doc,'$.Name')='United States')LIMIT1

我们可以验证 MySQL 转换到 SQL 的所有文档存储命令。这也意味着它是现有 MySQL 存储级别在与其他存储引擎工作时100%透明。让我们确认，只是为了好玩而已︰

mysql>alter table CountryInfo engine=MyISAM;

Query OK,239rows affected(0.06sec)

Records:239Duplicates:0Warnings:0

mysql-js>db.getCollection("CountryInfo").find('Name= "United States"').limit(1)

"GNP":8510700,

"IndepYear":1776,

"Name":"United States",

"_id":"USA",

"demographics":{

"LifeExpectancy":77.0999984741211,

"Population":278357000

"geography":{

"Continent":"North America",

"Region":"North America",

"SurfaceArea":9363520

"government":{

"GovernmentForm":"Federal Republic",

"HeadOfState":"George W. Bush",

"HeadOfState_title":"President"

1document inset(0.00sec)

2016-05-17T21:09:21.074726Z2399Query  alter table CountryInfo engine=MyISAM

2016-05-17T21:09:41.037575Z2399Quit

2016-05-17T21:09:43.014209Z186Query  SELECT doc FROM`world_x`.`CountryInfo`WHERE(JSON_EXTRACT(doc,'$.Name')='United States')LIMIT1

现在，性能如何？我们可以通过简单地将SQL查询和运行得到解释：

mysql>explain SELECT doc FROM`world_x`.`CountryInfo`WHERE(JSON_EXTRACT(doc,'$.Name')='United States')LIMIT1

***************************1.row***************************

id:1

select_type:SIMPLE

table:CountryInfo

partitions:NULL

type:ALL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:239

filtered:100.00

Extra:Using where

1row inset,1warning(0.00sec)

这看起来它好像没有使用索引。这是因为它没有索引名称。我们可以添加一个吗?当然，我们可以添加一个虚拟列，然后来索引它：

mysql>altertableCountryInfoaddcolumnNamevarchar(255)

->GENERATEDALWAYSAS(json_unquote(json_extract(`doc`,'$.Name')))VIRTUAL;

QueryOK,0rowsaffected(0.12sec)

Records:0Duplicates:0Warnings:0

mysql>altertableCountryInfoaddkey(Name);

QueryOK,0rowsaffected(0.02sec)

Records:0Duplicates:0Warnings:0

mysql>explainSELECTdocFROM`world_x`.`CountryInfo`WHERE(JSON_EXTRACT(doc,'$.Name')='United States')LIMIT1

***************************1.row***************************

id:1

select_type:SIMPLE

table:CountryInfo

partitions:NULL

type:ref

possible_keys:name

key:name

key_len:768

ref:const

rows:1

filtered:100.00

Extra:NULL

1rowinset,1warning(0.00sec)

真的很酷！我们添加了一个索引，现在我们就开始使用它。注意，我们不必参考新领域，放心，MySQL的优化器是足够聪明的（JSON_EXTRACT（DOC，’$.Name’）=’ 美国 ’ 在虚拟列索引扫描。

但是请注意：JSON属性是需要区分大小写的。如果你要使用(doc,’$.name') ,而不是(doc,'$.Name’)，它是不会产生错误，但只会打破搜索和查询寻找“名称”，它将返回到0行。

最后，如果你仔细观察db.getCollection的输出(“CountryInfo”).find(‘Name = “美国”’).limit(1)，你得注意到数据库已经过时的信息：

"government":{

"GovernmentForm":"Federal Republic",

"HeadOfState":"George W. Bush",

"HeadOfState_title":"President"

让我们把”乔治•布什”更改为“奥巴马”,将用.modify 子句︰

mysql-js>db.CountryInfo.modify("Name = 'United States'").set("government.HeadOfState","Barack Obama");

Query OK,1item affected(0.02sec)

mysql-js>db.CountryInfo.find('Name= "United States"')

"GNP":8510700,

"IndepYear":1776,

"Name":"United States",

"_id":"USA",

"demographics":{

"LifeExpectancy":77.0999984741211,

"Population":278357000

"geography":{

"Continent":"North America",

"Region":"North America",

"SurfaceArea":9363520

"government":{

"GovernmentForm":"Federal Republic",

"HeadOfState":"Barack Obama",

"HeadOfState_title":"President"

1documentinset(0.00sec)

总结

文件存储是一个有趣的概念，也是目前较好的MySQL JSON功能插件。在某些情况下，使用新的.find/.add/.modify 方法可方便的代替原始的SQL语句。

有些人可能会问，“为什么你要在数据库内部使用JSON来进行文档存储和存储信息？“在 JSON 中存储的数据，在某些特定的情况下是非常有用的，例如︰

你已经有一个JSON，并且需要存储。那么，使用JSON数据类型将更方便、更有效。
你有一个灵活的架构，以典型的互联网为例，一些传感器可能只发送温度数据，有些可能还会发送温度、湿度、光等（但光信息只记录在一天）。如果一个新的传感器开始发送新的数据类型时，它会以JSON格式存储，这样可以更加方便的让你无需提前声明，也不必运行“alter table”。

原文：Looking Inside the MySQL 5.7 Document Store