2023-08-12 13:11:24 52 0

- N +

sql語句優(yōu)化有哪些方法 sql優(yōu)化的一般步驟

大家好，今天小編來為大家解答sql語句優(yōu)化有哪些方法這個問題，sql優(yōu)化的一般步驟很多人還不知道，現(xiàn)在讓我們一起來看看吧！

mysql優(yōu)化教程

1、對SQL語句、索引、表結構等進行優(yōu)化。

2、開啟查詢緩存，QueryCache緩存了SELECT查詢及其結果數(shù)據(jù)集，當執(zhí)行一個同樣的SELECT查詢時，MySQL會從內(nèi)存中直接取出結果，加快了查詢執(zhí)行速度、減小了數(shù)據(jù)庫的壓力。執(zhí)行SHOWVARIABLESLIKE'have_query_cache';可以查看MySQL查詢緩存是否打開，開啟查詢緩存只需配置my.cnf文件即可，具體如下：

query_cache_type=1

query_cache_size=128M

query_cache_limit=1M

保存好后重啟MySQL。

3、選用InnoDB存儲引擎，MySQL常用存儲引擎是MyISAM和InnoDB，二者區(qū)別如下：

MyISAM

查詢速度快；

支持表級鎖，在上鎖期間表上不能進行其他操作；

支持全文檢索；

支持數(shù)據(jù)壓縮、自我復制、查詢緩存、數(shù)據(jù)加密；

不支持外鍵；

不支持事務，所以也就沒有COMMIT和ROLLBACK操作；

不支持集群數(shù)據(jù)庫。

InnoDB

支持行級鎖；

支持外鍵，對外鍵約束強制；

支持事務，可執(zhí)行COMMIT和ROLLBACK操作；

支持數(shù)據(jù)壓縮、自我復制、查詢緩存、數(shù)據(jù)加密；

可用在集群環(huán)境，但并不完全支持。InnoDB表可以轉(zhuǎn)換為NDB存儲引擎，這樣就能用在集群環(huán)境。

如何提高應用程序的sql語句的效率

1.SQL優(yōu)化的原則是：將一次操作需要讀取的BLOCK數(shù)減到最低,即在最短的時間達到最大的數(shù)據(jù)吞吐量。調(diào)整不良SQL通常可以從以下幾點切入：?檢查不良的SQL，考慮其寫法是否還有可優(yōu)化內(nèi)容?檢查子查詢考慮SQL子查詢是否可以用簡單連接的方式進行重新書寫?檢查優(yōu)化索引的使用?考慮數(shù)據(jù)庫的優(yōu)化器

2.避免出現(xiàn)SELECT*FROMtable語句，要明確查出的字段。

3.在一個SQL語句中，如果一個where條件過濾的數(shù)據(jù)庫記錄越多，定位越準確，則該where條件越應該前移。

4.查詢時盡可能使用索引覆蓋。即對SELECT的字段建立復合索引，這樣查詢時只進行索引掃描，不讀取數(shù)據(jù)塊。

5.在判斷有無符合條件的記錄時建議不要用SELECTCOUNT（*）和selecttop1語句。

6.使用內(nèi)層限定原則，在拼寫SQL語句時，將查詢條件分解、分類，并盡量在SQL語句的最里層進行限定，以減少數(shù)據(jù)的處理量。

7.應絕對避免在orderby子句中使用表達式。

大量數(shù)據(jù)查詢時候怎么優(yōu)化sql

1、優(yōu)化SQL語句，使用Where限定查詢的數(shù)據(jù)范圍2、建立相關字段的索引，避免查詢時進行全表掃描3、多數(shù)據(jù)表連接時，注意連接的主從表位置，避免小表Join大表

sql一次查詢需要關聯(lián)十幾張表，有什么優(yōu)化方案么

不知道開發(fā)的同學有沒有遇到過類似這樣的需求：

相同類型的數(shù)據(jù)在多個系統(tǒng)中，如果要得到全部的信息，就要連續(xù)調(diào)多個系統(tǒng)的接口；

業(yè)務復雜，一個需求需要關聯(lián)幾張表甚至幾十張表才能得到想要的結果；

系統(tǒng)做了分庫分表，但是需要統(tǒng)計所有的數(shù)據(jù)。

那么此類需求要如何滿足呢？我們選擇了“通過ETL提前進行數(shù)據(jù)整合”的方案。

什么是ETL

說到ETL，很多開發(fā)伙伴可能會有些陌生，更多的時候ETL是用在大數(shù)據(jù)、數(shù)據(jù)分析的相關崗位；我也是在近幾年的工作過程中才接觸到ETL的，現(xiàn)在的項目比較依賴ETL，可以說是項目中重要的一部分。

ETL是三個單詞的縮寫：

Extraction：抽取、提取；就是把數(shù)據(jù)從數(shù)據(jù)庫里面取出來；

Transformation：轉(zhuǎn)換；包括但不限于：數(shù)據(jù)篩選校驗、數(shù)據(jù)關聯(lián)、數(shù)據(jù)內(nèi)容及結構的修改、運算、統(tǒng)計等等；

Loading：加載；將處理后的數(shù)據(jù)保存到目標數(shù)據(jù)庫。

從這三個單詞基本可以了解ETL的作用：將各個業(yè)務系統(tǒng)的數(shù)據(jù)，通過抽取、清洗、轉(zhuǎn)換之后，將加工后的數(shù)據(jù)落地到數(shù)據(jù)庫中（數(shù)據(jù)倉庫）；在這個過程中，ETL可以將分散、零亂、標準不統(tǒng)一的數(shù)據(jù)整合到一起。

使用場景

我接觸過的項目，使用ETL工具的場景有這個幾種：

1.報表、BI系統(tǒng)：

在公司建設的初期，業(yè)務比較少，系統(tǒng)也比較少，一臺數(shù)據(jù)庫就搞定了；隨著公司業(yè)務的增加，業(yè)務系統(tǒng)被拆成很多系統(tǒng)；隨著數(shù)據(jù)量的繼續(xù)增加，單個系統(tǒng)的數(shù)據(jù)增加到一定程度的時候，也做了分庫分表；

這時候領導、業(yè)務人員在用數(shù)據(jù)做分析的時候，數(shù)據(jù)來源可能是多個系統(tǒng)的多張表，這時候企圖通過一個復雜的SQL跑出來結果就很困難了；通常公司會建立一個數(shù)據(jù)倉庫，通過ETL工具把數(shù)據(jù)抽取到數(shù)據(jù)倉庫中，再做數(shù)據(jù)的擬合和展示。

2.跨系統(tǒng)的數(shù)據(jù)加工或查詢：

我們現(xiàn)在所在公司，業(yè)務系統(tǒng)有幾百個，由于業(yè)務流程比較復雜，前端系統(tǒng)在做業(yè)務操作的時候，在正式提交交易之前，有很多業(yè)務校驗；

比如要查詢客戶在X系統(tǒng)的交易歷史，在Y系統(tǒng)的交易歷史，在Z系統(tǒng)的交易歷史；那么就需要分別調(diào)用X、Y、Z系統(tǒng)的接口，這個對前端系統(tǒng)很不友好，那么通常的解決方案是什么？

A方案：做一個中間服務，中間服務去調(diào)用X、Y、Z系統(tǒng)的接口，客戶端直接調(diào)用這個中間服務；這種方案只是把前端要做的事情，轉(zhuǎn)移到了中間服務；

B方案：整合X、Y、Z三個系統(tǒng)，建服務中臺；這種方法很好，但是極為難，對于很多公司來說，別說把X、Y、Z三個系統(tǒng)整合成一個中臺系統(tǒng)，就是其中一個系統(tǒng)本身進行重構，都是非常困難的；

C方案：把X、Y、Z三個系統(tǒng)中需要的數(shù)據(jù)，通過ETL抽取加工到一個數(shù)據(jù)倉庫中，對外提供服務；這個系統(tǒng)最大的好處是在不改造X、Y、Z三個系統(tǒng)的前提下，又可以實現(xiàn)跨系統(tǒng)的查詢。

我們在C方案的基礎上又往前做了一步，就是將落地后的數(shù)據(jù)又做了一次加工，將需要跨表關聯(lián)的數(shù)據(jù)，提前關聯(lián)好存入MongoDB中，對外提供查詢服務；這樣可以將多表關聯(lián)查詢，變成了單表查詢。

吐數(shù)據(jù)VS抽數(shù)據(jù)

接上文中第二個例子中的C方案，有些同學可能會有個疑問：數(shù)據(jù)抽取，需要抽取哪些數(shù)據(jù)呢？為什么不讓這些系統(tǒng)把數(shù)據(jù)吐出來呢？

答案也簡單，“有的時候，數(shù)據(jù)不一定能吐出來”。

MySQL數(shù)據(jù)庫往外吐數(shù)據(jù)有比較成熟的中間件，比如Canal，它可以通過監(jiān)聽Mysql的binlog日志來獲取數(shù)據(jù)，binlog設置為row模式，能夠獲取到每一條新增、刪除、修改的日志，同時還能獲取到修改前后的數(shù)據(jù)；

其他商用數(shù)據(jù)庫，比如Oracle、DB2等，我也查閱過相關的資料，也是有觸發(fā)器機制，可以當數(shù)據(jù)發(fā)生變化的時候通知出來，比如調(diào)用一段程序，將數(shù)據(jù)發(fā)送到消息隊列中，再由其他程序監(jiān)聽消息隊列做后續(xù)處理。

不管什么類型的數(shù)據(jù)庫，這種“吐數(shù)據(jù)”的方案，對于基礎設施的要求都比較高，并且對原有系統(tǒng)有一定的侵入性；所以我們采用了對原有系統(tǒng)侵入性更小的方案：主動抽數(shù)據(jù)。

ETL方案的優(yōu)缺點

1.優(yōu)點

侵入性較低，數(shù)據(jù)源系統(tǒng)只需要開通數(shù)據(jù)庫的訪問權限即可，為保證數(shù)據(jù)抽取對業(yè)務的影響，通常是訪問源系統(tǒng)的備庫，并且單獨設置一個只讀權限的數(shù)據(jù)庫用戶；

支持不同類型數(shù)據(jù)源的數(shù)據(jù)抽取，比如源庫有Mysql、DB2、Oracle，通過ETL也可以輕松搞定；

數(shù)據(jù)整合，將不同業(yè)務系統(tǒng)的相同數(shù)據(jù)整合在一起，比如有些系統(tǒng)M/F表示男女，有些系統(tǒng)1/0表示男女，ETL在抽取加工后轉(zhuǎn)換成統(tǒng)一的編碼；

2.缺點

比較致命的一個缺點，就是數(shù)據(jù)抽取和加工有一定的延遲，需要根據(jù)業(yè)務場景進行評估，是否接受這個延遲；

可能會受到源庫表結構變化的影響；

如果源庫中的表沒有時間戳，或者時間戳不準確，那么增量抽取就變得很困難；

需要招聘ETL開發(fā)崗，從我目前的經(jīng)驗看，不是特別好招。

我將持續(xù)分享Java開發(fā)、架構設計、程序員職業(yè)發(fā)展等方面的見解，希望能得到你的關注。