程序员的数据库性能优化.pptxVIP

;引 言;;;各种硬件的主要工作内容;数据库访问优化漏斗法则;;数据块(Block) 数据块是数据库中数据在磁盘中存储的最小单位，也是一次IO访问的最小单位，一个数据块通常可以存储多条记录，数据块大小是DBA在创建数据库或表空间时指定，可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构，一个数据库可以包括多个数据文件，一个数据文件内又包含多个数据块。;ROWID ROWID是每条记录在数据库中的唯一标识，通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号，如下图所示。;;创建并使用正确的索引 数据库索引的原理比较简单，但在复杂的表中真正能正确使用索引的人很少，即使是专业的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 ?||%（后导模糊查询） T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）;INDEX_COLUMN ?INDEX_COLUMN not in (?,?,...,?);我们一般在什么字段上建索引？ 这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件： 1、字段出现在查询条件中，并且查询条件可以使用索引； 2、语句执行频率高，一天会有几千次以上； 3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？ 这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估： 小表(记录数小于10000行的表)：筛选比例10%； 大表：(筛选返回记录数)(表总记录数*单条记录长度)/10000/16 单条记录长度≈字段平均内容长度之和+字段数*2;;只通过索引访问数据 有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。 如：select id,name from company where type=2; 如果这个SQL经常使用，我们可以在type,id,name上创建组合索引 create index my_comb_index on company(type,id,name); 有了这个组合索引后，SQL就可以直接通过my_comb_index索引返回数据，不需要访问company表。 还是拿字典举例：有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果。如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页，拼音目录有20页，那我们的数据访问成本相当于全表访问的50分之一。 切记，性能优化是无止境的，当性能可以满足需求时即可，不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里，所以这种只通过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询。;目前market工程采用的是rownum分页 SEL