復(fù)雜的數(shù)據(jù)需求的MySQL方案是怎樣的

今天就跟大家聊聊有關(guān)復(fù)雜的數(shù)據(jù)需求的MySQL方案是怎樣的，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

   前些天處理了一個(gè)需求，當(dāng)時(shí)的數(shù)據(jù)庫(kù)環(huán)境是Oracle，我算是想盡了Oracle相關(guān)的方案，而且在問(wèn)題的處理過(guò)程中，還在不斷的琢磨，如果失敗了還有什么其他的方案。

   所以盡管Oracle這么一個(gè)成熟的商業(yè)數(shù)據(jù)庫(kù)，做起來(lái)還是有些難度，需要一些額外的技巧，比如規(guī)避bug,間接實(shí)現(xiàn)需求等。

   但是換個(gè)角度，2億多數(shù)據(jù)的表，其實(shí)MySQL也不是新鮮事兒了。如果MySQL碰到了這種情況，該怎么處理呢。

梳理業(yè)務(wù)需求

  假設(shè)業(yè)務(wù)需求還是不變，如下：

   業(yè)務(wù)同學(xué)反饋，數(shù)據(jù)庫(kù)中有一個(gè)表數(shù)據(jù)量很大，因?yàn)橐鲆黄诨顒?dòng)，需要近期的數(shù)據(jù)，以前的舊數(shù)據(jù)可以考慮清理。清理多少舊數(shù)據(jù)呢，差不多是99%的量，數(shù)據(jù)量有多大呢，差不多兩個(gè)億。所以這個(gè)需求聽(tīng)起來(lái)蠻簡(jiǎn)單，但是業(yè)務(wù)同學(xué)明確希望能夠保持業(yè)務(wù)的可持續(xù)性，這樣一來(lái)就對(duì)實(shí)現(xiàn)方案有了一些選擇。

   這個(gè)看起來(lái)簡(jiǎn)單的需求，有下面的一些補(bǔ)充信息，數(shù)據(jù)庫(kù)為MySQL 5.6,數(shù)據(jù)量有2億，數(shù)據(jù)查詢效率非常差，99%以上都是臟數(shù)據(jù)，需要清理，開(kāi)發(fā)同學(xué)是根據(jù)時(shí)間范圍來(lái)進(jìn)行查詢；表里的數(shù)據(jù)只有insert,沒(méi)有update和delete。

總結(jié)下來(lái)，要做4件事情：

1. 優(yōu)化查詢，目前是基于時(shí)間范圍來(lái)查詢，經(jīng)過(guò)評(píng)估需要給這個(gè)表添加索引

2. 清理數(shù)據(jù)，表里有兩億數(shù)據(jù)，但是要清理絕大部分?jǐn)?shù)據(jù)。

3. 保證業(yè)務(wù)的可持續(xù)性，每10分鐘會(huì)做一次統(tǒng)計(jì)分析，數(shù)據(jù)會(huì)實(shí)時(shí)錄入系統(tǒng)

4. 把表修改為分區(qū)表，把舊數(shù)據(jù)放入一個(gè)分區(qū)，新數(shù)據(jù)放入另一個(gè)分區(qū)，變更之后刪除就分區(qū)即可

梳理需求優(yōu)先級(jí)

如此一來(lái)，給這個(gè)表添加索引就是亟待解決的關(guān)鍵問(wèn)題。

MySQL里面的online DDL功能還是很不錯(cuò)的，對(duì)于索引的操作，5.6版本支持還是很全的。

所以MySQL online DDL原生的方案就很不錯(cuò)，如果是5.5也沒(méi)關(guān)系還有pt-osc工具等可以實(shí)現(xiàn)。

大道至簡(jiǎn)，思路相通

   而對(duì)此的一個(gè)解決方案如下，數(shù)據(jù)流和之前Oracle的方案如出一轍，但是實(shí)現(xiàn)原理和細(xì)節(jié)有所差別。

   首先需要做得就是生成一個(gè)影子表serverlog_read,對(duì)于源庫(kù)的表數(shù)據(jù)變更都能夠同步到這個(gè)表里。

   MySQL里面是不支持物化視圖的，所以增量刷新等等方案就會(huì)受限，但是辦法總比困難多，MySQL里面要實(shí)現(xiàn)物化視圖還是有一些其他的方法的，比如說(shuō)Flexviews，或者是自己實(shí)現(xiàn)，通過(guò)觸發(fā)器的形式來(lái)實(shí)現(xiàn)需求，這里insert,delete,update都需要有觸發(fā)條件，所以pt工具默認(rèn)會(huì)創(chuàng)建的也是3個(gè)觸發(fā)器，原理很相似。

    有了這個(gè)物化視圖，緩存增量數(shù)據(jù)就有了基本保證，所以我們還需要兩個(gè)輔助的表，一個(gè)是serverlog_par_old，這是個(gè)分區(qū)表，只保留一個(gè)分區(qū)，里面會(huì)存放物化視圖里查到的刷新數(shù)據(jù)，另外一個(gè)是serverlog_host,這里面存放的是增量數(shù)據(jù)和實(shí)時(shí)錄入系統(tǒng)的數(shù)據(jù)。

    這個(gè)時(shí)候其實(shí)有三種類(lèi)別的數(shù)據(jù)處理需要考慮，第一類(lèi)是舊數(shù)據(jù)，也可以理解為冷數(shù)據(jù)，第二類(lèi)是增量數(shù)據(jù)，比如指定近一個(gè)月的數(shù)據(jù)需要保留，那么這個(gè)時(shí)間范圍內(nèi)的數(shù)據(jù)就是增量數(shù)據(jù)，第三類(lèi)是實(shí)時(shí)數(shù)據(jù)，數(shù)據(jù)會(huì)實(shí)時(shí)錄入系統(tǒng)，這個(gè)數(shù)據(jù)近乎是實(shí)時(shí)的。所以說(shuō)上面的方案就是對(duì)冷數(shù)據(jù)能夠歸檔，對(duì)增量數(shù)據(jù)能夠合理截取，對(duì)實(shí)時(shí)數(shù)據(jù)產(chǎn)生盡可能小的影響。

   2億的數(shù)據(jù)怎么合1千萬(wàn)的數(shù)據(jù)進(jìn)行切換呢，MySQL 5.6也是支持exchange partition的。所以這個(gè)操作支持起來(lái)是沒(méi)有問(wèn)題的，畢竟分區(qū)的操作就是這么幾種玩法。MySQL因?yàn)槠渥陨泶鎯?chǔ)的特性，實(shí)現(xiàn)這個(gè)需求其實(shí)更純粹。

    最后就是增量，實(shí)時(shí)數(shù)據(jù)的補(bǔ)錄，利用serverlog_hot來(lái)補(bǔ)數(shù)據(jù)就行。

方案之外的兩點(diǎn)補(bǔ)充

    額外補(bǔ)充兩點(diǎn)，也是MySQL在這個(gè)實(shí)現(xiàn)過(guò)程的兩個(gè)亮點(diǎn)。

    第一個(gè)亮點(diǎn)就是MySQL復(fù)制表結(jié)構(gòu)有著得天獨(dú)厚的優(yōu)勢(shì)，大家知道在MySQL 5.6中是不支持create table xxxx as select xx這種方式的，但是有很多更絕的方法。

    我們可以改寫(xiě)為下面的方式來(lái)做：

   1.create table test1 like test; --這種方式能夠完整的復(fù)制DDL信息。

    或者使用show create table來(lái)做，當(dāng)然這個(gè)略有些不方面，或者是使用mysqldump --no-date的方式來(lái)導(dǎo)出語(yǔ)句也可以。

   2.插入數(shù)據(jù)，比如insert into test1 select *from test;

  第二個(gè)亮點(diǎn)部分就是對(duì)于數(shù)據(jù)的備份歸檔，說(shuō)簡(jiǎn)單簡(jiǎn)單，說(shuō)復(fù)雜復(fù)雜，比如我們嚴(yán)格限定數(shù)據(jù)的有效性，不需要的舊數(shù)據(jù)就不在當(dāng)前的數(shù)據(jù)庫(kù)中保留，但是為了實(shí)現(xiàn)基本的備份需求，我們可以使用rename user的方式來(lái)做。Oracle實(shí)現(xiàn)rename user還是有些復(fù)雜的，而MySQL實(shí)現(xiàn)起來(lái)就很輕巧。說(shuō)得通俗一些，就是把里面的數(shù)據(jù)挪到另外一個(gè)目錄下了。

  要處理這樣一個(gè)需求，毫無(wú)疑問(wèn)盡管我信息滿滿，但是在實(shí)踐的時(shí)候還是是困難重重，碰到了問(wèn)題多思考和總結(jié)，就會(huì)形成自己的認(rèn)知體系，會(huì)少走很多彎路。

看完上述內(nèi)容，你們對(duì)復(fù)雜的數(shù)據(jù)需求的MySQL方案是怎樣的有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。