《sql优化.pptx

面向程序员的数据库访问性能优化法则一、数据库访问优化法则简介要正确的优化SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是2Mbps，硬盘是每分钟7200转等等。因此，为了快速找到SQL的性能瓶颈点，我们也需要了解我们计算机系统的硬件基本性能指标，下图展示的当前主流计算机性能指标数据。从上图可以看出，计算机系统硬件性能从高到代依次为：CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘由于SSD硬盘还处于快速发展阶段，所以本文的内容不涉及SSD相关应用系统。根据数据库知识，我们可以列出每种硬件主要的工作内容：CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；网络：结果数据传输、SQL请求、远程数据库访问（dblink）；硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。数据库访问优化法则数据库访问优化法则这个优化法则归纳为5个层次：1、? 减少数据访问（减少磁盘访问）2、? 返回更少数据（减少网络传输或磁盘访问）3、? 减少交互次数（减少网络传输）4、? 减少服务器CPU开销（减少CPU及内存开销）5、? 利用更多资源（增加资源）数据库访问优化法则由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。以下是每个优化法则层级对应优化效果及成本经验参考：三、数据库访问优化法则详解1、减少数据访问1.1、创建并使用正确的索引数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人很少，即使是专业的DBA也不一定能完全做到最优。 索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。 索引常见问题： 索引有哪些种类？ 常见的索引有B-TREE索引、位图索引、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少，这里不深入介绍。B-TREE索引包括很多扩展类型，如组合索引、反向索引、函数索引等等，以下是B-TREE索引的简单介绍： B-TREE索引也称为平衡树索引(Balance Tree)，它是一种按字段排好序的树形目录结构，主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。 叶子节点内容：索引字段内容+表记录ROWID 根节点，分支节点内容：当一个数据块中不能放下所有索引字段数据时，就会形成树形的根节点或分支节点，根节点与分支节点保存了索引树的顺序及各层级间的引用关系。BTREE索引结构示意图如下所示：图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首+笔划的组合索引。一个表中可以建多个索引，就如一本字典可以建多个目录一样（按拼音、笔划、部首等等）。一个索引也可以由多个字段组成，称为组合索引，如上图就是一个按部首+笔划的组合目录。SQL什么条件会使用索引？（重点）当字段上建有索引时，通常以下情况会使用索引：INDEX_COLUMN = ?INDEX_COLUMN ?INDEX_COLUMN = ?INDEX_COLUMN ?INDEX_COLUMN = ?INDEX_COLUMN between ? and ?INDEX_COLUMN in (?,?,...,?)INDEX_COLUMN like ?||‘%’（后导模糊查询）（oracle会，mysql不会）T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）SQL什么条件不会使用索引？（重点）查询条件不能使用索引原因INDEX_COLUMN ? INDEX_COLUMN ！= ? INDEX_COLUMN not in (?,?,...,?)不等于操作不能使用索引function(INDEX_COLUMN) = ?INDEX_COLUMN + 1 = ?INDEX_COLUMN || a = ?经过普通运算或函数运算后的索引字段不能使用索引 INDEX_COLUMN like %||?INDEX_COLUMN like %||?||%含前导模糊查询的Like语法不能使用索