数据库中对前端和后台进行系统优化

作者:网络 来源:佚名 更新时间:2009-01-06 13:36:07 点击:

本文中介绍的系统优化,主要针对前端和后台这两方面(后台方面主要对sql语句和数据存储进行了优化),下文中我们将介绍一些优化技巧和经验。

技巧:

1. 如何查出效率低的语句?

在mysql下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的sql语句。你也可以在启动配置文件中修改long query的时间,如:

# set long query time to 8 seconds

long_query_time=8

2. 如何查询某表的索引?

可使用show index语句,如:

show index from [表名]

3. 如何查询某条语句的索引使用情况?

可用explain语句来看一下某条select语句的索引使用情况。如果是update或delete语句,需要先转换为select语句。
4. 如何把导出innodb引擎的内容到错误日志文件中?

我们可以使用show innodb status命令来查看innodb引擎的很多有用的信息,如当前进程、事务、外键错误、死锁问题和其它一些统计数据。如何让该信息能记录在日志文件中 呢?只要使用如下语句创建innodb_monitor表,mysql就会每15秒钟把该系统写入到错误日志文件中:

create table innodb_monitor (a int) engine=innodb;

如果你不再需要导出到错误日志文件,只要删除该表即可:

drop table innodb_monitor;

5. 如何定期删除庞大的日志文件?

只要在启动配置文件中设置日志过期时间即可:

expire_logs_days=10

注意事项:

1. 重点关注索引

下面以表tsk_task表为例说明sql语句优化过程。tsk_task表用于保存系统监测任务,相关字段及索引如下:

id:主键;

mon_time:监测时间;建了索引;

status_id:任务状态;与sys_hier_info.id建立了外键关系。

注mysql自动会为外键建立索引,在本次优化过程中,发现这些自动建立的外键索引会对sql语句的效率产生不必要的干扰,需要特别注意!

首先,我们在日志文件中查到下面语句的执行比较慢,超过10秒了:

# query_time: 18 lock_time: 0 rows_sent: 295 rows_examined: 88143

select * from tsk_task where status_id = 1064 and mon_time >= '2007-11-22' and mon_time < '2007-11-23';

原来在88143条记录中要查出符合条件的295条记录,那当然慢了。赶紧用explain语句看一下索引使用情况吧:

+----+-------------+----------+------+----------

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |

+----+-------------+----------+------+-----------

| 1 | simple | tsk_task | ref | fk_task_status_id_to_sys_hier_info,tsk_task_key_mon_time | fk_task_status_id_to_sys_hier_info | 9 | const | 276168 | using where |

+----+-------------+----------+------+-----------

可以看出,有两个索引可用fk_task_status_id_to_sys_hier_info,tsk_task_key_mon_time,而最终执行语句时采用了status_id上的外键索引。

再看一下tsk_task表的索引情况吧:

+----------+------------------------------------

| table | key_name | column_name | cardinality |

+----------+------------+-----------------------

| tsk_task | primary | id | 999149 |

| tsk_task | fk_task_status_id_to_sys_hier_info | status_id | 16 |

| tsk_task | tsk_task_key_mon_time | mon_time | 13502 |

+----------+------------------------------------

在oracle或其他关系数据库下,where条件中的字段顺序对索引的选择起着很重要的作用。我们调整一下字段顺序,把status_id放在后面,再explain一下:

explain select * from tsk_task where mon_time >= '2007-11-22' and mon_time < '2007-11-23' and status_id = 1064;

但是没什么效果,mysql还是选用系统建立的status_id外键索引。

仔细分析一下,看来cardinality属性(即索引中的唯一值的个数)对索引的选择起了极其重要的作用,mysql选择了索引值唯一值个数小的那个索引作为整条语句的索引。

针对这条语句,如果使用fk_task_status_id_to_sys_hier_info做索引,而tsk_task表中存放很多天数据的话,那扫描的记录数会很多,速度较慢。可以有以下几个优化方案:

如果一天的任务数不多的话,我们删除索引fk_task_status_id_to_sys_hier_info,那mysql会使用索引tsk_task_key_mon_time,然后在该天的数据中在扫描status_id为1064的记录,那速度也不慢;

如果一天的任务数多的话,我们需删除索引fk_task_status_id_to_sys_hier_info和tsk_task_key_mon_time,然后再建立status_id,mon_time的联合索引,这样效率肯定会很高。

因此建议,对那些记录数多的表,建议不要使用外键,以避免造成性能效率的严重降低。

2. 尽量控制每张表的记录数

当一张表的记录数很大时,管理和维护就会很麻烦,如索引维护就会占用很长时间,从而会给系统的正常运行造成很大的干扰。

对随时间推移数据量不断增长的表,我们可以根据时间来区分实时数据和历史数据,可以使用后台服务程序定期移动实时表中的数据到历史表中,从而控制实时表的 记录数,提高查询和操作效率。但注意每次移动的时间要足够短,不要影响正常程序的数据写入。如果占用时间太长,可能会造成死锁问题。

3. 数据散列(partition)策略

当客户数达到一定规模后,单个数据库将无法支撑更高的并发访问,此时可以考虑把客户数据散列(partition)到多个数据库中,以分担负载,提高系统的整体性能与效率。