首頁 >數據庫 >SQL > 正文

SQL查詢如何優化?(詳解)

轉載2019-11-30 17:54:000312
第十期線上培訓班

為什么要優化

系統的吞吐量瓶頸往往出現在數據庫的訪問速度上,即隨著應用程序的運行,數據庫的中的數據會越來越多,處理時間會相應變慢,且數據是存放在磁盤上的,讀寫速度無法和內存相比

如何優化

1、設計數據庫時:數據庫表、字段的設計,存儲引擎

2、利用好MySQL自身提供的功能,如索引,語句寫法的調優

3、MySQL集群、分庫分表、讀寫分離

關于SQL語句的優化的方法方式,網絡有很多經驗,所以本文拋開這些,設法在DAO層的優化和數據庫設計優化上建樹,并列舉兩個簡單實例

例子1:ERP查詢優化

現狀分析:

1、缺少關聯索引
2、Mysql本身的性能所限,對多個表的關聯支持不好,目前的性能主要集中在列表查詢上面,列表查詢關聯了很多表

應對方法:

1 增加必要的索引:通過explain查看執行記錄,根據執行計劃添加索引;
2 先統計業務數據主表主鍵,獲取較小結果集,然后再利用結果集關聯查詢;
1) 先根據主表和條件查詢顯示業務數據的主鍵
2) 根據主鍵作為查詢條件,再關聯其他關聯表,查詢需要的業務字段
3) 在主表查詢時,針對需要關聯其他表的查詢條件,需要做只有設置這個條件,才會做表關聯的設置

例如 有如下表 TT_A   TT_B    TT_C  TT_D

假設未優化前的SQL是這樣的

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....

FROM  TT_A A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?.....

那么優化后的SQL是

第一步

SELECT
    A.ID

FROM  TT_A A
WHERE 1=1AND A.XX = ?AND A.VV = ?第二步

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....
FROM  ( SELECT A.ID,..... FROM  TT_A  WHERE ID IN (1,2,3..)  ) A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?

小結:

這種優化適用于,列表查詢,因為一個列表查詢的條件一般都是和主表掛鉤的,所以利用這一點,建立關鍵字段索引,同時通過查詢條件的限制大大的縮小主表的數據量。這樣關聯其他表的時候就會快的多

例子2:文章搜索優化

假設你要做個貼吧的文章搜索功能,最簡單直接的存儲結構,就是利用關系數據庫,創建這樣一個存儲文章的關系數據庫表 TT_ARTICLES:

那么,假如現在的搜索關鍵字是“目標”,我們就可以利用字符串匹配的方式來對 CONTENT 列進行匹配查詢:

select * from ARTICLES where CONTENT like '% 目標 %';

這很容易就實現了搜索功能。但是,這樣的方式有著明顯的問題,即使用 % 來進行字符串匹配是非常低效的,因此這樣的查詢需要遍歷整個表(全表掃描)。幾篇、幾十篇文章的時 候,還不是什么問題,但是如果有幾十萬、幾百萬的文章,這種方式是完全不可行的。且不說單獨的關系數據庫表就不能容納那么大的數據了,就是能夠容納,要掃描一遍,這里的時間代價是難以想象的

于是,我們就要引入“倒排索引”的技術了。在前面所述的場景下, 我們可以把這個概念拆分為兩個部分來解釋: 好,那上面的 ARTICLES 表依然存在,但現在需要添加一個關鍵字表 KEYWORDS,并且,KEYWORD 列需要添加索引,因此這條關鍵字的記錄可以被迅速找到:

當然,我們還需要一個關聯關系表把 KEYWORDS 表和 ARTICLES 表結合起來, KEYWORD_ID 和 ARTICLE_ID 作為聯合主鍵

你看,這其實是一個多對多的關系,即同一個關鍵字可以出現在多篇文章中,而一篇文章可 以包含多個不同的關鍵字。這樣,我們可以先根據被索引了的關鍵字,從 KEYWARDS 表 中找到相應的 KEYWORD_ID,進而根據它在上面的關聯關系表找到 ARTICLE_ID,再根據 它去 ARTICLES 表中找到對應的文章。

小結:

這看起來是三次查找,但是因為每次都走索引,就免去了全表掃描,在數據量較小的時候速 度并不慢,并且,在使用 SQL 實現的時候,這個過程完全可以放到一個 SQL 語句中。在數 據量較小的時候,上面的方法已經足夠好用了。 這樣解決了全表掃描和字符串 % 匹配查詢造成的性能問題。

總結:

在技術面試的時候,如果你能舉出實際的例子,或者是直接說自己開發過程的問題和收獲會讓面試分會加很多,回答邏輯性也要強一點,不要東一點西一點,容易把自己都繞暈的。例如,問為怎么優化SQL你不要一上來就直接回答加索引,你可以這樣回答:

面試官您好,首先我們的項目DB數據量遇到了瓶頸,導致列表查詢非常緩慢,給用戶的體驗不好,為了解決這個問題,有很多種方法,例如最基本的數據庫表設計,基本的SQL優化,MYSQL的集群,讀寫分離,分庫分表,架構上增加緩存層等,他們的優缺點……,綜合這些然后再結合我們項目特點,最后我們在技術選型的時候選了誰。

如果你這樣有條不紊,有理有據的回答了問題而且還說出這么多問題外的知識點,面試官會覺得你不只是一個會寫代碼的人,而是你邏輯清晰,你對技術選型,有自己的理解和思考

本文來自 SQL教程 欄目,歡迎學習!

以上就是SQL查詢如何優化?(詳解)的詳細內容,更多請關注php中文網其它相關文章!

php中文網最新課程二維碼
  • 相關標簽:SQL查詢
  • 本文轉載于:cnblogs,如有侵犯,請聯系[email protected]刪除
  • 相關文章

    相關視頻


    網友評論

    文明上網理性發言,請遵守 新聞評論服務協議

    我要評論
  • 專題推薦

    作者信息

    青燈夜游

    今天學習一小步,明天提升一大步

    最近文章
    SQL查詢如何優化?(詳解)312
    淺談ES6中的字符串(代碼示例)1029
    鮮為人知的 JSON.stringify 用法415
    推薦視頻教程
  • SQLite高級課程SQLite高級課程
  • SQL參考手冊SQL參考手冊
  • 數據庫mysql視頻教程數據庫mysql視頻教程
  • SQL趣味課堂SQL趣味課堂
  • MySQL數據庫技術小知識每日分享MySQL數據庫技術小知識每日分享
  • MySQL高級進階視頻教程MySQL高級進階視頻教程
  • 視頻教程分類
    第十期線上培訓班 足彩吧合买平台