mysql深入优化

Mysql深入优化dba&qaMysql深入优化本文档部分基于jaypipesjaypipesjaypipesjaypipes的演讲稿：PerformancecodingformysqlPerformancecodingformysqlPerformancecodingformysqlPerformancecodingformysql本文档部分测试结果来自网络，一并对原作者表示感谢mysqlmysqlmysqlmysql的系统架构的系统架构的系统架构的系统架构•高度耦合的子系统•基于链接的内存分配（相对于全局内存模块）•各种等级的cache•基于代价的优化器•模块化的存储引擎schema设计的基本原则•关系应用性能的基础构件•先设计基本系统，在设计特殊系统•尽可能的小（表，字段，数据类型）--abc原则•分而治之•清楚各种存储引擎的优缺点基础搞不好，建不起高楼themorerecordsyoucanfitintoapageinmemory/diskthefasterperformanceyoucando•是不是真的需要一个bigint的类型的主键？innodb引擎主键越小越好•使用intunsignednotnull来处理ip地址•谨慎的使用varchar数据类型myisam用charinnodb用varchar临时表用char代替varchar•尽量少的使用text类型如果有必要，还是考虑分表吧•坚决抵制使用blob数据类型如有必要，还是尽量用文件系统的文件来存储吧。smaller,smaller,smallersmaller,smaller,smallersmaller,smaller,smallersmaller,smaller,smaller以小定天下小原则之ipipipip地址的处理INET_ATON()把字符串转为数字INET_NTOA()把数字转为字符串分而治之分而治之分而治之分而治之•垂直分表把有很多列的表分成多个表•水平分表把有很多行的表分成多个表•不同的情况考虑不用的分表原则•mysql5.0之后的分区功能是就是一个对应用透明的水平分表功能垂直分表垂直分表垂直分表垂直分表#1#1#1#1•第一个表是基本数据，第二个表是额外数据且数据量较大•额外信息这块很多数据都是null或者很少被访问•考虑到结果所占的内存空间的尺寸分成的一级信息表，尺寸较小，一个内存页能装下更多的数据。查询速度也快垂直分表#2#2#2#2•把不变的数据分成一个表•计数数据经常修改，分成一个表•考虑到cache的效果不变部分的数据可以很好的缓存到querycache中而对计数数据的查询加hintsql_no_cache不写querycache提高性能•innodb表对不带条件的count()的操作很差，建立一个对应count表垂直分表垂直分表垂直分表垂直分表#3#3#3#3•我们目前的articlearticle_ontent模式是值得推荐的•一个不好的例子：女性网晒客：sk_dinary单行数据包括2个clob/text字段，8个255,1个1024,1个4000的vavarchar2单行数据超过8k,在oracle这边已经超过一个数据块的尺寸，如果迁移到mysql，将是很大的考验。垂直分表垂直分表垂直分表垂直分表#4----example#4----example#4----example#4----example垂直分表垂直分表垂直分表垂直分表#4----example#4----example#4----example#4----example垂直分表垂直分表垂直分表垂直分表#4----example#4----example#4----example#4----example垂直分表垂直分表垂直分表垂直分表#4----example#4----example#4----example#4----examplequerycachequerycachequerycachequerycache•必须确定应用的读写比例•querycache的设计原则是CPU与读性能的一个平衡点•querycache是基于select语句的hash结果集并且严格区分sql的版本严格区分大小写，空格数量相同的sql当前库不同，亦不匹配client的字符集不同也要cache两份数据•对sql语句中表的任何修改都将使结果集失效。•写多的应用不应该使用querycachequery_cache_type=0query_cache_size=0index---数据库黄页•提高查询速度，但是会影响数据修改的速度不是索引越多越好•确保where,on,groupby,orderby这些字句的字段在索引上•确保join的字段上有索引，且数据类型是一致的数据类型不一致，会有隐含的数据转换，增加开销•注意列的顺序原则上按照在表中的顺序。•索引覆盖(coveringindex)当一个select所查询的所有字段都在索引记录中的时候，不会再去从表中查询数据，直接从索引返回。•删除不需要/重复的索引•注意列在索引中的排列顺序只有最左边的列出现在where条件中，才有可能走索引•理解各种存储引擎中索引记录的组织形式index---数据库黄页myisammyisammyisammyisam引擎索引结构•非cluster数据结构•会出现空洞需要定期整理•....myd文件存放表记录表数据是无序存放的•.myi.myi.myi.myi文件存放所有的索引索引记录一个指针指向.myd文件中具体的数据记录index---数据库黄页innodbinnodbinnodbinnodb引擎索引结构引擎索引结构引擎索引结构引擎索引结构•cluster数据结构（主键索引与数据存放在一起）二级索引是Btree结构叶子节点是存储指向主键的指针而非指向数据•数据页为16KB数据页包含主键索引的叶子节点（数据+索引值）每个数据页都有个字典（位于数据页的尾部）存储排过序的主键索引值与指向数据页内具体记录的指针•主键字段越小越好。所有的二级索引都会存储主键索引值•如果没有指定主键，系统会自动制定一个唯一的索引作为主键。索引对查询效率的影响索引对查询效率的影响索引对查询效率的影响索引对查询效率的影响---1---1---1---1过度索引是如何影响数据库的性能的呢？1。在执行sql之前，数据库会根据metadata信息决定该使用哪个索引，如果索引过多会影响这一步骤的效率。2。由于每次数据更新和插入都要更新索引，因此会影响相关操作的效率测试环境：droptableifEXISTStest_index_performance;CREATETABLEtest_index_performance(idintprimarykey,col1varchar(10),col2varchar(10),col3varchar(10),col4varchar(10),col5varchar(10),col6varchar(10),col7varchar(10),col8varchar(10),col9varchar(10),col10varchar(10))engine=innodb;createPROCEDUREinsert_data_for_test_index_performance()beginDECLAREtotalintdefault100000;DECLAREiintdefault0;truncatetabletest_index_performance;while(itotal)doinsertintotest_index_performancevalues(i,’a',’a',’a',’a',’a',’a',’a',’a',’a',’a');seti=i+1;endwhile;end$$delimiter;callinsert_data_for_test_index_performance();索引对查询效率的影响索引对查询效率的影响索引对查询效率的影响索引对查询效率的影响---2---2---2---2测试1createindexidx1ontest_index_performance(col1);createindexidx2ontest_index_performance(col1,col2);createindexidx3ontest_index_performance(col1,col2,col3);createindexidx4ontest_index_performance(col1,col2,col3,col4);createindexidx5ontest_index_performance(col1,col2,col3,col4,col5);createindexidx6ontest_index_performance(col1,col2,col3,col4,col5,col6);createindexidx7ontest_index_performance(col1,col2,col3,col4,col5,col6,col7);createindexidx8ontest_index_performance(col1,col2,col3,col4,col5,col6,col7,col8);createindexidx9ontest_index_performance(col1,col2,col3,col4,col5,col6,col7,col8,col9);createindexidx10ontest_index_performance(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10);执行以下语句selectcount(*)fromtest_index_performancewherecol1=’a’;–showprofileforquery1;结果的statistics部分–1索引0.000070–2索引0.000083–3索引0.000107–4索引0.000112–5索引0.000126–6索引0.000155–7索引0.000152–8索引0.000164–9索引0.000187结果一：与执行计划相关的索引（出现在possiblekeyspossiblekeyspossiblekeyspossiblekeys的那些），索引的数量与sqlsqlsqlsql执行消耗时间成正比。索引对查询效率的影响索引对查询效率的影响索引对查询效率的影响索引对查询效率的影响---3---3---3---3测试2createindexidx12ontest_index_performance(col2);createindexidx13ontest_index_performance(col2,col3);createindexidx14ontest_index_performance(col2,col3,col4);createindexidx15ontest_index_performance(col2,col3,col4,col5);createindexidx16ontest_index_performance(col2,col3,col4,col5,col6);createindexidx17ontest_index_performance(col2,col3,col4,col5,col6,col7);createindexidx18ontest_index_performance(col2,col3,col4,col5,col6,col7,col8);createindexidx19ontest_index_performance(col2,col3,col4,col5,col6,col7,col8,col9);createindexidx20ontest_index_performance(col2,col3,col4,col5,co