• <tbody id="iuc8i"><rt id="iuc8i"></rt></tbody><bdo id="iuc8i"><wbr id="iuc8i"></wbr></bdo>
  • <td id="iuc8i"><option id="iuc8i"></option></td>
    <table id="iuc8i"></table>

    北京Java培訓
    達內北京中關村中心

    010-62126400

    北京Java培訓班技術干貨:一手好 SQL 是如何煉成的?

    • 時間:2020-06-10 17:18
    • 發布:北京java培訓
    • 來源:java知識庫

    本文達內北京Java培訓機構為大家分享一位java技術人員的工作經驗,他改造這些SQL的過程中,總結了一些經驗分享給大家,如果有錯誤歡迎批評指正。

    MySQL性能

    最大數據量

    拋開數據量和并發數,談性能都是耍流氓。MySQL沒有限制單表最大記錄數,它取決于操作系統對文件大小的限制。

    《阿里巴巴Java開發手冊》提出單表行數超過500萬行或者單表容量超過2GB,才推薦分庫分表。性能由綜合因素決定,拋開業務復雜度,影響程度依次是硬件配置、MySQL配置、數據表設計、索引優化。500萬這個值僅供參考,并非鐵律。微信搜索web_resource 關注獲取更多推送。

    博主曾經操作過超過4億行數據的單表,分頁查詢最新的20條記錄耗時0.6秒,SQL語句大致是select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20,prePageMinId是上一頁數據記錄的最小ID。

    雖然當時查詢速度還湊合,隨著數據不斷增長,有朝一日必定不堪重負。分庫分表是個周期長而風險高的大活兒,應該盡可能在當前結構上優化,比如升級硬件、遷移歷史數據等等,實在沒轍了再分。對分庫分表感興趣的同學可以閱讀分庫分表的基本思想。

    最大并發數

    并發數是指同一時刻數據庫能處理多少個請求,由max_connections和max_user_connections決定。max_connections是指MySQL實例的最大連接數,上限值是16384,max_user_connections是指每個數據庫用戶的最大連接數。

    MySQL會為每個連接提供緩沖區,意味著消耗更多的內存。如果連接數設置太高硬件吃不消,太低又不能充分利用硬件。一般要求兩者比值超過10%,計算方法如下:

    max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%

    查看最大連接數與響應最大連接數:

    show variables like '%max_connections%';

    show variables like '%max_user_connections%';

    在配置文件my.cnf中修改最大連接數

    [mysqld]

    max_connections = 100

    max_used_connections = 20

    查詢耗時0.5秒

    建議將單次查詢耗時控制在0.5秒以內,0.5秒是個經驗值,源于用戶體驗的3秒原則。如果用戶的操作3秒內沒有響應,將會厭煩甚至退出。響應時間=客戶端UI渲染耗時+網絡請求耗時+應用程序處理耗時+查詢數據庫耗時,0.5秒就是留給數據庫1/6的處理時間。

    實施原則

    相比NoSQL數據庫,MySQL是個嬌氣脆弱的家伙。它就像體育課上的女同學,一點糾紛就和同學鬧別扭(擴容難),跑兩步就氣喘吁吁(容量小并發低),常常身體不適要請假(SQL約束太多)。如今大家都會搞點分布式,應用程序擴容比數據庫要容易得多,所以實施原則是數據庫少干活,應用程序多干活。

    充分利用但不濫用索引,須知索引也消耗磁盤和CPU。

    不推薦使用數據庫函數格式化數據,交給應用程序處理。

    不推薦使用外鍵約束,用應用程序保證數據準確性。

    寫多讀少的場景,不推薦使用唯一索引,用應用程序保證唯一性。

    適當冗余字段,嘗試創建中間表,用應用程序計算中間結果,用空間換時間。

    不允許執行極度耗時的事務,配合應用程序拆分成更小的事務。

    預估重要數據表(比如訂單表)的負載和數據增長態勢,提前優化。

    數據表設計

    數據類型

    數據類型的選擇原則:更簡單或者占用空間更小。

    如果長度能夠滿足,整型盡量使用tinyint、smallint、medium_int而非int。

    如果字符串長度確定,采用char類型。

    如果varchar能夠滿足,不采用text類型。

    精度要求較高的使用decimal類型,也可以使用BIGINT,比如精確兩位小數就乘以100后保存。

    盡量采用timestamp而非datetime。

    相比datetime,timestamp占用更少的空間,以UTC的格式儲存自動轉換時區。

    避免空值

    MySQL中字段為NULL時依然占用空間,會使索引、索引統計更加復雜。從NULL值更新到非NULL無法做到原地更新,容易發生索引分裂影響性能。盡可能將NULL值用有意義的值代替,也能避免SQL語句里面包含is ot null的判斷。微信搜索web_resource 關注獲取更多推送。微信搜索web_resource 關注獲取更多推送。

    text類型優化

    由于text字段儲存大量數據,表容量會很早漲上去,影響其他字段的查詢性能。建議抽取出來放在子表里,用業務主鍵關聯。

    索引優化

    索引分類

    普通索引:最基本的索引。

    組合索引:多個字段上建立的索引,能夠加速復合查詢條件的檢索。

    唯一索引:與普通索引類似,但索引列的值必須唯一,允許有空值。

    組合唯一索引:列值的組合必須唯一。

    主鍵索引:特殊的唯一索引,用于唯一標識數據表中的某一條記錄,不允許有空值,一般用primary key約束。

    全文索引:用于海量文本的查詢,MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查詢精度以及擴展性不佳,更多的企業選擇Elasticsearch。

    索引優化

    分頁查詢很重要,如果查詢數據量超過30%,MYSQL不會使用索引。

    單表索引數不超過5個、單個索引字段數不超過5個。

    字符串可使用前綴索引,前綴長度控制在5-8個字符。

    字段唯一性太低,增加索引沒有意義,如:是否刪除、性別。

    合理使用覆蓋索引,如下所示:

    select login_name, nick_name from member where login_name = ?

    login_name, nick_name兩個字段建立組合索引,比login_name簡單索引要更快。

    SQL優化

    分批處理

    博主小時候看到魚塘挖開小口子放水,水面有各種漂浮物。浮萍和樹葉總能順利通過出水口,而樹枝會擋住其他物體通過,有時還會卡住,需要人工清理。MySQL就是魚塘,最大并發數和網絡帶寬就是出水口,用戶SQL就是漂浮物。微信搜索web_resource 關注獲取更多推送。

    不帶分頁參數的查詢或者影響大量數據的update和delete操作,都是樹枝,我們要把它打散分批處理,舉例說明:

    業務描述:更新用戶所有已過期的優惠券為不可用狀態。

    SQL語句:

    update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;

    如果大量優惠券需要更新為不可用狀態,執行這條SQL可能會堵死其他SQL,分批處理偽代碼如下:

    int pageNo = 1;

    int PAGE_SIZE = 100;

    while(true) {

    List batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 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 ++;

    }

    操作符<>優化

    通常<>操作符無法使用索引,舉例如下,查詢金額不為100元的訂單:

    select id from orders where amount != 100;

    如果金額為100的訂單極少,這種數據分布嚴重不均的情況下,有可能使用索引。鑒于這種不確定性,采用union聚合搜索結果,改寫方法如下:

    (select id from orders where amount > 100)

    union all

    (select id from orders where amount < 100 and amount > 0)

    OR優化

    在Innodb引擎下or無法使用組合索引,比如:

    select id,product_name from orders where mobile_no = '13421800407' or user_id = 100;

    OR無法命中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);

    此時id和product_name字段都有索引,查詢才最高效。

    IN優化

    IN適合主表大子表小,EXIST適合主表小子表大。由于查詢優化器的不斷升級,很多場景這兩者性能差不多一樣了。

    嘗試改為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';

    不做列運算

    通常在查詢條件列運算會導致索引失效,如下所示:

    查詢當日訂單

    select id from order where date_format(create_time,'%Y-%m-%d') = '2019-07-01';

    date_format函數會導致這個查詢無法使用索引,改寫后:

    select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

    避免Select all

    如果不查詢表中所有的列,避免使用SELECT *,它會進行全表掃描,不能有效利用索引。

    Like優化

    like用于模糊查詢,舉個例子(field已建立索引):

    SELECT column FROM table WHERE field like '%keyword%';

    這個查詢未命中索引,換成下面的寫法:

    SELECT column FROM table WHERE field like 'keyword%';

    去除了前面的%查詢將會命中索引,但是產品經理一定要前后模糊匹配呢?全文索引fulltext可以嘗試一下,但Elasticsearch才是終極武器。

    Join優化

    join的實現是采用Nested Loop Join算法,就是通過驅動表的結果集作為基礎數據,通過該結數據作為過濾條件到下一個表中循環查詢數據,然后合并結果。如果有多個join,則將前面的結果集作為循環數據,再次到后一個表中查詢數據。

    驅動表和被驅動表盡可能增加查詢條件,滿足ON的條件而少用Where,用小結果集驅動大結果集。

    被驅動表的join字段上加上索引,無法建立索引的時候,設置足夠的Join Buffer Size。

    禁止join連接三個以上的表,嘗試增加冗余字段。微信搜索web_resource 關注獲取更多推送。

    Limit優化

    limit用于分頁查詢時越往后翻性能越差,解決的原則:縮小掃描范圍,如下所示:

    select * from orders order by id desc limit 100000,10

    耗時0.4秒

    select * from orders order by id desc limit 1000000,10

    耗時5.2秒

    先篩選出ID縮小查詢范圍,寫法如下:

    select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10

    耗時0.5秒

    如果查詢條件僅有主鍵ID,寫法如下:

    select id from orders where id between 1000000 and 1000010 order by id desc

    耗時0.3秒

    如果以上方案依然很慢呢?只好用游標了,感興趣的朋友閱讀JDBC使用游標實現分頁查詢的方法

    其他數據庫

    作為一名后端開發人員,務必精通作為存儲核心的MySQL或SQL Server,也要積極關注NoSQL數據庫,他們已經足夠成熟并被廣泛采用,能解決特定場景下的性能瓶頸。

    達內18年Java培訓經驗,引領行業的Java課程,符合企業需求的Java技術,一線Java大牛授課,累計為100萬學員實現夢想,是你學Java的不二之選。

    Java程序員能賺多少月薪?

    Java自學能不能學會?

    零基礎學Java要從哪里入手?

    轉行學Java多久能月薪過萬

    達內北京Java培訓機構Java和Java大數據免費訓練營報名中,跟一線工程師學習Java、大數據技術、了解行業發展+就業前景,實現快速高薪就業!點擊頁面底部「閱讀原文」報名吧!

    上一篇:掌握正確學習路徑,入門Java大數據非常簡單!
    下一篇:3個閱讀跟蹤Java源碼的小技巧,很實用!

    馬上預約七天免費體驗課

    姓名:

    電話:

    達內北京Java培訓班:3本Java學習書籍分享!

    達內北京Java培訓班:5張圖詳細說新手如何學Java

    達內北京Java培訓:教你5分鐘搞定Java單例模式

    Java寫代碼第一步:配置環境及安裝開發工具

    選擇城市和中心
    江西省

    貴州省

    廣西省

    海南省

    豆奶最新版本下载,美女恶动态图gif动态图片,国产女明星视频在线,草草线在成年在线视频
  • <tbody id="iuc8i"><rt id="iuc8i"></rt></tbody><bdo id="iuc8i"><wbr id="iuc8i"></wbr></bdo>
  • <td id="iuc8i"><option id="iuc8i"></option></td>
    <table id="iuc8i"></table>