mysql 存储过程 光标_mysql存储过程 光标

当我可以在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 signals = new List();

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 from results_1mregr_c_ew_f order by

;

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

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

祝好运