詳解MySQL數據庫千萬級數據查詢和存儲

百萬級數據處理方案

數據存儲結構設計

表字段設計

  • 表字段 not null,因為 null 值很難查詢優化且占用額外的索引空間,推薦默認數字 0。
  • 數據狀態類型的字段,比如 status, type 等等,盡量不要定義負數,如 -1。因為這樣可以加上 UNSIGNED,數值容量就會擴大一倍。
  • 可以的話用 TINYINT、SMALLINT 等代替 INT,盡量不使用 BIGINT,因為占的空間更小。
  • 字符串類型的字段會比數字類型占的空間更大,所以盡量用整型代替字符串,很多場景是可以通過編碼邏輯來實現用整型代替的。
  • 字符串類型長度不要隨意設置,保證滿足業務的前提下盡量小。
  • 用整型來存 IP。
  • 單表不要有太多字段,建議在20以內。
  • 為能預見的字段提前預留,因為數據量越大,修改數據結構越耗時。

索引設計

  • 索引,空間換時間的優化策略,基本上根據業務需求設計好索引,足以應付百萬級的數據量,養成使用 explain 的習慣,關於 explain 也可以訪問:explain 讓你的 sql 寫的更踏實瞭解更多。
  • 一個常識:索引並不是越多越好,索引是會降低數據寫入性能的。
  • 索引字段長度盡量短,這樣能夠節省大量索引空間;
  • 取消外鍵,可交由程序來約束,性能更好。
  • 復合索引的匹配最左列規則,索引的順序和查詢條件保持一致,盡量去除沒必要的單列索引。
  • 值分佈較少的字段(不重復的較少)不適合建索引,比如像性別這種隻有兩三個值的情況字段建立索引意義不大。
  • 需要排序的字段建議加上索引,因為索引是會排序的,能提高查詢性能。
  • 字符串字段使用前綴索引,不使用全字段索引,可大幅減小索引空間。

查詢語句優化

  • 盡量使用短查詢替代復雜的內聯查詢。
  • 查詢不使用 select *,盡量查詢帶索引的字段,避免回表。
  • 盡量使用 limit 對查詢數量進行限制。
  • 查詢字段盡量落在索引上,尤其是復合索引,更需要註意最左前綴匹配。
  • 拆分大的 delete / insert 操作,一方面會鎖表,影響其他業務操作,還有一方面是 MySQL 對 sql 長度也是有限制的。
  • 不建議使用 MySQL 的函數,計算等,可先由程序處理,從上面提的一些點會發現,能交由程序處理的盡量不要把壓力轉至數據庫上。因為多數的服務器性能瓶頸都在數據庫上。
  • 查詢 count,性能:count(1) = count(*) > count(主鍵) > count(其他字段)。
  • 查詢操作符能用 between 則不用 in,能用 in 則不用 or。
  • 避免使用!=或<>、IS NULL或IS NOT NULL、IN ,NOT IN等這樣的操作符,因為這些查詢無法使用索引。
  • sql 盡量簡單,少用 join,不建議兩個 join 以上。

千萬級數據處理方案

數據存儲結構設計

到瞭這個階段的數據量,數據本身已經有很大的價值瞭,數據除瞭滿足常規業務需求外,還會有一些數據分析的需求。而這個時候數據可變動性不高,基本上不會考慮修改原有結構,一般會考慮從分區,分表,分庫三方面做優化:

分區:

  • 分區是根據一定的規則,數據庫把一個表分解成多個更小的、更容易管理的部分,是一種水平劃分。對應用來說是完全透明的,不影響應用的業務邏輯,即不用修改代碼。因此能存更多的數據,查詢,刪除也支持按分區來操作,從而達到優化的目的。如果有考慮分區,可以提前做準備,避免下列一些限制:
  • 一個表最多隻能有1024個分區(mysql5.6之後支持8192個分區)。但你實際操作的時候,最好不要一次性打開超過 100 個分區,因為打開分區也是有時間損耗的。
  • 如果分區字段中有主鍵或者唯一索引列,那麼所有主鍵列和唯一索引列都必須包含進來,如果表中有主鍵或唯一索引,那麼分區鍵必須是主鍵或唯一索引。
  • 分區表中無法使用外鍵約束。
  • NULL值會使分區過濾無效,這樣會被放入默認的分區裡,請千萬不要讓分區字段出現 NULL。
  • 所有分區必須使用相同的存儲引擎。

分表:

分表分水平分表和垂直分表。

水平分表即拆分成數據結構相同的各個小表,如拆分成 table1, table2…,從而緩解數據庫讀寫壓力。

垂直分表即將一些字段分出去形成一個新表,各個表數據結構不相同,可以優化高並發下鎖表的情況。

可想而知,分表的話,程序的邏輯是需要做修改的,所以,一般是在項目初期時,預見到大數據量的情況,才會考慮分表。後期階段不建議分表,成本很大。

分庫:

分庫一般是主從模式,一個數據庫服務器主節點復制到一個或多個從節點多個數據庫,主庫負責寫操作,從庫負責讀操作,從而達到主從分離,高可用,數據備份等優化目的。

當然,主從模式也會有一些缺陷,主從同步延遲,binlog 文件太大導致的問題等等,這裡不細講(筆者也學不動瞭)。

其他:

冷熱表隔離。對於歷史的數據,查詢和使用的人數少的情況,可以移入另一個冷數據庫裡,隻提供查詢用,來緩解熱表數據量大的情況。

數據庫表主鍵設計

數據庫主鍵設計,個人推薦帶有時間屬性的自增長數字ID。(分佈式自增長ID生成算法)

  • 雪花算法
  • 百度分佈式ID算法
  • 美團分佈式ID算法

為什麼要使用這些算法呢,這個與MySQL數據存儲結構有關

從業務上來說:

在設計數據庫時不需要費盡心思去考慮設置哪個字段為主鍵。然後是這些字段隻是理論上是唯一的,例如使用圖書編號為主鍵,這個圖書編號隻是理論上來說是唯一的,但實踐中可能會出現重復的情況。所以還是設置一個與業務無關的自增ID作為主鍵,然後增加一個圖書編號的唯一性約束。

從技術上來說:

1.如果表使用自增主鍵,那麼每次插入新的記錄,記錄就會順序添加到當前索引節點的後續位置,當一頁寫滿,就會自動開辟一個新的頁。 總的來說就是可以提高查詢和插入的性能。

2.對InnoDB來說主鍵索引既存儲索引值,又在葉子節點中存儲行的數據,也就是說數據文件本身就是按照b+樹方式存放數據的。

3.如果沒有定義主鍵,則會使用非空的UNIQUE鍵做主鍵 ; 如果沒有非空的UNIQUE鍵,則系統生成一個6字節的rowid做主鍵;聚簇索引中,N行形成一個頁(一頁通常大小為16K)。如果碰到不規則數據插入時,為瞭保持B+樹的平衡,會造成頻繁的頁分裂和頁旋轉,插入速度比較慢。所以聚簇索引的主鍵值應盡量是連續增長的值,而不是隨機值(不要用隨機字符串或UUID)。

4.故對於InnoDB的主鍵,盡量用整型,而且是遞增的整型。這樣在存儲/查詢上都是非常高效的。

MySQL面試題

MySQL數據庫千萬級數據查詢優化方案

limit分頁查詢越靠後查詢越慢。這也讓我們得出一個結論:

1、limit語句的查詢時間與起始記錄的位置成正比。

2、mysql的limit語句是很方便,但是對記錄很多的表並不適合直接使用

表使用InnoDB作為存儲引擎,id作為自增主鍵,默認為主鍵索引

SELECT id FROM test LIMIT 9000000,100;

現在優化的方案有兩種,即通過id作為查詢條件使用子查詢實現和使用join實現;

1、id>=的(子查詢)形式實現

select * from test where id >= (select id from test limit 9000000,1)limit 0,100

使用join的形式;

SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 9000000,100) b ON a.id = b.id

這兩種優化查詢使用時間比較接近,其實兩者用的都是一個原理,所以效果也差不多。但個人建議最好使用join,盡量減少子查詢的使用。註:目前是千萬級別查詢,如果將至百萬級別,速度會更快。

SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 1000000,100) b ON a.id = b.id

你用過MySQL那些存儲引擎

他們都有什麼特點和區別?

這是高級開發者面試時經常被問的問題。實際我們在平時的開發中,經常會遇到的。Mysql的存儲引擎有這麼多種,實際我們在平時用的最多的莫過於InnoDB和MyISAM瞭。所有如果面試官問道mysql有哪些存儲引擎,你隻需要告訴這兩個常用的就行。

那他們都有什麼特點和區別呢?

MyISAM:默認表類型,它是基於傳統的ISAM類型,ISAM是Indexed Sequential Access Method (有索引的順序訪問方法) 的縮寫,它是存儲記錄和文件的標準方法。不是事務安全的,而且不支持外鍵,如果執行大量的select,insert MyISAM比較適合。

InnoDB:支持事務安全的引擎,支持外鍵、行鎖、事務是他的最大特點。如果有大量的update和insert,建議使用InnoDB,特別是針對多個並發和QPS較高的情況。註:在MySQL 5.5之前的版本中,默認的搜索引擎是MyISAM,從MySQL 5.5之後的版本中,默認的搜索引擎變更為InnoDB

MyISAM和InnoDB的區別

1.InnoDB支持事務,MyISAM不支持。對於InnoDB每一條SQL語言都默認封裝成事務,自動提交,這樣會影響速度,所以最好把多條SQL語言放在begin和commit之間,組成一個事務;

2.InnoDB支持外鍵,而MyISAM不支持。

3.InnoDB是聚集索引,使用B+Tree作為索引結構,數據文件是和(主鍵)索引綁在一起的(表數據文件本身就是按B+Tree組織的一個索引結構),必須要有主鍵,通過主鍵索引效率很高。MyISAM是非聚集索引,也是使用B+Tree作為索引結構,索引和數據文件是分離的,索引保存的是數據文件的指針。主鍵索引和輔助索引是獨立的。

4.InnoDB不保存表的具體行數,執行select count(*) from table時需要全表掃描。而MyISAM用一個變量保存瞭整個表的行數,執行上述語句時隻需要讀出該變量即可,速度很快。

5.Innodb不支持全文索引,而MyISAM支持全文索引,查詢效率上MyISAM要高;5.7以後的InnoDB支持全文索引瞭。

6.InnoDB支持表、行級鎖(默認),而MyISAM支持表級鎖。;

7.InnoDB表必須有主鍵(用戶沒有指定的話會自己找或生產一個主鍵),而Myisam可以沒有。

8.Innodb存儲文件有frm、ibd,而Myisam是frm、MYD、MYI。

9.Innodb:frm是表定義文件,ibd是數據文件。

10.Myisam:frm是表定義文件,myd是數據文件,myi是索引文件。

MySQL復雜查詢語句的優化

說到復雜SQL優化,最多的是由於多表關聯造成瞭大量的復雜的SQL語句,那我們拿到這種sql到底該怎麼優化呢,實際優化也是有套路的,隻要按照套路執行就行。復雜SQL優化方案:

1.使用EXPLAIN關鍵詞檢查SQL。EXPLAIN可以幫你分析你的查詢語句或是表結構的性能瓶頸,就得EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的,你的數據表是如何被搜索和排序的,是否有全表掃描等;

2.查詢的條件盡量使用索引字段,如某一個表有多個條件,就盡量使用復合索引查詢,復合索引使用要註意字段的先後順序。

3.多表關聯盡量用join,減少子查詢的使用。表的關聯字段如果能用主鍵就用主鍵,也就是盡可能的使用索引字段。如果關聯字段不是索引字段可以根據情況考慮添加索引。

4.盡量使用limit進行分頁批量查詢,不要一次全部獲取。

5.絕對避免select *的使用,盡量select具體需要的字段,減少不必要字段的查詢;

6.盡量將or 轉換為 union all。

7.盡量避免使用is null或is not null。

8.要註意like的使用,前模糊和全模糊不會走索引。

9.Where後的查詢字段盡量減少使用函數,因為函數會造成索引失效。

10.避免使用不等於(!=),因為它不會使用索引。

11.用exists代替in,not exists代替not in,效率會更好;

12.避免使用HAVING子句, HAVING 隻會在檢索出所有記錄之後才對結果集進行過濾,這個處理需要排序,總計等操作。如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷。

13.千萬不要 ORDER BY RAND()

以上就是詳解MySQL數據庫千萬級數據查詢和存儲的詳細內容,更多關於MySQL數據庫千萬級數據查詢和存儲的資料請關註WalkonNet其它相關文章!

推薦閱讀: