MySQLクエリキャッシュに関するヒント

--SQL 1
ID = 121 のアカウントから ID、残高を選択します。
--SQL 2
ID が 121 の場合、アカウントから ID、残高を選択します。

#!/usr/bin/perl -w
Digest::MD5 qw(md5 md5_hex md5_base64) を使用します。
Math::BigInt を使用します。
my $stmt = "id = 121\0 のアカウントから id、balance を選択"; 
私の $hash = md5 $stmt; 
my($a,$b,$msb,$lsb) = unpack("V*",$hash);
私の$sqln = $msb*(2**32)+$lsb;
私の$stop = log($sqln) / log(32) + 1;
私の$sqlid = '';
私の$charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';
私の @chars = 分割 ''、$charbase32;
($i=0; $i < $stop-1; $i++){
 私の $x = Math::BigInt->new($sqln);
 私の$seq = $x->bdiv(32**$i)->bmod(32);
 $sqlid = $chars[$seq].$sqlid;
}
print "SQL は:\n $stmt \nSQL_ID は\n $sqlid\n";

SQL は次のとおりです: select id, balance from account where id = 121 
SQL_IDはdm5c6ck1g7bdsです
SQL は次のとおりです: select id, balance From account where id = 121 
SQL_IDは6xb8gvs5cmc9bです

mysql> '%query_cache%' のような変数を表示します。
+------------------------------+----------+
| 変数名 | 値 |
+------------------------------+----------+
| クエリキャッシュがある | はい |
| クエリキャッシュ制限 | 1048576 |
| クエリ キャッシュの最小解像度単位 | 4096 |
| クエリキャッシュサイズ | 16777216 |
| クエリキャッシュタイプ | オフ |
| query_cache_wlock_invalidate | オフ |

# MySQL 構成ファイル /etc/my.cnf を変更し、次の構成を追加して、MySQL サーバーを再起動します。
[mysqld]
クエリキャッシュサイズ = 32M
クエリキャッシュタイプ = 1

--ユーザー テーブル users を作成し、100 万件のデータを挿入します。
テーブル「users」を作成します（
 `id` bigint NOT NULL AUTO_INCREMENT、
 `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名前',
 `age` tinyint NOT NULL デフォルト '0' コメント 'age',
 `gender` char(1) NOT NULL デフォルト 'M' コメント '性別',
 `phone` varchar(16) NOT NULL DEFAULT '' COMMENT '電話番号',
 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',
 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻',
 主キー (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザー情報テーブル';

ユーザーからcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 1000000 |

#QueryCache 設定を無効にする query_cache_size = 0
クエリキャッシュタイプ = 0

-- クエリ ステートメントの最初の実行mysql> select * from users order by create_time desc limit 10;
+---------+------------+---------+-----------+-----------+----------------------+---------------------+
| ID | 名前 | 年齢 | 性別 | 電話番号 | 作成時間 | 更新時間 |
+---------+------------+---------+-----------+-----------+----------------------+---------------------+
| 997855 | ユーザー997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 行セット (0.89 秒)
-- 同じクエリを 2 回目に実行します。mysql> select * from users order by create_time desc limit 10;
+---------+------------+---------+-----------+-----------+----------------------+---------------------+
| ID | 名前 | 年齢 | 性別 | 電話番号 | 作成時間 | 更新時間 |
+---------+------------+---------+-----------+-----------+----------------------+---------------------+
| 997855 | ユーザー997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 行セット (0.90 秒)
-- プロファイル追跡 mysql> show profile cpu,block io for query 1; 
+----------------------+----------+-----------+-------------+--------------+---------------+
| ステータス | 期間 | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+-----------+-------------+--------------+---------------+
| 準備中 | 0.000022 | 0.000017 | 0.000004 | 0 | 0 |
| ソート結果 | 0.000014 | 0.000009 | 0.000005 | 0 | 0 |
| 実行中 | 0.000011 | 0.000007 | 0.000004 | 0 | 0 |
| データ送信中 | 0.000021 | 0.000016 | 0.000004 | 0 | 0 |
| ソートインデックスを作成しています | 0.906290 | 0.826584 | 0.000000 | 0 | 0 |

#QueryCache 設定を無効にする query_cache_size = 32M
クエリキャッシュタイプ = 1

-- クエリ ステートメントの最初の実行mysql> select * from users order by create_time desc limit 10;
+---------+------------+---------+-----------+-----------+----------------------+---------------------+
| ID | 名前 | 年齢 | 性別 | 電話番号 | 作成時間 | 更新時間 |
+---------+------------+---------+-----------+-----------+----------------------+---------------------+
| 997855 | ユーザー997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 行セット (0.89 秒)
-- 2 回目のクエリ ステートメントを実行します。mysql> select * from users order by create_time desc limit 10;
+---------+------------+---------+-----------+-----------+----------------------+---------------------+
| ID | 名前 | 年齢 | 性別 | 電話番号 | 作成時間 | 更新時間 |
+---------+------------+---------+-----------+-----------+----------------------+---------------------+
| 997855 | ユーザー997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
セット内の行数は 10 です (0.00 秒)
-- プロファイル追跡データmysql> クエリ 3 のプロファイル cpu、ブロック io を表示します。
+--------------------------------+----------+-----------+-------------+--------------+---------------+
| ステータス | 期間 | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+-----------+-------------+--------------+---------------+
| クエリ キャッシュ ロックを待機中 | 0.000016 | 0.000015 | 0.000001 | 0 | 0 |
| クエリのクエリ キャッシュを確認しています | 0.000007 | 0.000007 | 0.000000 | 0 | 0 |
| キャッシュされた権限をチェックしています | 0.000004 | 0.000003 | 0.000000 | 0 | 0 |
| 権限を確認しています | 0.000034 | 0.000033 | 0.000001 | 0 | 0 |
| キャッシュされた結果をクライアントに送信 | 0.000018 | 0.000017 | 0.000001 | 0 | 0 |

mysql> 'QCache\_%' のようなグローバル ステータスを表示します。
+-------------------------+-----------+
| 変数名 | 値 |
+-------------------------+-----------+
| Qcache_free_blocks | 1 | --クエリ キャッシュ内の空きメモリ ブロックの数。
| Qcache_free_memory | 33268592 | --クエリ キャッシュの空きメモリの量。
| Qcache_hits | 121 | --QC から結果セットが取得された回数。
| Qcache_inserts | 91 | -- クエリ結果セットが QC に追加された回数。つまり、クエリは QC に存在しないことを意味します。
| Qcache_lowmem_prunes | 0 | -- メモリ不足のためクエリ キャッシュから削除されたクエリの数。
| Qcache_not_cached | 0 | --キャッシュされていないクエリの数。
| Qcache_queries_in_cache | 106 | --クエリ キャッシュに登録されているクエリの数。
| Qcache_total_blocks | 256 | -- クエリ キャッシュ内のブロックの合計数。

           Qキャッシュヒット
クエリキャッシュヒット率 = ------------------------------------------------ x 100%
      Qcache_hits + Qcache_inserts + Qcache_not_cached
      
        query_cache_size = Qcache_free_memory
クエリ キャッシュ平均クエリ サイズ = --------------------------------------- 
          Qcache_queries_in_cache