10月19日にMySQL5.7がGAになったので、新規追加されたJSONデータ型の確認を行いました。
JSONはXMLと同じように、スマートフォンアプリ用のAPIやB2BやB2C連携でJSON APIが多く使われているようです。

基本的な動作検証
1. 基本的な構文バリデーション機能
2. JSONデータ型とTEXT型のパフォーマンス差
3. JSONとGenerated Columnの連携によるインデックス利用とパフォーマンス
4. JSONドキュメントの部分アップデート

ちなみに、ザックリとGoogleで検索してみると以下のように多くのサイトがJSON用のAPIを提供していました。
Twitter
https://dev.twitter.com/rest/reference/get/statuses/user_timeline
原子力規制委員会
http://radioactivity.nsr.go.jp/data/ja/real/area_24000/2401_trend.json
Google API
https://storage.googleapis.com/maps-devrel/google.json
ぐるナビ
http://api.gnavi.co.jp/api/tools/?apitype=ver1_RestSearchAPI
シカゴ市
https://data.cityofchicago.org/resource/alternative-fuel-locations.json?$limit=100&$offset=50

1. 基本的な構文バリデーション機能

検証テーブル (TEXT型)

CREATE TABLE `employees_txt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

検証テーブル (JSON型)

CREATE TABLE `employees_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

それぞれのテーブルに、構文ミスしたJSONデータを入れてみると以下のようになる。
JSONデータ型はINSERT時のバリデーションが正常に効いている。


root@localhost [NEW57]> INSERT INTO NEW57.employees_txt(data) VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0.00 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_txt(data) VALUES ('{"id": 2, "name": "Joe"');
Query OK, 1 row affected (0.01 sec)

root@localhost [NEW57]> SELECT * FROM NEW57.employees_txt;
+----+---------------------------+
| id | data                      |
+----+---------------------------+
|  1 | {"id": 1, "name": "Jane"} |
|  2 | {"id": 2, "name": "Joe"   |
+----+---------------------------+
2 rows in set (0.00 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_json(data) VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0.01 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_json(data) VALUES ('{"id": 2, "name": "Joe"');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 23 in value (or column) '{"id": 2, "name": "Joe"'.
root@localhost [NEW57]> SELECT * FROM NEW57.employees_json;
+----+---------------------------+
| id | data                      |
+----+---------------------------+
|  1 | {"id": 1, "name": "Jane"} |
+----+---------------------------+
1 row in set (0.00 sec)

root@localhost [NEW57]> 

2. JSONデータ型とTEXT型のパフォーマンス差
セミナーでも実施しましたが、以下のようなシンプルなテーブルに対して、
ダウンロードしてきた、20万6千件のJSONデータをインサートして参照レスポンス比較。

それぞれの参照クエリにて同じコマンドと同じデータでレスポンス比較
SELECT distinct json_extract(feature,’$.type’) as feature FROM NEW57.features_txt;
SELECT distinct json_extract(feature,’$.type’) as feature FROM NEW57.features_json;

結果としては、JSON型の方がTEXT型に比べて圧倒的に早い事が確認出来る。

[root@misc01 SOD2015]# ./json_and_text_without_index.sh 
INDEXの無いテーブルに対するSELECTを、TEXT型とJSONデータ型で比較します。
SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル

【TEXT型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m9.724s
user    0m0.005s
sys     0m0.002s

【JSON型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m1.506s
user    0m0.004s
sys     0m0.004s
[root@misc01 SOD2015]# 

3. JSONとGenerated Columnの連携によるインデックス利用とパフォーマンス
JSONドキュメントのオブジェクトの中からSTREET名の部分からデータを抽出して列を作成しIndexを付与。

列:      json_extract(feature,’$.properties.STREET’))
インデックス: KEY `feature_street` (`feature_street`)

 

JSONデータ型のサンプルを入れたテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
            id: 12250
       feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
  feature_type: "Feature"
feature_street: "MARKET"
Press [Enter] key to resume.

JSONデータ型とGenerated Columnを利用したテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: features
Create Table: CREATE TABLE `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.type')) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.properties.STREET')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `feature_type` (`feature_type`),
  KEY `feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4
Press [Enter] key to resume.

JSONドキュメントに対して、INDEX検索が利用出来るか確認 -> where json_extract(feature,'$.properties.STREET') = '"MARKET"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: feature_street
          key: feature_street
      key_len: 123
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL
[root@misc01 SOD2015]# 

4. JSONドキュメントの部分アップデート
JSONドキュメントの一部をUPDATEする場合の処理方法確認。
TEXT型だと、全ての列データを入れ替える必要がありますが、JSONだと一部のみ変更可能です。

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+------------------------------------------------------------------------+---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}| 50000   |
+----+------------------------------------------------------------------------+---------+

update T_JSON_DOC set T_JSON_DOC.body = JSON_REPLACE(body,"$.price",15000) 
where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+----------------------------------------------------------------------- +---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "冷蔵庫", "price": 15000, "Conditions": ["NEW", 2015]}| 15000   |
+----+------------------------------------------------------------------------+---------+

文字入れ替えに便利なJSONファンクション(例)
JSON_SET() 既存の値を置き換え、存在しない値を追加
JSON_INSERT() 既存の値を置き換えずに値を挿入
JSON_REPLACE() 既存の値のみを置き換えます

参照:
11.6 The JSON Data Type
https://dev.mysql.com/doc/refman/5.7/en/json.html

MySQL5.7セミナー資料
https://www-jp.mysql.com/news-and-events/seminar/downloads.html


MySQL5.7において、JSON(JavaScript Object Notation)がサポートされていて、
様々な言語やアプリ間で、より簡単にデータの受け渡しが行えるようになりました。
ドキュメントをJSONフォーマットでデータベースに保存して、
様々なアプリケーションから呼び出して利用出来るなど、
汎用性と運用面での活用が出来るようになっています。

1) ネイティブのJSONデータ型
効率的なデータ処理と保管にネイティブ内部バイナリ形式をサポート。
2) 組み込みJSONファンクション
効率よくドキュメントを保存,検索,更新,操作する事を可能にします。
3) JSON コンパレーター
文書データを容易にSQLクエリと統合することが可能
4) Generated Columnsを利用し、ドキュメントにインデックスを利用する事が可能。
新しいアナライザーは,自動的に利用可能で最適な機能インデックスを利用。

ファンクションの詳細は、こちらを参照してください。
12.16 JSON Functions

検証バージョン


root@localhost [USER01]> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.8-rc-log |
+--------------+
1 row in set (0.00 sec)

検証用テーブル


root@localhost [USER01]> show create table T_JSON\G
*************************** 1. row ***************************
       Table: T_JSON
Create Table: CREATE TABLE `T_JSON` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `body` json DEFAULT NULL,
  `WithTax` decimal(10,2) GENERATED ALWAYS AS (json_extract(body,'$.price')*1.08) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_total_cost_v` (`WithTax`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [USER01]> 

※WithTaxは、MySQL5.7から利用可能なGenerated Columnsで作成して、
 文書内のデータからインデックスを作成出来るようにしています。
 ここでは、文書からpriceを抜き出して、TAXを含むコストをベースにインデックスを作成しています。

テストデータ


root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":1,"name":"自転車","price":10000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.15 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":2,"name":"TV","price":30000,"Conditions":["USED",2013]}');
Query OK, 1 row affected (0.01 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":3,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.00 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":4,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.00 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":5,"name":"自転車","price":25000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.01 sec)

<SNIP>

JSONデータ型で作成した列に、データをINSERTする時にフォーマットが正しいかバリデーションしてくれます。
間違えていると以下のようにエラーになります。


root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":6,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 66 in value (or column) '{"id":6,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]'.
root@localhost [USER01]> 

データの参照


root@localhost [USER01]> select * from T_JSON;
+----+-----------------------------------------------------------------------------+----------+
| id | body                                                                        | WithTax  |
+----+-----------------------------------------------------------------------------+----------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}    | 10800.00 |
|  2 | {"id": 2, "name": "TV", "price": 30000, "Conditions": ["USED", 2013]}       | 32400.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}    | 54000.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}    | 54000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}    | 27000.00 |
+----+-----------------------------------------------------------------------------+----------+
5 rows in set (0.01 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "自転車"                    |
| "TV"                        |
| "冷蔵庫"                    |
| "冷蔵庫"                    |
| "自転車"                    |
+-----------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> 

select

該当のデータを含むデータの抽出例 (json_search())等。


root@localhost [USER01]> SELECT json_extract(body,'$.name')="自転車" FROM T_JSON;
+-----------------------------------------+
| json_extract(body,'$.name')="自転車"    |
+-----------------------------------------+
|                                       1 |
|                                       0 |
|                                       0 |
|                                       0 |
|                                       1 |
+-----------------------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> SELECT id,json_search(body,'one','%自転車%') FROM T_JSON;
+----+---------------------------------------+
| id | json_search(body,'one','%自転車%')    |
+----+---------------------------------------+
|  1 | "$.name"                              |
|  2 | NULL                                  |
|  3 | NULL                                  |
|  4 | NULL                                  |
|  5 | "$.name"                              |
+----+---------------------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') 
    -> FROM T_JSON where json_extract(body,'$.name') = '自転車';
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "自転車"                    |
| "自転車"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> 

JONS_WHERE

Generated Columnにインデックスを張ってあるので、インデックスを使用したドキュメントの検索の確認。


root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON where json_extract(body,'$.price') > 40000;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "冷蔵庫"                    |
| "冷蔵庫"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON where WithTax > 40000;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "冷蔵庫"                    |
| "冷蔵庫"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> explain SELECT json_extract(body,'$.name') FROM T_JSON where json_extract(body,'$.price') > 40000;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | T_JSON | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [USER01]> explain SELECT json_extract(body,'$.name') FROM T_JSON where WithTax > 40000;
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | T_JSON | NULL       | range | idx_total_cost_v | idx_total_cost_v | 6       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [USER01]> 

index

【アップデート】

MySQL5.7 JSONデータ型とTEXTデータ型の比較

JSONデータとGenerated_Columnを使う場合の考慮事項

MySQL JSONデータ型とJSON関数の復習

参考:
12.16 JSON Functions
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

JSON Labs Release: Effective Functional Indexes in InnoDB
http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/

MySQL and PostgreSQL JSON functions: do they differ much?
https://blogs.oracle.com/svetasmirnova/entry/mysql_and_postgresql_json_functions

JSON Labs Release: Effective Functional Indexes in InnoDB
http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/


以下のテーブルはi1 char(10)の列に `i1` (`i1`(3))と文字数を3文字に制限したインデックスを
作成してある。最初の3文字でデータが比較出来るようなSELECT文を実行した場合
参照するIndexのデータ量も少なくパフォーマンスが向上する。
但し、対外は色々なアプリケーションから一つのテーブルを参照する事が殆どだと思いますので
あまり利用する頻度は高くありません。列にNOT NULLを常に設定するなどと心がけて対応
する方が実際の運用では全体的なパフォーマンスを向上してくれるかと思います。


CREATE TABLE `fastindex` (
`i1` char(10) NOT NULL default '',
`i2` char(10) NOT NULL default '',
KEY `i1` (`i1`(3)),
KEY `i2` (`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

show_index_char

SELECT i1 FROM fastindex WHERE i1 LIKE 'abc%';
SELECT i2 FROM fastindex WHERE i2 LIKE 'abc%';

以下のEXPLAINではデータが殆ど入っていないので、PLANは変わりませんが
i1のインデックスは最初の3文字のみを利用して作成されているので、上記のSELECT
を実行した場合「WHERE i1 LIKE ‘abc%’;」の方が実行が早い。
index_fast

—————————————————————————————————————-
Indexing cannot only help speed up SELECT queries, but it can also improve
UPDATE and DELETE statements. This is because indexing can help the
server

    find the rows more quickly that should be updated or deleted

.
On the other hand, indexes will slow down UPDATE and DELETE statements because
not only the original data have to be updated but also the indexes.
—————————————————————————————————————-

WHERE, ORDER BY, GROUP BYを利用しない場合やデータの種類が少ない場合は
INDEXを付けてもパフォーマンスが向上する事は殆どありません。INSERT、DELETE、
UPDATEなどの処理でINDEXの更新も入るので遅くなる場合が考えられます。
インデックスが実際に利用されるかどうか常にイメージしながらINDEXを作成するように
心がけると良いかも知れません。

    以下のテーブルはName列に5文字を利用したIndexを付与してあります。


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 '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `idx_City_Name` (`Name`(5))
) ENGINE=MyISAM AUTO_INCREMENT=4083 DEFAULT CHARSET=utf8;

index_on_city

後からインデックスを追加する場合は、以下のようなコマンドを実行すれば良い。
——————————
mysql> CREATE INDEX Name ON City(Name(5));
mysql> ALTER TABLE City ADD INDEX(Name(5));

データのINSERT処理を早くするには?
————————————————————————————————————————-
You could use multiple-row inserts, rather than single-row inserts.
LOAD DATA INFILE will run even faster than any INSERT statement that inserts the
same amount of rows. For InnoDB tables, you could group inserts within a transaction
so that InnoDB will flush changes only when the transaction ends,
rather than after every single INSERT statement. If you’re planning to replace rows using
DELETE and INSERT, you could as well use the MySQL extension REPLACE that runs faster.

For a multiple-row INSERT statementでエラーが起きた場合………….
■ MyISAM では, エラーが起きるまでデータがINSERTされます。
■ InnoDBでは, Rolls backが起きてデータが一見もINSERTされずテーブルはEmptyのままになります。


インデックスをテーブルに対して付与すると、容易にパフォーマンスをしてくれますが
慣れてしまうと、あまり考えずに流れ作業のように作成してしまうので、
少し気にしながら作成した方がいいかもしれません。

一般的にSELECTオペレーションはインデックスを利用すると早くなりますが(Where句やOrderby利用時)、
INSERT,DELETE,UPDATEオペレーションは、インデックスも同時に更新しなければいけないので遅くなります。
UPDATE,DELETEの処理は

    データを早く見つける事が出来るので早くなる事もありますが

データとインデックスの両方を更新しなければいけないので遅くなる場合もあります
where句、Group by, Order byを利用しない場合や列のデータの種類が少ない場合はインデックスを付けても
パフォーマンスの向上は期待できない可能性があります。

「確認用テーブル」 

CREATE TABLE `T_index` (
`i1` char(10) NOT NULL default '',
`i2` char(10) NOT NULL default '',
KEY `i1` (`i1`(3)),
KEY `i2` (`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8


mysql> CREATE TABLE `T_index` (
-> `i1` char(10) NOT NULL default '',
-> `i2` char(10) NOT NULL default '',
-> KEY `i1` (`i1`(3)),
-> KEY `i2` (`i2`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.19 sec)

mysql> desc T_index;
+——-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———+——-+
| i1 | char(10) | NO | MUL | | |
| i2 | char(10) | NO | MUL | | |
+——-+———-+——+—–+———+——-+
2 rows in set (0.00 sec)

t_index

mysql> INSERT INTO T_index(i1,i2) values('abcdefg','abcdefg');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_index(i1,i2) values('hijklmn','hijklmn');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO T_index(i1,i2) values('opqrstu','opqrstu');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_index(i1,i2) values('vwxyz','vwxyz');
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_index;
+———+———+
| i1 | i2 |
+———+———+
| abcdefg | abcdefg |
| hijklmn | hijklmn |
| opqrstu | opqrstu |
| vwxyz | vwxyz |
+———+———+
4 rows in set (0.00 sec)

mysql>

mysql> SELECT i1 FROM T_index WHERE i1 LIKE 'abc%';
+———+
| i1 |
+———+
| abcdefg |
+———+
1 row in set (0.00 sec)

mysql> SELECT i2 FROM T_index WHERE i2 LIKE 'abc%';
+———+
| i2 |
+———+
| abcdefg |
+———+
1 row in set (0.00 sec)

mysql> explain SELECT i1 FROM T_index WHERE i1 LIKE 'abc%';
+—-+————-+———+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | T_index | range | i1 | i1 | 9 | NULL | 1 | Using where |
+—-+————-+———+——-+—————+——+———+——+——+————-+
1 row in set (0.05 sec)

mysql> explain SELECT i2 FROM T_index WHERE i2 LIKE 'abc%';
+—-+————-+———+——-+—————+——+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——-+—————+——+———+——+——+————————–+
| 1 | SIMPLE | T_index | index | i2 | i2 | 30 | NULL | 4 | Using where; Using index |
+—-+————-+———+——-+—————+——+———+——+——+————————–+
1 row in set (0.00 sec)

mysql>

同じデータとインデックスですが、インデックス列に含まれるデータ量が違う事によりプランが変わっています。
key_index

ここでは、CityテーブルのName列の最初の5文字を利用して
インデックスをCreate→Drop→Createしてます。

mysql> create index idx_City_Name on City(Name (5));
Query OK, 4081 rows affected (0.15 sec)
Records: 4081 Duplicates: 0 Warnings: 0

mysql> drop index idx_City_Name on City;
Query OK, 4081 rows affected (0.10 sec)
Records: 4081 Duplicates: 0 Warnings: 0

mysql> alter table City add index idx_City_Name(Name (5));
Query OK, 4081 rows affected (0.14 sec)
Records: 4081 Duplicates: 0 Warnings: 0

index_create

参考サイト:
12.1.2. ALTER TABLE 構文
MySQLクイック・リファレンス


UNIQUE インデックスは、インデックス内の全ての値は明確でなければいけないというような制限を作成します。
既存行とマッチするキー値の新しい行を追加しようとするとエラーが発生します。
全てのエンジンに対して、UNIQUE インデックスは NULL を含む事ができるカラムの複数 NULL 値を許容します


mysql> CREATE TABLE `A1` (
-> `comment` varchar(10) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc A1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CREATE UNIQUE INDEX IDX_UQ_A1 ON A1(comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc A1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql>

unique_idx

※ UNIQUE index will be UNI or PRI if the index does not allow NULL values.
※ MUL if the index does allow NULL values because NULL in a UNIQUE index is a special case:
Multiple NULL values are allowed, unlike any other value.


mysql> desc A1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO A1 VALUES (NULL),(NULL),('data'),('test'),(NULL);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from A1;
+---------+
| comment |
+---------+
| NULL |
| NULL |
| NULL |
| data |
| test |
+---------+
5 rows in set (0.00 sec)

mysql> INSERT INTO A1 VALUES('data');
ERROR 1062 (23000): Duplicate entry 'data' for key 'IDX_UQ_A1'
mysql>

unique_idx_null

参考サイト
12.1.7. CREATE INDEX 構文


PK以外の通常INDEXのレビュー

————————————————————————
インデックス作成時のインデックス名
————————————————————————

ALTER TABLE … ADD INDEX
インデックス名を指定しないで実行すると, MySQLがインデックスの最初のColumを利用して自動的に名前を付ける。

CREATE INDEX
インデックス名を指定しないで作成しようとするとエラーになる。


mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> explain select * from test_numbers where string='yahoo';
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_numbers | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table test_numbers add index idx_tn_string(string);
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0

add_index_0

インデックス追加後の実行プラン
add_index_1


mysql> drop index idx_tn_string on test_numbers;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> create index idx_tn_string on test_numbers(string);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | MUL | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

add_index_2

alter tableにてインデックスのDrop


mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | MUL | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test_numbers drop index idx_tn_string;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

補足: テーブル作成時にインデックスを追加する例


mysql> CREATE TABLE T_WITH_INDX (
-> col1 INT UNSIGNED NOT NULL,
-> col2 CHAR(50) NOT NULL,
-> col3 CHAR(50) NOT NULL,
-> PRIMARY KEY(col1),
-> UNIQUE(col2),
-> INDEX(col3)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc T_WITH_INDX;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1 | int(10) unsigned | NO | PRI | NULL | |
| col2 | char(50) | NO | UNI | NULL | |
| col3 | char(50) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

index_01

同じ内容で以下のようにインデックスを作成する事も可能


mysql> CREATE TABLE T_WITH_INDX(
-> col1 INT UNSIGNED NOT NULL PRIMARY KEY,
-> col2 CHAR(50) NOT NULL UNIQUE,
-> col3 CHAR(50) NOT NULL,
-> INDEX(col3)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc T_WITH_INDX;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1 | int(10) unsigned | NO | PRI | NULL | |
| col2 | char(50) | NO | UNI | NULL | |
| col3 | char(50) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

DROP INDEXではまとめて複数のインデックスをDROPする事は出来ません。
纏めて削除したい場合は、ALTER TABLE … DROP INDEXを利用する。


インデックスはDBのパフォーマンスチューニングには欠かせないものだが、その中でも中心的な
キーとなるPKを改めて確認してみました。

PRIMARY KEY は、すべてのキーカラムが NOT NULL として定義されていなければならないユニーク KEY である。
NOT NULL として明示的に定義されていないと、暗黙的(かつ自動的)に NOT NULL に設定される。
MySQL において、このキーは PRIMARY と呼ばれる。個々のテーブルは PRIMARY KEY を 1 つだけ持つことが出来る。
PRIMARY KEY がない場合に、何らかのアプリケーションがテーブルの PRIMARY KEY を要求すると、MySQL
では、NULL カラムをまったく持たない最初の UNIQUE キーが PRIMARY KEYとして返される。

複合インデックスを PRIMARY KEY にすることもできる。しかし、カラムの仕様で PRIMARY KEY キー属性
を使用して複合インデックスは作成することはできない。そのようにしても、単一のカラムがプライマリとして
マークされるにすぎない。 この場合、別に PRIMARY KEY(index_col_name, …) 節を使用する必要がある。

    インデックス確認方法

mysql> show index from テーブル名\G
mysql> desc テーブル名;

show_index

PKの追加


mysql> CREATE TABLE `Y2008` (
-> `comment` varchar(50) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table Y2008 add
-> constraint PK_Y2008 primary key (comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

pk

PKの削除


mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> drop index `PRIMARY` on Y2008;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

drop_index1


mysql> alter table Y2008 add constraint PK_Y2008 primary key (comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table Y2008 drop primary key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

drop_index2

——————————————————
テーブルに付与されたインデックス確認コマンド
——————————————————
mysql> show create table テーブル名;
mysql> show index from テーブル名;

table_show

——————————————————
テーブル作成時に追加する例
——————————————————

其の①
CREATE TABLE TABLE_ID(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(10)
);

其の②

CREATE TABLE TBL1 (col1 INT NOT NULL PRIMARY KEY);

其の③

CREATE TABLE TBL2 (
col1 INT NOT NULL,
col2 INT NOT NULL,
PRIMARY KEY (col1, col2)
);

index3


特定テーブルにあるインデックスをCacheしてパフォーマンスアップする方法

TABLE007、TABLE008にあるインデックスをTABLE_IDX_CACHEにロードしてみる。
サイズは2MBとしてみる。

テーブル構造

index_cache

1) TABLE_IDX_CACHEという名前のキーCACHEを作成

mysql> SET GLOBAL TABLE_IDX_CACHE.key_buffer_size = 2 * 1024 * 1024;
Query OK, 0 rows affected (0.01 sec)

2) 作成したCACHEにテーブルを割り当てる

mysql> CACHE INDEX animals,animals02 IN TABLE_IDX_CACHE;
+----------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------------------+----------+----------+
| DB02.animals | assign_to_keycache | status | OK |
| DB02.animals02 | assign_to_keycache | status | OK |
+----------------+--------------------+----------+----------+
2 rows in set (0.00 sec)

mysql>

index_cache2

3) INDEXを作成したKEY CACHEにロードする。


mysql> LOAD INDEX INTO CACHE animals,animals02;
+----------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------------+----------+----------+
| DB02.animals | preload_keys | status | OK |
| DB02.animals02 | preload_keys | status | OK |
+----------------+--------------+----------+----------+
2 rows in set (0.00 sec)

index_cache3

—————— 上記の方法で特定テーブルのIndexをKey Cacheにロードは完了 ———–

サーバー起動時に常に上記のインデックスをKey Cacheにロードしておく為には
以下の方法でロードする事が出来る。
——————————————————————————————————

(1) init_cache.sqlの作成 (インデックスロードスクリプト)


SET GLOBAL TABLE_IDX_CACHE.key_buffer_size = 2 * 1024 * 1024;
CACHE INDEX animals,animals02 IN TABLE_IDX_CACHE;
LOAD INDEX INTO CACHE animals,animals02;

(2)mysqlを起動するときに次のようにスクリプトを指定して起動させる

[root@colinux ~]# mysqld --init-file=init_cache.sql

※my.cnfに記入してもOK。