MySQL數(shù)據(jù)庫查詢優(yōu)化方案

1、 MySQL數(shù)據(jù)庫查詢優(yōu)化方案目 錄 TOC o 1-3 h z u HYPERLINK l _Toc533500288 一、索引相關 PAGEREF _Toc533500288 h 4 HYPERLINK l _Toc533500289 二、EXPLIAN中有用的信息 PAGEREF _Toc533500289 h 5 HYPERLINK l _Toc533500290 三、字段類型和編碼 PAGEREF _Toc533500290 h 9 HYPERLINK l _Toc533500291 四、SQL語句總結 PAGEREF _Toc533500291 h 12 HYPERLINK l _

2、Toc533500292 五、踩坑 PAGEREF _Toc533500292 h 14 HYPERLINK l _Toc533500293 六、千萬大表在線修改 PAGEREF _Toc533500293 h 14 HYPERLINK l _Toc533500294 七、慢查詢日志 PAGEREF _Toc533500294 h 15 HYPERLINK l _Toc533500295 八、查看sql進程和殺死進程 PAGEREF _Toc533500295 h 16 HYPERLINK l _Toc533500296 九、一些數(shù)據(jù)庫性能的思考 PAGEREF _Toc533500296 h

3、 18本文主要是總結了工作中一些常用的操作，以及不合理的操作，在對慢查詢進行優(yōu)化時收集的一些有用的資料和信息，本文適合有mysql基礎的開發(fā)人員。一、索引相關1、索引基數(shù)：基數(shù)是數(shù)據(jù)列所包含的不同值的數(shù)量。例如，某個數(shù)據(jù)列包含值1、3、7、4、7、3，那么它的基數(shù)就是4。索引的基數(shù)相對于數(shù)據(jù)表行數(shù)較高（也就是說，列中包含很多不同的值，重復的值很少）的時候，它的工作效果最好。如果某數(shù)據(jù)列含有很多不同的年齡，索引會很快地分辨數(shù)據(jù)行。如果某個數(shù)據(jù)列用于記錄性別（只有M和F兩種值），那么索引的用處就不大。如果值出現(xiàn)的幾率幾乎相等，那么無論搜索哪個值都可能得到一半的數(shù)據(jù)行。在這些情況下，最好根本不要使用

4、索引，因為查詢優(yōu)化器發(fā)現(xiàn)某個值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。慣用的百分比界線是30%。2、索引失效原因：1、對索引列運算，運算包括（+、-、*、/、！、%、like%_（%放在前面）2、類型錯誤，如字段類型為varchar，where條件用number。3、對索引應用內部函數(shù)，這種情況下應該建立基于函數(shù)的索引如select * from template t where ROUND(t.logicdb_id) = 1此時應該建ROUND(t.logicdb_id)為索引，mysql8.0開始支持函數(shù)索引，5.7可以通過虛擬列的方式來支持，之前只能新建一個

5、ROUND(t.logicdb_id)列然后去維護4、如果條件有or，即使其中有條件帶索引也不會使用（這也是為什么建議少使用or的原因），如果想使用or，又想索引有效，只能將or條件中的每個列加上索引5、如果列類型是字符串，那一定要在條件中數(shù)據(jù)使用引號，否則不使用索引；6、B-tree索引 is null不會走,is not null會走,位圖索引 is null,is not null 都會走7、組合索引遵循最左原則索引的建立1、最重要的肯定是根據(jù)業(yè)務經(jīng)常查詢的語句2、盡量選擇區(qū)分度高的列作為索引，區(qū)分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重復的

6、比率，比率越大我們掃描的記錄數(shù)就越少3、如果業(yè)務中唯一特性最好建立唯一鍵，一方面可以保證數(shù)據(jù)的正確性，另一方面索引的效率能大大提高二、EXPLIAN中有用的信息基本用法1、desc 或者 explain 加上你的sql2、extended explain加上你的sql，然后通過show warnings可以查看實際執(zhí)行的語句，這一點也是非常有用的，很多時候不同的寫法經(jīng)過sql分析之后實際執(zhí)行的代碼是一樣的提高性能的特性1、索引覆蓋(covering index)：需要查詢的數(shù)據(jù)在索引上都可以查到不需要回表 EXTRA列顯示using index2、ICP特性(Index Condition P

7、ushdown)：本來index僅僅是data access的一種訪問模式，存數(shù)引擎通過索引回表獲取的數(shù)據(jù)會傳遞到MySQL server層進行where條件過濾,5.6版本開始當ICP打開時，如果部分where條件能使用索引的字段，MySQL server會把這部分下推到引擎層，可以利用index過濾的where條件在存儲引擎層進行數(shù)據(jù)過濾。EXTRA顯示using index condition。需要了解mysql的架構圖分為server和存儲引擎層3、索引合并(index merge)：對多個索引分別進行條件掃描，然后將它們各自的結果進行合并(intersect/union)。一般用OR

8、會用到，如果是AND條件，考慮建立復合索引。EXPLAIN顯示的索引類型會顯示index_merge，EXTRA會顯示具體的合并算法和用到的索引extra字段1、using filesort：說明MySQL會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱為“文件排序” ，其實不一定是文件排序，內部使用的是快排2、using temporary：使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表。常見于排序order by和分組查詢group by3、using index：表示相應的SELECT操作中使用了覆蓋索引（Cov

9、ering Index），避免訪問了表的數(shù)據(jù)行，效率不錯。4、impossible where：WHERE子句的值總是false，不能用來獲取任何元組5、select tables optimized away：在沒有GROUP BY子句的情況下基于索引優(yōu)化MIN/MAX操作或者對于MyISAM存儲引擎優(yōu)化COUNT(*)操作， 不必等到執(zhí)行階段再進行計算，查詢執(zhí)行計劃生成的階段即完成優(yōu)化6、distinct：優(yōu)化distinct操作，在找到第一匹配的元祖后即停止找同樣值的操作using filesort,using temporary這兩項出現(xiàn)時需要注意下，這兩項是十分耗費性能的，在使用gr

10、oup by的時候，雖然沒有使用order by，如果沒有索引，是可能同時出現(xiàn)using filesort,using temporary的，因為group by就是先排序在分組，如果沒有排序的需要，可以加上一個order by NULL來避免排序，這樣using filesort就會去除，能提升一點性能。type字段system：表只有一行記錄（等于系統(tǒng)表），這是const類型的特例，平時不會出現(xiàn)const：如果通過索引依次就找到了，const用于比較主鍵索引或者unique索引。 因為只能匹配一行數(shù)據(jù)，所以很快。如果將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量eq_re

11、f：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質上也是一種索引訪問，它返回所有匹配 某個單獨值的行，然而它可能會找到多個符合條件的行，所以它應該屬于查找和掃描的混合體range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是在你的where語句中出現(xiàn)between、in等的查詢，這種范圍掃描索引比全表掃描要好，因為只需要開始于縮印的某一點，而結束于另一點，不用掃描全部索引index：Full Index Scan ，index與ALL的區(qū)別為index類型只遍歷索引樹

12、，這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小。 （也就是說雖然ALL和index都是讀全表， 但index是從索引中讀取的，而ALL是從硬盤讀取的）all：Full Table Scan，遍歷全表獲得匹配的行參考地址：/DrDanger/article/details/79092808三、字段類型和編碼1、mysql返回字符串長度：CHARACTER_LENGTH方法(CHAR_LENGTH一樣的)返回的是字符數(shù)，LENGTH函數(shù)返回的是字節(jié)數(shù)，一個漢字三個字節(jié)2、varvhar等字段建立索引長度計算語句：select count(distinct left(test,5)/count(*

13、) from table; 越趨近1越好3、mysql的utf8最大是3個字節(jié)不支持emoji表情符號，必須只用utf8mb4。需要在mysql配置文件中配置客戶端字符集為utf8mb4。jdbc的連接串不支持配置characterEncoding=utf8mb4，最好的辦法是在連接池中指定初始化sql，例如：hikari連接池，其他連接池類似spring.datasource.hikari.connection-init-sql=set names utf8mb4。否則需要每次執(zhí)行sql前都先執(zhí)行set names utf8mb4。4、msyql排序規(guī)則(一般使用_bin和_genera_c

14、i)：utf8_genera_ci不區(qū)分大小寫，ci為case insensitive的縮寫，即大小寫不敏感，utf8_general_cs區(qū)分大小寫，cs為case sensitive的縮寫，即大小寫敏感，但是目前MySQL版本中已經(jīng)不支持類似于*_genera_cs的排序規(guī)則，直接使用utf8_bin替代。utf8_bin將字符串中的每一個字符用二進制數(shù)據(jù)存儲，區(qū)分大小寫。那么，同樣是區(qū)分大小寫，utf8_general_cs和utf8_bin有什么區(qū)別？cs為case sensitive的縮寫，即大小寫敏感；bin的意思是二進制，也就是二進制編碼比較。utf8_general_cs排序規(guī)

15、則下，即便是區(qū)分了大小寫，但是某些西歐的字符和拉丁字符是不區(qū)分的，比如=a，但是有時并不需要=a，所以才有utf8_binutf8_bin的特點在于使用字符的二進制的編碼進行運算，任何不同的二進制編碼都是不同的，因此在utf8_bin排序規(guī)則下：a5、sql yog中初始連接指定編碼類型使用連接配置的初始化命令四、SQL語句總結常用的但容易忘的：1、如果有主鍵或者唯一鍵沖突則不插入：insert ignore into2、如果有主鍵或者唯一鍵沖突則更新,注意這個會影響自增的增量：INSERT INTOroom_remarks(room_id,room_remarks) VALUE(1,sdf)

16、 ON DUPLICATE KEY UPDATE room_remarks=2343、如果有就用新的替代，values如果不包含自增列，自增列的值會變化：REPLACE INTOroom_remarks(room_id,room_remarks) VALUE(1,sdf)4、備份表：CREATE TABLE user_info SELECT * FROM user_info5、復制表結構：CREATE TABLE user_v2 LIKE user6、從查詢語句中導入：INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,

17、num) SELECT id,num FROM user7、連表更新：UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id8、連表刪除：DELETE user FROM user,black WHERE user.id=black.id鎖相關(作為了解，很少用)1、共享鎖：select id from tb_test where id = 1 lockin share mode;2、排它鎖：select id from tb_test where id = 1for update優(yōu)化時用到：1、強制使用某個索引：select

18、* from table force index(idx_user) limit 2;2、禁止使用某個索引：select * from table ignore index(idx_user) limit 2;3、禁用緩存(在測試時去除緩存的影響)：select SQL_NO_CACHE from table limit 2;查看狀態(tài)1、查看字符集 SHOW VARIABLES LIKE character_set%;2、查看排序規(guī)則 SHOW VARIABLES LIKE collation%;SQL編寫注意1、where語句的解析順序是從右到左，條件盡量放where不要放having2、采

19、用延遲關聯(lián)(deferred join)技術優(yōu)化超多分頁場景，比如limit 10000,10,延遲關聯(lián)可以避免回表3、distinct語句非常損耗性能，可以通過group by來優(yōu)化4、連表盡量不要超過三個表五、踩坑1、如果有自增列，truncate語句會把自增列的基數(shù)重置為0，有些場景用自增列作為業(yè)務上的id需要十分重視2、聚合函數(shù)會自動濾空，比如a列的類型是int且全部是NULL，則SUM(a)返回的是NULL而不是03、mysql判斷null相等不能用“a=null”,這個結果永遠為UnKnown，where和having中,UnKnown永遠被視為false，check約束中，UnK

20、nown就會視為true來處理。所以要用“a is null”處理六、千萬大表在線修改mysql在表數(shù)據(jù)量很大的時候，如果修改表結構會導致鎖表，業(yè)務請求被阻塞。mysql在5.6之后引入了在線更新，但是在某些情況下還是會鎖表，所以一般都采用pt工具( Percona Toolkit)如對表添加索引：如下：pt-online-schema-change-user=root-host=localhost-ask-pass-alteraddindexidx_user_id(room_id,create_time)D=fission_show_room_v2,t=room_favorite_info-

21、execute七、慢查詢日志有時候如果線上請求超時，應該去關注下慢查詢日志，慢查詢的分析很簡單，先找到慢查詢日志文件的位置，然后利用mysqldumpslow去分析。查詢慢查詢日志信息可以直接通過執(zhí)行sql命令查看相關變量，常用的sql如下：-查看慢查詢配置-slow_query_log慢查詢日志是否開啟-slow_query_log_file的值是記錄的慢查詢日志到文件中-long_query_time指定了慢查詢的閾值-log_queries_not_using_indexes是否記錄所有沒有利用索引的查詢SHOWVARIABLESLIKE%quer%;-查看慢查詢是日志還是表的形式SHOWVARIABLESLIKElog_output-查看慢查詢的數(shù)量mysqldumpslow的工具十分簡單，我主要用到的是參數(shù)如下：-t:限制輸出的行數(shù)，我一般取前十條就夠了-s:根據(jù)什么來排序默認是平均查詢時間at，我還經(jīng)常用到c查詢次數(shù)，因為查詢次數(shù)很頻繁但是時間不高也是有必要優(yōu)化的，還有t查詢時間，查看那個語句特別卡。-v:輸出詳細信息例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500八、查看sql進程和殺死進程如果你執(zhí)行了一個sql的操作，但是遲遲沒有返回，你可以通過查詢進程列表看看他的實