首頁»數據庫»SQL優化指南

                    SQL優化指南

                    來源:不該相遇在秋天 發布時間:2018-12-01 閱讀次數:

                    慢查詢日志 開啟撒網模式

                        開啟了MySQL慢查詢日志之后,MySQL會自動將執行時間超過指定秒數的SQL統統記錄下來,這對于搜羅線上慢SQL有很大的幫助。

                    SHOW VARIABLES LIKE 'slow%'

                    以我剛安裝的mysql5.7為例 查詢結果是這樣子的:

                      slow_launch_time:表示如果建立線程花費了比這個值更長的時間,slow_launch_threads 計數器將增加
                      slow_query_log:是否開啟慢查詢日志 ON開啟,OFF關閉 默認沒有開啟
                      slow_query_log_file:日志保存路徑

                    SHOW VARIABLES LIKE 'long%'

                      long_query_time:達到多少秒的sql就記錄日志

                    客戶端可以用set設置變量的方式讓慢查詢開啟,但是個人不推薦,因為真實操作起來會有一些問題,比如說,重啟MySQL后就失效了,或者是開啟了慢查詢,我又去改變量值,它就不生效了。

                    編輯MySQL的配置文件:

                    vim /etc/my.cnf

                    加入如下三行:

                      slow_query_log=ON
                      slow_query_log_file=/var/lib/mysql/localhost-centos-slow.log
                      long_query_time=3

                    我這里設置的是3秒

                     重啟MySQL

                    systemctl restart mysqld;

                    服務器開一個監控:

                    tail -f /var/lib/mysql/localhost-centos-slow.log

                    客戶端走一條SQL:

                    SELECT SLEEP(3)

                    此時發現sql已經被記錄到日志里了。(有時候不一定,我看到很多博客講的是超過指定秒數,但我實驗得出的結果是達到指定秒數)

                    EXPLAIN 點對點分析你

                      explain是一個神奇的命令,可以查看sql的具體的執行計劃。

                    以一條聯查sql為例:

                    SELECT a.id,a.cn_name,a.role_id,r.name
                    FROM tb_usr_admins a
                    INNER JOIN tb_base_roles r ON r.id=a.role_id
                    WHERE a.cn_name="接單人員"

                    查詢結果是:

                    加上explain命令來執行:

                    EXPLAIN
                    SELECT a.id,a.cn_name,a.role_id,r.name
                    FROM tb_usr_admins a
                    INNER JOIN tb_base_roles r ON r.id=a.role_id
                    WHERE a.cn_name="接單人員"

                    查詢結果是:

                    這就是這條SQL的執行計劃,下面來說明一下這個執行計劃怎么看

                      id:代表優先級  id值越大,越先執行,id值相同,從上往下執行。(比如示例的這條sql的執行計劃,就是先執行第一行,再執行第二行)

                      select_type:表示select類型 取值如下

                        simple 簡單表 即不使用表連接或者子查詢
                        primary 包含union或者子查詢的主查詢 即外層的查詢
                        union UNION中的第二個或者后面的查詢語句
                        subquery 一般子查詢中的子查詢被標記為subquery,也就是位于select列表中的查詢
                        derived 派生表 該臨時表是從子查詢派生出來的
                        等等

                      type:表示MySQL在表中查找數據的方式,或者叫訪問類型,以下對于type取值的說明 從上往下性能由最差到最好

                        all:全表掃描,MySQL遍歷全表來找到匹配的行
                        index:索引全掃描,MySQL遍歷掙個索引來查詢匹配的行
                        range:索引范圍掃描,常見于<、<=、>、>=、between等操作符
                        ref:使用非唯一索引或唯一索引的前綴掃描,返回匹配的單行數據
                        eq_ref:類似ref,區別就在于使用的索引是唯一索引,簡單來說,就是多表連接中使用primary key或者unique index作為關聯條件。
                        const/system:單表中最多有一個匹配行,查詢起來非常迅速,常見于根據primary key或者唯一索引unique index進行的單表查詢
                        null:mysql不用訪問表或者索引,直接就能夠得到查詢的結果,例如select 1+2 as result。

                      possible_keys:表示查詢時可能使用的索引

                      key:表示實際使用的索引

                      key_len:使用到索引字段的長度

                      rows:掃描數量

                      Extra:執行情況的說明和描述,包含不適合在其他列中顯示但是對執行計劃非常重要的額外信息,常用取值如下:

                        Using index:直接訪問索引就取到了數據,高性能的表現。
                        Using where:直接在主鍵索引上過濾數據,必帶where子句,而且用不上索引
                        Using index condition:先條件過濾索引,再查數據,
                        Using filesort:使用了外部文件排序 只要見到這個 就要優化掉
                        Using temporary:創建了臨時表來處理查詢 只要見到這個 也要盡量優化掉

                    優化爭議無數的count()

                    統計列與統計行?

                      COUNT()是一個特殊的函數,有兩種不同的作用,它可以統計某個列值的數量,也可以統計行數。

                      在統計列值的時候要求列值是非空的,也就是不統計null。

                      當我們統計行的時候,常見的是COUNT(*),這種情況下,通配符*并不會像我們猜想的那樣擴展成所有的列,實際上,它會忽略所有的列而直接統計所有的行數

                    解密MyiSAM的‘快’

                      這是一個容易產生誤解的事情:MyiSAM的count()函數總是非常快。

                      不過它是有前提條件的,條件是沒有任何where條件的count(*)才非常快,因為此時無須實際的去計算表的行數,mysql可以利用存儲引擎的特性直接獲得這個值,如果mysql知道某列不可能有null值,那么mysql內部會將count(列)表達式優化為count(*)。

                      當統計帶有where條件的查詢,那么mysql的count()和其他存儲引擎就沒有什么不同了。

                    COUNT(1)、COUNT(*)、COUNT(列)

                      (先提前申明,本人是在innodb庫里做的實驗。)

                      1.count(1)和count(*)直接就是統計主鍵,他們兩個的效率是一樣的。如果刪除主鍵,他們都走全表掃描。

                      2.如果count(列)中的字段是索引的話,count(列)和count(*)一樣快,否則count(列)走全表掃描。

                    優化order by 語句

                    MySQL的排序方式

                      優化order by語句就不得不了解mysql的排序方式。

                      1.第一種通過有序索引返回數據,這種方式的extra顯示為Using Index,不需要額外的排序,操作效率較高。

                      2.第二種是對返回的數據進行排序,也就是通常看到的Using filesort,filesort是通過相應的排序算法,將數據放在sort_buffer_size系統變量設置的內存排序區中進行排序,如果內存裝載不下,它就會將磁盤上的數據進行分塊,再對各個數據塊進行排序,然后將各個塊合并成有序的結果集。

                    filesort的優化

                      了解了MySQL排序的方式,優化目標就清晰了:盡量減少額外的排序,通過索引直接返回有序數據。where條件和order by使用相同的索引。

                      1.創建合適的索引減少filesort的出現。

                      2.查詢時盡量只使用必要的字段,select 具體字段的名稱,而不是select * 選擇所有字段,這樣可以減少排序區的使用,提高SQL性能。

                    優化group by 語句

                    為什么order by后面不能跟group by ?

                      事實上,MySQL在所有的group by 后面隱式的加了order by ,也就是說group by語句的結果會默認進行排序。

                      如果你要在order by后面加group by ,那結果執行的SQL是不是這樣:select * from tb order by ... group by ... order by ... ? 這不是搞笑嗎?

                    禁止排序

                      既然知道問題了,那么就容易優化了,如果查詢包括group by但又不關心結果集的順序,而這種默認排序又導致了需要文件排序,則可以指定order by null 禁止排序。

                    例如:

                    select * from tb group by name order by null;

                    優化limit 分頁

                      一個非常常見又非常頭痛的場景:‘limit 1000,20’。

                      這時MySQL需要查詢1020條記錄然后只返回最后20條,前面的1000條都將被拋棄,這樣的代價非常高。如果所有頁面的訪問頻率都相同,那么這樣的查詢平均需要訪問半個表的數據。

                    第一種思路 在索引上分頁

                      在索引上完成分頁操作,最后根據主鍵關聯回原表查詢所需要的其他列的內容。

                    例如:

                    SELECT * FROM tb_user LIMIT 1000,10

                    可以優化成這樣:

                    SELECT * FROM tb_user u 
                    INNER JOIN (SELECT id FROM tb_user LIMIT 1000,10) AS b ON b.id=u.id

                    第二種思路 將limit轉換成位置查詢

                    這種思路需要加一個參數來輔助,標記分頁的開始位置:

                    SELECT * FROM tb_user WHERE id > 1000 LIMIT 10

                    優化子查詢

                      子查詢,也就是查詢中有查詢,常見的是where后面跟一個括號里面又是一條查詢sql

                      盡可能的使用join關聯查詢來代替子查詢。

                      當然 這不是絕對的,比如某些非常簡單的子查詢就比關聯查詢效率高,事實效果如何還要看執行計劃。

                      只能說大部分的子查詢都可以優化成Join關聯查詢。

                    改變執行計劃

                    提高索引優先級

                      use index 可以讓MySQL去參考指定的索引,但是無法強制MySQL去使用這個索引,當MySQL覺得這個索引效率太差,它寧愿去走全表掃描。。。

                    SELECT * FROM tb_user USE INDEX (user_name)

                      注意:必須是索引,不能是普通字段,(親測主鍵也不行)。

                    忽略索引

                      ignore index 可以讓MySQL忽略一個索引

                    SELECT * FROM tb_user IGNORE INDEX (user_name) WHERE user_name="張學友" 

                    強制使用索引

                      force index 使用了force index 之后 盡管效率非常低,MySQL也會照你的話去執行

                    SELECT * FROM tb_user FORCE INDEX (user_name) WHERE user_name="張學友" 

                    個人分享

                    查看執行計劃時建議依次觀察以下幾個要點:

                      1.SQL內部的執行順序。
                      2.查看select的查詢類型。
                      3.實際有沒有使用索引。
                      4.Extra描述信息

                    PS:一定要養成查看執行計劃的習慣,這個習慣非常重要。

                    QQ群:WEB開發者官方群(515171538),驗證消息:10000
                    微信群:加小編微信 849023636 邀請您加入,驗證消息:10000
                    提示:更多精彩內容關注微信公眾號:全棧開發者中心(fsder-com)
                    SQL
                    網友評論(共0條評論) 正在載入評論......
                    理智評論文明上網,拒絕惡意謾罵 發表評論 / 共0條評論
                    登錄會員中心
                    福彩试机号今天