社内勉强会：ソーシャルゲームのデータベース设计入门
?
7 likes?4,165 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.
社内勉强会：ソーシャルゲームのデータベース设计入门
- 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