TA的每日心情![](source/plugin/dsu_paulsign/img/emot/kx.gif) | 开心 2012-3-7 10:15 |
---|
签到天数: 11 天 [LV.3]偶尔看看II
|
现一生产库,MYSQL数据库信息如下ysql> status
--------------
mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i386) using readline 4.3Connection id: 19212
Current database: information_schema
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.14-beta-log
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: utf8
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /data/mysql/mysql.sock
Uptime: 4 days 9 hours 24 min 2 secThreads: 130 Questions: 508391673 Slow queries: 606 Opens: 966 Flush tables: 1 Open tables: 959 Queries per second avg: %
--------------
此数据库服务器中有一库,里面有两个分区表,一个分区表的数据特别大,而且数据是时实更新,现数据文件是50多GB,索引文件90多GB。采用的是HASH分区
表的结构如下| check_data_log | CREATE TABLE `check_data_log` (
`nCheckdataLogID` int(11) NOT NULL AUTO_INCREMENT,
`nCheckpointID` int(11) DEFAULT NULL,
`fValue` float(10,2) DEFAULT NULL,
`dtGetdatetime` datetime DEFAULT NULL,
`nCheckOK` int(1) DEFAULT NULL,
`SubClassWord` varchar(20) DEFAULT NULL,
`ValueWord` varchar(20) DEFAULT NULL,
KEY `idx_id` (`nCheckdataLogID`),
KEY `idx_key` (`nCheckpointID`,`dtGetdatetime`)
) ENGINE=MyISAM AUTO_INCREMENT=2117556084 DEFAULT CHARSET=gb2312 /*!50100 PARTITION BY HASH (nCheckpointID) PARTITIONS 300 */ |
索引如下
mysql> show index from mo.check_data_log;
+----------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| check_data_log | 1 | idx_id | 1 | nCheckdataLogID | A | 2109589671 | NULL | NULL | | BTREE | |
| check_data_log | 1 | idx_key | 1 | nCheckpointID | A | 11440 | NULL | NULL | YES | BTREE | |
| check_data_log | 1 | idx_key | 2 | dtGetdatetime | A | 2109589671 | NULL | NULL | YES | BTREE | |
+----------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)现在单查询没有什么问题,比如
select * from check_data_log limit 1000之类的语句如果用时间来做查询的话,就非常慢
因为这个表是个监控表,很多设备每十秒钟生成一次数据往里面插入数据!
如下面的语句select * from check_data_log where dtGetdatetime='2007-04-11 19:53:57';
现在想采用range 来分区,表的大小请看附件数据库是REDHAT 5.1 I386 准备迁移到64位平台上面,现在这个表中有20多亿条数据,我想在想查询我想我的数据把他过滤出来,根本无法查出来!用了子查询 insert into mo_new.check_data_log ( select distinct t1.nCheckdataLogID,t1.nCheckpointID,t1.fValue,t1.dtGetdatetime,t1.nCheckOK,t1.SubClassWord,t1.ValueWord from check_data_log t1 left join checkpoint t2 on t2.ncheckpointid where t2.nunitid in ( '7','12','13','19','21','22','45' ));
数据库有些优化参数还没有设置!刚接手没有多长时间,现在通过WEB访问生成某一时间段的图形非常慢,就象上面,我查询某一时间的都不行,别说WEB 页面上的1天或者 几十小时!唉,比较头痛各位,帮忙提提建议!
多谢!
|
|