分类: Mysql/postgreSQL
2008-05-08 09:40:52
原来在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)到多个数据库中,以分担负载,提高系统的整体性能与效率。