文章分类 » 数据库相关

ORM到底是用还是不用?

ORM即Object/Relation Mapping的简写,一般称作“对象关系映射”,在Web开发中最常出没于和关系型数据库交互的地方。接口、中间件、库、包,你都可以这么称呼它。

我们可以结合PHP和MySQL,从ORM的四个核心理念来认识它:

  • 简单:ORM以最基本的形式建模数据。比如ORM会将MySQL的一张表映射成一个PHP类(模型),表的字段就是这个类的成员变量
  • 精确:ORM使所有的MySQL数据表都按照统一的标准精确地映射成PHP类,使系统在代码层面保持准确统一
  • 易懂:ORM使数据库结构文档化。比如MySQL数据库就被ORM转换为了PHP程序员可以读懂的PHP类,PHP程序员可以只把注意力放在他擅长的PHP层面(当然能够熟练掌握MySQL更好)
  • 易用:ORM的避免了不规范、冗余、风格不统一的SQL语句,可以避免很多人为Bug,方便编码风格的统一和后期维护

接下来再通过一个很基本的例子来说明一下ORM的使用,还以PHP和MySQL为例。

user这个数据模型是再普遍不过的了。假设我们有一张user数据表,结构如图:

在OOP中通常我们需要写一个对应的class User来作为user数据表的数据模型:

// 声明class User
class User{
    $id;
    $name;

    function create(){/*...*/}
    function load($id){/*...*/}
}

// 使用class User
$user = new User();
$user->name = 'fancy';
$user->create();

但是通过ORM,我们可以不用去声明class User,可以直接继承ORM提供的工厂类,比如:

// 直接使用!对于熟悉MVC的亲知道这个意义之所在!
$user = new ORM('user');  // ORM都有自己的规则,这里直接使用了MySQL的表名
$user->name = 'fancy';    // MySQL的表的字段就是$user对象的成员变量
$user->save();            // 掉用ORM提供的接口函数

ORM一般都针对数据模型提供了一下常见的接口函数,比如:create(), update(), save(), load(), find(), find_all(), where()等,也就是讲sql查询全部封装成了编程语言中的函数,通过函数的链式组合生成最终的SQL语句。

所以由这些来看,ORM对于敏捷开发和团队合作开发来说,好处是非常非常大的。这里就罗列一下我想到的ORM显著的优点

  • 大大缩短了程序员的编码时间,减少甚至免除了对Model的编码
  • 良好的数据库操作接口,使编码难度降低,使团队成员的代码变得简洁易读、风格统一
  • 动态的数据表映射,在数据表结构甚至数据库发生改变时,减少了相应的代码修改
  • 减少了程序员对数据库的学习成本
  • 可以很方便地引入数据缓存之类的附加功能

但是ORM并不是一个完美的东西,它同时也有其自身不可避免的缺点

  • 自动化进行关系数据库的映射需要消耗系统性能。其实这里的性能消耗还好啦,一般来说都可以忽略之,特别是有cacha存在的时候
  • 在处理多表联查、where条件复杂之类的查询时,ORM的语法会变得复杂且猥琐
  • 越是功能强大的ORM越是消耗内存,因为一个ORM Object会带有很多成员变量和成员函数。有一次修复bug时就遇见,使用ORM查询的时候会占用12MB的内存,而使用SQL的查询时只占用了1.7MB……

ORM就是这么一个让人又爱又恨的东西。回到我们开始的问题:“ORM到底是用还是不用?”。

Fancy个人的观点是:ORM要用!但关键部位不能用!

因为对于一般的Web应用开发来说,使用ORM确实能带来上述的诸多好处,而且在大部分情况下涉及不到ORM的不好的地方。但是在系统里面有大数据量、大运算量、复杂查询的地方,就不要用ORM。ORM的性能问题将给你带来灾难。在这些地方就可以使用纯SQL或者其他简单轻量的DB Helper库了。在详细了解ORM之后,你就可以扬长避短让ORM发挥其最大效用了。

原文链接: http://www.fancycedar.info/2013/01/orm/

MySQL存储引擎MyISAM与InnoDB的优劣

使用MySQL当然会接触到MySQL的存储引擎,在新建数据库和新建数据表的时候都会看到。

MySQL默认的存储引擎是MyISAM,其他常用的就是InnoDB了。

至于到底用哪种存储引擎比较好?这个问题是没有定论的,需要根据你的需求和环境来衡量。所以对这两种引擎的概念、原理、异同和各自的优劣点有了详细的了解之后,再根据自己的情况选择起来就容易多了。

MyISAM InnoDB
存储结构 每张表被存放在三个文件:
  1. frm-表格定义
  2. MYD(MYData)-数据文件
  3. MYI(MYIndex)-索引文件
所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB
存储空间 MyISAM可被压缩,存储空间较小 InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复 由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了
事务安全 不支持 每次查询具有原子性 支持 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表
AUTO_INCREMENT MyISAM表可以和其他字段一起建立联合索引 InnoDB中必须包含只有该字段的索引
SELECT MyISAM更优
INSERT InnoDB更优
UPDATE InnoDB更优
DELETE InnoDB更优 它不会重新建立表,而是一行一行的删除
COUNT without WHERE MyISAM更优。因为MyISAM保存了表的具体行数 InnoDB没有保存表的具体行数,需要逐行扫描统计,就很慢了
COUNT with WHERE 一样 一样,InnoDB也会锁表
只支持表锁 支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的
外键 不支持 支持
FULLTEXT全文索引 支持 不支持 可以通过使用Sphinx从InnoDB中获得全文索引,会慢一点

总的来说,MyISAM和InnoDB各有优劣,各有各的使用环境。

但是InnoDB的设计目标是处理大容量数据库系统,它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。

我觉得使用InnoDB可以应对更为复杂的情况,特别是对并发的处理要比MyISAM高效。同时结合memcache也可以缓存SELECT来减少SELECT查询,从而提高整体性能。

使用不定个数的参数构造查询条件

通过get方法传过来0-3个参数,参数的个数不定,并通过这些参数构造出mysql查询语句.

下面是具体例子:

  • case1_url: www.***.com?id=1&name=2&gender=3/
  • case2_url: www.***.com?id=1&gender=3/
  • case3_url: www.***.com/

  • 在case1中我们传递了全部3个参数,这个很容易实现.

  • 在case3中我们没有传递参数,这个也很容易实现.
  • 但case2中只传递了部分参数应该怎么做呢?这个以前也纠结了不少时间,虽然实现了但方法都是很水的,这次请教了老段之后深深体会到了数据结构的强大之处.

解决方法

建立数据结构

建立2个数组,一个用来存储get中的变量名,另一个存储要构造mysql查询语句中的参数名;

$get_arrays = array(
        'id',
        'name',
        'gender',
);
//与前一个数组中的键值相对应,不过上面存的是get中的参数,这里是数据库中的名称.
$db_arrays = array(
        'user_id',
        'user_name',
        'user_gender',
);

构造查询串

利用循环将传递过来的参数存到新的数组中. 这里我们要新建一个数组query_array,用来存储查询语句 用到了两个函数:

//检查给定的键名或索引是否在数组中
bool array_key_exists(mixed $key , array $search)
//使用字符串$glue,将数组$pieces的所有元素连接起来
string implode ( string $glue , array $pieces )
join()// implode的别名
foreach($get_arrays as $key => $value)
{
        //判断参数是否通过get方法传了过来
        $judge = array_key_exists($value, $_GET);
        if($judge)
        {
                $array[$key] = "`{$db_arrays[$key]}`= '{$_GET[$get_array[$key]]}'";
                //将要查询的变量及值用"="连接,写入数组中,此时array数组形如:$array=('id=1', 'gender=3');
        }
        $condition = join(" and ", $array);     // 使用"and"将各项条件连接起来
}?>

注:本文中的示例代码中未对输入进行转义,请勿应用在生产环境中。

Mysql错误号#1062解决办法

最近做新的HOJ的后台管理功能,实现一个简单的交互界面,能够让管理员方便地添加比赛。但是实际编码的时候发现一些数据库的问题:

  1. 存储比赛的表中,比赛的编号不是连续的。
  2. 比赛的编号不是自动增加的。
  3. 想要添加比赛,就要手动设置比赛的编号。

手动设置比赛的编号显然十分麻烦,不符合方便添加比赛的设计需求。那么就要把比赛编号字段设置成自动增加的。于是,输入了一条命令:

ALTER TABLE Contests CHANGE Contestid Contestid INT(11) NOT NULL AUTO_INCREMENT;

结果,我还是异想天开了——Mysql抛出了 #1062号错误:

ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

仔细分析以后发现了出错原因。比赛的表(Contests)中原本是有数据的,比赛的编号是从0起递增的。修改Contestid字段到AUTO_INCREMENT时,Mysql尝试将Contestid=0的这行的Contestid修改成1,但是原表中已经存在了Contestid=1的项目,而对于主键Contestid来说,值应该是唯一的,所以会报错说出现重复值‘1’。那这样一来,该如何修改Contestid字段,使其变成自动增加的呢?

方法一:

找出了这么一个愚钝的方法,步骤如下:

  1. 导出Contests表中的数据到temp.sql文件。
  2. 删除Contests表中的所有数据。
  3. 修改Contestid字段为AUTO_INCREMENT。
  4. 导入temp.sql,完成。

方法二:

上面的方法显然很麻烦,而且如果数据量特别大的话,将会十分耗时。后来我又尝试了另一个方法:

  1. 修改Contestid为0的一行,使其Contestid为大于0的整数。当然不能和表中其他行的Contestid重复。
  2. 修改Contestid字段为AUTO_INCREMENT。
  3. 将步骤1中的那一行的Contestid改回0,完成。事后可能需要修改AUTO_INCREMENT记录。

这里假设Contests表和其他表没有关联。如果和其他表有关联,那么只要依照关联关系,利用上面的方法适当修改即可。

数据库操作之使用别名

表的别名

通常使用表的别名可以简化输入,或者在存在子查询的查询语句中方便地表示子查询的结果表。 看一个实例:

SELECT u.name
FROM users AS u, orders AS o
WHERE u.userid = o.userid;

声明所要使用的表,并使用 AS 为其指定别名可以大大简化输入。为表指定别名还有另一个重要作用就是当要关联一个表到表本身的时候就必须使用表的别名。举个例子可能会更明确:如果要查找一个存储用户信息的表,找出在同一个城市中的用户,就可以给一个表取两个不同的别名:

SELECT u1.name, u2.name, u1.city
FROM users AS u1, users AS u2
WHERE u1.city = u2.city
AND u1.name != u2.name;

即将 users 表看成两个不同的表 u1u2,并在两个表的city字段执行关联。使用 u1.name != u2.name 条件来避免用户作为其自身的匹配而出现。

字段的别名

有时候会出现两个表中含有相同字段的情况。例如 table1table2 中都含有 id 字段,多数情况我们可能会想, table2 中的 id 是关联到 table1 的外键。但不排除两个 id 字段相互无关的情况。如果出现这种情况,而查询结果还需要将其都显示出来,这时就可以为字段指定别名:

SELECT table1.id AS t1_id, table2.id AS t2_id
FROM ...
WHERE ...

这时候查询结果中,table1 中的 id 字段将以 t1_id 出现,table2 中的 id 字段将以 t2_id 出现。

Mysql按条件计数的几种方法

最近在给某网站的后台添加一系列的统计功能,遇到很多需要按条件计数的情况。尝试了几种方法,下面简要记录,供大家参考。

问题描述

为使讨论简单易懂,我将问题稍作简化,去掉诸多的背景。

从前有一个皇帝,他有50个妃子,这些妃子很没有天理的给他生了100,000个儿子,于是,皇帝很苦恼,海量的儿子很难管理,而且,他想知道每个妃子给他生了多少个儿子,从而论功行赏,这很难办。于是,皇帝请了一个程序员帮他编了一个程序,用数据库来存储所有的儿子的信息,这样就可以用程序来统计和管理啦。

数据库的结构如下:

id皇子的唯一编号
mother皇子母亲的唯一编号

皇帝把妃子分成了两个等级,天宫娘娘(编号小于25)和地宫娘娘(编号大于等于25),他想知道天宫娘娘们和地宫娘娘们的生育能力孰强孰弱。于是,程序员开始写SQL Query了。

方法1:使用GROUP BY

SQL Query

SELECT COUNT(*) FROM `prince` GROUP BY `mother` > 24;

执行结果

count(*)
50029
49971

在100,000行数据上的运行时间:0.0335 秒

分析

这种GROUP BY方法的最大问题在于:无法区分所得到的结果。这两个数字哪一个是天宫娘娘们所生的皇子数,哪一个是地宫娘娘们所生的皇子数呢?不知道。所以,尽管它统计出了总数,但是没有什么意义。

因此,为了区分统计结果,必须要把条件 mother > 24 也作为一个字段在结果集中作为一个字段体现出来,修改后的sql如下:

SELECT COUNT(*) AS `number`, `mother` > 24 AS `type` FROM `prince` GROUP BY `mother` > 24;

执行结果

number  type
50029   0
49971   1

条件表达式作为字段时,该字段的值就是该条件表达式的值,因此,对应我们的例子,type = 1 也就是表示 mother > 24 的值为1,因此,第二行中的数字代表地宫娘娘们所生的皇子数。

经过修改后,我们看出,天宫娘娘们略胜一筹。

优缺点

缺点是显而易见的,由于使用了条件表达式作为分组依据,它只能做二元的划分,对于要分成多类进行统计的情况不能够胜任。比如要分别统计1~10号、11~24号,25号~50号妃子的产子数,就无法实现了。

另外,由于使用了GROUP BY,因此涉及到排序,执行时间上要更长。

我暂时没有发现这种方法的优点。

方法2:使用嵌套的SELECT

使用嵌套的SELECT也可以达到目的,在每个SELECT子句中统计一个条件下的数据,然后用一个主SELECT把这些统计数据整合起来。

SQL Query

SELECT 
    ( SELECT COUNT( * ) FROM `prince` WHERE `mother` >24 ) AS `digong`, 
    ( SELECT COUNT( * ) FROM `prince` WHERE `mother` <=24 ) AS `tiangong`

执行结果

digong  tiangong
49971   50029

在100,000行数据上的运行时间:0.0216 秒

分析

这种嵌套SELECT的方法非常直观,就是分别统计各个条件下的数值,最后进行汇总,通俗易懂,跟自然语言没啥区别了。

优缺点

优点就是直观,而且速度也比GROUP BY要快。虽然是3条SELECT语句,看起来比GROUP BY的方案多了2条语句,但是它不涉及到排序,这就节省了很多时间。

缺点可能就是语句稍多,对语句数量有洁癖的同学可能会比较不舒服。

方法3:使用CASE WHEN

CASE WHEN语句的功能很强大,可以定义灵活的查询条件,很适合进行分类统计。

SQL Query

SELECT 
    COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) AS `digong`, 
    COUNT( CASE WHEN `mother` <=24 THEN 1 ELSE NULL END ) AS `tiangong`
FROM prince

执行结果

digong  tiangong
49971   50029

在100,000行数据上的运行时间:0.02365825 秒

分析

此方法的关键在于

COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) 

这里的COUNT和CASE WHEN联合使用,做到了分类计数。先使用CASE WHEN,当满足条件时,将字段值设置为 1, 不满足条件时,将字段值设置为NULL,接着COUNT函数仅对非NULL字段进行计数,于是,问题解决。

优缺点

优点嘛,此方法也不涉及到排序,因此运行时间上与方法2相当,SELECT语句减少到了 1 条。

缺点就是语句比较长,对语句长度有洁癖的同学可能会比较不舒服。

总结

对于确定分类的按条件计数,可以尽量不用GROUP BY,从而避免排序动作,加速Query的执行。

如果需要根据某个字段的值进行分类,而该字段的值是可变的,比如皇帝要统计每一个妃子的产子数,而他可能不停的再娶很多妃子,这种情况下,使用方法2和方法3就不太灵光了,还是使用一个GROUP BY来得简单便捷。