数据库性能优化

MySQL基础表和数据

数据库访问优化法则

了解计算机系统的硬件基本性能指标，可以快速找到SQL的性能瓶颈点，下面是当前主流计算机性能指标数据。

从图上可以看到基本上每种设备都有两个指标：

延时（响应时间）：表示硬件的突发处理能力；

带宽（吞吐量）：代表硬件持续处理能力。

从上图可以看出，计算机系统硬件性能从高到代依次为：

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

根据数据库知识，我们可以列出每种硬件主要的工作内容：

CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；

网络：结果数据传输、SQL请求、远程数据库访问（dblink）；

硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。

根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下图所示的性能基本优化法则：

由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。

以下是5个层次的优化法则对应优化效果及成本经验参考：

优化法则	做法	性能提升效果	优化成本
减少数据访问	减少磁盘访问	1~1000	低
返回更少数据	减少网络传输或磁盘访问	1~100	低
减少交互次数	减少网络传输	1~20	低
减少服务器CPU开销	减少CPU及内存开销	1~5	低
利用更多资源	增加资源	@~10	高

 

DML数据操作语言(insert delete update select)
DTL数据事务语言(commit rollback savepoint)
DDL数据定义语言(create alter drop..)
DCL数据控制语言(grant revoke)

数据库性能优化的点很多，这里描述的是常用的一些对数据库性能有所提升的点：
1、数据库设计
2、SQL语句优化
3、数据库参数配置
4、恰当的硬件资源和操作系统
5、使用适当的存储过程 [模块化编程，可以提升性能]

数据库设计

表设计优化

（合理的表名，合理的字段名(参考 数据库表、字段设计）

数据库索引

参考 数据库索引

选择合适的存储引擎

参考 存储引擎

业务库分库、分表技术（水平分割、垂直分割）

SQL语句优化

查询SQL优化

参考 查询SQL优化

1、数据分页处理

一般数据分页方式有：

1.1、客户端(应用程序或浏览器)分页

将数据从应用服务器全部下载到本地应用程序或浏览器，在应用程序或浏览器内部通过本地代码进行分页处理

优点：编码简单，减少客户端与应用服务器网络交互次数

缺点：首次交互时间长，占用客户端内存

适应场景：客户端与应用服务器网络延时较大，但要求后续操作流畅，如手机GPRS，超远程访问（跨国）等等。

1.2、应用服务器分页

将数据从数据库服务器全部下载到应用服务器，在应用服务器内部再进行数据筛选。以下是一个应用服务器端Java程序分页的示例：

List list=executeQuery(“select * from employee order by id”);

Int count= list.size();

List subList= list.subList(10, 20);

优点：编码简单，只需要一次SQL交互，总数据与分页数据差不多时性能较好。

缺点：总数据量较多时性能较差。

适应场景：数据库系统不支持分页处理，数据量较小并且可控。

1.3、数据库SQL分页

采用数据库SQL分页需要两次SQL完成

一个SQL计算总数量

一个SQL返回分页后的数据

优点：性能好

缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。

oracle数据库一般采用rownum来进行分页，常用分页语法有如下两种：

直接通过rownum分页：

select * from (

select a.*,rownum rn from

(select * from product a where company_id=? order by status) a

where rownum<=20)

where rn>10;

数据访问开销=索引IO+索引全部记录结果对应的表数据IO

采用rowid分页语法

优化原理是通过纯索引找出分页记录的ROWID，再通过ROWID回表返回数据，要求内层查询和排序字段全在索引里。

create index myindex on product(company_id,status);

select b.* from (

select * from (

select a.*,rownum rn from

(select rowid rid,status from product a where company_id=? order by status) a

where rownum<=20)

where rn>10) a, product b

where a.rid=b.rowid;

数据访问开销=索引IO+索引分页结果对应的表数据IO

实例：

一个公司产品有1000条记录，要分页取其中20个产品，假设访问公司索引需要50个IO，2条记录需要1个表数据IO。

那么按第一种ROWNUM分页写法，需要550(50+1000/2)个IO，按第二种ROWID分页写法，只需要60个IO(50+20/2);

2、宁可集中批量操作，避免频繁读写【减少交互次数】

2.1、batch DML

数据库访问框架一般都提供了批量提交的接口，jdbc支持batch的提交处理方法，当你一次性要往一个表中插入1000万条数据时，如果采用普通的executeUpdate处理，那么和服务器交互次数为1000万次，按每秒钟可以向数据库服务器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000条提交一次，那么和服务器交互次数为1万次，交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。

假设要向一个普通表插入1000万数据，每条记录大小为1K字节，表上没有任何索引，客户端与数据库服务器网络是100Mbps，以下是根据现在一般计算机能力估算的各种batch大小性能对比值：

单位：ms	No batch	Batch=10	Batch=100	Batch=1000	Batch=10000
服务器事务处理时间	0.1	0.1	0.1	0.1	0.1
服务器IO处理时间	0.02	0.2	2	20	200
网络交互发起时间	0.1	0.1	0.1	0.1	0.1
网络数据传输时间	0.01	0.1	1	10	100
小计	0.23	0.5	3.2	30.2	300.2
平均每条记录处理时间	0.23	0.05	0.032	0.0302	0.03002

从上可以看出，Insert操作加大Batch可以对性能提高近8倍性能，一般根据主键的Update或Delete操作也可能提高2-3倍性能，但不如Insert明显，因为Update及Delete操作可能有比较大的开销在物理IO访问。以上仅是理论计算值，实际情况需要根据具体环境测量。

2.2、In List

很多时候我们需要按一些ID查询数据库记录，我们可以采用一个ID一个请求发给数据库，如下所示：

for :var in ids[] do begin

select * from mytable where id=:var;

end;

我们也可以做一个小的优化， 如下所示，用ID INLIST的这种方式写SQL：

select * from mytable where id in(:id1,id2,...,idn);

通过这样处理可以大大减少SQL请求的数量，从而提高性能。那如果有10000个ID，那是不是全部放在一条SQL里处理呢？答案肯定是否定的。首先大部份数据库都会有SQL长度和IN里个数的限制，如ORACLE的IN里就不允许超过1000个值。

另外当前数据库一般都是采用基于成本的优化规则，当IN数量达到一定值时有可能改变SQL执行计划，从索引访问变成全表访问，这将使性能急剧变化。随着SQL中IN的里面的值个数增加，SQL的执行计划会更复杂，占用的内存将会变大，这将会增加服务器CPU及内存成本。

评估在IN里面一次放多少个值还需要考虑应用服务器本地内存的开销，有并发访问时要计算本地数据使用周期内的并发上限，否则可能会导致内存溢出。

综合考虑，一般IN里面的值个数超过20个以后性能基本没什么太大变化，也特别说明不要超过100，超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本，这个需要专业DBA评估。

2.3、设置Fetch Size

当我们采用select从数据库查询数据时，数据默认并不是一条一条返回给客户端的，也不是一次全部返回客户端的，而是根据客户端fetch_size参数处理，每次只返回fetch_size条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时，可以加大fetch_size，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能。

以下是jdbc测试的代码，采用本地数据库，表缓存在数据库CACHE中，因此没有网络连接及磁盘IO开销，客户端只遍历游标，不做任何处理，这样更能体现fetch参数的影响：

String vsql ="select * from t_employee";

PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

pstmt.setFetchSize(1000);

ResultSet rs = pstmt.executeQuery(vsql);

int cnt = rs.getMetaData().getColumnCount();

Object o;

while (rs.next()) {

for (int i = 1; i <= cnt; i++) {

o = rs.getObject(i);

}

}

测试示例中的employee表有100000条记录，每条记录平均长度135字节

以下是测试结果，对每种fetchsize测试5次再取平均值：

fetchsize	elapse_time（s）
1	20.516
2	11.34
4	6.894
8	4.65
16	3.584
32	2.865
64	2.656
128	2.44
256	2.765
512	3.075
1024	2.862
2048	2.722
4096	2.681
8192	2.715

Oracle jdbc fetchsize默认值为10，由上测试可以看出fetchsize对性能影响还是比较大的，但是当fetchsize大于100时就基本上没有影响了。fetchsize并不会存在一个最优的固定值，因为整体性能与记录集大小及硬件平台有关。根据测试结果建议当一次性要取大量数据时这个值设置为100左右，不要小于40。注意，fetchsize不能设置太大，如果一次取出的数据大于JVM的内存会导致内存溢出，所以建议不要超过1000，太大了也没什么性能提高，反而可能会增加内存溢出的危险。

注：图中fetchsize在128以后会有一些小的波动，这并不是测试误差，而是由于resultset填充到具体对像时间不同的原因，由于resultset已经到本地内存里了，所以估计是由于CPU的L1,L2 Cache命中率变化造成，由于变化不大，所以笔者也未深入分析原因。

iBatis的SqlMapping配置文件可以对每个SQL语句指定fetchsize大小，如下所示：

<select id="getAllProduct" resultMap="HashMap" fetchSize="1000">

select * from employee

</select>

2.4、使用存储过程

大型数据库一般都支持存储过程，合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中，但是又不可能一条SQL完成，这时你需要如下3步操作：

a：将A表数据全部取出到客户端；

b：计算出要更新的数据；

c：将计算结果更新到B表。

如果采用存储过程你可以将整个业务逻辑封装在存储过程里，然后在客户端直接调用存储过程处理，这样可以减少网络交互的成本。

当然，存储过程也并不是十全十美，存储过程有以下缺点：

a、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。

b、学习成本高，DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。

c、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。

d、存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。

e、如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。

f、为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于java的class文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。

个人观点：普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。

2.5、优化业务逻辑

要通过优化业务逻辑来提高性能是比较困难的，这需要程序员对所访问的数据及业务流程非常清楚。

举一个案例：

某移动公司推出优惠套参，活动对像为VIP会员并且2010年1，2，3月平均话费20元以上的客户。

那我们的检测逻辑为：

select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';

select vip_flag from member where phone_no='13988888888';

if avg_money>20 and vip_flag=true then

begin

执行套参();

end;

如果我们修改业务逻辑为:

select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';

if avg_money>20 then

begin

select vip_flag from member where phone_no='13988888888';

if vip_flag=true then

begin

执行套参();

end;

end;

通过这样可以减少一些判断vip_flag的开销，平均话费20元以下的用户就不需要再检测是否VIP了。

如果程序员分析业务，VIP会员比例为1%，平均话费20元以上的用户比例为90%，那我们改成如下：

select vip_flag from member where phone_no='13988888888';

if vip_flag=true then

begin

select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';

if avg_money>20 then

begin

执行套参();

end;

end;

这样就只有1%的VIP会员才会做检测平均话费，最终大大减少了SQL的交互次数。

以上只是一个简单的示例，实际的业务总是比这复杂得多，所以一般只是高级程序员更容易做出优化的逻辑，但是我们需要有这样一种成本优化的意识。

2.6、使用ResultSet游标处理记录

现在大部分Java框架都是通过jdbc从数据库取出数据，然后装载到一个list里再处理，list里可能是业务Object，也可能是hashmap。

由于JVM内存一般都小于4G，所以不可能一次通过sql把大量数据装载到list里。为了完成功能，很多程序员喜欢采用分页的方法处理，如一次从数据库取1000条记录，通过多次循环搞定，保证不会引起JVM Out of memory问题。

以下是实现此功能的代码示例，t_employee表有10万条记录，设置分页大小为1000：

d1 = Calendar.getInstance().getTime();

vsql = "select count(*) cnt from t_employee";

pstmt = conn.prepareStatement(vsql);

ResultSet rs = pstmt.executeQuery();

Integer cnt = 0;

while (rs.next()) {

cnt = rs.getInt("cnt");

}

Integer lastid=0;

Integer pagesize=1000;

System.out.println("cnt:" + cnt);

String vsql = "select count(*) cnt from t_employee";

PreparedStatement pstmt = conn.prepareStatement(vsql);

ResultSet rs = pstmt.executeQuery();

Integer cnt = 0;

while (rs.next()) {

cnt = rs.getInt("cnt");

}

Integer lastid = 0;

Integer pagesize = 1000;

System.out.println("cnt:" + cnt);

for (int i = 0; i <= cnt / pagesize; i++) {

vsql = "select * from (select * from t_employee where id>? order by id) where rownum<=?";

pstmt = conn.prepareStatement(vsql);

pstmt.setFetchSize(1000);

pstmt.setInt(1, lastid);

pstmt.setInt(2, pagesize);

rs = pstmt.executeQuery();

int col_cnt = rs.getMetaData().getColumnCount();

Object o;

while (rs.next()) {

for (int j = 1; j <= col_cnt; j++) {

o = rs.getObject(j);

}

lastid = rs.getInt("id");

}

rs.close();

pstmt.close();

}

以上代码实际执行时间为6.516秒

很多持久层框架为了尽量让程序员使用方便，封装了jdbc通过statement执行数据返回到resultset的细节，导致程序员会想采用分页的方式处理问题。实际上如果我们采用jdbc原始的resultset游标处理记录，在resultset循环读取的过程中处理记录，这样就可以一次从数据库取出所有记录。显著提高性能。

这里需要注意的是，采用resultset游标处理记录时，应该将游标的打开方式设置为FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)，否则会把结果缓存在JVM里，造成JVM Out of memory问题。

代码示例：

String vsql ="select * from t_employee";

PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

pstmt.setFetchSize(100);

ResultSet rs = pstmt.executeQuery(vsql);

int col_cnt = rs.getMetaData().getColumnCount();

Object o;

while (rs.next()) {

for (int j = 1; j <= col_cnt; j++) {

o = rs.getObject(j);

}

}

调整后的代码实际执行时间为3.156秒

从测试结果可以看出性能提高了1倍多，如果采用分页模式数据库每次还需发生磁盘IO的话那性能可以提高更多。

iBatis等持久层框架考虑到会有这种需求，所以也有相应的解决方案，在iBatis里我们不能采用queryForList的方法，而应用该采用queryWithRowHandler加回调事件的方式处理，如下所示：

MyRowHandler myrh=new MyRowHandler();

sqlmap.queryWithRowHandler("getAllEmployee", myrh);

class MyRowHandler implements RowHandler {

public void handleRow(Object o) {

//todo something

}

}

iBatis的queryWithRowHandler很好的封装了resultset遍历的事件处理，效果及性能与resultset遍历一样，也不会产生JVM内存溢出。

3、[减少数据库服务器CPU运算]

3.1、使用绑定变量

绑定变量是指SQL中对变化的值采用变量参数的形式提交，而不是在SQL中直接拼写对应的值。

非绑定变量写法：select * from employee where id=1234567

绑定变量写法：

select * from employee where id=?

Preparestatement.setInt(1,1234567)

Java中Preparestatement就是为处理绑定变量提供的对像，绑定变量有以下优点：

1、防止SQL注入

2、提高SQL可读性

3、提高SQL解析性能，不使用绑定变更我们一般称为硬解析，使用绑定变量我们称为软解析。

第1和第2点很好理解，做编码的人应该都清楚，这里不详细说明。关于第3点，到底能提高多少性能呢，下面举一个例子说明：

假设有这个这样的一个数据库主机：

2个4核CPU

100块磁盘，每个磁盘支持IOPS为160

业务应用的SQL如下：

select * from table where pk=?

这个SQL平均4个IO（3个索引IO+1个数据IO）

IO缓存命中率75%（索引全在内存中，数据需要访问磁盘）

SQL硬解析CPU消耗：1ms （常用经验值）

SQL软解析CPU消耗：0.02ms（常用经验值）

假设CPU每核性能是线性增长，访问内存Cache中的IO时间忽略，要求计算系统对如上应用采用硬解析与采用软解析支持的每秒最大并发数：

是否使用绑定变量	CPU支持最大并发数	磁盘IO支持最大并发数
不使用	2*4*1000=8000	100*160=16000
使用	2*4*1000/0.02=400000	100*160=16000

从以上计算可以看出，不使用绑定变量的系统当并发达到8000时会在CPU上产生瓶颈，当使用绑定变量的系统当并行达到16000时会在磁盘IO上产生瓶颈。所以如果你的系统CPU有瓶颈时请先检查是否存在大量的硬解析操作。

使用绑定变量为何会提高SQL解析性能，这个需要从数据库SQL执行原理说明，一条SQL在Oracle数据库中的执行过程如下图所示：

当一条SQL发送给数据库服务器后，系统首先会将SQL字符串进行hash运算，得到hash值后再从服务器内存里的SQL缓存区中进行检索，如果有相同的SQL字符，并且确认是同一逻辑的SQL语句，则从共享池缓存中取出SQL对应的执行计划，根据执行计划读取数据并返回结果给客户端。

如果在共享池中未发现相同的SQL则根据SQL逻辑生成一条新的执行计划并保存在SQL缓存区中，然后根据执行计划读取数据并返回结果给客户端。

为了更快的检索SQL是否在缓存区中，首先进行的是SQL字符串hash值对比，如果未找到则认为没有缓存，如果存在再进行下一步的准确对比，所以要命中SQL缓存区应保证SQL字符是完全一致，中间有大小写或空格都会认为是不同的SQL。

如果我们不采用绑定变量，采用字符串拼接的模式生成SQL,那么每条SQL都会产生执行计划，这样会导致共享池耗尽，缓存命中率也很低。

一些不使用绑定变量的场景：

a、数据仓库应用，这种应用一般并发不高，但是每个SQL执行时间很长，SQL解析的时间相比SQL执行时间比较小，绑定变量对性能提高不明显。数据仓库一般都是内部分析应用，所以也不太会发生SQL注入的安全问题。

b、数据分布不均匀的特殊逻辑，如产品表，记录有1亿，有一产品状态字段，上面建有索引，有审核中，审核通过，审核未通过3种状态，其中审核通过9500万，审核中1万，审核不通过499万。

要做这样一个查询：

select count(*) from product where status=?

采用绑定变量的话，那么只会有一个执行计划，如果走索引访问，那么对于审核中查询很快，对审核通过和审核不通过会很慢；如果不走索引，那么对于审核中与审核通过和审核不通过时间基本一样；

对于这种情况应该不使用绑定变量，而直接采用字符拼接的方式生成SQL，这样可以为每个SQL生成不同的执行计划，如下所示。

select count(*) from product where status='approved'; //不使用索引

select count(*) from product where status='tbd'; //不使用索引

select count(*) from product where status='auditing';//使用索引

3.2、合理使用排序

Oracle的排序算法一直在优化，但是总体时间复杂度约等于nLog(n)。普通OLTP系统排序操作一般都是在内存里进行的，对于数据库来说是一种CPU的消耗，曾在PC机做过测试，单核普通CPU在1秒钟可以完成100万条记录的全内存排序操作，所以说由于现在CPU的性能增强，对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上万条以上时，你需要注意是否一定要这么做了，大记录集排序不仅增加了CPU开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降，这种需求需要与DBA沟通再决定，取决于你的需求和数据，所以只有你自己最清楚，而不要被别人说排序很慢就吓倒。

以下列出了可能会发生排序操作的SQL语法：

order by

group by

distinct

exists子查询

not exists子查询

in子查询

not In子查询

union（并集），Union All也是一种并集操作，但是不会发生排序，如果你确认两个数据集不需要执行去除重复数据操作，那请使用Union All 代替Union。

minus（差集）

intersect（交集）

create Index

merge Join，这是一种两个表连接的内部算法，执行时会把两个表先排序好再连接，应用于两个大表连接的操作。如果你的两个表连接的条件都是等值运算，那可以采用Hash Join来提高性能，因为Hash Join使用Hash 运算来代替排序的操作。具体原理及设置参考SQL执行计划优化专题。

3.3、减少比较操作

我们SQL的业务逻辑经常会包含一些比较操作，如a=b，a<b之类的操作，对于这些比较操作数据库都体现得很好，但是如果有以下操作，我们需要保持警惕：

Like模糊查询，如下所示：

a like ‘%abc%’

Like模糊查询对于数据库来说不是很擅长，特别是你需要模糊检查的记录有上万条以上时，性能比较糟糕，这种情况一般可以采用专用Search或者采用全文索引方案来提高性能。

不能使用索引定位的大量In List，如下所示：

a in (:1,:2,:3,…,:n) ----n>20

如果这里的a字段不能通过索引比较，那数据库会将字段与in里面的每个值都进行比较运算，如果记录数有上万以上，会明显感觉到SQL的CPU开销加大，这个情况有两种解决方式：

a、 将in列表里面的数据放入一张中间小表，采用两个表Hash Join关联的方式处理；

b、 采用str2varList方法将字段串列表转换一个临时表处理，关于str2varList方法可以在网上直接查询，这里不详细介绍。

以上两种解决方案都需要与中间表Hash Join的方式才能提高性能，如果采用了Nested Loop的连接方式性能会更差。

如果发现我们的系统IO没问题但是CPU负载很高，就有可能是上面的原因，这种情况不太常见，如果遇到了最好能和DBA沟通并确认准确的原因。

3.4、大量复杂运算在客户端处理

什么是复杂运算，一般我认为是一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等。

如果有大量这类函数运算，尽量放在客户端处理，一般CPU每秒中也只能处理1万-10万次这样的函数运算，放在数据库内不利于高并发处理。

其他：
避免使用两个不同类型的列进行表的连接
讲解：
1、当连接两个不同类型的列时，其中一个列必须转换成另一个列的类型，级别低的会被转换成高级别的类型，转换操作会消耗一定的系统资源;
2、如果你使用两个不同类型的列来连接表，其中一个列原本可以使用索引，但经过转换后，优化器就不会使用它的索引了
select column_list from small_table, large_table where small_table.float_column = large_table.int_column;
在这个例子中，数据库会将int列转换为float类型，因为int比float类型的级别低，large_table.int_column上的索引就不会被使用，但smalltable.float_column上的索引可以正常使用。
避免死锁
1、在你的存储过程和触发器中访问同一个表时总是以相同的顺序;
2、事务应尽可能地缩短，在一个事务中应尽可能减少涉及到的数据量;
3、永远不要在事务中等待用户输入。
使用“基于规则的方法”而不是使用“程序化方法”编写SQL
1、数据库引擎专门为基于规则的SQL进行了优化，因此处理大型结果集时应尽量避免使用程序化的方法(使用游标或UDF[User Defined Functions]处理返回的结果集) ;
2、如何摆脱程序化的SQL呢?有以下方法：
使用内联子查询替换用户定义函数;
使用相关联的子查询替换基于游标的代码;
如果确实需要程序化代码，至少应该使用表变量代替游标导航和处理结果集。
避免使用动态SQL
除非迫不得已，应尽量避免使用动态SQL，因为：
1、动态SQL难以调试和故障诊断；
2、如果用户向动态SQL提供了输入，那么可能存在SQL注入风险。
避免使用临时表
1、除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;
2、大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。
用户定义函数中使用下列最佳实践
不要在你的存储过程，触发器，函数和批处理中重复调用函数，例如，在许多时候，你需要获得字符串变量的长度，无论如
在存储过程中使用下列最佳实践
1、不要使用SP_xxx作为命名约定，它会导致额外的搜索，增加I/O(因为系统存储过程的名字就是以SP_开头的)，同时这么做还会增加与系统存储过程名称冲突的几率;
2、将Nocount设置为On避免额外的网络开销;
3、当索引结构发生变化时，在EXECUTE语句中(第一次)使用WITH RECOMPILE子句，以便存储过程可以利用最新创建的索引;
4、使用默认的参数值更易于调试。
在触发器中使用下列最佳实践
1、最好不要使用触发器，触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程;、
2、如果能够使用约束实现的，尽量不要使用触发器;
3、不要为不同的触发事件(Insert，Update和Delete)使用相同的触发器;
4、不要在触发器中使用事务型代码。
在视图中使用下列最佳实践
1、为重新使用复杂的TSQL块使用视图，并开启索引视图;
2、如果你不想让用户意外修改表结构，使用视图时加上SCHEMABINDING选项;
3、如果只从单个表中检索数据，就不需要使用视图了，如果在这种情况下使用视图反倒会增加系统开销，一般视图会涉及多个表时才有用。
在事务中使用下列最佳实践
1、SQL Server 2005之前，在BEGIN TRANSACTION之后，每个子查询修改语句时，必须检查@@ERROR的值，如果值不等于0，那么最后的语句可能会导致一个错误，如果发生任何错误，事务必须回滚。从SQL Server 2005开始，Try..Catch..代码块可以处理TSQL中的事务，因此在事务型代码中最好加上Try…Catch…;
2、避免使用嵌套事务，使用@@TRANCOUNT变量检查事务是否需要启动(为了避免嵌套事务);
3、尽可能晚启动事务，提交和回滚事务要尽可能快，以减少资源锁定时间。
要完全列举最佳实践不是本文的初衷，当你了解了这些技巧后就应该拿来使用，否则了解了也没有价值。此外，你还需要评审和监视数据访问代码是否遵循下列标准和最佳实践。

数据库参数配置

对MySQL配置优化 [在my.ini中配置最大并发数, 调整缓存大小 ]
最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 1G

对于myisam，需要调整key_buffer_size
当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数

port=3306
default-storage-engine=INNODB 
max_connections=100

恰当的硬件资源和操作系统

1、数据库服务器硬件升级，内存，硬盘等适当升级
2、内存超过4G的情况下，使用64位操作系统，建议使用Lunix操作系统，安装MySQL 5.5及以上版本
3、读写[写: update/delete/insert]分离
如果数据库压力很大，一台机器无法支撑，那么可以使用MySQL复制实现多台机器同步，将数据库的压力分散。
Master、Slave1, Slave2...，其中Master用于写，Slave1到n用于查，每个数据库分担的压力小很多。
要实现这种方式，需要程序特别设计，写都操作master，读都操作slave，给程序开发带来了额外负担。当然目前已经有中间件来实现这个代理，对程 序来读写哪些数据库是透明的。官方有个mysql-proxy，但是还是alpha版本的。新浪有个amobe for mysql，也可达到这个目的。
other:

[利用更多的资源]

1.1、客户端多进程并行访问

多进程并行访问是指在客户端创建多个进程(线程)，每个进程建立一个与数据库的连接，然后同时向数据库提交访问请求。当数据库主机资源有空闲时，我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时，采用多进程并行访问性能不会提高，反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。

例如：

我们有10000个产品ID，现在需要根据ID取出产品的详细信息，如果单线程访问，按每个IO要5ms计算，忽略主机CPU运算及网络传输时间，我们需要50s才能完成任务。如果采用5个并行访问，每个进程访问2000个ID，那么10s就有可能完成任务。

那是不是并行数越多越好呢，开1000个并行是否只要50ms就搞定，答案肯定是否定的，当并行数超过服务器主机资源的上限时性能就不会再提高，如果再增加反而会增加主机的进程间调度成本和进程冲突机率。

以下是一些如何设置并行数的基本建议：

如果瓶颈在服务器主机，但是主机还有空闲资源，那么最大并行数取主机CPU核数和主机提供数据服务的磁盘数两个参数中的最小值，同时要保证主机有资源做其它任务。

如果瓶颈在客户端处理，但是客户端还有空闲资源，那建议不要增加SQL的并行，而是用一个进程取回数据后在客户端起多个进程处理即可，进程数根据客户端CPU核数计算。

如果瓶颈在客户端网络，那建议做数据压缩或者增加多个客户端，采用map reduce的架构处理。

如果瓶颈在服务器网络，那需要增加服务器的网络带宽或者在服务端将数据压缩后再处理了。

1.2、数据库并行处理

数据库并行处理是指客户端一条SQL的请求，数据库内部自动分解成多个进程并行处理，如下图所示：

并不是所有的SQL都可以使用并行处理，一般只有对表或索引进行全部访问时才可以使用并行。数据库表默认是不打开并行访问，所以需要指定SQL并行的提示，如下所示：

select /*+parallel(a,4)*/ * from employee;

并行的优点：

使用多进程处理，充分利用数据库主机资源（CPU,IO），提高性能。

并行的缺点：

1、单个会话占用大量资源，影响其它会话，所以只适合在主机负载低时期使用；

2、只能采用直接IO访问，不能利用缓存数据，所以执行前会触发将脏缓存数据写入磁盘操作。

注：

1、并行处理在OLTP类系统中慎用，使用不当会导致一个会话把主机资源全部占用，而正常事务得不到及时响应，所以一般只是用于数据仓库平台。

2、一般对于百万级记录以下的小表采用并行访问性能并不能提高，反而可能会让性能更差。

拓展

性能优化基于数据

数据备份与还原

create database test;
use test;
CREATE TABLE `new_table1` (
`id` int(11) NOT NULL,
`name` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `new_table1` VALUES (1, 'Jef'),(2, 'Ran'),(3, 'Da'), (4, 'Nan'),(5, 'Yuan'),(6, 'Gu');
CREATE TABLE `new_table2` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `new_table3` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

定时完成数据库的备份
项目实际需求，请完成定时备份某个数据库，或者定时备份数据库的某些表的操作
windows 下每隔1小时，备份一次数据test
windows 每天晚上2:00 备份 test下 某一张表
cmd>

mysqldump –u root –p密码 数据库名 > 把数据库放入到某个目录

场景：备份 test库的所有表
进入mysqldump所在的目录C:\Program Files\MySQL\MySQL Server 5.7\bin，默认根目录会自动配置到Path中，如果没有也可以手动配置到Path中
场景：把test数据库的所有表全部导出备份
cmd>

mysqldump –u root –proot test > E:/saveMySQL/test.sql

场景：将test数据库的new_table1 new_table2导出备份
cmd>

mysqldump –u root –proot test new_table1 new_table2 > E:/saveMySQL/new_table1And2.sql 

如何恢复数据
进入的mysql操作界面
mysql>

source E:/saveMySQL/new_table1And2.sql

上述的后缀也可用.log的形式进行备份
定时备份:(把命令写入到my.bat 中)
windows 如何定时备份 (每天凌晨2:00)
使用windows自带的计划任务，定时执行批处理命令。

增量备份和还原

定义：MySQL数据库会以二进制的形式，自动把用户对mysql数据库的操作，记录到文件，当用户希望恢复的时候，可以使用备份文件进行恢复。
增量备份会记录dml语句、创建表的语句，不会记录select。记录的东西包括：sql语句本身、操作时间，位置
进行增量备份的步骤和恢复
注意：mysql5.0及之前的版本是不支持增量备份的
1、配置my.ini文件或者my.conf，启用二进制备份。
打开my.ini文件，查找log-bin，进行配置：

log-bin=E:/saveMySQL/MySQLSave.log

如果是5.7还要进行配置：

binlog_format=mixed

2、重启MySQL服务
这个时候会在saveMySQL目录下面看到以下两个文件：
MySQLSave.000001：日志备份文件。如果要查看这个日志文件里面的信息，我们可以使用mysqlbinlog程序查看，mysqlbinlog程序存放在MySQL的bin目录下面，C:\Program Files\MySQL\MySQL Server 5.7\bin，默认根目录会自动配置到Path中，如果没有也可以手动配置到Path中
执行sql语句

update new_table1 set id = 20 where id=1;

Win + R，cmd

mysqlbinlog 备份文件路径E:\saveMySQL\MySqlSave.000001

MySqlSave.index：日志索引文件，里面记录了所以的日志文件。（E:\saveMySQL\MySqlSave.000001）
3、假设现在问题来了，我这条update是误操作，如何进行恢复
在mysql日志中会记录每一次操作的时间和位置，所以我们既可以根据时间来恢复，也可以根据位置来恢复。
那么，我们现在马上可以从上图看出，这条语句产生的时间是"180617 23:22:44"，位置是1497
按时间来恢复
我们可以选择在语句产生时间的前一秒
执行cmd命令：

mysqlbinlog --stop-datetime="2018-06-17 23:22:43" E:\saveMySQL\MySqlSave.000001 | mysql -uroot -p

这个时候我再执行SQL语句查看
select * from new_table1;
结果变成了
按位置来恢复
执行cmd命令：

mysqlbinlog --stop-position="1497" E:\saveMySQL\MySqlSave.000001 | mysql -uroot -p

这个时候再执行SQL来查看结果，又变回来了。