Mysql面試題及千萬級數據查詢優化

今天在說Mysql查詢優化之前,我先說一個常見的面試題,并帶著問題深入探討研究。這樣會讓大家有更深入的理解。

一,Mysql數據庫中一個表里有一千多萬條數據,怎么快速的查出第900萬條后的100條數據?

怎么查,誰能告訴我答案?有沒有人想著,不就一條語句搞定嘛

select * from table limit 9000000,100;

那我們試試,去執行下這個SQL看看吧

 

 

看見了嗎,查了100條數據用了7.063s。這能算的上是快速查詢嗎,估計沒人能接受了這種速度吧!基于這個問題,我今天就要說說大數據時的快速查詢了。

 

 


首先,我演示下大數據分頁查詢,我的test表里有1000多萬條數據,然后使用limit進行分頁測試:select * from test limit 0,100;

耗時:0.005s

select * from test limit 1000,100;

耗時:0.006s

select * from test limit 10000,100;

耗時:0.013s

select * from test limit 100000,100;

耗時:0.104s

select * from test limit 500000,100;

耗時:0.395s

select * from test limit 1000000,100;

耗時:0.823s

select * from test limit 5000000,100;

耗時:3.909s

select * from test limit 10000000,100;

耗時:10.761s

我們發現一個現象,分頁查詢越靠后查詢越慢。這也讓我們得出一個結論:

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

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

對大數據量limit分頁性能優化

說到查詢優化,我們首先想到的肯定是使用索引。利用了索引查詢的語句中如果條件只包含了那個索引列,那在這種情況下查詢速度就很快了。因為利用索引查找有相應的優化算法,且數據就在查詢索引上面,不用再去找相關的數據地址了,這樣節省了很多時間。另外Mysql中也有相關的索引緩存,在并發高的時候利用緩存就效果更好了。

我的test表使用InnoDB作為存儲引擎,id作為自增主鍵,默認為主鍵索引。那我們現在用覆蓋索引查詢,看看效果如何:

SELECT id FROM test LIMIT 9000000,100;

總耗時4.256s,相對于7.063s少了很多。
現在優化的方案有兩種,即通過id作為查詢條件使用子查詢實現和使用join實現;
1,id>=的(子查詢)形式實現

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

耗時 4.262s;

2,使用join的形式;

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

耗時 4.251s;這兩種優化查詢使用時間比較接近,其實兩者用的都是一個原理,所以效果也差不多。但個人建議最好使用join,盡量減少子查詢的使用。注:目前是千萬級別查詢,如果將至百萬級別,速度會更快,我有親自測試一下語句,查詢時間0.410s。

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

 

二,你用過mysql那些存儲引擎,他們都有什么特點和區別?

這是高級開發者面試時經常被問的問題。實際我們在平時的開發中,經常會遇到的,在用SQLyog等工具創建表時,就有一個引擎項要你去選。如下圖:

 

 

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。

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

  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() 

接下來會繼續總結一些面試中的問題共享給大家,如果覺得內容不錯請關注此公眾號,我會不定期的推送一些干貨給大家。

 

推薦閱讀:

Api接口文檔管理工具,你知道哪些呢?

Java項目支付寶支付接入及詳解

微信支付之掃碼、APP、小程序支付接入詳解

 

掃碼關注公眾號,發送關鍵詞獲取相關資料:

  1. 發送“Springboot”領取電商項目實戰源碼;

  2. 發送“SpringCloud”領取cloud學習實戰資料;

 

 

posted @ 2019-10-26 10:26  碼農大哥  閱讀(...)  評論(... 編輯 收藏
三d开奖结果走势图