MySQL数据库优化总结 - 数据库编程

限制每类操作获得积分不同，每人每天每类积分都有上限。比如登录，一次登录就可以获得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 );

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

选择合适的引擎

Mysql提供了很多种引擎，我们用的最多的是myisam，innodb，memory这三类。官方手册上说道myisqm比innodb的读速度要快，大概是3倍。不过书不能尽信啊，《OreIlly.High.Performance.Mysql》这本书里提到了myisam和innodb的比较，在测试中myisam的表现还不及innodb。至于memory，哈哈，还是比较好用的。在批处理种作临时表是个不错的选择(如果内存够大)。在我的一个批处理中，速度比近乎1：10。

二、SQL语句优化

Sql语句优化工具

·慢日志

如果发现系统慢了，又说不清楚是哪里慢，那么就该用这个工具了。只需要为mysql配置参数，mysql会自己记录下来慢的sql语句。配置很简单，参数文件里配置：

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，这样读起来就舒服多了。而且这个工具可以实现简单的排序，让我们有的放矢。

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更新表的统计(例如关键字集的势)，这样会影响优化器进行的选择。

还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句

MySQL数据库优化总结(二)