MySQL 数据库优化

       对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。

一般来说，要保证数据库的效率，要做好下面四个方面的工作：sql语句优化及索引、数据库（表）设计、数据库參数配置、恰当的硬件资源和操作系统。这个顺序也表现了这四个工作对性能影响的大小。

以下我们逐个阐明：

    一、SQL语句优化以及索引

　   1. Sql语句优化工具：慢日志： 

        使用MySQL慢查日志对有效率问题的SQL进行监控。

        慢日志分析工具：mysqldumpslow输出

　　假设发现系统慢了，又说不清楚是哪里慢。那么就该用这个工具了。

仅仅须要为mysql配置參数，mysql会自己记录下来慢的sql语句。

配置非常easy。參数文件中配置：

　　slow_query_log=d:/slow.txt

　　long_query_time = 2

　　就能够在d:/slow.txt里找到运行时间超过2秒的语句了，依据这个文件定位问题吧。

　　·mysqldumpslow.pl

慢日志文件可能会非常大。让人去看是非常难受的事。这时候我们能够通过mysql自带的工具来分析。这个工具能够格式化慢日志文件。对于仅仅是參数不同的语句 会归类类并，比方有两个语句select * from a where id=1 和select * from a where id=2，经过这个工具整理后就仅仅剩下select * from a where id=N，这样读起来就舒服多了。

并且这个工具能够实现简单的排序，让我们有的放矢。

       2.Explain

　　如今我们已经知道是哪个语句慢了，那么它为什么慢呢?

看看mysql是怎么运行的吧。用explain能够看到mysql运行计划。以下的使用方法来源于手冊

　　EXPLAIN语法(获取SELECT相关信息)

　　EXPLAIN [EXTENDED] SELECT select_options

　　EXPLAIN语句能够用作DESCRIBE的一个同义词，或获得关于MySQL怎样运行SELECT语句的信息：

　　· EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。

　　· 假设在SELECT语句前放上关键词EXPLAIN，MySQL将解释它怎样处理SELECT，提供有关表怎样联接和联接的次序。

　　该节解释EXPLAIN的第2个使用方法。

　　借助于EXPLAIN，能够知道什么时候必须为表增加索引以得到一个使用索引来寻找记录的更快的SELECT。

　　假设因为使用不对的索引出现了问题，应执行ANALYZE TABLE更新表的统计(比如keyword集的势)。这样会影响优化器进行的选择。

　　还能够知道优化器是否以一个最佳次序联接表。

为了强制优化器让一个SELECT语句依照表命名顺序的联接次序，语句应以STRAIGHT_JOIN而不仅仅是SELECT开头。

　 　EXPLAIN为用于SELECT语句中的每一个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。

MySQL用一遍扫描多次联 接(single-sweep multi-join)的方式解决全部联接。这意味着MySQL从第一个表中读一行。然后找到在第二个表中的一个匹配行。然后在第3个表中等等。当全部的 表处理完后，它输出选中的列而且返回表清单直到找到一个有很多其它的匹配行的表。从该表读入下一行并继续处理下一个表。

　　当使用EXTENDEDkeyword时，EXPLAIN产生附加信息，能够用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写而且运行优化规则后SELECT语句是什么样子。而且还可能包含优化过程的其他注解。

　  

       3.假设什么都做不了，试试全索引扫描

　　假设一个语句实在不能优化了，那么另一个方法能够试试：索引覆盖。

　　假设一个语句能够从索引上获取所有数据。就不须要通过索引再去读表。省了非常多I/O。比方这样一个表        

　　假设我要统计每一个学生每道题的得分情况。我们除了要给每一个表的主键外键建立索引，还要对【得分情况】的实际得分字段索引。这样，整个查询就能够从索引得到数据了。

      
       二、数据库（表）设计

　　适度的反范式,注意是适度的

　　我们都知道三范式。基于三范式建立的模型是最有效保存数 据的方式。也是最easy扩展的模式。我们在开发应用程序时，设计的数据库要最大程度的遵守三范式，特别是对于OLTP型的系统。三范式是必须遵守的规则。

当 然。三范式最大的问题在于查询时通常须要join非常多表，导致查询效率非常低。

所以有时候基于性能考虑。我们须要有意的违反三范式，适度的做冗余，以达到提 高查询效率的目的。注意这里的反范式是适度的，必须为这样的做法提供充分的理由。以下就是一个糟糕的实例：　　

　 　在这里，为了提高学生活动记录的检索效率。把单位名称冗余到学生活动记录表里。单位信息有500条记录，而学生活动记录在一年内大概有200万数据量。 假设学生活动记录表不冗余这个单位名称字段。仅仅包括三个int字段和一个timestamp字段。仅仅占用了16字节，是一个非常小的表。而冗余了一个 varchar(32)的字段后则是原来的3倍。检索起来对应也多了这么多的I/O。并且记录数相差悬殊，500 VS 2000000 。导致更新一个单位名称还要更新4000条冗余记录。由此可见，这个冗余根本就是适得其反。

　　以下这个冗余就非常好　　

　 　能够看到，[学生考试总分]是冗余的，这个分数全然能够通过[得分情况]汇总得到。在【学生考试总分】里。一次考试一个学生仅仅有一条记录，而在【得分情 况】里。一个学生针对试卷里一个小题的一个小问一条记录，粗略的算一下比例大概是1：100。并且判卷子得分是不会轻易变的，更新的频率不高。所以说这个 冗余是比較好的。

       1.适当建立索引

　　说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，仅仅要运行个正确的’create index’，查询速度就可能提高百倍千倍。这可真有诱惑力。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作。添加了大量的I/O。因为索引的存储结构不同于表的存储。一个表的索引所占空间比数据所占空间还大的情况常常发生。这意味着我们在写数据库的时候做了非常多额外的工作。而这个工作仅仅是为了提高读的效率。因此，我们建立一个索引。必须保证这个索引不会“亏本”。一般须要遵守这种规则：

　　索引的字段必须是常常作为查询条件的字段;

　　假设索引多个字段。第一个字段要是常常作为查询条件的。假设仅仅有第二个字段作为查询条件，这个索引不会起到作用;

　　索引的字段必须有足够的区分度;

　　Mysql 对于长字段支持前缀索引;

　　2.对表进行水平划分

　 　表的水平划分是为了解决单表的数据量过大的问题。水平划分的表每个表的结构都是完毕一致的。

       假设一个表的记录数太多了，比方上千万条，并且须要常常检索。那么我们就有必要化整为零了。

假设我拆成100个表。那么每一个表仅仅有10万条记录。

当然这 须要数据在逻辑上能够划分。

一个好的划分根据。有利于程序的简单实现。也能够充分利用水平分表的优势。

比方系统界面上仅仅提供按月查询的功能，那么把表按月 拆分成12个。每一个查询仅仅查询一个表就够了。假设非要依照地域来分，即使把表拆的再小，查询还是要联合全部表来查，还不如不拆了。所以一个好的拆分根据是 最重要的。

　　这里有个比較好的实例        

　 　每一个学生做过的题都记录在这个表里，包含对题和错题。

每一个题会相应一个或多个知识点。我们须要依据错题来分析学生在哪个知识点上掌握的不足。这个表非常容 易达到千万级，迫切须要拆分，那么依据什么来拆呢?从需求上看，不管是老师还是学生，终于会把焦点落在一个学生的身上。学生会关心自己。老师会关心自己班 的学生。

并且每一个学科的知识点是不同的。所以我们非常easy想到，联合学科和知识点两个字段来拆分这个表。这样拆下来，每一个表大概2万条数据，检索效率非常 高。

    　3.对表进行垂直划分

       所谓的垂直拆分。就是把原来一个有非常多列的表拆分成多个表，这攻克了表的宽度问题。通常垂直拆分能够按一下原则进行：

        (1)把不经常使用的字段单独存放到一个表中；

        (2)把大字段独立存放到一个表中。

        (3)把常常一起使用的字段放到一起。  　　

        有些表记录数并不多，可能也就2、3万条。可是字段却非常长，表占用空间非常大。检索表时须要运行大量I/O，严重减少了性能。这个时候须要把大的字段拆分到还有一个表，而且该表与原表是一对一的关系。        

　 　【试题内容】、【答案信息】两个表。最初是作为几个字段加入到【试题信息】里的，能够看到试题内容和答案这两个字段非常长，在表里有3万记录时，表已经占 了1G的空间，在列试题列表时非常慢。经过分析。发现系统非常多时候是依据【冊】、【单元】、类型、类别、难易程度等查询条件。分页显示试题具体内容。而每 次检索都是这几个表做join，每次要扫描一遍1G的表。非常郁闷啊。我们全然能够把内容和答案拆分成还有一个表，仅仅有显示具体内容的时候才读这个大表，由此 就产生了【试题内容】、【答案信息】两个表。

      4. 选择适当的字段类型，特别是主键

　　选择字段的一般原则是保小不保大，能用占用字节小的字段就不用大字段。比方主键， 我们强烈建议用自增类型。不用guid。为什么?省空间啊?空间是什么?空间就是效率!按4个字节和按32个字节定位一条记录，谁快谁慢太明显了。涉及到 几个表做join时，效果就更明显了。值得一提的是，datetime和timestamp，datetime占用8个字节，而timestamp占用4 个字节，仅仅用了一半，而timestamp表示的范围是1970—2037，对于大多数应用，尤其是记录什么考试时间。登录时间这类信息。绰绰有余啊。

　　5.文件、图片等大文件用文件系统存储，不用数据库

　　不用多说。铁律!!!数据库仅仅存储路径。

　　6.外键表示清楚，方便建立索引

　　我们都知道，在powerdesigner里为两个实体建立关系，生成物理模型时会自己主动给外键建立索引。所以我们不要怕建立关系把线拉乱。建立个ShortCut就好了。

　　7.掌握表的写入时机

　　在库模式相同的情况下，怎样使用数据库也对性能有着重要作用。相同是写入一个表，先写和后写对兴许的操作会产生非常大影响。

比如在上面提到的适度冗余里的样例。        

　 　我们最初的目的是记录考生的总分，以达到提高检索效率的目的。也就是在录入成绩时写入这个表。

在需求里有这种要求：列出本次考试的全部学生成绩，没有 录入成绩的也显示该学生名称，仅仅是总分显示为空。这个查询就须要用【学生信息】left outer join 【学生考试总分信息】，大家都知道outer join 的效率比join是要低的，为了避免这个问题，我们就在布置考试的时候写入这个表。把全部学生都插入进去。分数都是null，这样一来我们就能够用 join达到这个效果了。并且还有这种优点：在某次考试中。安排了一个班全部学生考试。全部学生都录入了成绩。如今班里转来一个新生，那么在此时假设查 询学生成绩，就会列出这个新生，结果是未录入成绩，这显然是不正确的。

假设在安排的时候就写入。就能够记录下该次考试中实际的考生了，这个表的作用，也就不 知是冗余了。

　   8.宁可集中批量操作，避免频繁读写

　　系统里包括了积分部分。学生和老师通过系统做了操作都能够获得积分，并且积分规 则非常复杂。限制每类操作获得积分不同。每人每天每类积分都有上限。比方登录。一次登录就能够获得1分。可是无论你登录多少次，一天仅仅能累积一个登录积分。

这个还是简单的，有的积分非常变态，比方老师积分中有一类是看老师判作业的情况，规则是：老师判了作业。发现学生有错的。学生改过了，老师再判，假设这时候 学生都对了，就给老师加分，假设学生还是错的，那就接着改，知道学生都改对了。老师都判完了。才干给老师加分。假设用程序来处理，非常可能每一个功能都会额外 的写一堆代码来处理这个鸡肋似的积分。不仅编程的同事干活找不到重点。还平白给数据库带来了非常大的压力。经过和需求人员的讨论，确定积分没有必要实时累 积，于是我们採取后台脚本批量处理的方式。夜深人静的时候。让机器自己玩去吧。

　　这个变态的积分规则用批处理读出来是这种：　　

      

1 select person_id, @semester_id, 301003, 0, @one_marks, assign_date, @one_marks

2          from hom_assignmentinfo   ha, hom_assign_class hac

3          where ha.assignment_id = hac.assignment_id

4               and ha.assign_date between @time_begin and @time_end

5               and ha.assignment_id not in

6                    (

7                         select haa.assignment_id from hom_assignment_appraise haa, hom_check_assignment hca

8                          where haa.appraise_id = hca.appraise_id and haa.if_submit=1

9                               and (

10                                      (hca.recheck_state = 3004001 and hca.check_result in (3003002, 3003003) )

11                                       or

12                                      (hca.recheck_state = 3004002 and hca.recheck_result in (3003002, 3003003))

13                                    )

14                    )

15               and ha.assignment_id not in

16                    (

17                         select assignment_id from hom_assignment_appraise where if_submit=0 and result_type = 0

18                    )

19               and ha.assignment_id in     

20                    (

21                         select haa.assignment_id from hom_assignment_appraise haa, hom_check_assignment hca

22                          where haa.appraise_id = hca.appraise_id and haa.if_submit=1

23                               and hca.check_result in (3003002, 3003003)

24                    );

 

　　这还仅仅是个中间过程。这要是用程序实时处理，即使编程人员不罢工，数据库也会歇了。

　　9.选择合适的引擎

　 　Mysql提供了非常多种引擎。我们用的最多的是myisam，innodb，memory这三类。

官方手冊上说道myisqm比innodb的读速度要 快，大概是3倍。

只是书不能尽信啊。《OreIlly.High.Performance.Mysql》这本书里提到了myisam和innodb的比 较，在測试中myisam的表现还不及innodb。

至于memory。哈哈，还是比較好用的。在批处理种作暂时表是个不错的选择(假设内存够大)。在我的一个批处理中，速度比近乎1：10。

 

　三、数据库參数配置

      数据库是基于OS的，眼下大多数MySQL都是安装在Linux系统之上的，所以对于OS的一些參数配置也会影响MySQL的性能。

     

      

       最重要的參数就是内存，我们主要用的innodb引擎，所以以下两个參数调的非常大

　　# Additional memory pool that is used by InnoDB to store metadata

　　# information. If InnoDB requires more memory for this purpose it will

　　# start to allocate it from the OS. As this is fast enough on most

　　# recent operating systems, you normally do not need to change this

　　# value. SHOW INNODB STATUS will display the current amount used.

　　innodb_additional_mem_pool_size = 64M

　　# 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 = 5G

　　对于myisam，须要调整key_buffer_size

　　当然调整參数还是要看状态，用show status语句能够看到当前状态，以决定改调整哪些參数

　　Cretated_tmp_disk_tables 添加tmp_table_size

　　Handler_read_key 高表示索引正确 Handler_read_rnd高表示索引不对

　　Key_reads/Key_read_requests 应小于0.01 计算缓存损失率，添加Key_buffer_size

　　Opentables/Open_tables 添加table_cache

　　select_full_join 没有有用索引的链接的数量。假设不为0，应该检查索引。

　　select_range_check 假设不为0。该检查表索引。

　　sort_merge_passes 排序算法已经运行的合并的数量。假设该值较大，应添加sort_buffer_size

　　table_locks_waited 不能马上获得的表的锁的次数。假设该值较高，应优化查询

　　Threads_created 创建用来处理连接的线程数。假设Threads_created较大，要添加 thread_cache_size值。

　　缓存訪问率的计算方法Threads_created/Connections。

　

      四、合理的硬件资源和操作系统

　　假设你的机器内存超过4G，那么毋庸置疑应当採用64位操作系统和64位mysql，成本高效果不明显。

　　读写分离

　　假设数据库压力非常大。一台机器支撑不了，那么能够用mysql复制实现多台机器同步，将数据库的压力分散。

　　

　　Master

　　Slave1

　　Slave2

　　Slave3

　　 主库master用来写入，slave1—slave3都用来做select。每一个数据库分担的压力小了非常多。

　 　要实现这样的方式，须要程序特别设计，写都操作master，读都操作slave，给程序开发带来了额外负担。当然眼下已经有中间件来实现这个代理。对程 序来读写哪些数据库是透明的。官方有个mysql-proxy。可是还是alpha版本号的。

新浪有个amobe for mysql，也可达到这个目的。结构例如以下　　

　　用法能够看amobe的手冊。