一號(hào)學(xué)院MySQL性能優(yōu)化課程-途牛網(wǎng)王強(qiáng)

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

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

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

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

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

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

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

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

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

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

11、 1 4、所以只能使用前綴索引：比如有索引包含 a、b兩個(gè)字段，那么條件可以是a = X and b = X a = x 對(duì)于單獨(dú)的 b = x ，這個(gè)索引是無(wú)效的，所以上面的2個(gè)情況，建立一個(gè)負(fù)荷索引就可以了。 Like 操作，對(duì)于 like xxx%是可以使用索引的，但是 like %xxx%, like %XXX 是不可以使用索引的。SQL調(diào)優(yōu)實(shí)戰(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í)行計(jì)劃：優(yōu)化思路：這個(gè)sql其實(shí)最后就返回了9條記錄，典型的分頁(yè)問(wèn)題。很多的分頁(yè)操作，是我們過(guò)濾了全部數(shù)據(jù)以后，只取排序以后的前N條記錄，大部分其他記錄是被扔掉了，所以優(yōu)化的思路，應(yīng)該是直接取驅(qū)動(dòng)表中的9條記錄，然后再關(guān)聯(lián)其他表，這樣的執(zhí)行效率才是最高的，下面我們就按

13、照這個(gè)思路來(lái)處理這條sql。這條sql我們看的話，應(yīng)該是以phpcms_member為驅(qū)動(dòng)表，為了避免最后的排序操作，我們需要以point為索引的第一個(gè)字段，這樣就避免的最后的排序操作，然后我們?cè)偌由蟯serid字段，這樣where條件我們就可以用這個(gè)索引來(lái)過(guò)濾了。1、給 phpcms_member 表添加索引：Create index index_point_userid on phpcms_member(point desc , userid ) ;添加了這個(gè)索引以后，我們看到執(zhí)行計(jì)劃還是沒(méi)有大的改觀改寫(xiě)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í)行計(jì)劃SQL優(yōu)化實(shí)例2Sql語(yǔ)句如下：KQ_USER_INFO符合條件的記錄大概有1.7w，KQ_ATTENDANCE_DETAIL表在指定日