MySQL数据库优化总结

1、对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因 此数据库性能至关重要。一般来说，要保证数据库的效率，要做好以下四个方 面的工作：数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操 作系统，这个顺序也表现了这四个工作对性能影响的大小。下面我们逐个阐明： 一、数据库设计适度的反范式,注意是适度的。我们都知道三范式，基于三范式建立的模型 是最有效保存数 据的方式，也是最容易扩展的模式。我们在开发应用程序时， 设计的数据库要最大程度的遵守三范式，特别是对于OLTP型的系统，三范式是 必须遵守的规则。当然，三范式最大的问题在于查询时通常需要join很多表， 导致查询效率很低

2、。所以有时候基于性能考虑，我们需要有意的违反三范式， 适度的做冗余，以达到提 高查询效率的目的。注意这里的反范式是适度的，必 须为这种做法提供充分的理由。下面就是一个糟糕的实例：在这里，为了提高学生活动记录的检索效率，把单位名称冗余到学生活动 记录表里。单位信息有500 条记录，而学生活动记录在一年内大概有200 万数 据量。如果学生活动记录表不冗余这个单位名称字段，只包含三个int字段和 一个times tamp字段，只占用了 16字节，是一个很小的表。而冗余了一个 varchar(32)的字段后则是原来的3倍，检索起来相应也多了这么多的I/O。而且记录数相差悬殊，500 VS 200000

3、0 ，导致更新一个单位名称还要更新 4000 条冗余记录。由此可见，这个冗余根本就是适得其反。下面这个冗余就很好IILIUIlIfmt Due raitiuiX)n t匸 asill hl lit I讥 帆 hiin. rtfcitaIII.IILIUIlIfmt Due raitiuiX)n t匸 asill hl lit I讥 帆 hiin. rtfcitaIII.-liZJ-IHHt tticbariQ-FILIlXlMp.VI叭Ikl irt dfchlJZ)Y31也利曲曷Msl!LTrtv2a43$i27i 円讯荷1罰柯 litA!fIk!十IHIK庆砒前戌工 j. :1i f J

4、:-f tfw人仙 M-t7ApBS-*iiciEn.i崎虚审:inrl JinZSC)*“也*余mnththt妆nMt2lit-tablit12it”：-梢并1# 联 1 lit lit可以看到，学生考试总分是冗余的，这个分数完全可以通过得分情况 汇总得到。在【学生考试总分】里，一次考试一个学生只有一条记录，而在【得 分情 况】里，一个学生针对试卷里一个小题的一个小问一条记录，粗略的算一 下比例大概是 1：100。而且判卷子得分是不会轻易变的，更新的频率不高，所 以说这个冗余是比较好的。适当建立索引说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改 程序，不用调sql，只要执行

5、个正确的create index，查询速度就可能提高 百倍千倍，这可真有诱惑力。可是天下没有免费的午餐，查询速度的提高是以 插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。由于索引 的存储结构不同于表的存储，一个表的索引所占空间比数据所占空间还大的情 况经常发生。这意味着我们在写数据库的时候做了很多额外的工作，而这个工 作只是为了提高读的效率。因此，我们建立一个索引，必须保证这个索引不会 “亏本”。一般需要遵守这样的规则：（1）索引的字段必须是经常作为查询条件的字段（2）如果索引多个字段，第一个字段要是经常作为查询条件的。如果只有 第二个字段作为查询条件，这个索引不会起到作用;

6、（3）索引的字段必须有足够的区分度;（4）Mysql 对于长字段支持前缀索引。对表进行水平划分如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我 们就有必要化整为零了。如果我拆成100个表，那么每个表只有 10万条记录。 当然这 需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实 现，也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能， 那么把表按月 拆分成12个，每个查询只查询一个表就够了。如果非要按照地域来分，即使把表拆的再小，查询还是要联合所有表来查，还不如不拆了。所以一个好的拆分依据是 最重要的。这里有个比较好的实例学生融题本学半誥颈本掠识是in

7、t班缴标识号int试题标识int人员标识号int学科int题目来源类型int做题时间datetime对错int每个学生做过的题都记录在这个表里，包括对题和错题。每个题会对应一 个或多个知识点，我们需要根据错题来分析学生在哪个知识点上掌握的不足。 这个表很容 易达到千万级，迫切需要拆分，那么根据什么来拆呢?从需求上看， 无论是老师还是学生，最终会把焦点落在一个学生的身上。学生会关心自己， 老师会关心自己班 的学生。而且每个学科的知识点是不同的。所以我们很容易 想到，联合学科和知识点两个字段来拆分这个表。这样拆下来，每个表大概2 万条数据，检索效率非常高。对表进行垂直划分有些表记录数并不多，可能也

8、就2、3 万条，但是字段却很长，表占用空间 很大，检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段 拆分到另一个表，并且该表与原表是一对一的关系。K Re lFeMnce_129F k_Ralatif)nsh ip_d 1fPReialiirtIrtirtrcnar(256j趣&识htltK Re lFeMnce_129F k_Ralatif)nsh ip_d 1fPReialiirtIrtirtrcnar(256j趣&识htlt呗n河题号inr齟 vfrctarOaQ试題內容谀识ini心.齢试题向客 rtJlar(20；4Sqj单元iM册岸息樣识lot单朮潜稀varelta

9、r(12?3irVtWmt冷倍息谍眉見翩世单庾识itivarchan(04)排环int页码irt学科inL适用年城arcriar(643出版社varchar(1 28)版本varcnru 胡】备注3ichsrC256)是昶用ir-il敦祠居Jt救扔示识迥i逊住单元标氓iniml*3intini本踊几同int喘剧程度iniini版删I息aichar(26页码ini开血间ijatmti me题号一狀輛昭2)出题思路w3rchar(40CD呈静音勰ini百銅文件昭咨wanchar(26-K茎音频文佯冻题aiuhar4)文件裕式翹WFCtiarO)挨吏时间datti meini婕51册标碍intiM題

10、状恵iniinr审耐问dateti msvarchard 2旳int选蜩量ihlvarcharGSS)FK_Reiatk)nshiM试题内容】、【答案信息】两个表，最初是作为几个字段添加到【试题信息】里的，可以看到试题内容和答案这两个字段很长，在表里有 3 万记录时，表已经占 了 1G 的空间，在列试题列表时非常慢。经过分析，发现系统很多时候是根据【册】、【单元】、类型、类别、难易程度等查询条件，分页显示试 题详细内容。而每次检索都是这几个表做join，每次要扫描一遍1G的表，很 郁闷啊。我们完全可以把内容和答案拆分成另一个表，只有显示详细内容的时候才读这个大表，由此 就产生了【试题内容】、【

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

12、啊。文件、图片等大文件用文件系统存储，不用数据库不用多说，铁律!数据库只存储路径。外键表示清楚，方便建立索引我们都知道，在 powerdesigner 里为两个实体建立关系，生成物理模型时 会自动给外键建立索引。所以我们不要怕建立关系把线拉乱，建立个 ShortCut 就好了。掌握表的写入时机在库模式相同的情况下，如何使用数据库也对性能有着重要作用。同样是写入一个表，先写和后写对后续的操作会产生很大影响。例如在上面提到的适度冗余里的例子，ttt ih-tHithi 航MthtdAFOib 4制亡4胆) ttt ih-tHithi 航MthtdAFOib 4制亡4胆) hli“ 料厲MlU j|

13、 口! StrAifiaiiierASU clir慎包IfelClFUMdl 占 El diem 归忙殆I怎询lit I卄 lit deeHakS deta Ibl ;1ll-t7 .1 3uhl1L 1-Ml MQr fiiuit2何ur.fl1：EHC00-ntFP 却 iMl 骨SiJffhtJLiht* B-litf j ihi击tin詡員刁fetf我们最初的目的是记录考生的总分，以达到提高检索效率的目的，也就是在录入成绩时写入这个表。在需求里有这样的要求：列出本次考试的所有学生成绩，没有 录入成绩的也显示该学生名称，只是总分显示为空。这个查询就需要用【学生信息】lef tout er

14、 join【学生考试总分信息】，大家都知道outerjoin的效率比join是要低的，为了避免这个问题，我们就在布置考试的时候写入这个表，把所有学生都插入进去，分数都是null，这样一来我们就可以用join达到这个效果了。而且还有这样的好处：在某次考试中，安排了一个班所 有学生考试，所有学生都录入了成绩。现在班里转来一个新生，那么在此时如 果查 询学生成绩，就会列出这个新生，结果是未录入成绩，这显然是不对的。 如果在安排的时候就写入，就可以记录下该次考试中实际的考生了，这个表的 作用，也就不 知是冗余了。宁可集中批量操作，避免频繁读写系统里包含了积分部分，学生和老师通过系统做了操作都可以获得积

15、分， 而且积分规 则很复杂，限制每类操作获得积分不同，每人每天每类积分都有上 限。比如登录，一次登录就可以获得1 分，但是不管你登录多少次，一天只能 累积一个登录积分。 这个还是简单的，有的积分很变态，比如老师积分中有一 类是看老师判作业的情况，规则是：老师判了作业，发现学生有错的，学生改 过了，老师再判，如果这时候 学生都对了，就给老师加分，如果学生还是错的， 那就接着改，知道学生都改对了，老师都判完了，才能给老师加分。如果用程 序来处理，很可能每个功能都会额外 的写一堆代码来处理这个鸡肋似的积分。 不仅编程的同事干活找不到重点，还平白给数据库带来了很大的压力。经过和 需求人员的讨论，确定积

16、分没有必要实时累 积，于是我们采取后台脚本批量处 理的方式。夜深人静的时候，让机器自己玩去吧。这个变态的积分规则用批处理读出来是这样的：select person_id, semester_id, 301003, 0, one_marks, assign_date, one_marksfrom hom_assignmentinfoha, hom_assign_class hacwhere ha.assignment_id = hac.assignment_idand ha.assign_datebetween time_begin and time_enchaa, hom_check_assi

17、gnment hcawhere haa.appraise_id = hca.appraise_id and haa.if_subm it=land hca.check_result in （3003002, 3003003）；这还只是个中间过程，这要是用程序实时处理，即使编程人员不罢工，数 据库也会歇了。选择合适的引擎Mysql 提供了很多种引擎，我们用的最多的是 myisam，innodb，memory 这 三类。官方手册上说道 myisqm 比 innodb 的读速度要 快，大概是 3 倍。不过书 不能尽信啊，Orellly.High.Performance.Mysql这本书里提到了 my

18、isam和 innodb的比 较，在测试中myisam的表现还不及innodb。至于memory，哈哈， 还是比较好用的。在批处理种作临时表是个不错的选择（如果内存够大）。在我 的一个批处理中，速度比近乎 1：10。二、SQL语句优化慢日志如果发现系统慢了，又说不清楚是哪里慢，那么就该用这个工具了。只需 要为 mysql 配置参数， mysql 会自己记录下来慢的 sql 语句。配置很简单，参数 文件里配置：slow_query_log=d:/slow.txtlong_query_time = 2就可以在d:/slow. txt里找到执行时间超过2秒的语句了，根据这个文件 定位问题吧。mysq

19、ldumpslow.pl慢日志文件可能会很大，让人去看是很难受的事。这时候我们可以通过mysql自带的工具来分析。这个工具可以格式化慢日志文件，对于只是参数不同 的语句 会归类类并，比如有两个语句select * from a where id=1和select * from a where id=2,经过这个工具整理后就只剩下select * from a where id=N， 这样读起来就舒服多了。而且这个工具可以实现简单的排序，让我们有的放矢。Explain现在我们已经知道是哪个语句慢了，那么它为什么慢呢?看看mysql是怎么 执行的吧，用explain可以看到mysql执行计划，下面

20、的用法来源于手册EXPLAIN语法（获取SELECT相关信息）EXPLAIN EXTENDED SELECT select_optionsEXPLAIN语句可以用作DESCRIBE的一个同义词，或获得关于MySQL如何执 行SELECT语句的信息： EXPLAIN tbl_name 是 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 的一个同义词。如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。11 该节解释EXPLAIN的第2个用法。借助于EXPLAIN,可以知道什么时候必

21、须为表加入索引以得到一个使用索引 来寻找记录的更快的 SELECT。如果由于使用不正确的索引出现了问题，应运行ANALYZE TABLE更新表的 统计(例如关键字集的势)，这样会影响优化器进行的选择。还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以STRAIGHT_JOIN而不只是 SELECT 开头。EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查 询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接 (single-sweep multi-join)的方式解决所有联接。这意味着MyS

22、QL从第一个表 中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当 所有的 表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹 配行的表。从该表读入下一行并继续处理下一个表。当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS 浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规 则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。如果什么都做不了，试试全索引扫描如果一个语句实在不能优化了，那么还有一个方法可以试试：索引覆盖。如果一个语句可以从索引上获取全部数据，就不需要通过索引再去读表，

23、省了很多I/O。比如这样一个表域柱童J6埠溟写 沁wkK鷄柝说iivt域柱童J6埠溟写 沁wkK鷄柝说iivt濾孫标识i时卓冲irwt如果我要统计每个学生每道题的得分情况，我们除了要给每个表的主键外四pk辽程述s,是站识EiMnshipM4Q君5?琳设号int 后imtinirmIV#4 丫审mni囁肖閣訝幷如C瓦拿】dBcimjIp.Xvar chahJSfipiniinFv*T 京呂饬一扎启朽溟耳inL99別吧h凯竝）取旳坷弓watiav(32)int键建立索引，还要对【得分情况】的实际得分字段索引，这样，整个查询就可 以从索引得到数据了。三、数据库参数配置最重要的参数就是内存，我们主要用

24、的innodb引擎，所以下面两个参数调 的很大Additional memory pool that is used by InnoDB to store metadatainformation. If InnoDB requires more memory for this purpose it willstart to allocate it from the OS. As this is fast enough on mostrecent operating systems, you normally do not need to change this# value. SHOW INN

25、ODB 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# param

26、eter up to 80% of the machine physical memory size. Do notset 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_size14 -Handler_read_key