MySQL優(yōu)化概述

1、筆記2018/11/16設(shè)計:功能:架構(gòu):MySQL優(yōu)化概述存儲引擎，字段類型，范式 索引，緩存，分區(qū)。主從復(fù)制，讀寫分離，負(fù)載均衡。1 / 18d合理SQL測試，經(jīng)驗。存儲弓Create table tableName () engin e=myisam|i nnodb;依賴于存儲引一種用來存儲 MySQL中對象（記錄和索引）的一種特定的結(jié)構(gòu)（文件結(jié)構(gòu)） 存儲引擎，處于 MySQL服務(wù)器的最底層，直接存儲數(shù)據(jù)。導(dǎo)致上層的操作, 擎的選擇。Client MySQLTip：存儲引擎就是特定的數(shù)據(jù)存儲格式（方案）筆記2018/11/167 / 18Show engines查看當(dāng)前MySQL支持的存

2、儲引擎列表Innodb=5.5默認(rèn)的存儲引擎, 提供事務(wù)，行級鎖定， 事務(wù)安全型存儲引擎。存儲格式數(shù)據(jù)，索引集中存儲, 數(shù)據(jù) 索引創(chuàng)建存儲于同一個表空間文件中.frm（記錄行）（一種檢索機(jī)制，innodb表后，存在文件如下: 表結(jié)構(gòu)文件。也需要一定的空間）,inrtcdh_lfrm2O15M/12FRM 文件QKBMySQL推薦使用的存儲引擎。 外鍵約束的存儲引擎。更加注重數(shù)據(jù)的完整性和安全性。Innodb表空間文件：innodb的數(shù)據(jù)和索引。*一廠幵0錄耐丘耳夾:巳 F (3 e,降蠱日禺丈小*用其Kii-PC.pW2：5/-3a； &43pro1 E1卓舌K.l-Pftrr一：七4”】”從

3、41.1(w此 f=./ iHibdjlal1 111 Ju-a-ilK 112015/4/1； & 561 J . 11| 1交伴L L J 1ifiiiaj ：,11 Hd爐b該位置，可以被配置的。默認(rèn)，所有的innodb表的表空間文件，都在同一個空間中。通過配置，達(dá)到每張innodb表，一個表空間文件的目的:inifs(ll show uai*ialile& like inno(ib_f ile_pei_table;4一 一一一一一一一一 4一一一一一一一 +! UAi*iAbl&_nane! Ualue !A! irino(lb_file_pei_talle ! OFF1 rww in

4、 開啟該配置:set global innodb_f ile_pep_table - 1;(iiiery OK, 0 ruus difftuLtiil mysqlshou uariables like * innodfc_f ile_iei*_table ;I Uai*iable_naine Ualue ! iranodh ile jper_table ! OHf Ii raw In sec irnodb_2.FTrn ir nodbNibd2：!lS/4/122 15/41? IChCiFRM文悍文斗數(shù)據(jù)按照王鍵順序存儲lb1920212324insert insert insert ins

5、ert insertinto into iivO intcintiodb innodb innodb innodb innodb22222I細(xì)1E Z1 E I 4b J4Z tstlfc 1 niPf UHiBUuJ；插入時做排序工作,效率低。特定功能事務(wù)：外鍵約束：并發(fā)性處理：擅長處理并發(fā)的。行級鎖定：維護(hù)數(shù)據(jù)完整性。升并發(fā)性。也支持表級鎖定,row-level locking，實現(xiàn)了行級鎖定，在一定情況下，可以選擇行級鎖來提 innodb根據(jù)操作選擇。多版本并發(fā)控制，MVCC,效果達(dá)到無阻塞讀操作。MylSAM=5.6 innodb MYD文件的壓縮存儲。壓縮前：into myisain

6、_2 selectnull.titlefrom myisam-2;I myi sa m_2 .W/DLJ nq曬宙2915；4；12 11:062?J5/4/13 IIXTMlVD丈件MlVl文哼25,500 KBBj?7Ka壓縮：工具myisamPack完成 壓縮功能:I czrpaniai 1 myiwm og.fxr 1 FPxiMma 口“上:右辭c期2r.ii4/；n ：5：丸一hf43 KB趣血3S/15 ；5:Z3-耐？口進(jìn)入到 需要壓縮表的數(shù)據(jù)目錄： 執(zhí)行壓縮指令 myisampack表名C:MJsere Kang :E ： cd anpsqldata pbp34F: Xamp

7、Fywq 17駁tAphp3 4nyis2PtpAC k ptyiart_2 Coniipes&insf m_y i合mi_2 .HYD:- Calculating statistics-Ccinpre：5：5in fileSB.23kHencnber tu run nisAPtcbh-r1rmyiam_2.frm7；015/4；117 n 刊FRM之碎f)罰.myitarn 2.MTO3O1S/4/13 UjOSMYDfitt12 74； l；myiMn2Vl201S/4/12 11:11M町文胖:ii is amc Jik -rq n y is a.iii_2- checlc record

8、 delete-chain-recoverinsrsovtBata records: 1316720 F ix ing index 1HylSAH-table Fyi*己fi_2E: Sartp nj；5qldata plip3 4mvisam 2.friin；O15/4/12 10:59FRJM立畔9 ICBmyisa nri_2.MVD2015/4/12 11:06MVD交件12741 1 insert into iiirisani_2 values (NULL,鳳彳青揚； ERROR 103t 如果需要更新，則需要解壓后更新，再壓縮（重新索引）禾U用工具：myisamchk -unpac

9、k表名J h 計S(GlEj k OFp I m蘆ql 卜 bin51SSF座疋罕王筆記2018/11/16Ez SmpMnsQlMla taphp34nisdirichk unp ack my TEaii_2L recoverinsi uith sort? MylSrtn-table niyiweini_2 Data records: 1316720一 Fixing index 1結(jié)果JB m/iiaF-ZfrmI iryi 站 ti 2 MVU，典 ha 出 a 厶 delel&d2015/4/1? 10592015/1/12 11-21201/4/1 ILU6fRvl左件 仲C文忖 UH

10、UtLJ交弁301574/12 1131 加立f牟9 Ke25,600 KB1Z./41KH Il=g kbFlush table myisam_2iifcysql f Lush tabLe rayisLra_2；Qiiepv OK, 0 pows affected 并發(fā)性:僅僅支持表級鎖定。支持 并發(fā)插入。寫操作中的插入操作，不會阻塞讀操作（其他操作）Innodb PK myisam :數(shù)據(jù)完整性，并發(fā)性處理，擅長更新，刪除。 高速查詢及插入。擅長插入，查詢。Archive存檔型僅提供插入和查詢操作。非常高效無阻塞的插入和查詢。Memory內(nèi)存型數(shù)據(jù)存儲于內(nèi)存中，存儲引擎。緩存型存儲引擎。插

11、件式存儲引擎當(dāng)客戶端操作 表（記錄）時，為了保證操作的隔離性 （多個客戶端操作不能互相影響） 通過加鎖來處理。操作方面：讀鎖：讀操作時增加的鎖，也叫共享鎖，S-lock。特征是 阻塞其他客戶端的寫操作, 不阻塞讀操作。X-lock。特征，阻塞其他客戶端寫鎖：寫操作時增加的鎖，也叫獨占鎖或排他鎖，的讀，寫操作。鎖定粒度（范圍）：行級：提升并發(fā)性，鎖本身開銷大 表級：不利于并發(fā)性，鎖本身開銷小。類型選擇滿足需求。原則：盡可能小（占用存儲空間少）Tinyint, smalli nt, mediu min t,i nt, bigi ntVarchar(N) varchar(M)Datetime, ti

12、mesta mp盡可能定長（占用存儲空間固定）Char,varcharDecimal (變長)，double(float)(定長)盡可能使用整數(shù)IP V4, intun sig ned， varchar(15)En umSet多用位運算。范式，逆范式GoodsGoods_id, goods_ name, cat_idCategory Cat_id, cat_ name.分類列表查詢:分類ID分類名稱計算機(jī)商品數(shù)量56710 / 18dcount(g.goods_id) as goods_count from category as c left join goods as gSelect c.

13、*,c.cat_id=g.cat_id group by c.cat_id;此時商品數(shù)量較大。重新設(shè)計category表：增加存當(dāng)前分類下商品數(shù)量的字段。CategoryCat_id, cat_ name, goods_co unt每當(dāng)商品改動時，修改對應(yīng)分類的數(shù)量信息。 再查詢分類列表時：Select * from category;此時額外的消耗，出現(xiàn)在維護(hù)該字段的正確性上，保證商品的任何更新都正確的處理該數(shù)量才可以。索引的使用，建立與記利用關(guān)鍵字，就是記錄的部分?jǐn)?shù)據(jù)（某個字段，某些字段，某個字段的一部分） 錄位置的對應(yīng)關(guān)系，就是索引。索引的關(guān)鍵字一定是排序的。筆記2018/11/161索

14、引，按照天鍵字姓建立索引（天鍵字”記錄位置j1HanH0x12345678 , 0x99999999|i0xabcdef01Yangn0xaaaaaaaa；Zhaor0x87654321|Zhoui0x66666666測試查詢，添加索引前后比對執(zhí)行時間:nysql Select * fron enp where eiiipnD=12345t7jI 二4-Rigr I hire date enpnosalConn i deptnoi 1234SE7 ! UtiJX i iiniESMAN ! L ： 2015-(34-1；： i：enane job11 / 18piyuql扌 altei t a

15、ble enp add In矗 x Capipno、； Quepy OK, 1S Qflnnn i*ous affected O _ 37 sec、 Eecoids： 180B0B9 Duplicates： & Uarninqs: 0jnysql select * fron enp where eiiipfio=12345t71 enpnoenane jobmgr hiredateI sal-+CDmiTi i deptno f+i :了 i KUtiJK i SRLhSnnNL !iS11111 set B. OS Hcc索引的類型4種類型:主索引，唯一索引，普通索引，全文索引。無論任何類型

16、，都是通過建立關(guān)鍵字與位置的對應(yīng)關(guān)系來實現(xiàn)的。以上類型的差異：對索引關(guān)鍵字的要求不同。關(guān)鍵字：記錄的部分?jǐn)?shù)據(jù)（某個字段，某些字段，某個字段的一部分）普通索引,in dex:對關(guān)鍵字沒有要求。唯一索引unique index :要求關(guān)鍵字不能重復(fù)。同時增加唯一約束。主鍵索引,primary key :要求關(guān)鍵字不能重復(fù)，也不能為NULL。同時增加主鍵約束。全文索引fulltext key :關(guān)鍵字的來源不是所有字段的數(shù)據(jù),而是從字段中提取的特別關(guān)鍵詞。關(guān)鍵字的來源：可以是某個字段，也可以是某些字段。如果一個索引通過在多個字段上提取 的關(guān)鍵字，稱之為復(fù)合索引。筆記2018/11/16alter t

17、able emp add in dex (field1, field2);管理索引的語法創(chuàng)建59 T6061626364656667L L69建表時create table student (stu_id int unsigned not null auto_increment Xing varchanot null default ming varchar(32) not null default stu_sn chair(10) not null default 八 tu_desc text, primary key 3tu_id unique index ui ( stu_sn)- in

18、dex xingT.ng x.ing, fulltext index desc (stu_desc)enginezmyisam charsetutfS;TiP;索引可以起名字，但是主索引不能起名字，因為一個表僅僅可以有一個主索引，其他 索引可以出現(xiàn)多個。名字可以省略，mysql會默認(rèn)生成，通常使用字段名來充當(dāng)。更新表結(jié)構(gòu)alter add add add addtable student_2primary key ( stu_id ), 云蘆弓 unique index ui ( stu_rf ), 匚 index xingning (xing ming - fulltext index de

19、sc studesc );-Tip：1，如果表中存在數(shù)據(jù)，數(shù)據(jù)符合唯一或主鍵的約束才可能創(chuàng)建成功。2， Auto_increment 屬性，依賴于一個 KEY刪除919293alter table stud皂nt_2 drop drop drop dropprimary key index inde* xingming, index desc;Tip：別忘了auto in creme nt 依賴于 KEYExpl ain執(zhí)行計劃可以通過在select語句前使用 explain,來獲取該查詢語句的執(zhí)行計劃，而不是真正執(zhí)行該 語句。exp la in select * f von entp uhe

20、pe empno-1234567G 町JUXH 拭JCMKHWNHMWXMW 爼耳且1 ,賣芋苗 K 苗器:It1 SIMPLEid = select_tsFpe : tablje : type = possihle_keys = key： kei_len : pftf ： rows :Extra：1 r-tjw in set 可能使用到的查詞索引enp ref enpno enpno4 const1譌k存儲引擎漲取畤記錄，?，F(xiàn)該直詞刪除索引時，再看執(zhí)行計劃:nysql explain select * fronid： 1SlflFlEennMLhULLMULLHULLHULL1800000U

21、sliitj ulier-eemp iiheie efipno1 345t7(3I 1, POU WMMKMWMWMMWWWBWMWMWselect_type: table:type： nsBihle_KeV5 : key： key_leii ； ref :rutJii = Extra=1 row in scL explain select * from enp order by enane limit; 100G HKXKmMKKKWKXKMXKWKM 1 . r O U MMKMmmKmXid select_type table type po ss ifclejeys ke key_le

22、n refrows Extra1 SiriFLEenpALLNULLNULLNULLNULL 1800006Using filesort11 rou in set CO.00 sec此時，沒有任何索引。 在ename字段上建立索引:100 。俯 in set SJnPLl enp Index HULLnys(3(l exp lain select * fpom enp order by en衣mt limit 103G 14： 1.elect_tjfpe :table:type :ciimic GSHULLpusK丄hie Jtys :kc y -lie*_Ic n 1Z f 1vovrn :

23、 103 Extia?n set n _ no sec)Tip：對比以上兩個執(zhí)行計劃: extra位置:14 /佃園筆記2018/11/161J. OtJLJOtJtJExtra： Using filesort1 row in set rows irSTEIExtra:1 row in set 其中：extra額外信息。Using filesort，表示使用文件排序（外部排序，內(nèi)存外部）索引覆蓋索引擁有的關(guān)鍵字內(nèi)容，覆蓋了查詢所需要的全部數(shù)據(jù)， 此時，就不需要在數(shù)據(jù)區(qū)獲取數(shù)據(jù), 僅僅在索引區(qū)即可。例如，利用名字檢索:emp where enarne like ab匚 ;Lie select e

24、mpnOj enamej job from可以在ename字段建立索引:112 alter table emp addindex (ename);21 /佃分析執(zhí)行:pysql explain select enpno. nane jobfpon Rp ubere enaii likerouid； 1SIMPLE enp range enane enane 62NULLS3elect_type: table: type - pDss ible_keys: key : key_len: ref；i!*ows :Extra: Using where fl row in set 再增加一個索引:完成

25、相同的查詢：iiys(il CKplain select emtno enane, job from enp uhere enane like abcKMl NMWMWMWKMMMmMWIOIMMWMKMK 1 _ r O H W M K M K H K * K H KM M H H W KM M Mid: selecttyre: table: tvse : liosslble_kei)s : kep： keLi_len : ref :1SIMPLEeimpaws :Extra:u in set 繩.Rran yeenarne -rwl it一indev nxilit 一 i ndex62HU

26、LL57Using wJiere: Using index sec再例如:說明，不是非要查詢用到，才可以索引覆蓋，只要滿足要求都可以覆蓋!inisql explain select enane from erip G MXHXKmfKKXMmWMMXMXK .M M K K K W MH H 譴 S W W M昶 M K M H TC M W M M If Kselect.tpe: table； tyjpe: pDDo ibloJiDo : hy : liey_lii ； ref；id： 1SIMPLEindex HULLenane62HULLrous： mUUUUU Extra: Using

27、 Index 1 row in set nysql exp lain select enane, job f rori enp G XMMKMKKXMMMMNMMKMKMKMXMMMMK J_ . FDlf MM JK MMMMid： select_type: table: type: poss iblejte ys : key： key_len: ref :1SIMPLEepip indexNULLEu L it _in de X 95NULL1890000Usingr indexVO us :Ext ra:1 row in set 直到索引使用場景時:建立索引索引時，不要僅僅考慮（在所有的

28、where字段上增加索引,where檢索了吧，同時考慮其他的使用場景。 就是不合理的）使用原則索引存在，沒有滿足使用原則，導(dǎo)致索引無效:列獨立如果需要某個字段上使用索引，則需要在字段參與的表達(dá)中，保證字段獨立在一側(cè)。121122123124125126127select * from emp where empno=1234567j explain select 乂 from emp where enipno=12345&7G select * from emp where empno=123457-l;explain 2el&ct * fnom emp where empno=i2345S7

29、-lG select * from emp where empno+1=12456/;explain select 乂 from emp where enipno-l=123-i557G第三個語句empn o-1就不是列獨立：就不能用索引。類似函數(shù)內(nèi)等。nya ql Dxpl口inQDloot * f i*i*o ompne *1 -234567id： 1 select_type: SIUPLE table: enp type； ALL PQSsible_l(e?: HULL Kev： NULL kcy_leii: HULL ref： HULL vauB: 1S0BO90Fv上PA： Hsin

30、g uhKHft 1 POU in set 6.00 sec)unix_timestamp()-$gc_maxlifetime )其他兩個列獨立可以使用:(write_time exp lain select * f ran enp wlie re enpn d =12345 67-1 G1 SinFLE enp pef eripnD empn a4 const1 Using 11 row in ?et id： select_t_ye； table: tiipe： pos ible _keiis: Iteu； key_len * ref： PDUS - Extra：where左原則Like：匹

31、配模式必須要左邊確定不能以通配符開頭。12913013113213515413H13613713S1314?“142143144select * from emp where enameatec囂；explain select * 卡0皿 emp wherelikeselect from emp where ename like ?b%c; explain select * frorr emp wher ename like - =. 亠打； 亠 二二亠=、 i select * from emp where ename like Xabc; exp lain select * from em

32、p wfiere ename like - . - - = =、 工當(dāng)=select * froffi emp where ename like _abc; explain select * from emp 神ere enam like- 亠丄 .3 .T 7-. = 左;上 戸 ,；：select * from emp where ename like _sbc; explain select* from emp b#iere erame likeab%e G亠二壬 匸：.ahcG二_abcC嚴(yán)cm1-L.業(yè)務(wù)邏輯上出現(xiàn)：field like%keyw0類似查詢，需要使用全文索引。復(fù)合索引

33、：一個索引關(guān)聯(lián)多個字段。 僅僅針對左邊字段有效果。丄f-r1=5146147alter table empadd index(enamej empno);丄Hi152153154155156select * froTi結(jié)果:emp vifhere ename like ahc%;explain select * from emp 艸hereselect 甲 from emp where empno = exp lain select * from emp ThereEname的查詢，使用了索弓I:nys(ll explain select * fFon enp whepe enane WMMX

34、MWXWMMXMXXWMHXMWXHXMXMX 丄-TOW MKMHKHMMXid： select_type: table:type : posG ib1pf Icfius： hey： ley_len: ref :rous :Extra:1SIMPLEemp ran 呂 e mill t-i inHfix iiul_index6NULL91Using uhepe1 rou in set Empno的查詢沒有使用索引:ename like abc%G1234567 Jempno = 1234567Q筆記2018/11/16仃/佃inysiil exrlain se lect * f ran erw wLere enpnn = 1234567*41POU M MMKMKKKKMKMWM MKKKKKM1.id- select_type： tabic: typepuss 111 kev