数据库性能优化讲座

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,关于数据库性能优化的讲座,研发部讲师介绍：,姓名：邵宗文,部门：研发中心,岗位：数据库平台主管,主要负责：公司数据库平台,2009,年月,6,日,产品概要：给各个应用部门提供一个高可用的数据库平台服务,。,产品目标：通过数据库平台，实现应用项目数据库的资源合理调配，让应用部门能够更加专注于产品代码开发，无需过多考虑后台数据库的部署和运维。,产品特色：,实现数据库的高可用，对有问题的数据库机器实现自动故障下线和自动修复上线。,针对数据库的各种状况的自动化监控，报警。,分布式多,IDC,的数据中心，既能提高南北用户访问体验，并能做到,IDC,级容灾和切换。,每天定时备份，保证了误操作之后几分钟之内相应数据恢复。,自动将相关慢日志,sql,发送给对应应用开发人员,依据各个应用项目的生命周期，进行机器资源的合理调配，给公司大大降低服务器成本。,数据库平台产品介绍,目前部署规模：,4,个,IDC,数据中心（北京，天津，上海，广州），约,50T,的数据量，约有,300,个产品项目使用，重点产品包括财经，体育，发布，音乐，读书，,UC,统一会员,空间,app,，朋友，圈子，汽车，科技，房产，博客广告分享平台等。,成本节省方面：,通过数据库平台，大大节约了公司的成本，解决了以前各个部门单独申请机器，导致项目出现冷热周期而出现机器低使用率问题。,重点产品优化案例：,在,2007,年财经特别火爆时候，数据库访问量急剧增大，同时实时性必须得兼顾情况下，后来财经自己把大部分数据库迁移到数据库平台之后，上述问题都被成功解决，并且多个财经产品如自选股，模拟炒股。,2008,奥运会期间，数据库平台为体育部门成功解决了奥运期间数据量更新多，且实时性要求特别高的问题。,数据库平台成功案例,其他优化案例：,新浪北美，香港的数据库架构改造。,圈子数据库的重新设计和架构改造。,发布数据库的数据库架构改造。,UC,数据库的迁移和架构重新改造,统一会员信息库的重新架构和改造, 目前数据库平台运维人员,2,人,数据库性能优化,数据库应用系统设计的性能考虑,数据库应用实现的性能优化,数据库参数的优化,缺省以,MySQL 4.0/4.1/5.0,，,MyISAM,表为主,何时需要优化,低层次,发现负载过高、性能下降时,一般,了解数据库处理机制，实现时优化索引,高层次,设计应用时，从表结构设计上保证,结构设计优化原则,1.,了解自己的应用,应用类型,读多写少（如体育项目），读写比例差不多（如邮件），和写多读少（如投票，统计）,预计数据量,半年？一年？后续扩展？, 决定单表还是多表，扩展的方法,预计访问量,多少读？多少写？峰值？, 几台服务器，主从方式,实时数据和非实时数据,哪些必须实时查询？哪些可以预先准备或近似？哪些用于统计汇总？,时间的要求,实时性高的项目，如财经，体育，实时性低的项目如博客圈。,结构设计优化原则,2.,数据表尽量小,-,行数少，字段类型高效,-,为什么？,IO,高效,全表遍历,表级锁,提高并发度,便于应用分布式结构,可扩展性好,alter table,快,损坏修复快,备份和数据库重建时间短,-,手段：,分库、分表,使用最合适的类型长度，比如男女代码用,tinyint,就可以了，,IP,用,varchar(15),就,一些如当天统计活跃用户的自己内部需要的数据可以用内存表。,应该尽量把字段设置为,NOT NULL,，这样在将来执行查询的时候，数据库不用去比较,NULL,值。,-,负面影响：,日志、统计等用途要慎选分表依据,分表原则的选择,按时间,按地区,按,ID,，手机号,按,hash,值,要点：平均分担数据和负载,结构设计优化原则,3.,表数量的限制,-,为什么？,-,受文件系统操作限制，文件数过大需要更多文件句柄，且大目录操作造成复制、压缩、备份效率低。,-,打开表占用数据库资源（,table_cache,）,-,建议一个库不应超过,300-400,个表,-,不当的设计：,长期运营的项目，每次活动一个（一组）表,-,与“表尽量小”矛盾，一般来说表数量限制较严格,结构设计优化原则,4.,字段定义最好能适当,-,最费时的操作是行寻址，后续的整块读写延迟有限,-,分割字段可能意味着联合查询（,join,）,优点：数据逻辑清晰，冗余小，更新方便,缺点：临时表，优化复杂,-,可以接受适当的冗余和汇总数据,-,尽量避免使用,text,varchar(255),结构设计优化原则,5.,访问量大的应用考虑读写分开,使用,replication,适于读多写少的应用,写库,master,读库,1,slave1,读库,n,slaven, ,HEAP,内存表，缺省为,hash,索引（适合,=,，不适合,range),速度快,有长度限制,适于做一些统计。,InnoDB,支持事务,但是不容易维护，同时目前,web,应用主要还是读多写少。,性能总体比,MyISAM,低，但有特例,6.,其他类型的表格式,7.,补充,/,替代解决方案,结果,cache,提高响应速度，减轻,DB,负载，如,squid,php cachelite,Hash,存储结构（文件库）,速度快，消耗资源少，并发度高。,无,SQL,能力，备份困难,Memcached,对频繁投票统计操作和长期变化不大的数据效果很好,Mysql,的调用流程主要环节,语法分析,索引检索,全表检索,优化整合,连接数据库,发送查询请求 用户认证,编译执行,中断连接,其他条件过滤,生成结果,自带优化功能,从索引取得,限制大小,Query_cache,中存在,通过,lex/yacc,Cache,池,2.,库表的优化,正确使用索引，避免全表搜索,使用定长表,且定期做,OPTIMIZE TABLE,命令（注意这个命令会锁表，请在数据库访问小的时候做）,在对大表进行添加索引，一定要选择访问小的时间段做，否则会导致严重问题。,注,:,一般临晨,1-2,点时候是访问的低谷。,索引使用的关键,“,一次查询中一个表上只有一个索引会起作用！ ”,5.0,以后的版本有发展，增加了多个索引检索结果归并（,index-merge,）的机制。,“索引会减慢写库操作，延长写入时间”,所以只建立必要的有效的索引，并且可以使用,insert delayed,等方法。减少磁盘的频繁,IO,开销。,索引优化第一步、发现问题,记录,slow query log,启动参数,-log-slow-queries=,log_file_path,# Time: 090605 17:07:15,# UserHost: biz_rbiz_r 10.XX.XX.XX,# Query_time: 2 Lock_time: 0 Rows_sent: 66 Rows_examined: 175883,select distinct F84_1039 from TB_OBJECT_1039,TB_OBJECT_1090 where (F4_1090=A or F4_1090=B) and OB_REVISIONS_1090=F1_1039 order by F84_1039 desc;,log_parser,协助分析,# 99 Queries,# Total time: 476, Average time: 4.80808080808081,# Taking 3 to 14 seconds to complete,# Rows analyzed 1104 - 98810,select count(*) from koubei where subid=NNN and status=NNN and flg=NNN;,select count(*) from koubei where subid=111 and status=1 and flg=0;,索引优化第二步、查找原因,explain select 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: Using where (,Using index,，,Using filesort,Using temporary,),索引优化第三步、选择和试验,稳妥地改进,将需要优化的相关表复制到测试环境,在测试环境启动一个测试,daemon,，关闭,query cache,或是使用,select SQL_NO_CACHE,方式。,未优化时测试若干次查询时间,选择合适的索引试验建立。可以通过,use index(xx),来强制使用。检查是否有效。,测试查询时间变化，反复试验得到最优结果,保持关注，根据情况随时改变索引设置,选择合适的索引（,1,）,选择区分度最大的字段最有效,如果预测相关记录数超过一定比例（,30%,），数据库选择全表扫描。,show index from,tablename,获取表上索引的情况。,Cardinality “,基数”,-,避免使用,cardinality,小的值做索引,-,避免,NULL,，通过,analyze table,tablename,得到更准确的估算,选择合适的索引（,2,）,联合索引规则,-,总是同时出现在查询条件的多个字段可以考虑联合索引,-,组成索引的字段从左到右地出现于查询条件时索引起作用，,col1,应该是最常用，区分度最好的字段,create index index1 on table (col1, col2, col3);, where col1=x and col2=y and col3=z;, where col1=x and col2=y;, where col2=y and col3=z;,-,不需要再建一个,index(col1),了,-,可能用于,order by,（稍后详述）,关于排序,尽量使用带主键的字段做,order by,的排序,尽量不要多提供页面的查找（最好只提供,20,页），避免机器爬虫查找，导致数据库压力负载过高。因为做,order by filed limit xxxxxx,20,是非常消耗数据库资源。,Union,一个含,OR,条件的语句可以分解成多个语句的,union,-,好处：绕开一次查询只用一个索引的限制,-,例子：,SELECT * FROM Headline WHERE ExpireTime = 1012201600 OR Id = 1081020749 ORDER BY ExpireTime ASC LIMIT 10),UNION,(SELECT * FROM Headline WHERE Id FROM_DAYS(TO_DAYS(CURDATE()-N),例子：,select * from table where status=1 and order by update_time limit 10000,10;,使用定长表,优点,表长度上限高 （,show table status,Max_data_length,）,查询速度快，生成结果快（由于寻址快）,表损坏影响有限,修复快,缺点,空间浪费,权衡：分离变长字段到另外的表，提升主表性能,3.,数据库参数的优化,show status;,(5.0,之后的用,show global status),Flush status,show variables,; (5.0,之后的用,show global status),show processlist;,索引缓冲区参数,show global status like key%,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,排序相关参数,show global status like sort%,Sort_merge_passes,中间结果,merge,次数,Sort_range,部分数据排序,Sort_scan,扫描全表排序,Sort_rows,排序结果总行数,sort_merge_passes,如果很大，说明需要提高,sort_buffer_size,sort_scan,非常大，可能需要优化索引,Sort buffer,是线程,buffer,，总分配额是,buffer_size*threads,，不要过大造成换出。,tmp_table_size,show status like Created_tmp_%;,Created_tmp_disk_tables,在磁盘上建立临时表数,Created_tmp_tables,在内存和磁盘上建立临时表总数,Created_tmp_files,建立临时文件数,EXPLAIN,里显示,Using Temporary,如果,created_tmp_disk_tables,所占比例高，可以考虑提高,tmp_table_size,打开数据表,Show global status like open%;,Open_files,打开文件数,Open_tables,打开表数,Opened_tablesopen,表次数总计,Opened_tables,很大，说明,table_cache,参数需要调高,Query Cache,是什么,select sql_cache|sql_no_cache cols from table,启动参数,query_cache_size 64M,show status like q%,：,Qcache_hits,Qcache_free_memory,Questions,命中率,= QCache_hits/Questions,query cache,参数,锁的问题,MyISAM,是表级锁,分表的设计减少锁发生的可能,但是受磁盘,IO,影响，在一个机器的对频繁写的大表拆分并不能解决问题。,show status like table%,Table_locks_immediate,立即获得表锁次数,Table_locks_waited,等待表锁释放次数,Table_locks_waited,比例高的话要考虑分表,用,show processlist,观察锁的情况,“,Locked”,是被锁的线程，要关心其他运行状态的产生锁的线程，例如正在“,Sorting result”,、“,Sending data”,锁的问题（,2,）,读,-,读不会有锁,读,-,写，写,-,写原则上不可并发,特殊情况：,insert +,表中无删除行,通常写操作优先级高,通过启动参数,-low-priority-updates,改变,结论：避免复杂耗时查询,特别是写操作多的表,提示,%,关键字,%,任何索引不起作用，应尽量避免，例如使用全文检索。如果不可避免，尽量使用大于,3,个字符的关键字，,mysql,对此采用了较快速的算法,长度小于一个数据块的表，不用建索引,SELECT *,优：,sql,语句短,劣：结果集不受控制，表改变易错,尽量少用,MySQL,做复杂计算，比如,md5(),多个磁盘，,(RAID 10),有助于性能和稳定性,结语,“,结合实际情况不断优化”,“让数据库多做它擅长的工作”,谢谢参与,！,一些,mysql,工具,下载地址,Yum install sinasrv2-mysqlha,Q & A,研发部讲师联系方式：,姓名： 邵宗文,分机：,5303,邮箱：,zongwen,手机：,13426095308,MSN: helbreathszw,积 极 创新 责任,