《数据库性能优化》PPT课件.ppt

数据库性能优化 B2B DBA 陈立 2011年Q3 主题 基本元素访问堆表访问B树索引案例分析执行计划 基本元素 总览 一本字典 一个表字典中的一页 一个数据块一个字的解释 一笔记录页码跟行号 ROWID 基本元素 堆表 物理上由若干数据块组成物理上的分界线 HighWaterMark逻辑上由若干数据行组成每笔数据的唯一地址 ROWID 基本元素 数据块 保存数据的最小容器读取数据的最小单位 8k或16k 固定大小 基本元素 ROWID 访问一笔记录的最快方式ROWID只是一个坑 不是一个萝卜 用它来访问数据虽然快 但是不安全 因为随着DELETE INSERT操作 坑里埋的萝卜会变 坑还是原来的坑 萝卜已经不是原来的萝卜了 访问堆表 全表扫描 全表扫描用多快读的方式访问高水位线以下的所有数据块哪怕这些数据块里现在都没有数据哪怕最终只得到少量的记录就像在一本没有索引的字典里找出所有单人旁的字 你必须将整本字典从头到尾查阅一遍 访问堆表 ROWID访问 访问指定ROWID的记录最快的访问方式查看字典中第X页第Y行的字 访问堆表 STOP机制 查询到指定的记录数之后就停止扫描无需借助索引 也不必访问整张表的所有数据块在字典中找出5个笔画数为20的字 访问B树索引 典型结构 访问B树索引 特点 自索引的索引平衡树 每个叶节点到根节点的距离相等叶节点之间存在有序链表结构对 like a like a 操作效率差 访问B树索引 索引唯一扫描 访问B树索引 索引范围扫描 访问B树索引 索引快速全扫描 Selectcount fromtable1 访问B树索引 代价 索引唯一扫描 o 1 索引的层次数索引范围扫描 o n 所需要返回的叶块索引快速全扫描 o N 所有叶块 实际问题 1 问 在索引字段上施加函数 为什么性能差to char gmt create mmdd 0101 答 因为无法使用索引正确的写法 gmt createbetweento date 20090101 yyyymmdd andto date 20090102 yyyymmdd 用索引范围扫描 实际问题 2 问 在索引字段上用全模糊 为什么性能差member idlike alibaba 答 因为无法使用索引 正确的做法 B树索引无法解决这种需求 考虑用其他技术手段代替 比如搜索引擎 实际问题 3 问 索引是 member id group id 为什么这个性能差wheregroup id 89721答 用了索引快速全扫描或者其他低效的扫描方式 正确的做法 创建以group id为首列的索引 使用索引范围扫描 实际问题 4 问 计数为什么有时候count id 比count 慢 答 count id 等价于count whereidisnotnull 如果没有 id 索引 那么会用全表扫描 而count 会自动选择最优的索引用索引快速全扫描 正确的写法 计数统一使用count 实际问题 5 判断member id在offer表中是否存在记录 哪个写法性能好 A selectcount fromofferwheremember id member id B selectcount fromofferwheremember id member id andrownum 1 两种写法都用了索引范围扫描 B 在这个基础上还用到了STOP机制 查到第一笔记录之后就立刻停止了扫描 所以性能更好 实际问题 6 问 索引 member id subject 这两句性能有差别吗 A selectsubjectfromofferwheremember id member id B selectsubject gmt createfromofferwheremember id member id 答 两句都用了索引范围扫描 B 还需要根据索引中查到的ROWID访问表里的记录 当对应的记录数较多时 比 A 的性能差很多 实际问题 7 问 这个SQL如何优化 SELECT FROM SELECTt rownumASrnFROM SELECT FROMblog blog articleWHEREdomain id 1ANDdraft 0ORDERBYdomain id draft gmt createDESC tWHERErownum 3 实际问题 7 答 在索引内完成过滤 排序 分页 取得一页的ROWID 用它们再去跟原表做join SELECTt FROM SELECTrid rownumASrnFROM SELECTROWIDASridFROMblog blog articleWHEREdomain id 1ANDdraft 0ORDERBYdomain id draft gmt createDESC WHERErownum 3ANDa rid t ROWID 执行计划 提出问题 执行计划 设计思路 算法一 遍历新华字典 对其中的每个字都去语文课本里找对应的字 每个字的查找都是将整本课本遍历一遍 直到将课本中该课的生字全部找到 算法二 在课本的目录中找到该课 并翻到对应的页 针对该页中的这些生字依次到字典里去查 查的时候使用部首检索法 算法三 找个做好的同学作业来抄一下 执行计划 概念 执行计划是数据库完成一次查询的具体步骤 它用树型结构来描述了这些步骤的执行顺序 执行计划 如何查看执行计划 可以看看这些URL DBA团队blog 最贴近阿里巴巴业务场景ITPUB 国内人气最旺最权威的Oracle论坛 数据库与其他数据源 数据库Vs数据仓库数据库Vs搜索引擎数据库VsCache 数据库Vs数据仓库 概览 数据库 DB Database 擅长事务处理数据实时变化7 24小时支持网站面向网站所有会员数据仓库 DW DataWarehouse 擅长海量数据汇总运算静态存放历史数据夜间运算 白天提供简单查询大部分面向公司内部提供各种报表和数据提取逐渐向网站会员提供数据支持 数据库Vs数据仓库 相互关系 数据库Vs搜索引擎 概览 数据库数据的持久保存只适合精确查找 不适合模糊搜索暂时不支持线性扩展搜索引擎是数据库中数据的一个快照擅长模糊搜索 精于搜索分词 结果排序可以线性扩展 数据库Vs搜索引擎 关系 数据库VsCache 概览 数据库数据永久保存系统资源总量受到硬件和软件共同限制 暂时不可扩展Cache 缓存 数据临时保存响应速度快适合单一键值对的数据查询系统能力可通过增加硬件投入来敏捷扩展开发周期短 技术成熟稳定合理使用Cache可以