数据库性能优化讲座.ppt

关于数据库性能优化的讲座,研发部讲师介绍：姓名：邵宗文部门：研发中心岗位：数据库平台主管主要负责：公司数据库平台2009年月6日,产品概要：给各个应用部门提供一个高可用的数据库平台服务。产品目标：通过数据库平台，实现应用项目数据库的资源合理调配，让应用部门能够更加专注于产品代码开发，无需过多考虑后台数据库的部署和运维。产品特色：实现数据库的高可用，对有问题的数据库机器实现自动故障下线和自动修复上线。针对数据库的各种状况的自动化监控，报警。分布式多IDC的数据中心，既能提高南北用户访问体验，并能做到IDC级容灾和切换。每天定时备份，保证了误操作之后几分钟之内相应数据恢复。自动将相关慢日志sql发送给对应应用开发人员依据各个应用项目的生命周期，进行机器资源的合理调配，给公司大大降低服务器成本。,数据库平台产品介绍,目前部署规模：4个IDC数据中心（北京，天津，上海，广州），约50T的数据量，约有300个产品项目使用，重点产品包括财经，体育，发布，音乐，读书，UC,统一会员,空间app，朋友，圈子，汽车，科技，房产，博客广告分享平台等。成本节省方面：通过数据库平台，大大节约了公司的成本，解决了以前各个部门单独申请机器，导致项目出现冷热周期而出现机器低使用率问题。重点产品优化案例：在2007年财经特别火爆时候，数据库访问量急剧增大，同时实时性必须得兼顾情况下，后来财经自己把大部分数据库迁移到数据库平台之后，上述问题都被成功解决，并且多个财经产品如自选股，模拟炒股。2008奥运会期间，数据库平台为体育部门成功解决了奥运期间数据量更新多，且实时性要求特别高的问题。,数据库平台成功案例,其他优化案例：新浪北美，香港的数据库架构改造。圈子数据库的重新设计和架构改造。发布数据库的数据库架构改造。UC数据库的迁移和架构重新改造统一会员信息库的重新架构和改造,目前数据库平台运维人员2人,数据库性能优化,数据库应用系统设计的性能考虑,数据库应用实现的性能优化,数据库参数的优化,缺省以MySQL4.0/4.1/5.0，MyISAM表为主,何时需要优化,低层次发现负载过高、性能下降时一般了解数据库处理机制，实现时优化索引高层次设计应用时，从表结构设计上保证,结构设计优化原则,1.了解自己的应用应用类型读多写少（如体育项目），读写比例差不多（如邮件），和写多读少（如投票，统计）预计数据量半年？一年？后续扩展？决定单表还是多表，扩展的方法预计访问量多少读？多少写？峰值？几台服务器，主从方式实时数据和非实时数据哪些必须实时查询？哪些可以预先准备或近似？哪些用于统计汇总？时间的要求实时性高的项目，如财经，体育，实时性低的项目如博客圈。,结构设计优化原则,2.数据表尽量小-行数少，字段类型高效-为什么？IO高效全表遍历表级锁提高并发度便于应用分布式结构可扩展性好altertable快损坏修复快备份和数据库重建时间短-手段：分库、分表使用最合适的类型长度，比如男女代码用tinyint就可以了，IP用varchar(15)就一些如当天统计活跃用户的自己内部需要的数据可以用内存表。应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。-负面影响：日志、统计等用途要慎选分表依据,分表原则的选择按时间按地区按ID，手机号按hash值要点：平均分担数据和负载,结构设计优化原则,3.表数量的限制-为什么？-受文件系统操作限制，文件数过大需要更多文件句柄，且大目录操作造成复制、压缩、备份效率低。-打开表占用数据库资源（table_cache）-建议一个库不应超过300-400个表-不当的设计：长期运营的项目，每次活动一个（一组）表-与“表尽量小”矛盾，一般来说表数量限制较严格,结构设计优化原则,4.字段定义最好能适当-最费时的操作是行寻址，后续的整块读写延迟有限-分割字段可能意味着联合查询（join）优点：数据逻辑清晰，冗余小，更新方便缺点：临时表，优化复杂-可以接受适当的冗余和汇总数据-尽量避免使用text,varchar(255),结构设计优化原则,5.访问量大的应用考虑读写分开使用replication适于读多写少的应用,写库master,读库1slave1,读库nslaven,HEAP内存表，缺省为hash索引（适合=，不适合range)速度快有长度限制适于做一些统计。InnoDB支持事务但是不容易维护，同时目前web应用主要还是读多写少。性能总体比MyISAM低，但有特例,6.其他类型的表格式,7.补充/替代解决方案,结果cache提高响应速度，减轻DB负载，如squid,phpcacheliteHash存储结构（文件库）速度快，消耗资源少，并发度高。无SQL能力，备份困难Memcached对频繁投票统计操作和长期变化不大的数据效果很好,Mysql的调用流程主要环节,语法分析,索引检索,全表检索,优化整合,连接数据库,发送查询请求用户认证,编译执行,中断连接,其他条件过滤,生成结果,自带优化功能,从索引取得,限制大小,Query_cache中存在,通过lex/yacc,Cache池,2.库表的优化,正确使用索引，避免全表搜索使用定长表,且定期做OPTIMIZETABLE命令（注意这个命令会锁表，请在数据库访问小的时候做）在对大表进行添加索引，一定要选择访问小的时间段做，否则会导致严重问题。注:一般临晨1-2点时候是访问的低谷。,索引使用的关键,“一次查询中一个表上只有一个索引会起作用！”5.0以后的版本有发展，增加了多个索引检索结果归并（index-merge）的机制。“索引会减慢写库操作，延长写入时间”所以只建立必要的有效的索引，并且可以使用insertdelayed等方法。减少磁盘的频繁IO开销。,索引优化第一步、发现问题,记录slowquerylog启动参数-log-slow-queries=log_file_path#Time:09060517:07:15#UserHost:biz_rbiz_r10.XX.XX.XX#Query_time:2Lock_time:0Rows_sent:66Rows_examined:175883selectdistinctF84_1039fromTB_OBJECT_1039,TB_OBJECT_1090where(F4_1090=AorF4_1090=B)andOB_REVISIONS_1090=F1_1039orderbyF84_1039desc;log_parser协助分析#99Queries#Totaltime:476,Averagetime:4.80808080808081#Taking3to14secondstocomplete#Rowsanalyzed1104-98810selectcount(*)fromkoubeiwheresubid=NNNandstatus=NNNandflg=NNN;selectcount(*)fromkoubeiwheresubid=111andstatus=1andflg=0;,索引优化第二步、查找原因,explainselectfromwhere.Gid:1select_type:SIMPLEtable:msg_1100type:ref(ALL)possible_keys:major_defect,major_defect_2,statuskey:major_defect_2(NULL)key_len:5ref:constrows:51863(越少越好)Extra:Usingwhere(Usingindex，UsingfilesortUsingtemporary),索引优化第三步、选择和试验,稳妥地改进将需要优化的相关表复制到测试环境在测试环境启动一个测试daemon，关闭querycache或是使用selectSQL_NO_CACHE方式。未优化时测试若干次查询时间选择合适的索引试验建立。可以通过useindex(xx)来强制使用。检查是否有效。测试查询时间变化，反复试验得到最优结果保持关注，根据情况随时改变索引设置,选择合适的索引（1）,选择区分度最大的字段最有效如果预测相关记录数超过一定比例（30%），数据库选择全表扫描。showindexfromtablename获取表上索引的情况。Cardinality“基数”-避免使用cardinality小的值做索引-避免NULL，通过analyzetabletablename得到更准确的估算,选择合适的索引（2）,联合索引规则-总是同时出现在查询条件的多个字段可以考虑联合索引-组成索引的字段从左到右地出现于查询条件时索引起作用，col1应该是最常用，区分度最好的字段createindexindex1ontable(col1,col2,col3);wherecol1=xandcol2=yandcol3=z;wherecol1=xandcol2=y;wherecol2=yandcol3=z;-不需要再建一个index(col1)了-可能用于orderby（稍后详述）,关于排序,尽量使用带主键的字段做orderby的排序尽量不要多提供页面的查找（最好只提供20页），避免机器爬虫查找，导致数据库压力负载过高。因为做orderbyfiledlimitxxxxxx,20是非常消耗数据库资源。,Union,一个含OR条件的语句可以分解成多个语句的union-好处：绕开一次查询只用一个索引的限制-例子：SELECT*FROMHeadlineWHEREExpireTime=1012201600ORId=1081020749ORDERBYExpireTimeASCLIMIT10)UNION(SELECT*FROMHeadlineWHEREIdFROM_DAYS(TO_DAYS(CURDATE()-N)例子：select*fromtablewherestatus=1andorderbyupdate_timelimit10000,10;,使用定长表,优点表长度上限高（showtablestatusMax_data_length）查询速度快，生成结果快（由于寻址快）表损坏影响有限修复快缺点空间浪费权衡：分离变长字段到另外的表，提升主表性能,3.数据库参数的优化,showstatus;(5.0之后的用showglobalstatus)Flushstatusshowvariables;(5.0之后的用showglobalstatus)showprocesslist;,索引缓冲区参数,showglobalstatuslikekey%Key_blocks_used曾经使用过的最大缓冲区块数Key_read_requests读取索引请求数Key_reads物理读取索引次数Key_write_requests写索引请求数Key_writes物理写索引次数若key_reads/key_read_request过大(1%)，需要提高key_buffer_size可参考：如果key_block_used*1Kkey_buffer_size，也可考虑提高负面影响过大可能造成换出；系统崩溃造成的影响更大最多是所有.MYI的大小粗略估算索引大小：（key值长度+4）*行数*1.5,排序相关参数,showglobalstatuslikesort%Sort_merge_passes中间结果merge次数Sort_range部分数据排序Sort_scan扫描全表排序Sort_rows排序结果总行数sort_merge_passes如果很大，说明需要提高sort_buffer_sizesort_scan非常大，可能需要优化索引Sortbuffer是线程buffer，总分配额是buffer_size*threads，不要过大造成换出。,tmp_table_size,showstatuslikeCreated_tmp_%;Created_tmp_disk_tables在磁盘上建立临时表数Created_tmp_tables在内存和磁盘上建立临时表总数Created_tmp_files建立临时文件数EXPLAIN里显示UsingTemporary如果created_tmp_disk_tables所占比例高，可以考虑提高tmp_table_size,打开数据表,Showglobalstatuslikeopen%;Open_files打开文件数Open_tables打开表数Opened_tablesopen表次数总计Opened_tables很大，说明table_cache参数需要调高,QueryCache是什么selectsql_cache|sql_no_cachecolsfromtable启动参数query_cache_size64Mshowstatuslikeq%：Qcache_hitsQcache_free_memoryQuestions命中率=QCache_hits/Questions,querycache参数,锁的问题,MyISAM是表级锁分表的设计减少锁发生的可能,但是受磁盘IO影响，在一个机器的对频繁写的大表拆分并不能解决问题。showstatusliketable%Table_locks_immediate立即获得表锁次数Table_locks_waited等待表锁释放次数Table_locks_waited比例高的话要考虑分表用showprocesslist观察锁的情况“Locked”是被锁的线程，要关心其他运行状态的产生锁的线程，例如正在“Sortingresult”、“Sendingdata”,锁的问题（2）,读-读不会有锁读-写，写-写原则上不可并发特殊情况：insert+表中无删除行通常写操作优先级高通过启动参数-low-priority-updates改变结论：避免复杂耗时查询特别是写操作多的表,提示,%关键字%任何索引不起作用，应尽量避免，例如使用全文检索。如果不可避免，尽量使用大于3个字符的关键字，mysql对此采用了较快速的算法长度小于一个数据块的表，不用建索引SELECT*优：sql语句短劣：结果集不受控制，表改变易错尽量少用MySQL做复杂计算，比如md5()多个磁盘，(RAID10)有助于性能和稳定性,结语,“结合实际情况不断优化”“让数据库多做它擅长的工作”谢谢参与！一些mysql工具下载地址Yuminstallsinasrv2-mysqlha,Q&A,研发部讲师联系方式：姓名：邵宗文分机：5303邮箱：zongwen手机：13426095308MSN:helbreathszw,积极创新责任,