堪稱大神級(jí)別的sql優(yōu)化精彩內(nèi)容不容錯(cuò)過(guò)哦

上傳人:ta****fu 文檔編號(hào):210939824 上傳時(shí)間:2023-05-18 格式:DOCX 頁(yè)數(shù):10 大?。?24.82KB
收藏 版權(quán)申訴 舉報(bào) 下載
堪稱大神級(jí)別的sql優(yōu)化精彩內(nèi)容不容錯(cuò)過(guò)哦_第1頁(yè)
第1頁(yè) / 共10頁(yè)
堪稱大神級(jí)別的sql優(yōu)化精彩內(nèi)容不容錯(cuò)過(guò)哦_第2頁(yè)
第2頁(yè) / 共10頁(yè)
堪稱大神級(jí)別的sql優(yōu)化精彩內(nèi)容不容錯(cuò)過(guò)哦_第3頁(yè)
第3頁(yè) / 共10頁(yè)

下載文檔到電腦,查找使用更方便

9.98 積分

下載資源

還剩頁(yè)未讀,繼續(xù)閱讀

資源描述:

《堪稱大神級(jí)別的sql優(yōu)化精彩內(nèi)容不容錯(cuò)過(guò)哦》由會(huì)員分享,可在線閱讀,更多相關(guān)《堪稱大神級(jí)別的sql優(yōu)化精彩內(nèi)容不容錯(cuò)過(guò)哦(10頁(yè)珍藏版)》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、來(lái),教你寫(xiě)一手好SQL 本人負(fù)責(zé)的項(xiàng)目主要采用阿里云數(shù)據(jù)庫(kù) MySQL,最近頻繁出現(xiàn)慢 SQL 告警,執(zhí)行時(shí)間最長(zhǎng)的竟然高達(dá) 5 分鐘。 圖片來(lái)自 Pexels 導(dǎo)出日志后分析,主要原因竟然是沒(méi)有命中索引和沒(méi)有分頁(yè)處理。其實(shí)這是非常低級(jí)的錯(cuò)誤,我不禁后背一涼,團(tuán)隊(duì)成員的技術(shù)水平亟待提高啊。 改造這些 SQL 的過(guò)程中,總結(jié)了一些經(jīng)驗(yàn)分享給大家,如果有錯(cuò)誤歡迎批評(píng)指正。 MySQL 性能 ①最大數(shù)據(jù)量 拋開(kāi)數(shù)據(jù)量和并發(fā)數(shù),談性能都是耍流氓。MySQL 沒(méi)有限制單表最大記錄數(shù),它取決于操作系統(tǒng)對(duì)文件大小的限制。 《阿里巴巴 Java 開(kāi)發(fā)手冊(cè)》提出單表行數(shù)超過(guò) 500 萬(wàn)行

2、或者單表容量超過(guò) 2GB,才推薦分庫(kù)分表。 性能由綜合因素決定,拋開(kāi)業(yè)務(wù)復(fù)雜度,影響程度依次是硬件配置、MySQL 配置、數(shù)據(jù)表設(shè)計(jì)、索引優(yōu)化。500 萬(wàn)這個(gè)值僅供參考,并非鐵律。 我曾經(jīng)操作過(guò)超過(guò) 4 億行數(shù)據(jù)的單表,分頁(yè)查詢最新的 20 條記錄耗時(shí) 0.6 秒,SQL 語(yǔ)句大致是: select?field_1,field_2?from?table?where?id?

3、分表是個(gè)周期長(zhǎng)而風(fēng)險(xiǎn)高的大活兒,應(yīng)該盡可能在當(dāng)前結(jié)構(gòu)上優(yōu)化,比如升級(jí)硬件、遷移歷史數(shù)據(jù)等等,實(shí)在沒(méi)轍了再分。對(duì)分庫(kù)分表感興趣的同學(xué)可以閱讀分庫(kù)分表的基本思想。 ②最大并發(fā)數(shù) 并發(fā)數(shù)是指同一時(shí)刻數(shù)據(jù)庫(kù)能處理多少個(gè)請(qǐng)求,由 max_connections 和 max_user_connections 決定。 max_connections 是指 MySQL 實(shí)例的最大連接數(shù),上限值是 16384,max_user_connections 是指每個(gè)數(shù)據(jù)庫(kù)用戶的最大連接數(shù)。 MySQL 會(huì)為每個(gè)連接提供緩沖區(qū),意味著消耗更多的內(nèi)存。如果連接數(shù)設(shè)置太高硬件吃不消,太低又不能充分利用硬件。 一般

4、要求兩者比值超過(guò) 10%,計(jì)算方法如下: max_used_connections?/?max_connections?*?100%?=?3/100?*100%?≈?3%? 查看最大連接數(shù)與響應(yīng)最大連接數(shù): show variables like '%max_connections%'; show variables like '%max_user_connections%'; 在配置文件 f 中修改最大連接數(shù): [mysqld] max_connections = 100 max_used_connections = 20 ③查詢耗時(shí) 0.5 秒 建議將單次查詢

5、耗時(shí)控制在 0.5 秒以內(nèi),0.5 秒是個(gè)經(jīng)驗(yàn)值,源于用戶體驗(yàn)的 3 秒原則。如果用戶的操作 3 秒內(nèi)沒(méi)有響應(yīng),將會(huì)厭煩甚至退出。 響應(yīng)時(shí)間=客戶端 UI 渲染耗時(shí)+網(wǎng)絡(luò)請(qǐng)求耗時(shí)+應(yīng)用程序處理耗時(shí)+查詢數(shù)據(jù)庫(kù)耗時(shí),0.5 秒就是留給數(shù)據(jù)庫(kù) 1/6 的處理時(shí)間。 ④實(shí)施原則 相比 NoSQL 數(shù)據(jù)庫(kù),MySQL 是個(gè)嬌氣脆弱的家伙。它就像體育課上的女同學(xué),一點(diǎn)糾紛就和同學(xué)鬧別扭(擴(kuò)容難),跑兩步就氣喘吁吁(容量小并發(fā)低),常常身體不適要請(qǐng)假(SQL 約束太多)。 如今大家都會(huì)搞點(diǎn)分布式,應(yīng)用程序擴(kuò)容比數(shù)據(jù)庫(kù)要容易得多,所以實(shí)施原則是數(shù)據(jù)庫(kù)少干活,應(yīng)用程序多干活: · 充分利用但不濫用索

6、引,須知索引也消耗磁盤(pán)和 CPU。 · 不推薦使用數(shù)據(jù)庫(kù)函數(shù)格式化數(shù)據(jù),交給應(yīng)用程序處理。 · 不推薦使用外鍵約束,用應(yīng)用程序保證數(shù)據(jù)準(zhǔn)確性。 · 寫(xiě)多讀少的場(chǎng)景,不推薦使用唯一索引,用應(yīng)用程序保證唯一性。 · 適當(dāng)冗余字段,嘗試創(chuàng)建中間表,用應(yīng)用程序計(jì)算中間結(jié)果,用空間換時(shí)間。 · 不允許執(zhí)行極度耗時(shí)的事務(wù),配合應(yīng)用程序拆分成更小的事務(wù)。 · 預(yù)估重要數(shù)據(jù)表(比如訂單表)的負(fù)載和數(shù)據(jù)增長(zhǎng)態(tài)勢(shì),提前優(yōu)化。 數(shù)據(jù)表設(shè)計(jì) ①數(shù)據(jù)類型 數(shù)據(jù)類型的選擇原則,更簡(jiǎn)單或者占用空間更?。? · 如果長(zhǎng)度能夠滿足,整型盡量使用 tinyint、smallint、medium_int 而非 i

7、nt。 · 如果字符串長(zhǎng)度確定,采用 char 類型。 · 如果 varchar 能夠滿足,不采用 text 類型。 · 精度要求較高的使用 decimal 類型,也可以使用 BIGINT,比如精確兩位小數(shù)就乘以 100 后保存。 · 盡量采用 timestamp 而非 datetime。 相比 datetime,timestamp 占用更少的空間,以 UTC 的格式儲(chǔ)存自動(dòng)轉(zhuǎn)換時(shí)區(qū)。 ②避免空值 MySQL 中字段為 NULL 時(shí)依然占用空間,會(huì)使索引、索引統(tǒng)計(jì)更加復(fù)雜。從 NULL 值更新到非 NULL 無(wú)法做到原地更新,容易發(fā)生索引分裂影響性能。 因此盡可能將 NUL

8、L 值用有意義的值代替,也能避免 SQL 語(yǔ)句里面包含 is not null 的判斷。 ③Text 類型優(yōu)化 由于 Text 字段儲(chǔ)存大量數(shù)據(jù),表容量會(huì)很早漲上去,影響其他字段的查詢性能。建議抽取出來(lái)放在子表里,用業(yè)務(wù)主鍵關(guān)聯(lián)。 索引優(yōu)化 索引分類如下: · 普通索引:最基本的索引。 · 組合索引:多個(gè)字段上建立的索引,能夠加速?gòu)?fù)合查詢條件的檢索。 · 唯一索引:與普通索引類似,但索引列的值必須唯一,允許有空值。 · 組合唯一索引:列值的組合必須唯一。 · 主鍵索引:特殊的唯一索引,用于唯一標(biāo)識(shí)數(shù)據(jù)表中的某一條記錄,不允許有空值,一般用 primary key 約束。 ·

9、 全文索引:用于海量文本的查詢,MySQL 5.6 之后的 InnoDB 和 MyISAM 均支持全文索引。由于查詢精度以及擴(kuò)展性不佳,更多的企業(yè)選擇 Elasticsearch。 索引優(yōu)化原則: · 分頁(yè)查詢很重要,如果查詢數(shù)據(jù)量超過(guò) 30%,MySQL 不會(huì)使用索引。 · 單表索引數(shù)不超過(guò) 5 個(gè)、單個(gè)索引字段數(shù)不超過(guò) 5 個(gè)。 · 字符串可使用前綴索引,前綴長(zhǎng)度控制在 5-8 個(gè)字符。 · 字段唯一性太低,增加索引沒(méi)有意義,如:是否刪除、性別。 · 合理使用覆蓋索引,如下所示: select?login_name,?nick_name?from?member?where?l

10、ogin_name?=??? login_name, nick_name 兩個(gè)字段建立組合索引,比 login_name 簡(jiǎn)單索引要更快。 SQL 優(yōu)化 ①分批處理 博主小時(shí)候看到魚(yú)塘挖開(kāi)小口子放水,水面有各種漂浮物。浮萍和樹(shù)葉總能順利通過(guò)出水口,而樹(shù)枝會(huì)擋住其他物體通過(guò),有時(shí)還會(huì)卡住,需要人工清理。 MySQL 就是魚(yú)塘,最大并發(fā)數(shù)和網(wǎng)絡(luò)帶寬就是出水口,用戶 SQL 就是漂浮物。 不帶分頁(yè)參數(shù)的查詢或者影響大量數(shù)據(jù)的 update 和 delete 操作,都是樹(shù)枝,我們要把它打散分批處理,下面舉例說(shuō)明。 業(yè)務(wù)描述:更新用戶所有已過(guò)期的優(yōu)惠券為不可用狀態(tài)。 SQL 語(yǔ)句: u

11、pdate?status=0?FROM?`coupon`?WHERE?expire_date?<=?#{currentDate}?and?status=1;? 如果大量?jī)?yōu)惠券需要更新為不可用狀態(tài),執(zhí)行這條 SQL 可能會(huì)堵死其他 SQL,分批處理偽代碼如下: int pageNo = 1; int PAGE_SIZE = 100; while(true) { List batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and stat

12、us = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}'); if (CollectionUtils.isEmpty(batchIdList)) { return; } update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}') pageNo ++; } ②操作符 <> 優(yōu)化 通常 <> 操作符無(wú)法使用索引,舉例如下,查詢金額不為 100 元的訂單: selec

13、t?id?from?orders?where?amount??!=?100;? 如果金額為 100 的訂單極少,這種數(shù)據(jù)分布嚴(yán)重不均的情況下,有可能使用索引。 鑒于這種不確定性,采用 union 聚合搜索結(jié)果,改寫(xiě)方法如下: (select id from orders where amount > 100) union all (select id from orders where amount < 100 and amount > 0) ③OR 優(yōu)化 在 Innodb 引擎下 OR 無(wú)法使用組合索引,比如: select?id,product_name?from?

14、orders?where?mobile_no?=?'13421800407'?or?user_id?=?100;? OR 無(wú)法命中 mobile_no + user_id 的組合索引,可采用 union,如下所示: (select id,product_name from orders where mobile_no = '13421800407') union (select id,product_name from orders where user_id = 100); 此時(shí) id 和 product_name 字段都有索引,查詢才最高效。 ④IN 優(yōu)化 IN 適合

15、主表大子表小,EXIST 適合主表小子表大。由于查詢優(yōu)化器的不斷升級(jí),很多場(chǎng)景這兩者性能差不多一樣了。 嘗試改為 Join 查詢,舉例如下: select?id?from?orders?where?user_id?in?(select?id?from?user?where?level?=?'VIP');? 采用 Join 如下所示: select?o.id?from?orders?o?left?join?user?u?on?o.user_id?=?u.id?where?u.level?=?'VIP';? ⑤不做列運(yùn)算 通常在查詢條件列運(yùn)算會(huì)導(dǎo)致索引失效,如下所示,查詢當(dāng)日訂單:

16、select?id?from?order?where?date_format(create_time,'%Y-%m-%d')?=?'2019-07-01';? date_format 函數(shù)會(huì)導(dǎo)致這個(gè)查詢無(wú)法使用索引,改寫(xiě)后: select?id?from?order?where?create_time?between?'2019-07-01?00:00:00'?and?'2019-07-01?23:59:59';? ⑥避免Select All 如果不查詢表中所有的列,避免使用 SELECT *,它會(huì)進(jìn)行全表掃描,不能有效利用索引。 ⑦Like 優(yōu)化 Like 用于模糊查詢,舉個(gè)例子

17、(field 已建立索引): SELECT?column?FROM?table?WHERE?field?like?'%keyword%';? 這個(gè)查詢未命中索引,換成下面的寫(xiě)法: SELECT?column?FROM?table?WHERE?field?like?'keyword%';? 去除了前面的 % 查詢將會(huì)命中索引,但是產(chǎn)品經(jīng)理一定要前后模糊匹配呢?全文索引 fulltext 可以嘗試一下,但 Elasticsearch 才是終極武器。 ⑧Join 優(yōu)化 Join 的實(shí)現(xiàn)是采用 Nested Loop Join 算法,就是通過(guò)驅(qū)動(dòng)表的結(jié)果集作為基礎(chǔ)數(shù)據(jù),通過(guò)該結(jié)數(shù)據(jù)作為過(guò)濾

18、條件到下一個(gè)表中循環(huán)查詢數(shù)據(jù),然后合并結(jié)果。 如果有多個(gè) Join,則將前面的結(jié)果集作為循環(huán)數(shù)據(jù),再次到后一個(gè)表中查詢數(shù)據(jù)。 驅(qū)動(dòng)表和被驅(qū)動(dòng)表盡可能增加查詢條件,滿足 ON 的條件而少用 Where,用小結(jié)果集驅(qū)動(dòng)大結(jié)果集。 被驅(qū)動(dòng)表的 Join 字段上加上索引,無(wú)法建立索引的時(shí)候,設(shè)置足夠的 Join Buffer Size。 禁止 Join 連接三個(gè)以上的表,嘗試增加冗余字段。 ⑨Limit 優(yōu)化 Limit 用于分頁(yè)查詢時(shí)越往后翻性能越差,解決的原則:縮小掃描范圍,如下所示: select * from orders order by id desc limit 10000

19、0,10 耗時(shí)0.4秒 select * from orders order by id desc limit 1000000,10 耗時(shí)5.2秒 先篩選出 ID 縮小查詢范圍,寫(xiě)法如下: select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10 耗時(shí)0.5秒 如果查詢條件僅有主鍵 ID,寫(xiě)法如下: select id from orders where id between 1000000 and 1000010 order by id desc 耗時(shí)0.3秒 如果以上方案依然很慢呢?只好用游標(biāo)了,感興趣的朋友閱讀 JDBC 使用游標(biāo)實(shí)現(xiàn)分頁(yè)查詢的方法。 其他數(shù)據(jù)庫(kù) 作為一名后端開(kāi)發(fā)人員,務(wù)必精通作為存儲(chǔ)核心的 MySQL 或 SQL Server,也要積極關(guān)注 NoSQL 數(shù)據(jù)庫(kù),他們已經(jīng)足夠成熟并被廣泛采用,能解決特定場(chǎng)景下的性能瓶頸。

展開(kāi)閱讀全文
溫馨提示:
1: 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
2: 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
3.本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
5. 裝配圖網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

相關(guān)資源

更多
正為您匹配相似的精品文檔
關(guān)于我們 - 網(wǎng)站聲明 - 網(wǎng)站地圖 - 資源地圖 - 友情鏈接 - 網(wǎng)站客服 - 聯(lián)系我們

copyright@ 2023-2025  zhuangpeitu.com 裝配圖網(wǎng)版權(quán)所有   聯(lián)系電話:18123376007

備案號(hào):ICP2024067431號(hào)-1 川公網(wǎng)安備51140202000466號(hào)


本站為文檔C2C交易模式,即用戶上傳的文檔直接被用戶下載,本站只是中間服務(wù)平臺(tái),本站所有文檔下載所得的收益歸上傳人(含作者)所有。裝配圖網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)上載內(nèi)容本身不做任何修改或編輯。若文檔所含內(nèi)容侵犯了您的版權(quán)或隱私,請(qǐng)立即通知裝配圖網(wǎng),我們立即給予刪除!