上周和一個(gè)做電商運(yùn)營(yíng)的朋友聊天。他說(shuō)團(tuán)隊(duì)打算上一套“高端數(shù)據(jù)分析平臺(tái)”第一步是讓技術(shù)部把 MySQL 里的訂單表、用戶表、商品表導(dǎo)到數(shù)據(jù)倉(cāng)庫(kù)再上 BI 看板。結(jié)果光是字段口徑就對(duì)了三天支付金額到底要不要包含取消訂單用戶 id 到底按賬號(hào)算還是按手機(jī)號(hào)去重報(bào)表剛上線一周業(yè)務(wù)部門(mén)又開(kāi)始抱怨數(shù)據(jù)不對(duì)。技術(shù)負(fù)責(zé)人最后說(shuō)了一句讓我印象很深的話問(wèn)題根本不在平臺(tái)在數(shù)據(jù)源頭。這個(gè)場(chǎng)景太常見(jiàn)了。很多數(shù)據(jù)分析項(xiàng)目一上來(lái)就談架構(gòu)、談大數(shù)據(jù)組件卻忽略了一個(gè)事實(shí)絕大多數(shù)企業(yè)里的核心業(yè)務(wù)數(shù)據(jù)仍然躺在 MySQL 這類(lèi)關(guān)系型數(shù)據(jù)庫(kù)里。MySQL 承載的不僅是一筆筆線上交易更是后續(xù)所有分析、報(bào)表、決策的事實(shí)基礎(chǔ)。想搭建一套真正能落地的企業(yè)數(shù)據(jù)分析架構(gòu)繞開(kāi) MySQL 去談 Spark、Flink、數(shù)據(jù)湖基本等于蓋樓不打地基。我不推銷(xiāo)任何課程也不想羅列工具清單。這篇文章想結(jié)合這幾年在業(yè)務(wù)數(shù)據(jù)項(xiàng)目里的真實(shí)體會(huì)聊聊以 MySQL 作為核心驅(qū)動(dòng)時(shí)數(shù)據(jù)分析架構(gòu)怎么搭、SQL 怎么用、踩坑怎么排查以及所謂“高級(jí)數(shù)據(jù)分析實(shí)訓(xùn)”到底應(yīng)該訓(xùn)練什么。1. 為什么企業(yè)數(shù)據(jù)分析架構(gòu)繞不開(kāi) MySQL1.1 大多數(shù)業(yè)務(wù)系統(tǒng)的數(shù)據(jù)源頭仍然是 MySQL不管是在電商、CRM、ERP還是內(nèi)容管理系統(tǒng)里很多業(yè)務(wù)方選擇的第一代數(shù)據(jù)庫(kù)都是 MySQL。原因很直接開(kāi)發(fā)門(mén)檻低、生態(tài)完善、運(yùn)維資料多、硬成本友好。哪怕公司后來(lái)引入了微服務(wù)架構(gòu)、分布式中間件核心的交易、會(huì)員、商品、庫(kù)存這類(lèi)數(shù)據(jù)往往仍然落在 MySQL 里。數(shù)據(jù)分析不等于直接對(duì)業(yè)務(wù)庫(kù)做查詢(xún)但任何分析項(xiàng)目都得先和這些源頭數(shù)據(jù)打交道。你可以用 Python 讀取數(shù)據(jù)也可以用 BI 工具直連更可以把數(shù)據(jù)同步到數(shù)倉(cāng)里但前提是你得理解這些源頭表訂單表里的狀態(tài)字段有哪些枚舉值支付時(shí)間和創(chuàng)建時(shí)間到底誰(shuí)先誰(shuí)后用戶表是單主鍵還是有多套 id如果這些源頭的數(shù)據(jù)理解錯(cuò)了后面所有加工都會(huì)把錯(cuò)誤放大。很多人以為“高端數(shù)據(jù)分析架構(gòu)”是拿復(fù)雜組件堆出來(lái)的但真實(shí)企業(yè)里最值錢(qián)的分析師往往是那個(gè)能把 MySQL 業(yè)務(wù)庫(kù)講明白的人。他不需要寫(xiě)多少高深的算法但每當(dāng)業(yè)務(wù)問(wèn)“這個(gè)數(shù)為什么變了”他能很快定位到是哪個(gè)表、哪個(gè)字段、哪個(gè)狀態(tài)出了變化。1.2 “核心驅(qū)動(dòng)”的真實(shí)含義“核心驅(qū)動(dòng)”不是指 MySQL 永遠(yuǎn)站在 C 位而是說(shuō)它承擔(dān)了數(shù)據(jù)接入、基礎(chǔ)清洗、口徑固化、輕度聚合這一連串又臟又累的活。在常見(jiàn)的企業(yè)數(shù)據(jù)鏈路里MySQL 是這樣參與數(shù)據(jù)分析的業(yè)務(wù)系統(tǒng)寫(xiě)入 MySQLMySQL 是一手?jǐn)?shù)據(jù)的存儲(chǔ)層。數(shù)據(jù)分析師寫(xiě) SQL從 MySQL 里做取數(shù)、刷數(shù)、核數(shù)。報(bào)表系統(tǒng)或 BI 工具直接連接 MySQL展示實(shí)時(shí)或準(zhǔn)實(shí)時(shí)的業(yè)務(wù)看板。數(shù)據(jù)同步工具把 MySQL 數(shù)據(jù)搬進(jìn)數(shù)倉(cāng)供全公司做更重的分析。也就是說(shuō)MySQL 是所有數(shù)據(jù)鏈路的入口。它像一個(gè)“數(shù)據(jù)收費(fèi)站”決定了進(jìn)入下一層的數(shù)據(jù)是否干凈、口徑是否一致、字段是否可信。如果第一公里沒(méi)走好后面數(shù)倉(cāng)再規(guī)范也難彌補(bǔ)源頭數(shù)據(jù)質(zhì)量問(wèn)題。這也是為什么我始終認(rèn)為企業(yè)數(shù)據(jù)分析架構(gòu)最先要設(shè)計(jì)好的不是大數(shù)據(jù)平臺(tái)而是 MySQL 里的表結(jié)構(gòu)、字段規(guī)范、指標(biāo)口徑和訪問(wèn)路徑。1.3 不是所有分析都要用大數(shù)據(jù)平臺(tái)很多團(tuán)隊(duì)有一種慣性思維報(bào)表慢就上大數(shù)據(jù)數(shù)據(jù)量大就搞分布式。但真實(shí)場(chǎng)景里有相當(dāng)一部分分析完全可以在 MySQL 里完成甚至 MySQL 是更合適的選擇?？梢詤⒖歼@幾個(gè)判斷標(biāo)準(zhǔn)數(shù)據(jù)量級(jí)百萬(wàn)行以?xún)?nèi)MySQL 配合合理索引完全可以高效分析千萬(wàn)行只要查詢(xún)模式不匪夷所思也能穩(wěn)定跑。真正到上億行、還要做全量復(fù)雜聚合時(shí)才需要考慮數(shù)倉(cāng)或大數(shù)據(jù)引擎。查詢(xún)復(fù)雜度如果是多表 join、窗口函數(shù)、分組聚合MySQL 8.0 能覆蓋大多數(shù)日常分析。如果要做全量 ETL、海量日志分析、機(jī)器學(xué)習(xí)特征工程那是另一個(gè)領(lǐng)域。時(shí)效要求T1 日?qǐng)?bào)、后臺(tái)管理報(bào)表、運(yùn)營(yíng)取數(shù)MySQL 可以直接應(yīng)對(duì)。秒級(jí)實(shí)時(shí)大屏、復(fù)雜實(shí)時(shí)推薦才需要時(shí)效性更強(qiáng)的組件。團(tuán)隊(duì)能力如果團(tuán)隊(duì)只有 MySQL 和 BI 基礎(chǔ)硬上一套 Hadoop 生態(tài)只會(huì)讓運(yùn)維和開(kāi)發(fā)都陷入泥潭。場(chǎng)景MySQL 直接分析數(shù)倉(cāng) / 大數(shù)據(jù)組件數(shù)據(jù)量百萬(wàn)到千萬(wàn)級(jí)查詢(xún)可控TB 級(jí)以上需要全量計(jì)算核心訴求快速看數(shù)、日常報(bào)表、輕量聚合復(fù)雜 ETL、全域建模、算法特征時(shí)效要求T1 或分鐘級(jí)秒級(jí)實(shí)時(shí)或大規(guī)模并行計(jì)算團(tuán)隊(duì)基礎(chǔ)熟悉 SQL 和業(yè)務(wù)有專(zhuān)門(mén)數(shù)據(jù)研發(fā)和運(yùn)維團(tuán)隊(duì)這不是說(shuō)大數(shù)據(jù)平臺(tái)沒(méi)用而是說(shuō)“先想清楚問(wèn)題再選工具”。很多號(hào)稱(chēng)高并發(fā)的分析場(chǎng)景實(shí)際數(shù)據(jù)量還不到一百萬(wàn)行真正的瓶頸是索引缺失、SQL 寫(xiě)法不合理而不是數(shù)據(jù)庫(kù)選型。2. 用 MySQL 搭數(shù)據(jù)底座先解決模型和口徑2.1 數(shù)據(jù)模型不是 DBA 的事分析者也必須懂我見(jiàn)過(guò)不少分析師SQL 寫(xiě)得很溜但拿到表之后完全不明白業(yè)務(wù)過(guò)程。他能寫(xiě)復(fù)雜的子查詢(xún)卻不知道“支付金額有負(fù)數(shù)”是因?yàn)橛型丝钜膊恢馈坝唵螤顟B(tài)為 5”到底表示已發(fā)貨還是已完成。數(shù)據(jù)分析的本質(zhì)是回答業(yè)務(wù)問(wèn)題而業(yè)務(wù)問(wèn)題最終要落到一張張表上。所以哪怕不會(huì)做完整的數(shù)倉(cāng)建模也必須理解兩個(gè)基礎(chǔ)概念事實(shí)表和維度表。事實(shí)表記錄發(fā)生了什么訂單、支付流水、退款記錄、日志訪問(wèn)。它的特點(diǎn)是一行對(duì)應(yīng)一次事件有大量可度量字段比如數(shù)量、金額、時(shí)間。維度表描述這個(gè)發(fā)生的主體是什么用戶、商品、門(mén)店、渠道。它的特點(diǎn)是相對(duì)穩(wěn)定提供名稱(chēng)、分類(lèi)、屬性等描述信息。簡(jiǎn)單說(shuō)事實(shí)表是流水賬維度表是字典。分析一個(gè)指標(biāo)時(shí)先想清楚它來(lái)自哪張事實(shí)表需要按哪些維度描述再?zèng)Q定怎么 join、怎么聚合。2.2 明細(xì)表、匯總表、寬表的定位在 MySQL 里做數(shù)據(jù)分析最容易犯的錯(cuò)是“一張表走天下”。有些剛開(kāi)始做數(shù)據(jù)的同學(xué)恨不得把所有字段都塞進(jìn)一張大表里結(jié)果表變得異常臃腫查詢(xún)慢、更新難、權(quán)限也難控制。實(shí)際項(xiàng)目中我更建議把表拆成幾類(lèi)職責(zé)表類(lèi)型定位使用建議明細(xì)表保留每行原始事實(shí)是最底層的依據(jù)盡量不刪改按業(yè)務(wù)日期分區(qū)匯總表按日、周、月預(yù)聚合用于固定報(bào)表速度快適合高頻查詢(xún)寬表把頻繁 join 的維度字段冗余進(jìn)來(lái)面向分析主題減少重復(fù) join舉例來(lái)說(shuō)一套電商分析庫(kù)可以這樣組織order_detail訂單明細(xì)表一行一個(gè)訂單明細(xì)。sku_daily_summary商品每日匯總表由定時(shí)任務(wù)生成。user_info_extend用戶寬表包含用戶基礎(chǔ)屬性、最近一次下單時(shí)間、累計(jì)消費(fèi)金額等。寬表不是不能用而是不要一開(kāi)始就追求“萬(wàn)能大寬表”。正確的思路是底層明細(xì)表保持相對(duì)規(guī)范分析層再按主題適度冗余。這樣既保證數(shù)據(jù)一致性又提升查詢(xún)效率。2.3 指標(biāo)口徑要前置用視圖固化下來(lái)口徑不統(tǒng)一是所有數(shù)據(jù)分析團(tuán)隊(duì)最痛苦的問(wèn)題。同一個(gè)銷(xiāo)售額有人統(tǒng)計(jì)下單金額有人統(tǒng)計(jì)支付金額還有人統(tǒng)計(jì)扣除退款后的凈額。大家各寫(xiě)各的 SQL到月底一對(duì)數(shù)誰(shuí)也說(shuō)服不了誰(shuí)。解決口徑問(wèn)題技術(shù)手段只是輔助關(guān)鍵是把定義先定清楚。每個(gè)團(tuán)隊(duì)都應(yīng)該維護(hù)一份指標(biāo)字典明確指標(biāo)名稱(chēng)、定義、計(jì)算公式、統(tǒng)計(jì)維度、有效時(shí)間。定義清楚了再用數(shù)據(jù)庫(kù)手段固化。我最推薦的做法是把一些高頻并且口徑穩(wěn)定的指標(biāo)封裝成視圖。CREATE VIEW v_gmv_daily AS SELECT DATE(pay_time) AS stat_date, SUM(order_amount) AS gmv FROM orders WHERE status paid AND refund_status 0 GROUP BY DATE(pay_time);這是一個(gè)很典型的示意把所有已支付且未退款的訂單按支付日期匯總成 GMV。以后業(yè)務(wù)要查日 GMV直接SELECT * FROM v_gmv_daily不需要每個(gè)人重新寫(xiě)一遍判斷邏輯。口徑不統(tǒng)一時(shí)所有高級(jí)分析都是空中樓閣。先統(tǒng)一“銷(xiāo)售額怎么算”再談“銷(xiāo)售額預(yù)測(cè)”。3. 從 SQL 查詢(xún)到分析能力七成場(chǎng)景靠這些手段3.1 分析型 SQL 和普通業(yè)務(wù) CRUD 不是一回事業(yè)務(wù)開(kāi)發(fā)寫(xiě) SQL 常常是“按主鍵查一行”或者“更新某個(gè)狀態(tài)字段”。分析型 SQL 面對(duì)的是全量數(shù)據(jù)要做聚合、分組、排序、比例、去重、時(shí)間對(duì)比。很多從 CRUD 轉(zhuǎn)過(guò)來(lái)的開(kāi)發(fā)者剛接觸數(shù)據(jù)分析時(shí)會(huì)有一種“會(huì)語(yǔ)法但不會(huì)解題”的感覺(jué)。核心原因在于分析型任務(wù)更看重怎么把業(yè)務(wù)問(wèn)題翻譯成數(shù)據(jù)操作。比如怎么算每個(gè)用戶最近一次下單時(shí)間怎么算相鄰兩筆訂單的時(shí)間間隔怎么算每個(gè)商品被多少個(gè)用戶復(fù)購(gòu)怎么算某個(gè)月的新客在次月留存率這些問(wèn)題單獨(dú)看都不難但組合在一起就涉及 join、聚合、窗口函數(shù)、時(shí)間函數(shù)、子查詢(xún)的綜合運(yùn)用。打好這個(gè)底子比背 100 個(gè)函數(shù)更重要。3.2 窗口函數(shù)復(fù)雜分析場(chǎng)景的真正分水嶺不少分析任務(wù)用GROUP BY能做到但代價(jià)是丟失明細(xì)。遇到“用戶最近一單”“商品排名”“累計(jì)銷(xiāo)售額”這類(lèi)需求窗口函數(shù)是更優(yōu)雅的方案。窗口函數(shù)和普通聚合最大的區(qū)別是它在不減少行數(shù)的前提下對(duì)每一行做計(jì)算。常用的包括ROW_NUMBER()按分組生成序號(hào)。RANK()/DENSE_RANK()排名。LAG()/LEAD()取當(dāng)前行前一行或后一行的值。SUM(...) OVER(PARTITION BY ...)分組累計(jì)?？匆粋€(gè)真實(shí)高頻的例子求每個(gè)用戶最近一次下單時(shí)間。SELECT user_id, order_time FROM ( SELECT user_id, order_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE rn 1;這段 SQL 的邏輯是先把每個(gè)用戶的訂單按時(shí)間倒序編號(hào)再取出編號(hào)為 1 的那一行。它保留了所有明細(xì)只是把“最近一次”這個(gè)概念表達(dá)出來(lái)了。一定要留意版本MySQL 8.0 才支持窗口函數(shù)。如果還在用 5.7這類(lèi)需求通常要改寫(xiě)成用戶變量或自連接實(shí)現(xiàn)更繞也更容易出錯(cuò)。所以做數(shù)據(jù)分析實(shí)訓(xùn)第一件事就是確認(rèn)數(shù)據(jù)庫(kù)版本。3.3 用視圖和存儲(chǔ)過(guò)程把重復(fù)分析固化成模板日常取數(shù)里會(huì)有大量重復(fù)工作。比如每周都要跑一次銷(xiāo)售周報(bào)每個(gè)月都要統(tǒng)計(jì)一次用戶增長(zhǎng)。如果每次都從頭寫(xiě) SQL不僅慢而且容易因?yàn)橐粋€(gè)過(guò)濾條件的差異導(dǎo)致結(jié)果對(duì)不上。更推薦的做法是把穩(wěn)定的分析邏輯固化成視圖或存儲(chǔ)過(guò)程。視圖適合“查詢(xún)模板”比如前面說(shuō)的日 GMV 視圖、周復(fù)購(gòu)率視圖。業(yè)務(wù)或分析師只需要查視圖不需要理解背后的表關(guān)系。存儲(chǔ)過(guò)程適合“定時(shí)計(jì)算”比如每天凌晨把前一天的匯總結(jié)果寫(xiě)入?yún)R總表形成日?qǐng)?bào)數(shù)據(jù)。一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程示意DELIMITER // CREATE PROCEDURE sp_sales_daily_report() BEGIN INSERT INTO sales_daily_summary(stat_date, gmv, order_cnt, user_cnt) SELECT DATE(pay_time) AS stat_date, SUM(order_amount) AS gmv, COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS user_cnt FROM orders WHERE status paid AND pay_time CURDATE() - INTERVAL 1 DAY AND pay_time CURDATE() GROUP BY DATE(pay_time); END // DELIMITER ;我這里只是給了個(gè)整體結(jié)構(gòu)真實(shí)環(huán)境里還要處理冪等防止重復(fù)跑任務(wù)、日志記錄、失敗告警。存儲(chǔ)過(guò)程不是銀彈復(fù)雜業(yè)務(wù)邏輯放進(jìn)應(yīng)用層更易調(diào)試。但簡(jiǎn)單的、高頻率的匯總?cè)蝿?wù)用存儲(chǔ)過(guò)程確實(shí)能減少很多重復(fù)勞動(dòng)。3.4 用 EXPLAIN 驗(yàn)證性能別只憑感覺(jué)SQL 寫(xiě)出來(lái)能跑只是第一步。數(shù)據(jù)分析師如果處理的數(shù)據(jù)量上來(lái)還得有性能意識(shí)。MySQL 里最直接的調(diào)優(yōu)入口就是EXPLAIN。EXPLAIN SELECT user_id, order_time FROM orders WHERE status paid ORDER BY pay_time DESC;執(zhí)行后重點(diǎn)看幾個(gè)字段type是否走了索引至少要達(dá)到ref級(jí)別ALL說(shuō)明全表掃描。key實(shí)際用到的索引。rows預(yù)估掃描多少行數(shù)字越大越危險(xiǎn)。Extra出現(xiàn)Using filesort或Using temporary時(shí)要想想能否通過(guò)索引優(yōu)化。很多人調(diào) SQL 只看執(zhí)行時(shí)間但執(zhí)行時(shí)間會(huì)受數(shù)據(jù)量、CPU、網(wǎng)絡(luò)影響。用EXPLAIN能看到更本質(zhì)的東西這條 SQL 是不是在用一種高效的方式獲取數(shù)據(jù)。4. 從單表到集群數(shù)據(jù)量上來(lái)以后怎么演進(jìn)4.1 先判斷 MySQL 是否需要拆分有些團(tuán)隊(duì)一聊到“架構(gòu)升級(jí)”第一反應(yīng)就是分庫(kù)分表、上分布式中間件。但分庫(kù)分表是一種把復(fù)雜度從 SQL 層轉(zhuǎn)移到架構(gòu)層的決策成本很高遠(yuǎn)不是慢查詢(xún)的銀彈。要不要拆分應(yīng)該先看四個(gè)指標(biāo)單表數(shù)據(jù)量是否持續(xù)突破億級(jí)歸檔和分區(qū)是否已經(jīng)失效連接數(shù)數(shù)據(jù)庫(kù)連接是否頻繁打滿連接池是否存在大量等待慢查詢(xún)占比優(yōu)化 SQL 和索引后慢查詢(xún)是否仍然居高不下備份恢復(fù)時(shí)長(zhǎng)單庫(kù)備份或恢復(fù)時(shí)間是否已經(jīng)長(zhǎng)到不可接受如果只是為了跑一個(gè)報(bào)表優(yōu)先嘗試優(yōu)化 SQL、加索引、建匯總表而不是拆庫(kù)。很多項(xiàng)目的實(shí)際數(shù)據(jù)量不到百萬(wàn)行問(wèn)題出在一個(gè)LIKE %xxx%導(dǎo)致全表掃描或者日期字段上用了函數(shù)導(dǎo)致索引失效。這種時(shí)候談分布式屬于用大炮打蚊子。用分庫(kù)分表解決慢查詢(xún)是最昂貴的優(yōu)化方式。它把問(wèn)題從“SQL 怎么寫(xiě)”變成了“架構(gòu)怎么維護(hù)”。4.2 主從復(fù)制與讀寫(xiě)分離當(dāng) MySQL 承擔(dān)了在線業(yè)務(wù)又有數(shù)據(jù)分析需求時(shí)最自然的演進(jìn)是主從復(fù)制與讀寫(xiě)分離。主庫(kù)負(fù)責(zé)增刪改從庫(kù)負(fù)責(zé)查詢(xún)分析報(bào)表盡量走從庫(kù)避免把線上業(yè)務(wù)庫(kù)壓垮。讀寫(xiě)分離的架構(gòu)并不復(fù)雜主庫(kù)開(kāi)啟二進(jìn)制日志從庫(kù)通過(guò) I/O 線程拉取日志并回放保持和主庫(kù)一致。日常查詢(xún)連接從庫(kù)寫(xiě)操作連接主庫(kù)。但這個(gè)方案有一個(gè)繞不開(kāi)的問(wèn)題主從延遲。從庫(kù)同步是異步進(jìn)行的在寫(xiě)入量大的時(shí)刻從庫(kù)可能滯后主庫(kù)幾百毫秒甚至幾秒。如果業(yè)務(wù)要求“我剛下單馬上能在報(bào)表里看到”從庫(kù)不一定能滿足。實(shí)際落地通常是這樣區(qū)分實(shí)時(shí)性要求高的關(guān)鍵查詢(xún)走主庫(kù)。運(yùn)營(yíng)看板、后臺(tái)報(bào)表、分析查詢(xún)走從庫(kù)。對(duì)延遲極度敏感的場(chǎng)景考慮半同步復(fù)制或引入緩存。用 GTID 復(fù)制比傳統(tǒng)基于日志位點(diǎn)的復(fù)制更好維護(hù)切換主從時(shí)更安全新建從庫(kù)也會(huì)簡(jiǎn)單不少。4.3 分庫(kù)分表和分布式架構(gòu)的邊界分庫(kù)分表一般是在數(shù)據(jù)量或?qū)懭雺毫Φ搅艘欢ǔ潭群蟛趴紤]。真正的難點(diǎn)不是把數(shù)據(jù)拆開(kāi)而是拆完之后的查詢(xún)邏輯變得復(fù)雜。舉個(gè)例子訂單表按user_id分片后單用戶的訂單查詢(xún)很快天然解決了數(shù)據(jù)量大的問(wèn)題。但如果你想分析“全平臺(tái)近 30 天銷(xiāo)量 Top 100 商品”數(shù)據(jù)分散在幾十個(gè)分片里就必須聚合所有分片再排序。MySQL 本身不擅長(zhǎng)跨庫(kù)聚合于是你要引入中間件、匯總層或者干脆把數(shù)據(jù)同步到數(shù)倉(cāng)里去算。所以在搭建數(shù)據(jù)分析架構(gòu)時(shí)一定要提前想清楚分片的邊界按什么鍵分片決定了高頻查詢(xún)能不能命中單個(gè)分片?？绶制?join 會(huì)變得極其困難要盡量避免。全量分析、多維分析不適合在分片后的 MySQL 上做交給數(shù)倉(cāng)或 OLAP 引擎更合理。如果已經(jīng)走到分庫(kù)分表這一步MySQL 的角色就開(kāi)始從“分析引擎”變成“業(yè)務(wù)源庫(kù)”了。真正的分析計(jì)算應(yīng)該由后面更擅長(zhǎng)批量聚合的組件承擔(dān)。4.4 MySQL 在數(shù)據(jù)鏈路里的真實(shí)位置一個(gè)相對(duì)完整的數(shù)據(jù)分析架構(gòu)可以簡(jiǎn)化成這樣的鏈路業(yè)務(wù)應(yīng)用 - MySQL業(yè)務(wù)庫(kù)- 同步工具 - 數(shù)倉(cāng)/數(shù)據(jù)湖ODS/DWD/ADS- BI/API這里的同步工具常見(jiàn)的有基于 Binlog 的實(shí)時(shí)同步組件也有離線批量同步工具。但不管用哪種MySQL 都是數(shù)據(jù)可信度的第一道防線。源頭的字段類(lèi)型、枚舉值、時(shí)間格式、主外鍵關(guān)系會(huì)一路傳遞到數(shù)倉(cāng)最終影響報(bào)表和決策。這也是“核心驅(qū)動(dòng)”的另一層含義MySQL 不只是數(shù)據(jù)倉(cāng)庫(kù)的上游它定義的業(yè)務(wù)邏輯和字段語(yǔ)義決定了整個(gè)分析架構(gòu)能走多遠(yuǎn)。5. 真正落地時(shí)最容易踩的坑與排查鏈路5.1 數(shù)據(jù)分析報(bào)錯(cuò)別急著改 SQL先按鏈路排查很多新人遇到報(bào)錯(cuò)第一反應(yīng)是百度或改 SQL。但實(shí)際問(wèn)題可能根本不在 SQL而在環(huán)境、配置或數(shù)據(jù)本身。我建議遇到任何分析異常都按這個(gè)順序排查層級(jí)要檢查什么典型現(xiàn)象現(xiàn)象具體報(bào)錯(cuò)卡住結(jié)果不對(duì)SQL 返回空、執(zhí)行超時(shí)、統(tǒng)計(jì)與業(yè)務(wù)對(duì)不上輸入源表結(jié)構(gòu)、字段類(lèi)型、join 基數(shù)、時(shí)間范圍字段不存在、join 后行數(shù)翻倍、時(shí)間差 8 小時(shí)環(huán)境MySQL 版本、字符集、時(shí)區(qū)、權(quán)限窗口函數(shù)不支持、中文亂碼、遠(yuǎn)程連不上參數(shù)連接超時(shí)、批量大小、排序內(nèi)存大批量查詢(xún)斷開(kāi)、limit 深分頁(yè)慢工具邊界語(yǔ)法兼容、驅(qū)動(dòng)認(rèn)證、調(diào)度平臺(tái)老客戶端報(bào)認(rèn)證不支持、存儲(chǔ)過(guò)程權(quán)限受限排查順序不要反過(guò)來(lái)。我曾經(jīng)幫同事查一個(gè)“統(tǒng)計(jì)結(jié)果多了一倍”的問(wèn)題他一直在改 SQL最后發(fā)現(xiàn)是 join 的表里存在一對(duì)多關(guān)系導(dǎo)致訂單主表被放大。問(wèn)題不在 SQL 語(yǔ)法而在輸入層的數(shù)據(jù)關(guān)系理解。5.2 SQL 編寫(xiě)中的高頻錯(cuò)誤有幾個(gè) SQL 錯(cuò)誤幾乎每周都會(huì)看到。第一類(lèi)UPDATE忘了加WHERE。UPDATE users SET status 1;這條 SQL 會(huì)把全表的狀態(tài)都改成 1。MySQL 里可以通過(guò)sql_safe_updates來(lái)約束開(kāi)啟后不帶WHERE或沒(méi)有走索引的UPDATE/DELETE會(huì)被拒絕。SET sql_safe_updates 1; UPDATE users SET status 1 WHERE id 100;這只是一個(gè)客戶端會(huì)話設(shè)置生產(chǎn)環(huán)境更推薦從賬號(hào)權(quán)限和應(yīng)用規(guī)范上約束。第二類(lèi)排序和分頁(yè)性能差。ORDER BY字段沒(méi)有索引時(shí)MySQL 會(huì)做filesort。分頁(yè)越深OFFSET越大性能下降越明顯。更穩(wěn)妥的做法是用“上一頁(yè)最后一條記錄 ID”做條件查詢(xún)而不是不斷加深OFFSET。第三類(lèi)字段類(lèi)型隱式轉(zhuǎn)換。當(dāng)字段是VARCHAR你用數(shù)字條件去查MySQL 可能放棄索引。比如SELECT * FROM users WHERE phone 13800000000;如果phone是VARCHAR這里的數(shù)字會(huì)轉(zhuǎn)成字符串但有時(shí)候索引就是不走。更穩(wěn)妥的做法是優(yōu)先保證類(lèi)型一致。第四類(lèi)JOIN條件不完整。分析結(jié)果突然翻倍時(shí)先檢查是不是一對(duì)多 join 導(dǎo)致的而不是急著加過(guò)濾條件。所有 join 都應(yīng)該清楚左表一行右表可能匹配多少行。第五類(lèi)聚合和明細(xì)混用。在沒(méi)有ONLY_FULL_GROUP_BY模式的 MySQL 中SELECT非聚合列但沒(méi)在GROUP BY里能執(zhí)行但結(jié)果不確定。建議在數(shù)據(jù)庫(kù)配置里打開(kāi)ONLY_FULL_GROUP_BY從機(jī)制上避免這類(lèi)問(wèn)題。5.3 環(huán)境、安裝和客戶端連接坑數(shù)據(jù)分析環(huán)境里安裝 MySQL 和連接 MySQL 也是高頻問(wèn)題。在 Windows 上安裝 MySQL 時(shí)服務(wù)啟動(dòng)失敗常見(jiàn)原因有端口被占用、配置文件寫(xiě)錯(cuò)、data 目錄沒(méi)有初始化權(quán)限。如果安裝時(shí)遇到服務(wù)無(wú)法啟動(dòng)先看錯(cuò)誤日志通常比卸載重裝有效得多。MySQL 8 默認(rèn)使用caching_sha2_password認(rèn)證插件。一些老版本客戶端工具或驅(qū)動(dòng)在連接時(shí)會(huì)報(bào)認(rèn)證協(xié)議不支持的錯(cuò)。解決思路有兩種升級(jí)客戶端驅(qū)動(dòng)或者把對(duì)應(yīng)用戶的認(rèn)證插件改回mysql_native_password。在本地做實(shí)驗(yàn)時(shí)也可以直接用 Docker 起一個(gè) MySQL。這是我個(gè)人比較推薦的方式因?yàn)楦蓛?、易清理、可以隨時(shí)換版本。docker run -d \ --name mysql \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDyourpassword \ -e TZAsia/Shanghai \ -v /data/mysql:/var/lib/mysql \ mysql:8注意兩點(diǎn)一是掛載數(shù)據(jù)目錄防止容器刪除后數(shù)據(jù)丟失二是設(shè)置時(shí)區(qū)避免 MySQL 內(nèi)部時(shí)間和業(yè)務(wù)時(shí)間相差 8 小時(shí)。這個(gè)命令只適合本地實(shí)驗(yàn)生產(chǎn)環(huán)境還要考慮參數(shù)文件、備份策略和資源限制。5.4 數(shù)據(jù)結(jié)果驗(yàn)證分析報(bào)告的最后一道關(guān)口分析結(jié)果是要支撐決策的不能“看著差不多”就提交。我給自己定過(guò)幾條鐵律抽樣核對(duì)隨機(jī)抽 50 到 100 條明細(xì)手工算一遍關(guān)鍵字段。交叉驗(yàn)證同一個(gè)指標(biāo)用兩種不同 SQL 實(shí)現(xiàn)方式去算看結(jié)果是否一致。對(duì)照業(yè)務(wù)和分析系統(tǒng)的導(dǎo)出、財(cái)務(wù)系統(tǒng)的報(bào)表、運(yùn)營(yíng)后臺(tái)的計(jì)數(shù)做對(duì)比。波動(dòng)檢查和上一周期對(duì)比任何不合理的大幅波動(dòng)都要先解釋清楚再往下發(fā)報(bào)告。運(yùn)行成功不等于結(jié)果正確。做數(shù)據(jù)分析必須把“結(jié)果驗(yàn)證”當(dāng)作流程的一部分而不是額外工作。6. 把“高級(jí)實(shí)訓(xùn)”落到日常比堆課時(shí)更重要6.1 搭建一個(gè)最小實(shí)驗(yàn)環(huán)境不管你是剛開(kāi)始學(xué)數(shù)據(jù)分析還是想補(bǔ) MySQL 這塊短板我都會(huì)建議先花一下午搭一個(gè)最小實(shí)驗(yàn)環(huán)境而不是光看視頻或讀文檔。步驟很簡(jiǎn)單本地安裝 MySQL 8或用 Docker 起一個(gè)實(shí)例。準(zhǔn)備一份盡量接近真實(shí)業(yè)務(wù)的樣例數(shù)據(jù)比如電商訂單、用戶、商品表。通過(guò)命令行或 Workbench 連上數(shù)據(jù)庫(kù)先跑通最基本的增刪改查。mysql -h127.0.0.1 -uroot -p SHOW DATABASES;如果本機(jī)沒(méi)有歷史數(shù)據(jù)可以用 Python 生成模擬訂單也可以導(dǎo)入開(kāi)源練習(xí)數(shù)據(jù)集。關(guān)鍵是數(shù)據(jù)本身要有一點(diǎn)復(fù)雜度至少包含多張有關(guān)聯(lián)的表否則后面很難練習(xí) join 和聚合。6.2 一個(gè)可復(fù)用的五步分析法當(dāng)手里有一份數(shù)據(jù)面對(duì)一個(gè)開(kāi)放的分析問(wèn)題時(shí)怎么下手我總結(jié)過(guò)一個(gè)五步法后面做任何分析項(xiàng)目都可以先按這個(gè)流程過(guò)一遍。第一步理解業(yè)務(wù)問(wèn)題。業(yè)務(wù)方問(wèn)“復(fù)購(gòu)率怎么樣”你要先搞清楚他關(guān)心的是哪個(gè)商品、哪個(gè)時(shí)間周期、哪個(gè)用戶群體。第二步定義口徑。復(fù)購(gòu)率是復(fù)購(gòu)用戶數(shù)除以購(gòu)買(mǎi)用戶數(shù)還是復(fù)購(gòu)訂單數(shù)除以總訂單數(shù)一個(gè)用戶買(mǎi)同一商品兩次算不算復(fù)購(gòu)這些必須提前確認(rèn)。第三步設(shè)計(jì) SQL。從哪些表取數(shù)主表和從表按什么鍵 join在哪里過(guò)濾、在哪里聚合第四步實(shí)現(xiàn)并驗(yàn)證。執(zhí)行 SQL 后抽樣核對(duì)和業(yè)務(wù)系統(tǒng)交叉驗(yàn)證。第五步展示與迭代。用 BI 或 Python 可視化拿到業(yè)務(wù)反饋后再修正。拿電商復(fù)購(gòu)率舉例核心 SQL 大致長(zhǎng)這樣SELECT product_id, COUNT(DISTINCT user_id) AS buy_users, SUM(CASE WHEN buy_cnt 2 THEN 1 ELSE 0 END) AS repurchase_users FROM ( SELECT product_id, user_id, COUNT(*) AS buy_cnt FROM order_items WHERE order_time NOW() - INTERVAL 30 DAY GROUP BY product_id, user_id ) t GROUP BY product_id;這個(gè)寫(xiě)法里內(nèi)層子查詢(xún)算出每個(gè)用戶在每個(gè)商品上的購(gòu)買(mǎi)次數(shù)外層按商品統(tǒng)計(jì)購(gòu)買(mǎi)人數(shù)和復(fù)購(gòu)人數(shù)。真實(shí)業(yè)務(wù)里還要剔除測(cè)試訂單、退款訂單、特殊渠道等但框架就是這樣。6.3 把經(jīng)驗(yàn)沉淀成指標(biāo)字典和 SQL 模板獨(dú)立做完一個(gè)分析項(xiàng)目后最該產(chǎn)出的不是結(jié)果表而是三樣能被反復(fù)使用的東西。第一是指標(biāo)字典。每個(gè)指標(biāo)一句定義寫(xiě)清楚分子、分母、統(tǒng)計(jì)維度、特殊規(guī)則。以后任何人問(wèn)起“這個(gè)指標(biāo)怎么算”都只需要查字典。第二是 SQL 模板。把高頻查詢(xún)整理成模板包括基礎(chǔ)表