1. 
          

          1. 新聞動(dòng)態(tài)

            如何去寫(xiě)一手好SQL?

            網(wǎng)站建設 發(fā)布者:cya 2020-01-04 08:55 訪(fǎng)問(wèn)量:317

            作者:編碼磚家

            鏈接:https://www.cnblogs.com/xiaoyangjia/p/11267191.html

            • MySQL性能

            • 數據表設計

            • 索引優(yōu)化

            • SQL優(yōu)化

            • 其他數據庫

            博主負責的項目主要采用阿里云數據庫MySQL,最近頻繁出現慢SQL告警,執行時(shí)間最長(cháng)的竟然高達5分鐘。導出日志后分析,主要原因竟然是沒(méi)有命中索引和沒(méi)有分頁(yè)處理。其實(shí)這是非常低級的錯誤,我不禁后背一涼,團隊成員的技術(shù)水平亟待提高啊。改造這些SQL的過(guò)程中,總結了一些經(jīng)驗分享給大家,如果有錯誤歡迎批評指正。

            MySQL性能

            最大數據量

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

            《阿里巴巴Java開(kāi)發(fā)手冊》提出單表行數超過(guò)500萬(wàn)行或者單表容量超過(guò)2GB,才推薦分庫分表。性能由綜合因素決定,拋開(kāi)業(yè)務(wù)復雜度,影響程度依次是硬件配置、MySQL配置、數據表設計、索引優(yōu)化。500萬(wàn)這個(gè)值僅供參考,并非鐵律。

            博主曾經(jīng)操作過(guò)超過(guò)4億行數據的單表,分頁(yè)查詢(xún)最新的20條記錄耗時(shí)0.6秒,SQL語(yǔ)句大致是select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20,prePageMinId是上一頁(yè)數據記錄的最小ID。

            雖然當時(shí)查詢(xún)速度還湊合,隨著(zhù)數據不斷增長(cháng),有朝一日必定不堪重負。分庫分表是個(gè)周期長(cháng)而風(fēng)險高的大活兒,應該盡可能在當前結構上優(yōu)化,比如升級硬件、遷移歷史數據等等,實(shí)在沒(méi)轍了再分。對分庫分表感興趣的同學(xué)可以閱讀分庫分表的基本思想。

            最大并發(fā)數

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

            MySQL會(huì )為每個(gè)連接提供緩沖區,意味著(zhù)消耗更多的內存。如果連接數設置太高硬件吃不消,太低又不能充分利用硬件。一般要求兩者比值超過(guò)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

            查詢(xún)耗時(shí)0.5秒

            建議將單次查詢(xún)耗時(shí)控制在0.5秒以?xún)龋?.5秒是個(gè)經(jīng)驗值,源于用戶(hù)體驗的3秒原則。如果用戶(hù)的操作3秒內沒(méi)有響應,將會(huì )厭煩甚至退出。響應時(shí)間=客戶(hù)端UI渲染耗時(shí)+網(wǎng)絡(luò )請求耗時(shí)+應用程序處理耗時(shí)+查詢(xún)數據庫耗時(shí),0.5秒就是留給數據庫1/6的處理時(shí)間。

            實(shí)施原則

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

            • 充分利用但不濫用索引,須知索引也消耗磁盤(pán)和CPU。

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

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

            • 寫(xiě)多讀少的場(chǎng)景,不推薦使用唯一索引,用應用程序保證唯一性。

            • 適當冗余字段,嘗試創(chuàng )建中間表,用應用程序計算中間結果,用空間換時(shí)間。

            • 不允許執行極度耗時(shí)的事務(wù),配合應用程序拆分成更小的事務(wù)。

            • 預估重要數據表(比如訂單表)的負載和數據增長(cháng)態(tài)勢,提前優(yōu)化。

            數據表設計

            數據類(lèi)型

            數據類(lèi)型的選擇原則:更簡(jiǎn)單或者占用空間更小。

            • 如果長(cháng)度能夠滿(mǎn)足,整型盡量使用tinyint、smallint、medium_int而非int。

            • 如果字符串長(cháng)度確定,采用char類(lèi)型。

            • 如果varchar能夠滿(mǎn)足,不采用text類(lèi)型。

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

            • 盡量采用timestamp而非datetime。

            相比datetime,timestamp占用更少的空間,以UTC的格式儲存自動(dòng)轉換時(shí)區。

            避免空值

            MySQL中字段為NULL時(shí)依然占用空間,會(huì )使索引、索引統計更加復雜。從NULL值更新到非NULL無(wú)法做到原地更新,容易發(fā)生索引分裂影響性能。盡可能將NULL值用有意義的值代替,也能避免SQL語(yǔ)句里面包含is not null的判斷。

            text類(lèi)型優(yōu)化

            由于text字段儲存大量數據,表容量會(huì )很早漲上去,影響其他字段的查詢(xún)性能。建議抽取出來(lái)放在子表里,用業(yè)務(wù)主鍵關(guān)聯(lián)。

            索引優(yōu)化

            索引分類(lèi)

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

            • 組合索引:多個(gè)字段上建立的索引,能夠加速復合查詢(xún)條件的檢索。

            • 唯一索引:與普通索引類(lèi)似,但索引列的值必須唯一,允許有空值。

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

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

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

            索引優(yōu)化

            • 分頁(yè)查詢(xún)很重要,如果查詢(xún)數據量超過(guò)30%,MYSQL不會(huì )使用索引。

            • 單表索引數不超過(guò)5個(gè)、單個(gè)索引字段數不超過(guò)5個(gè)。

            • 字符串可使用前綴索引,前綴長(cháng)度控制在5-8個(gè)字符。

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

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

            select login_name, nick_name from member where login_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ā)數和網(wǎng)絡(luò )帶寬就是出水口,用戶(hù)SQL就是漂浮物。

            不帶分頁(yè)參數的查詢(xún)或者影響大量數據的update和delete操作,都是樹(shù)枝,我們要把它打散分批處理,舉例說(shuō)明:

            業(yè)務(wù)描述:更新用戶(hù)所有已過(guò)期的優(yōu)惠券為不可用狀態(tài)。

            SQL語(yǔ)句:

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

            如果大量?jì)?yōu)惠券需要更新為不可用狀態(tài),執行這條SQL可能會(huì )堵死其他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 ++;
            }

            操作符<>優(yōu)化

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

            select id from orders where amount != 100;

            如果金額為100的訂單極少,這種數據分布嚴重不均的情況下,有可能使用索引。鑒于這種不確定性,采用union聚合搜索結果,改寫(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 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字段都有索引,查詢(xún)才最高效。

            IN優(yōu)化

            IN適合主表大子表小,EXIST適合主表小子表大。由于查詢(xún)優(yōu)化器的不斷升級,很多場(chǎng)景這兩者性能差不多一樣了。

            嘗試改為join查詢(xún),舉例如下:

            select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

            采用JOIN如下所示:

            select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

            不做列運算

            通常在查詢(xún)條件列運算會(huì )導致索引失效,如下所示:

            查詢(xún)當日訂單

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

            date_format函數會(huì )導致這個(gè)查詢(xún)無(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

            如果不查詢(xún)表中所有的列,避免使用SELECT *,它會(huì )進(jìn)行全表掃描,不能有效利用索引。

            Like優(yōu)化

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

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

            這個(gè)查詢(xún)未命中索引,換成下面的寫(xiě)法:

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

            去除了前面的%查詢(xún)將會(huì )命中索引,但是產(chǎn)品經(jīng)理一定要前后模糊匹配呢?全文索引fulltext可以嘗試一下,但Elasticsearch才是終極武器。

            Join優(yōu)化

            join的實(shí)現是采用Nested Loop Join算法,就是通過(guò)驅動(dòng)表的結果集作為基礎數據,通過(guò)該結數據作為過(guò)濾條件到下一個(gè)表中循環(huán)查詢(xún)數據,然后合并結果。如果有多個(gè)join,則將前面的結果集作為循環(huán)數據,再次到后一個(gè)表中查詢(xún)數據。

            驅動(dòng)表和被驅動(dòng)表盡可能增加查詢(xún)條件,滿(mǎn)足ON的條件而少用Where,用小結果集驅動(dòng)大結果集。
            被驅動(dòng)表的join字段上加上索引,無(wú)法建立索引的時(shí)候,設置足夠的Join Buffer Size。
            禁止join連接三個(gè)以上的表,嘗試增加冗余字段。

            Limit優(yōu)化

            limit用于分頁(yè)查詢(xún)時(shí)越往后翻性能越差,解決的原則:縮小掃描范圍,如下所示:

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

            耗時(shí)0.4秒

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

            耗時(shí)5.2秒

            先篩選出ID縮小查詢(xún)范圍,寫(xiě)法如下:

            select * from orders where id > (select id from orders order by id desc  limit 10000001order by id desc limit 0,10

            耗時(shí)0.5秒

            如果查詢(xún)條件僅有主鍵ID,寫(xiě)法如下:

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

            耗時(shí)0.3秒

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

            其他數據庫

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



            關(guān)鍵字: 開(kāi)封網(wǎng)站建設 SQL語(yǔ)句

            文章連接: http://www.gostscript.com/wzjss/666.html

            版權聲明:文章由 晨展科技 整理收集,來(lái)源于互聯(lián)網(wǎng)或者用戶(hù)投稿,如有侵權,請聯(lián)系我們,我們會(huì )立即刪除。如轉載請保留

            双腿国产亚洲精品无码不卡|国产91精品无码麻豆|97久久久久久久极品|无码人妻少妇久久中文字幕
                1.