Mysql数据库操作心得

2009年04月29日 | 作者: Fonny

狠狠的研究了一下Mysql数据库方面的资料.

心得如下:

设计数据库结构

我租用的空间,也就是您现在连接的IP,只提供了一个数据库,所以我基本按照服务器端的情况在本地模拟了一个环境,当然,PHP,Mysql,apache的版本都比租的空间的高,不过基本能实现模拟效果.

所谓设计,刚刚开始我只是对着书本上的设计方案进行模拟,在最初的学习,模仿过程中我学会了一些sql的语法,知道了Mysql中的大多数数据格式的用法和特性,还有一些简单的函数,比如:

计数器用得最多的: Last_insert_id()

插入修改时间的: NOW()

高级一点的还有:

Innodb的关联索引键,(使用PHPmyadmin操作很方便)

InnoDB的事务处理,

SART TRANSACTION()

COMMIT()

ROLLBACK()

等用法,不过我经过一再考虑,决定目前不使用INNODB数据库,主要还是速度方面的因素,毕竟作为网站数据库,不需要那么精密的数据库完整性,使用非事务类型的数据库通过结合服务器脚本验证,同样可以达到效果,但是毕竟事务型数据库更偏重于读写量都大的程序,网站,还是用MYISAM比较合适,因为它更适合读操作多的程序.

看了Mysql的官方文档,还了解了INSERT INTO … WHERE…语句的用法(今天才测试成功-_-!);

这些基础的东西实在花费了我不少精力,但是设计数据库就像某位高手说的,重点不在于数据库结构是否”学院”,而是高效,速度.

无论怎么设计,只要速度能达到自己能力的极限,就按照自己的想法去做吧.

那么,怎么提高效率呢?

针对网站程序的设计而言,主要是读多,写少,我选用MYISAM数据库类型,它的SELECT速度已经非常高效和智能了,需要做的仅仅只是设计好各个表的索引,基本上能搞定对大多数的效率问题.

如何设置索引呢?

我摸索这个问题也花费了很多精力,查找资料,解析好多经典程序,包括WordPress的数据库设计,最后还是从一本很旧的书里面看到了答案,也许并不一定完整,但是相当好用,总结成一句话,甚至可以免掉例子,那就是:

把每个SELECT语句中的WHERE后面的键都设置为索引即可.

加几句的话,还有些方法可说:

比如:

如果可以减少查询次数,某些表中存在重叠数据也无妨,毕竟咱们读多写少啊,INERT多几个位置带来SELECT次数的减少,绝对稳赚不赔的.

同样一个SELECT语句,注意把可能性大的那一个(也就是重复次数少的)键放在前面,这样可以减少很多查询时间.如果一个表中存在多个经常同时查询的列,不妨设为多索引(不是一个索引加一个索引,而是联合索引,通过两个键确保唯一性).

基本上,通过以上几点技巧,一个数据库就可以开始设计了,对于整体的设计方案还有技巧,我总结的东西如下:

尽量保持每个表的读写负荷一致或者接近.

比如,一般来说都是把”新闻分类”(Category)作为单独的一个表,”新闻”(news)作为一个表,这样设计结构相当清晰,但是你发现没有,新闻类别的INSERT和UPDATE操作绝对大大少于”新闻”表,那么这两个表的负荷就不太一致了.

如何解决呢?

我目前考虑,把”新闻分类”表结合到”整站控制”(options)表中,因为这两个表都是每次启动程序需要读取的,基本上也就是一次,更改整站的options和类别的几率都很小,这样就减少了一个表的空间,减少了一次查询,而加大了options表的写操作几率,虽然赶不上”新闻”表更新的负荷,却也更接近了些.

同样,如果像WordPress设计,把所有发布的东西都放在post表中,不区分”图片”,”视频”,”blog”,”下载文件”等等,基本上也是为了减少查询次数,结构相当不清晰,效果却不错.但是我个人认为,这样设计并不能针对所有类型的Blog,比如我的”影忆坊”.

我的网站对图片的要求较多,甚至主要就是图片,那么每次查询都会下载很多无用数据,或者通过选择条件过滤也行,而这不仅仅是稍微加大了数据库查询难度,还带来了这个表的数据会越来越庞大,查询速度会越来越慢.所以我考虑需要将主要post类型(比如:’photo’,'movie’单列一个表,同时将不常用的post类型结合为一个表,平均一下各个表的负荷.

接下来是计数器.

计数器其实是个很麻烦的东西.

编写起来很容易,但是就像Timer一样,这玩意是实时运行的,如果对每个post都跟踪计数,整站计数,页面计数,类别计数…..会造成很多额外的INSERT和UPDATE操作.

(目前我考虑针对每个post发表时自动INSERT一个计数器到一个单独的计数器表中,这样会造成计数器表的行数成为’全球首富’,因为它含有所有的post的东西的计数器,另外还有整个程序中相对固定的计数器,所以我再考虑要不要这样设计,如何将其分配到其他地方,例如WordPress这样,直接放在post自身,但是这样会有更多UPDATE操作到post表中了,它已经很累了…)

其他的感觉问题都不是很大,至少我目前了解的东西都在这里了.想到其他的再继续补充.