More Related Content
Viewers also liked (11)
More from Shuhei Iitsuka (20)
テ?ータヘ?ースを使おう
- 1. データベースを使おう InnoDB ?でやるお (＾ω＾)
- 2. テスト用にデータベースを ? 用意しました ?? friend ?(約 1000 ?万件) ? –? user_id ?VARCHAR(128) ? –? friend_id ?VARCHAR(128) ? –? … ? –? PRIMARY ?KEY ?(user_id, ?friend_id) ? –? UNIQUE ?KEY ?(friend_id, ?user_id) ? ?? user ?(約 100 ?万件) ? –? id ?VARCHAR(128) ? –? name ?VARCHAR(256) ? –? gender_id ?INT ?UNSIGNED ? –? lang ?VARCHAR(10) ? –? created_at ?DATETIME ? –? … ? –? PRIMARY ?KEY ?(id) ?
- 3. 試してみよう ?? 普通の SELECT ?文 ? –? SELECT ?* ?FROM ?user ?LIMIT ?10; ? –? SELECT ?* ?FROM ?user; ? ?? 条件指定 ? –? SELECT ?* ?FROM ?user ?WHERE ?id ?= ? '109092915251428393573'; ? –? SELECT ?* ?FROM ?user ?WHERE ?name ?= ?'天野仁史'; ? –? SELECT ?id, ?name ?FROM ?user ?WHERE ?created_at ?> ? DATE_SUB(NOW(), ?INTERVAL ?10 ?MINUTE); ? ?? 同姓同名ランキング ? –? SELECT ?user.name, ?COUNT(id) ?FROM ?user ?GROUP ?BY ? user.name ?ORDER ?BY ?COUNT(id) ?DESC ?LIMIT ?10; ?
- 4. 試してみよう ?? 従業員数ランキング ? –? SELECT ?company.id, ?company.name, ?COUNT(user_employment.id) ? FROM ?user_employment ?LEFT ?JOIN ?company ?ON ? user_employment.company_id ?= ?company.id ?GROUP ?BY ?company.id ? ORDER ?BY ?COUNT(user_employment.id) ?DESC ?LIMIT ?10; ? ?? 企業の従業員一覧 ? –? SELECT ?user.id, ?user.name ?FROM ?user ?LEFT ?JOIN ?user_employment ? ON ?user.id ?= ?user_employment.user_id ?LEFT ?JOIN ?company ?ON ? user_employment.company_id ?= ?company.id ?WHERE ?company.name ? = ?'Google'; ? ?? 共通の友達 ? –? SELECT ?* ?FROM ?friend ?AS ?f1 ?LEFT ?JOIN ?friend ?AS ?f2 ?ON ?f1.friend_id ?= ? f2.user_id ?WHERE ?f1.user_id ?= ?'109092915251428393573' ?AND ? f2.friend_id ?= ?'113100517422007103669' ?
- 5. 想像してみよう Id name gender_id lang created_at 1 佐藤太郎 1 ja 2011-?‐01-?‐01 ?22:32:44 2 鈴木花子 0 en 2011-?‐01-?‐01 ?23:32:44 3 山田二郎 1 id 2011-?‐01-?‐03 ?22:32:44 4 天野仁史 1 zh-?‐cn 2011-?‐01-?‐05 ?22:32:44 5 飯塚修平 1 zh-?‐tw 2011-?‐01-?‐06 ?22:32:44 … … … … 123013810 猫ひろし 1 en ? 2011-?‐12-?‐31 ?22:32:44 ? SELECT ?* ?FROM ?user ?WHERE ?created_at ?< ?'2011-?‐08-?‐01' ?LIMIT ?10; ?
- 6. 想像してみよう Id name gender_id lang created_at 1 佐藤太郎 1 ja 2011-?‐01-?‐01 ?22:32:44 10 ?件見つかるま 2 鈴木花子 0 en 2011-?‐01-?‐01 ?23:32:44 で探すぜ！ ? 3 山田二郎 1 id 2011-?‐01-?‐03 ?22:32:44 4 天野仁史 1 zh-?‐cn 2011-?‐01-?‐05 ?22:32:44 5 飯塚修平 1 zh-?‐tw 2011-?‐01-?‐06 ?22:32:44 … … … … 123013810 猫ひろし 1 en ? 2011-?‐12-?‐31 ?22:32:44 ? SELECT ?* ?FROM ?user ?WHERE ?created_at ?< ?'2011-?‐08-?‐01' ?LIMIT ?10; ? 遅いよね
- 7. 想像してみよう 8 Id name 2 ?分木で 2 ?分探 gender_id lang created_at 1 佐藤太郎 1 ja 2011-?‐01-?‐01 ?22:32:44 索するぜ！ ? 2 鈴木花子 0 en 2011-?‐01-?‐01 ?23:32:44 4 12 3 山田二郎 1 id 2011-?‐01-?‐03 ?22:32:44 4 天野仁史 1 zh-?‐cn 2011-?‐01-?‐05 ?22:32:44 2 6 5 飯塚修平 1 zh-?‐tw 2011-?‐01-?‐06 ?22:32:44 … … … … 123013810 猫ひろし 1 en ? 2011-?‐12-?‐31 ?22:32:44 ? SELECT ?* ?FROM ?user ?WHERE ?created_at ?< ?'2011-?‐08-?‐01' ?LIMIT ?10; ? あたまいいね
- 8. インデックスの仕組み ?? B+ ?木 ? –? 探索が速くなる ? ?? O(n/2) ?→ ?O(log(n)) ? –? 挿入は遅くなる ? ?? O(1) ?→ O(log(n)) ? wikipedia: ?B+ 木から ?? ハッシュ関数を使う場合もあるよ ? –? 検索も挿入も O(1) ? –? 範囲検索はできない ?
- 9. 複合インデックス ?? 複数のカラムに条件を指定して探索したい場 合は、複合インデックスを使う ? ?? 二つのキーを、上下の桁として合わせた値に 対して木が作られるので、順番が大切 ? –? どちらが上の桁で、下の桁になるかが重要 ?
- 10. EXPLAIN ?? select_type ? –? サブクエリの種類 ? –? DEPENDENT ?SUBQUERY, ?UNCACHEABLE ?SUBQUERY ?は要改善 ? ?? type ? –? const ? ?? primary ?key ?や unique ?index ?を探索に使う ? –? eq_ref ? ?? JOIN ?に primary ?key ?や unique ?index ?を使ってる ? –? range ? ?? インデックスを使った範囲 ? –? ref ? ?? インデックス使ってる ? –? index ? ?? フルインデックススキャン。要改善 ? –? ALL ? ?? フルテーブルスキャン。要改善 ? ?? key ? –? 実際に使われるインデックス ?
- 11. DEPENDENT ?SUBQUERY ?? WHERE ?IN ?(SUBQUERY) ?は遅い ? –? このクエリが ? ?? SELECT ?user_id ?FROM ?friend ?WHERE ?user_id ?IN ?(SELECT ? friend_id ?FROM ?friend ?WHERE ?user_id ?= ?' ? 109092915251428393573 ?') ?AND ?friend_id ?= ?' ? 113100517422007103669'; ? –? 以下のクエリに変更される ? ?? SELECT ?user_id ?FROM ?friend ?AS ?f1 ?EXISTS ?(SELECT ?1 ?FROM ? friend ?AS ?f2 ?WHERE ?f2.user_id ?= ?' ?109092915251428393573 ? ' ?AND ?f2.friend_id ?= ?f1.user_id) ?AND ?f1.friend_id ?= ? '113100517422007103669'; ? ?? hmp://nippondanji.blogspot.com/2009/03/ mysql_25.html ?を参考に
- 12. トランザクションを知ろう ?? 処理途中の中途半端な状態が、影響しないようにすること ? –? アトミック性 (Atomicity) ? ?? トランザクションに含まれるタスクが全て実行されるか、あるいは全く実行され ないことを保証する ? –? 一貫性 (Consistency) ? ?? トランザクション終了時に与えられたルールを守っている。 ? ?? 外部キー制約など ? –? 分離性 (Isolaoon) ? ?? 処理が直列化されている ? ?? 速度が犠牲になるため、独立性を一部しか実装しないことが多い ? –? トランザクション分離レベル ? –? 永続性 (Durability) ? ?? クラッシュしても一度コミットされたデータは復元できる ? (wikipedia: ?ACID ?から) ?
- 13. トランザクション分離レベル ?? SERIALIZABLE ? –? 完全に分離性を実現 ? ?? REPEATABLE ?READ ?(InnoDB ?のデフォルト) ? –? 一度、読んだデータが変更されることがない ? –? ファントムリード ? ?? READ ?COMMITTED ? –? 常にコミット済みのデータのみを読み取る ? –? ファントムリード ? –? ファジーリード ? ?? READ ?UNCOMMITTED ? –? ファントムリード ? –? ファジーリード ? –? ダーティリード ?
- 14. 異常な読み込み ?? ダーティリード ? –? 他のトランザクションが ROLLBACK ?したはずなの に、読めてる ? ?? ファジーリード ? –? 同じ行を 2 ?回読んだとき、その間に他のトランザ クションが値を変更したのが読めてしまう ? ?? ファントムリード ? –? 同じ条件で行を読んだのに、他のトランザクション による挿入で、１回目と２回目で結果が変わる
- 15. ロック ?? InnoDB ? –? X ?(排他)ロック ? ?? DELETE, ? ?UPDATE, ? ?INSERT ?, ? ?SELECT ?FOR ?UPDATE ? –? S ?(共有)ロック ? ?? SELECT ?… ?FROM ?… ?LOCK ?IN ?SHARE ?MODE ? –? 一貫非ロック読み取り ? ?? SELECT ?… ?FROM ?…
- 16. ロックの监视 ?? /etc/my.cnf ? ignore-?‐builtin-?‐innodb ? plugin-?‐ load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innod b_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;inn odb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_re set=ha_innodb_plugin.so ? SELECT ?* ?FROM ?information_schema.INNODB_LOCKS; ?