一號學院MySQL性能優(yōu)化課程-途牛網(wǎng)王強

1、 SQL優(yōu)化SQL優(yōu)化相關：1、CBO2、索引3、執(zhí)行計劃4、日常注意事項5、調優(yōu)實戰(zhàn)CBO語句的代價(Cost),這里的代價主要指Cpu和內存。CPU Costing的計算方式現(xiàn)在默認為CPU+I/O兩者之和。優(yōu)化器在判斷是否用這種方式時,主要參照的是表及索引的統(tǒng)計信息。統(tǒng)計信息給出表的大小、有少行、每行的長度等信息。這些統(tǒng)計信息起初在庫內是沒有的，是做analyze后才出現(xiàn)的，很多的時侯過期統(tǒng)計信息會令優(yōu)化器做出一個錯誤的執(zhí)行計劃,因些應及時更新這些信息。CBOMysql如何查看統(tǒng)計信息： show table status like tablename;CBORows:表中記錄的數(shù)量Av

2、g_row_length:每條記錄的長度Index_length：索引占用磁盤的空間大小 。data_length：表占用磁盤的空間大小 。如何收集統(tǒng)計信息Analyze table收集表和索引統(tǒng)計信息，適用于MyISAM和InnoDB；對于innodb表，還可以使用以下選項來收集1、表第一次打開的時候2、表修改的行超過1/16或者20億條3、執(zhí)行show index/table或者查詢information_schema.tables/statistics表時統(tǒng)計信息相關參數(shù)innodb_stats_on_metadata參數(shù)用來控制此行為，設置為false時不自動更新統(tǒng)計信息。Innodb

3、_stats_sample_pages每次收集統(tǒng)計信息時采樣的頁數(shù)，默認為8，建議適當加大這個值，使統(tǒng)計信息更準確。索引索引是sql優(yōu)化中一個很重要的手段，很多性能問題，都是沒有合適的索引造成的。假設我們有個表中數(shù)據(jù)量是100w，我們要查找某個記錄，如果沒有索引，那么我們就只能遍歷整個表，如果記錄是表中最后一條記錄，那么我們就是訪問了100w條記錄，才找到需要的，而如果有索引，我們一般經過3-4次IO，就可以找到需要的記錄，效果對比起來十分明顯。索引的作用索引主要的作用：1、快速定位記錄2、避免排序和使用臨時表3、可以將隨機IO變?yōu)轫樞騃O索引的字段索引可以包含一個或者多個字段，如果包含多個字

4、段，那么列的順序也很重要，因為mysql只能高效的使用索引的最左前綴列，建議是將選擇性高的放在最前面。選擇性：是指列的num_distinct/rowcount，這個比值越高，說明字段的選擇性越好，查詢的效率就越高。舉個例子，比如員工表包含了身份證和性別等字段，下面2個查詢條件：select * from table where 身份證號碼=320112XXXXXXXXXXXXX select * from table where 性別=320112XXXXXXXXXXXXX字段選擇性在執(zhí)行第一條sql的時候，就返回一條記錄，效率最高，而第二條記錄則返回了全部的某個性別的記錄，數(shù)據(jù)量很大，自然

5、效果就不好，甚至都不會使用索引了。MYSQL索引種類B-TREE索引：mysql的表就是基于索引的表哈希索引：基于哈希表實現(xiàn)，只能精確匹配，不能用于排序空間索引：myisam支持的索引全文檢索：myisam支持MYSQL執(zhí)行計劃要優(yōu)化sql，我們首先要理解sql是如何運作的，只有這樣，我們才能去做優(yōu)化。優(yōu)化的最終目標，就是通過最少的IO，獲取我們需要的數(shù)據(jù)，在Mysql里，我們可以通過explain查看執(zhí)行計劃，通過執(zhí)行計劃可以基本看清楚一個sql執(zhí)行起來是如何獲取數(shù)據(jù)的。EXPLAINEXPLAIN各個字段的含義ID：序號，表示了執(zhí)行順序，執(zhí)行的時候，是從大到小的順序執(zhí)行，如果ID相同，則從

6、上到下依次執(zhí)行。EXPLAIN各個字段的含義SELECT TYPE：表示查詢中每個select子句的類型（簡單 OR復雜），有以下一些：SIMPLE ：查詢中不包含子查詢或者UNIONRIMARY：查詢中若包含任何復雜的子部分，最外層查詢則被標記為primarySUBQUERY：在SELECT或WHERE列表中包含了子查詢。EXPLAIN各個字段的含義DERIVED：在FROM列表中包含的子查詢被標記為：（衍生）若第二個SELECT出現(xiàn)在UNION之后，則被標記為UNION；若UNION包含在 FROM子句的子查詢中，外層SELECT將被標記為：DERIVEDUNION RESULT：從UNI

7、ON表獲取結果的SELECT被標記為這個TYPE字段Type：表示MySQL在表中找到所需行的方式，又稱“訪問類型”，常見類型如下，由左至右，由最差到最好：ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行index，F(xiàn)ull Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹range：索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于between、等的查詢RANGE訪問類型的不同形式性能差異ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進行的查找,比如 field1 = abc

8、 eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描，比如 field1 = abc const、system：當MySQL對查詢某部分進行優(yōu)化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量system是const類型的特例，當查詢的表只有一行的情況下， 使用systemNULL：MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引possible_keys：指出MySQL能使用哪個索引在表中找到行，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用Key：顯示MyS

9、QL在查詢中實際使用的索引，若沒有使用索引，顯示為NULLTIPS：查詢中若使用了覆蓋索引，則該索引僅出現(xiàn)在key列表中key_len：表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度Ref:表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值Rows: 表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)Extra: 包含不適合在其他列中顯示但十分重要的額外信息，有以下一些常見信息：1、Using where： 表示MySQL服務器在存儲引擎受到記錄后進行“后過濾”（Post-filter）, 如果查詢未能使用索引，Using where的作

10、用只是提醒我們MySQL將用where子句來過濾結果集2、using temporary： 表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢3、Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序”日常的一些注意事項1、索引建議用選擇性好的字段，這樣效率高一些2、不要過度索引，過多索引會影響表的插入、更新效率3、不要對索引上的字段使用表達式，這樣索引不會被使用： where to_char(tm,yyyy-mm-dd) = 2015-03-01應該改為where tm = 2015-03-01and tm 200 - where valu 200 -

11、 1 4、所以只能使用前綴索引：比如有索引包含 a、b兩個字段，那么條件可以是a = X and b = X a = x 對于單獨的 b = x ，這個索引是無效的，所以上面的2個情況，建立一個負荷索引就可以了。 Like 操作，對于 like xxx%是可以使用索引的，但是 like %xxx%, like %XXX 是不可以使用索引的。SQL調優(yōu)實戰(zhàn)SELECT m.userid ,m.point ,panyname_s,c.logo ,c.great ,i.answercount,m.areaid AS company_areaidFROM phpcms_member mLEFT JOI

12、N phpcms_member_company c ON c.userid = m.useridLEFT JOIN phpcms_member_info i ON i.userid = m.useridWHERE m.userid 1 AND c.logo != ORDER BY m.point DESC LIMIT 0 ,9;執(zhí)行計劃：優(yōu)化思路：這個sql其實最后就返回了9條記錄，典型的分頁問題。很多的分頁操作，是我們過濾了全部數(shù)據(jù)以后，只取排序以后的前N條記錄，大部分其他記錄是被扔掉了，所以優(yōu)化的思路，應該是直接取驅動表中的9條記錄，然后再關聯(lián)其他表，這樣的執(zhí)行效率才是最高的，下面我們就按

13、照這個思路來處理這條sql。這條sql我們看的話，應該是以phpcms_member為驅動表，為了避免最后的排序操作，我們需要以point為索引的第一個字段，這樣就避免的最后的排序操作，然后我們再加上userid字段，這樣where條件我們就可以用這個索引來過濾了。1、給 phpcms_member 表添加索引：Create index index_point_userid on phpcms_member(point desc , userid ) ;添加了這個索引以后，我們看到執(zhí)行計劃還是沒有大的改觀改寫SQLSELECT m.userid ,m.point ,panyname_s ,c.

14、logo , c.great ,i.answercount ,m.areaid AS company_areaidFROM ( SELECT userid FROM phpcms_member a WHERE userid 1 AND EXISTS ( SELECT *FROM phpcms_member_companyWHERE userid = a.userid AND c.logo != ）ORDER BY a.point DESC LIMIT 0 , 9 ) mLEFT JOIN phpcms_member_company c ON c.userid = m.useridLEFT JOIN phpcms_member_info i ON i.userid = m.useridORDER BY m.point DESC LIMIT 0 , 9 ;優(yōu)化后的執(zhí)行計劃SQL優(yōu)化實例2Sql語句如下：KQ_USER_INFO符合條件的記錄大概有1.7w，KQ_ATTENDANCE_DETAIL表在指定日