歡迎關注公眾號:【愛編碼】 如果有需要後台回復2019贈送1T的學習資料哦!!
在這個快速發展的時代,時間變得 越來越重要,也流逝得非常得快,有些人長大了,有些人卻變老了。稍不留神,2019已經過完了三分之一。回首這四個月收穫什麼,懂得了什麼?歡迎留言分享給我喲。
言歸正傳: MySQL的查詢怎麼才能更快,更合理?除了加索引還有什麼可以學習的呢?
要想更好地學習某樣東西,從其原理和運作方式入手更容易掌握。道理你們都懂,我就不廢話了。
下圖是MySQL查詢執行流程圖:
?客戶端發送一條查詢給伺服器。?伺服器先檢查查詢緩存,如果命中了緩存,則立刻返回查詢在緩存中的結果。否則會進入下一個階段。?服務端進行SQL解析、預處理、再由優化器生成對應的執行計劃。?MySQL根據優化器生成的執行計劃,調用存儲引擎的API來執行查詢。?將結果返回給客戶端。
對於MySQL,最簡單的衡量查詢開銷的三個指標如下:
?響應時間?掃描的行數?返回的行數
沒有哪個指標能夠完美地衡量查詢的開銷,但它們大致反映了MySQL在內部執行查詢時需要訪問多少數據,並可以大概推算出查詢運行的時間。
查詢慢的原因基本都是:我們的不合理操作導致查詢的多餘數據太多了。 常見原因有以下:
1.查詢不需要的記錄。 2.多表關聯時返回全部列 3.總是取出全部列
最簡單且見效最快的方式就是給你的條件加索引(主鍵索引,普通索引,唯一索引等)。註:索引是要另開闢一塊空間存儲的,所以不能不要錢滴都加索引。
MySQL的子查詢實現是非常糟糕的。比如下面的
SELECT * FROM book WHERE book_id IN (SELECT book_id FROM author WHERE author_id = 1)
MySQL對IN()列表中的選項有專門的優化策略,一般會認為MySQL會先執行子查詢返回所有包含author_id 為1的book_id。
或許你想MySQL的運行時這樣子的:
SELECT GROUP_CONCAT(book_id) FROM author WHERE author_id = 1 SELECT * FROM book WHERE book_id IN (1,21,3,45,656,766,213,123)
但是,MySQL會將相關的外層表壓到子查詢中的,就是下面的樣子:
SELECT * FROM book WHERE EXISTS (SELECT * FROM author WHERE author_id = 1 AND book.book_id = author.book_id)
原因:因為子查詢需要book_id ,所以MySQL認為無法先執行這個子查詢,而是先對book 進行全表掃描,然後再根據book_id進行子查詢。具體可以EXPLAIN該SQL進行分析。
建議: 1.使用左外連接(LEFT OUTER JOIN)代替子查詢。
SELECT * from book LEFT OUTER JOIN author USING(book_id) WHERE author.author_id = 1
影響因素:還有數據表放的位置等,具體應用場景就只能你自己explain該語句對比哪種性能比較好點。
2.確保ON或者USING子句的列上有索引 在創建索引的時候就要考慮到關聯的順序。
如果希望UNION的各個子句能根據LIMIT只取部分結果集,或者希望能夠先排好序再合併結果集的話。 第一個例子:會將author 表和user 表兩個表都存放到一個臨時表中,再從臨時表中取出前20條。
(SELECT first_name FROM author ORDER BY last_name) UNION ALL (SELECT first_name FROM user ORDER BY last_name) LIMIT 20
對比上面的這樣子,就有很大的改善了。
(SELECT first_name FROM author ORDER BY last_name LIMIT 20) UNION ALL (SELECT first_name FROM user ORDER BY last_name LIMIT 20) LIMIT 20
比如: 求最小值 第一種方案:
SELECT MIN(id) FROM article WHERE author = zero
第二種方案:
SELECT id FROM article USE INDEX(PRIMARY) WHERE author = zero LIMIT 1
和第一種方案的對比,效果其實是一樣的,但是它們的性能略有不同,具體還請自己具體場景分析,擇優選擇。
比如如果想統計文章id大於25的數量,可以如下:
EXPLAIN SELECT COUNT(*) FROM article WHERE id >25
另外一種思路:可以先查詢文章總數,減去小於等於25的數量。僅僅提供思路,具體效果還是你具體情況,自己比較,擇優選擇。
EXPLAIN SELECT (SELECT COUNT(*) FROM article) - COUNT(*) FROM article WHERE id <=25
題外話: 如果需要區分不同顏色的商品數量時,可以如下做法:
seelct count(color = blue OR NULL) as blue,COUNT(color = red OR NULL) AS RED FROM items
它們的優化最有效的方法就是用索引來。 但是GROUP BY有時候用得不對,索引是會失效的。 比如:把兩個單獨的索引合併成一個組合索引,即把where條件欄位的索引和group by的分組欄位索引組合成一個。
解決方法:參考這篇函數索引[1]
下面這條查詢,非常常見。
select film_id,description from film order by title limit 50,5;
但是如果這個表很大的時候,那麼這個50變成100654這樣子的話,這裡MySQL就要掃描100654+5條數據,然後丟棄100654條,僅僅去最後5條。 一種思路:
select film_id,description from film inner join (select film_id from film order by title limit 50,5) as lim USING(film_id);
該思路是通過延遲關聯將大大提升查詢效率,它讓MySQL掃描儘可能少的頁面。獲取需要訪問的記錄後,再更加關聯列會原表查詢所需要的所有列。以上並不一定符合你,具體還需explain對比擇優使用。
小結: 總體來說都是圍繞著盡量少全表掃描,盡量使用索引進行優化。 最後往往是要自己在實際場景多用explain分析是否有更好的sql解決方案。
1.隱式轉換導致索引失效. 這一點應當引起重視.也是開發中經常會犯的錯誤. 由於表的欄位tu_mdn定義為varchar2(20),但在查詢時把該欄位作為number類型以where條件傳給Oracle,這樣會導致索引失效.
錯誤的例子:select * from test where tu_mdn=13333333333; 正確的例子:select * from test where tu_mdn=13333333333;
2. 對索引列進行運算導致索引失效 所指的對索引列進行運算*包括(+,-,,/,! 等)
錯誤的例子:select * from test where id-1=9; 正確的例子:select * from test where id=10;
3. 使用內部函數導致索引失效. 對於這樣情況應當創建基於函數的索引.
// 錯誤的例子: select * from test where round(id)=10; //說明,此時id的索引已經不起作用了 //正確的例子:首先建立函數索引 create index test_id_fbi_idx on test(round(id)); //然後 select * from test where round(id)=10;
4. 不要將空的變數值直接與比較運算符(符號)比較。 如果變數可能為空,應使用 IS NULL 或 IS NOT NULL 進行比較,或者使用 ISNULL 函數。
5. 不要在 SQL 代碼中使用雙引號。 因為字元常量使用單引號。如果沒有必要限定對象名稱,可以使用(非 ANSI SQL 標準)括弧將名稱括起來。
6. 以下使用會使索引失效,應避免使用
a. 使用 <> 、not in 、not exist、!= b. like "%_" 百分號在前(可採用在建立索引時用reverse(columnName)這種方法處理) c. 單獨引用複合索引里非第一位置的索引列.應總是使用索引的第一個列,如果索引是建立在多個列上, 只有在它的第一個列被where子句引用時,優化器才會選擇使用該索引。 d. 字元型欄位為數字時在where條件里不添加引號. e. 當變數採用的是times變數,而表的欄位採用的是date變數時.或相反情況。
a. 使用 <> 、not in 、not exist、!=
暫時統計到這麼多,如果有更多的以後再補充。
EXPLAIN是用來分析SQL執行情況分析的
EXPLAIN 命令的輸出內容大致如下:
mysql> explain select * from user_info where id = 2G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
各列的含義如下:
?id: SELECT 查詢的標識符. 每個 SELECT 都會自動分配一個唯一的標識符.?select_type: SELECT 查詢的類型.?table: 查詢的是哪個表?partitions: 匹配的分區?type: join 類型?possible_keys: 此次查詢中可能選用的索引?key: 此次查詢中確切使用到的索引.?ref: 哪個欄位或常數與 key 一起被使用?rows: 顯示此查詢一共掃描了多少行. 這個是一個估計值.?filtered: 表示此查詢條件所過濾的數據的百分比?extra: 額外的信息
更詳細的可以參考這篇【性能優化神器 Explain 使用分析[2]】或者【高性能MySQL[3]】
查詢優化目的就是為了快速得到結果,所以每當寫完SQL應該思考以下幾點:
?是否需要全表查詢以及返回的數據是否合理。?是否需要索引,索引是否合理。?是否有更好的解決辦法。
如果對 Java、大數據感興趣請長按二維碼關注一波,我會努力帶給你們價值。覺得對你哪怕有一丁點幫助的請幫忙點個贊或者轉發哦。 關注公眾號【愛編碼】,回復2019有相關資料哦。
[1] 函數索引: https://www.linuxidc.com/Linux/2016-02/128066.htm
[1]
[2]
[3]