数据库性能优化讲座-2

关于数据库性能优化的讲座研发部讲师介绍：姓名：邵宗文部门：研发中心岗位：数据库平台主管主要负责：公司数据库平台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,phpcachelite•Hash存储结构（文件库）速度快，消耗资源少，并发度高。无SQL能力，备份困难•Memcached对频繁投票统计操作和长期变化不大的数据效果很好Mysql的调用流程主要环节语法分析索引检索全表检索优化整合连接数据库发送查询请求用户认证编译执行中断连接其他条件过滤生成结果自带优化功能从索引取得限制大小Query_cache中存在通过lex/yaccCache池2.库表的优化•正确使用索引，避免全表搜索•使用定长表,且定期做OPTIMIZETABLE命令（注意这个命令会锁表，请在数据库访问小的时候做）•在对大表进行添加索引，一定要选择访问小的时间段做，否则会导致严重问题。注:一般临晨1-2点时候是访问的低谷。索引使用的关键•“一次查询中一个表上只有一个索引会起作用！”5.0以后的版本有发展，增加了多个索引检索结果归并（index-merge）的机制。•“索引会减慢写库操作，延长写入时间”所以只建立必要的有效的索引，并且可以使用insertdelayed等方法。减少磁盘的频繁IO开销。索引优化第一步、发现问题•记录slowquerylog启动参数--log-slow-queries[=log_file_path]#Time:09060517:07:15#User@Host:biz_r[biz_r]@[10.XX.XX.XX]#Query_time:2Lock_time:0Rows_sent:66Rows_examined:175883selectdistinctF84_1039fromTB_OBJECT_1039,TB_OBJECT_1090where(F4_1090='A'orF4_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;索引优化第二步、查找原因explainselect…from…where….\G•id:1•select_type:SIMPLE•table:msg_1100•type:ref(ALL)•possible_keys:major_defect,major_defect_2,status•key:major_defect_2(NULL)•key_len:5•ref:const•rows: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=5000000ORDERBYExpireTimeASCLIMIT10(SELECT*FROMHeadlineWHEREExpireTime=1081020749ORDERBYExpireTimeASCLIMIT10)UNION(SELECT*FROMHeadlineWHEREId=50000ORDERBYExpireTimeASCLIMIT10)ORDERBYExpireTimeASCLIMIT10Orderby的优化•排序的步骤1.通过索引或全表搜索得到符合条件数据的tuple（索引值+行指针），存放在sortbuffer2.如果sort_buffer满了，做一次排序，生成一个有序块存放在临时文件3.重复上面两步处理所有数据4.多路归并各有序块，得到最后的排序结果（行指针）5.根据行指针取得要求的字段PS:通过适当提高sort_buffer_size,tmp_table_size来分配多一些内存给查询语句使用。•想办法“不做”或“少做”•一般来说用整数排序比字符串排序稍好Orderby（2）•“不做”-selectt.*fromtleftjoinsons.id=t.idwheres.name=t.name;-select*fromtwheretimexxFROM_DAYS(TO_DAYS(CURDATE())-N)例子：select*fromtablewherestatus=1and…orderbyupdate_timelimit10000,10;使用定长表•优点表长度上限高（showtablestatusMax_data_length）查询速度快，生成结果快（由于寻址快）表损坏影响有限修复快•缺点空间浪费•权衡：分离变长字段到另外的表，提升主表性能3.数据库参数的优化•showstatus;(5.0之后的用showglobalstatus)–Flushstatus•showvariables;(5.0之后的用showglobalstatus)•showprocesslist;索引缓冲区参数•show[global]statuslike‘key%’–Key_blocks_used曾经使用过的最大缓冲区块数–Key_read_requests读取索引请求数–Key_read