关于MYSQL 分区表的选择！

huzi1986

现一生产库，MYSQL数据库信息如下

ysql> status
--------------
mysql  Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i386) using readline 4.3

Connection 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 sec

Threads: 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天或者 几十小时！唉，比较头痛

各位，帮忙提提建议！


多谢！

huzi1986

没有人帮忙嘛

seesea2517

select * from check_data_log where dtGetdatetime='2007-04-11 19:53:57';

这里使用的查询条件是否因为dtGetdatetime只是联合索引的一部分，单独用它起不到加快查询速度的作用吧？

seesea2517

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' ));

这里是否可以在选择 check_data_log 的时候做些条件限制，以减少连接的记录数量？

分区方面还没有接触过，现在常用的只是 mysql 4.0，所以还没有什么建设性意见：）

huzi1986

谢谢！这些都考虑过了！继续寻找解决办法

talen-t

你把所有的SQL都贴出来,才知道怎么帮你分区和优化
PS:
select * from check_data_log where dtGetdatetime='2007-04-11 19:53:57';这个用不到索引,所以慢

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' ));

如此复杂的insert成早改掉

huzi1986

谢谢你的回复！我上面的字段有建立联合索引！应该会走索引才对！那个没有办法！