第一份工作持續了兩年多，雖然所在的單位系統已經上線 N 年了，非常穩定，剩餘的工作都是維護居多。

但因為人數持續增長，也陸續面臨了各種奇怪的挑戰，常常要翻 LOG 查問題跟上面的人報告可以有哪些解法。

在找這些問題學到了不少，加上常常需要跟 DBA 還有 DC 打交道，多少增廣見聞。

礙於全部的筆記跟資料都留在公司電腦了，趁著休息的這段時間，
剛好慢慢回顧一下兩年來的一些心得，並陸續整理在這篇文章。

一些工作經驗回顧

機器選擇

Q: 如何評估新服務機器量

A: 估當前需求，評估使用次數，讀得多，寫得多，呼叫頻率為何?是否離峰時段跟尖峰差距很大。
再評估未來一到兩年需求可能會增長的量，各別壓測WEB與DB，看是否會遇到瓶頸，決定好後把服務簡單搭起來完整壓測 WEB+DB 再次評估。

單台 WEB 機器+單台 DB 機器，WEB 遇到瓶頸:

解決方案:
1.加 WEB 機器，導入利用 nginx 做 loadbalance

優點: 簡單、便宜
缺點: 未來瓶頸變成 MYSQL、擴展性低、可用性低

2.拆服務、拆機器

優點: 性能高、擴展性高，未來可以依功能拆服務依服務加機器、可用性高
缺點: 須對服務本身拆分難度高、WEB 機與 DB 數量需要較多，較昂貴

機器的 loadbalance:

有錢: 利用機器例如 F5 在Layer4(連線層)、Layer7(應用層)做掉
沒錢: Nginx

NGINX 方式有幾種算法
1.round-robin(default)
2.least connected load balancing
3.weighted load balancing
4.ip_hash

其中1~3方法無法保證同個使用者用到同一台。
另外曾經遇到使用最少連線時未設定上限，
導致瞬間大流量全部灌到同一台上掛掉，故設定上要小心。

http://mysql.taobao.org/monthly/
http://nginx.org/en/docs/http/load_balancing.html
https://www.ithome.com.tw/node/65773

其他:

worker_connections 、
client_max_body_size、
keepalive_timeout…

資料庫:

MYSQL

loadbalance
開服務之前可跑 warmup
sharding 可利用id或是地區等
connection pool 減少開連線成本
依照業務拆DB，但MYSQL要考量JOIN與跨DB transactions問題
拆表

垂直分表，一張表中有很多欄位不常用，將欄位拆出去，但要使用完整資料可能會多一道手續
水平分表
- 以 user_id，timestamp 之類去分範圍儲存，例如11000、10012000。
- 一開始就切好數量，並以 user_id 之類的值去 hash 來決定儲存的表，但之後再次遇到問題會比較麻煩。
- 水平拆分後要 join 需多次 join 後再合併。
- count 也要進行相對多次，解決方法可以再開一張表紀錄更新某類型的表總數，但如果這個值是需要很精確一值頻繁更新的話會對DB造成另一個負擔。
- order by 也要分很多段。

定期切+清理 partition
可以把一些固定性的資料例如明細或報表等只保留幾個月，由程式定期將舊資料移至 column base database 例如 infobright，撈取大量資料或是老舊資料時切換連線即可，優點是SELECT非常快速，但缺點是要進行 UPDATE 或 DELETE 非常非常慢。

Redis

warmup，開服務前可先將固定已知資料預先塞入(如全球IP等)。
redis cluster 缺點是無法直接scan，需要分台掃
sharding，缺點是單線程沒備份掛掉很麻煩，最好是放置消失也沒關係的資料
connection pool
要救回資料有幾種，有固定或可回推資料可以從db或log建回來，否則可考慮使用RDB、AOF設定來還原，RDB還是寫在memory中所以機器也掛的話一樣無法復原，優點是機器沒掛的話復原很快，且檔案小。AOF備份的話優點是每次寫入成功後就將該命令寫到檔案中，但缺點是檔案大，還原時間長。Redis 4.0 之後提供 RDB-AOF 混用的方案。

MYSQL 優化

欄位型態是否合適
- 數字:
  bigint(8byte)、int(4byte)、smallint(2byte)、tinyint(1byte)
  曾遇到使用int但 auto_increment 頂到上限，後來改成 bigint unsinged 實際範圍變成 1 ~ 2^64-1。
  但是相對的若是不需要那麼大則使用tinyint即可，例如年齡，boolean等。
  另外能用數字存的盡量別用字串。
- 字串:
  char、varchar、tiniytext、text、mediumtext，
  其中 char 為固定大小指定多少就是多少不夠會補空白，適合用在確定長度的資料。

其他:

char 是固定的，開多大就是多大。
varchar 則是浮動的，

以 latin1 編碼下每個字元為 1byte 來比較，結果如下

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
‘’	’ ’	4 bytes	‘’	1 byte
‘ab’	'ab ’	4 bytes	‘ab’	3 bytes
‘abcd’	‘abcd’	4 bytes	‘abcd’	5 bytes
‘abcdefgh’	‘abcd’	4 bytes	‘abcd’	5 bytes

但通常資料庫會使用 utf8mb4_unicode_ci 以應付一些表情符號等，而 utf8mb4 一個字是 4byte，mysql varchar 最大長度為64k(2^16-1 = 65535 byte)，考量到使用 utf8mb4，最多只能開到 VARCHAR(16383)。

https://dev.mysql.com/doc/refman/8.0/en/char.html
https://matthung0807.blogspot.com/2019/06/mysql-varcharmaximum-length.html
https://stackoverflow.com/questions/332798/equivalent-of-varcharmax-in-mysql/332805#332805

SLOW QUERY 查詢

LOG 分析工具 pt-query-digest(https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html)
使用 EXPLAIN + SQL_NO_CACHE 觀察

Type Possible keys Key Rows Scanned Duration (seconds) Extra info Rows returned

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_extra
https://www.awaimai.com/2141.html

id:執行順序，id越大的越先被完成執行
select_type:
table 哪張表執行的，也有可能為以下命名。
– unionM,N: The row refers to the union of the rows with id values of M and N.

– derivedN: The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.

– subqueryN: The row refers to the result of a materialized subquery for the row with an id value of N. See Section 8.2.2.2, “Optimizing Subqueries with Materialization”.
partitions
type: const > eq_reg > ref > range > index > ALL。
possible_keys:可用索引
key:實際用的index，沒有中的話就是NULL
key_len: INDEX 長度，通常越短越好
ref: All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.
rows:查詢時使用到的行數
filtered:需要讀到的row占總數百分比
extra: 通常都先用這個當指標來優化，主要看有沒有 filesort 跟 temporary 產生。
- 當出現 Using filesort 代表query中做了額外的 sort，可能是 order by 跟 index 條件相衝。
- 當出現 Using temporary 代表query過程中需要開臨時表，通常發生在 order by 與 group by 條件用到不同欄位時。
ignore index、force index
曾經遇到 mysql，選擇判斷使用 merge index 後反而比較慢，這時候可以視情況拔掉 index，或是改為比較快的 index。

https://blog.gslin.org/archives/2014/01/16/4140/index-下的不好會對-mysql-的-index-merge-產生負面效果/
https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/

搜尋此網誌

筆記

Markdown

[心得]一些工作經驗回顧