传递大量数据的查询的最佳MySQL设置？

漆雕欣德

2023-03-14

问题内容：

我从事科学家工作，并使用MySQL作为数值模拟结果的存储库。通常，我有一组通过实验获得的数据和一个对照组。这两个数据集存储在一个表中。一个指示符字段告诉我记录是来自实验还是来自控件集。该表通常具有约1亿条记录。5000万次实验和5000万个控件。

在对数据进行后处理时，我的典型任务是首先发出以下两个查询：

select b0,t0 from results_1mregr_c_ew_f where RC='E' and df>60  /// getting experiments data

和

select b0,t0 from results_1mregr_c_ew_f where RC='C' and df>60 /// getting controls data

我在RC，df上有一个多列索引。这些查询会花费大量时间，而查询会花费大部分时间来“发送数据”

我正在具有12GB RAM的8core
MacPro上运行此程序。我是这台机器的一个用户，此任务是主要任务，因此我可以将所有RAM专用于MySQL。所有表都是MyISAM（如果可以提高查询速度，我可以将它们转换）。

对于如何加快这些查询的任何建议，我将不胜感激。我应该更改一些设置，索引，查询…吗？

我希望在每个查询中都能获得约5000万条记录。 请注意，由于管理原因，不能将表格分为两个表格，一个表格包含实验，一个表格包含对照观察。

这是输出：

explain select b0, t0 from results_1mregr_c_ew_f  where RC="C" and df>60
+----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+
| id |select_type|table                |type |possible_keys|key|key_len|ref |rows   |Extra      |
+----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+
|  1 |SIMPLE     |results_1mregr_c_ew_f|range|ff           |ff |11     |NULL|6251121|Using where|
+----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+

以下是输出：

show indexes from results_1mregr_c_ew_f;
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                 | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| results_1mregr_c_ew_f |          0 | PRIMARY  |            1 | id          | A         |    50793996 |     NULL | NULL   |      | BTREE      |         |
| results_1mregr_c_ew_f |          1 | ff       |            1 | RC          | A         |           3 |     NULL | NULL   |      | BTREE      |         |
| results_1mregr_c_ew_f |          1 | ff       |            2 | df          | A         |         120 |     NULL | NULL   |      | BTREE      |         |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

以下是输出：

CREATE TABLE `results_1mregr_c_ew_f` (
  `b0` double NOT NULL COMMENT '    ',
  `s0` double NOT NULL,
  `t0` double NOT NULL,
  `b1` double NOT NULL,
  `s1` double NOT NULL,
  `t1` double NOT NULL,
  `b2` double NOT NULL,
  `s2` double NOT NULL,
  `t2` double NOT NULL,
  `b3` double NOT NULL,
  `s3` double NOT NULL,
  `t3` double NOT NULL,
  `b4` double NOT NULL,
  `s4` double NOT NULL,
  `t4` double NOT NULL,
  `AD` char(4) NOT NULL,
  `chisq` double NOT NULL,
  `RC` char(7) NOT NULL,
  `colq` varchar(255) NOT NULL,
  `df` int(11) NOT NULL,
  `ncol` int(11) NOT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `p1` float NOT NULL,
  `p2` float NOT NULL,
  `p3` float NOT NULL,
  `p4` float NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ff` (`RC`,`df`)
) ENGINE=MyISAM AUTO_INCREMENT=50793997 DEFAULT CHARSET=ascii |

问题答案：

当我可以在60秒内在类似硬件上执行相同操作时，您的查询要花2个小时才能执行，这是一件非常错误的事情。

以下某些内容可能会有所帮助…

为您的引擎调整MySQL

检查服务器配置并进行相应优化。以下某些资源应该是有用的。

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
http://www.mysqlperformanceblog.com/
http://www.highperfmysql.com/
http://forge.mysql.com/wiki/ServerVariables
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/
http://jpipes.com/presentations/perf_tuning_best_practices.pdf
http://jpipes.com/presentations/index_coding_optimization.pdf
http://www.jasny.net/?p=36

现在不那么明显了…

考虑使用存储过程来处理数据服务器端

为什么不处理MySQL内部的所有数据，从而不必将大量数据发送到应用程序层？以下示例使用游标在2分钟内循环和处理服务器端5000万行。我不是游标的忠实拥护者，尤其是在MySQL游标非常有限的地方，但是我猜想您会循环结果集并进行某种形式的数值分析，因此在这种情况下使用游标是合理的。

简化的myisam结果表-基于您的密钥。

drop table if exists results_1mregr_c_ew_f;
create table results_1mregr_c_ew_f
(
id int unsigned not null auto_increment primary key,
rc tinyint unsigned not null,
df int unsigned not null default 0,
val double(10,4) not null default 0,
ts timestamp not null default now(),
key (rc, df)
)
engine=myisam;

我生成了1亿行数据，其中关键字段的基数与您的示例大致相同：

show indexes from results_1mregr_c_ew_f;

Table                   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Index_type
=====                   ==========  ========    ============    =========== =========   =========== ==========
results_1mregr_c_ew_f       0       PRIMARY         1               id          A       100000000   BTREE   
results_1mregr_c_ew_f       1       rc              1               rc          A               2   BTREE   
results_1mregr_c_ew_f       1       rc              2               df          A             223   BTREE

存储过程

我创建了一个简单的存储过程，该过程将获取所需的数据并对其进行处理（使用与示例相同的where条件）

drop procedure if exists process_results_1mregr_c_ew_f;

delimiter #

create procedure process_results_1mregr_c_ew_f
(
in p_rc tinyint unsigned,
in p_df int unsigned
)
begin

declare v_count int unsigned default 0;
declare v_done tinyint default 0;
declare v_id int unsigned;
declare v_result_cur cursor for select id from results_1mregr_c_ew_f where rc = p_rc and df > p_df;
declare continue handler for not found set v_done = 1;

open v_result_cur;

repeat
    fetch v_result_cur into v_id;

    set v_count = v_count + 1;
    -- do work...

until v_done end repeat;
close v_result_cur;

select v_count as counter;

end #

delimiter ;

观察到以下运行时：

call process_results_1mregr_c_ew_f(0,60);

runtime 1 = 03:24.999 Query OK (3 mins 25 secs)
runtime 2 = 03:32.196 Query OK (3 mins 32 secs)

call process_results_1mregr_c_ew_f(1,60);

runtime 1 = 04:59.861 Query OK (4 mins 59 secs)
runtime 2 = 04:41.814 Query OK (4 mins 41 secs)

counter
========
23000002 (23 million rows processed in each case)

嗯，性能有些令人失望，因此进入下一个想法。

考虑使用innodb引擎（令人震惊的恐怖）

为什么是innodb？因为它具有聚簇索引！您会发现使用innodb的插入速度较慢，但希望读取速度会更快，因此这是值得的折衷选择。

通过聚集索引访问行的速度很快，因为行数据位于索引搜索所位于的同一页上。如果表很大，则与使用不同于索引记录的页面存储行数据的存储组织相比，聚集索引体系结构通常可以节省磁盘I
/ O操作。例如，MyISAM将一个文件用于数据行，将另一个文件用于索引记录。

更多信息在这里：

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

简化的innodb结果表

drop table if exists results_innodb;
create table results_innodb
(
rc tinyint unsigned not null,
df int unsigned not null default 0,
id int unsigned not null, -- cant auto_inc this !!
val double(10,4) not null default 0,
ts timestamp not null default now(),
primary key (rc, df, id) -- note clustered (innodb only !) composite PK
)
engine=innodb;

innodb的一个问题是它不支持构成复合键一部分的auto_increment字段，因此您必须自己使用序列生成器，触发器或其他方法（可能是在填充结果表本身的应用程序中）提供递增的键值??

同样，我生成了1亿行数据，这些键字段的基数与您的示例大致相同。如果这些数字与myisam示例不匹配，请不要担心，因为innodb估计基数，因此它们不会完全相同。（但它们-
使用相同的数据集）

show indexes from results_innodb;

Table           Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Index_type
=====           ==========  ========    ============    =========== =========   =========== ==========
results_innodb      0       PRIMARY         1               rc          A                18     BTREE   
results_innodb      0       PRIMARY         2               df          A                18     BTREE   
results_innodb      0       PRIMARY         3               id          A         100000294     BTREE

存储过程

存储过程与上面的myisam示例完全相同，但是从innodb表中选择数据。

declare v_result_cur cursor for select id from results_innodb where rc = p_rc and df > p_df;

结果如下：

call process_results_innodb(0,60);

runtime 1 = 01:53.407 Query OK (1 mins 53 secs)
runtime 2 = 01:52.088 Query OK (1 mins 52 secs)

call process_results_innodb(1,60);

runtime 1 = 02:01.201 Query OK (2 mins 01 secs)
runtime 2 = 01:49.737 Query OK (1 mins 50 secs)

counter
========
23000002 (23 million rows processed in each case)

比myisam引擎实施快约 2-3分钟 ！（innodb FTW）

分而治之

在使用游标的服务器端存储过程中处理结果可能不是最佳解决方案，尤其是因为MySQL不支持诸如数组和复杂数据结构之类的东西，而这些东西在3GL语言（如C＃等）或什至在其他数据库（如作为Oracle
PL / SQL。

因此，这里的想法是将成批的数据返回到应用程序层（C＃等），然后可以将结果添加到基于集合的数据结构中，然后在内部处理数据。

存储过程

该存储过程需要3个参数rc，df_low和df_high，这使您可以选择以下数据范围：

call list_results_innodb(0,1,1); -- df 1
call list_results_innodb(0,1,10); -- df between 1 and 10
call list_results_innodb(0,60,120); -- df between 60 and 120 etc...

显然，df范围越高，您将提取的数据越多。

drop procedure if exists list_results_innodb;

delimiter #

create procedure list_results_innodb
(
in p_rc tinyint unsigned,
in p_df_low int unsigned,
in p_df_high int unsigned
)
begin
    select rc, df, id from results_innodb where rc = p_rc and df between p_df_low and p_df_high;
end #

delimiter ;

我还敲出了一个myisam版本，除了所使用的表外，该版本完全相同。

call list_results_1mregr_c_ew_f(0,1,1);
call list_results_1mregr_c_ew_f(0,1,10);
call list_results_1mregr_c_ew_f(0,60,120);

基于上面的光标示例，我希望innodb版本的性能优于myisam版本。

我开发了一个 快速且肮脏的
多线程C＃应用程序，该应用程序将调用存储过程并将结果添加到集合中以进行后查询处理。您不必使用线程，可以按顺序完成相同的批处理查询方法，而不会造成性能损失。

每个线程（QueryThread）选择一个df数据范围，循环结果集，并将每个结果（行）添加到结果集合中。

class Program
    {
        static void Main(string[] args)
        {
            const int MAX_THREADS = 12; 
            const int MAX_RC = 120;

            List<AutoResetEvent> signals = new List<AutoResetEvent>();
            ResultDictionary results = new ResultDictionary(); // thread safe collection

            DateTime startTime = DateTime.Now;
            int step = (int)Math.Ceiling((double)MAX_RC / MAX_THREADS) -1;

            int start = 1, end = 0;
            for (int i = 0; i < MAX_THREADS; i++){
                end = (i == MAX_THREADS - 1) ? MAX_RC : end + step;
                signals.Add(new AutoResetEvent(false));

                QueryThread st = new QueryThread(i,signals[i],results,0,start,end);
                start = end + 1;
            }
            WaitHandle.WaitAll(signals.ToArray());
            TimeSpan runTime = DateTime.Now - startTime;

            Console.WriteLine("{0} results fetched and looped in {1} secs\nPress any key", results.Count, runTime.ToString());
            Console.ReadKey();
        }
    }

运行时观察如下：

Thread 04 done - 31580517
Thread 06 done - 44313475
Thread 07 done - 45776055
Thread 03 done - 46292196
Thread 00 done - 47008566
Thread 10 done - 47910554
Thread 02 done - 48194632
Thread 09 done - 48201782
Thread 05 done - 48253744
Thread 08 done - 48332639
Thread 01 done - 48496235
Thread 11 done - 50000000
50000000 results fetched and looped in 00:00:55.5731786 secs
Press any key

因此，在不到60秒的时间内获取了5000万行并将其添加到集合中。

我使用myisam存储过程尝试了同样的事情，该过程花了2分钟才能完成。

50000000 results fetched and looped in 00:01:59.2144880 secs

移至innodb

在我的简化系统中，myisam表的性能不会太差，因此可能不值得迁移到innodb。如果您确实决定将结果数据复制到innodb表中，请按照以下步骤进行操作：

start transaction;

insert into results_innodb 
 select <fields...> from results_1mregr_c_ew_f order by <innodb primary key>;

commit;

在将整个事物插入并包装到事务中之前，通过innodb PK对结果进行排序将加快速度。

我希望其中一些证明是有帮助的。

祝好运

传递大量数据的查询的最佳MySQL设置？

为您的引擎调整MySQL

考虑使用存储过程来处理数据服务器端

考虑使用innodb引擎（令人震惊的恐怖）

分而治之

移至innodb

相关阅读

相关文章

相关问答

相关工具

相关文档