だらけた日記

MySQL のメモ

実行計画(EXPLAIN ステートメント)

EXPLAIN select * from messages

-- 実行結果
/*
## id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

'1', 'SIMPLE', 'messages', NULL, 'ALL', NULL, NULL, NULL, NULL, '997277', '100.00', NULL

*/
Column Description
id SELECT識別子。同一のクエリ内でのSELECT文の順番や、サブクエリやUNIONなどの関係を示す。
select_type SELECTクエリのタイプ。例えば、SIMPLE（単純SELECT）、SUBQUERY（サブクエリ内のSELECT）、UNION（UNIONの2番目以降のSELECT）などがある。
table クエリが参照しているテーブル名。
partitions クエリが参照しているパーティション。パーティショニングされていない場合はNULL。
type ジョインのタイプ。例えば、ALL（フルテーブルスキャン）、index（インデックス全スキャン）、range（範囲指定によるインデックス使用）など。
possible_keys クエリ実行時に使用可能なインデックス。
key 実際にクエリで使用されているインデックス。
key_len 使用されているインデックスの長さ。
ref インデックスを検索する際に参照されるカラムや定数。
rows クエリ実行によって読み込まれる行数の推定値。
filtered テーブルの行がクエリの条件にどれだけマッチするかのパーセンテージ。
Extra クエリ実行に関する追加情報。例えば、Using index（インデックスのみを使用してデータを取得）、Using where（WHERE条件を使用する）、Using temporary（一時テーブルを使用する）、などがある。
select_type
  • SIMPLE: 単純なSELECTクエリで、サブクエリやUNIONが使われていない場合。
  • PRIMARY: サブクエリやUNIONの最も外側のSELECT。
  • SUBQUERY: サブクエリ内のSELECT。最も外側ではないSELECT文。
  • DERIVED: FROM句内のサブクエリで生成された一時テーブルを参照するSELECT。
  • UNION: UNIONによって結合された二番目以降のSELECTクエリ。
  • UNION RESULT: UNIONクエリの結果を含む一時テーブル。
type
  • ALL: フルテーブルスキャン。テーブルの全行がスキャンされる。
  • index: インデックス全スキャン。インデックスの全エントリがスキャンされる。
  • range: 範囲スキャン。インデックスを使って特定の範囲の行が検索される。
  • ref: インデックスを使って、一つまたは複数の値で行が検索される。
  • eq_ref: 主キーまたはユニークキーのインデックスを使って、単一の行が検索される。
  • const: 主キーまたはユニークキーによって、単一の行が検索される。クエリ実行時に結果が定数として扱われる。
  • system: テーブルに一行のみ存在する（または空の場合）。クエリ実行時に結果が定数として扱われる。
Extra
  • Using index: クエリがインデックスのみを使用してデータを取得し、テーブルの行を読み込む必要がない場合。
  • Using where: WHERE句を使用して、特定の行をフィルタリングする場合。
  • Using temporary: クエリの結果を格納するために一時テーブルを使用する場合。例えば、ORDER BYやGROUP BYを含むクエリで一時テーブルが必要な場合。
  • Using filesort: MySQLが結果をソートするために外部のソート操作を使用する場合。インデックスを利用できないソート処理に使われる。

MySQL 8.0.18 では、EXPLAIN ANALYZE

EXPLAIN analyze select * from messages

-- 実行結果
/*
## EXPLAIN
'-> Table scan on messages  (cost=198 rows=1900) (actual time=4.43..23 rows=1900 loops=1)\n'

*/

SHOW STATUS ステートメント

MySQLサーバーに接続されているスレッドの数

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

table status

SHOW TABLE STATUS LIKE 'messages';

tableのSize

SELECT 
  TABLE_SCHEMA AS `Database`, 
  TABLE_NAME AS `Table`, 
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `Size in MB`
FROM 
  information_schema.TABLES
WHERE 
  TABLE_SCHEMA = 'ここにデータベース名を指定'
ORDER BY 
  (DATA_LENGTH + INDEX_LENGTH) DESC;

統計情報更新

ANALYZE TABLE {tableName}

--　複数(カンマまで繋げる)
ANALYZE TABLE {tableName1},{tableName2}

クエリキャッシュの SELECT オプション

クエリ キャッシュは MySQL 5.7.20 で非推奨となり、MySQL 8.0 では削除されました。

select SQL_CACHE id from message;

エラー対応

  1. case1
Error Code: 3675. Create table/tablespace 'states' failed, as disk is full

  1. システム全体のディスク使用状況を確認

     df -h | sort -r -k 5,5

  2. 使用率が高いファイルシステムの特定<参考サイト　CentOS ディスク容量不足の原因調査＆MySQLのバイナリログ自動削除設定>

    実際の調査コマンド ```bash bash-4.4## du -sh /* 0 /bin 4.0K /boot 0 /dev 28K /docker-entrypoint-initdb.d 0 /entrypoint.sh 3.1M /etc 4.0K /home 0 /lib 0 /lib64 4.0K /media 4.0K /mnt 4.0K /opt du: cannot read directory '/proc/1/task/1/fdinfo': Permission denied du: cannot read directory '/proc/1/task/208/fdinfo': Permission denied du: cannot read directory '/proc/1/task/211/fdinfo': Permission denied du: cannot read directory '/proc/1/task/212/fdinfo': Permission denied du: cannot read directory '/proc/1/task/213/fdinfo': Permission denied du: cannot read directory '/proc/1/task/214/fdinfo': Permission denied du: cannot read directory '/proc/1/task/215/fdinfo': Permission denied du: cannot read directory '/proc/1/task/216/fdinfo': Permission denied du: cannot read directory '/proc/1/task/217/fdinfo': Permission denied du: cannot read directory '/proc/1/task/218/fdinfo': Permission denied du: cannot read directory '/proc/1/task/219/fdinfo': Permission denied du: cannot read directory '/proc/1/task/220/fdinfo': Permission denied du: cannot read directory '/proc/1/task/222/fdinfo': Permission denied du: cannot read directory '/proc/1/task/223/fdinfo': Permission denied du: cannot read directory '/proc/1/task/224/fdinfo': Permission denied du: cannot read directory '/proc/1/task/225/fdinfo': Permission denied du: cannot read directory '/proc/1/task/226/fdinfo': Permission denied du: cannot read directory '/proc/1/task/227/fdinfo': Permission denied du: cannot read directory '/proc/1/task/232/fdinfo': Permission denied du: cannot read directory '/proc/1/task/233/fdinfo': Permission denied du: cannot read directory '/proc/1/task/234/fdinfo': Permission denied du: cannot read directory '/proc/1/task/235/fdinfo': Permission denied du: cannot read directory '/proc/1/task/236/fdinfo': Permission denied du: cannot read directory '/proc/1/task/237/fdinfo': Permission denied du: cannot read directory '/proc/1/task/238/fdinfo': Permission denied du: cannot read directory '/proc/1/task/239/fdinfo': Permission denied du: cannot read directory '/proc/1/task/240/fdinfo': Permission denied du: cannot read directory '/proc/1/task/241/fdinfo': Permission denied du: cannot read directory '/proc/1/task/245/fdinfo': Permission denied du: cannot read directory '/proc/1/task/246/fdinfo': Permission denied du: cannot read directory '/proc/1/task/247/fdinfo': Permission denied du: cannot read directory '/proc/1/task/248/fdinfo': Permission denied du: cannot read directory '/proc/1/task/249/fdinfo': Permission denied du: cannot read directory '/proc/1/task/250/fdinfo': Permission denied du: cannot read directory '/proc/1/task/251/fdinfo': Permission denied du: cannot read directory '/proc/1/task/252/fdinfo': Permission denied du: cannot read directory '/proc/1/task/253/fdinfo': Permission denied du: cannot read directory '/proc/1/task/255/fdinfo': Permission denied du: cannot read directory '/proc/1/task/256/fdinfo': Permission denied du: cannot read directory '/proc/1/task/257/fdinfo': Permission denied du: cannot read directory '/proc/1/map_files': Permission denied du: cannot read directory '/proc/1/fdinfo': Permission denied du: cannot access '/proc/281/task/281/fd/3': No such file or directory du: cannot access '/proc/281/task/281/fdinfo/3': No such file or directory du: cannot access '/proc/281/task/283/fd/3': No such file or directory du: cannot access '/proc/281/task/283/fdinfo/3': No such file or directory du: cannot access '/proc/281/fd/3': No such file or directory du: cannot access '/proc/281/fdinfo/3': No such file or directory 0 /proc 20K /root 32K /run 0 /sbin 4.0K /srv 0 /sys 4.0K /tmp 541M /usr 39G /var bash-4.4## bash-4.4## du -sh /var/* 4.0K /var/adm 28K /var/cache 4.0K /var/db 4.0K /var/empty 4.0K /var/ftp 4.0K /var/games 4.0K /var/gopher 12K /var/kerberos 39G /var/lib 4.0K /var/local 0 /var/lock 4.0K /var/log 0 /var/mail 4.0K /var/nis 4.0K /var/opt 4.0K /var/preserve 0 /var/run 12K /var/spool 4.0K /var/tmp 4.0K /var/yp bash-4.4## du -sh /var/lib/* 16K /var/lib/alternatives 988K /var/lib/dnf 4.0K /var/lib/games 4.0K /var/lib/misc 39G /var/lib/mysql 4.0K /var/lib/mysql-files 4.0K /var/lib/mysql-keyring 11M /var/lib/rpm 4.0K /var/lib/rpm-state 8.0K /var/lib/selinux 4.0K /var/lib/supportinfo bash-4.4## du -sh /var/lib/mysql/* 192K /var/lib/mysql/##ib_16384_0.dblwr 8.2M /var/lib/mysql/##ib_16384_1.dblwr 101M /var/lib/mysql/##innodb_redo 804K /var/lib/mysql/##innodb_temp 44K /var/lib/mysql/0b8459b69ac1.log 148K /var/lib/mysql/attendances 4.0K /var/lib/mysql/auto.cnf 4.0K /var/lib/mysql/binlog.000061 32K /var/lib/mysql/binlog.000062 4.0K /var/lib/mysql/binlog.000063 4.0K /var/lib/mysql/binlog.000064 4.0K /var/lib/mysql/binlog.000065 4.0K /var/lib/mysql/binlog.000066 4.0K /var/lib/mysql/binlog.000067 4.0K /var/lib/mysql/binlog.000068 32K /var/lib/mysql/binlog.000069 52K /var/lib/mysql/binlog.000070 1.3M /var/lib/mysql/binlog.000071 15M /var/lib/mysql/binlog.000072 32K /var/lib/mysql/binlog.000073 8.0K /var/lib/mysql/binlog.000074 1.1G /var/lib/mysql/binlog.000075 1.1G /var/lib/mysql/binlog.000076 1.1G /var/lib/mysql/binlog.000077 1.1G /var/lib/mysql/binlog.000078 1.1G /var/lib/mysql/binlog.000079 1.1G /var/lib/mysql/binlog.000080 1.1G /var/lib/mysql/binlog.000081 285M /var/lib/mysql/binlog.000082 2.5M /var/lib/mysql/binlog.000083 1.1G /var/lib/mysql/binlog.000084 1.1G /var/lib/mysql/binlog.000085 1.1G /var/lib/mysql/binlog.000086 1.1G /var/lib/mysql/binlog.000087 732M /var/lib/mysql/binlog.000088 1.1G /var/lib/mysql/binlog.000089 1.1G /var/lib/mysql/binlog.000090 1.1G /var/lib/mysql/binlog.000091 1.1G /var/lib/mysql/binlog.000092 1.1G /var/lib/mysql/binlog.000093 1.1G /var/lib/mysql/binlog.000094 1.1G /var/lib/mysql/binlog.000095 1.1G /var/lib/mysql/binlog.000096 1.1G /var/lib/mysql/binlog.000097 1.1G /var/lib/mysql/binlog.000098 1.1G /var/lib/mysql/binlog.000099 1.1G /var/lib/mysql/binlog.000100 1.1G /var/lib/mysql/binlog.000101 1.1G /var/lib/mysql/binlog.000102 1.1G /var/lib/mysql/binlog.000103 1.1G /var/lib/mysql/binlog.000104 1.1G /var/lib/mysql/binlog.000105 734M /var/lib/mysql/binlog.000106 1.1G /var/lib/mysql/binlog.000107 1.1G /var/lib/mysql/binlog.000108 1.1G /var/lib/mysql/binlog.000109 1.1G /var/lib/mysql/binlog.000110 1.1G /var/lib/mysql/binlog.000111 1.1G /var/lib/mysql/binlog.000112 259M /var/lib/mysql/binlog.000113 367M /var/lib/mysql/binlog.000114 4.0K /var/lib/mysql/binlog.000115 4.0K /var/lib/mysql/binlog.index 4.0K /var/lib/mysql/ca-key.pem 4.0K /var/lib/mysql/ca.pem 4.0K /var/lib/mysql/client-cert.pem 4.0K /var/lib/mysql/client-key.pem 1.3M /var/lib/mysql/conference 266M /var/lib/mysql/contacts 420K /var/lib/mysql/emergencys 24K /var/lib/mysql/ib_buffer_pool 0 /var/lib/mysql/ib_buffer_pool.incomplete 12M /var/lib/mysql/ibdata1 12M /var/lib/mysql/ibtmp1 148K /var/lib/mysql/monitorings 36K /var/lib/mysql/mysql 31M /var/lib/mysql/mysql.ibd 0 /var/lib/mysql/mysql.sock 1.7M /var/lib/mysql/performance_schema 4.0K /var/lib/mysql/private_key.pem 4.0K /var/lib/mysql/public_key.pem 4.0K /var/lib/mysql/server-cert.pem 4.0K /var/lib/mysql/server-key.pem 116K /var/lib/mysql/sys 977M /var/lib/mysql/undo_001 241M /var/lib/mysql/undo_002 ```

  3. バイナリログのディスクスペース使用量を減らす方法:

    1. バイナリログの削除: MySQLではPURGE BINARY LOGSコマンドを使用して古いバイナリログを安全に削除できます。例えば、特定の日付より前の全てのログを削除するには、次のコマンドを使用します（MySQLプロンプト内で）:

        PURGE BINARY LOGS BEFORE '2024-02-20 22:46:26';

    2. バイナリログの自動削除設定: expire_logs_days オプションをMySQLの設定ファイル（通常はmy.cnfまたはmy.ini）に追加して、古いバイナリログが自動的に削除されるように設定できます。例えば、7日より古いバイナリログを自動的に削除するには、次の行を設定ファイルに追加します:

          [mysqld]
    expire_logs_days = 7

関連記事

だらけた日記

© 2023 NYBEYOND.