社内勉强会：ソーシャルゲームのデータベース设计入门
7 likes4,177 views
The document discusses database indexing and optimization. It explains the concepts of primary keys, covering indexes, and read/write operations. It also provides examples of using indexes to optimize queries and discusses how MySQL handles read and write operations through its binary log and relay log.
More Related Content
More from Naoyuki Yamada (18)
Recently uploaded (20)
社内勉强会：ソーシャルゲームのデータベース设计入门
- 1. 2011 3 4 2011 3 5
- 2. ? DB ? Index Index Covering Index Primary Key Read Write ? MySQL DB SQL 2011 3 5
- 3. ? →(10001,A) ※ ? Primary Key →Primary Key ? Primary Key 2011 3 5
- 4. ? ID ID 1 1 2 2 2 2011 3 5
- 5. ? ID ID 1 10001 1 10002 2 10001 ID ID 1 10001 2 10002 1 10002 2 10001 2 10002 ID 10001 10002 2011 3 5
- 6. ? ID ID 1 101 2 102 ID ID 3 103 1 101 4 102 2 102 3 103 4 102 ID 101 102 103 2011 3 5
- 7. ALTER TABLE ? ? (ALTER TABLE) →ALTER TABLE ? ? → 2011 3 5
- 8. ID ID 1 1 101 1 1 2 234 2 1 3 553 3 1 4 232 4 2 1 102 2 2 271 2 3 952 ID 1 101 234 553 232 873 0 102 0 2 102 271 8652 98 0 234 202 0 3 103 952 76 765 872 45 0 0 4 102 197 2312 762 4 232 672 34 ? 2011 3 5
- 9. Index ? WHERE Index Primary Key WHERE user_id = 10001 → user_id Index( Primary Key) WHERE user_id = 10001 AND friend_id = 10002 → (user_id, friend_id) WHERE visiting_id = 10001 AND visited_id IN (1,2,3) AND action=2 → (visiting_id,visited_id,action) WHERE status = 2 ORDER BY start_time → (status,start_time) 2011 3 5
- 10. Index ? Index WHERE start_time < 12345678 AND state = 2 →(start_time,state) (state,start_time) ? Index WHERE visited_id = 10002 AND visiting_id = 10001 AND action=2 → (visiting_id,visited_id,action) ? Index WHERE visiting_id = 10001 AND action = 2 AND visited_id = 10002 → (visiting_id,visited_id,action) Index visiting_id 2011 3 5
- 11. Covering Index ? MySQL Covering Index - ( ? )o sasata299's blog http://blog.livedoor.jp/sasata299/archives/51336006.html ? Covering Index → 2011 3 5
- 12. Primary Key id ? Primary Key → Primary Key MySQL id Primary Key MySQL :: MySQL 5.1 :: 13.5.13 InnoDB http://dev.mysql.com/doc/refman/5.1/ja/innodb-table-and-index.html ? id WHERE id IN (a,b,c,d...) ? id id Primary Key ( Covering Index ) 2011 3 5
- 13. Primary Key ? id Primary Key 1 Primary Key ? id ? id Primary Key (“WHERE user_id=10001” ) 2011 3 5
- 14. auto_increment ? ID CREATE TABLE seq_history( seq_id bigint unsigned not null ); ? auto_increment MySQL5.1 DB auto_increment 2011 3 5
- 15. Read Write ? Read(SELECT) DB Write DB DB ? Write SELECT ? SELECT ? Read Write 2011 3 5
- 16. ? Read/Write SQL ? IO → → 2011 3 5
- 17. MySQL ? DB DB DB DB DB (3)Relay Log (1) (INSERT,UPDATE ) SQL Binary Log SQL IO Binary Log Relay Log (2)IO Binary Log DB 2011 3 5
- 18. DB ? DB DB →MySQL 1 ? → SSD cf. 2011 3 5
- 19. ? NG UPDATE history_tbl SET state = 2 WHERE state = 1 LIMIT 10 →state=1 UPDATE history_tbl SET state = 2 ORDER BY finish_time LIMIT 1 → ?nish_time ? UPDATE WHERE Primary Key 2011 3 5