出典について
この記事はThe Percona Performance Blog内のAlexander Rubin氏による「Advanced Query Tuning in MySQL 5.6 and MySQL 5.7 Webinar: Q&A」(2015/8/24)を翻訳したものである。
8月22日のオンラインセミナー「MySQL 5.6および5.7における高度なクエリーチューニング」に参加していただいてありがとう(私のスライドおよび動画はここで確認できる)。ここでは質問とその回答の一覧を紹介する(いい質問をありがとう) 。
Q: ここにexplainの例がある
mysql> explain extended select id, site_id from test_index_id where site_id=1
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_index_id
type: ref
possible_keys: key_site_id
key: key_site_id
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index
我々が"id"を選択していること、key_site_idはsite_idのみを含んでいることをふまえて、なぜsite_idがクエリーのカバリングインデックスとなるのだろうか?
テーブルがInnoDBなので、全てのセカンダリインデックスは主キー("id")を含む。このケースではセカンダリインデックスは上記のクエリーに必要な全ての情報を持っており、key_site_idが"カバリングインデックス"となる。
Q: アプリケーションは時と共に変わる。定期的にインデックスが利用されているかどうか確認し、使われてなかったら削除した方が良いか?もしそうであればどのようにすれば良いだろうか?
いい考えだ。Percona ToolkitまたはMySQL 5.6のperformance_schemaを使うと簡単にできる。
下記のいずれかである。
- スロークエリーログを有効化し各クエリーを記録し、pt-index-usageを利用する
- 下記のクエリーを発行する(FromDualのブログ投稿より)
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;
Q: MySQL 5.6/5.7での重複したインデックスはクエリー発行中のデータベースの性能への影響を引き起こすだろうか?
重複したキーはselectクエリーに負の影響を与えうる。
- MySQLが混乱し誤ったインデックスを選択する可能性がある
- インデックス合計容量が増え、MySQLがメモリ不足に陥る可能性がある
Q: どこにインデックスを作成するか知る為にクエリーの性能を計測する良い方法は(スロークエリーログ以外に)なにかあるか?
スロークエリーログは最も一般的な方法である。MySQL 5.6ではPerformance Schemaを使うことも、events_statements_summary_by_digestテーブルを使うこともできる。
Q: これがオンラインセミナーの範囲か分からないが...フルテキストインデックスのベストプラクティスはなにかあるか?
これはこのオンラインセミナーの範囲ではないが、フルテキストインデックスに関しては多数の発表をしてきた。例えばCreating Geo Enabled Applications with MySQL 5.6(MySQL 5.6における地理情報を有効にしたアプリケーションの作成)である。
Q: テーブルあたりで定義可能なインデックスサイズの最大値および、最大数の制限はいくつか?
ディスク上のインデックスサイズに制限はないが、アクティブなインデックスはメモリ上に収まる方が性能観点で良い。
InnoDBではインデックスサイズの制限があり、テーブルは最大64のセカンダリインデックスまで保持できる。
Q: テーブルに合計したい2つの列があった場合、その合計に対して計算済みのインデックスとしてインデックスを生成することができるか?それに加え計算済みインデックスは"case when"を持ちうるか?
明確にするために述べるが、これはMySQL 5.7(未リリース)の特徴である。
そのとおり、今やドキュメントに記載されている。
CREATE TABLE triangle (
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
Q: DayOfTheWeek(曜日)の様なカーディナリティーのとても低い列にインデックスを生成してしまった。通常は悪い例ということになるね?
その通りである! "select count(*) from .. where DayOfTheWeek = 7"のようなクエリを発行していない限りは、そのインデックスはあまり役に立たないだろう。
Q: 主キーを事前に指定しない場合、mysqlまたはinnodbがバックグラウンドで(隠れて)インデックスを作成するという記事をみた。where句の大部分がプライマリーキーまたは準プライマリーキーとして使われていない場合、それは主キーそのものとは異なるものだろうか?
"隠れ"主キーは6バイトで、全てのセカンダリキーにも付与(複製)される。隠れ主キーより小さいINT型の主キーをauto_incrementで作成しすることもできる(多くとも40億行しかデータを格納する予定がない場合に限る)。また、隠れ主キーはクエリ内で使うことができない。
次のクエリ(information_schemaに対するもの)は、プライマリーキーがないテーブル(つまり"隠れ"主キーがある)を全て抽出するのに利用できる。
SELECT tables.table_schema, tables.table_name, tables.table_rows
FROM information_schema.tables
LEFT JOIN (
SELECT table_schema, table_name
FROM information_schema.statistics
GROUP BY table_schema, table_name, index_name
HAVING
SUM(
CASE WHEN non_unique = 0 AND nullable != 'YES' THEN 1 ELSE 0 END
) = COUNT(*)
) puks
ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name
WHERE puks.table_name IS NULL
AND tables.table_type = 'BASE TABLE' AND engine='InnoDB'
mysql.innodb_index_statsテーブルも隠れプライマリーキーを探すのに利用できる。
例:
mysql> select * from mysql.innodb_index_stats;
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | t1 | GEN_CLUST_INDEX | 2015-08-08 20:48:23 | n_diff_pfx01 | 96 | 1 | DB_ROW_ID |
| test | t1 | GEN_CLUST_INDEX | 2015-08-08 20:48:23 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | GEN_CLUST_INDEX | 2015-08-08 20:48:23 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
Q: alter tableをインデックスを作成するのに使うことがあるが、インデックス作成時にMySQLはどのようにデータをソートしているのだろうか?一時テーブルをソートに使っているのだろうか?
とてもいい質問だ。"alter table ... add index"文の挙動は時と共に変化してきた。オンラインDDLの概要にドキュメントがある。
歴史的に、InnoDBのテーブルに対する多くのDDL操作はコストが高いものであった。多くのALTER TABLE操作は、新しい空のテーブルをリクエストされたオプションおよびインデックスを持つように定義し、既存の行を1つ1つ新しいテーブルにコピーし、行が挿入されるとインデックスを更新していた。元のテーブルの全ての行がコピーされた後、古いテーブルは破棄され、コピーしたテーブルが元のテーブル名称にリネームされる。
MySQL 5.5およびInnoDBプラグインが入っているMySQL 5.1では、CREATE INDEXおよびDROP INDEXはテーブルコピーをしないように改善されている。この機能はFast Index Creationとして知られる。
MySQLが"Fast Index Creation"を利用する場合は、MySQLの一時ディレクトリに一連の一時ファイルを作成する。
セカンダリインデックスを既存のテーブルに加える場合は、InnoDBはテーブルをスキャンし、メモリバッファと一時ファイルを使って、行をセカンダリインデックスのキー順にソートする。それからB-treeがキーバリュー順で作成され、これはインデックスがランダムである状態で行を挿入するよりはるかに効率が良い
Q: InnoDBのデッドロックは5.6に比べて5.7はどの程度改善されているのだろうか。これはパラメータの設定に依存するのだろうか?
InnoDBのデッドロックに関するディスカッションは本発表の範囲外である。Valerii Kravchuk およびNilnandan JoshiがPercona Live 2015ですばらしい講演をしている(スライドが利用可能である): InnoDBのロックおよびデッドロックを理解する
Q: 6600万レコードあるテーブルがある。これに仮想列を作り、インデックスをはるのにはどの程度の性能上の影響があるだろうか。またどのようにすれば良いだろうか?物理ディスク上のインデックスを再構成することに関して何かアドバイスはあるだろうか?
MySQL 5.7はまだリリースされていないため、仮想列の挙動は変わりうる。ここでの主な疑問点は、次の操作がオンラインでできるかどうかである。
- 仮想列の追加(これはメタデータの変更のみであり、非常に軽量の操作なはずのため)
- 仮想列へのインデックス生成
lab版のリリースではオンラインではないが、変更の可能性はある。
参加いただいたことに重ねてお礼申し上げる。
