数据库如何优化

body{

line-height:200%;

}

如何优化MySQL数据库

当MySQL数据库邂逅优化，它有好几个意思，今天我们所指的是性能优化。

我们究竟该如何对MySQL数据库进行优化呢？下面我就从MySQL对硬件的选择、Mysql的安装、myf的优化、MySQL如何进行架构设计及数据切分等方面来说明这个问题。

1服务器物理硬件的优化

1)磁盘(I/O)，MySQL每一秒钟都在进行大量、复杂的查询 *** 作，对磁盘的读写量可想而知，所以推荐使用RAID10磁盘阵列，如果资金允许，可以选择固态硬盘做RAID10；

2)cpu对Mysql的影响也是不容忽视的，建议选择运算能力强悍的CPU。

2MySQL应该采用编译安装的方式

MySQL数据库的线上环境安装，我建议采取编译安装，这样性能会较大的提升。

3MySQL配置文件的优化

1)skip

-name

-resolve，禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间；

2)back_log

=

384，back_log指出在MySQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中，对于Linux系统而言，推荐设置小于512的整数。

3)如果key_reads太大，则应该把myf中key_buffer_size变大，保持key_reads/key_read_requests至少在1/100以上，越小越好。

4MySQL上线后根据status状态进行适当优化

1)打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响也会很小。

2)MySQL服务器过去的最大连接数是245，没有达到服务器连接数的上限256，应该不会出现1040错误。比较理想的设置是：Max_used_connections/max_connections

100%

=85%

5MySQL数据库的可扩展架构方案

1)MySQL

cluster，其特点为可用性非常高，性能非常好，但它的维护非常复杂，存在部分Bug;

2)DRBD磁盘网络镜像方案，其特点为软件功能强大，数据可在底层块设备级别跨物理主机镜像，且可根据性能和可靠性要求配置不同级别的同步。

说一下不同引擎的优化，myisam读的效果好，写的效率差，这和它数据存储格式，索引的指针和锁的策略有关的，它的数据是顺序存储的（innodb数据存储方式是聚簇索引），他的索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快，（innodb索引节点存的则是数据的主键，所以需要根据主键二次查找）；myisam锁是表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的，去设置concurrent_insert参数，定期执行表优化 *** 作，更新 *** 作就没有办法了）是串行的，所以写起来慢，并且默认的写优先级比读优先级高，高到写 *** 作来了后，可以马上插入到读 *** 作前面去，如果批量写，会导致读请求饿死，所以要设置读写优先级或设置多少写 *** 作后执行读 *** 作的策略;myisam不要使用查询时间太长的sql，如果策略使用不当，也会导致写饿死，所以尽量去拆分查询效率低的sql,

为了能最小化磁盘I/O MyISAM 存储引擎采用了很多数据库系统使用的一种策略 它采用一种机制将最经常访问的表保存在内存区块中

对索引区块来说 它维护着一个叫索引缓存(索引缓冲)的结构体 这个结构体中放著许多那些最常使用的索引区块的缓冲区块 对数据区块来说 MySQL没有使用特定的缓存 它依靠 *** 作系统的本地文件系统缓存

本章首先描述了 MyISAM 索引缓存的基本 *** 作 然后讨论在MySQL 中所做的改进 它提高了索引缓存性能 同时能更好地控制缓存 *** 作

线程之间不再是串行地访问索引缓存 多个线程可以并行地访问索引缓存 可以设置多个索引缓存 同时也能指定数据表索引到特定的缓存中

索引缓存机制对 ISAM 表同样适用 不过 这种有效性正在减弱 自从MySQL 开始 MyISAM 表类型引进之后 ISAM 就不再建议使用了 MySQL 更是延续了这个趋势 ISAM 类型默认被禁用了

可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小 如果这个值大小为 那么就不使用缓存 当这个值小得于不足以分配区块缓冲的最小数量( )时 也不会使用缓存

当索引缓存无法 *** 作时 索引文件就只通过 *** 作系统提供的本地文件系统缓冲来访问(换言之 表索引区块采用的访问策略和数据区块的一致)

一个索引区块在 MyISAM 索引文件中是一个连续访问的单元 通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的 这个树的底部时叶子节点 叶子节点之上则是非叶子节点)

在索引缓存结构中所有的区块大小都是一样的 这个值可能等于 大于 或小于表的索引区块大小 通常这两个值是不一样的

当必须访问来自任何表的索引区块时 服务器首先检查在索引缓存中是否有可用的缓冲区块 如果有 服务器就访问缓存中的数据 而非磁盘 就是说 它直接存取缓存 而不是存取磁盘 否则 服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块 将它的内容替换成请求表的索引区块的拷贝 一旦新的索引区块在缓存中了 索引数据就可以存取了

当发生被选中要替换的区块内容修改了的情况时 这个区块就被认为 脏 了 那么 在替换之前 它的内容就必须先刷新到它指向的标索引

通常服务器遵循LRU(最近最少使用)策略 当要选择替换的区块时 它选择最近最少使用的索引区块 为了想要让选择变得更容易 索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链) 当一个区块被访问了 就把它放到队列的最后位置 当区块要被替换时 在队列开始位置的区块就是最近最少使用的 它就是第一候选删除对象

共享访问索引缓存

在MySQL 以前 访问索引缓存是串行的 两个线程不能并行地访问索引缓存缓冲 服务器处理一个访问索引区块的请求只能等它之前的请求处理完 结果 新的请求所需的索引区块就不在任何索引缓存环冲区块中 因为其他线程把包含这个索引区块的缓冲给更新了

从MySQL 开始 服务器支持共享方式访问索引缓存

没有正在被更新的缓冲可以被多个线程访问

缓冲正被更新时 需要使用这个缓冲的线程只能等到更新完成之后

多个线程可以初始化需要替换缓存区块的请求 只要它们不干扰别的线程(也就是 它们请求不同的索引区块 因此不同的缓存区块被替换)

共享方式访问索引缓存令服务器明显改善了吞吐量

多重索引缓存

共享访问索引缓存改善了性能 却不能完全消除线程间的冲突 它们仍然争抢控制管理存取索引缓存缓冲的结构 为了更进一步减少索引缓存存取冲突 MySQL 提供了多重索引缓存特性 这能将不同的表索引指定到不同的索引缓存

当有多个索引缓存 服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个 默认地 所有的 MyISAM 表索引都缓存在默认的索引缓存中 想要指定到特定的缓存中 可以使用 CACHE INDEX 语句

如下语句所示 指定表的索 t t 和 t 引缓存到名为 hot_cache 的缓存中

mysql> CACHE INDEX t  t  t  IN hot_cache; + + + + + | Table | Op | Msg_type | Msg_text | + + + + + | test t  | assign_to_keycache | status | OK | | test t  | assign_to_keycache | status | OK | | test t  | assign_to_keycache | status | OK | + + + + +

注意 如果服务器编译支持存 ISAM 储引擎了 那么 ISAM 表也使用索引缓存机制 不过 ISAM 表索引只能使用默认的索引缓存而不能自定义

CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的 如下 mysql> SET GLOBAL keycache key_buffer_size= ;想要删除索引缓存 只需设置它的大小为 mysql> SET GLOBAL keycache key_buffer_size= ;索引缓存变量是一个结构体变量 由名字和组件构成 例如 keycache key_buffer_size keycache 就是缓存名 key_buffer_size 是缓存组件 默认地 表索引在服务器启动时指定到主(默认的)索引缓存中 当一个索引缓存被删掉后 指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去 对一个繁忙的系统来说 我们建议以下三条策略来使用索引缓存 热缓存占用 %的总缓存空间 用于繁重搜索但很少更新的表 冷缓存占用 %的总缓存空间 用于中等强度更新的表 如临时表 冷缓存占用 %的总缓存空间 作为默认的缓存 用于所有其他表 使用三个缓存的一个原因是好处在于 存取一个缓存结构时不会阻止对其他缓存的访问 访问一个表索引的查询不会跟指定到其他缓存的查询竞争 性能提高还表现在以下几点原因 热缓存只用于检索记录 因此它的内容总是不需要变化 所以 无论什么时候一个索引区块需要从磁盘中引入 被选中要替换的缓存区块的内容总是要先被刷新 索引被指向热缓存中后 如果没有需要扫描全部索引的查询 那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中 在临时表里必须频繁执行一个更新 *** 作是相当快的 如果要被更新的节点已经在缓存中了 它无需先从磁盘中读取出来 当临时表的索引大小和冷缓存大小一样时 那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的

中点插入策略

默认地 MySQL 的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块 不过它也支持更完善的方法 叫做 中点插入策略

使用中点插入策略时 LRU链就被分割成两半 一个热子链 一个温子链 两半分割的点不是固定的 不过缓存管理系统会注意不让温子链部分 太短 总是至少包括全部缓存区块的 key_cache_division_limit 比率 key_cache_division_limit 是缓存结构体变量的组件部分 因此它是每个缓存都可以设置这个参数值

当一个索引区块从表中读入缓存时 它首先放在温子链的末尾 当达到一定的点击率(访问这个区块)后 它就提升到热子链中去 目前 要提升一个区块的点击率( )对每个区块来说都是一样的 将来 我们会让点击率依靠B树中对应的索引区块节点的级别 包含非叶子节点的索引区块所要求的提升点击率就低一点 包含叶子节点的B索引树的区块的值就高点

提升起来的区块首先放在热子链的末尾 这个区块在热子链内一直循环 如果这个区块在该子链开头位置停留时间足够长了 它就会被降级回温子链 这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的

这个阀值是这么描述的 一个索引缓存包含了 N 个区块 热子链开头的区块在低于 Nkey_cache_age_threshold/ 次访问后就被移动到温子链的开头位置 它又首先成为被删除的候选对象 因为要被替换的区块还是从温子链的开头位置开始的

中点插入策略就能在缓存中总能保持更有价值的区块 如果更喜欢采用LRU策略 只需让 key_cache_division_limit 的值低于默认值

中点插入策略能帮助改善在执行需要有效扫描索引 它会将所有对应到B树中高级别的有价值的节点推出的查询时的性能 为了避免这样 就必须设定 key_cache_division_limit 远远低于 以采用中点插入策略 则在扫描索引 *** 作时那些有价值的频繁点击的节点就会保留在热子链中了

索引预载入

如果索引缓存中有足够的区块用来保存全部索引 或者至少足够保存全部非叶子节点 那么在使用前就载入索引缓存就很有意义了 将索引区块以十分有效的方法预载入索引缓存缓冲 从磁盘中顺序地读取索引区块

没有预载入 查询所需的索引区块仍然需要被放到缓存中去 虽然索引区块要保留在缓存中 因为有足够的缓冲 它们可以从磁盘中随机读取到 而非顺序地

想要预载入缓存 可以使用 LOAD INDEX INTO CACHE 语句 如下语句预载入了表 t 和 t 的索引节点(区块)

mysql> LOAD INDEX INTO CACHE t  t  IGNORE LEAVES; + + + + + | Table | Op | Msg_type | Msg_text | + + + + + | test t  | preload_keys | status | OK | | test t  | preload_keys | status | OK | + + + + +

增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块 因此 上述语句加载了 t 的全部索引区块 但是只加载 t 的非叶子节点区块

如果使用 CACHE INDEX 语句将索引指向一个索引缓存 将索引区块预先放到那个缓存中去 否则 索引区块只会加载到默认的缓存中去

索引缓存大小

MySQL 引进了对每个索引缓存的新变量 key_cache_block_size 这个变量可以指定每个索引缓存的区块大小 用它就可以来调整索引文件I/O *** 作的性能

当读缓冲的大小和本地 *** 作系统的I/O缓冲大小一样时 就达到了I/O *** 作的最高性能了 但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能 读比较大的叶子节点时 服务器会读进来很多不必要的数据 这大大阻碍了读其他叶子节点

目前 还不能控制数据表的索引区块大小 这个大小在服务器创建索引文件 ` MYI 时已经设定好了 它根据数据表的索引大小的定义而定 在很多时候 它设置成和I/O缓冲大小一样 在将来 可以改变它的值 并且会全面采用变量 key_cache_block_size

重建索引缓存

索引缓存可以通过修改其参数值在任何时候重建它 例如

mysql> SET GLOBAL cold_cache key_buffer_size= ;

如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样 服务器就会清空原来的缓存 在新的变量值基础上重建缓存 如果缓存中有任何的 脏 索引块 服务器会先把它们保存起来然后才重建缓存 重新设定其他的索引缓存变量并不会重建缓存

lishixinzhi/Article/program/Oracle/201311/16615

一、概述

随着数据库在各个领域的使用不断增长，越来越多的应用提出了高性能的要求。数据库性能调优是知识密集型的学科，需要综合考虑各种复杂的因素：数据库缓冲区的大小、索引的创建、语句改写等等。总之，数据库性能调优的目的在于使系统运行得更快。

调优需要有广泛的知识，这使得它既简单又复杂。

说调优简单，是因为调优者不必纠缠于复杂的公式和规则。许多学术界和业界的研究者都在尝试将调优和查询处理建立在数学基础之上。

称调优复杂，是因为如果要完全理解常识所依赖的原理，还需要对应用、数据库管理系统、 *** 作系统以及硬件有广泛而深刻的理解。

数据库调优技术可以在不同的数据库系统中使用。如果需要调优数据库系统，最好掌握如下知识：1)查询处理、并发控制以及数据库恢复的知识；2)一些调优的基本原则。

这里主要描述索引调优。

二、索引调优

索引是建立在表上的一种数据组织，它能提高访问表中一条或多条记录的特定查询效率。因此，适当的索引调优是很重要的。

对于索引调优存在如下的几个误区：

误区1：索引创建得越多越好

实际上：创建的索引可能建立后从来未使用。索引的创建也是需要代价的，对于删除、某些更新、插入 *** 作，对于每个索引都要进行相应的删除、更新、插入 *** 作。从而导致删除、某些更新、插入 *** 作的效率变低。

误区2：对于一个单表的查询，可以索引1进行过滤再使用索引2进行过滤

实际上：假设查询语句如下select from t1 where c1=1 and c2=2，c1列和c2列上分别建有索引ic1、ic2。先使用ic1(或ic2)进行过滤，产生的结果集是临时数据，不再具有索引，所以不可使用ic2(或ic1)进行再次过滤。

索引优化的基本原则：

1、将索引和数据存放到不同的文件组

没有将表数据和索引数据存储到不同的文件组，而不加区别地将它们存储到同一文件组。这样，不但会造成I/O竞争，也为数据库的维护工作带来不变。

2、组合索引的使用

假设存在组合索引it1c1c2(c1,c2)，查询语句select from t1 where c1=1 and c2=2能够使用该索引。查询语句select from t1 where c1=1也能够使用该索引。但是，查询语句select from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

根据where条件的不同，归纳如下：

1) c1=1 and c2=2：使用索引it1c1c2进行等值查找。

2) c1=1 and c2>2：使用索引it1c1c2进行范围查找，可以有两种方法。

方法1，使用通过索引键(1,2)在B树中命中一条记录，然后向后扫描找出 第一条符合条件的记录，从此记录往后的每一条记录都是符合条件的。这种方法的弊端在于：如果c1=1 and c2=2对应的记录数很多，会产生很多无效的扫描。

方法2，如果c2对应的int型数据，可以使用索引键(1,3)在B树中命中一条记录，从此记录往后的每一条记录都是符合条件的。

本文中的例子均采用方法1。

3)c1>1 and c2=2：因为索引的第一个列不是等于号的，索引即使后面出现了c2=2，也不能将c2=2应用于索引查找。这里，通过索引键(1,- ∞)在B树中命中一条记录，向后扫描找出第一条符合c1>1的记录，此后的每一条记录判断是否符合c2=2，如果符合则输出，否则过滤掉。这里我们称c2=2没有参与到索引运算中去。这种情况在实际应用中经常出现。

4)c1>1：通过索引键(1,- ∞) 在B树中命中一条记录，以此向后扫描找出第一条符合c1>1的记录，此后的每条记录都是符合条件的。

3、唯一索引与非唯一索引的差异

假设索引int1c1(c1)是唯一索引，对于查询语句select c1 from t1 where c1=1，达梦数据库使用索引键(1)命中B树中一条记录，命中之后直接返回该记录(因为是唯一索引，所以最多只能有一条c1=1的记录)。

假设索引it1c2(c2)是非唯一索引，对于查询语句select c2 from t2 where c2=2，达梦数据库使用索引键(2)命中B树中一条记录，返回该记录，并继续向后扫描，如果该记录是满足c=2，返回该记录，继续扫描，直到遇到第一条不符合条件c2=2的记录。

于是，我们可以得知，对于不存在重复值的列，创建唯一索引优于创建非唯一索引。

4、非聚集索引的作用

每张表只可能一个聚集索引，聚集索引用来组织真实数据。语句“create table employee (id int cluster primary key,name varchar(20),addr varchar(20))”。表employee的数据用id来组织。如果要查找id=1000的员工记录，只要用索引键(1000)命中该聚集索引。但是，对于要查找name=’张三’的员工记录就不能使用该索引了，需要进行全表扫描，对于每一条记录判断是否满足name=’张三’，这样会导致查询效率非常低。

要使用聚集索引，必需提供id，我们只能提供name，于是需要引入一个辅助结构实现name到id的转换，这就是非聚集索引的作用。该非聚集索引的键是name，值是id。于是语句“select from employee where name=’张三’”的执行流程是：通过键(’张三’)命中非聚集索引，得到对应的id值3(假设’张三’对应的id为3)，然后用键(3)命中聚集索引，得到相应的记录。

5、是不是使用非聚集索引的查询都需要进行聚集的查询

不是的，虽然在上一点中查询转换为聚集索引的查找，有时候可以只需要使用非聚集索引。

创建表并创建相应的索引：create table t1(c1 int,c2 int,c3 int);create index it1c2c3 on t1(c2,c3)。查询语句为：select c3 from t1 where c2=1。

因为索引it1c2c3(c2,c3)覆盖查询语句中的列(c2,c3)。所以，该查询语句的执行流程为：通过索引键(1,- ∞)命中索引it1c2c3，对于该记录直接返回c3对应的值，继续向后扫描，如果索引记录中c1还是等于1，那么输出c3，以此类推，直到出现第一条c1不等于1的索引记录,结束查询。

6、创建索引的规则

创建索引首先要考虑的是列的可选择性。比较一下列中唯一键的数量和表中记录的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行数”的比值越接近于1，则该列的可选择行越高。在可选择性高的列上进行查询，返回的数据就较少，比较适合索引查询。相反，比如性别列上只有两个值，可选择行就很小，不适合索引查询。

本文首先讨论了基于第三范式的数据库表的基本设计，着重论述了建立主键和索引的策略和方案，然后从数据库表的扩展设计和库表对象的放置等角度概述了数据库管理系统的优化方案。

关键词： 优化（Optimizing） 第三范式（3NF） 冗余数据（Redundant Data） 索引（Index） 数据分割（Data Partitioning） 对象放置（Object Placement）

1 引言

数据库优化的目标无非是避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争。为了便于读者阅读和理解，笔者参阅了Sybase、Informix和Oracle等大型数据库系统参考资料，基于多年的工程实践经验，从基本表设计、扩展设计和数据库表对象放置等角度进行讨论，着重讨论了如何避免磁盘I/O瓶颈和减少资源竞争，相信读者会一目了然。

2 基于第三范式的基本表设计

在基于表驱动的信息管理系统（MIS）中，基本表的设计规范是第三范式（3NF）。第三范式的基本特征是非主键属性只依赖于主键属性。基于第三范式的数据库表设计具有很多优点：一是消除了冗余数据，节省了磁盘存储空间；二是有良好的数据完整性限制，即基于主外键的参照完整限制和基于主键的实体完整性限制，这使得数据容易维护，也容易移植和更新；三是数据的可逆性好，在做连接（Join）查询或者合并表时不遗漏、也不重复；四是因消除了冗余数据（冗余列），在查询（Select）时每个数据页存的数据行就多，这样就有效地减少了逻辑I/O，每个Cash存的页面就多，也减少物理I/O；五是对大多数事务(Transaction)而言，运行性能好；六是物理设计(Physical Design)的机动性较大，能满足日益增长的用户需求。

在基本表设计中，表的主键、外键、索引设计占有非常重要的地位，但系统设计人员往往只注重于满足用户要求，而没有从系统优化的高度来认识和重视它们。实际上，它们与系统的运行性能密切相关。现在从系统数据库优化角度讨论这些基本概念及其重要意义：

（1）主键(Primary Key)：主键被用于复杂的SQL语句时，频繁地在数据访问中被用到。一个表只有一个主键。主键应该有固定值（不能为Null或缺省值，要有相对稳定性），不含代码信息，易访问。把常用（众所周知）的列作为主键才有意义。短主键最佳（小于25bytes），主键的长短影响索引的大小，索引的大小影响索引页的大小，从而影响磁盘I/O。主键分为自然主键和人为主键。自然主键由实体的属性构成，自然主键可以是复合性的，在形成复合主键时，主键列不能太多，复合主键使得Join作复杂化、也增加了外键表的大小。人为主键是，在没有合适的自然属性键、或自然属性复杂或灵敏度高时，人为形成的。人为主键一般是整型值（满足最小化要求），没有实际意义，也略微增加了表的大小；但减少了把它作为外键的表的大小。

（2）外键（Foreign Key）：外键的作用是建立关系型数据库中表之间的关系（参照完整性），主键只能从独立的实体迁移到非独立的实体，成为后者的一个属性，被称为外键。

（3）索引(Index)：利用索引优化系统性能是显而易见的，对所有常用于查询中的Where子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样减少数据存取时间；利用索引可以优化或排除耗时的分类作；把数据分散到不同的页面上，就分散了插入的数据；主键自动建立了唯一索引，因此唯一索引也能确保数据的唯一性（即实体完整性）；索引码越小，定位就越直接；新建的索引效能最好，因此定期更新索引非常必要。索引也有代价：有空间开销，建立它也要花费时间，在进行Insert、Delete和Update作时，也有维护代价。索引有两种：聚族索引和非聚族索引。一个表只能有一个聚族索引，可有多个非聚族索引。使用聚族索引查询数据要比使用非聚族索引快。在建索引前，应利用数据库系统函数估算索引的大小。

① 聚族索引（Clustered Index）：聚族索引的数据页按物理有序储存，占用空间小。选择策略是，被用于Where子句的列：包括范围查询、模糊查询或高度重复的列（连续磁盘扫描）；被用于连接Join作的列；被用于Order by和Group by子句的列。聚族索引不利于插入作，另外没有必要用主键建聚族索引。

② 非聚族索引（Nonclustered Index）：与聚族索引相比，占用空间大，而且效率低。选择策略是，被用于Where子句的列：包括范围查询、模糊查询（在没有聚族索引时）、主键或外键列、点（指针类）或小范围（返回的结果域小于整表数据的20%）查询；被用于连接Join作的列、主键列（范围查询）；被用于Order by和Group by子句的列；需要被覆盖的列。对只读表建多个非聚族索引有利。索引也有其弊端，一是创建索引要耗费时间，二是索引要占有大量磁盘空间，三是增加了维护代价（在修改带索引的数据列时索引会减缓修改速度）。那么，在哪种情况下不建索引呢？对于小表（数据小于5页）、小到中表（不直接访问单行数据或结果集不用排序）、单值域（返回值密集）、索引列值太长（大于20bitys）、容易变化的列、高度重复的列、Null值列，对没有被用于Where子语句和Join查询的列都不能建索引。另外，对主要用于数据录入的，尽可能少建索引。当然，也要防止建立无效索引，当Where语句中多于5个条件时，维护索引的开销大于索引的效益，这时，建立临时表存储有关数据更有效。

批量导入数据时的注意事项：在实际应用中，大批量的计算（如电信话单计费）用C语言程序做，这种基于主外键关系数据计算而得的批量数据（文本文件），可利用系统的自身功能函数（如Sybase的BCP命令）快速批量导入，在导入数据库表时，可先删除相应库表的索引，这有利于加快导入速度，减少导入时间。在导入后再重建索引以便优化查询。

（4）锁：锁是并行处理的重要机制，能保持数据并发的一致性，即按事务进行处理；系统利用锁，保证数据完整性。因此，我们避免不了死锁，但在设计时可以充分考虑如何避免长事务，减少排它锁时间，减少在事务中与用户的交互，杜绝让用户控制事务的长短；要避免批量数据同时执行，尤其是耗时并用到相同的数据表。锁的征用：一个表同时只能有一个排它锁，一个用户用时，其它用户在等待。若用户数增加，则Server的性能下降，出现“假死”现象。如何避免死锁呢？从页级锁到行级锁，减少了锁征用；给小表增加无效记录，从页级锁到行级锁没有影响，若在同一页内竞争有影响，可选择合适的聚族索引把数据分配到不同的页面；创建冗余表；保持事务简短；同一批处理应该没有网络交互。

（5）查询优化规则：在访问数据库表的数据(Access Data)时，要尽可能避免排序（Sort）、连接(Join)和相关子查询作。经验告诉我们，在优化查询时，必须做到：

① 尽可能少的行；

② 避免排序或为尽可能少的行排序，若要做大量数据排序，最好将相关数据放在临时表中作；用简单的键（列）排序，如整型或短字符串排序；

③ 避免表内的相关子查询；

④ 避免在Where子句中使用复杂的表达式或非起始的子字符串、用长字符串连接；

⑤ 在Where子句中多使用“与”（And）连接，少使用“或”(Or)连接；

⑥ 利用临时数据库。在查询多表、有多个连接、查询复杂、数据要过滤时，可以建临时表（索引）以减少I/O。但缺点是增加了空间开销。

除非每个列都有索引支持，否则在有连接的查询时分别找出两个动态索引，放在工作表中重新排序。

3 基本表扩展设计

基于第三范式设计的库表虽然有其优越性（见本文第一部分），然而在实际应用中有时不利于系统运行性能的优化：如需要部分数据时而要扫描整表，许多过程同时竞争同一数据，反复用相同行计算相同的结果，过程从多表获取数据时引发大量的连接作，当数据来源于多表时的连接作；这都消耗了磁盘I/O和CPU时间。

尤其在遇到下列情形时，我们要对基本表进行扩展设计：许多过程要频繁访问一个表、子集数据访问、重复计算和冗余数据，有时用户要求一些过程优先或低的响应时间。

如何避免这些不利因素呢？根据访问的频繁程度对相关表进行分割处理、存储冗余数据、存储衍生列、合并相关表处理，这些都是克服这些不利因素和优化系统运行的有效途径。

31 分割表或储存冗余数据

分割表分为水平分割表和垂直分割表两种。分割表增加了维护数据完整性的代价。

水平分割表：一种是当多个过程频繁访问数据表的不同行时，水平分割表，并消除新表中的冗余数据列；若个别过程要访问整个数据，则要用连接作，这也无妨分割表；典型案例是电信话单按月分割存放。另一种是当主要过程要重复访问部分行时，最好将被重复访问的这些行单独形成子集表（冗余储存），这在不考虑磁盘空间开销时显得十分重要；但在分割表以后，增加了维护难度，要用触发器立即更新、或存储过程或应用代码批量更新，这也会增加额外的磁盘I/O开销。

垂直分割表（不破坏第三范式），一种是当多个过程频繁访问表的不同列时，可将表垂直分成几个表，减少磁盘I/O（每行的数据列少，每页存的数据行就多，相应占用的页就少），更新时不必考虑锁，没有冗余数据。缺点是要在插入或删除数据时要考虑数据的完整性，用存储过程维护。另一种是当主要过程反复访问部分列时，最好将这部分被频繁访问的列数据单独存为一个子集表（冗余储存），这在不考虑磁盘空间开销时显得十分重要；但这增加了重叠列的维护难度，要用触发器立即更新、或存储过程或应用代码批量更新，这也会增加额外的磁盘I/O开销。垂直分割表可以达到最大化利用Cache的目的。

总之，为主要过程分割表的方法适用于：各个过程需要表的不联结的子集，各个过程需要表的子集，访问频率高的主要过程不需要整表。在主要的、频繁访问的主表需要表的子集而其它主要频繁访问的过程需要整表时则产生冗余子集表。

注意，在分割表以后，要考虑重新建立索引。

32 存储衍生数据

对一些要做大量重复性计算的过程而言，若重复计算过程得到的结果相同（源列数据稳定，因此计算结果也不变），或计算牵扯多行数据需额外的磁盘I/O开销，或计算复杂需要大量的CPU时间，就考虑存储计算结果（冗余储存）。现予以分类说明：

若在一行内重复计算，就在表内增加列存储结果。但若参与计算的列被更新时，必须要用触发器更新这个新列。

若对表按类进行重复计算，就增加新表（一般而言，存放类和结果两列就可以了）存储相关结果。但若参与计算的列被更新时，就必须要用触发器立即更新、或存储过程或应用代码批量更新这个新表。

若对多行进行重复性计算（如排名次），就在表内增加列存储结果。但若参与计算的列被更新时，必须要用触发器或存储过程更新这个新列。

总之，存储冗余数据有利于加快访问速度；但违反了第三范式，这会增加维护数据完整性的代价，必须用触发器立即更新、或存储过程或应用代码批量更新，以维护数据的完整性。

33 消除昂贵结合

对于频繁同时访问多表的一些主要过程，考虑在主表内存储冗余数据，即存储冗余列或衍生列（它不依赖于主键），但破坏了第三范式，也增加了维护难度。在源表的相关列发生变化时，必须要用触发器或存储过程更新这个冗余列。当主要过程总同时访问两个表时可以合并表，这样可以减少磁盘I/O作，但破坏了第三范式，也增加了维护难度。对父子表和1：1关系表合并方法不同：合并父子表后，产生冗余表；合并1：1关系表后，在表内产生冗余数据。

4 数据库对象的放置策略

数据库对象的放置策略是均匀地把数据分布在系统的磁盘中，平衡I/O访问，避免I/O瓶颈。

⑴ 访问分散到不同的磁盘，即使用户数据尽可能跨越多个设备，多个I/O运转，避免I/O竞争，克服访问瓶颈；分别放置随机访问和连续访问数据。

⑵ 分离系统数据库I/O和应用数据库I/O。把系统审计表和临时库表放在不忙的磁盘上。

⑶ 把事务日志放在单独的磁盘上，减少磁盘I/O开销，这还有利于在障碍后恢复，提高了系统的安全性。

⑷ 把频繁访问的“活性”表放在不同的磁盘上；把频繁用的表、频繁做Join作的表分别放在单独的磁盘上，甚至把把频繁访问的表的字段放在不同的磁盘上，把访问分散到不同的磁盘上，避免I/O争夺；

⑸ 利用段分离频繁访问的表及其索引（非聚族的）、分离文本和图像数据。段的目的是平衡I/O，避免瓶颈，增加吞吐量，实现并行扫描，提高并发度，最大化磁盘的吞吐量。利用逻辑段功能，分别放置“活性”表及其非聚族索引以平衡I/O。当然最好利用系统的默认段。另外，利用段可以使备份和恢复数据更加灵活，使系统授权更加灵活。

设计数据库的优化措施。这要看你对预期的数据量的一个估计，不同的数据量有不同的策略。100万数据的表和1亿的数据表的策略肯定是不一样的。同样的设计，查询语句不一样，效果可能也不一样。

比较常用的数据库设计方面的处理措施是，

1、索引的建立，一张表，如果有一些经常查询的字段上，要建立索引。比如库存表，你会经常按厂家查询，那么在厂家这个字段上就要建立索引。如楼上所说，在某些时刻，要采取违反第3范式的一些数据库设计手段。

2、分库，分表技术。可以按业务层次，或者日期、厂家、地区等字段，对表进行横向或纵向的分割。把事务表和数据仓库表分开等。

3、事实上，对于系统的优化，从数据库本身的优化，数据库表的设计，以及应用程序的设计上，关联是很密切的。比如在数据库，可以把临时表，或者一些日志类的表放在内存盘中。在程序设计上，采用缓存机制，分布式数据库机制等等，都是提高系统响应能力的方法。

以上就是关于数据库如何优化全部的内容，包括:数据库如何优化、为什么要用mysql索引优化数据库、MySQL数据库优化（七）等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！