MySQL 5.7.12 で X Plugin がリリースされてから、 MySQL Server を JSON形式の Document Store として扱えるようになりました。
MySQL の Document Store に関連する弊社の記事として、以下の記事がございますので、興味があれば、ご一読下さい。
また、MySQL 5.7.8 から使用可能となったJSONデータ型についても以下の記事がございますので、未読の方は是非、一読頂ければと思います。
今回は執筆時点の最新バージョン（8.0.18）の MySQL Server と MySQL Shell を使用して、JSONドキュメントと戯れてみたいと思います。
環境の準備
MySQL Server と MySQL Shell は同一サーバにバージョン 8.0.18 がインストール済みであるものとします。
また、MySQL Server 5.7 では、インストール後に X Plugin を有効にする必要がありましたが、MySQL Server 8.0 からはデフォルトで有効になっています。
確認用のデータとして、公式サイトで提供されている Example Databases のうち、「world_x」データベースにJSONデータが含まれているので、これを使用して確認したいと思います。
まずは、「world_x」のダンプファイルをダウンロードします。
|
1 2 3 4 |
# curl -LO https://downloads.mysql.com/docs/world_x-db.tar.gz # tar xzf world_x-db.tar.gz # ls world_x-db README.txt world_x.sql |
MySQL Shell から、ダンプファイルをインポートするデータベースを作成します。
データベース名も「world_x」としています。
|
1 2 3 |
# mysqlsh root@localhost --sql -e "CREATE DATABASE world_x;" Please provide the password for 'root@localhost': ************** Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): Y |
作成した「world_x」データベースにダウンロードしたダンプファイルをインポートします。
|
1 |
# mysqlsh root@localhost/world_x --sql < world_x-db/world_x.sql |
データベース・テーブル確認
MySQL Shell のSQLモードでログインし、テーブルを確認します。
|
1 2 3 4 5 6 7 8 9 10 11 |
# mysqlsh root@localhost/world_x --sql MySQL Shell SQL > SHOW TABLES; +-------------------+ | Tables_in_world_x | +-------------------+ | city | | country | | countryinfo | | countrylanguage | +-------------------+ 4 rows in set (0.0014 sec) |
MySQL Shell のログイン後のプロンプト表示は、本来
MySQL localhost:33060+ ssl world_x SQL > のように表示されますが、長いので MySQL Shell (SQL or JS) > のように表記しております。
４つのテーブルが作成されていますが、これらのうちJSONデータを保持するテーブルは「city」「countryinfo」の２つとなります。
「city」テーブルは従来の各カラムを定義した中にJSON型のカラムを設けたテーブルとなっています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
MySQL Shell SQL > SHOW CREATE TABLE city\G *************************** 1. row *************************** Table: city Create Table: CREATE TABLE `city` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Info` json DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.0004 sec) MySQL Shell SQL > SELECT * FROM city WHERE Name = 'Tokyo'; +------+-------+-------------+----------+-------------------------+ | ID | Name | CountryCode | District | Info | +------+-------+-------------+----------+-------------------------+ | 1532 | Tokyo | JPN | Tokyo-to | {"Population": 7980230} | +------+-------+-------------+----------+-------------------------+ 1 row in set (0.0042 sec) |
一方、「countryinfo」テーブルは Collection といわれるJSONドキュメントを格納する雛形的なテーブルとなり、Document Store として使用したい場合は一般的にはこの形式を使用する事になるかと思います。
ドキュメントを格納するjson型の「doc」カラムとドキュメントの「_id」フィールドと紐づく仮想列の主キーで構成されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
MySQL Shell SQL > SHOW CREATE TABLE countryinfo\G *************************** 1. row *************************** Table: countryinfo Create Table: CREATE TABLE `countryinfo` ( `doc` json DEFAULT NULL, `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL, PRIMARY KEY (`_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.0005 sec) MySQL localhost:33060+ ssl world_x SQL > SELECT JSON_PRETTY(doc) FROM countryinfo WHERE JSON_UNQUOTE(JSON_EXTRACT(doc,'$.Name')) = 'Japan'; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | JSON_PRETTY(doc) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "GNP": 3787042, "_id": "JPN", "Name": "Japan", "IndepYear": -660, "geography": { "Region": "Eastern Asia", "Continent": "Asia", "SurfaceArea": 377829 }, "government": { "HeadOfState": "Akihito", "GovernmentForm": "Constitutional Monarchy" }, "demographics": { "Population": 126714000, "LifeExpectancy": 80.69999694824219 } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.0008 sec) |
本記事は、こちらの Collection を MySQL Shell の Javascript モードを使用してNoSQLライクにドキュメント操作してみます。
MySQL Shell で Collection の確認
MySQL Shell でログインし、Collection を確認します。
|
1 2 3 4 5 |
# mysqlsh root@localhost/world_x MySQL Shell JS > db.getCollections() [ <Collection:countryinfo> ] |
「countryinfo」が Collection として存在しているのが確認できます。
ドキュメント操作（検索）
ドキュメントを検索するには find 関数を使用します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
MySQL Shell JS > db.countryinfo.find("Name = 'Japan'") { "GNP": 3787042, "_id": "JPN", "Name": "Japan", "IndepYear": -660, "geography": { "Region": "Eastern Asia", "Continent": "Asia", "SurfaceArea": 377829 }, "government": { "HeadOfState": "Akihito", "GovernmentForm": "Constitutional Monarchy" }, "demographics": { "Population": 126714000, "LifeExpectancy": 80.69999694824219 } } 1 document in set (0.0007 sec) |
取得するフィールドの指定や、指定する値を変数にバインドしたりする事も可能です。
|
1 2 3 4 |
MySQL Shell JS > db.countryinfo.find("Name = :country").fields("geography.Continent").bind("country", "Japan") { "geography.Continent": "Asia" } |
Collection には仮想列の主キー(_id)が存在しますが、主キーを指定する場合は、getOne() で条件指定する事もできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
MySQL Shell JS > db.countryinfo.getOne("JPN") { "GNP": 3787042, "IndepYear": -660, "Name": "Japan", "_id": "JPN", "demographics": { "LifeExpectancy": 80.69999694824219, "Population": 126714000 }, "geography": { "Continent": "Asia", "Region": "Eastern Asia", "SurfaceArea": 377829 }, "government": { "GovernmentForm": "Constitutional Monarchy", "HeadOfState": "Akihito" } } |
また、レコード数をカウントしたい場合は、以下のように非常にシンプルに取得できます。
|
1 2 |
MySQL Shell JS > db.countryinfo.count() 239 |
その他にも、sort や group by の指定等も可能なので、非常に幅広い検索方法を指定する事ができます。
ドキュメント操作（登録）
ドキュメントを登録するには add 関数を使用します。
以下のように既存データにある全てのフィールドを指定しなくても登録する事が可能です。
|
1 2 3 4 5 6 7 8 9 |
MySQL Shell JS > db.countryinfo.add({"_id": "XYZ", "Name": "MyCountry1", "demographics": {"Population": 1}}); MySQL Shell JS > db.countryinfo.getOne('XYZ') { "Name": "MyCountry1", "_id": "XYZ", "demographics": { "Population": 1 } } |
また、既存データにないフィールドを追加する事もできます。
テーブル構造を変更する為にALTER文を実行する必要もなく、Document Store を使用する利点でもあるかと思います。
|
1 2 3 4 5 6 7 8 9 10 11 |
MySQL Shell JS > db.countryinfo.add({"_id": "YZA", "Name": "MyCountry2", "demographics": {"Population": 1}, "Info": "Add Info"}); Query OK, 1 item affected (0.0155 sec) MySQL Shell JS > db.countryinfo.getOne('YZA') { "Info": "Add Info", "Name": "MyCountry2", "_id": "YZA", "demographics": { "Population": 1 } } |
また、主キー(_id)を省略した場合には、自動的に生成された乱数が主キーに設定されます。
|
1 2 3 4 5 6 7 8 9 10 11 |
MySQL Shell JS > db.countryinfo.add({"Name": "MyCountry3", "demographics": {"Population": 1}}); Query OK, 1 item affected (0.0025 sec) MySQL Shell JS > db.countryinfo.find("Name = 'MyCountry3'"); { "_id": "00005de8ad770000000000000002", "Name": "MyCountry3", "demographics": { "Population": 1 } } 1 document in set (0.0007 sec) |
ドキュメント操作（更新）
ドキュメントを更新するには modify 関数を使用します。
先程、登録したレコードの "Name": "MyCountry1" の「demographics.Population」の値を更新してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
MySQL Shell JS > db.countryinfo.modify("Name = 'MyCountry1'").set("demographics.Population", 100) Query OK, 1 item affected (0.0122 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.find("Name = 'MyCountry1'"); { "_id": "XYZ", "Name": "MyCountry1", "demographics": { "Population": 100 } } 1 document in set (0.0008 sec) |
「demographics」フィールドを削除してみます。
|
1 2 3 4 5 6 7 8 9 |
MySQL Shell JS > db.countryinfo.modify("Name = 'MyCountry1'").unset("demographics") Query OK, 1 item affected (0.0158 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.find("Name = 'MyCountry1'"); { "_id": "XYZ", "Name": "MyCountry1" } |
再度、「demographics.Population」フィールドを追加してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
MySQL Shell JS > db.countryinfo.modify("Name = 'MyCountry1'").set("demographics", {Population: 500}) Query OK, 1 item affected (0.0120 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.find("Name = 'MyCountry1'"); { "_id": "XYZ", "Name": "MyCountry1", "demographics": { "Population": 500 } } 1 document in set (0.0007 sec) |
フィールド値の配列を操作することもできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
MySQL Shell JS > db.countryinfo.modify("Name = 'MyCountry1'").set("ArrayField", []) Query OK, 1 item affected (0.0025 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.modify("Name = 'MyCountry1'").arrayAppend("ArrayField", "ONE") Query OK, 1 item affected (0.0043 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.modify("Name = 'MyCountry1'").arrayAppend("ArrayField", "THREE") Query OK, 1 item affected (0.0039 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.modify("Name = 'MyCountry1'").arrayInsert("ArrayField[1]", "TWO") Query OK, 1 item affected (0.0040 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.find("Name = 'MyCountry1'"); { "_id": "XYZ", "Name": "MyCountry1", "ArrayField": [ "ONE", "TWO", "THREE" ], "demographics": { "Population": 500 } } 1 document in set (0.0008 sec) MySQL Shell JS > db.countryinfo.modify("Name = 'MyCountry1'").arrayDelete("ArrayField[0]") Query OK, 1 item affected (0.0107 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.find("Name = 'MyCountry1'"); { "_id": "XYZ", "Name": "MyCountry1", "ArrayField": [ "TWO", "THREE" ], "demographics": { "Population": 500 } } 1 document in set (0.0009 sec) |
ドキュメント操作（削除）
ドキュメントを削除するには remove 関数を使用します。
remove関数の引数に条件を指定して削除します。
|
1 2 3 4 |
MySQL Shell JS > db.countryinfo.remove("Name = 'MyCountry1'") Query OK, 1 item affected (0.0254 sec) MySQL localhost:33060+ ssl world_x JS > db.countryinfo.find("Name = 'MyCountry1'"); Empty set (0.0008 sec) |
フィールドの値でソートして、件数指定して削除したりする事もできます。
以下は「demographics.Population」の値が低い上位５件を削除しています。
|
1 2 |
MySQL Shell JS > db.countryinfo.remove("true").sort("demographics.Population").limit(5) Query OK, 5 items affected (0.0060 sec) |
ドキュメント操作（トランザクション）
ACID にも準拠しており、Document Store として使用した場合にもトランザクションが使用可能です。
トランザクションを発行する際は、sessionオブジェクトを使用します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
MySQL Shell JS > db.session.startTransaction() Query OK, 0 rows affected (0.0003 sec) MySQL Shell JS > db.countryinfo.find("Name = 'Japan'").fields("geography.Continent"); { "geography.Continent": "Asia" } 1 document in set (0.0007 sec) MySQL Shell JS > db.countryinfo.modify("Name = 'Japan'").set("geography.Continent", 'Europe') Query OK, 1 item affected (0.0010 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL Shell JS > db.countryinfo.find("Name = 'Japan'").fields("geography.Continent"); { "geography.Continent": "Europe" } 1 document in set (0.0008 sec) MySQL Shell JS > db.session.rollback() Query OK, 0 rows affected (0.0096 sec) MySQL Shell JS > db.countryinfo.find("Name = 'Japan'").fields("geography.Continent"); { "geography.Continent": "Asia" } 1 document in set (0.0007 sec) |
ドキュメント操作（インデックス）
Collection内のフィールドにインデックスを作成する事もできます。
まずはインデックスの有効性を確認する為に、簡単に100万件レコード存在するJSONファイルを作成します。
|
1 2 3 4 5 6 7 |
# for i in {1..1000000}; do printf "{\"_id\": %d, \"name\": \"smartstyle%d\"}\n" $i $i >> /var/lib/mysql-files/load.json ; done # tail -n 5 /var/lib/mysql-files/load.json {"_id": 999996, "name": "smartstyle999996"} {"_id": 999997, "name": "smartstyle999997"} {"_id": 999998, "name": "smartstyle999998"} {"_id": 999999, "name": "smartstyle999999"} {"_id": 1000000, "name": "smartstyle1000000"} |
MySQL Shell でログインし、作成したJSONファイルをインポートする Collection（smartstyle）を作成します。
|
1 2 3 4 5 6 7 8 |
# mysqlsh root@localhost/world_x MySQL Shell JS > db.createCollection("smartstyle") <Collection:smartstyle> MySQL Shell JS > db.getCollections() [ <Collection:countryinfo>, <Collection:smartstyle> ] |
MySQL Shell 8.0.13 で機能追加された JSON Import Utility で、作成した Collection にJSONファイルをインポートします。
|
1 2 3 4 5 6 |
MySQL Shell JS > util.importJson("/var/lib/mysql-files/load.json", {collection: "smartstyle"}) Importing from file "/var/lib/mysql-files/load.json" to collection `world_x`.`smartstyle` in MySQL Server at localhost:33060 .. 765125.. 1000000.. 1000000 Processed 43.78 MB in 1000000 documents in 29.0002 sec (34.48K documents/s) Total successfully imported documents 1000000 (34.48K documents/s) |
それでは、 name フィールドにインデックスを付与し、name フィールドを条件指定した検索を実行します。
|
1 2 3 4 5 6 7 8 |
MySQL Shell JS > db.smartstyle.createIndex('nameIndex', {"fields": [{"field": "$.name", "type": "CHAR(20)"}]}); Query OK, 0 rows affected (5.7321 sec) MySQL Shell JS > db.smartstyle.find("name = 'smartstyle100'") { "_id": 100, "name": "smartstyle100" } 1 document in set (0.0094 sec) |
実行時間は 0.0094秒 となりました。
MySQL Severの General Query Log を有効にして find 関数実行時に実際に実行されたSQLを確認し、MySQL Shell の SQL モードで EXPLAIN してみました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
MySQL Shell JS > \sql Switching to SQL mode... Commands end with ; MySQL Shell SQL > EXPLAIN SELECT doc FROM `world_x`.`smartstyle` WHERE (JSON_EXTRACT(doc,'$.name') = 'smartstyle100')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: smartstyle partitions: NULL type: ref possible_keys: nameIndex key: nameIndex key_len: 81 ref: const rows: 1 filtered: 100 Extra: Using where 1 row in set, 1 warning (0.0006 sec) Note (code 1003): /* select#1 */ select `world_x`.`smartstyle`.`doc` AS `doc` from `world_x`.`smartstyle` where (`world_x`.`smartstyle`.`$ix_c20_2BAA686C5604372BA51E965E6346198F5072C3D7` = 'smartstyle100') |
付与した nameIndex が使用されていることが確認できますね。
（SQLが整形された旨のメッセージが出力されます。）
また、付与したインデックスを削除して、再度、検索を実行して、検索速度の違いを確認します。
|
1 2 3 4 5 6 7 |
MySQL Shell JS > db.smartstyle.dropIndex('nameIndex'); MySQL localhost:33060+ ssl world_x JS > db.smartstyle.find("name = 'smartstyle100'") { "_id": 100, "name": "smartstyle100" } 1 document in set (1.5289 sec) |
インデックスが存在しない場合は、1.5秒程度の検索時間を要していますが、インデックスを付与した場合は0.01秒未満となっています。
簡単な検証ではありますが、インデックスが有効に作用されている事が分かります。
まとめ
今回は、MySQL Shell で操作を行いましたが、以下のコネクタにも「X DevAPI」が実装されており、同じように NoSQL で JSON ドキュメントの操作が可能です。
- MySQL Connector/Node.js
- MySQL Connector/PHP
- MySQL Connector/Python
- MySQL Connector/J
- MySQL Connector/NET
- MySQL Connector/C++
他のドキュメント指向データベース製品を使用されていて不満を感じられている方や、MySQL Server＋別データベース製品を使用していて、MySQL Server だけで事足りるかもと感じられた方は、是非、お試し頂ければと思います。
これから MySQL がより幅広く使用されていく事を期待したいと思います。
