Oracle、mysql产品性能优化总结

我不是女神ヾ 2022-10-12 04:29 654阅读 0赞

### Oracle、mysql产品性能优化总结 ###

###                                         李万鸿2018-8-7 ###

###  ###

###   数据库是产品的一大性能瓶颈，oracle、mysql等数据库的查询速度直接影响产品的性能指标。通过多年的多次实战优化oracle、mysql产品，找到了内在的原理和规律，取得了明显的成效，这里做个总结，希望对大家高效正确使用数据库有所帮助。 ###

### 数据库性能调整目标： ###

###   改善指定类型的SQL语句的性能 ###

###   改善专门的数据库应用的性能 ###

###   改善所有同时应用数据库的用户及其应用的所有性能 ###

### **1 索引的建立和使用** ###

### **1原理与方法** ###

###  数据库的查询速度慢是产品性能不达标的主要原因，大多数产品都是读多写少，而提高查询速度的关键在于索引，因此合理设置表的索引是优化的一个核心任务。建立索引可避免全表扫描，快速定位数据，从而提高查询速度。 ###

### 数据库执行selelct查询，首先会生成执行计划，根据where的查询字段到数据库选取数据，然后join连接、group、order排序。这是数据库的基本原理。Oracle和mysql的sql语句执行顺序相反，Oracle是从右到左，mysql是从左到右。为了提高查询速度，需要把最容易判断出的条件放在查询的最前面，这就是建立索引和使用索引的原理和规律。 ###

### 为此，在建立索引时，需要综合考虑这张表所对应的sql语句，查询条件有哪些字段，各个字段的类型、取值大小，表的读写情况，然后根据上面的原理，选取字段建立索引。索引的顺序很重要，原则是取值越少的越放在前面，比如validflag，一般取值为0和1，那么通过这个字段，可以很快判断一半的数据，效率很高，所以放在最前面，其余与此类似。 ###

### 对于where、group、order条件都需要建立索引，group、order要单独建索引。事务是辩证的，凡事有利就有弊，索引利于查询，但不利于写入数据，因此，要把握好这个矛盾，权衡利弊达到最佳效果。索引的数量不易过多，一般建4到5个，每条索引的字段不要超过6个，具体情况根据需求和使用来定，法无定法，因地制宜，随机应变。这样可以在保证写入速度的同时提高查询速度，达到优化的全局整体效果最高。 ###

### 索引建立后就需要根据索引来修改设sql的查询字段顺序，Oracle和mysql的sql语句执行顺序相反，sql条件的写法顺序相反。根据oracle的sql执行顺序规律，sql查询条件的执行是从右到左的，因此，查询条件的顺序与索引顺序相反。Mysql的查询条件的顺序与索引顺序相同。根据字段取值大小，索引的规律是从小到大， Oracle和mysql都遵循此规律；mysql查询的顺序与索引相同，Oracle查询的顺序与索引相反。这样可以简单明了地建立和使用索引。 ###

### 一般，索引的第一个字段为查询条件的第一个字段，便于使用索引查询。Oracle9I后的版本，会自动根据查询字段在所有索引中查找，所以查询的第一个字段可以与索引不同。而mysql没有此功能，因此mysql查询的第一个字段必须与索引的第一个字段相同，后面的顺序要一致，这样才能用到索引，不会进行全表扫描。索引的类型为normal，不要设为唯一unique类型，否则引起插入错误。唯一索引是根据业务需求而定的，会产生唯一性的约束，normal索引不会。唯一索引的速度是最快的。 ###

### 对于特别慢的复杂sql，可以使用explain分析sql，找到耗时的问题点，加以优化。优化后要进行全面压测，保证整个产品所有性能达标，如果有不达标的，要综合分析后再对索引进行调整。 ###

### 索引的优化越早越好，因为索引是关系到全局的，一张表的索引的变动会影响到成百上千的sql的变动，因此，在设计和使用数据库及成品开发的前期阶段，就要合理设计和正确使用索引。这样可以保证产品的性能一开始就是最优，有效提升产品的质量和开发效率。 ###

### **2操作步骤** ###

###   完成以上优化并不难，可通过简单有效的操作进行，一般的工程师都可以实现。对于一张表建索引，可以采用以下办法来逐步完成： ###

1.  ### 找到表所用到的所有sql的查询条件的字段，并加以分析，确定使用频度，可以用word画一张表来统计。 ###
2.  ### 根据查询条件和字段合理决定建几个索引，包含哪些字段。这一部很关键，直接影响到查询的性能，索引的数量和包含字段的数量都要合理，不是越多越好，应该尽量精简。 ###
3.  ### 为了解索引效果，可以运行一条复杂的sql语句，使用pl/sql或navicat工具运行sql，看第一次查询的速度。查询后重复查询会快很多，因为已经被缓存了，所以只能看第一次查的询时间才能了解真实效果。可修改查询字段的顺序进行查询，比较效果，通过多次试验探索从而决定最佳的设计。 ###
4.  ### 索引建好后，需修改用到这张表的所有sql查询条件，按照相同或相反的顺序调整查询字段顺序，然后测试运行，评估效果。 ###
5.  ### 全面修改完毕，需进行系统全面压测，评估读写整体性能指标，达到全局最优化。 ###

### **2 表和字段的创建** ###

### 索引优化只是数据库性能优化的一部分，表和字段的设计是根本，一个设计合理的数据库，需要在准确把握客户需求的基础上，科学划分表及表的字段。一般，表和字段越少越好，满足三范式，表之间用键关联，不要设外键，不利于表的使用。表的字段数量要控制好，在50个以内。 ###

### 表需要扩展，一般都设置很多预留字段，这个问题可以优化。有2个新的方法： ###

1.  ### 建立一张扩展表保存所有的扩展字段，extension（id，table\_Id，extension \_field，extension \_field\_name,field \_value），需要扩展的表正需要增加一个扩展字段extension \_field。如果有表需要增加字段，比如user表，可以在user表增加一个扩展字段extension \_field，以后需增加补贴薪资类目，新增数据时，在user的扩展字段extension \_field中保存Subsidized wages，然后在extension表插入（id，user表的id，“Subsidized wages”，“补贴薪资”，3000），这样就完成了新增一个字段，查询时通过user表的id和Subsidized wages从extension表即刻获取数据。这个办法也很灵活，可以无限扩展。 ###
2.  ###  使用json，建立一个扩展字段，类型为text，有需要增加的扩展字段，采用key：value的json方式存入，非常灵活，可以无限扩展。以上面为例，key为Subsidized wages，value为\{补贴薪资，3000\}。 ###
3.  ### 对于表的数据量过大，可以采用分库、分区、分表的方法解决。Mycat是mysql水平分表的利器，可以自动扩展分表，实现读写分离、主从集群、自动完成分页、join等复杂操作，值得使用。 ###

### **3 Sql语句优化** ###

###    Sql查询的关键在于先查出量小的数据再去查询量大的数据，用小表驱动大表，尽量在主表使用最多的查询条件，降低主表查询的数据，这是优化复杂sql的秘诀。复杂sql涉及多张表的join，主表的选择至关重要，一定要选数据量小的表为主表，而且主表的查询条件尽量多，尽量把能提前到主表的查询条件放在主表的where里面，这样一开始的查询其数据量可以最少，然后在join关联其他表的数据就会很快，显著提升速度，起到化腐朽为神奇的奇迹效果。 ###

### 举个例子： ###

###  ###

### 这个sql运行很慢，需几十秒，看执行计划： ###

###  ###

1.  ### 整个执行计划的COST并不高； ###
2.  ### 纵观整个执行计划，并没有出现SORT操作。那么ORDER BY又是怎么实现的呢？ ###

### 在执行计划中，有如下一个操作： ###

### INDEX FULL SCAN DESCENDING的对象是PK\_CCP\_CROSS\_DATA，查看了下这个对象： ###

### 这个UNIQUE INDEX的字段对象是CCP\_CORSS\_DATA\_ID，而该字段就是ORDER BY的字段对象。查看了下该表的数据量，接近4000万， ###

### 在分页ORDER BY时出现INDEX FULL SCAN DESCENDING的操作。 ###

### 优化方法：等价改写后的SQL如下： ###

###  ###

### 再看执行计划： ###

###  ###

### 成本下降，从之前的560变成了249，超过了一半。降低成本仅仅是优化的手段，优化的目的是让SQL跑得更快，所以真正考验SQL等价改写效果的是SQL的执行效率。 ###

### 将改写后的SQL在数据库中执行：0.7s，风驰电掣，飞速提升！ ###

### 成功的秘密就在于在开始就使用查询条件把关键查询的数量降低了，只有15条数据，后面再join就很轻松了： ###

###  ###

###   **一、操作符优化** ###

### **1、IN 操作符** ###

### 用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别： ###

### ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。 ###

### **推荐方案：**在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。 ###

### **2、NOT IN操作符** ###

### 此操作是强列不推荐使用的，因为它不能应用表的索引。 ###

### **推荐方案：**用NOT EXISTS 方案代替 ###

### **3、IS NULL 或IS NOT NULL操作**（判断字段是否为空） ###

### 判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。 ###

### **推荐方案**：用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。 ###

### **4、> 及 < 操作符（大于或小于操作符）** ###

### 大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。 ###

### **5、LIKE操作符** ###

### LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。 ###

### 一个实际例子：用YW\_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY\_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY\_BH LIKE ’X5400%’ OR YY\_BH LIKE ’B5400%’ 则会利用YY\_BH的索引进行两个范围的查询，性能肯定大大提高。 ###

### **6、UNION操作符** ###

### UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：   select \* from gc\_dfys   union   select \* from ls\_jg\_dfys   这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。 ###

### **推荐方案：**采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。 ###

### select \* from gc\_dfys   union all   select \* from ls\_jg\_dfys ###

### 7.join ###

### **J****oin****的on条件不为空，left join****、****right join****等****join的****连接****查询走索引****，****Join** **的****on  ****只要连接条件，****其余的放在where。** ###

### **二、SQL书写的影响** ###

### **1、同一功能同一性能不同写法SQL的影响。** ###

### 如一个SQL在A程序员写的为  Select \* from zl\_yhjbqk ###

### B程序员写的为 Select \* from dlyx.zl\_yhjbqk（带表所有者的前缀） ###

### C程序员写的为 Select \* from DLYX.ZLYHJBQK（大写表名） ###

### D程序员写的为 Select \*  from DLYX.ZLYHJBQK（中间多了空格） ###

### 以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。 ###

### **2、WHERE后面的条件顺序影响** ###

### WHERE子句后面的条件顺序对[**大数据**][Link 1]量表的查询会产生直接的影响。如：   Select \* from zl\_yhjbqk where dy\_dj = '1KV以下' and xh\_bz=1   Select \* from zl\_yhjbqk where xh\_bz=1 and dy\_dj = '1KV以下'   以上两个SQL中dy\_dj（电压等级）及xh\_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy\_dj = '1KV以下'条件在记录集内比率为99%，而xh\_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy\_dj及xh\_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy\_dj及xh\_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。 ###

### **3、查询表顺序的影响** ###

### 在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下，ORACLE会按表出现的顺序进行链接，由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接） ###

### **三、SQL语句索引的利用** ###

### **1、操作符优化（同上）** ###

### **2、对条件字段的一些优化** ###

### **采用函数处理的字段不能利用索引，**如： ###

### substr(hbs\_bh,1,4)=’5400’，优化处理：hbs\_bh like ‘5400%’ ###

### trunc(sk\_rq)=trunc(sysdate)， 优化处理：sk\_rq>=trunc(sysdate) and sk\_rq<trunc(sysdate+1) ###

### 进行了显式或隐式的运算的字段不能进行索引，如：ss\_df+20>50，优化处理：ss\_df>30 ###

### ‘X’ || hbs\_bh>’X5400021452’，优化处理：hbs\_bh>’5400021542’ ###

### sk\_rq+5=sysdate，优化处理：sk\_rq=sysdate-5 ###

### hbs\_bh=5401002554，优化处理：hbs\_bh=’ 5401002554’，注：此条件对hbs\_bh 进行隐式的to\_number转换，因为hbs\_bh字段是字符型。 ###

### **条件内包括了多个本表的字段运算时不能进行索引**，如：ys\_df>cx\_df，无法进行优化   qc\_bh || kh\_bh=’5400250000’，优化处理：qc\_bh=’5400’ and kh\_bh=’250000’ ###

### **四、其他** ###

### 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 ###

### 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：   select id from t where num is null   可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：   select id from t where num=0 ###

### 3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 ###

### 4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：   select id from t where num=10 or num=20   可以这样查询：   select id from t where num=10   union all   select id from t where num=20 ###

### 5.in 和 not in 也要慎用，否则会导致全表扫描，如：   select id from t where num in(1,2,3)   对于连续的数值，能用 between 就不要用 in 了：   select id from t where num between 1 and 3 ###

### 6.下面的查询也将导致全表扫描：   select id from t where name like '%abc%'   若要提高效率，可以考虑全文检索。 ###

### 7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：   select id from t where [num=@num][num_num] ###

### 可以改为强制查询使用索引：   select id from t with(index(索引名)) where [num=@num][num_num] ###

### 8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：   select id from t where num/2=100   应改为:   select id from t where num=100\*2 ###

### 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：   select id from t where substring(name,1,3)='abc' // oracle总有的是substr函数。   select id from t where datediff(day,createdate,'2005-11-30')=0 //查过了确实没有datediff函数。   应改为:   select id from t where name like 'abc%'   select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' //    oracle 中时间应该把char 转换成 date 如： createdate >= to\_date('2005-11-30','yyyy-mm-dd') ###

### 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 ###

### 11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 ###

### 12.不要写一些没有意义的查询，如需要生成一个空表结构：   select col1,col2 into \#t from t where 1=0   这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：   create table \#t(...) ###

### 13.很多时候用 exists 代替 in 是一个好的选择：   select num from a where num in(select num from b)   用下面的语句替换：   select num from a where exists(select 1 from b where num=a.num) ###

### 14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。 ###

### 15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过5个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。 ###

### 16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。 ###

### 17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。 ###

### 18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。 ###

### 19.任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段。 ###

### 20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。 ###

### 21.避免频繁创建和删除临时表，以减少系统表资源的消耗。 ###

### 22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。 ###

### 23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。 ###

### 24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 ###

### 25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。 ###

### 26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。 ###

### 27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST\_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。 ###

### 28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE\_IN\_PROC 消息。 ###

### 29.尽量避免大事务操作，提高系统并发能力。 ###

### 30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。 ###

### ORACLE的提示功能是比较强的功能，也是比较复杂的应用，并且提示只是给ORACLE执行的一个建议，有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用，一般不建议开发人员应用ORACLE提示，因为各个数据库及服务器性能情况不一样，很可能一个地方性能提升了，但另一个地方却下降了，ORACLE在SQL执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。 ###

### **4 数据库参数优化** ###

### 数据库系统的参数需要根据硬件和操作系统及使用情况进行优化设置，实践表明，优化前后，性能可以提升几十到上百倍。 ###

1.  ### **oracle优化** ###

### **数据库安装时的优化** ###

### 在数据库安装时作的优化工作主要是关于db\_block\_size参数的设置，该参数决定了ORACLE每次操作多少的数据。该参数在安装时一经确认就不能修改，除非重新安装数据库。一般来说，一个中型的应用系统的db\_block\_size大小可设为4K，而对于一个较大型应用，db\_block\_size一般设为8K或更大。 ###

### 一个较大的db\_block\_size，不仅可以加快系统的运行速度（因为从系统的I/O吞吐能力来说，一次性读取较多的数据可以减少I/O的读取次数），而且可以有较大的系统扩展能力（对于一个系统而言，在它的db\_block\_size确认之后，它的最大扩展数其实也已经确定下来，而如果一个系统的扩展能力有限的话，则系统容易发生诸如ORA-01562表空间无法扩展之类的错误）。 ###

### 注：在ORACLE7.3之后的版本中，在创建表空间时，有一个参数autoextent，如果这个参数设置为ON，则在达到最大的扩展值时自动扩展，而不再受最大扩展数的限制。Db\_block\_size和最大扩展数的对应关系如下： ###

<table style="margin-left:3.15pt;"> 
 <tbody> 
  <tr> 
   <td style="background-color:#ffffff;width:89.4pt;"> <h3 style="text-align:left;"><strong><span style="color:#333333;"><strong>DB_BLOCK_SIZE</strong></span></strong></h3> </td> 
   <td style="background-color:#ffffff;width:78.6pt;"> <h3 style="text-align:left;"><strong><span style="color:#333333;"><strong>MAX EXTENTS</strong></span></strong></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:89.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">512BYTES</span></h3> </td> 
   <td style="background-color:#ffffff;width:78.6pt;"> <h3 style="text-align:left;"><span style="color:#333333;">25</span></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:89.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">1K</span></h3> </td> 
   <td style="background-color:#ffffff;width:78.6pt;"> <h3 style="text-align:left;"><span style="color:#333333;">57</span></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:89.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">2K</span></h3> </td> 
   <td style="background-color:#ffffff;width:78.6pt;"> <h3 style="text-align:left;"><span style="color:#333333;">121</span></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:89.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">4K</span></h3> </td> 
   <td style="background-color:#ffffff;width:78.6pt;"> <h3 style="text-align:left;"><span style="color:#333333;">249</span></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:89.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">8K</span></h3> </td> 
   <td style="background-color:#ffffff;width:78.6pt;"> <h3 style="text-align:left;"><span style="color:#333333;">505</span></h3> </td> 
  </tr> 
 </tbody> 
</table>

### **初始化参数的修改** ###

###        初始化参数的修改参见1.3.2，值得注意的是需要许多参数之间存在关联，需要根据实际需要进行调整（如lm\_ress、lm\_proc、dlm\_locks等参数相互关联）；同时许多参数和操作系统核心参数相关，需要先对操作系统核心参数进行修改（如process参数和OS的信号量、最大进程数相关等）。 ###

### **优化存储设计** ###

### 存储设计的原则为：把由用户创建的其余表空间同SYSTEM表空间进行分离，把系统的数据表空间同索引表空间分离，把操作频繁和不经常操作的表划分在不同的表空间中；控制文件和重作日志文件尽可能的分配在不经常使用的磁盘上。 ###

### a．表空间的设计原则 ###

### l  把用户数据与数据字典数据分开 ###

### l  把不同应用组的数据分别放在不同的表空间中 ###

### l  为了减少I/O的竞争，把不同表空间的数据文件放在不同的硬盘上 ###

### l  分离用户数据和回滚段的数据以防止某个磁盘出现故障丢失数据 ###

### l  控制数据的可用性，例如使一些表空间online而另一些表空间offline ###

### l  为特殊类型的数据库使用保留某个表空间，例如高频率的更新操作，只读操作或临时段的存储等 ###

### l  索引空间同数据表空间分离 ###

### l  建议每一个表空间对应的数据文件的大小应小于2G（某些操作系统不支持大于2G的文件；可以提高备份的速度及其安全性） ###

### l  创建临时表空间时指定temporary选项 ###

### b．回滚段设计原则 ###

*  ### 回滚段使用规则 ###

### l  一个事务的回退信息可以写到在线的任何的一个回滚段中 ###

### l  一个事务的所有回退信息只能写入一个回滚段中 ###

### l  多个事务的回退信息可以同时写入同一个回滚段中，甚至同一个extent中 ###

### l  回滚段的extent中的所有事务都被提交或回退了，这个extent就可以被重新使用 ###

### l  一旦回滚段的某个extent被重新使用，则这个extent中所有的信息都不可用 ###

### l  在系统需要更多的回滚空间时，如果回滚段的下一个extent中有活动事务，例程就会扩展此回滚段 ###

### l  SELECT、INSERT、UPDATE和DELETE语句都可能会使数据库从多个回滚段去读数据 ###

### l  回滚段的头永远不会移入当前被回滚的事务为所占用的extent中 ###

### l  当回滚段的头进行扩展时，它是按顺序的，不会跳过环中的extent ###

### l  如果回滚段的头不能使用下一个extent，它就会另外分配一个EXTENT，并把它插入到环中 ###

*  ### SQL语句产生的回退量 ###

### 用于存储回退信息所需的字节数是由两个方面决定的：事务的类型（如INSERT、UPDATE、DELETE等）和被处理的实际数据。一般来说，插入一条记录比删除同样一条记录产生的回退要少，这是因为当回退一个插入时只需删除该记录，而回退一个删除时则需重新插入那条记录（存储一个ROWID比存储用来重构该记录本身所需的字节数要少）。到目前为止，还没有公式可以计算一条SQL语句所产生的回退量，DBA只能监测到一条SQL语句实际产生了多少回退信息。ORACLE公司根据测试得到的SQL产生的回退量如下： ###

<table style="margin-left:3.15pt;"> 
 <tbody> 
  <tr> 
   <td style="background-color:#ffffff;width:79.2pt;"> <h3 style="text-align:left;"><span style="color:#333333;">记录数</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">插入产生的回退量</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">删除产生的回退量</span></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:79.2pt;"> <h3 style="text-align:left;"><span style="color:#333333;">1000</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">61949</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">114290</span></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:79.2pt;"> <h3 style="text-align:left;"><span style="color:#333333;">10000</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">621456</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">1143029</span></h3> </td> 
  </tr> 
 </tbody> 
</table>

### 此外，更改一条记录的一部分比更改整条记录所产生的回退信息要多。请看下面所列出的数据信息： ###

<table style="margin-left:3.15pt;"> 
 <tbody> 
  <tr> 
   <td style="background-color:#ffffff;width:79.2pt;"> <h3 style="text-align:left;"><span style="color:#333333;">记录数</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">更改3列产生的回退量</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">更改8列产生的回退量</span></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:79.2pt;"> <h3 style="text-align:left;"><span style="color:#333333;">1000</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">81192</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">101192</span></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:79.2pt;"> <h3 style="text-align:left;"><span style="color:#333333;">10000</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">821472</span></h3> </td> 
   <td style="background-color:#ffffff;width:113.4pt;"> <h3 style="text-align:left;"><span style="color:#333333;">1014383</span></h3> </td> 
  </tr> 
 </tbody> 
</table>

### 可以通过对V$ROLLSTAT视图查询回滚段的使用情况。 ###

*  ### 回滚段设计原则 ###

### l  对于OLTP应用，回滚段的设计原则为：使用大量（30～50个）的小回滚段（例如initial 128k，next 128k，minextents 10，optimal 2m），设置初始化参数中的transaction\_per\_rollback\_segment=6～10以减少回滚段的竞争。 ###

### l  对于批处理操作，回滚段的设计原则是：使用少量（<10个）的大回滚段（例如initial 10m，next 10m，minextents 10，optimal 100m）以避免出现诸如ORA-01562、ORA-01555之类的错误影响应用正常运行。 ###

### l  对于混合型应用（大量的OLTP应用及少量批处理应用），回滚段设计原则是使用大量在线的小回滚段结合少量离线的大回滚段。一旦需要进行批处理操作，使用以下语句使用大回滚段： ###

### *svrmgrl > alter rollback segment r01 online;* ###

### *svrmgrl > set transaction use rollback segment r01;* ###

### *svrmgrl > SQL** **语句* ###

### *svrmgrl > alter rollback segment r01 offline;* ###

### 在PL/SQL中，可以使用ORACLE的包进行回滚段的指定。 ###

### **系统设计优化** ###

### a．数据库块结构 ###

### 在ORACLE数据库中，数据的存储是以块（block）为基本单位，块的大小即数据库创建时确定的db\_block\_size的大小。多个块构成一个扩展区域（extent），多个扩展区域构成一个段（segment）。 ###

### 数据块的结构如下所示： ###

<table style="margin-left:3.15pt;"> 
 <tbody> 
  <tr> 
   <td style="background-color:#ffffff;width:244.8pt;"> <h3 style="text-align:left;"><span style="color:#333333;">公用的变长标题（common and variable header）&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>&nbsp;<strong><u><span style="color:#333333;"><strong><u>A</u></strong></span></u></strong></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:244.8pt;"> <h3 style="text-align:left;"><span style="color:#333333;">表目录（table directory）&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>&nbsp;<strong><u><span style="color:#333333;"><strong><u>B</u></strong></span></u></strong></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:244.8pt;"> <h3 style="text-align:left;"><span style="color:#333333;">行目录（row directory）&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>&nbsp;<strong><u><span style="color:#333333;"><strong><u>C</u></strong></span></u></strong></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:244.8pt;"> <h3 style="text-align:left;"><span style="color:#333333;">未用空间（free space）&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>&nbsp;<strong><u><span style="color:#333333;"><strong><u>D</u></strong></span></u></strong></h3> </td> 
  </tr> 
  <tr> 
   <td style="background-color:#ffffff;width:244.8pt;"> <h3 style="text-align:left;"><span style="color:#333333;">行数据（row data）&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>&nbsp;<strong><u><span style="color:#333333;"><strong><u>E</u></strong></span></u></strong></h3> </td> 
  </tr> 
 </tbody> 
</table>

### l  A包含一般的块信息，如块地址、段的类型（数据段、索引段或回滚段等） ###

### l  B包含所存放的表的信息 ###

### l  C包含块中的行信息（如行数据区中的每一行的行地址）。 ###

### l  D包含可用于插入新行及修改 ###

### l  E包含在表或索引数据。需要的附加空间的行或额外的事物头。 ###

### b．表设计 ###

### l  PCTFREE和PCTUSED ###

### 在建表时用到关于存储空间的参数是PCTFREE和PCTUSED，这两个参数构成了每一数据块的总体构成：PCTFREE参数为块中保留的未用空间百分比，用于修改块中已存在行；PCTUSED为当一数据块被填满后（由PCTFREE决定），新行不能被插入该块，只有块的使用率低于PCTUSED时，才可插入，在达到PCTUSED值之前，块中的未用空间仅用于修改行；PCTUSED和PCTFREE值的总和不能超过100， PCTFEE参数尤为重要。 ###

### 注：一个数据库表中的每一条记录以一行片（ROW PIECE）或多个行片形式存储：如果一个整行可插入到单个数据块中，那该行初始存储为一个行片；如果不能插入到单个数据块或者由于对已存在的行进行修改时引起超出该块，则可能会发生行链接和行迁移（行链接------ROW CHAINED：一条记录使用多个行片进行存储，若数据在一个数据块中无法存放，而在其余块中也无法找到有足够的空间存放该行数据，则将发生行链接。行迁移------ROW MIGRATION：如果在其余块中能找到足够的空间存放该行的数据，那么，这一整行的数据就全部转移到另一能够容纳下该行数据的块中即称为行迁移）。行迁移导致数据从一个数据块移到另一数据块，加大了系统I/O负担；行链接则导致对于数据的查找将进行跨块查询，也大大地增加了系统的I/O负担。 ###

### 如果PCTFREE参数的设置太小，将导致行链接问题。使用以下语句可以查找哪些表发生了行链接问题： ###

### *Analyze table table\_name list chained rows into chained\_rows；* ###

### 执行该操作前需执行?/rdbms/admin/utlchain.sql脚本创建CHAINED\_ROWS表。如果确认系统存在大量的行链接，需要对这些数据进行处理（将发生行链接的数据存放在临时表中；删除这部分数据；修改表的PCTFREE值；再把临时表的数据插入到表中）。 ###

### l  INITRANS ###

### ORACLE在对表或索引进行操作之前，对每一数据块的修改，都要在块中申请一个事务项（事务项值的大小依赖于OS）。INITRANS参数保证可同时并发修改块的最小事务数，它避免动态事务项的开销，INITRANS参数太小则在ORACLE中并发操作多时可能导致锁升级（行级锁à块级锁）。 ###

### l  STORAGE ###

### ORACLE公司建议表的扩展次数控制在5个以下，因此需要根据实际的数据量计算所需要的表大小设置合适的INITIAL和NEXT存储策略；建议PCTINCREASE设为0以避免诸如表空间无法扩展等错误。 ###

### l  对表的空间大小的确定 ###

### 对表所需空间大小可以进行如下的估算（db\_block\_size=2k）： ###

### *max(4,ceil(ROW\_COUNT/((round((1958-(initrans\*23))\*((100-PCT\_FREE)/100))/ADJ\_ROW\_SIZE))))\*2* ###

### 其中： ###

### n  一个块的实际可用字节数为1958bytes ###

### n  每个INITRANS使用23bytes ###

### n  PCT\_FREE为建表时要指定的值 ###

### n  表中每行估计的已调试的行大小（ADJ\_ROW\_SIZE） ###

### n  表的估计行数（ROW\_COUNT） ###

### n  ceil取加1的整数，round进行四舍五入 ###

### 对于已有数据的旧的数据库结构中，对表占用空间大小的计算公式如下： ###

### *select avg(nvl(vsize(col1),0)) +…+ avg(nvl(vsize(coln),n)) from table\_name;* ###

### 通过对现有的建表策略及数据量的分析，可以确定一个良好的建表策略以提高应用的效率及空间的利用率。 ###

### c．索引设计 ###

### l  索引的存储参数同表的存储参数设置 ###

### l  创建索引的限制 ###

### 一个索引最多可包含16列，索引项为每一列的数据值的连串，按指定的列的顺序连串。这一顺序对ORACLE如何使用该索引非常重要。ORACLE在一张表上可建立多个索引，索引的数目没有限制。但是应该注意增加索引会增加维护表所需的处理时间。建议只有当要索引的数据在表中所占的数据量，少于总数据量的25%时，使用索引才会提高查询的速度，否则对表的全表扫描的速度将比使用索引的速度快。 ###

### l  索引空间大小的确认 ###

### *max(4,1.01\*(ROW\_COUNT/((floor(((2048-113-(initrans\*23))\*(1-(pct\_free/100)))/((10+uniqueness)+number\_col\_index+(total\_col\_length))))))\*2)* ###

### 其中： ###

### n  表的估计行数（ROW\_COUNT） ###

### n  块的实际可用行数（1935或2048-113） ###

### n  为索引指定的自由百分比（PCT\_FREE） ###

### n  索引是否是唯一的，对字节数是有影响的（如果索引是唯一的，UNIQUENESS变量为1，否则为0） ###

### n  索引的列数（NUMBER\_COL\_INDEX） ###

### 索引的估计长度（NUMBER\_COL\_LENGTH） ###

1.  ### **mysql优化** ###

###    在 MySQL 5.7 中，实际上只有 4 个重要的选项需要去调整，还有其他的 InnoDB 或者全局的 MySQL 选项可能需要根据特定的负载和硬件来做出调整。 ###

### 首先，在 f 中的\[mysqld\] 节下面添加如下配置。配置完成之后，需要重启 MySQL 服务： ###

### \[mysqld\]   \# other variables here   innodb\_buffer\_pool\_size = 1G \# (adjust value here, 50%-70% of total RAM)   innodb\_log\_file\_size = 256M   innodb\_flush\_log\_at\_trx\_commit = 1 \# may change to 2 or 0   innodb\_flush\_method = O\_DIRECT ###

### 说明： ###

### innodb\_buffer\_pool\_size ###

### 设置为 RAM 大小的 50%-70%，不需要大于数据库的大小 ###

### innodb\_flush\_log\_at\_trx\_commit ###

### 1（默认值），0/2 （性能更好，但稳定性更差） ###

### innodb\_log\_file\_size ###

### 128M – 2G (不需要大于 buffer pool) ###

### innodb\_flush\_method ###

### O\_DIRECT (避免双缓冲技术) ###

### 下一步 ###

### 对于新安装的实例而言，那些设置都是很好的起点。还有许多其他的选项，在某些场景下，可以提高 MySQL 的性能。通常，我会部署一套 MySQL 监控/图形工具（例如，Percona 监控语句管理平台，俗称：PMMM），然后根据 MySQL 监控面板提供的信息来执行进一步的调优。 ###

### 根据监控图表，我们能进一步做什么样的优化? ###

### InnoDB 缓冲池大小的信息，看下面这个图： ###

###  ###

### 从上面可以看出，可用的 RAM 和空闲的页的数量小于缓冲池的总大小，我们可以把 InnoDB 缓冲池的大小增加到 10G 从而获得更好的性能。 ###

### InnoDB redo 日志的大小的信息，看下面这个图： ###

###  ###

### 可以看到，InnoDB 通常每小时写 2.26GB 的数据，这已经超出了 redo 日志的大小（2G）。现在我们可以增大 innodb\_log\_file\_size 选项，然后重启 MySQL。另外，使用 "show engine innodb status" 命令来计算一个合适的 InnoDB redo 日志的大小值。 ###

### 其他选项 ###

### 有很多其他的 InnoDB 选项可以进一步调整： ###

### 设置 innodb\_autoinc\_lock\_mode = 2 (interleaved mode) 可以去掉表级 AUTO-INC 锁的必要性（还可以提高在使用多行插入语句来插入数据到拥有自增主键的表的时候的性能）。这要求 binlog\_format=ROW 或者 MIXED（在 MySQL 5.7 中，默认值是 ROW）。 ###

### innodb\_io\_capacity 和 innodb\_io\_capacity\_max ###

### 这是一个更加高级的调优，只有当你在频繁写操作的时候才有意义（它不适用于读操作，例如 SELECTs）。若你真的需要对它进行调整，最好的方法是要了解系统可以支持多大的 IOPS。譬如，假设服务器有一块 SSD 硬盘，我们可以设置 innodb\_io\_capacity\_max=6000 和 innodb\_io\_capacity=3000（最大值的一半）。运行 sysbench 或者任何其他基准工具来对磁盘吞吐量来进行基准测试是一个好方法。 ###

### 然而，我们需要去担心这个选项吗？看下面这张缓冲池的"脏页" ###

###  ###

### 在这种情况下，脏页的总量很大，而且看起来 InnoDB 刷新脏页的速度跟不上脏页的速度。如果我们有一个高速的磁盘子系统（例如：SSD），可以增加 innodb\_io\_capacity 和 innodb\_io\_capacity\_max 来得到更好的性能。 ###

### 总结（太长,请不要看版本） ###

### 新的 MySQL 5.7 配置项的默认值对于一般的负载更加友好。与此同时，我们仍然需要配置 InnoDB 的选项来发挥出大内存的优势。   安装完毕之后，遵循以下步骤来操作： ###

### 如上所述，在 f 中配置 InnoDB 选项，并重启 MySQL 服务 ###

### 部署一套监控系统（例如：Percona 监控语句管理平台） ###

### 通过监控图形来判断是否对 MySQL 进一步调优。\[client\]   no-beep ###

### \# pipe   \# socket=0.0   port=3306 ###

### \[mysql\] ###

### default-character-set=utf8 ###

###    \# SERVER SECTION   \# ----------------------------------------------------------------------   \#   \# The following options will be read by the MySQL Server. Make sure that   \# you have installed the server correctly (see above) so it reads this    \# file.   \#   \# server\_type=3   \[mysqld\] ###

### \# The next three options are mutually exclusive to SERVER\_PORT below.   \# skip-networking ###

### \# enable-named-pipe ###

### \# shared-memory ###

### \# shared-memory-base-name=MYSQL ###

### \# The Pipe the MySQL Server will use   \# socket=MYSQL ###

### \# The TCP/IP Port the MySQL Server will listen on   port=3306 ###

### \# Path to installation directory. All paths are usually resolved relative to this.   \# basedir="C:/Program Files/MySQL/MySQL Server 5.7/"   basedir = "C:\\ProgramData\\MySQL\\MySQL Server 5.7"   \# Path to the database root   datadir="C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Data"   tmpdir = "C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Data"   socket = "C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Data\\mysql.sock" ###

## ##

### \# The default character set that will be used when a new schema or table is   \# created and no character set is defined   character-set-server=utf8 ###

### \# The default storage engine that will be used when create new tables when   default-storage-engine=INNODB ###

### explicit\_defaults\_for\_timestamp = true   \# Set the SQL mode to strict   sql-mode="STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION" ###

### \# Enable Windows Authentication   \# plugin-load=authentication\_windows.dll ###

### \# General and Slow logging.   log-output=FILE   general-log=1   general\_log\_file="MS-20150605JTJQ.log"   slow-query-log=1   slow\_query\_log\_file="MS-20150605JTJQ-slow.log"   long\_query\_time=10 ###

### \# Binary Logging.   log-bin="MS-20150605JTJQ-bin" ###

### \# Error Logging.   log-error="MS-20150605JTJQ.err" ###

### \# Server Id.   server-id=1 ###

### \# The maximum amount of concurrent sessions the MySQL server will   \# allow. One of these connections will be reserved for a user with   \# SUPER privileges to allow the administrator to login even if the   \# connection limit has been reached.   max\_connections=2000 ###

### \# Query cache is used to cache SELECT results and later return them   \# without actual executing the same query once again. Having the query   \# cache enabled may result in significant speed improvements, if your   \# have a lot of identical queries and rarely changing tables. See the   \# "Qcache\_lowmem\_prunes" status variable to check if the current value   \# is high enough for your load.   \# Note: In case your tables change very often or if your queries are   \# textually different every time, the query cache may result in a   \# slowdown instead of a performance improvement.   query\_cache\_size=0 ###

### \# The number of open tables for all threads. Increasing this value   \# increases the number of file descriptors that mysqld requires.   \# Therefore you have to make sure to set the amount of open files   \# allowed to at least 4096 in the variable "open-files-limit" in   \# section \[mysqld\_safe\]   table\_open\_cache=2000 ###

### \# Maximum size for internal (in-memory) temporary tables. If a table   \# grows larger than this value, it is automatically converted to disk   \# based table This limitation is for a single table. There can be many   \# of them.   tmp\_table\_size=246M ###

### \# How many threads we should keep in a cache for reuse. When a client   \# disconnects, the client's threads are put in the cache if there aren't   \# more than thread\_cache\_size threads from before.  This greatly reduces   \# the amount of thread creations needed if you have a lot of new   \# connections. (Normally this doesn't give a notable performance   \# improvement if you have a good thread implementation.)   thread\_cache\_size=300 ###

### \#限定用于每个[**数据库**][Link 2]线程的栈大小。默认设置足以满足大多数应用   thread\_stack = 192k   \#\*\*\* MyISAM Specific options   \# The maximum size of the temporary file MySQL is allowed to use while   \# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.   \# If the file-size would be bigger than this, the index will be created   \# through the key cache (which is slower).   \#myisam\_max\_sort\_file\_size=100G ###

### \# If the temporary file used for fast index creation would be bigger   \# than using the key cache by the amount specified here, then prefer the   \# key cache method.  This is mainly used to force long character keys in   \# large tables to use the slower key cache method to create the index.   \#myisam\_sort\_buffer\_size=37M ###

### \# Size of the Key Buffer, used to cache index blocks for MyISAM tables.   \# Do not set it larger than 30% of your available memory, as some memory   \# is also required by the OS to cache rows. Even if you're not using   \# MyISAM tables, you should still set it to 8-64M as it will also be   \# used for internal temporary disk tables.   key\_buffer\_size=512M ###

### \# Size of the buffer used for doing full table scans of MyISAM tables.   \# Allocated per thread, if a full scan is needed.   read\_buffer\_size=4M   read\_rnd\_buffer\_size=32M ###

### \#\*\*\* INNODB Specific options \*\*\*   \# innodb\_data\_home\_dir=0.0 ###

### \# Use this option if you have a MySQL server with InnoDB support enabled   \# but you do not plan to use it. This will save memory and disk space   \# and speed up some things.   \# skip-innodb ###

### innodb\_data\_home\_dir = "C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Data\\"   \# If set to 1, InnoDB will flush (fsync) the transaction logs to the   \# disk at each commit, which offers full ACID behavior. If you are   \# willing to compromise this safety, and you are running small   \# transactions, you may set this to 0 or 2 to reduce disk I/O to the   \# logs. Value 0 means that the log is only written to the log file and   \# the log file flushed to disk approximately once per second. Value 2   \# means the log is written to the log file at each commit, but the log   \# file is only flushed to disk approximately once per second.   innodb\_flush\_log\_at\_trx\_commit=0 ###

### \# The size of the buffer InnoDB uses for buffering log data. As soon as   \# it is full, InnoDB will have to flush it to disk. As it is flushed   \# once per second anyway, it does not make sense to have it very large   \# (even with long transactions).   innodb\_log\_buffer\_size=16M ###

### \# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and   \# row data. The bigger you set this the less disk I/O is needed to   \# access data in tables. On a dedicated database server you may set this   \# parameter up to 80% of the machine physical memory size. Do not set it   \# too large, though, because competition of the physical memory may   \# cause paging in the operating system.  Note that on 32bit systems you   \# might be limited to 2-3.5G of user level memory per process, so do not   \# set it too high.   innodb\_buffer\_pool\_size=256M ###

### \# Size of each log file in a log group. You should set the combined size   \# of log files to about 25%-100% of your buffer pool size to avoid   \# unneeded buffer pool flush activity on log file overwrite. However,   \# note that a larger logfile size will increase the time needed for the   \# recovery process.   innodb\_log\_file\_size=128M ###

### \# Number of threads allowed inside the InnoDB kernel. The optimal value   \# depends highly on the application, hardware as well as the OS   \# scheduler properties. A too high value may lead to thread thrashing.   innodb\_thread\_concurrency=128 ###

### \# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.   innodb\_autoextend\_increment=1000 ###

### \# The number of regions that the InnoDB buffer pool is divided into.   \# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,   \# by reducing contention as different threads read and write to cached pages.   innodb\_buffer\_pool\_instances=8 ###

### \# Determines the number of threads that can enter InnoDB concurrently.   innodb\_concurrency\_tickets=5000 ###

### \# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before   \# it can be moved to the new sublist.   innodb\_old\_blocks\_time=1000 ###

### \# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.   innodb\_open\_files=300 ###

### \# When this variable is enabled, InnoDB updates statistics during metadata statements.   innodb\_stats\_on\_metadata=0 ###

### \# When innodb\_file\_per\_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table   \# in a separate .ibd file, rather than in the system tablespace.   innodb\_file\_per\_table=1 ###

### \# Use the following list of values: 0 for crc32, 1 for strict\_crc32, 2 for innodb, 3 for strict\_innodb, 4 for none, 5 for strict\_none.   innodb\_checksum\_algorithm=0 ###

### \# The number of outstanding connection requests MySQL can have.   \# This option is useful when the main MySQL thread gets many connection requests in a very short time.   \# It then takes some time (although very little) for the main thread to check the connection and start a new thread.   \# The back\_log value indicates how many requests can be stacked during this short time before MySQL momentarily   \# stops answering new requests.   \# You need to increase this only if you expect a large number of connections in a short period of time.   back\_log=80 ###

### \# If this is set to a nonzero value, all tables are closed every flush\_time seconds to free up resources and   \# synchronize unflushed data to disk.   \# This option is best used only on systems with minimal resources.   flush\_time=0 ###

### \# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use   \# indexes and thus perform full table scans.   join\_buffer\_size=128M ###

### \# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the   \# mysql\_stmt\_send\_long\_data() C API function.   max\_allowed\_packet=1024M ###

### \# If more than this many successive connection requests from a host are interrupted without a successful connection,   \# the server blocks that host from performing further connections.   max\_connect\_errors=2000 ###

### \# Changes the number of file descriptors available to mysqld.   \# You should try increasing the value of this option if mysqld gives you the error "Too many open files".   open\_files\_limit=4161 ###

### \# Set the query cache type. 0 for OFF, 1 for ON and 2 for DEMAND.   query\_cache\_type=0 ###

### \# If you see many sort\_merge\_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the   \# sort\_buffer\_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization   \# or improved indexing.   sort\_buffer\_size=32M ###

### \# The number of table definitions (from .frm files) that can be stored in the definition cache.   \# If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.   \# The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.   \# The minimum and default values are both 400.   table\_definition\_cache=1400 ###

### \# Specify the maximum size of a row-based binary log event, in bytes.   \# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.   binlog\_row\_event\_max\_size=8K ###

### \# If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk.   \# (using fdatasync()) after every sync\_master\_info events.   sync\_master\_info=10000 ###

### \# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.   \# (using fdatasync()) after every sync\_relay\_log writes to the relay log.   sync\_relay\_log=10000 ###

### \# If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk.   \# (using fdatasync()) after every sync\_relay\_log\_info transactions.   sync\_relay\_log\_info=10000 ###

### \#批量插入数据缓存大小，可以有效提高插入效率，默认为8M   bulk\_insert\_buffer\_size = 64M ###

### interactive\_timeout = 120   wait\_timeout = 120   log-bin-trust-function-creators=1 ###

### ** 5数据库分区** ###

### 此文从以下几个方面来整理关于分区表的概念及操作: ###

###         1.表空间及分区表的概念 ###

###         2.表分区的具体作用 ###

###         3.表分区的优缺点 ###

###         4.表分区的几种类型及操作方法 ###

###         5.对表分区的维护性操作. ###

### (1.) 表空间及分区表的概念 ###

### 表空间：   　　是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表， 所以称作表空间。 ###

## ##

### 分区表：    当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 ###

## ##

### ( 2).表分区的具体作用 ###

### Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。 ###

## ##

### 分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。 ###

## ##

### 什么时候使用分区表：   1、表的大小超过2GB。 ###

### 2、表中包含历史数据，新的数据被增加都新的分区中。 ###

## ##

###  (3).表分区的优缺点 ###

### 表分区有以下优点：    1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 ###

### 2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； ###

### 3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； ###

### 4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。 ###

## ##

### 缺点：    分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。 ###

## ##

### (4).表分区的几种类型及操作方法 ###

###  一.范围分区：   范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。 ###

### 当使用范围分区时，请考虑以下几个规则： ###

### 1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。 ###

### 2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。 ###

### 3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。 ###

###  ###

### **6产品性能优化方法** ###

###   **性能优化需要全面考虑产品的各个方面，包括****java****前端、****.Net****前端、****Java****代码、****.Net****代码、数据库、****Sql****、****Iis****、****Tomcat****、****Windows****、****Linux****、网络、服务器配置等经验，分析产品的耗时点，找到正确的原因，从而采取合适的办法进行优化。架构师在设计时要考虑性能问题，遵循****“****全面系统****思考，深入细致分析，反复权衡利弊，最佳方案决策”****的****原则，****正确****分析和采取行动，****确保系统的性能。项目经理要重点抓性能，督促QA、工程师做好性能优化。工程师在开发时，要进行性能优化工作，并自己测试性能，保证性能的指标，然后再提交测试。** ###

### **优化****内容为前端、后台代码、sql、数据库、****操作系统****、中间件****（tomcat****、websphere****）****、****配置****参数****、****缓存****、****多线程、异步并行****、****消息****等****方面。****这些****内容是一个紧密联系的整体，****牵一发****而动全局，****比如数据库****底层变动会****引起****代码的****变动****，****从而****提升性能****。** ###

### **要****抓住问题的关键点，****查找原因****所在，****然后****有的放矢地进行优化。****比如****查询、新增、提交电子流很慢，****原因****在于查询语句慢，****这****有****2个****原因：****1前端****调用了****20多次****查询，****2****sql查询慢。****找到了****原因，再进行优化就容易****了。方法****是****减少****了前端调用，****使用****了页面缓存解决****；****优化了****索引和****sql对索引的查询顺序，****测试****顺利达标。** ###

### **索引****的****优化****方法是****通过****实战探索出来的****，网上没有****，连oracle公司的官方资料都没有提到，****对****大家的优化工作将会起到巨大的帮助。****数据库****的参数和索引的优化完全可以****由****数据库系统本身****完成****，****现在****大数据和****AI人工智能****技术非常成熟，****数据库****系统应该采用这些技术，****通过****对****表****、数据****和****sql的情况分析，自动进行智能优化，把工程师解放出来，****为****产品开发和运行提供****坚固****的基石。** ###

### **《孙子兵法》曰****：****兵无常势****，****以上****方法和原理要举一反三地灵活运用****，根据****不同的业务需求****合理****决策，****正确****使用以上方法，****一定****会柳暗花明又一村****，****性能指标更上一层楼。****目前****所有的产品都可以通过以上方法****优化****，全面提升性能****，价值非凡。** ###

###  ###

[Link 1]: http://lib.csdn.net/base/hadoop
[num_num]: mailto:num=@num
[Link 2]: http://lib.csdn.net/base/mysql