首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >查询执行的“正常”时间

问查询执行的“正常”时间
EN

Database Administration用户
提问于 2019-08-25 12:15:37
回答 1查看 247关注 0票数 2

我在一个MariaDB数据库中有一个表,它有700,000,000行。我已经索引了其中的一些列。

我的问题是,我的数据库是否表现为“正常”--因为我以前没有处理过这么多数据的经验。

看看这些查询,如果您认为这是正常的响应时间,请告诉我:

代码语言:javascript
复制
SELECT count( DISTINCT(QualityLevel) ) FROM patient_records;
+---------------------------------+
| count( DISTINCT(QualityLevel) ) |
+---------------------------------+
|                          265595 |
+---------------------------------+
1 row in set (1.248 sec)

我认为这是一个很好的响应时间。然而,请看下面的情况:

代码语言:javascript
复制
select count(QualityLevel) from patient_records where QualityLevel>10.14;
+---------------------+
| count(QualityLevel) |
+---------------------+
|           700756562 |
+---------------------+
1 row in set (3 min 10.324 sec)

没那么好(我想?)。此外,还有:

代码语言:javascript
复制
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文件:

代码语言:javascript
复制
[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

我的表是:

代码语言:javascript
复制
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 |
EN

回答 1

Database Administration用户

回答已采纳

发布于 2019-08-26 00:55:43

有SHOW CREATE TABLE会很有帮助,但我会做一些猜测.

您有INDEX(QualityLevel),而该索引比buffer_pool小吗?

您一直在进行大量的查询;这不是第一次?(因此,有些东西是缓存的。)

您没有使用SQL_NO_CACHE来避免查询缓存吗?

你有多少内存?innodb_buffer_pool_size=4GB很小,除非你只有6GB。

COUNT(DISTINCT ..)看起来太快了--可能缓存有帮助。

在进行计时测试时:

代码语言:javascript
复制
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。)

一般来说,添加一个有用的索引是值得的--尽管代价是负的(磁盘空间和插入成本)。

在构建复合(多列)索引时,单个列的基数与其无关。索引成为一个单位,其组成部分没有被注意到。

对于此查询:

代码语言:javascript
复制
select count(recordID) from patient_records
      where QualityLevel>10.14 and snpID='.';

以下是按此顺序排列的最佳顺序:

代码语言:javascript
复制
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接口

在web界面中说“确切的56627747小部件”是过去式的。即使是“大约五千六百万”也很少被使用。这可以通过一项夜间工作来有效地实现,它可以重新计算你的数据的统计信息。

如果您确实需要精确性和速度,那么构建一个汇总表并查询它。例如,该表可以对不同质量括号中的小部件数量进行每日计数。

汇总表是数十亿行数据集的关键。

票数 3
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/246207

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档