手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表Tag:mysql

MySQL之表结构修改[转]

Author:丹臣 posted on Taobao.com

mysql数据库里,对一个已创建的表进行DDL操作,比如说添加一个字段。在做测试时,发现ddl操作的时间特别的长。oracle里,通常情况下只是 修改数据字典就可以了,操作时间非常的短,阻塞DML的时间也比较短。mysql数据库对表进行ddl操作跟oracle数据库有很大的不同,它先要把原 表拷贝一份到临时表,这期间不阻塞select,阻塞所有的更改操作(update,delete,insert),对临时表ddl操作完成,删除原表, 重命名临时表。
如果一张比较大的表进行ddl变更,比如说40G,那拷贝的时间让人无法忍受,并且阻塞所有的DML操作,让业务无法继续。

以下是测试过程:

mysql> desc t1;
+-------------------+------------------+------+-----+---------+-------+
| Field                  | Type               | Null   | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| id                      | int(11)            | YES  | MUL | NULL    |       |
| nick                   | varchar(32)   | YES  |         | NULL    |       |
| email                 | varchar(32)    | YES  |         | NULL    |       |
| gmt_create       | datetime         | YES  |         | NULL    |       |
| gmt_modified    | datetime        | YES  |         | NULL    |       |
+-------------------+------------------+------+-----+---------+-------+
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  2228017 |
+----------+
1 row in set (1.78 sec)

现在对它进行表结构变更,增加一列:

mysql> alter table t1 add(tel varchar(20));
Query OK, 2304923 rows affected (41.03 sec)
Records: 2304923  Duplicates: 0  Warnings: 0

在上述表结构变更过程中,启动另外一个会话,进行select查询操作和一个更新操作:

mysql> select count(*) from t1;
+---------------+
| count(*)      |
+---------------+
|  2304923     |
+---------------+
1 row in set (2.10 sec)

mysql> select * from t1 limit 10;
+------+-------+------------------------+----------------------------+----------------------------+
| id      | nick   | email                       | gmt_create                 | gmt_modified              |
+------+-------+------------------------+----------------------------+----------------------------+
|    0   | nick0 | nick0@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    1   | nick1 | nick1@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    2   | nick2 | nick2@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    3   | nick3 | nick3@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    4   | nick4 | nick4@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    5   | nick5 | nick5@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    6   | nick6 | nick6@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    7   | nick7 | nick7@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    8   | nick8 | nick8@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    9   | nick9 | nick9@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
+------+-------+------------------------+----------------------------+----------------------------+
10 rows in set (0.00 sec)

mysql> update t1 set nick='test_nick' where id=1;
Query OK, 4 rows affected (43.89 sec)          --这里是阻塞的时间
Rows matched: 4  Changed: 4  Warnings: 0

通过以上实验可以看出,对表进行ddl操作时,mysql并不阻塞select查询,但会严重阻塞dml操作。另外,如果你要对表进行ddl操作,由于有一个拷贝操作,你要计算好你的可用空间够不够?如果你的系统经常要进行表结构变更,那么你将不得不要考虑此问题!

--EOF--

膘叔:说实话,一般对于表的操作往往都是在夜深人静的时候,虽然select并不影响,但为避免在更新表结构的时候更新数据,这还是必须的。。。以防万一啊

 

Tags: mysql, database, 修改, 表结构, taobao dba

MySQL主从服务器的一些技巧

问题:主从服务器表类型的选择

一般的共识是主服务器使用innodb,从服务器使用myisam,以便各尽其能。

问题:主从服务器字段类型的选择

字段类型对于分页等操作有很大影响。主服务器一般是innodb,因为不涉及查询,所以可以使用varchar等来存储字符串来节省空间,从服务器一般是 myisam,因为涉及查询,所以必须在char和varchar之间仔细权衡,没有varchar, text, blob字段的表是静态表,反之是动态表,静态表的检索效率要比动态表好若干倍,一般来说,所有涉及大结果集的查询都应该尽可能保证在静态表上完成,这里 说一个例子:比如说常见的articles表有title(varchar), body(text)等字段,在做文章列表的时候,因为不是静态表,所以查询不会很快,下面开始重构解决方案:把原来的articles表拆分成 subjects表和contents表,title字段设置为一个足够的char类型放在subjects表里,body字段还保持是text类型放到 contents表里,subjects和contents表之间的关系是一对多,这样,顺带着也方便的实现了多页文章的功能,而且更重要的是在查询文章 列表的时候,操作都是在subjects静态表里完成,效率肯定会比前一种方案提升很多。

问题:主从服务器NOW()函数造成数据不一致

假设在主服务器上执行一条INSERT .... VALUES ( ..., NOW()),那么在从服务器上也会同样执行一条的SQL语句,但是一来主从服务器各自的时间设置可能就不一致,二来主从服务器间的SQL同步也可能存在 时间上的的延迟,这样,NOW()在两台服务器上的结果就可能不一致。解决方法是显而易见的,就是不要使用NOW(),时间的计算在应用程序里完成。这里 介绍一个额外的小技巧:在PHP里如果想获得当前时间的时间戳,不要用time(),而应该使用$_SERVER[‘REQUEST_TIME’] (PHP版本大于5.1有效),这样少做了一次系统调用,更有效率。

问题:主从服务器读写分离时读操作失败

先重现一下问题:比如说添加一条新数据,添加成功后根据last_insert_id跳转到新添加数据的浏览页面。在此过程中添加新数据的操作是在主服务 器上完成的,浏览新数据的操作实在从服务器上完成的,不过由于主从服务器间SQL同步存在延迟,所以当使用last_insert_id在从服务器上查询 的时候,从服务器很可能还没有还没来得及同步到此记录,所以读操作失败。解决思路也不复杂,在代码里加入一个缓存层(可以使用memcached),新添 加的数据都顺手放到缓存层里一份,新数据的读操作也先查询缓存层,这样就不会再有读操作失败的问题了,当然删除或者更新数据的时候也要顺带着处理好缓存数 据,可以使用观察者模式来搞定。不过这样缓存方案只限于读取单一的记录,对于读取列表的记录的情况,则是无效的。

问题:主从服务器索引是否有必要保持一致

一般都是利用主从服务器完成读写分离,从服务器上进行读操作,主服务器进行写操作,这样的话,主服务器上仅保留主键,外键,唯一索引等必要的索引即可,以 便保持数据合法性,而对于那些原本用于优化SELECT操作的索引,可以全部删除,如此的话主服务器的写操作效率会提升很多。

作者:老王
原文:http://hi.baidu.com/thinkinginlamp/blog/item/5d72dd5469b1885fd0090633.html

Tags: database, mysql, 主从数据库, myisam, innodb

walker早期文章:通过MySQL内置全文检索实现中文的相关检索

一直在搜索这块有问题,中文搜索的话,不可避免的会遇到搜索 A 的时候会出现 B 结果的情形,这种情况在MYSQL4.0的时候犹为明显,最近在翻代码的时候,翻到walker以前写的一篇文章,依稀记得大概也是05年左右的事情了吧。

05年这家伙写了不少代码,什么BMP识别啥的。如今也都随着服务器的损坏而烟消云散了。借着google的光,把walker这篇以前的文章再COPY回来。为以后也开发可以做个参考,只是它的这个方法太占数据库空间了。呵呵

如今的walker代码是几乎不写了,以wow为主,可怜的人啊。。。。。http://www.walkerlee.net 是他现在的网站。

文章如下:
/**
*   @author   :   walkerlee
*   @copyright   :   www.neatstudio.com   |   www.walkerlee.net
*/

转载请保留以上信息。

关键字:MySQL   全文检索   全文索引   中文分词   二元分词   区位码   相似度

注:本文使用的MySQL版本为:MySQL   4.0.x

在MySQL4中,是已经开始支持全文检索(索引)的了。但是只是对英文支持全文检索。
由于英文在书写上的特殊性,使得分词算法相对中文来说,简单得多。一般来说,我们可以通过单词与单词之间的空格,以及标点符号来完成这个分词过程。
但是就中文来说,就没有那么简单。MySQL无法对中文做出正确的分词,假设有如下英文句子:

"Hello   world!   Hello   PHP! "

通过上面提及的方法,可以很简单的把这个句子分词为:

1   Hello
2   world
3   PHP

我们再来看看中文的句子:

"你好世界,你好PHP! "

按照英文的算法,分词如下:

1   你好世界
2   你好PHP

显然是不能满足我们的需要的。

所以,首先我们要做的是,把中文的句子转变为MySQL眼中的英文,以便使得它能以英文分词算法去对句子进行正确的分词处理。
先将上面中文句子进行标点过滤处理,得到以下句子:

你好世界   你好PHP

接着再使用中文分词中较简单实现的二元分词算法对句子进行二元分词,得到以下句子:

你好   好世   世界   你好   PHP

因为把标点符号替换为空格,以及PHP本身为英文字母的关系,可以不用进行二元切分,所以得到上面句子。
这个时候,我们来看看处理过后的句子,会发现,就其书写格式上来说,已经符合英文的书写格式,既以空格,标点来对单词形成自然间隔。只是上面句子没有标点,只有空格而已。
到此,我们已经成功的将中文“翻译”为MySQL能理解的“英文”书写格式。

但是,问题还没解决,首先,MySQL中,ft_min_word_len(分词词汇最小长度)这个参数的默认值为4,也就是4个字母以上长度的单词,才会被考虑,小于4个的,将会被忽略。
如果不改变这个长度,按照上面的分词结果,我们将无法通过   你好,世界,PHP等检索到相关的结果,因为分出来的词太短了,不在MySQL的选择范围内。
我们可以通过修改ft_min_word_len的值,将其设置为2来解决上面问题,但是这样做的话,在检索列表中的原本就为英文的短小词汇,如:PHP,MP3,也会被划入检索范围内,这样做的结果是,出现很多无意义的相关结果。

请看以下列表:

[MP3]   the   look
[MP3]   because   of   you

因为他们都同有MP3在标题中,所以会出现上述提到的问题。

回到ft_min_word_len值的问题,我们之所以要修改他,是为了能让MySQL找到我们的二元分词,但是短小的英文又被“无辜”的卷入,我们目 前要解决的问题就是,如何使得MySQL能检索到二个字的中文词汇,又能忽略掉原本的英数?第一个反应是把中文MD5,这样以上分词就将转化为以下结果:

你好   好世   世界   你好   PHP   =>   b94ae3c6d892b29cf48d9bea819b27b9   f5625345be46432fb0fd51340fcf6679   9067de5206278a93823f9c5dc2c737fd   b94ae3c6d892b29cf48d9bea819b27b9   PHP

这样做,首先是使得中文分词的长度超越了默认的2个字,同时消除了中文的歧义性。(MySQL4对中文的处理有问题),搜索“车轮”时候,不再会出现类似“发动机”结果的问题。(车轮的例子只是为了方便理解而做出的假设)

通过上面的做法,已经解决了分词最小长度的问题,顺利的把中文词汇长度升级,从而达到把中文词汇划入检索范围,把较短的英数划出检索范围。
休息一下,然后发现这个MD5后的字符串是否太长了点……比较占用空间,要不,于是想到区位码,4位数的区位码能表示一个GB汉字,一个词有二个汉字组成,转换为区位码后是8个数字。不但能确定惟一性,也就MD5而已减少了长度。下面是转换后的:

你好   好世   世界   你好   PHP   =>   b94ae3c6d892b29cf48d9bea819b27b9   f5625345be46432fb0fd51340fcf6679   9067de5206278a93823f9c5dc2c737fd   b94ae3c6d892b29cf48d9bea819b27b9   PHP   =>   36672635   26354232   42322971   36672635   PHP

呵呵,是不是比MD5的小了很多呢?最后我们把相同的词汇留一个,多余的删除。得到

36672635   26354232   42322971   PHP

于是就完成了   "你好世界,你好PHP! "   到   "36672635   26354232   42322971   PHP "   的转换。

通过上面方法结合MySQL全文检索语句,我们可以通过给出一个标题例如: "迈克尔·杰克逊   -《危险之旅之布加勒斯特站》 "找出类似以下的相关标题

迈克尔杰克逊   -《迈克尔杰克逊危险布加勒斯特演唱会》
Michael   Jackson   -《迈克尔杰克逊   罗马尼亚   危险演唱会》
迈克尔杰克Michael   Jackson   -《危险之旅》
迈克尔杰克逊   -《迈克尔杰克逊   美国50annive演唱会危险片段》
迈克尔杰克逊   -《迈克尔杰克逊   终极收藏   原版DVD危险演唱会》
迈克尔杰克逊     杰克逊五兄弟   -《The   Jackson   Motown   25   演唱会》
迈克尔杰克逊   -《迈克尔杰克逊BAD日本Yokohama演唱会》
迈克尔杰克逊   -《迈克尔杰克逊日本大阪演唱会》
迈克尔杰克逊   -《迈克尔杰克逊之胜利-达拉丝演唱会》
迈克尔杰克逊   -《迈克尔杰克逊之胜利演唱会   比丽珍   片段》
迈克尔杰克逊   -《迈克尔杰克逊德国危险演唱会之   billie   jean片段》
迈克尔杰克逊   -《Michael   Jackson   -30周年演唱会》
Michael   Jackson   -《迈克尔杰克逊   马尼拉   历史演唱会》
迈克尔杰克逊   -《1993年美国橄榄球中场休息精彩表演》

表结构   article
title     varchar   200         --------       用于存放标题   (显示用)
ft             text           ----         fulltext     用于存放标题分词结果   (检索用)

首先我们在把标题保存到数据库时候,就已经对标题进行分词转区位码,保存到ft字段中,用于相关性的检索。
然后把给出的标题 "迈克尔·杰克逊   -《危险之旅之布加勒斯特站》 "转为 "34853143   31432291   22910104   01042960   29603143   31434923   46034753   47535414   54143435   34355414   54141828   18282851   28513253   32534325   43254456   44565330 ",最后进行全文检索查询:

SELECT   title,   MATCH(   ft   )   AGAINST(   '34853143   31432291   22910104   01042960   29603143   31434923   46034753   47535414   54143435   34355414   54141828   18282851   28513253   32534325   43254456   44565330 '   IN   BOOLEAN   MODE   )   AS   score  
FROM   article  
WHERE   MATCH(   ft   )   AGAINST(   '34853143   31432291   22910104   01042960   29603143   31434923   46034753   47535414   54143435   34355414   54141828   18282851   28513253   32534325   43254456   44565330 '   IN   BOOLEAN   MODE   )  
ORDER   BY   score   DESC  
LIMIT   0,   5

从SQL   Query上来看,进行了两次全文检索,其实不然,MySQL会将其视为一次,所以不比担心。
同时使用了AS   score,这个score是相似度,分值越高,自然越与给出的标题相近。

二点建议:
1.在实际使用中,挑选score大于1的作为检索结果。
2.检索结果会将本身标题也算入其中,根据score排序,为第一条,别忘记过滤哦   ^_^。

站在用户的立场来说,我们给用户提供了更多的相关内容,站在搜索引擎立场上来说,给关键字提供了更多的相关链接,形成了良好的站内互联结构,提高了搜索引擎对网页的评价。

如果各位碰到错误的不合理的地方,恳请指正,共同进步。谢谢!

参考资料:

1.Monkey的二元分词   作者:Monkey   http://www.baidu.com/s?wd=monkey+%B6%FE%D4%AA%B7%D6%B4%CA&cl=3
2.PHP里如何实现汉字转区位码   提供者:haoyoul   http://zhidao.baidu.com/question/5371961.html
3.对dvbbs.php全文搜索的完全分析   作者:fcicq     http://www.phpx.com/happy/viewthread.php?tid=124691

 

 

Tags: walkerlee, 全文检索, mysql, 中文

MYSQL官方的文章:几种无限分类的算法……

我只贴一种,其余的去看:http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The Adjacency List Model

Typically the example categories shown above will be stored in a table like the following (I'm including full CREATE and INSERT statements so you can follow along):

CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);


INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;

+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see that FLASH is a child of mp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL.

Retrieving a Full Tree

The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

Tags: mysql, 无限分类, 算法, 存储结构, 官方

精通MYSQL数据库——连载十二

三大范式:第一范式,第二范式,第三范式,听着名字就很恐怖。但其实现在的人都被这个所谓的第三范式折腾死了,有事没事就拿出来涮涮,究竟怎么理解这些呢?一个一个慢慢的介绍。

数据库理论家们为数据库的设计1对N,N对N这种问题总结出了一个通用的解决方案,只需一步一步地三个范式(Normal Form)的规则应用到自己的数据库上就可以了。

第一范式的规则:

1、内容相似的数据必须“消除”(所谓消除,即再创建一个表来存储他们)

2、必须为每一组相关数组分别创建一个数据表

3、每条数据记录必须用一个主键来标识

第一条规则,看上去就比较适用于1对多的情况

第二条规则就不太好控制了,很多人认为第二条规则很难理解,数据的相关度,很难简单的描述清楚

第三条规则其实是一个实践经验,它的意思是数据表里的第一个数据行都应该包括一个独一无二的标识符作为索引。在使用MYSQL的时候,我们大多采用了自增字段来作为主键,但并非只有整数的自增字段才能作为索引,只要是独一无二的数据列,都可以用来做索引,之所以采用自增列,那是因为:1、不需要主动插入值2、整数列的时间和空间效率相对比其他类型的要高。

第二范式的规则:

1、只要数据列里的内容出现重复,就意味着应该把数据表拆分为多个子表

2、拆分形成的数据表必须用外键关联起来

第二范式,其实是在第一范式的基础上再进行一个拆分。指的就是第一范式规则的第二条内容。

外键关系在第二范式里显得特别重要,是因为第二范式时,数据会拆得更细,如果没有外键关联,恐怕数据就找不回来了。外键相当于我们日常所说的:交叉引用,对开发人员来说相当于指针。

第三范式的规则

第三范式只有一条规则:与主键没有直接关系的数据列必须“消除”

其实也就是把第二范式再分解,再建表。

可想而知,等到真正把一堆数据完全按照第三范式来进行设计的时候,恐怕在数据库里也就只能看到一堆ID了,数据呢?数据在哪里?通过外键,外键的外键,外键的外键的外键来慢慢的一个一个搜索吧。

对于MYSQL 4.1以下的版本,第三范式是会要了他们的命的,foreign key功能的不完善,让MYSQL 4.1以下版本,基本不适合第三范式,能用到第二范式设计时,数据表的效率已经几乎不能保证了。

这三个范式是著名学者E.F.Codd最先提出来的,后人在此基础 上对大到数学集合理论、小到关系数据库设计细节等诸多方面进行了研究和探索。如果对这方面有兴趣的朋友,还是多找找相关的书籍看看为好。

如果你的性子比较急,那么你可以尝试按照下面的方法来进行:

1、设计数据库的时候,一定要给自己充足的时间,如果等到数据库充满了数据,而程序也几乎开发完毕时,才发现数据库设计有缺陷,那么花费的代价就太大了

2、如果发现自己创建的表的数据列有序号,如name1,name2等,那一般就意味着还有更好的解决方案没有采用。可以考虑多创建一个表,而把这些分拆开。

3、第一时间往数据库里多插点测试数据,如果发现冗余量很大,往往就是表需要分拆的信号

4、设计时应该注意数据与数据之间的关联及引用关系

5、对于设计完的数据库,应该自己尝试写SQL语句,看看能否达到你预想的目标,如果达不到,那就要考虑是否设计的有问题。

6、如果你还是等不急,根据你的需要,到网上找找有没有类似的示例数据库,可以考虑拿来作借鉴。

说了这么多时间的范式,最后再说说他们的优缺点吧

缺点:数据表的个数越多,也就相对证明了从表单里获取数据并往表里插的时候,复杂性非常大,给开发人员会带来很大的困扰。同样,表多了,查询结果时,从中提取相关数据生成查询结果的复杂性也就越大;数据库的容量随着表的拆分量的增大而增大(不过现在也不是什么矛盾了,硬盘的价格几乎也快到了白菜价了,这点可以被忽略)

优点:严格按照范式设计出来的数据库,能够提供最丰富、最灵活的查询选项,人们往往都是在等到必须使用一种新的查询或者必须对数据进行一种新的分类时才会真正意识到这一点,但可惜的是,这些新需求往往都是出现在数据库已投入运行数月之后,到时候再改数据库,代价非常大。


现在工作有点忙,连载不会忘记,但更新频率会放慢,毕竟全部都是手工打出来的字。

不会象写小说那样太监掉的,毕竟这个东西对我自己来说,也是一种学习

给自己加油,为自己打气。也谢谢大家的支持

Tags: mysql, 精通, 数据库, 连载