MySQL High Performance Tuning Guide in Udemy Section 6

MySQL High Performance Tuning Guide in Udemy Section 6

36. Motivation

• 複合キーを使うときのよくある間違いは、間違った順序で列にインデックスを付けたり、それぞれにインデックスを付けたりすること

SELECT id, name, district, population
FROM world.city
WHERE countrycode = 'DEU' AND population > 1000000;

• countrycodeとpopulationそれぞれにindexが貼ってある場合、それぞれで絞った場合にどのくらい絞り込めるかを統計情報から判断してどっちかのindexを使うか決めることになるけど、複合インデックスを使えばもっと効率良くなる
• (countrycode, population)を作る

英語

• pitfall
  • 隠れた危険、わな
• orders of magnitude
  • 桁違い
  • can be many orders of magnitude slower than ...
    • クソ桁違いに遅くなるよ

37. Columns Order

SELECT id, name, district, population
FROM world.city
WHERE countrycode = 'DEU' AND population > 1000000;

• この例では(countrycode, population)の順番でOK
  • contrycodeは=の条件で検索しているから、より絞り込めることが予想できるから
• contrycode = 'DEU' AND population = 1000000 の場合はどっちの順番でインデックスを貼るか考えないといけない
• 観点としては、どっちのインデックスがよく使われるか、どっちがよくデータを絞り込めるか

CREATE INDEX idx on tab_1 (col_a, col_b, col_c);

①SELECT * FROM tab_1 WHERE col_a = 'val1' AND col_b = 'val2';

②SELECT * from tab_1 WHERE col_b = 'val2';

①はidxが使えるけど、②はidxが使えない。①はcol_c分は余計になってるけどcol_aとcol_bが使える
また、col_bを検索するのにcol_aが透過条件なら使える

38. Commposite Index Hands-on

• 37の解説内容をベンチマーク取って結果を見るだけなので省略

39. Using Redundant Indexes

• redundant indexe は duplicate indexとは違う
• (col_a, col_b) があるとき
  • (col_a)はredundant
  • (col_b, col_a)はnot redundant
  • (col_b)はnot redundant
• (col_a, col_b)は(col_a)としても機能するから
• redundantでもフルテキストインデックスなどは無駄じゃない場合もある

① SELECT count(*) FROM userinfo WHERE state_id = 5;
② SELECT state_id, city, address FROM userinfo WHERE state_id = 5;

• どちらにも有効なindexは(state_id, city, address)

40. Composite Indexes Extra Remarks

• redundant indexは消したらいい

select * from sys.schema_redundanta_indexes;

• でredundant indexを確認できる

• col_aでフィルタしながらcovering indexでprimary keyも返したい場合はidx(col_a)だけ定義すれば良い

• 理由は、secondary indexはすでにprimary keyをデフォルトで含むように作られるから

41. Conclusions

省略