我在一个MariaDB数据库中有一个表,它有700,000,000行。我已经索引了其中的一些列。
我的问题是,我的数据库是否表现为“正常”--因为我以前没有处理过这么多数据的经验。
看看这些查询,如果您认为这是正常的响应时间,请告诉我:
SELECT count( DISTINCT(QualityLevel) ) FROM patient_records;
+---------------------------------+
| count( DISTINCT(QualityLevel) ) |
+---------------------------------+
| 265595 |
+---------------------------------+
1 row in set (1.248 sec)我认为这是一个很好的响应时间。然而,请看下面的情况:
select count(QualityLevel) from patient_records where QualityLevel>10.14;
+---------------------+
| count(QualityLevel) |
+---------------------+
| 700756562 |
+---------------------+
1 row in set (3 min 10.324 sec)没那么好(我想?)。此外,还有:
select count(recordID) from patient_records where QualityLevel>10.14 and snpID='.';
+-----------------+
| count(recordID) |
+-----------------+
| 56627747 |
+-----------------+
1 row in set (23 min 53.028 sec)虽然这两个列(QualityLevel和snpID)都被编入了索引,但速度相当慢。
我将需要为这些查询构建一个web界面,但不可能需要25分钟才能执行。我在这里错过了什么?也许DB分区会有所帮助(对此一无所知,因此任何建议都会受到高度赞赏)。
我的my.cnf文件:
[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/home/ssd/mysql_datadir/mysql.sock
innodb_buffer_pool_size=4GB
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256k
[client]
port=3306
socket=/home/ssd/mysql_datadir/mysql.sock
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d我的表是:
patient_records | CREATE TABLE `patient_records` (
`recordID` int(11) NOT NULL AUTO_INCREMENT,
`MRD_sample_FORMAT_id` int(11) NOT NULL,
`DataType` char(1) NOT NULL,
`SequencingOrigin` char(1) NOT NULL,
`ChrNo` varchar(2) NOT NULL,
`ChrPos` int(10) unsigned NOT NULL,
`snpID` varchar(20) NOT NULL,
`NuclREF` varchar(500) NOT NULL,
`NuclALT` varchar(3000) NOT NULL,
`QualityLevel` float NOT NULL,
`FilterString` char(1) NOT NULL,
`InfoString` varchar(1000) NOT NULL,
`GT` varchar(20) DEFAULT NULL,
`AD` varchar(20) DEFAULT NULL,
`DP` varchar(20) DEFAULT NULL,
`GQ` varchar(20) DEFAULT NULL,
`PL` varchar(20) DEFAULT NULL,
PRIMARY KEY (`recordID`),
KEY `fk_patients_idx` (`MRD_sample_FORMAT_id`),
KEY `ChrNo` (`ChrNo`) USING BTREE,
KEY `snpID` (`snpID`),
KEY `idx_patient_records_ChrNo` (`ChrNo`),
KEY `position_chrom` (`ChrPos`),
KEY `quality` (`QualityLevel`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=700756618 DEFAULT CHARSET=latin1 |发布于 2019-08-26 00:55:43
有SHOW CREATE TABLE会很有帮助,但我会做一些猜测.
您有INDEX(QualityLevel),而该索引比buffer_pool小吗?
您一直在进行大量的查询;这不是第一次?(因此,有些东西是缓存的。)
您没有使用SQL_NO_CACHE来避免查询缓存吗?
你有多少内存?innodb_buffer_pool_size=4GB很小,除非你只有6GB。
COUNT(DISTINCT ..)看起来太快了--可能缓存有帮助。
在进行计时测试时:
SELECT SQL_NO_CACHE ... -- to avoid the Query cache.并运行两次查询--以补偿I/O缓存。
您应该切换到InnoDB。然后,用250 of内存设置innodb_buffer_pool_size = 200G。这将有助于提高InnoDB性能。(注意: MyISAM根本不使用它;相反,它使用的是key_buffer_size,对于仅使用MyISAM的设置,它应该设置为大约50G。)
一般来说,添加一个有用的索引是值得的--尽管代价是负的(磁盘空间和插入成本)。
在构建复合(多列)索引时,单个列的基数与其无关。索引成为一个单位,其组成部分没有被注意到。
对于此查询:
select count(recordID) from patient_records
where QualityLevel>10.14 and snpID='.';以下是按此顺序排列的最佳顺序:
INDEX(snpID, , -- because of `=`
QualityLevel, -- a range, so comes after `=`
recordID) -- tacked on to make it "covering"关于COUNT(recordID)的一个注意事项--检查哪些行具有recordID的非空值。这个测试是不必要的,因为它被声明为NOT NULL。我建议养成简单地说COUNT(*)的习惯。
有了COUNT(*),INDEX(snpID, QualityLevel)也会一样好。
要设计最佳索引,您需要知道SELECTs、DELETEs和UPDATEs。你不能事先猜到。看看我的烹饪书。
另一个注意事项:WHERE子句的顺序并不重要;优化器尝试AND子句的所有重新排序。它将尝试将多个表JOINed的所有重排序放在一起。(LEFT可能限制优化器的尝试,也可能不限制它的尝试。)
在web界面中说“确切的56627747小部件”是过去式的。即使是“大约五千六百万”也很少被使用。这可以通过一项夜间工作来有效地实现,它可以重新计算你的数据的统计信息。
如果您确实需要精确性和速度,那么构建一个汇总表并查询它。例如,该表可以对不同质量括号中的小部件数量进行每日计数。
汇总表是数十亿行数据集的关键。
https://dba.stackexchange.com/questions/246207
复制相似问题