Modern Web 2016
恰如其分的 MySQL 設計技巧
Ant
yftzeng@gmail.com
2016-08-24
引用去年簡報
Slide 68
Slide 68 text
68/120
Business
License
Elastic business
Workload
Technology
Scale-up
Application
Connection
Database
File system
OS Kernel
Hardware
Scale-out
Replication
Clustering
Sharding
Disaster Recovery
Multi Regional Resiliency
CONSILIENCE
Architecture
and more ...
引用去年簡報
Slide 69
Slide 69 text
69/120
當業務需求變更程式設計時
預想但不過早調優
總工程師不該把每次新需求都認為獨立需求
( 多想二分鐘，團隊可以不必自虐 )
Elastic business
引用去年簡報
Slide 70
Slide 70 text
70/120
預想但不過早調優
Slide 71
Slide 71 text
71/120
Premature optimization is the root of all evil.
過早最佳化是萬惡的根源
Slide 72
Slide 72 text
72/120
Premature optimization is the root of all evil.
過早最佳化是萬惡的根源
手拿菜刀砍電線，一路火花帶閃電
Slide 73
Slide 73 text
73/120
Premature optimization is the root of all evil.
過早最佳化是萬惡的根源
手拿菜刀砍電線，一路火花帶閃電
Slide 74
Slide 74 text
74/120
We should forget about small efficiencies,
say about 97% of the time:
Premature optimization is the root of all evil.
Yet we should not pass up our opportunities in that critical 3%.
Slide 75
Slide 75 text
75/120
We should forget about small efficiencies,
say about 97% of the time:
Premature (micro) optimization is the root of all evil.
Yet we should not pass up our opportunities in that critical 3%.
Slide 76
Slide 76 text
76/120
Premature (micro) optimization is the root of all evil.
Do some { Architectural optimizations, Algorithms, … } early.
Slide 77
Slide 77 text
77/120
Premature (micro) optimization is the root of all evil.
Do some { Architectural optimizations, Algorithms, … } early.
程式與架構需懂得區分
【架構】異動會牽扯組件邊界，影響巨大
Slide 78
Slide 78 text
78/120
Premature (micro) optimization is the root of all evil.
Do some { Architectural optimizations, Algorithms, … } early.
即未來需求變更時，屬程式異動，還是架構異動？
程式與架構需懂得區分
【架構】異動會牽扯組件邊界，影響巨大
簡言之，如果需求發生異動，需花多久時間滿足？
Slide 79
Slide 79 text
79/120
狀態
原表格設計
Elastic business
id name ... is_deleted
1 Apple ... 0
2 Banana ... 1
引用去年簡報
Slide 80
Slide 80 text
80/120
狀態
新業務需要儲存「鎖定」狀態
Elastic business
id name ... is_deleted
1 Apple ... 0
2 Banana ... 1
id name ... is_deleted is_locked
1 Apple ... 0 1
2 Banana ... 1 0
引用去年簡報
Slide 81
Slide 81 text
81/120
狀態
其實若狀態是沒交集的，則可以合併
Elastic business
id name ... status
1 Apple ... 2
2 Banana ... 0
id name ... is_deleted is_locked
1 Apple ... 0 1
2 Banana ... 1 0
{ 0: deleted, 1: enabled, 2: locked }
引用去年簡報
Slide 82
Slide 82 text
82/120
標籤雲
原表格設計
Elastic business
id name tag1
1 Apple admin
2 Banana reporter
3 Cherry reporter
SELECT * FROM {Table}
WHERE tag1 = ‘admin’
引用去年簡報
Slide 83
Slide 83 text
83/120
標籤雲
新增標籤
Elastic business
id name tag1 tag2 tag3
1 Apple admin reporter programmer
2 Banana reporter programmer NULL
3 Cherry reporter admin NULL
SELECT * FROM {Table}
WHERE (tag1 = ‘admin’ OR tag2 = ‘admin’ OR tag3 = ‘admin’)
AND (tag1 = ‘reporter’ OR tag2 = ‘reporter’ OR tag3 = ‘reporter’)
SELECT * FROM {Table}
WHERE ‘admin’ IN (tag1, tag2, tag3)
AND ‘reporter’ IN (tag1, tag2, tag3)
ALTER TABLE !!
引用去年簡報
Slide 84
Slide 84 text
84/120
Tag
Elastic business
id tag
1 admin
1 reporter
1 programmer
2 reporter
... ...
新增標籤 ( 另他法 )
標籤雲
id name X X X
1 Apple X X X
2 Banana X X X
SELECT * FROM {Table}
INNER JOIN ‘Tag’ AS t1 USING (id)
INNER JOIN ‘Tag’ AS t2 USING (id)
WHERE t1.tag = ‘admin’
AND t2.tag = ‘reporter’
引用去年簡報
Slide 85
Slide 85 text
85/120
Elastic business
或是 M:N
標籤雲
id name
1 Apple
2 Banana
id tag_id
1 1
1 2
1 3
2 2
2 3
tag_id name
1 admin
2 reporter
3 programmer
引用去年簡報
Slide 86
Slide 86 text
86/120
Elastic business
廣告需求
營運有新的需求，受眾在
一天內不要看到相同廣告。
瞭解，預計一個工作天。
第一天
引用去年簡報
Slide 87
Slide 87 text
87/120
Elastic business
廣告需求
營運有新的需求，受眾在
小時內不要看到相同廣告。
瞭解，預計二個工作天。
第二天
時間粒度不同
引用去年簡報
92/120
Business
License
Elastic business
Workload
Technology
Scale-up
Application
Connection
Database
File system
OS Kernel
Hardware
Scale-out
Replication
Clustering
Sharding
Disaster Recovery
Multi Regional Resiliency
CONSILIENCE
Architecture
and more ...
引用去年簡報
Slide 93
Slide 93 text
93/120
Workload
Processing Intensive Capacity
CPU intensive
Memory intensive
Storage/IO intensive
Bandwidth intensive
OLTP
OLAP
Data warehouse
Throughput
Latency
Memory footprint
Service-level agreement
Bond
Performance
Security
Cost restriction
引用去年簡報
Slide 94
Slide 94 text
94/120
Workload
Processing Intensive Capacity
CPU intensive
Memory intensive
Storage/IO intensive
Bandwidth intensive
OLTP
OLAP
Data warehouse
Throughput
Latency
Memory footprint
Service-level agreement
Bond
Performance
Security
Cost restriction
引用去年簡報