但是没什么效果,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)到多个数据库中,以
分担负载,提高系统的整体性能与效率。
数据散列可以考虑采用federeated或sharded方式,网上有不少这方面的资料。
一些小技巧
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建立了外键关系。
评论0