我是一名科学家，我使用 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

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

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

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;

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   

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)

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;

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   

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)

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...

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 ; 

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

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

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

start transaction;

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

commit;

关于mysql - 用于提供大量数据的查询的最佳 MySQL 设置？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4129071/