FeiYan

网站导航

搜索

腾讯微博 新浪微博 FeelBLog 开源中国社区

MySQL是否应该使用存储过程

2012-09-22 22:48:22     1个评论     9095次访问

关系型数据库一般都支持过程和函数,用来解决一些复杂的业务逻辑,以前写C#的时候写过很多存储过程和触发器去完成一些比较麻烦的逻辑(例如:使用触发器删除SQL Server关联数据),C#工程师童鞋应该都写过分页存储过程。那么在MySQL中,尤其是当面临比较高的流量时,是否应该使用存储过程呢?

关于这个问题我请教过我们经验丰富的架构师,他的意见是使用存储过程会给本已高负荷的MySQL数据库增加额外的负担,而使用存储过程节省的网络通信流量可以忽略不计;还有淘宝的DBA丁奇,他的意见是使用存储过程对性能的影响不大,但是一般线上的业务MySQL只用来存储/查询数据,倒是有一个项目全部使用存储过程实现,尤其是在mysql端的cpu很空闲的情况下,用存储过程也不错;


下面是一个简单的测试,一个dept表,1-1000个部门,和部门的别名;一个users表,200000个用户,随机属于1000个部门中的一个;假设users表中只有部门名称,没有部门名称别名,在users表中添加此字段`dept_alias`后根据dept表更新`dept_alias`的值:

//部门信息表
CREATE TABLE `dept` (
  `name` char(255) CHARACTER SET utf8 NOT NULL DEFAULT NULL,
  `alias` char(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
//用户数据表
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` char(255) CHARACTER SET utf8 DEFAULT NULL,
  `gender` enum('男','女') CHARACTER SET utf8 DEFAULT '男',
  `dept` char(255) CHARACTER SET utf8 DEFAULT NULL,
  `dept_alias` char(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_dept` (`dept`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
//测试存储过程
DROP PROCEDURE IF EXISTS testProcedure;
CREATE PROCEDURE testProcedure()
BEGIN
    DECLARE flag INT DEFAULT 0;
    DECLARE tID INT;
    DECLARE tDept CHAR(255);
    DECLARE tAlias CHAR(20);
    DECLARE cur CURSOR FOR SELECT id,dept FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
    OPEN cur;
    FETCH cur INTO tID,tDept;
    WHILE flag<>1 DO
        SELECT alias FROM dept WHERE name = tDept INTO tAlias;
        UPDATE users SET dept_alias=tAlias WHERE id=tID;
        FETCH cur INTO tID,tDept;
    END WHILE;
    CLOSE cur;
END

首先,这个需要使用下面的一条SQL语句就可以实现。

-- 4.25 s
UPDATE users AS u SET u.dept_alias=(SELECT alias FROM dept WHERE name=u.dept);

不过,为了测试,先将users中的数据逐一读出,然后一一查询更新,使用存储过程和使用通常的查询做法分别如下所示:

//time: 17.667736053467 s
//memory: 55128 bytes (不包含MySQL内存,仅供参考)
mysql_connect('127.0.0.1','root','develop') OR die('Connect Failure');
mysql_select_db('test') OR die('SELECT DB Error!');
mysql_query('SET NAMES utf8;');
$t1 = getMicrotime();
mysql_query('CALL testProcedure();');
$t2 = getMicrotime();
var_dump( $t2-$t1,memory_get_usage() );
mysql_close();
    
function getMicrotime() {
    list( $usec, $sec ) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
//time: 72.716572999954 s
//memory: 60096 bytes (不包含MySQL内存,仅供参考)
mysql_connect('127.0.0.1','root','root') OR die('Connect Failure');
mysql_select_db('test') OR die('SELECT DB Error!');
mysql_query('SET NAMES utf8;');
mysql_query('UPDATE users SET dept_alias = NULL');
$t1 = getMicrotime();
$q1 = mysql_query('SELECT id,dept FROM users');
while( $row = mysql_fetch_array( $q1, MYSQL_ASSOC ) ){
    $q2 = mysql_query("SELECT alias FROM dept WHERE name='".$row['dept']."'");
    if( $single = mysql_fetch_array( $q2, MYSQL_ASSOC ) ){
        $dept_alias = $single['alias'];
        $sql = "UPDATE users SET dept_alias='".$dept_alias."' WHERE id=".$row['id'];
        mysql_query($sql);
    }
}
$t2 = getMicrotime();
var_dump( $t2-$t1,memory_get_usage() );
mysql_close();
    
function getMicrotime() {
    list( $usec, $sec ) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

在这个非严谨的简单测试里面使用存储过程会更加高效,同样类似这样MySQL函数不能直接查询出来的结果集,又需要在代码中循环的,可以把这方面的逻辑搬到数据库中实施。不过,实际项目中很少有一次读出20万数据的,差别就不会这么大了,而代码中直接写SQL会节省一些麻烦,所以会有很多人选择不给服务器增加压力。

关于是否使用存储过程的论述到此为止,这个问题没有什么正确答案,要根据业务需求去判断是否可以使用。不过,我算是说服我自己以后可以在MySQL数据库使用存储过程或者触发器去完成一些复杂的逻辑。下面是一些关于MySQL存储过程的优点和缺点的总结:

MySQL存储过程的优点
  1. 预编译,相对于直接的SQL效率会高点,同时可以降低SQL语句传输过程中消耗的流量;

  2. 简化业务逻辑,可以把需求转化给专业的DBA(如果有的话);

  3. 更方便的使用MySQL数据库事物的处理,尤其是购物类网站;

  4. 安全、用户权限更容易管理;

  5. 修改存储过程基本上不需要修改程序代码,而直接写SQL修改SQL一般都要修改相关的程序;

MySQL存储过程的优点
  1. MySQL没有提供很好的开发和调试工具,这点SQL Server做的更好;

  2. 可用函数有限,很难写出非常复杂的查询;

  3. 迁移数据库麻烦,如果使用存储过程的应用要把数据库升级到Oracle,将会是一个比麻烦的工程;

  4. 目前还无法确定在高并发(百万或者千万级)下存储过程是否会严重影响数据库性能。

文章标签: mysql  存储过程 

本文地址:MySQL是否应该使用存储过程

相关文章

2011-11-03:PHP转换汉字拼音和Unicode

2012-09-03:高性能网站架构基础篇

2012-09-12:MySQL索引使用方法和性能优化

2012-10-25:Linux/Ubuntu下CMake编译MySQL

2012-10-30:MySQL Query Cache引起的查询缓慢

2012-11-06:Win7配置Nginx+PHP+MySQL 坑爹的性能

2012-11-08:Ubuntu配置Nginx+PHP+MySQL开发环境

2013-03-05:MySQL 5.6.10安装图解和配置

1 Comments »

  1. rinrin
    http://www.9958.pw/post/mysql_procedure 跟大家分享一下

    2014-01-15 16:25:29   

发布评论

最新评论

  1. SpecsSpecs

    不错~~

  2. zhyzhy

    我也遇到这个问题 不知道是swf 、jcrop 、 uploadify 还是浏览器缓存

  3. java开发java开发

    您好!我按你的源码进行了编,能正常运行,但是我们项目是由java开发的,怎么做才能将生成的一个可执行文件?我想把这个文件放到服务器上直接运行,不想安装~