主页 / dba / 问题 / 246207
Accepted
bioplanet
Asked: 2019-08-26 04:15:37 +0800 CST

查询执行的“正常”时间

  • 772

我在 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 分钟才能执行。我在这里想念什么？也许数据库分区会有所帮助（对此一无所知，因此任何建议都将受到高度赞赏）。

我的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

我的 SHOW CREATE TABLE 是：

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 |
mysql mariadb

1 个回答

  1. Best Answer

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

    你有INDEX(QualityLevel)，并且那个索引小于 buffer_pool？
    你一直在做一堆查询；这些不是第一个？（因此有些东西被缓存了。）
    你没有使用SQL_NO_CACHEQuery 来避免缓存？

    你有多少内存？ innodb_buffer_pool_size=4GB除非您只有 6GB，否则它相当小。

    COUNT(DISTINCT ..)似乎太快了——可能缓存有帮助。

    在进行时序测试时：

    SELECT SQL_NO_CACHE ...   -- to avoid the Query cache.

    并运行两次查询——以补偿 I/O 缓存。

    回复评论

    您应该切换到 InnoDB。然后，使用 250GB 的 RAM，设置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)-- 检查哪些行具有非 NULL 值的注释recordID。这个测试是不必要的，因为它是声明的NOT NULL。我建议养成简单地说的习惯COUNT(*)。

    与COUNT(*),INDEX(snpID, QualityLevel)一样好。

    要设计最佳索引，您需要知道SELECTs、DELETEs和UPDATEs。你不能预先猜测。请参阅我的食谱。

    另一个注意事项：WHERE子句的顺序无关紧要；优化器会尝试对AND子句进行所有重新排序。它将JOINed一起尝试对多个表进行所有重新排序。（LEFT可能会或可能不会限制优化器将尝试的内容。）

    需要为这些查询构建一个 Web 界面

    在 Web 界面中说“正好有 56627747 个小部件”已经过时了。即使是“大约 5600 万”，这些天也很少使用。这可以通过重新计算数据和其他有关数据的统计数据的夜间工作来有效地实现。

    如果您确实需要精度和速度，则构建一个汇总表并进行查询。例如，该表可以每天计算各种质量范围内的小部件数量。

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

    • 3

相关问题