100万件程度のデータを用意。
0.1 秒以下を指標とする。

スロークエリの抽出

スロークエリ設定確認

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0007
https://ptune.jp/tech/how-to-check-mysql-slow-query/

show variables like 'slow_query%'

出力例

Variable_name	Value
slow_query_log	ON
slow_query_log_file	/var/lib/mysql/dcde79b8befd-slow.log

show variables like 'long%';

+-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+

※1秒

設定内容は、「my.cnf」でも確認可能

スロークエリ検出有効設定を ON

※MySQL を再起動すると、再び OFF に戻る

set global slow_query_log=1;

スロークエリ基準時間設定

以下、「1秒以下以上のクエリを検出する」という設定

set global long_query_time=1;

set global long_query_time=0.4;

※「show variables like 'long%'」で見ても変わっていない場合、一度コンソールを開きなおす

スロークエリの出力ログを設定

set global slow_query_log_file ='/usr/local/var/mysql/slow_query.log';

set global slow_query_log_file ='/var/lib/mysql/slow_query.log'

スロークエリ

select sleep(15)

ログ出力例

# cat /var/lib/mysql/dcde79b8befd-slow.log
mysqld, Version: 5.7.35 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2021-10-04T04:07:59.774545Z
# User@Host: root[root] @ myapp01-php_1.bigbang-shared-network [172.18.0.7]  Id:   115
# Query_time: 0.001368  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use asims;
SET timestamp=1633320479;
# administrator command: Prepare;
# Time: 2021-10-04T04:07:59.822910Z
# User@Host: root[root] @ myapp01-php_1.bigbang-shared-network [172.18.0.7]  Id:   114
# Query_time: 0.002219  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1633320479;
# administrator command: Prepare;
# Time: 2021-10-04T04:07:59.826203Z
# User@Host: root[root] @ myapp01-php_1.bigbang-shared-network [172.18.0.7]  Id:   114
# Query_time: 0.002824  Lock_time: 0.000036 Rows_sent: 18  Rows_examined: 1116
SET timestamp=1633320479;
select `table_name`, `logical_table_name`, `column_name`, `logical_column_name`, `is_editable_required_attribute` from `table_column_definitions` where `id` in (select `table_column_definition_id` from `by_function_table_column_definitions` where `target_table_type` = '28') and `table_column_definitions`.`deleted_at` is null order by `table_name` desc, `sort_order` asc;

実行計画

explain

「explain」を付けて実行

explain
select 
 `table_name`, `logical_table_name`, `column_name`, `logical_column_name`, `is_editable_required_attribute`
 from `table_column_definitions` where `id` in (select `table_column_definition_id` from `by_function_table_column_definitions` where `target_table_type` = '28') 
and `table_column_definitions`.`deleted_at` is null order by `table_name` desc, `sort_order` asc;

出力例

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	table_column_definitions	« NULL »	ALL	PRIMARY	« NULL »	« NULL »	« NULL »	247	10	Using where; Using filesort
1	SIMPLE	by_function_table_column_definitions	« NULL »	ref	by_function_table_column_definitions_index_1	by_function_table_column_definitions_index_1	8	asims.table_column_definitions.id	3	10	Using index condition; Using where; FirstMatch(table_column_definitions)

key - 効いている index ref - 対象のカラム。（cons が最速） row - updateの場合、その行数となる？

type

const・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。
ref・・・ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。
range・・・インデックスを用いた範囲検索。
index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
ALL・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。

注意点

index が効く範囲と、update の範囲が異なる事がある。（デッドロックの範囲に注意する）

テーブルロックについて

https://yoku0825.blogspot.com/2012/07/deadlock-found-when-trying-to-get-lock.html
https://qiita.com/dorarep/items/d7d767e1bceb2d5649a7

update 分を発行した時、業ロックが発生する。

indexで絞れる範囲まで絞ってから、indexで絞りきれなかったrows全てにロックをかけてる。

例

colomn1 インデックスなし colomn2　インデックスあり

update  TABLE1
   set  column1 = 1
 where  column1 = 200
   and  column2 = 300

この場合、column2 にしかインデックスが効いていない。そのため、column1 の全範囲がロック対象となる。 ※インデックスもロック制御となる（？）

update  TABLE1
   set  column1 = 1
 where  column1 = 200

この場合、全行がロック対象となる。

※デッドロックが頻発する場合、テーブルインデックスを見直す（デッドロックが発生した SQL はログに出力され、メールで飛ぶ。）

レンジロック（insert によるロック）

insert 文を発行する時、id の空き番を予約する。
この時、同様の insert 文が複数並列で動作した時、デッドロックが発生する事がある。

同様の insert 文が同時に３つ発行された場合、２つ目までは処理できるが、３つ目はエラー（デッドロック）となる。
（MySQL の仕様。7では改善されてるっぽい？ MySQL 5 は悲痛な叫びがいろいろ聞こえるが、7はほとんど見られない。ユーザ数の違いによるもの？）

index を細かく張ることで回避できる。（同時に、「同一荷主の同一販売チャンネルの出荷指示No」と、同時に実行される可能性が極め低い単位まで、index の粒度を上げる事で回避できる）

デッドロック発生のエラーメッセージ例

critical 9999900001 A system error has occurred. :[message]SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL:          update  shipment_record_details

[trace]#0 /application/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection-&gt;runQueryCallback(&#039;  ...&#039;, Array, Object(Closure))
#1 /application/vendor/laravel/framework/src/Illuminate/Database/Connection.php(496): Illuminate\Database\Connection-&gt;run(&#039;               ...&#039;, Array, Object(Closure))
#2 /application/vendor/laravel/framework/src/Illuminate/Database/Connection.php(429): Illuminate\Database\Connection-&gt;affectingStatement(&#039;    ...&#039;, Array)
#3 /application/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php(367): Illuminate\Database\Connection-&gt;update(&#039; ...&#039;, Array)
#4 /application/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(261): Illuminate\Database\DatabaseManager-&gt;__call(&#039;update&#039;, Array)

ロック待ちのタイムアウト時間の確認／設定

確認

デフォルトは 50秒

mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

設定

mysql> SET innodb_lock_wait_timeout=5;

MySQLのインデックス作成方法 - 効いてないと思ったらexplainで確認する

https://style.potepan.com/articles/17616.html

インデックスの種類

MySQLではPRIMARY、UNIQUE、INDEX、FULLTEXT、SPATIALの5種類のインデックスが設定可能です。

SQL内の「ADD INDEX」の部分を「ADD UNIQUE」などに差し替えることでインデックス種類の指定ができます。

PRIMARYは、プライマリキーのインデックスです。プライマリキー指定をすると自動的に設定されます。

UNIQUEは、ユニークキー、外部キー用のインデックスです。

INDEXは一般のインデックスです。基本は「ADD INDEX」でインデックス指定すると良いでしょう。

FULLTEXTは、全文検索用のインデックスです。不定長のtextカラムに設定すると、全文検索の速度が大幅に改善されますが、インデックスに使用する領域を多く使うという欠点もあります。

SPATIALは、空間データ用のインデックスです。空間データとは、都市や山などの立体情報や町や市などの範囲情報で、これらを格納するのに向いている空間データ型に対するのが空間インデックスです。MySQLでは、空間データのソリューション開発に関わる 250 以上の企業、機関、および大学の国際的なコンソーシアムOpen Geospatial Consortium (OGC) の仕様に沿って、空間データが実装されています。

インデックスの再構築方法

照合順序の修正をおこなった場合など、インデックスを再設定するには、単純にインデックスをドロップしてから再作成します。