目錄

生產(chǎn)環(huán)境Redis中的熱點(diǎn)key如何發(fā)現(xiàn)并優(yōu)化蹲蒲？

有哪些常見的核心的MySQL應(yīng)用開發(fā)規(guī)范?

高可用架構(gòu)MHA有什么樣的不足和風(fēng)險點(diǎn)嗎翔悠？

為什么pt-osc操作表以后中文注釋顯示???添履，如何避免郁轻？

MySQL 5.6升級5.7都有什么注意事項(xiàng)

在用阿里云泥兰、騰訊云等公有云時庄涡，你是如何評估新建主機(jī)/數(shù)據(jù)庫對象的配置級別量承？

ALTER TABLE 出現(xiàn)duplicate primary xxx報錯的原因及處理？

InnoDB在什么情況下會觸發(fā)檢查點(diǎn)(checkpoint)?

update t set a=29 and b in (1,2,3,4);這樣寫有什么問題嗎穴店？

一撕捍、生產(chǎn)環(huán)境Redis中的熱點(diǎn)key如何發(fā)現(xiàn)并優(yōu)化？

1泣洞、用戶消費(fèi)的數(shù)據(jù)遠(yuǎn)大于生產(chǎn)的數(shù)據(jù)（熱賣商品忧风、熱點(diǎn)新聞、熱點(diǎn)評論球凰、明星直播）

2狮腿、請求分片集中腿宰，超過單Server的性能極限。

熱點(diǎn)key可能造成如下問題：

1缘厢、流量集中吃度，達(dá)到物理網(wǎng)卡上限

2、請求過多昧绣，緩存分片服務(wù)被打垮

3规肴、DB擊穿，引起業(yè)務(wù)雪崩

如何發(fā)現(xiàn)熱點(diǎn)key：

1夜畴、通過tcpdump抓包拖刃，可以分析抓取到網(wǎng)絡(luò)包分析key的頻率

2、redis客戶端抓取贪绘，例如請求key的時候記錄日志兑牡，分析日志得到key的訪問頻率

3、redis的monitor可以記錄redis的所有操作税灌，記錄并分析monitor日志得到key的訪問頻率（注意：monitor可能會造成性能問題均函，慎重使用）

4、Redis 4.0提供了—hot-keys配合maxmemory-policy可以統(tǒng)計(jì)熱點(diǎn)key

5菱涤、第三方開源項(xiàng)目苞也，如facebook開源項(xiàng)目redis-faina，原理同3

解決方案如下：

1粘秆、對于”get”類型的熱點(diǎn)key如迟，通常可以為redis添加slave攻走，通過slave承擔(dān)讀壓力來緩解

2殷勘、服務(wù)端本地緩存，服務(wù)端先請求本地緩存昔搂，緩解redis壓力

3玲销、多級緩存方案，通過多級請求摘符，層層過濾解決熱點(diǎn)key問題

4贤斜、proxy方案，有些方案會探測分片熱點(diǎn)key逛裤，緩存在proxy上緩解redis壓力

5蠢古、同解決big方案類似，將一個key通過hash分解為多個key别凹，value值一樣，將這些key分散到集群多個分片中洽糟，需要訪問時先根據(jù)hash算出對應(yīng)的key炉菲，然后訪問的具體的分片

二堕战、有哪些常見的核心的MySQL應(yīng)用開發(fā)規(guī)范?

這里重點(diǎn)介紹下Schema設(shè)計(jì)規(guī)范，其他規(guī)范請參考末尾《知數(shù)堂開發(fā)規(guī)范》

（一）schema設(shè)計(jì)原則：

1拍霜、盡量小的原則嘱丢，不浪費(fèi)

2、為了高并發(fā)祠饺，禁止使用外鍵

3越驻、每個表必須有主鍵

4年鸳、字符集和庫級保持一致锉走，不單獨(dú)定義字段字符集

（二）字段規(guī)范：

1、每個表建議不超過30-50個字段

2盖腕、優(yōu)先選擇utf8mb4字符集勺鸦，它的兼容性最好并巍，而且還支持emoji字符。如果對存儲容量比較敏感的换途，可以改成latin1字符集

3懊渡、嚴(yán)禁在數(shù)據(jù)庫中明文存儲用戶密碼、身份證军拟、信用卡號（信用卡PIN碼）等核心機(jī)密數(shù)據(jù)剃执，務(wù)必先行加密

4、存儲整型數(shù)據(jù)時懈息，默認(rèn)加上UNSIGNED肾档，擴(kuò)大存儲范圍

5、建議用INT UNSIGNED存儲IPV4地址漓拾，查詢時再利用INET_ATON()阁最、INET_NTOA()函數(shù)轉(zhuǎn)換

6、如果遇到BLOB骇两、TEXT字段速种，則盡量拆出去，再用主鍵做關(guān)聯(lián)

7低千、在夠用的前提下配阵，選擇盡可能小的字段，用于節(jié)省磁盤和內(nèi)存空間

8示血、涉及精確金額相關(guān)用途時棋傍，建議擴(kuò)大N倍后，全部轉(zhuǎn)成整型存儲（例如把分?jǐn)U大百倍）难审，避免浮點(diǎn)數(shù)加減出現(xiàn)不準(zhǔn)確問題

（三）常用數(shù)據(jù)類型參考：

1瘫拣、字符類型建議采用varchar數(shù)據(jù)類型（InnoDB建議用varchar替代char）

2、金額貨幣科學(xué)計(jì)數(shù)建議采用decimal數(shù)據(jù)類型告喊，如果運(yùn)算在數(shù)據(jù)庫中完成可以考慮使用bigint存儲麸拄，單位：分

3派昧、自增長標(biāo)識建議采用int或bigint數(shù)據(jù)類型，如果該表有大量的刪除及再寫入就使用bigint,反之int就夠用

4拢切、時間類型建議采用為datetime/timestamp數(shù)據(jù)類型

5蒂萎、禁止使用text、longtext等的數(shù)據(jù)類型

6淮椰、字段值如果為非負(fù)數(shù)五慈，就加上unsigned定語，提升可用范圍

（四）SQL規(guī)范

1主穗、在MySQL中SQL語句一般不區(qū)分大小寫泻拦，全部小寫

2、sql語句在使用join, 子查詢一定先要進(jìn)行explain確定執(zhí)行計(jì)劃

3黔牵、為每個業(yè)務(wù)收集sql list.

知數(shù)堂開發(fā)規(guī)范：https://github.com/zhishutech...

三聪轿、高可用架構(gòu)MHA有什么樣的不足和風(fēng)險點(diǎn)嗎？

MHA作為傳統(tǒng)復(fù)制下的高可用霸主猾浦，在今天的GTID環(huán)境下陆错，開始慢慢走向沒落，更多的人開始開始選擇replication-manager或者orchestrator等高可用解決方案

不足及風(fēng)險點(diǎn)：

1金赦、failover依賴于外部腳本音瓷，比如VIP切換需要自己編寫腳本實(shí)現(xiàn)

2、MHA啟動后只檢測主庫是否正常夹抗，并不檢查從庫狀態(tài)及主從延遲

3绳慎、需要基于SSH免認(rèn)證配置，存在一定的安全隱患

4漠烧、沒有提供從服務(wù)器的讀負(fù)載均衡功能

5杏愤、從節(jié)點(diǎn)出現(xiàn)宕機(jī)等異常并沒有能力處理，即沒有從庫故障轉(zhuǎn)移能力

6已脓、在高可用切換期間珊楼，某些場景下可能出現(xiàn)數(shù)據(jù)丟失的情況，并不保證數(shù)據(jù)0丟失

7度液、無法控制RTO恢復(fù)時間

具體的數(shù)據(jù)丟失場景移步吳老師公開課《把MHA拉下神壇》

https://ke.qq.com/course/430673?tuin=2ce85033

四厕宗、為什么pt-osc操作表以后中文注釋顯示???，如何避免堕担？

一般來說已慢，生產(chǎn)環(huán)境使用的表都會使用中文注釋表信息以及字段信息，但是如果使用pt-osc且未指定字符類型的情況下進(jìn)行在線變更后霹购，中文注釋都會變成"???"佑惠，雖然不影響正常使用，但是對于認(rèn)為閱讀起來會造成困擾，某些平臺會依據(jù)注釋生成數(shù)據(jù)字典膜楷，因此正確的姿勢是在使用pt-osc工具時通過--charset=utf8指定utf8字符集

示例：

pt-online-schema-change -h 127.0.0.1 -u xxx -p xxx --alter="add index idx_id(id)" --chunk-size=5000 \

--print --no-version-check --execute D=xucl,t=test --charset=utf8

五乍丈、MySQL 5.6升級5.7都有什么注意事項(xiàng)

（一）MySQL升級的方式一般來說有兩種

1、通過inplace方式原地升級把将，升級系統(tǒng)表

2、通過新建實(shí)例忆矛，高版本作為低版本的從庫進(jìn)行滾動升級

MySQL5.7版本做了非常多的改變察蹲，升級5.6到5.7時需要考慮兼容性，避免升級到5.7之后因?yàn)榉N種參數(shù)設(shè)置不正確導(dǎo)致業(yè)務(wù)受影響催训，建議首先逐一查看release note

(二)需要注意的參數(shù)及問題：

1洽议、sql_mode：MySQL 5.7采用嚴(yán)格模式，例如ONLY_FULL_GROUP_BY等

2漫拭、innodb_status_output_locks：MySQL 5.7支持將死鎖信息打印到error log（其實(shí)這個參數(shù)MySQL 5.6就已支持）

3亚兄、innodb_page_cleaners：MySQL 5.7將臟頁刷新線程從master線程獨(dú)立出來了，對應(yīng)參數(shù)為innodb_page_cleaners

4采驻、innodb_strict_mode：控制CREATE TABLE, ALTER TABLE, CREATE INDEX, 和 OPTIMIZE TABLE的語法問題

5审胚、show_compatibility_56=ON：控制show變量及狀態(tài)信息輸出，如果未開啟show status 命令無法獲取Slave_xxx 的狀態(tài)

6礼旅、log_timestamps：控制error log/slow_log/genera log日志的顯示時間膳叨，該參數(shù)可以設(shè)置為:UTC 和 SYSTEM，但是默認(rèn)使用 UTC

7痘系、disable_partition_engine_check：在表多的情況下可能導(dǎo)致啟動非常慢

8菲嘴、range_optimizer_max_mem_size：范圍查詢優(yōu)化參數(shù)，這個參數(shù)限制范圍查詢優(yōu)化使用的內(nèi)存汰翠，默認(rèn)8M

9龄坪、MySQL 5.7新增優(yōu)化器選項(xiàng)derived_merge=on，可能導(dǎo)致SQL全表掃描复唤，而在MySQL 5.6下可能表現(xiàn)為auto key

10健田、innodb_undo_directory && innodb_undo_logs：MySQL 5.7支持將undo從ibdata1獨(dú)立出來（只支持實(shí)例初始化，不支持在線變更）

11苟穆、主從復(fù)制問題：MySQL5.7到小于5.6.22的復(fù)制存在bug(bug 74683)

12抄课、SQL兼容性問題：SQL在MySQL 5.7和MySQL 5.6環(huán)境下結(jié)果可能不一致，因此建議獲取線上SQL雳旅，在同樣數(shù)據(jù)的環(huán)境下跟磨，在兩個實(shí)例運(yùn)行獲取到的結(jié)果計(jì)算hash，比較hash值做兼容性判斷

（三）友情提醒

1攒盈、升級前一定要做好備份５志小！型豁！

2僵蛛、升級正式環(huán)境前提前在測試環(huán)境進(jìn)行仔細(xì)測試尚蝌，確認(rèn)無誤以后再升級正式環(huán)境

3、做好相應(yīng)的回退方案

六充尉、在用阿里云飘言、騰訊云等公有云時，你是如何評估新建主機(jī)/數(shù)據(jù)庫對象的配置級別驼侠？

這里以云下業(yè)務(wù)遷移云上為例來探討

1姿鸿、首先熟悉現(xiàn)有業(yè)務(wù)的基本架構(gòu)，比如一主多從倒源、sharding架構(gòu)等苛预，并且知道相應(yīng)的業(yè)務(wù)分布

2、獲取現(xiàn)有業(yè)務(wù)的監(jiān)控獲取到的峰值QPS笋熬、TPS热某、IOPS、CPU使用率胳螟、磁盤使用量昔馋、內(nèi)存使用量、最大連接數(shù)等關(guān)鍵指標(biāo)

3旺隙、獲取現(xiàn)有數(shù)據(jù)庫的關(guān)鍵參數(shù)指標(biāo)绒极，如innodb_buffer_pool_size等

4、公有云每個規(guī)格都提供了相應(yīng)的參數(shù)指標(biāo)蔬捷，如：核數(shù)垄提、內(nèi)存、IOPS周拐、最大連接數(shù)等指標(biāo)

5铡俐、根據(jù)第2、3妥粟、4步選擇相應(yīng)規(guī)格的RDS审丘，原則為RDS規(guī)格參數(shù)大于現(xiàn)有環(huán)境狀態(tài)指標(biāo)，其中IOPS需要進(jìn)行換算(云上的IOPS一般按4k算勾给，而自建的一般按16k算)

6滩报、上云前最好先購買實(shí)例進(jìn)行測試，包括使用sysbench進(jìn)行標(biāo)準(zhǔn)壓測播急、業(yè)務(wù)兼容性測試脓钾、業(yè)務(wù)壓測等來判斷實(shí)例規(guī)格是否滿足性能要求，桩警，建議云上實(shí)例性能預(yù)留比如20-30%浮動空間

7可训、特別提醒，云上實(shí)例通常會把binlog以及SQL運(yùn)行產(chǎn)生的臨時表、臨時文件也計(jì)入磁盤空間握截，此外云上數(shù)據(jù)表的碎片率可能會比自建實(shí)例大很多（曾經(jīng)遇到本地5G的表云上占用120G）飞崖，因此要特別注意磁盤空間要預(yù)留充足

8、最后說明一點(diǎn)谨胞，遷移云上最好選擇數(shù)據(jù)庫版本同自建版本固歪。還有，盡量不要使用云上數(shù)據(jù)

七胯努、ALTER TABLE 出現(xiàn)duplicate primary xxx報錯的原因及處理昼牛？

好多同學(xué)都曾經(jīng)問過這個問題，還有同學(xué)說這是bug康聂，實(shí)際上這并不是bug

(一)原因分析

1、Online DDL操作時MySQL會將DML操作緩存起來存入到變更日志

2胞四、等到DDL執(zhí)行完成后再應(yīng)用變更日志中的DML操作

3恬汁、在Oline DDL執(zhí)行期間，并行的DML可能會沒先檢查唯一性直接插入一條相同主鍵的數(shù)據(jù)辜伟，這時并不會導(dǎo)致DDL報錯氓侧，而是在DDL執(zhí)行完成再次應(yīng)用變更日志時才報錯，最終導(dǎo)致DDL報錯執(zhí)行失敗

（二）問題說明

其實(shí)這是Online DDL的正常情況导狡，官方文檔說明如下：

When running an in-place online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction

ref：https://dev.mysql.com/doc/ref...

（三）建議

1约巷、推薦使用pt-osc、gh-ost等第三方工具進(jìn)行DDL操作

2旱捧、建議在業(yè)務(wù)低谷期進(jìn)行操作

八独郎、InnoDB在什么情況下會觸發(fā)檢查點(diǎn)(checkpoint)?

（一）MySQL的checkpoint分類

1、sharp checkpoint（激烈檢查點(diǎn)枚赡，要求盡快將所有臟頁都刷到磁盤上氓癌，對I/O資源的占有優(yōu)先級高）

2、fuzzy checkpoint（模糊檢查點(diǎn)贫橙，會根據(jù)系統(tǒng)負(fù)載及臟頁數(shù)量適當(dāng)平衡贪婉，不要求立即將所有臟頁寫入磁盤，這事默認(rèn)的方式）

（二）觸發(fā)時機(jī)

1卢肃、數(shù)據(jù)庫正常關(guān)閉時疲迂，即innodb_fast_shutdown=0時需要執(zhí)行sharp checkpoint

2、redo log發(fā)生切換時或者redo log快滿的時候進(jìn)行fuzzy checkpoint

3莫湘、master thread每隔1秒或10秒定期進(jìn)行fuzzy checkpoint

4尤蒿、innodb保證有足夠多的空閑page，如果發(fā)現(xiàn)不足逊脯，需要移除lru list末尾的page优质，如果這些page是臟頁，那么也需要fuzzy checkpoint

5、innodb buffer pool中臟頁比超過innodb_max_dirty_pages_pct時也會觸發(fā)fuzzy checkpoint

（三）checkpoint相關(guān)參數(shù)及狀態(tài)

1巩螃、innodb_fast_shutdown

2演怎、innodb_io_capacity/innodb_io_capacity_max

3、innodb_lru_scan_depth

4避乏、innodb_max_dirty_pages_pct/innodb_max_dirty_pages_pct_lwm

5爷耀、Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total

6、Innodb_buffer_pool_wait_free

九拍皮、update t set a=29 and b in (1,2,3,4);這樣寫有什么問題嗎歹叮？

（一）SQL分析

乍一看這個SQL貌似沒有什么問題，本意是將t表中b的值屬于1/2/3/4的數(shù)據(jù)的a列修改為29铆帽，但實(shí)際上該SQL是將t表數(shù)據(jù)的a列改成了條件29 & b in (1,2,3,4)的真假判斷值

即：update t set a = (29 and b in (1,2,3,4));

修改后的SQL應(yīng)該為update t set a = 29 where b in (1,2,3,4);

（二）注意事項(xiàng)

1咆耿、生產(chǎn)環(huán)境中進(jìn)行批量數(shù)據(jù)修改時應(yīng)該開啟事務(wù)，修改確認(rèn)后再進(jìn)行提交操作

2爹橱、進(jìn)行DML操作時萨螺，建議還是通過SQL審核工具審核后執(zhí)行

3、建議打開sql_safe_updates選項(xiàng)愧驱，避免沒有WHERE條件的更新慰技、刪除操作

有任何問題都可以加微信討論，歡迎溝通~~ 互相進(jìn)步组砚！

微信：lvqingshan_