<th id="uz6zr"><table id="uz6zr"><sub id="uz6zr"></sub></table></th> <tbody id="uz6zr"></tbody>
  • <menuitem id="uz6zr"><dfn id="uz6zr"></dfn></menuitem><menuitem id="uz6zr"><strong id="uz6zr"></strong></menuitem>
      1. 面試官扎心一問:有什么優化方案

        發表于:2020-10-23 09:25  作者:佚名   來源:ITPUB

        字體: | 上一篇 | 下一篇 |我要投稿 | 推薦標簽: 軟件開發 數據庫

          當需要從數據庫查詢的表有上萬條記錄的時候,一次性查詢所有結果會變得很慢,特別是隨著數據量的增加特別明顯,這時需要使用分頁查詢。對于數據庫分頁查詢,也有很多種方法和優化的點。下面簡單說一下我知道的一些方法。
          準備工作
          為了對下面列舉的一些優化進行測試,下面針對已有的一張表進行說明。
          ·表名:order_history
          ·描述:某個業務的訂單歷史表
          ·主要字段:unsigned int id,tinyint(4) int type
          ·字段情況:該表一共37個字段,不包含text等大型數據,最大為varchar(500),id字段為索引,且為遞增。
          ·數據量:5709294
          ·MySQL版本:5.7.16線下找一張百萬級的測試表可不容易,如果需要自己測試的話,可以寫shell腳本什么的插入數據進行測試。以下的 sql 所有語句執行的環境沒有發生改變,下面是基本測試結果: 
          select count(*) from orders_history; 
          返回結果:5709294
          三次查詢時間分別為:
          ·8903 ms
          ·8323 ms
          ·8401 ms
          一般分頁查詢
          一般的分頁查詢使用簡單的 limit 子句就可以實現。limit 子句聲明如下:
          SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 
          LIMIT 子句可以被用于指定 SELECT 語句返回的記錄數。需注意以下幾點:
          ·第一個參數指定第一個返回記錄行的偏移量,注意從0開始
          ·第二個參數指定返回記錄行的最大數目
          ·如果只給定一個參數:它表示返回最大的記錄行數目
          ·第二個參數為 -1 表示檢索從某一個偏移量到記錄集的結束所有的記錄行
          ·初始記錄行的偏移量是 0(而不是 1)
          下面是一個應用實例:
          select * from orders_history where type=8 limit 1000,10;
          該條語句將會從表 orders_history 中查詢offset: 1000開始之后的10條數據,也就是第1001條到第1010條數據(1001 <= id <= 1010)。
          數據表中的記錄默認使用主鍵(一般為id)排序,上面的結果相當于:
          select * from orders_history where type=8 order by id limit 10000,10; 
          三次查詢時間分別為:
          ·3040 ms
          ·3063 ms
          ·3018 ms
          針對這種查詢方式,下面測試查詢記錄量對時間的影響:
          select * from orders_history where type=8 limit 10000,1; 
          select * from orders_history where type=8 limit 10000,10; 
          select * from orders_history where type=8 limit 10000,100; 
          select * from orders_history where type=8 limit 10000,1000; 
          select * from orders_history where type=8 limit 10000,10000;
          三次查詢時間如下:
          ·查詢1條記錄:3072ms 3092ms 3002ms
          ·查詢10條記錄:3081ms 3077ms 3032ms
          ·查詢100條記錄:3118ms 3200ms 3128ms
          ·查詢1000條記錄:3412ms 3468ms 3394ms
          ·查詢10000條記錄:3749ms 3802ms 3696ms
          另外我還做了十來次查詢,從查詢時間來看,基本可以確定,在查詢記錄量低于100時,查詢時間基本沒有差距,隨著查詢記錄量越來越大,所花費的時間也會越來越多。
          針對查詢偏移量的測試:
          select * from orders_history where type=8 limit 100,100; 
          select * from orders_history where type=8 limit 1000,100; 
          select * from orders_history where type=8 limit 10000,100; 
          select * from orders_history where type=8 limit 100000,100; 
          select * from orders_history where type=8 limit 1000000,100; 
          三次查詢時間如下:
          ·查詢100偏移:25ms 24ms 24ms
          ·查詢1000偏移:78ms 76ms 77ms
          ·查詢10000偏移:3092ms 3212ms 3128ms
          ·查詢100000偏移:3878ms 3812ms 3798ms
          ·查詢1000000偏移:14608ms 14062ms 14700ms
          隨著查詢偏移的增大,尤其查詢偏移大于10萬以后,查詢時間急劇增加。
          這種分頁查詢方式會從數據庫第一條記錄開始掃描,所以越往后,查詢速度越慢,而且查詢的數據越多,也會拖慢總查詢速度。
          使用子查詢優化
          這種方式先定位偏移位置的 id,然后往后查詢,這種方式適用于 id 遞增的情況。
          select * from orders_history where type=8 limit 100000,1; 
           
          select id from orders_history where type=8 limit 100000,1; 
           
          select * from orders_history where type=8 and 
          id>=(select id from orders_history where type=8 limit 100000,1) 
          limit 100; 
           
          select * from orders_history where type=8 limit 100000,100;
          4條語句的查詢時間如下:
          ·第1條語句:3674ms
          ·第2條語句:1315ms
          ·第3條語句:1327ms
          ·第4條語句:3710ms
          針對上面的查詢需要注意:
          ·比較第1條語句和第2條語句:使用 select id 代替 select * 速度增加了3倍
          ·比較第2條語句和第3條語句:速度相差幾十毫秒
          ·比較第3條語句和第4條語句:得益于 select id 速度增加,第3條語句查詢速度增加了3倍
          這種方式相較于原始一般的查詢方法,將會增快數倍。
          使用 id 限定優化
          這種方式假設數據表的id是連續遞增的,則我們根據查詢的頁數和查詢的記錄數可以算出查詢的id的范圍,可以使用 id between and 來查詢:
          select * from orders_history where type=2 
          and id between 1000000 and 1000100 limit 100; 
          查詢時間:15ms 12ms 9ms
          這種查詢方式能夠極大地優化查詢速度,基本能夠在幾十毫秒之內完成。限制是只能使用于明確知道id的情況,不過一般建立表的時候,都會添加基本的id字段,這為分頁查詢帶來很多便利。
          還可以有另外一種寫法:
          select * from orders_history where id >= 1000001 limit 100; 
          當然還可以使用 in 的方式來進行查詢,這種方式經常用在多表關聯的時候進行查詢,使用其他表查詢的id集合,來進行查詢:
          select * from orders_history where id >= 1000001 limit 100;
          這種 in 查詢的方式要注意:某些 mysql 版本不支持在 in 子句中使用 limit。
          使用臨時表優化
          這種方式已經不屬于查詢優化,這兒附帶提一下。
          對于使用 id 限定優化中的問題,需要 id 是連續遞增的,但是在一些場景下,比如使用歷史表的時候,或者出現過數據缺失問題時,可以考慮使用臨時存儲的表來記錄分頁的id,使用分頁的id來進行 in 查詢。這樣能夠極大的提高傳統的分頁查詢速度,尤其是數據量上千萬的時候。
          關于數據表的id說明
          一般情況下,在數據庫中建立表的時候,強制為每一張表添加 id 遞增字段,這樣方便查詢。
          如果像是訂單庫等數據量非常龐大,一般會進行分庫分表。這個時候不建議使用數據庫的 id 作為唯一標識,而應該使用分布式的高并發唯一 id 生成器來生成,并在數據表中使用另外的字段來存儲這個唯一標識。
          使用先使用范圍查詢定位 id (或者索引),然后再使用索引進行定位數據,能夠提高好幾倍查詢速度。即先 select id,然后再 select *;

          本文內容不用于商業目的,如涉及知識產權問題,請權利人聯系51Testing小編(021-64471599-8017),我們將立即處理

        評 論

        論壇新帖

        頂部 底部


        建議使用IE 6.0以上瀏覽器,800×600以上分辨率,法律顧問:上海瀛東律師事務所 張楠律師
        版權所有 上海博為峰軟件技術股份有限公司 Copyright©51testing.com 2003-2020, 滬ICP備05003035號
        投訴及意見反饋:webmaster@51testing.com; 業務聯系:service@51testing.com 021-64471599-8017

        滬公網安備 31010102002173號

        51Testing官方微信

        51Testing官方微博

        掃一掃 測試知識全知道

        在线看黄AV免费悠久