欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

淺談MySQL大表優(yōu)化方案

 更新時(shí)間:2020年11月04日 09:28:04   作者:福祿網(wǎng)絡(luò)研發(fā)團(tuán)隊(duì)  
這篇文章主要介紹了淺談MySQL大表優(yōu)化方案,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

背景

阿里云RDS FOR MySQL(MySQL5.7版本)數(shù)據(jù)庫業(yè)務(wù)表每月新增數(shù)據(jù)量超過千萬,隨著數(shù)據(jù)量持續(xù)增加,我們業(yè)務(wù)出現(xiàn)大表慢查詢,在業(yè)務(wù)高峰期主業(yè)務(wù)表的慢查詢需要幾十秒嚴(yán)重影響業(yè)務(wù)

方案概述

20201030141518

一、數(shù)據(jù)庫設(shè)計(jì)及索引優(yōu)化

MySQL數(shù)據(jù)庫本身高度靈活,造成性能不足,嚴(yán)重依賴開發(fā)人員的表設(shè)計(jì)能力以及索引優(yōu)化能力,在這里給幾點(diǎn)優(yōu)化建議

  • 時(shí)間類型轉(zhuǎn)化為時(shí)間戳格式,用int類型儲(chǔ)存,建索引增加查詢效率
  • 建議字段定義not null,null值很難查詢優(yōu)化且占用額外的索引空間
  • 使用TINYINT類型代替枚舉ENUM
  • 存儲(chǔ)精確浮點(diǎn)數(shù)必須使用DECIMAL替代FLOAT和DOUBLE
  • 字段長(zhǎng)度嚴(yán)重根據(jù)業(yè)務(wù)需求來,不要設(shè)置過大
  • 盡量不要使用TEXT類型,如必須使用建議將不常用的大字段拆分到其它表
  • MySQL對(duì)索引字段長(zhǎng)度是有限制的, innodb引擎的每個(gè)索引列長(zhǎng)度默認(rèn)限制為767字節(jié)(bytes),所有組成索引列的長(zhǎng)度和不能大于3072字節(jié)(mysql8.0單索引可以創(chuàng)建1024字符)
  • 大表有DDL需求時(shí)請(qǐng)聯(lián)系DBA

最左索引匹配規(guī)則

顧名思義就是最左優(yōu)先,在創(chuàng)建組合索引時(shí),要根據(jù)業(yè)務(wù)需求,where子句中使用最頻繁的一列放在最左邊。復(fù)合索引很重要的問題是如何安排列的順序,比如where后面用到c1, c2 這兩個(gè)字段,那么索引的順序是(c1,c2)還是(c2,c1)呢,正確的做法是,重復(fù)值越少的越放前面,比如一個(gè)列 95%的值都不重復(fù),那么一般可以將這個(gè)列放最前面

  • 復(fù)合索引index(a,b,c)
  • where a=3 只使用了a
  • where a=3 and b=5 使用了a,b
  • where a=3 and b=5 and c=4 使用了a,b,c
  • where b=3 or where c=4 沒有使用索引
  • where a=3 and c=4 僅使用了 a
  • where a=3 and b>10 and c=7 使用了a,b
  • where a=3 and b like ‘xx%' and c=7 使用了a,b
  • 其實(shí)相當(dāng)于創(chuàng)建了多個(gè)索引:key(a)、key(a,b)、key(a,b,c)

二、數(shù)據(jù)庫切換到PloarDB讀寫分離

PolarDB是阿里云自研的下一代關(guān)系型云數(shù)據(jù)庫,100%兼容MySQL存儲(chǔ)容量最高可達(dá)100 TB,單庫最多可擴(kuò)展到16個(gè)節(jié)點(diǎn),適用于企業(yè)多樣化的數(shù)據(jù)庫應(yīng)用場(chǎng)景。PolarDB采用存儲(chǔ)和計(jì)算分離的架構(gòu),所有計(jì)算節(jié)點(diǎn)共享一份數(shù)據(jù),提供分鐘級(jí)的配置升降級(jí)、秒級(jí)的故障恢復(fù)、全局?jǐn)?shù)據(jù)一致性和免費(fèi)的數(shù)據(jù)備份容災(zāi)服務(wù)。

集群架構(gòu),計(jì)算與存儲(chǔ)分離
PolarDB采用多節(jié)點(diǎn)集群的架構(gòu),集群中有一個(gè)Writer節(jié)點(diǎn)(主節(jié)點(diǎn))和多個(gè)Reader節(jié)點(diǎn)(只讀節(jié)點(diǎn)),各節(jié)點(diǎn)通過分布式文件系統(tǒng)(PolarFileSystem)共享底層的存儲(chǔ)(PolarStore)

讀寫分離
當(dāng)應(yīng)用程序使用集群地址時(shí),PolarDB通過內(nèi)部的代理層(Proxy)對(duì)外提供服務(wù),應(yīng)用程序的請(qǐng)求都先經(jīng)過代理,然后才訪問到數(shù)據(jù)庫節(jié)點(diǎn)。代理層不僅可以做安全認(rèn)證和保護(hù),還可以解析SQL,把寫操作(例如事務(wù)、UPDATE、INSERT、DELETE、DDL等)發(fā)送到主節(jié)點(diǎn),把讀操作(例如SELECT)均衡地分發(fā)到多個(gè)只讀節(jié)點(diǎn),實(shí)現(xiàn)自動(dòng)的讀寫分離。對(duì)于應(yīng)用程序來說,就像使用一個(gè)單點(diǎn)的數(shù)據(jù)庫一樣簡(jiǎn)單。

在離線混合場(chǎng)景:不同業(yè)務(wù)用不同的連接地址,使用不同的數(shù)據(jù)節(jié)點(diǎn),避免相互影響

20201029160013

Sysbench性能壓測(cè)報(bào)告:

PloarDB 4核16G 2臺(tái)

20201029160525

20201029160550

PloarDB 8核32G 2臺(tái)

20201029160755

20201029160845

三、分表歷史數(shù)據(jù)遷移到MySQL8.0 X-Engine存儲(chǔ)引擎

分表業(yè)務(wù)表保留3個(gè)月數(shù)據(jù)(這個(gè)根據(jù)公司需求來),歷史數(shù)據(jù)按月分表到歷史庫X-Engine存儲(chǔ)引擎表, 為什么要選用X-Engine存儲(chǔ)引擎表,它有什么優(yōu)點(diǎn)?

節(jié)約成本, X-Engine的存儲(chǔ)成本約為InnoDB的一半

X-Engine分層存儲(chǔ)提高QPS, 采用層次化的存儲(chǔ)結(jié)構(gòu),將熱數(shù)據(jù)與冷數(shù)據(jù)分別存放在不同的層次中,并默認(rèn)對(duì)冷數(shù)據(jù)所在層次進(jìn)行壓縮

X-Engine是阿里云數(shù)據(jù)庫產(chǎn)品事業(yè)部自研的聯(lián)機(jī)事務(wù)處理OLTP(On-Line Transaction Processing)數(shù)據(jù)庫存儲(chǔ)引擎。
X-Engine存儲(chǔ)引擎不僅可以無縫對(duì)接兼容MySQL(得益于MySQL Pluginable Storage Engine特性),同時(shí)X-Engine使用分層存儲(chǔ)架構(gòu)。因?yàn)槟繕?biāo)是面向大規(guī)模的海量數(shù)據(jù)存儲(chǔ),提供高并發(fā)事務(wù)處理能力和降低存儲(chǔ)成本,在大部分大數(shù)據(jù)量場(chǎng)景下,數(shù)據(jù)被訪問的機(jī)會(huì)是不均等的,訪問頻繁的熱數(shù)據(jù)實(shí)際上占比很少,X-Engine根據(jù)數(shù)據(jù)訪問頻度的不同將數(shù)據(jù)劃分為多個(gè)層次,針對(duì)每個(gè)層次數(shù)據(jù)的訪問特點(diǎn),設(shè)計(jì)對(duì)應(yīng)的存儲(chǔ)結(jié)構(gòu),寫入合適的存儲(chǔ)設(shè)備

  • X-Engine使用了LSM-Tree作為分層存儲(chǔ)的架構(gòu)基礎(chǔ),并進(jìn)行了重新設(shè)計(jì):
  • 熱數(shù)據(jù)層和數(shù)據(jù)更新使用內(nèi)存存儲(chǔ),通過內(nèi)存數(shù)據(jù)庫技術(shù)(Lock-Free index structure/append only)提高事務(wù)處理的性能。
  • 流水線事務(wù)處理機(jī)制,把事務(wù)處理的幾個(gè)階段并行起來,極大提升了吞吐。
  • 訪問頻度低的數(shù)據(jù)逐漸淘汰或是合并到持久化的存儲(chǔ)層次中,并結(jié)合多層次的存儲(chǔ)設(shè)備(NVM/SSD/HDD)進(jìn)行存儲(chǔ)。
  • 對(duì)性能影響比較大的Compaction過程做了大量?jī)?yōu)化:
  • 拆分?jǐn)?shù)據(jù)存儲(chǔ)粒度,利用數(shù)據(jù)更新熱點(diǎn)較為集中的特征,盡可能的在合并過程中復(fù)用數(shù)據(jù)。
  • 精細(xì)化控制LSM的形狀,減少I/O和計(jì)算代價(jià),有效緩解了合并過程中的空間增大。
  • 同時(shí)使用更細(xì)粒度的訪問控制和緩存機(jī)制,優(yōu)化讀的性能。

20201029162440

四、阿里云PloarDB MySQL8.0版本并行查詢

分表之后我們的數(shù)據(jù)量依然很大,并沒有完全解決我們的慢查詢問題,只是降低了我們業(yè)務(wù)表的體量,這部分慢查詢我們需要用到PolarDB的并行查詢優(yōu)化

PolarDB MySQL 8.0重磅推出并行查詢框架,當(dāng)您的查詢數(shù)據(jù)量到達(dá)一定閾值,就會(huì)自動(dòng)啟動(dòng)并行查詢框架,從而使查詢耗時(shí)指數(shù)級(jí)下降
在存儲(chǔ)層將數(shù)據(jù)分片到不同的線程上,多個(gè)線程并行計(jì)算,將結(jié)果流水線匯總到總線程,最后總線程做些簡(jiǎn)單歸并返回給用戶,提高查詢效率。
并行查詢(Parallel Query)利用多核CPU的并行處理能力,以8核32 GB配置為例,示意圖如下所示。

20201029163124

并行查詢適用于大部分SELECT語句,例如大表查詢、多表連接查詢、計(jì)算量較大的查詢。對(duì)于非常短的查詢,效果不太顯著。

并行查詢用法,使用Hint語法可以對(duì)單個(gè)語句進(jìn)行控制,例如系統(tǒng)默認(rèn)關(guān)閉并行查詢情況下,但需要對(duì)某個(gè)高頻的慢SQL查詢進(jìn)行加速,此時(shí)就可以使用Hint對(duì)特定SQL進(jìn)行加速。

SELECT /+PARALLEL(x)/ … FROM …; – x >0

SELECT /*+ SET_VAR(max_parallel_degree=n) */ * FROM … // n > 0

查詢測(cè)試:數(shù)據(jù)庫配置 16核32G 單表數(shù)據(jù)量超3千萬

沒加并行查詢之前是4326ms,加了之后是525ms,性能提升8.24倍

lALPDhmOtqINirTNAl_NBIw_1164_607

lALPDgQ9vsVjxDbNAl7NBHk_1145_606

五、交互式分析Hologre

大表慢查詢我們雖然用并行查詢優(yōu)化提升了效率,但是一些特定的需求實(shí)時(shí)報(bào)表、實(shí)時(shí)大屏我們還是無法實(shí)現(xiàn),只能依賴大數(shù)據(jù)去處理。
這里推薦大家阿里云的交互式分析Hologre(
https://help.aliyun.com/product/113622.html

20201030151537

六、后記

千萬級(jí)大表優(yōu)化是根據(jù)業(yè)務(wù)場(chǎng)景,以成本為代價(jià)優(yōu)化的,不是一上來就數(shù)據(jù)庫水平切分?jǐn)U展,這樣會(huì)給運(yùn)維和業(yè)務(wù)帶來巨大挑戰(zhàn),很多時(shí)候效果不一定好,我們的數(shù)據(jù)庫設(shè)計(jì)、索引優(yōu)化、分表策略是否做到位了,應(yīng)該根據(jù)業(yè)務(wù)需求選擇合適的技術(shù)去實(shí)現(xiàn)。

到此這篇關(guān)于淺談MySQL大表優(yōu)化方案的文章就介紹到這了,更多相關(guān)MySQL大表優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • windows下mysql 5.7版本中修改編碼為utf-8的方法步驟

    windows下mysql 5.7版本中修改編碼為utf-8的方法步驟

    mysql的默認(rèn)編碼是拉?。╨atin1),當(dāng)輸入中文的時(shí)候就會(huì)報(bào)錯(cuò),所以需要將編碼修改為utf8,從網(wǎng)上找了相關(guān)教程都不可以,索性自己摸索后分享給大家,下面這篇文章主要給大家介紹了在mysql 5.7版本中如何修改編碼為utf-8的方法步驟,需要的朋友可以參考下。
    2017-06-06
  • MySQL實(shí)時(shí)監(jiān)控工具orztop的使用介紹

    MySQL實(shí)時(shí)監(jiān)控工具orztop的使用介紹

    這篇文章主要給大家介紹了MySQL實(shí)時(shí)監(jiān)控工具orztop的使用,文中給出了詳細(xì)的介紹,相信對(duì)大家的學(xué)習(xí)具有一定的參考借鑒價(jià)值,有需要的朋友可以參考借鑒,下面來一起看看吧。
    2017-01-01
  • 阿里云centos7中安裝MySQL8.0.13的方法步驟

    阿里云centos7中安裝MySQL8.0.13的方法步驟

    這篇文章主要介紹了阿里云centos7中安裝MySQL8.0.13的方法步驟,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-07-07
  • MySQL 中定義和使用變量的方法

    MySQL 中定義和使用變量的方法

    MySQL 提供了多種類型的變量,以適應(yīng)不同的應(yīng)用場(chǎng)景,用戶定義的變量適用于簡(jiǎn)單的會(huì)話內(nèi)數(shù)據(jù)傳遞,局部變量適合在復(fù)雜的存儲(chǔ)過程中使用,而會(huì)話變量則用于調(diào)整和優(yōu)化數(shù)據(jù)庫會(huì)話的行為,這篇文章主要介紹了MySQL 中定義和使用變量,需要的朋友可以參考下
    2024-04-04
  • SQL語句中OR和AND的混合使用的小技巧

    SQL語句中OR和AND的混合使用的小技巧

    在SQL語句中我們可能會(huì)經(jīng)?;旌鲜褂玫絆R和AND,其中可能會(huì)出現(xiàn)一些小問題,下面小編來講一講它的使用技巧
    2019-05-05
  • Mysql關(guān)聯(lián)查詢的幾種實(shí)現(xiàn)方式

    Mysql關(guān)聯(lián)查詢的幾種實(shí)現(xiàn)方式

    這篇文章主要介紹了Mysql關(guān)聯(lián)查詢的幾種實(shí)現(xiàn)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2024-04-04
  • 深入Mysql字符集設(shè)置分析

    深入Mysql字符集設(shè)置分析

    深入Mysql字符集設(shè)置分析,使用mysql的朋友可以參考下
    2012-03-03
  • mysql免安裝版配置步驟詳解分享

    mysql免安裝版配置步驟詳解分享

    這篇文章主要介紹了mysql免安裝版配置步驟詳解,提供了二個(gè)網(wǎng)友的安裝方法,大家可以參考使用
    2013-12-12
  • MySQL之my.cnf配置文件圖文詳解

    MySQL之my.cnf配置文件圖文詳解

    my.cnf是mysql啟動(dòng)時(shí)加載的配置文件,一般會(huì)放在mysql的安裝目錄中,用戶也可以放在其他目錄加載,下面這篇文章主要給大家介紹了關(guān)于MySQL之my.cnf配置文件的相關(guān)資料,需要的朋友可以參考下
    2022-09-09
  • centos下mysql主從同步快速設(shè)置步驟分享

    centos下mysql主從同步快速設(shè)置步驟分享

    記錄一個(gè)比較簡(jiǎn)便的mysql的主從同步設(shè)置步驟,方便日后使用。
    2012-06-06

最新評(píng)論