MySQLのJSON型を使ってみる

RESULT.md

バージョン

• MySQL: 8.0.18

テーブル

mysql> show create table users;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `attributes` json NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=18614313 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show indexes from users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY  |            1 | id          | A         |    16164395 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_name |            1 | name        | A         |     6983301 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

データ量

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
| 18600000 |
+----------+
1 row in set (2.56 sec)

サンプル

mysql> select * from users limit 5 \G;
*************************** 1. row ***************************
        id: 1
      name: Maximo Rippin
attributes: {"name": "Maximo Rippin", "email": "karl.weber@macgyver.co", "address_city": "Port Kaitlynborough", "address_full": "Apt. 184 2138 Lakia Avenue, Pabloburgh, ME 95232-2912", "address_state": "Mississippi", "address_country": "Sierra Leone", "address_zipcode": "86864-3676"}
*************************** 2. row ***************************
        id: 2
      name: Marleen Fritsch V
attributes: {"name": "Marleen Fritsch V", "email": "ray_aufderhar@reichel.com", "address_city": "Feestberg", "address_full": "502 Emard Springs, Leorabury, IL 86642-9411", "address_state": "Washington", "address_country": "Turks and Caicos Islands", "address_zipcode": "62773"}
*************************** 3. row ***************************
        id: 3
      name: Rep. Tarra Schoen
attributes: {"name": "Rep. Tarra Schoen", "email": "jame_wehner@lind-fadel.name", "address_city": "Lake Denisha", "address_full": "98029 Fabian Pass, Vernonchester, TN 95786-0194", "address_state": "New Mexico", "address_country": "Saint Barthelemy", "address_zipcode": "72789-6911"}
*************************** 4. row ***************************
        id: 4
      name: Layla Dare Esq.
attributes: {"name": "Layla Dare Esq.", "email": "josefine@pacocha.co", "address_city": "North Rudolphport", "address_full": "88530 Honey Branch, O'Connerbury, IL 72541-8761", "address_state": "South Dakota", "address_country": "Namibia", "address_zipcode": "70574-8842"}
*************************** 5. row ***************************
        id: 5
      name: Minh Gulgowski
attributes: {"name": "Minh Gulgowski", "email": "hank@ritchie.co", "address_city": "Reillyfort", "address_full": "14771 Pacocha Point, Faychester, RI 12364", "address_state": "Indiana", "address_country": "Sao Tome and Principe", "address_zipcode": "14398-0477"}
5 rows in set (0.00 sec)

ERROR: 
No query specified

INDEXが使える時（絞り込み）

インデックスが張られた name で検索し、ソート対象のレコードが少ないので十分速い。

mysql> select id, name, `attributes`->"$.address_zipcode" as zipcode from users where name = 'Aaron Abbott' order by `attributes`->"$.address_zipcode" asc limit 1;
+---------+--------------+--------------+
| id      | name         | zipcode      |
+---------+--------------+--------------+
| 4132842 | Aaron Abbott | "07298-3072" |
+---------+--------------+--------------+
1 row in set (0.01 sec)

INDEXが使えない時（絞り込み）

インデックスが張られていないJSONの値を比較しているので遅い。

mysql> select id, name, `attributes`->"$.address_zipcode" as zipcode from users where `attributes`->"$.name" = 'Aaron Abbott' order by `attributes`->"$.address_zipcode" asc limit 1;
+---------+--------------+--------------+
| id      | name         | zipcode      |
+---------+--------------+--------------+
| 4132842 | Aaron Abbott | "07298-3072" |
+---------+--------------+--------------+
1 row in set (18.23 sec)