MySQL优化概述

1、笔记2018/11/16设计:功能:架构:MySQL优化概述存储引擎，字段类型，范式 索引，缓存，分区。主从复制，读写分离，负载均衡。1 / 18d合理SQL测试，经验。存储弓Create table tableName () engin e=myisam|i nnodb;依赖于存储引一种用来存储 MySQL中对象（记录和索引）的一种特定的结构（文件结构） 存储引擎，处于 MySQL服务器的最底层，直接存储数据。导致上层的操作, 擎的选择。Client MySQLTip：存储引擎就是特定的数据存储格式（方案）笔记2018/11/167 / 18Show engines查看当前MySQL支持的存

2、储引擎列表Innodb=5.5默认的存储引擎, 提供事务，行级锁定， 事务安全型存储引擎。存储格式数据，索引集中存储, 数据 索引创建存储于同一个表空间文件中.frm（记录行）（一种检索机制，innodb表后，存在文件如下: 表结构文件。也需要一定的空间）,inrtcdh_lfrm2O15M/12FRM 文件QKBMySQL推荐使用的存储引擎。 外键约束的存储引擎。更加注重数据的完整性和安全性。Innodb表空间文件：innodb的数据和索引。*一厂幵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该位置，可以被配置的。默认，所有的innodb表的表空间文件，都在同一个空间中。通过配置，达到每张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文悍文斗数据按照王键顺序存储lb1920212324insert insert insert ins

5、ert insertinto into iivO intcintiodb innodb innodb innodb innodb22222I细1E Z1 E I 4b J4Z tstlfc 1 niPf UHiBUuJ；插入时做排序工作,效率低。特定功能事务：外键约束：并发性处理：擅长处理并发的。行级锁定：维护数据完整性。升并发性。也支持表级锁定,row-level locking，实现了行级锁定，在一定情况下，可以选择行级锁来提 innodb根据操作选择。多版本并发控制，MVCC,效果达到无阻塞读操作。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-耐？口进入到 需要压缩表的数据目录： 执行压缩指令 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结果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 并发性:仅仅支持表级锁定。支持 并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）Innodb PK myisam :数据完整性，并发性处理，擅长更新，删除。 高速查询及插入。擅长插入，查询。Archive存档型仅提供插入和查询操作。非常高效无阻塞的插入和查询。Memory内存型数据存储于内存中，存储引擎。缓存型存储引擎。插

11、件式存储引擎当客户端操作 表（记录）时，为了保证操作的隔离性 （多个客户端操作不能互相影响） 通过加锁来处理。操作方面：读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是 阻塞其他客户端的写操作, 不阻塞读操作。X-lock。特征，阻塞其他客户端写锁：写操作时增加的锁，也叫独占锁或排他锁，的读，写操作。锁定粒度（范围）：行级：提升并发性，锁本身开销大 表级：不利于并发性，锁本身开销小。类型选择满足需求。原则：尽可能小（占用存储空间少）Tinyint, smalli nt, mediu min t,i nt, bigi ntVarchar(N) varchar(M)Datetime, ti

12、mesta mp尽可能定长（占用存储空间固定）Char,varcharDecimal (变长)，double(float)(定长)尽可能使用整数IP V4, intun sig ned， varchar(15)En umSet多用位运算。范式，逆范式GoodsGoods_id, goods_ name, cat_idCategory Cat_id, cat_ name.分类列表查询:分类ID分类名称计算机商品数量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;此时商品数量较大。重新设计category表：增加存当前分类下商品数量的字段。CategoryCat_id, cat_ name, goods_co unt每当商品改动时，修改对应分类的数量信息。 再查询分类列表时：Select * from category;此时额外的消耗，出现在维护该字段的正确性上，保证商品的任何更新都正确的处理该数量才可以。索引的使用，建立与记利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分） 录位置的对应关系，就是索引。索引的关键字一定是排序的。笔记2018/11/161索

14、引，按照天键字姓建立索引（天键字”记录位置j1HanH0x12345678 , 0x99999999|i0xabcdef01Yangn0xaaaaaaaa；Zhaor0x87654321|Zhoui0x66666666测试查询，添加索引前后比对执行时间: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、，都是通过建立关键字与位置的对应关系来实现的。以上类型的差异：对索引关键字的要求不同。关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）普通索引,in dex:对关键字没有要求。唯一索引unique index :要求关键字不能重复。同时增加唯一约束。主键索引,primary key :要求关键字不能重复，也不能为NULL。同时增加主键约束。全文索引fulltext key :关键字的来源不是所有字段的数据,而是从字段中提取的特别关键词。关键字的来源：可以是某个字段，也可以是某些字段。如果一个索引通过在多个字段上提取 的关键字，称之为复合索引。笔记2018/11/16alter t

17、able emp add in dex (field1, field2);管理索引的语法创建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;索引可以起名字，但是主索引不能起名字，因为一个表仅仅可以有一个主索引，其他 索引可以出现多个。名字可以省略，mysql会默认生成，通常使用字段名来充当。更新表结构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，如果表中存在数据，数据符合唯一或主键的约束才可能创建成功。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执行计划可以通过在select语句前使用 explain,来获取该查询语句的执行计划，而不是真正执行该 语句。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存储引擎漲取畤记录，專现该直词删除索引时，再看执行计划: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：对比以上两个执行计划: extra位置:14 /佃園笔记2018/11/161J. OtJLJOtJtJExtra： Using filesort1 row in set rows irSTEIExtra:1 row in set 其中：extra额外信息。Using filesort，表示使用文件排序（外部排序，内存外部）索引覆盖索引拥有的关键字内容，覆盖了查询所需要的全部数据， 此时，就不需要在数据区获取数据, 仅仅在索引区即可。例如，利用名字检索:emp where enarne like ab匚 ;Lie select e

24、mpnOj enamej job from可以在ename字段建立索引:112 alter table emp addindex (ename);21 /佃分析执行: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检索了吧，同时考虑其他的使用场景。 就是不合理的）使用原则索引存在，没有满足使用原则，导致索引无效:列独立如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。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就不是列独立：就不能用索引。类似函数内等。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 - . - - = =、 工当=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严cm1-L.业务逻辑上出现：field like%keyw0类似查询，需要使用全文索引。复合索引

33、：一个索引关联多个字段。 仅仅针对左边字段有效果。丄f-r1=5146147alter table empadd index(enamej empno);丄Hi152153154155156select * froTi结果: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