数据库性能优化讲座.ppt

关于数据库性能优化的讲座,研发部讲师介绍： 姓名：邵宗文 部门：研发中心 岗位：数据库平台主管 主要负责：公司数据库平台 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 = 50000 ORDER BY ExpireTime ASC LIMIT 10) ORDER BY ExpireTime ASC LIMIT 10,Order by的优化,排序的步骤 1. 通过索引或全表搜索得到符合条件数据的tuple（索引值+行指针），存放在sort buffer 2. 如果sort_buffer满了，做一次排序，生成一个有序块存放在临时文件 3. 重复上面两步处理所有数据 4. 多路归并各有序块，得到最后的排序结果（行指针） 5. 根据行指针取得要求的字段 PS: 通过适当提高sort_buffer_size,tmp_table_size来分配多一些内存给查询语句使用。 想办法“不做”或“少做” 一般来说用整数排序比字符串排序稍好,Order by （2）,“不做” - select t.* from t left join s on s.id=t.id where =; - select * from t where timexxFROM_DAYS(TO_DAYS(CURDATE()-N) 例子：select * from table where status=1 and order by update_time limit 10000,10;,使用定长表,优点 表长度上限高 （show table statusMax_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_tables open表次数总计 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影响，在一个机器的