MySQL v5.7 勉強会/study-mysql-ver-5-7

Transcript

1. ANDPAD
   MySQL v5.7 勉強会
   2022.07.28
   

   View Slide

2. 自己紹介

   • 三谷　智史（@mita2）

   • 仕事その１　とある企業のSRE

   • 仕事その２　ANDPADのデータベース技術顧問

   • ご相談は #dev_dbパフォーマンスチューニング

   • 　

   • http://mita2db.hateblo.jp/

   

   View Slide

3. Aurora v5.7 Compatible になりました

   Aurora v1 → v2 バージョンアップ

   おめでとうございます 👏👏👏

   

   View Slide

4. 本日のゴール

   MySQL v5.7 / Aurora v2 で追加された機能を

   活かして開発できるようになる

   

   View Slide

5. 目次

   1. MySQL / Aurora のバージョンと歴史

   2. MySQL v5.7 の新機能

   3. MySQL v8.0 の紹介

   4. 質疑応答

   時間があれば
   

   View Slide

6. 目次

   1. MySQL / Aurora のバージョンと歴史

   2. MySQL v5.7 の新機能

   3. MySQL v8.0 の紹介

   4. 質疑応答

   

   View Slide

7. MySQL のバージョンの見方

   • X.Y.Z

   • Major version

   • X.Y.Z

   • Minor version

   • 例）v5.7.24

   • Major ver: 5.7, Minor ver: 24

   • SELECT @@version; で取得できる

   

   View Slide

8. MySQL と Auroraのバージョンの対応

   • MySQL v5.6.X = Aurora v1.X.X

   • MySQL v5.7.X = Aurora v2.X.X

   • MySQL v8.0.X = Aurora v3.X.X

   • SELECT @@aurora_version; で取得できる

   

   • マイナーバージョンの１：１の関係はない

   

   View Slide

9. MySQL/Auroraのバージョンとライフサイクル

   • MySQL 5.7 は 2015/10 にリリース

   • データベースのライフサイクルは長い

   • Aurora は MySQL に遅れてリリースされる

   • そのぶん、サポート期限も伸びている

   • Aurora 1.X (compat v5.6) のサポート期限は 2023/02

   ?
   ?
   

   View Slide

10. 目次

    1. MySQL / Aurora のバージョンと歴史

    2. MySQL v5.7 の新機能

    3. MySQL v8.0 の紹介

    4. 質疑応答

    

    View Slide

11. MySQL v5.7 の新機能

    • v5.6 → v5.7 で 150 以上の新機能 が追加された

    • 開発者が知っておくと良いものをピックアップ

    • Server Side Statement Timeout 

    • Performance Schema の強化 と sys Schema

    • Generated Column

    • 全文検索

    • JSON型

    

    View Slide

12. MySQL v5.7 の新機能

    • Server Side Statement Timeout 

    • Performance Schema の強化 と sys Schema

    • Generated Column

    • 全文検索

    • JSON型

    

    View Slide

13. • 機能概要

    • SELECT文にタイムアウトを設定できる

    

    

    

    mysql> SET max_execution_time=1000; -- 1 sec
    mysql> SELECT * FROM bigtable;
    ERROR 3024 (HY000): Query execution was interrupted, maximum statement
    execution time exceeded
    mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM bigtable;
    ERROR 3024 (HY000): Query execution was interrupted, maximum statement
    execution time exceeded
    Server Side Statement Timeout

    【 Optimizer Hint をつかう 】

    【SETで指定する】
    

    View Slide

14. Server Side Statement Timeout

    • Aurora 2.X では、MAX_EXECUTION の Optimizer Hints は未サポート（※）

    

    

    • Active Rercord の optimizer_hints() は効かないため注意

    

    • SET max_execution_time=XXX を使う

    

    User.optimizer_hints("MAX_EXECUTION_TIME(1000)”)
    ※ https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.html#AuroraMySQL.Reference.Hints

    -- 使えない
    mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM bigtable;
    

    View Slide

15. 他のタイムアウト手法との比較

    ミドルウェアやフレームワーク
    レベル
    アプリケーション全体に設定（クエリ単位ではない）
    DB接続ドライバ レベル サポートしているドライバが限られる
    MySQL のサーバサイド
    ステートメントタイムアウト
    (MAX_EXECUTION_TIME)
    特定のクエリに設定可能、ドライバに依存しない
    v5.7
    new!
    

    View Slide

16. 他のMySQLパラメータとの比較

    wait_timeout / interactive_timeout
    アイドル時間が指定の値を超えた場合
    コネクションをサーバサイドから切断する
    無駄なコネクションがDBに溜まるのを防ぐ
    connection_timeout
    クライアントが
    サーバへ接続する際のタイムアウト
    net_read_timeout / net_write_timeout
    サーバがクライアントとの通信に
    対して設定するタイムアウト
    max_execution_time クエリの実行時間に対するタイムアウト
    v5.7
    new!
    

    View Slide

17. MySQL v5.7 の新機能

    • Server Side Statement Timeout 

    • Performance Schema の強化 と sys Schema

    • Generated Column

    • 全文検索

    • JSON型

    

    View Slide

18. Performance_schema と sys スキーマ

    • 機能概要

    • モニタリング機構

    • v5.5 で登場

    • 主にパフォーマンスに関する統計

    • AWS Performance Insight は P_S を

    ベースにしていると思われる（※）

    

    • Aurora v2 ではデフォルトOFF

    • ANDPADではONに変更済み

    ※ https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_PerfInsights.EnableMySQL.html
    mysql> show tables from performance_schema;
    +------------------------------------------------------+
    | Tables_in_performance_schema |
    +------------------------------------------------------+
    | accounts |
    | cond_instances |
    | events_stages_current |
    | events_stages_history |
    | events_stages_history_long |
    | events_stages_summary_by_account_by_event_name |
    | events_stages_summary_by_host_by_event_name |
    | events_stages_summary_by_thread_by_event_name |
    | events_stages_summary_by_user_by_event_name |
    | events_stages_summary_global_by_event_name |
    

    View Slide

19. sys スキーマ

    • Performance_schema を見やすくするためのビューやプロシージャ集

    
 mysql> SHOW CREATE TABLE sys.statement_analysis \G
    *************************** 1. row ***************************
    View: statement_analysis
    Create View:
    CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER
    VIEW `statement_analysis` AS select
    `sys`.`format_statement`(`performance_schema`.`events_statements_summary_by_digest`.`DIGEST_TEXT`) AS
    `query`,`performance_schema`.`events_statements_summary_by_digest`.`SCHEMA_NAME` AS
    `db`,if(((`performance_schema`.`events_statements_summary_by_digest`.`SUM_NO_GOOD_INDEX_USED` > 0) or
    (`performance_schema`.`events_statements_summary_by_digest`.`SUM_NO_INDEX_USED` > 0)),'*','') AS
    `full_scan`,`performance_schema`.`events_statements_summary_by_digest`.`COUNT_STAR` AS
    `exec_count`,`performance_schema`.`events_statements_summary_by_digest`.`SUM_ERRORS` AS
    `err_count`,`performance_schema`.`events_statements_summary_by_digest`.`SUM_WARNINGS` AS
    `warn_count`,`sys`.`format_time`(`performance_schema`.`events_statements_summary_by_digest`.`SUM_TIMER_WAIT`) AS
    `total_latency`,`sys`.`format_time`(`performance_schema`.`events_statements_summary_by_digest`.`MAX_TIMER_WAIT`) AS
    `max_latency`,`sys`.`format_time`(`performance_schema`.`events_statements_summary_by_digest`.`AVG_TIMER_WAIT`) AS
    `avg_latency`,`sys`.`format_time`(`performance_schema`.`events_statements_summary_by_digest`.`SUM_LOCK_TIME`) AS
    `lock_latency`,`performance_schema`.`events_statements_summary_by_digest`.`SUM_ROWS_SENT` AS
    `rows_sent`,round(ifnull((`performance_schema`.`events_statements_summary_by_digest`.`SUM_ROWS_SENT` /
    nullif(`performance_schema`.`events_statements_summary_by_digest`.`COUNT_STAR`,0)),0),0) AS `rows_sent_avg`
    
    

    View Slide

20. Performance_schema と sys スキーマ

    • v5.7で出来るようになったこと

    • sys スキーマがデフォルトでバンドルされた

    • ALTER TABLE の進捗モニター

    • トランザクションレベルの統計（デフォルトではOFF）

    

    View Slide

21. 便利な p_s/sys

    • クエリ分析 sys.statement_analysis

    • 未利用インデックス sys.schema_unused_indexes

    • 冗長インデックス sys.schema_redundant_indexes

    • ALTER TABLE 進捗 モニター

    

    v5.7
    new!
    

    View Slide

22. sys.statement_analysis

    mysql> SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 5\G
    *************************** 1. row ***************************
    query: SELECT `tag_groups` . * FROM ` ... AND `tag_groups` . `role` = ?
    db: production_asp
    full_scan:
    exec_count: 11
    err_count: 0
    warn_count: 0
    total_latency: 10.99 ms
    max_latency: 3.54 ms
    avg_latency: 998.94 us
    lock_latency: 1.31 ms
    rows_sent: 20
    rows_sent_avg: 2
    rows_examined: 103
    rows_examined_avg: 9
    rows_affected: 0
    rows_affected_avg: 0
    tmp_tables: 0
    tmp_disk_tables: 0
    rows_sorted: 0
    sort_merge_passes: 0
    digest: 2e2abac366d3b023e68564d3773f03fc
    first_seen: 2022-06-13 14:26:25
    last_seen: 2022-06-17 13:22:13
    社外非公開
    

    View Slide

23. sys. schema_unused_indexes

    mysql> SELECT * FROM sys.schema_unused_indexes LIMIT 5;
    +----------------+----------------------------------------------+----------------------------------------~
    | object_schema | object_name | index_name ~
    +----------------+----------------------------------------------+----------------------------------------~
    | production_asp | accounting_bank_charge_settings | index_accounting_bank_charge_settings_o~
    | production_asp | accounting_customer_advance_paid_setting | idx_acaps_on_customer_advance_paid_cate~
    | production_asp | accounting_payment_cooperative_fee_settings | index_accounting_payment_cooperative_fe~
    | production_asp | accounting_payment_nontaxable_ | idx_account_pay_nontax_cooperative_fee_~
    | production_asp | accounting_receipt_settings | idx_account_receipt_settings_on_receipt~
    +----------------+----------------------------------------------+----------------------------------------~
    5 rows in set (0.09 sec)
    • 利用されてないインデックス

    • 全てのDBサーバを確認するのを忘れずに

    社外非公開
    

    View Slide

24. sys. schema_redundant_indexes

    • 冗長なインデックス

    mysql> SELECT * FROM sys.schema_redundant_indexes LIMIT 1\G
    *************************** 1. row ***************************
    table_schema: production_asp
    table_name: account_item_masters
    redundant_index_name: index_account_item_masters_on_client_id
    redundant_index_columns: client_id
    redundant_index_non_unique: 1
    dominant_index_name: index_aim_on_client_id_and_item_code
    dominant_index_columns: client_id,item_code
    dominant_index_non_unique: 0
    subpart_exists: 0
    sql_drop_index: ALTER TABLE `production_asp`.`account_item_masters` DROP INDEX
    `index_account_item_masters_on_client_id`
    1 row in set (0.72 sec)
    mysql> SELECT COUNT(*) FROM sys.schema_redundant_indexes WHERE table_schema = 'production_asp'\G
    *************************** 1. row ***************************
    COUNT(*): 14
    1 row in set (0.72 sec)
    社外非公開
    社外非公開
    

    View Slide

25. ALTER TABLE 進捗 モニター

    • ALTER TABLE の進捗具合を確認する

    • 準備

    • デフォルトでは無効化されているため、有効化が必要

    -- 戻すときは、ENABLED を NO に
    mysql> UPDATE performance_schema.setup_instruments
    SET ENABLED = 'YES’
    WHERE NAME LIKE 'stage/innodb/alter%’;
    mysql> UPDATE performance_schema.setup_consumers
    SET ENABLED = 'YES’
    WHERE NAME LIKE '%stages%';
    

    View Slide

26. ALTER TABLE 進捗 モニター

    • events_stages_current をモニターすることで進捗がわかる

    mysql> SELECT NOW(), EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
    +---------------------+------------------------------------------------------+----------------+----------------+
    | NOW() | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
    +---------------------+------------------------------------------------------+----------------+----------------+
    | 2022-06-11 11:36:04 | stage/innodb/alter table (read PK and internal sort) | 405384 | 1214338 |
    +---------------------+------------------------------------------------------+----------------+----------------+
    1 row in set (0.00 sec)
    Mysql> SELECT NOW(), EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
    +---------------------+---------------------------------------+----------------+----------------+
    | NOW() | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
    +---------------------+---------------------------------------+----------------+----------------+
    | 2022-06-11 11:36:42 | stage/innodb/alter table (merge sort) | 839865 | 1230822 |
    +---------------------+---------------------------------------+----------------+----------------+
    1 row in set (0.01 sec)
    

    View Slide

27. 補足：カウンターをリセットする

    • Performance_schema, sys の値は累積値

    • リセット

    mysql> CALL sys.ps_truncate_all_tables(0);
    +---------------------+
    | summary |
    +---------------------+
    | Truncated 44 tables |
    +---------------------+
    1 row in set (0.01 sec)
    

    View Slide

28. MySQL v5.7 の新機能

    • Server Side Statement Timeout 

    • Performance Schema の強化 と sys Schema

    • Generated Column

    • 全文検索

    • JSON型

    

    View Slide

29. Generated Column

    • 機能概要

    • 生成列

    • 他のカラム（列）から式によってカラムの値を生成できる

    

    View Slide

30. Generated Column 例

    https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0150 より引用
    mysql> select * from zipcode;
    +----+--------------+
    | pk | prefecture |
    +----+--------------+
    | 1 | 北海道 |
    | 2 | 青森県 |
    ~
    mysql> ALTER TABLE zipcode ADD COLUMN region VARCHAR(255) GENERATED ALWAYS AS
    (CASE
    WHEN prefecture = '北海道’
    THEN '北海道地方’
    WHEN prefecture IN ('青森県', '秋田県', '岩手県', '山形県', '宮城県', '福島県’)
    THEN '東北地方’
    
    WHEN prefecture IN ('香川県', '徳島県', '愛媛県', '高知県’)
    THEN '四国地方’
    WHEN prefecture IN ('大分県', '宮崎県', '福岡県', '鹿児島県', '熊本県', '佐賀県', '長崎県', '沖縄
    県’)
    THEN '九州地方' END) VIRTUAL;
    

    View Slide

31. Generated Column 例

    • 式が適用された結果がカラムに反映される

    mysql> SELECT * FROM zipcode;
    +----+--------------+-----------------+
    | pk | prefecture | region |
    +----+--------------+-----------------+
    | 1 | 北海道 | 北海道地方 |
    | 2 | 青森県 | 東北地方 |
    | 3 | 岩手県 | 東北地方 |
    | 4 | 宮城県 | 東北地方 |
    mysql> UPDATE zipcode SET prefecture = '東京都' WHERE pk = 1;
    mysql> SELECT * FROM zipcode WHERE pk = 1;
    +----+------------+--------------+
    | pk | prefecture | region |
    +----+------------+--------------+
    | 1 | 東京都 | 関東地方 |
    +----+------------+--------------+
    1 row in set (0.00 sec)
    

    View Slide

32. DISK
    VIRTUAL と STORED

    • VIRTUAL

    • デフォルト

    • 生成したデータをデータファイルに書き込まない。

    参照する際に都度、計算式を適用する。

    ビューのような動き。

    • STORED

    • 実際に生成した値をデータファイルに書き込む

    • 👍 参照する際の都度、計算式を適用する必要がない

    • 👎 容量の増加

    東京都
    mysql> select * from zipcode where prefecture='東京都';
    +----+---------------+--------------+
    | pk | prefecture | region |
    +----+---------------+--------------+
    | 1 | 東京都 | 関東地方 |
    +----+---------------+--------------+
    f
    DISK
    東京都 関東地方
    mysql> select * from zipcode where prefecture='東京都';
    +----+---------------+--------------+
    | pk | prefecture | region |
    +----+---------------+--------------+
    | 1 | 東京都 | 関東地方 |
    +----+---------------+--------------+
    

    View Slide

33. Generate Columnを活用したチューニング例

    • インデックス目的であればVIRTUAL推奨

    mysql> SELECT `orders`.* FROM `orders`
    WHERE `orders`.`deleted_at` IS NULL AND `orders`.`client_id` = 12345
    AND (contracted_cd REGEXP '^000-[0-9]+$’)
    ORDER BY CAST(replace(contracted_cd,'000-','') as SIGNED) desc LIMIT 1 \G
    1 row in set (2.50 sec)
    mysql> SELECT `orders`.contracted_cd FROM `orders`
    WHERE `orders`.`deleted_at` IS NULL AND `orders`.`client_id` = 12345 AND
    (contracted_cd REGEXP '^000-[0-9]+$’);
    +---------------+
    | contracted_cd |
    +---------------+
    | 000-10000 |
    | 000-20000 |
    ~~
    | 000-9000 |
    +---------------+
    ハイフンより後ろの
    数値で降順ソート
    

    View Slide

34. Generate Columnを活用したチューニング例

    mysql> ALTER TABLE orders ADD COLUMN contracted_sub_cd INT
    GENERATED ALWAYS AS (CAST(replace(contracted_cd,'000-','') as SIGNED)) VIRTUAL;
    1 row in set (2.50 sec)
    mysql> SELECT contracted_sub_cd FROM `orders`
    WHERE `orders`.`deleted_at` IS NULL AND `orders`.`client_id` = 12345 AND
    (contracted_cd REGEXP '^000-[0-9]+$’);
    +---------------+-------------------+
    | contracted_cd | contracted_sub_cd |
    +---------------+-------------------+
    | 000-10000 | 10000 |
    | 000-20000 | 20000 |
    ~~
    | 000-9000 | 9000 |
    +---------------+-------------------+
    4 rows in set (0.00 sec)
    

    View Slide

35. Generate Columnを活用したチューニング例

    mysql> CREATE INDEX idx_client_id_contracted_sub_cd
    ON orders(client_id, contracted_sub_cd);
    Query OK, 0 rows affected (0.02 sec)
    id: 1
    select_type: SIMPLE
    table: orders
    partitions: NULL
    type: range
    possible_keys:
    key: idx_client_id_contracted_sub_cd
    key_len: 5
    ref: NULL
    rows: 4
    filtered: 25.00
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    id: 1
    select_type: SIMPLE
    table: orders
    partitions: NULL
    type: ALL
    possible_keys:
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 4
    filtered: 25.00
    Extra: Using where; Using filesort
    1 row in set, 1 warning (0.00 sec)
    • 実行計画の変化

    

    View Slide

36. MySQL v5.7 の新機能

    • Server Side Statement Timeout 

    • Performance Schema の強化 と sys Schema

    • Generated Column

    • 全文検索

    • JSON型

    

    View Slide

37. フルテキストインデックスの日本語対応

    • 機能概要

    • 日本語に使えるパーサーがサポートされた

    • Mecab と N-gram

    

    View Slide

38. 今日は単純な文字列マッチ

    (BOOLEAN MODE)

    に絞って解説

    

    View Slide

39. LIKE検索とパフォーマンスのおさらい

    • 前方一致　LIKE ’keyword%’;

    • （ふつうの）インデックスが利用できます。

    • 後方一致　LIKE ‘%keyword’;

    • （ふつうの）インデックスが利用できない。遅い。

    • 中間一致　LIKE ‘%keyword%’;

    • （ふつうの）インデックスが利用できない 。遅い。

    

    View Slide

40. • 前方一致　LIKE ’keyword%’;

    • （ふつうの）インデックスが利用できます。

    • 後方一致　LIKE ‘%keyword’;

    • （ふつうの）インデックスが利用できない。遅い。

    • 中間一致　LIKE ‘%keyword%’;

    • （ふつうの）インデックスが利用できない 。遅い。

    LIKE検索とパフォーマンスのおさらい

    フルテキストインデックス
    で高速化できる
    

    View Slide

41. インデックスの作成

    • CRATE FULLTEXT INDEX

    • WITH PARSER で日本語対応のパーサーを指定する

    

    

    

    

    mysql> SELECT * FROM fts_tbl;
    +----+-------------------------------------------------------------------------------------------+
    | pk | data |
    +----+-------------------------------------------------------------------------------------------+
    | 1 | 施工現場を幸せに。利用社数増加中の施工管理サービス。 |
    | 2 | 施工管理といえばANDPAD。施工状況をどこでも確認できるから、もう工期に遅れない。 |
    | 3 | 【シェアNo.1】施工管理アプリ| ANDPAD（アンドパッド） |
    +----+-------------------------------------------------------------------------------------------+
    mysql> CREATE FULLTEXT INDEX idx_data ON fts_tbl(data) WITH PARSER ngram;
    Query OK, 0 rows affected (0.16 sec)
    

    View Slide

42. 検索

    • MATCH 〜 AGAINST

    • フルテキストインデックスが存在しないとエラー

    -- SELECT * FROM fts_tbl WHERE data LIKE ‘%ANDPAD%’ と同じ
    mysql> SELECT * FROM fts_tbl WHERE MATCH(data) AGAINST ('ANDPAD' IN BOOLEAN MODE);
    +----+----------------------------------------------------------------------------------------+
    | pk | data |
    +----+----------------------------------------------------------------------------------------+
    | 2 | 施工管理といえばANDPAD。施工状況をどこでも確認できるから、もう工期に遅れない。 |
    | 3 | 【シェアNo.1】施工管理アプリ| ANDPAD（アンドパッド） |
    +----+----------------------------------------------------------------------------------------+
    

    View Slide

43. 検索

    • 論理演算も可能

    mysql> SELECT * FROM fts_tbl WHERE MATCH(data) AGAINST ('ANDPAD' IN BOOLEAN MODE);
    +----+----------------------------------------------------------------------------------------+
    | pk | data |
    +----+----------------------------------------------------------------------------------------+
    | 2 | 施工管理といえばANDPAD。施工状況をどこでも確認できるから、もう工期に遅れない。 |
    | 3 | 【シェアNo.1】施工管理アプリ| ANDPAD（アンドパッド） |
    +----+----------------------------------------------------------------------------------------+
    mysql> SELECT * FROM fts_tbl WHERE MATCH(data) AGAINST ('ANDPAD -アプリ' IN BOOLEAN MODE);
    +----+-----------------------------------------------------------------------------------------+
    | pk | data |
    +----+-----------------------------------------------------------------------------------------+
    | 2 | 施工管理といえばANDPAD。施工状況をどこでも確認できるから、もう工期に遅れない。 |
    +----+-----------------------------------------------------------------------------------------+
    

    View Slide

44. Parser

    パーサー
    トークナイズ
    方法
    Aurora 2.X
    でのサポート
    特徴
    デフォルト
    スペース
    区切り
    ◯
    スペース区切りでトークナイズ
    日本語の文章には使えない
    Mecab 形態素解析 ×
    文法、単語の品詞（辞書）でトークナイズ
    ノイズの少ない検索結果
    新語や略語など、検索漏れのリスクあり
    単語での検索向け
    ngram N-gram ◯
    2-gram「ANDPAD」→[AN,ND,DP,PA,AD]
    インデックス容量が大きい
    シンプルに文字列の有無で検索
    • 文章をトークナイズして、「キーワード」と「ドキュメントID」の対応付けを行う（転
    置インデックス）

    

    View Slide

45. 注意事項

    • 記号が検索できない

    • B'zを検索できるようになるまで

    • https://qiita.com/vukujin/items/49c7164061ffca61f1da

    

    • ストップワードを無効化して利用する

    • 一部の英字列が検索できなくなる原因

    • https://mita2db.hateblo.jp/entry/2019/12/08/090000

    

    View Slide

46. よく考えて使う

    • MySQL の全文検索機能は全文検索専用のDBには劣る

    • 本当に文字列を含む・含まないだけで十分？

    • 表記ゆれ：「アンドパッド」＝「アンド・パッド」「ANDPAD」

    • スペル補正：「アンドパット」→「アンドパッド」

    • スコアリング、高度なロジックでソートしたい

    • ちょっとした補助機能や社内向け機能で使おう

    • 本当に「検索」が必要かも考えてみる

    • 検索に似たもの：フィルター、タグ

    検索専用の
    データベース
    を使いましょう
    

    View Slide

47. MySQL v5.7 の新機能

    • Server Side Statement Timeout 

    • Performance Schema の強化 と sys Schema

    • Generated Column

    • 全文検索

    • JSON型

    

    View Slide

48. JSON型

    • 機能概要

    • JSONを保存するための専用の型ができた

    • JSON関数と合わせて利用

    

    View Slide

49. JSON関数

    • 値を取り出す

    

    

    

    • JSONを生成する

    

    

    

    mysql> SELECT json_col, JSON_EXTRACT(json_col, '$.name'), JSON_UNQUOTE(json_col->'$.name’)
    FROM json_tbl WHERE pk = 3 \G
    *************************** 1. row ***************************
    json_col: {"name": "john", "items": {"car": 5, "pen": 5, "book": 10}}
    JSON_EXTRACT(json_col, '$.name'): "john"
    JSON_UNQUOTE(json_col->'$.name'): john
    1 row in set (0.00 sec)
    

    

    

    -- INSERT INTO json_tbl (json_col) VALUES(‘"name": "taro", "items": [{"pen": "2022-07-10""}]}’) と同じ
    mysql> INSERT INTO json_tbl (json_col)
    VALUES(JSON_OBJECT('name', 'taro', 'items', JSON_ARRAY(JSON_OBJECT(‘time’, CURDATE()))));
    Query OK, 1 row affected (0.00 sec)
    

    View Slide

50. テキスト型とJSON型の比較

    • バリデーション

    

    

    • 最適化

    

    

    

    • MySQL v8.0 で より効率的なUPDATE (Partial Update) をサポート

    mysql> INSERT INTO text_tbl (text_col) VALUES('{"name": }');
    Query OK, 1 row affected (0.01 sec)
    mysql> INSERT INTO json_tbl (json_col) VALUES('{"name": }');
    ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position
    9 in value for column 'json_tbl.json_col'.
    mysql> INSERT INTO json_tbl (json_col) VALUES('{"name": "taro"}');
    Query OK, 1 row affected (0.01 sec)
    mysql> SELECT json_col FROM json_tbl;
    +------------------+
    | json_col |
    +------------------+
    | {"name": "taro"} |
    +------------------+
    余計なスペース
    

    View Slide

51. Generated Column との合わせ技

    • Generated Column で、特定のフィールドをカラムとして切り出す

    

    mysql> ALTER TABLE json_tbl ADD `name` varchar(30) GENERATED ALWAYS AS
    (JSON_UNQUOTE(`json_col`->"$.name")) VIRTUAL;
    mysql> SELECT * FROM json_tbl;
    +----+------------------------------------------------------------------+-------+
    | pk | json_col | name |
    +----+------------------------------------------------------------------+-------+
    | 3 | {"name": "john", "items": {"car": 5, "pen": 5, "book": 10}} | john |
    | 4 | {"name": "alice", "items": {"chair": 1, "shoes": 3, "water": 5}} | alice |
    +----+------------------------------------------------------------------+-------+
    2 rows in set (0.00 sec)
    

    View Slide

52. Generated Column との合わせ技

    mysql> ALTER TABLE json_tbl ADD INDEX idx_name (name);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> EXPLAIN SELECT * FROM json_tbl WHERE name = "john";
    +----+-------------+----------+------+---------------+----------+~
    | id | select_type | table | type | possible_keys | key |~
    +----+-------------+----------+------+---------------+----------+~
    | 1 | SIMPLE | json_tbl | ref | idx_name | idx_name |~
    +----+-------------+----------+------+---------------+----------+~
    1 row in set, 1 warning (0.00 sec)
    mysql> EXPLAIN SELECT * FROM json_tbl WHERE name = "john";
    +----+-------------+----------+------+---------------+------+~
    | id | select_type | table | type | possible_keys | key |~
    +----+-------------+----------+------+---------------+------+~
    | 1 | SIMPLE | json_tbl | ALL | NULL | NULL |~
    +----+-------------+----------+------+---------------+------+~
    1 row in set, 1 warning (0.00 sec)
    

    View Slide

53. JSONのままMySQLに保存するメリット

    • データを一箇所で管理できる

    • DynamoDB等 他のデータベースに保存するケースと比較して

    

    • 複雑な構造のデータを簡単に扱える

    • スキーマレス

    

    • パフォーマンス面で有利

    • 複雑なデータを正規化し、分解したケースと比較して

    

    View Slide

54. JSONをMySQLに保存するデメリット

    • １つのカラムに複数の値を入れることになる

    • データの整合性を担保するのが困難

    • スキーマレス

    • 制約も利用できない

    • RDBの良さが失われる！

    

    View Slide

55. 目次

    1. MySQL / Aurora のバージョンと歴史

    2. MySQL v5.7 の新機能

    3. MySQL v8.0 の紹介

    4. 質疑応答

    

    View Slide

56. 8.0 変更点 抜粋

    1. クエリキャッシュの廃止

    2. インスタントDDL

    3. TempTable ストレージエンジン

    • TEXT/BLOB 型もオンメモリでソート/GROUP BY される

    4. NOWAIT/ SKIP LOCKED

    5. Window 関数、CTE のサポート

    

    View Slide

57. 目次

    1. MySQL / Aurora のバージョンと歴史

    2. MySQL v5.7 の新機能

    3. MySQL v8.0 の紹介

    4. 質疑応答

    

    View Slide

58. まとめ

    

    View Slide

59. まとめ

    • 本日紹介した MySQL 5.7 新機能

    • Server Side Statement Timeout 

    • Performance Schema の強化 と sys Schema

    • Generated Column

    • 全文検索

    • JSON型

    • ぜひ、活用してください！

    

    View Slide

60. 過去資料

    • MySQL SQLチューニング 勉強会
    • 社内ドキュメント
    • MySQL ロック勉強会（基礎編）

    • https://tech.andpad.co.jp/entry/2022/03/30/101500
    • MySQL ロック勉強会（DDL編）

    • https://tech.andpad.co.jp/entry/2020/08/18/132130
    • Datadog APM を使ったパフォーマンス分析 勉強会
    • https://tech.andpad.co.jp/entry/2021/02/10/170000
    • データベース利用規約／SQLレビュー観点
    • 社内ドキュメント
    

    View Slide

61. Thanks

    

    View Slide