Optimzingmysql酷讯网.ppt

,Optimzingmysql,Ifeeltheneed.theneedforspeed.,壮志凌云（topgun）,性能监控10分性能优化20分常见的架构10分Toolsandtips10分,性能监控,操作系统级别的Mysql级别,操作系统,vmstatiostatfreetopsarmpstatuptime,netstatstrace,mysql,ShowprocesslistMysqlreport,vmstat,基本的物理和虚拟内存的使用和一些基本的系统统计信息VmstatSMVmstat55Swap颠簸现象Toppsaxl,iostat,度量磁盘i/o,cpu和设备io使用Iostat-ciostatdx5%iowait并发请求的数量并发=（r/s+w/s）*(svctm/1000)(从头到尾服务请求时间),Cpu密集型机器,Io密集型,Swapidle,谁可能导致高的cpuusagequery，joins,every进程切换，锁表内存排序临时表加密算法谁可能导致高的diskusage临时表硬盘上排序startwithanidea,thenlookforinfromationsupportit,netstat,服务链接状态netstat-nat|awkprint$6|sort|uniq-c|sort-n是否被dos：netstat-atun|awkprint$5|cut-d:-f1|sed-e/$/d|sort|uniq-c|sort-n/bin/netstat-na|grepESTABLISHED|awkprint$5|awk-F:print$1|sort|uniq-c|sort-rn|grep-v-E172.16|127.0|awkif($2!=nullshowglobalstatuslikekey_read%;key_cache_miss_rateKey_reads/Key_read_requests*100%duschfind/mysqldatapath/-name“*.MYI”Innodb缓冲池innodb_bufer_pool_size80%showstatuslikeInnodb_buffer_pool_read%Innodb_buffer_pool_read_requests/Innodb_buffer_pool_readsshowinnodbstatusGBUFFERPOOLANDMEMORYTotalmemoryallocated4668764894;,查询缓存,showglobalstatuslikeQcache%;showvariableslikequery_cache%;查询缓存利用率=(query_cache_sizeQcache_free_memory)/query_cache_size*100%查询缓存命中率=(Qcache_hitsQcache_inserts)/Qcache_hits*100%,innodb,刷写日志缓冲Innodb_flush_log_at_trx_commit012打开并清写日志和文件innodb_flush_method,myisam,初始化：initial=key_buffer_size+query_cache_size连接per_connection=(sort_buffer_size+read_rnd_buffer_size+join_buffer_size+read_buffer_size+thead_stack)总和：initial+max_connections*per_connectioninitial+(max_connections*per_connection)/3),innodb,初始化：initial=innodb_buffer_pool_size+query_cache_size连接per_connection=(sort_buffer_size+join_buffer_size+thead_stack+binlog_cache_size+read_buffer_size+read_rnd_buffer_size)总和：initial+max_connections*per_connectioninitial+(max_connections*per_connection)/3),数据类型选择,更小通常更好,慷慨并不明智简单就好使用mysql内建的类型保存日期和时间，使用timestamp保存，空间是datetime一半使用整数保存ip15bytesforchar(15)vs.4bytesfortheintegerip2long()和long2ip()inet_aton3尽量避免null4Char/varcha的选择对于MyISAM而言，如果没有VARCHAR，TEXT等变长类型，那么每行数据所占用的空间都是定长的（Fixed），俗称静态表，相对应的就是动态表。当执行一次查询时，MySQL可以通过索引文件找到所需内容的实际行号，此时，由于每行数据所占用的空间都是定长的（Fixed），所以可以通过查询到的实际行号直接定位到数据文件的具体位置，对于InnoDB而言，数据行是没有所谓定长与否的概念的，这是由其结构所决定的：在InnoDB中，数据就位于PrimaryKey的B-Tree叶节点之上而除PrimaryKey之外的索引被称为SecondaryIndex，它们直接指向PrimaryKey。用char来代替varchar，MyISAM是这样，InnoDB则相反5使用enum代替字符串类型selectinternet+0fromhotel_infogroupbyinternet;,索引,隔离列select*fromtablenamewhereid+1=5Select*whereTO_DAYS(CURRENT_DATE)TO_DAYS(data_col)=date_sub(current_date,interval10day)Select*wheredata_col=date_sum(2010-04-12,interval10day)EXPLAINSELECT*FROMfilmWHEREtitleLIKETr%GEXPLAINSELECT*FROMfilmWHERELEFT(title,2)=TrG组合索引前缀索引覆盖索引合并索引去除多余索引和重复索引createtabletest(idintnotnullprimarykey,unique(id),index(id),合并索引,索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素,SELECT*FROMtbl_nameWHEREkey_part1=10ORkey_part2=20;,前缀索引,Key(a,b,c)Orderbya,orderbya,borderbya,b,corderbyadesc,bdesc,cdescWherea=constorderbyb,c,wherea=constandb=constorderbycWherea=constorderbyb,cwherea=constandbconstorderby,cOrderbyaasc,bdesc,cdescWhereg=constoderbyb,cWherea=constorderbycWherea=constorderbya,dWhereaxxorderbyb,cWhereaxxorderbya,bWherea=constorderbybdesc,aasc,覆盖索引,Select*fromproductswhereactor=seancarreyandandtitlelike%apollo%Select*fromproductsjoin(selectprod_idfromproductswhereactor=seancarreyandtitlelike%apollo%)ast1on(d_id=_id),逆范式化,适当的冗余分拆表,产品设计,产品设计-数据交互-mysqlAntoinedeSaintExupery是法国作家兼飞机设计师，他曾经说过：“设计者确定其设计已经达到了完美的标准不是不能再增加任何东西，而是不能再减少任何东西。”分页的实现,1explainselectSQL_NO_CACHE*frompage_testforceindex(idx_b_c)whereb=1orderbycdesclimit2000,10;+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|page_test|ref|idx_b_c|idx_b_c|4|const|2222|Usingwhere|+-+-+-+-+-+-+-+-+-+-+2mysqlexplainselectSQL_NO_CACHE*frompage_test,(selectSQL_NO_CACHEidfrompage_testforceindex(idx_b_c)whereb=1orderbycdesclimit2000,10)tempwherepage_test.id=temp.id;+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+-+-+-+-+-+-+-+-+-+-+|1|PRIMARY|ALL|NULL|NULL|NULL|NULL|10|1|PRIMARY|page_test|eq_ref|PRIMARY|PRIMARY|8|temp.id|1|2|DERIVED|page_test|ref|idx_b_c|idx_b_c|4|2222|Usingwhere;Usingindex|+-+-+-+-+-+-+-+-+-+-+3explainselectSQL_NO_CACHE*frompage_testforceindex(idx_b_id)whereb=1andid10;selectsql_no_cache(selectcount(*)fromstatistic_go)-count(*)fromstatistic_gowhereid=0;建立计数器触发器对同一个表的select和updateupdatehotel_imageinnerjoin(selectcount(*)ascntfromhotel_image)asdersethotel_image.size=t;,其它一些,Groupby不进行排序，可以orderybynull能够快速缩小结果集的WHERE条件写在前面，如果有恒量条件，也尽量放在前面使用UNION来取代IN和OR定期执行optimize/analyzetable往innoDB表导入数据时，先关闭autocommit模式，否则会实时刷新数据到磁盘对于频繁更改的MyISAM表,应尽量避免更新所有变长字段(VARCHAR、BLOB和TEXT)分表分库汇总表十大热门话题createtablehotel_infonewlikehotel_info;renametablehotel_infotohotel_info_old,hotel_infonewtohotel_info放弃关系型数据库key=value，计数表,常见架构方案,Replication双master集群。,replication,1数据分发,scaleout，sacleup2负载均衡loadbalance3备份，一般不会用作备份，一旦执行delete操作，replication也不会保留4高可用5可以在不同的主从库上使用不同的存储引擎,原理,简单的讲就是master记录其变化到binlog，slave接收到变化后会记录到他的Relaylog，slave通过重放relaylog，然后就写进自己的log1)、Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；2)、Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；3)、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；4)、Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行,常见问题,延迟主从不同步命令复制基于行复制,高可用,通过Heartb