MySQLのキャッシュ

SQL文をキャッシュに入れる

• InnorDBとMyISAMの区別及び長所/短所
  • ロック方式：　InnoDBは行ロック、MyISAMはテーブルロック方式
  • チューニング：　InnoDBではinnodb_buffer_pool_sizeが一番大事で、MyISAMではデータページのキャッシュを管理しないため、key_buffer_sizeでの管理が大事。（InnoDBではbuffer_pool_sizeを使用可能なメモリ80%、MyISAMの場合はkey_buffer_sizeを使用可能なメモリの25%が目安）
  • 性能：　MyISAMはシンプルのため高速（リード）ですが、InnoDBとの差が大きくない現状です。
  • 壊れやすさ：　MyISAMが壊れやすい、InnoDBが壊れにくいです。
  • 全文検索：　MyISAMはFULLTESTインデックスのため、全文検索に対応していますが、データ量が多ければ多いほど、全文検索が遅くなる記事があります。
• MyISAMがリードが速いと考えられますが、MyISAMのキャッシュ機能は「キーキャッシュ」（key cache、key_buffer_sizeにキーをセットする）といいます。このキャッシュが.MYIファイルからページインデックスのみをキャッシュし、データページをキャッシュしないことです。
• InnoDB　Buffer Poolがデータとインデックスページをキャッシュします。すべてのデータセットをRAMにキャッシュすることが可能です。またはメモリのサイズより、InnoDBを80%、コネクションを10%、OSに残りの10%を設定することも可能です。
• 以前使用したSQL文をキャッシュして再利用することができます
  • 以下のSQL文で以前使用したクエーリを抽出する（InnoDB、MyISAM共に利用可能）
    

    SELECT DISTINCT
        CONCAT('SELECT ',ndxcollist,' FROM ',
        db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM (
        SELECT
            engine,table_schema db,table_name tb,index_name,
            GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (SELECT engine,table_schema,table_name
                FROM information_schema.tables
                WHERE engine IN ('InnoDB','MyISAM')) B
                USING (table_schema,table_name)
            WHERE
                B.table_schema NOT IN ('information_schema','mysql')
                AND A.index_type <> 'FULLTEXT'
            ORDER BY
                table_schema,table_name,index_name,seq_in_index
            ) A
        GROUP BY
            table_schema,table_name,index_name
    ) AA
    ORDER BY
        engine DESC,db,tb
    ;
    

• 上記抽出した結果をファイルに保存する（例：/root/MakeSelectQueriesToLoad.sql）

  mysql -u... -p... -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql

• 上記保存SQL文を再度実行します

  mysql -u... -p... < /root/SelectQueriesToLoad.sql

• 結果として、これらのSQL文をInnoDBのBuffer Pool/MyISAMのKey Casheにプリロードされる
• もしInnoDBの場合に以下の命令を実行して、データページをInnoDB Buffer Poolに入れることができる

  replace WHERE engine IN ('InnoDB','MyISAM') with WHERE engine='InnoDB';
  replace CONCAT('SELECT ',ndxcollist,' FROM ', with CONCAT('SELECT * FROM ',