Oracle数据库管理----性能优化

红太狼 2021-12-15 14:27 371阅读 0赞

https://blog.csdn.net/yzllz001/article/details/54848513

# 数据库访问优化法则 #

要正确的优化SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是2Mbps，硬盘是每分钟7200转等等。因此，为了快速找到SQL的性能瓶颈点，我们也需要了解我们计算机系统的硬件基本性能指标，下图展示的当前主流计算机性能指标数据。

![0_1291636622rnbL.gif][]

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

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

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

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

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

由于SSD硬盘还处于快速发展阶段，所以本文的内容不涉及SSD相关应用系统。

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

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

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

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

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

![0_12916372042dJ3.gif][]

这个优化法则归纳为5个层次：

1、  减少数据访问（减少磁盘访问）

2、  返回更少数据（减少网络传输或磁盘访问）

3、  减少交互次数（减少网络传输）

4、  减少服务器CPU开销（减少CPU及内存开销）

5、  利用更多资源（增加资源）

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

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

<table> 
 <tbody> 
 <tr> 
 <td> 优化法则 </td> 
 <td> 性能提升效果 </td> 
 <td> 优化成本 </td> 
 </tr> 
 <tr> 
 <td> 减少数据访问 </td> 
 <td> 1~1000 </td> 
 <td> 低 </td> 
 </tr> 
 <tr> 
 <td> 返回更少数据 </td> 
 <td> 1~100 </td> 
 <td> 低 </td> 
 </tr> 
 <tr> 
 <td> 减少交互次数 </td> 
 <td> 1~20 </td> 
 <td> 低 </td> 
 </tr> 
 <tr> 
 <td> 减少服务器CPU开销 </td> 
 <td> 1~5 </td> 
 <td> 低 </td> 
 </tr> 
 <tr> 
 <td> 利用更多资源 </td> 
 <td> @~10 </td> 
 <td> 高 </td> 
 </tr> 
 </tbody> 
</table>

接下来，我们针对5种优化法则列举常用的优化手段并结合实例分析。

# 二、Oracle数据库两个基本概念 #

## 数据块(Block) ##

数据块是数据库中数据在磁盘中存储的最小单位，也是一次IO访问的最小单位，一个数据块通常可以存储多条记录，数据块大小是DBA在创建数据库或表空间时指定，可指定为2K、4K、8K、16K或32K字节。下图是一个[Oracle][]数据库典型的物理结构，一个数据库可以包括多个数据文件，一个数据文件内又包含多个数据块；

![0_1291636632fz7h.gif][]

## ROWID ##

ROWID是每条记录在数据库中的唯一标识，通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号，如下图所示：

![0_1291636638QCqG.gif][]

# 三、数据库访问优化法则详解 #

## 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索引结构示意图如下所示：

![0_1291636647TTAA.gif][]

如果我们把一个表的内容认为是一本字典，那索引就相当于字典的目录，如下图所示：

![0_12916366512rkn.gif][]

![0_129163667068Da.gif][]

图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首+笔划的组合索引。

一个表中可以建多个索引，就如一本字典可以建多个目录一样（按拼音、笔划、部首等等）。

一个索引也可以由多个字段组成，称为组合索引，如上图就是一个按部首+笔划的组合目录。

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（两个表通过索引字段关联）

SQL什么条件不会使用索引？

<table> 
 <tbody> 
 <tr> 
 <td> 查询条件 </td> 
 <td> 不能使用索引原因 </td> 
 </tr> 
 <tr> 
 <td> INDEX_COLUMN &lt;&gt; ? INDEX_COLUMN not in (?,?,...,?) </td> 
 <td> 不等于操作不能使用索引 </td> 
 </tr> 
 <tr> 
 <td> function(INDEX_COLUMN) = ? INDEX_COLUMN + 1 = ? INDEX_COLUMN || 'a' = ? </td> 
 <td> 经过普通运算或函数运算后的索引字段不能使用索引 </td> 
 </tr> 
 <tr> 
 <td> INDEX_COLUMN like '%'||? INDEX_COLUMN like '%'||?||'%' </td> 
 <td> 含前导模糊查询的Like语法不能使用索引 </td> 
 </tr> 
 <tr> 
 <td> INDEX_COLUMN is null </td> 
 <td> B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引 </td> 
 </tr> 
 <tr> 
 <td> NUMBER_INDEX_COLUMN='12345' CHAR_INDEX_COLUMN=12345 </td> 
 <td> Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。 </td> 
 </tr> 
 <tr> 
 <td> a.INDEX_COLUMN=a.COLUMN_1 </td> 
 <td> 给索引查询的值应是已知数据，不能是未知字段值。 </td> 
 </tr> 
 <tr> 
 <td colspan="2"> 注： 经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。 有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引 如：我们company表建了一个id+name的组合索引，以下SQL是不能使用索引的 Select * from company where name=? Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题，但是通过index skip scan提高性能的条件比较特殊，使用不好反而性能会更差。 &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

我们一般在什么字段上建索引？

这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

1、字段出现在查询条件中，并且查询条件可以使用索引；

2、语句执行频率高，一天会有几千次以上；

3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？

这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：

小表(记录数小于10000行的表)：筛选比例<10%；

大表：(筛选返回记录数)<(表总记录数\*单条记录长度)/10000/16

单条记录长度≈字段平均内容长度之和+字段数\*2

以下是一些字段是否需要建B-TREE索引的经验分类：

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; </td> 
 <td> 字段类型 </td> 
 <td> 常见字段名 </td> 
 </tr> 
 <tr> 
 <td> 需要建索引的字段 </td> 
 <td> 主键 </td> 
 <td> ID,PK </td> 
 </tr> 
 <tr> 
 <td> 外键 </td> 
 <td> PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID </td> 
 </tr> 
 <tr> 
 <td> 有对像或身份标识意义字段 </td> 
 <td> HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO </td> 
 </tr> 
 <tr> 
 <td> 索引慎用字段,需要进行数据分布及使用场景详细评估 </td> 
 <td> 日期 </td> 
 <td> GMT_CREATE,GMT_MODIFIED </td> 
 </tr> 
 <tr> 
 <td> 年月 </td> 
 <td> YEAR,MONTH </td> 
 </tr> 
 <tr> 
 <td> 状态标志 </td> 
 <td> PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG </td> 
 </tr> 
 <tr> 
 <td> 类型 </td> 
 <td> ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE </td> 
 </tr> 
 <tr> 
 <td> 区域 </td> 
 <td> COUNTRY,PROVINCE,CITY </td> 
 </tr> 
 <tr> 
 <td> 操作人员 </td> 
 <td> CREATOR,AUDITOR </td> 
 </tr> 
 <tr> 
 <td> 数值 </td> 
 <td> LEVEL,AMOUNT,SCORE </td> 
 </tr> 
 <tr> 
 <td> 长字符 </td> 
 <td> ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT </td> 
 </tr> 
 <tr> 
 <td> 不适合建索引的字段 </td> 
 <td> 描述备注 </td> 
 <td> DESCRIPTION,REMARK,MEMO,DETAIL </td> 
 </tr> 
 <tr> 
 <td> 大字段 </td> 
 <td> FILE_CONTENT,EMAIL_CONTENT </td> 
 </tr> 
 </tbody> 
</table>

如何知道SQL是否使用了正确的索引？

简单SQL可以根据索引使用语法规则判断，复杂的SQL不好办，判断SQL的响应时间是一种策略，但是这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用，需要到数据库中查看SQL真实的执行计划，这个话题比较复杂，详见SQL执行计划专题介绍。

索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？

这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表[测试][Link 3]数据，仅供参考：

索引对于Insert性能降低56%

索引对于Update性能降低47%

索引对于Delete性能降低29%

因此对于写IO压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。

### 1.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请求的字段都建在索引里，所以这种只通过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询。

### 1.3、优化SQL执行计划 ###

SQL执行计划是关系型数据库最核心的技术之一，它表示SQL执行时的数据访问[算法][Link 4]。由于业务需求越来越复杂，表数据量也越来越大，程序员越来越懒惰，SQL也需要支持非常复杂的业务逻辑，但SQL的性能还需要提高，因此，优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外，还需要有一套优秀的算法库来提高SQL性能。

目前ORACLE有SQL执行计划的算法约300种，而且一直在增加，所以SQL执行计划是一个非常复杂的课题，一个普通DBA能掌握50种就很不错了，就算是资深DBA也不可能把每个执行计划的算法描述清楚。虽然有这么多种算法，但并不表示我们无法优化执行计划，因为我们常用的SQL执行计划算法也就十几个，如果一个程序员能把这十几个算法搞清楚，那就掌握了80%的SQL执行计划调优知识。

由于篇幅的原因，SQL执行计划需要专题介绍，在这里就不多说了。

1.创建用户时，指定限额

[SQL][]>conn / as sysdba;

Connected.

SQL>create user anqing identified by anqing default tablespace users temporarytablespace temp quota 10M on users;

Usercreated.

查询用户配额的信息：

SQL>select tablespace\_name,username,max\_bytes from  DBA\_TS\_QUOTAS whereusername='ANQING';

TABLESPACE\_NAME     USERNAME    MAX\_BYTES

\---------------------------------------- ----------

USERS                   ANQING      10485760

2.更改用户的表空间限额：

不对用户做表空间限额控制:

SQL>grant unlimited tablespace to anqing;

Grantsucceeded.

这种方式是全局性的，修改的是当前用户对于所有表空间的配额，即赋予该用户无限个表空间创建表权限，每个表空间下都没有配额限制。

如果我们想改某个具体的,即针对用户的某个特定的表空间，可以使用如下SQL：

SQL>alter user anqing quota unlimited on users;

Useraltered.

查看配额：

SQL>select tablespace\_name,username,max\_bytes from  DBA\_TS\_QUOTAS whereusername='ANQING';

TABLESPACE\_NAME               USERNAME   MAX\_BYTES

\---------------------------------------- ----------

USERS                        ANQING             -1

这时候max\_bytes为-1，即不受限制。

3.回收用户对表空间的配额:

回收空间配额有两种方式：

1）全局：

SQL>revoke unlimited tablespace from anqing;

Revokesucceeded.

在查看配额，已经没有了相关信息：

SQL>select tablespace\_name,username,max\_bytes from  DBA\_TS\_QUOTAS whereusername='ANQING';

no rowsselected

2）特定表空间：

SQL>alter user anqing quota 0 on users;

oracle数据库的内存结构比较复杂，下面对pga/sga/uga做比较分析。

1. sga组成:

database buffer cache:包括 default pool,keep pool,recycle pool；

redo log buffer  
    share pool:包括 library cache,dictionary cache  
    large pool  
    java pool  
    streams pool  
    fixed sga  
2.pga组成：

1）sql工作区：sort area(排序区),hash area(构造hash表）,bitmap merge area(索引区)

2）uga区  
3.pga和uga比较：  
uga：user global area ，是会话含义的内存区  
        为了保证数据可以被会话访问到，所以mts模式属于sga中的大池，专有模式属于pga，属于用户的内存区。  
         uga保存当前会话相关的信息，比如会话登录信息、pl／sql包的参数信息,绑定变量的值。  
pga：program global area，是操作系统含义上的内存区，  
       可以理解为操作系统在一个进程启动时，为他分配的内存空间  
        查询使用 show pga;  
4.sga和pga比较：  
sga：共享数据块，所有进程可以访问,数据并发访问  
         涉及lock，latch,锁定和队列  
        是数据库最主要优化区域，一些重要的指标：data buffer hit,library hit(hard/soft parse),hot blocks  
pga：为专有进程服务，进程间无法数据共享，数据独占  
       无需锁定机制  
        性能优化只需要考虑它的大小。  
\---------------------

四、[MySQL 数据库性能优化之缓存参数优化][MySQL]

数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是 IO，尽可能将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化：

*  query\_cache\_size/query\_cache\_type （global） Query cache 作用于整个 MySQL Instance，主要用来缓存 MySQL 中的 ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当我们打开了 Query Cache 功能，MySQL在接受到一条select语句的请求后，如果该语句满足Query Cache的要求（未显式说明不允许使用Query Cache，或者已经显式申明需要使用Query Cache），MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash，然后到Query Cache 中直接查找是否已经缓存。也就是说，如果已经在缓存中，该select请求就会直接将数据返回，从而省略了后面所有的步骤（如 SQL语句的解析，优化器优化以及向存储引擎请求数据等），极大的提高性能。当然，Query Cache 也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache 中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache 可能会得不偿失。Query Cache的使用需要多个参数配合，其中最为关键的是 query\_cache\_size 和 query\_cache\_type ，前者设置用于缓存 ResultSet 的内存大小，后者设置在何场景下使用 Query Cache。在以往的经验来看，如果不是用来缓存基本不变的数据的MySQL数据库，query\_cache\_size 一般 256MB 是一个比较合适的大小。当然，这可以通过计算Query Cache的命中率（Qcache\_hits/(Qcache\_hits+Qcache\_inserts)\*100)）来进行调整。query\_cache\_type可以设置为0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache（使用sql\_no\_cache）之外的所有的select都使用query cache，只有显示要求才使用query cache（使用sql\_cache）。
 *  binlog\_cache\_size （global） Binlog Cache 用于在打开了二进制日志（binlog）记录功能的环境，是 MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog\_cache\_size。同时，我们可以通过binlog\_cache\_use 以及 binlog\_cache\_disk\_use来分析设置的binlog\_cache\_size是否足够，是否有大量的binlog\_cache由于内存大小不够而使用临时文件（binlog\_cache\_disk\_use）来缓存了。
 *  key\_buffer\_size （global） Key Buffer 可能是大家最为熟悉的一个 MySQL 缓存参数了，尤其是在 MySQL 没有更换默认存储引擎的时候，很多朋友可能会发现，默认的 MySQL 配置文件中设置最大的一个内存参数就是这个参数了。key\_buffer\_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。此外，当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意，由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的 SQL 一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。
 *  bulk\_insert\_buffer\_size （thread）和key\_buffer\_size一样，这个参数同样也仅作用于使用 MyISAM存储引擎，用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件：insert … select …  
    insert … values (…) ,(…),(…)…  
    load data infile… into… (非空表)
 *  innodb\_buffer\_pool\_size（global）当我们使用InnoDB存储引擎的时候，innodb\_buffer\_pool\_size 参数可能是影响我们性能的最为关键的一个参数了，他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key\_buffer\_size 参数，当然，可能更像是 Oracle 的 db\_cache\_size。简单来说，当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。和key\_buffer\_size 对于 MyISAM 引擎一样，innodb\_buffer\_pool\_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，所以如果我们有足够的内存，尽可将该参数设置到足够打，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中，直至全部。我们可以通过 (Innodb\_buffer\_pool\_read\_requests – Innodb\_buffer\_pool\_reads) / Innodb\_buffer\_pool\_read\_requests \* 100% 计算缓存命中率，并根据命中率来调整 innodb\_buffer\_pool\_size 参数大小进行优化。
 *  innodb\_additional\_mem\_pool\_size（global）这个参数我们平时调整的可能不是太多，很多人都使用了默认值，可能很多人都不是太熟悉这个参数的作用。innodb\_additional\_mem\_pool\_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL 会记录 Warning 信息到数据库的 error log 中，这时候你就知道该调整这个参数大小了。
 *  innodb\_log\_buffer\_size （global）这是 InnoDB 存储引擎的事务日志所使用的缓冲区。类似于 Binlog Buffer，InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innofb Log Buffer 中，当满足 innodb\_flush\_log\_trx\_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。可以通过 innodb\_log\_buffer\_size 参数设置其可以使用的最大内存空间。  
    注：innodb\_flush\_log\_trx\_commit 参数对 InnoDB Log 的写入性能有非常关键的影响。该参数可以设置为0，1，2，解释如下：0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；  
    1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；  
    2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。此外，MySQL文档中还提到，这几种设置中的每秒同步一次的机制，可能并不会完全确保非常准确的每秒就一定会发生同步，还取决于进程调度的问题。实际上，InnoDB 能否真正满足此参数所设置值代表的意义正常 Recovery 还是受到了不同 OS 下文件系统以及磁盘本身的限制，可能有些时候在并没有真正完成磁盘同步的情况下也会告诉 mysqld 已经完成了磁盘同步。
 *  innodb\_max\_dirty\_pages\_pct （global）这个参数和上面的各个参数不同，他不是用来设置用于缓存某种数据的内存大小的一个参数，而是用来控制在 InnoDB Buffer Pool 中可以不用写入数据文件中的Dirty Page 的比例（已经被修但还没有从内存中写入到数据文件的脏数据）。这个比例值越大，从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘IO。但是，如果这个比例值过大，当数据库 Crash 之后重启的时间可能就会很长，因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。同时，过大的比例值同时可能也会造成在达到比例设定上限后的 flush 操作“过猛”而导致性能波动很大。

上面这几个参数是 MySQL 中为了减少磁盘物理IO而设计的主要参数，对 MySQL 的性能起到了至关重要的作用。

—EOF—

按照  [mcsrainbow][] 朋友的要求，这里列一下根据以往经验得到的相关参数的建议值：

*  query\_cache\_type : 如果全部使用innodb存储引擎，建议为0，如果使用MyISAM 存储引擎，建议为2，同时在SQL语句中显式控制是否是哟你gquery cache
 *  query\_cache\_size: 根据 命中率（Qcache\_hits/(Qcache\_hits+Qcache\_inserts)\*100)）进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大
 *  binlog\_cache\_size: 一般环境2MB～4MB是一个合适的选择，事务较大且写入频繁的数据库环境可以适当调大，但不建议超过32MB
 *  key\_buffer\_size: 如果不使用MyISAM存储引擎，16MB足以，用来缓存一些系统表信息等。如果使用 MyISAM存储引擎，在内存允许的情况下，尽可能将所有索引放入内存，简单来说就是“越大越好”
 *  bulk\_insert\_buffer\_size: 如果经常性的需要使用批量插入的特殊语句（上面有说明）来插入数据，可以适当调大该参数至16MB～32MB，不建议继续增大，某人8MB
 *  innodb\_buffer\_pool\_size: 如果不使用InnoDB存储引擎，可以不用调整这个参数，如果需要使用，在内存允许的情况下，尽可能将所有的InnoDB数据文件存放如内存中，同样将但来说也是“越大越好”
 *  innodb\_additional\_mem\_pool\_size: 一般的数据库建议调整到8MB～16MB，如果表特别多，可以调整到32MB，可以根据error log中的信息判断是否需要增大
 *  innodb\_log\_buffer\_size: 默认是1MB，系的如频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB
 *  innodb\_max\_dirty\_pages\_pct: 根据以往的经验，重启恢复的数据如果要超过1GB的话，启动速度会比较慢，几乎难以接受，所以建议不大于 1GB/innodb\_buffer\_pool\_size(GB)\*100 这个值。当然，如果你能够忍受启动时间比较长，而且希望尽量减少内存至磁盘的flush，可以将这个值调整到90，但不建议超过90

注：以上取值范围仅仅只是我的根据以往遇到的数据库场景所得到的一些优化经验值，并不一定适用于所有场景，所以在实际优化过程中还需要大家自己不断的调整分析，也欢迎大家随时通过 Mail 与我联系沟通交流优化或者是架构方面的技术，一起探讨相互学习。

# Mysql优化总结 #

一、索引  
1、创建索引：  
（1）．ALTER TABLE     
 ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。      
      
 ALTER TABLE table\_name ADD INDEX index\_name (column\_list)     
    
 ALTER TABLE table\_name ADD UNIQUE (column\_list)     
    
 ALTER TABLE table\_name ADD PRIMARY KEY (column\_list)     
    
（2）、CREATE INDEX     
 CREATE INDEX可对表增加普通索引或UNIQUE索引。     
    
 CREATE INDEX index\_name ON table\_name (column\_list)     
    
 CREATE UNIQUE INDEX index\_name ON table\_name (column\_list)    
2、查看索引    
    
 mysql> show index from tblname;     
    
 mysql> show keys from tblname;   
3、删除索引  
 可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE 内部作为一条语句处理，语法如下。    
 DROP INDEX index\_name ON talbe\_name     
    
 ALTER TABLE table\_name DROP INDEX index\_name     
    
 ALTER TABLE table\_name DROP PRIMARY KEY

索引：[http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html][http_www.cnblogs.com_hustcat_archive_2009_10_28_1591648.html]  
\*\*explain +select ·····用来获取select语句的执行的相关信息及索引的使用等  
\*\*describe table table\_name;  
\*\*analyze table table\_name;查看表的信息，帮助优化  
\*\*show 查看执行状态

二、my.ini中的配置  
[http://www.chinaz.com/program/2009/1210/100740.shtml][http_www.chinaz.com_program_2009_1210_100740.shtml]  
mysql > show status; 可以查看具体的设置 服务器的状态  
具体的配置呀什么，没有亲自试验过

三、数据表引擎  
 1、MyISAM：mysql默认的  
 2、InnoDB：支持事务、锁、外键、聚簇索引  
引擎介绍：[http://blog.csdn.net/cheungjustin/article/details/5999880][http_blog.csdn.net_cheungjustin_article_details_5999880]  
 [http://limaolinjia.blog.163.com/blog/static/539162282011012145139/][http_limaolinjia.blog.163.com_blog_static_539162282011012145139]

四、索引的类型：  
 1、B-Tree索引  
 2、hash索引  
具体的参考还是一）

五、事务  
数据表引擎使用InnoDB  
[http://www.cnblogs.com/winner/archive/2011/11/09/2242272.html][http_www.cnblogs.com_winner_archive_2011_11_09_2242272.html]

六、存储过程  
经编译和优化后存储在数据库服务器中，运行效率高，可以降低客户机和服务器之间的通信量，有利于集中控制，易于维护 （P247）  
[http://blog.sina.com.cn/s/blog\_52d20fbf0100ofd5.html][http_blog.sina.com.cn_s_blog_52d20fbf0100ofd5.html]

七、mysql profiling（mysql性能分析器）优化sql语句  
查看SQL执行消耗系统资源的信息  
\++++需要开启+++  
具体使用：[http://www.jiunile.com/mysql-profiling%E7%9A%84%E4%BD%BF%E7%94%A8.html][http_www.jiunile.com_mysql-profiling_E7_9A_84_E4_BD_BF_E7_94_A8.html]

八、慢查询日志  
\++++需要开启++++  
通过慢日志查询可以知道哪些SQL语句执行效率低下，那些sql语句使用的频率高等  
对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。  
具体参考：[http://blog.csdn.net/renzhenhuai/article/details/8839874][http_blog.csdn.net_renzhenhuai_article_details_8839874]

关于mysql的一些讲解：[http://www.ccvita.com/category/mysql][http_www.ccvita.com_category_mysql]

转载于:https://www.cnblogs.com/klb561/p/10704594.html

[0_1291636622rnbL.gif]: /images/20211214/ba61405fda894a4d8d05ce2474adae8c.png
[Link 1]: http://lib.csdn.net/base/mysql
[Link 2]: http://lib.csdn.net/base/hadoop
[0_12916372042dJ3.gif]: /images/20211214/00d3601728884947a6ea5c2519004d4c.png
[Oracle]: http://lib.csdn.net/base/oracle
[0_1291636632fz7h.gif]: /images/20211214/3531dbbb43b24520a577b497ede74b31.png
[0_1291636638QCqG.gif]: /images/20211214/75502e09fa5a4f59a01e4cce404c97b3.png
[0_1291636647TTAA.gif]: /images/20211214/61ce421eaa244535bea20a71bf2bac57.png
[0_12916366512rkn.gif]: /images/20211214/05979bdceb0e4c3b8b1ac482058d566f.png
[0_129163667068Da.gif]: /images/20211214/68c6c6e28ed04225a8260b7506e68b04.png
[Link 3]: http://lib.csdn.net/base/softwaretest
[Link 4]: http://lib.csdn.net/base/datastructure
[SQL]: http://www.dataguru.cn/article-8711-1.html?union_site=innerlink
[MySQL]: http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter
[mcsrainbow]: http://heylinux.com/
[http_www.cnblogs.com_hustcat_archive_2009_10_28_1591648.html]: http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
[http_www.chinaz.com_program_2009_1210_100740.shtml]: http://www.chinaz.com/program/2009/1210/100740.shtml
[http_blog.csdn.net_cheungjustin_article_details_5999880]: http://blog.csdn.net/cheungjustin/article/details/5999880
[http_limaolinjia.blog.163.com_blog_static_539162282011012145139]: http://limaolinjia.blog.163.com/blog/static/539162282011012145139/
[http_www.cnblogs.com_winner_archive_2011_11_09_2242272.html]: http://www.cnblogs.com/winner/archive/2011/11/09/2242272.html
[http_blog.sina.com.cn_s_blog_52d20fbf0100ofd5.html]: http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html
[http_www.jiunile.com_mysql-profiling_E7_9A_84_E4_BD_BF_E7_94_A8.html]: http://www.jiunile.com/mysql-profiling%E7%9A%84%E4%BD%BF%E7%94%A8.html
[http_blog.csdn.net_renzhenhuai_article_details_8839874]: http://blog.csdn.net/renzhenhuai/article/details/8839874
[http_www.ccvita.com_category_mysql]: http://www.ccvita.com/category/mysql