分类： Mysql/postgreSQL
2009-05-12 15:40:23
 讨论 关于mysql细节的探讨1、100万以上速度变慢是怎么变的？纯查询应该不会，如果是写入，考虑用INNODB，提供更加细粒度的锁。 2、太泛了，和你的查询条件，查询语句有关。 3、这个时候一般是表坏了，需要myisamchk 4、没有太多好方法，毕竟MYSQL的在线备份一直没有解决。 |
| 讨论 关于mysql细节的探讨 今天转一个帖字 关于设计合理的数据库表 原文引自 如果有不清楚的清查看原文，并希望大家对此评论优劣，探讨设计技巧 [code] 在动态网站的设计中，数据库设计的重要性不言而喻。如果设计不当,查询起来就非常吃力，程序的性能也会受到影响。无论你使用的是mySQL或者Oracle数据库，通过进行正规化的表格设计，可以令你的PHP代码更具可读性，更容易扩展，从而也会提升应用的性能。 简单说来，正规化就是在表格设计时，消除冗余性和不协调的从属关系。在本文中，我将通过五个渐进的过程来告诉你在设计中应该了解的正规化技巧。从而建立一个可行而且效率高的数据库。本文也会详细分析一下可以利用的关系类型。 这里假定我们要建立一个用户信息的表格，其中要存储用户的名字、公司、公司地址和一些个人的收藏夹或url。在开始时，你可能定义一个如下的表格结构： 零状态形式 users name company company_address url1 url2 Joe ABC 1 Work Lane abc.com xyz.com Jill XYZ 1 Job Street abc.com xyz.com 由于没有进行任何的正规化处理，我们将这种形式的表称为零状态形式的表。留意其中的url1和url2字段---如果我们在应用中需要第三个url 呢？这样你就要在表格中多加一列，很明显，这不是一个好办法。如果你要创建一个富有扩展性的系统，你就要考虑使用第一个正规化的形式，并且应用到该表格 中。 第一级正规化形式 1．消除每个表格中重复的组 2．为每套相关的数据建立一个独立的表格 3．使用一个主键来标识每套相关的数据 以上的表格明显违反了上面第一条的规定，那么第三条的主键又是什么意思呢？很简单，它只是在每个记录中加入一个唯一的、自动增加的整型值。通过这个值，就可以将两个姓名一样的记录区分开来。通过应用第一级正规化形式，我们得到了以下的表格： users userId name company company_address url 1 Joe ABC 1 Work Lane abc.com 1 Joe ABC 1 Work Lane xyz.com 2 Jill XYZ 1 Job Street abc.com 2 Jill XYZ 1 Job Street xyz.com 现在我们的表格可以说已经处在第一级正规化的形式了，它已经解决了url字段的限制问题，不过这样的处理后又带来了一个新的问题。每次在user表中 插入一条记录的时候，我们都必须重复所有的公司和用户数据。这样不仅令数据库比以前大了，而且很容易出错。因此还要经过第二级正规化处理。 第二级正规化形式 1．为应用在多条记录的字段建立独立的表格 2．通过一个foreign key来关联这些表格的值 我们将url的值放在一个独立的表格中，这样我们就可以在以后加入更多的数据，而无需担心产生重复的值。我们还通过主键值来关联这些字段： users userId name company company_address 1 Joe ABC 1 Work Lane 2 Jill XYZ 1 Job Street urls urlId relUserId url 1 1 abc.com 2 1 xyz.com 3 2 abc.com 4 2 xyz.com 如上所示，我们创建了独立的表格，users表中的主键userid 现在与url表中的foreign key relUserId关联。现在的情况好象已经得到了明显的改善。不过，如果我们要为ABC公司加入一个员工记录呢？或者更多，200个？这样我们就必须重 复使用公司名和地址，这明显不够冗余。因此我们将应用第三级正规化方法： 第三级正规化形式 1．消除不依赖于该键的字段 公司名及地址与User Id都是没有关系的，因此它们应用拥有自己的公司Id： users userId name relCompId 1 Joe 1 2 Jill 2 companies compId company company_address 1 ABC 1 Work Lane 2 XYZ 1 Job Street urls urlId relUserId url 1 1 abc.com 2 1 xyz.com 3 2 abc.com 4 2 xyz.com 这样我们就将companies表中的主键comId 和users表中名字为relCompId的foreign key关联起来，就算为ABC公司加入200个员工，在companies中也只有一条记录。我们的users和urls表可以不断地扩大，而无需担心插 入不必要的数据。大部分的开发者都认为经过三步的正规化就足够了，这个数据库的设计已经可以很方便地处理整个企业的负担，此看法在大多数的情况下是正确 的。 我们可以留意一下url的字段--你注意到数据的冗余了吗？如果给用户用户输入这些url数据的HTML页面是一个文本框，可任意输入的话，这并没有 问题，两个用户输入同样收藏夹的概率较少，不过，如果是通过一个下拉式的菜单，只让用户选择两个url输入，或者更多一点。这种情况下，我们的数据库还可 以进行下一级别的优化--第四步，对于大多数的开发者来说，这一步都是忽略的，因为它要依赖一个很特别的关系--一个多对多的关系，这在我们的应用中是还 没有遇到过的。 数据关系 在定义第四个正规化的形式前，我想首先提一下三种基本的数据关系：一对一，一对多和多对多。我们回头看一下经过第一个正规化的users表。要是我们 将url的字段放在一个独立的表中，每次在users表中插入一个记录，我们就会在urls表中插入一行。我们将得到一个一对一的关系：用户表中的每一 行，都将在urls表中找到相应的一行。对于我们的应用来说，这既不实用也不标准。 然后看看第二个正规化的例子。对于每个用户记录，我们的表格允许有多个urls的记录与之关联。这是一个一对多的关系，这是一个很常见的关系。 对于多对多的关系来说，就有点复杂了。在我们的第三个正规化形式的例子中，我们的一个用户与很多的url有关，而我们想将该结构变为允许多个用户与多个的urls有关，这样我们就可以得到一个多对多的结构。在讨论前，我们先看看表格结构会有些什么变化 users userId name relCompId 1 Joe 1 2 Jill 2 companies compId company company_address 1 ABC 1 Work Lane 2 XYZ 1 Job Street urls urlId url 1 abc.com 2 xyz.com url_relations relationId relatedUrlId relatedUserId 1 1 1 2 1 2 3 2 1 4 2 2 为了进一步减低数据的冗余，我们运用第四级正规化形式。我们创建了一个颇奇怪的url_relations表，里面的字段均为主键或者foreign key。通过这个表，我们就可以消除urls表中的重复项目。以下是第四个正规化形式的具体要求： 第四个正规化形式 1．在一个多对多的关系中，独立的实体不能存放在同一个表格中 由于它仅应用于多对多的关系，因此大多数的开发者可以忽略这条规定。不过在某些情况下，它是非常实用的，这个例子就是这样，我们通过将相同的实体分离出来，并且将关系移到它们自己的表格中，从而改进了urls表格。 为了令你更容易明白，我们举个具体的例子，以下将用一个SQL语句选择出所有属于joe的urls： SELECT name, url FROM users, urls, url_relations WHERE url_relations.relatedUserId = 1 AND users.userId = 1 AND urls.urlId = url_relations.relatedUrlId 如果我们想要遍历每个人的个人信息和url信息，我们可以这样做： SELECT name, url FROM users, urls, url_relations WHERE users.userId = url_relations.relatedUserId AND urls.urlId = url_relations.relatedUrlId 第五级正规化形式 还有一级正规化的形式，它并不常见，有点深奥，并且在大部分的情况下都是不必要的。它的原则是： 1．原来的表格必须可以通过由它分离出去的表格重新构建 使用这个规定的好处是，你可以确保不会在分离的表格中引入多余的列，所有你创建的表格结构都与它们的实际需要一样大。应用这条规定是一个好习惯，不过除非你要处理一个非常大型的数据，否则你将不需要用到它 [/code] |
| 讨论 关于mysql细节的探讨 索引的例子引自 同时超越php也有相关连接 [code] 就象许多的PHP开发者一样，在刚开始建立动态网站的时候，我都是使用相对简单的数据结构。PHP在连接数据库方面的确实是十分方便（译者注：有些人认为 PHP在连接不同数据库时没有一个统一的接口，不太方便，其实这可以通过一些扩展库来做到这一点），你无需看大量的设计文档就可以建立和使用数据库，这也 是PHP获得成功的主要原因之一。 前些时候，一位颇高级的程序员居然问我什么叫做索引，令我感到十分的惊奇，我想这绝不会是沧海一粟，因为有成千上万的开发者（可能大部分是使用MySQL的）都没有受过有关数据库的正规培训，尽管他们都为客户做过一些开发，但却对如何为数据库建立适当的索引所知较少，因此我起了写一篇相关文章的念头。 最普通的情况，是为出现在where子句的字段建一个索引。为方便讲述，我们先建立一个如下的表。 CREATE TABLE mytable ( id serial primary key, category_id int not null default 0, user_id int not null default 0, adddate int not null default 0 ); 很简单吧，不过对于要说明这个问题，已经足够了。如果你在查询时常用类似以下的语句： SELECT * FROM mytable WHERE category_id=1; 最直接的应对之道，是为category_id建立一个简单的索引： CREATE INDEX mytable_categoryid ON mytable (category_id); OK，搞定？先别高兴，如果你有不止一个选择条件呢？例如： SELECT * FROM mytable WHERE category_id=1 AND user_id=2; 你的第一反应可能是，再给user_id建立一个索引。不好，这不是一个最佳的方法。你可以建立多重的索引。 CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id); 注意到我在命名时的习惯了吗？我使用"表名_字段1名_字段2名"的方式。你很快就会知道我为什么这样做了。 现在你已经为适当的字段建立了索引，不过，还是有点不放心吧，你可能会问，数据库会真正用到这些索引吗？测试一下就OK，对于大多数的数据库来说，这是很容易的，只要使用EXPLAIN命令： EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2; This is what Postgres 7.1 returns (exactly as I expected) NOTICE: QUERY PLAN: Index Scan using mytable_categoryid_userid on mytable (cost=0.00..2.02 rows=1 width=16) EXPLAIN 以上是postgres的数据，可以看到该数据库在查询的时候使用了一个索引（一个好开始），而且它使用的是我创建的第二个索引。看到我上面命名的好处了吧，你马上知道它使用适当的索引了。 接着，来个稍微复杂一点的，如果有个ORDER BY字句呢？不管你信不信，大多数的数据库在使用order by的时候，都将会从索引中受益。 SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC; 有点迷惑了吧？很简单，就象为where字句中的字段建立一个索引一样，也为ORDER BY的字句中的字段建立一个索引： CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate); 注意: "mytable_categoryid_userid_adddate" 将会被截短为 "mytable_categoryid_userid_addda" CREATE EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC; NOTICE: QUERY PLAN: Sort (cost=2.03..2.03 rows=1 width=16) ->; Index Scan using mytable_categoryid_userid_addda on mytable (cost=0.00..2.02 rows=1 width=16) EXPLAIN 看看EXPLAIN的输出，好象有点恐怖啊，数据库多做了一个我们没有要求的排序，这下知道性能如何受损了吧，看来我们对于数据库的自身运作是有点过于乐观了，那么，给数据库多一点提示吧。 为了跳过排序这一步，我们并不需要其它另外的索引，只要将查询语句稍微改一下。这里用的是postgres，我们将给该数据库一个额外的提示--在 ORDER BY语句中，加入where语句中的字段。这只是一个技术上的处理，并不是必须的，因为实际上在另外两个字段上，并不会有任何的排序操作，不过如果加入， postgres将会知道哪些是它应该做的。 EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY category_id DESC,user_id DESC,adddate DESC; NOTICE: QUERY PLAN: Index Scan Backward using mytable_categoryid_userid_addda on mytable (cost=0.00..2.02 rows=1 width=16) EXPLAIN 现在使用我们料想的索引了，而且它还挺聪明，知道可以从索引后面开始读，从而避免了任何的排序。 以上说得细了一点，不过如果你的数据库非常巨大，并且每日的页面请求达上百万算，我想你会获益良多的。不过，如果你要做更为复杂的查询呢，例如将多张表结 合起来查询，特别是where限制字句中的字段是来自不止一个表格时，应该怎样处理呢？我通常都尽量避免这种做法，因为这样数据库要将各个表中的东西都结 合起来，然后再排除那些不合适的行，搞不好开销会很大。 如果不能避免，你应该查看每张要结合起来的表，并且使用以上的策略来建立索引，然后再用EXPLAIN命令验证一下是否使用了你料想中的索引。如果是的话，就OK。不是的话，你可能要建立临时的表来将他们结合在一起，并且使用适当的索引。 要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。 以上介绍的只是一些十分基本的东西，其实里面的学问也不少，单凭EXPLAIN我们是不能判定该方法是否就是最优化的，每个数据库都有自己的一些优化 器，虽然可能还不太完善，但是它们都会在查询时对比过哪种方式较快，在某些情况下，建立索引的话也未必会快，例如索引放在一个不连续的存储空间时，这会增 加读磁盘的负担，因此，哪个是最优，应该通过实际的使用环境来检验。 在刚开始的时候，如果表不大，没有必要作索引，我的意见是在需要的时候才作索引，也可用一些命令来优化表，例如MySQL可用"OPTIMIZE TABLE"。 综上所述，在如何为数据库建立恰当的索引方面，你应该有一些基本的概念了。 [/code] 最后看到蓝色键盘在informix的精华里面也有优化索引相关 大家也不妨看看 更多索引相关介绍可以看这里 超越php |
| 讨论 关于mysql细节的探讨 下面有关于备份的讨论 主要是针对mysqldump 在mysql精华里面也有好好先生的帖子 一起参考 我感觉还都比较简单 具体操作的时候以前的同时写过一个脚本 每天自动搜索mysql上的更新 并且把相应更新每天写道新的机器的mysql里面 这个是为了实现简单的数据同步 也可以看成备份的一个方法 |
| 讨论 关于mysql细节的探讨 热备份可以试试看mysqlhotcopy |
| 讨论 关于mysql细节的探讨 |
| 讨论 关于mysql细节的探讨 是不是可以利用Mysql的复制功能完成数据库的备份？ 下面是我闲时翻译的一段文字． 原文：http://dev.mysql.com/doc/mysql/en/Replication.html ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃ 6 Mysql的复制 从版本3.23.15开始，Mysql就开始有了数据库复制功能。本章介绍了Mysql提供的各种复制特性，主要包括：复制的概念，怎样建立复制服务器以及相应的复制选项。当然也包括FAQ以及一些解决故障的方法和建议。 要想了解与复制相关的SQL语句，请参考14.6节：复制语句。 建议您经常访问我们的网站：mysql.com，并关注我们关于此章的更新。复制功能在不断地进行改良，我们将会经常更新与之相关的信息。 本章目录 6.1 简介 6.2 复制实施描述 6.3 复制实施细节 6.3.1 主线程状态描述 6.3.2 从服务器I/O线程状态描述 6.3.3 从服务器SQL线程状态描述 6.3.4 中间文件和状态文件 6.4 怎样建立复制 6.5 不同版本间复制的兼容性 6.6 升级 6.6.1 升级到4.0或4.1 6.6.2 升级到5.0 6.7 特性和已知的问题 6.8 启动选项 6.9 FAQ 6.10 故障诊断 6.11 Bugs报告 6.1 简介 MySQL 3.23.15及以上版本支持单向复制。其中一个服务器作为主服务器，另一个或多个作为从服务器。主服务器把对数据库的更新写入二进制的日志文件，并且维护这些文件的索引以保持对日志文件的跟踪。这些日志文件作为更新记录被传送到从服务器。当某个从服务器与主服务器进行连接的时候，日志文件会提示主服务器上一次成功更新的最后位置，从服务器会捕捉到从那之后的变化并等待主服务器新的更新通知。 一个从服务器也可扮演主服务器的角色，比如在你想建立一个服务器复制链的时候。 注意当你使用复制的时候，被复制表的所有更新都会在主服器上执行。否则，我们必须在用户对主服务器的数据库表更新与用户对从服务器的数据库表更新两者之间避免冲突。 单向复制有利于速度，性能以及系统管理。 随着主/从服务器的建立，系统的性能也大大增强。当主服务器出现故障，可以把从服务器作为备用服务器。 通过在主从服务器间实现客户机查询的负载均衡可以有效提高系统对客户机的反应时间。SELECT查询在从服务器上的执行可以减轻主服务器的查询负载。为保持主从服务器的同步，数据修改命令还是直接在主服务器上执行。如果非数据更新查询居多的话，这种负载均衡策略是有效的，但这只是一种理想状况。 复制的另外一个好处就是我们可以通过从服务器来实现对数据的备份，而不会影响主服务器的正常运行。主服务器会一直保持更新，直到备份开始进行。具体见5.7.1节：数据库备份。 6.2 复制实施描述 MySQL复制实现的基础在于主服务器在二进制日志中保持跟踪数据库的所有变化（如更新，删除等）。因此，为了使用复制功能，我们必须在主服务器上开启二进制日志功能，请参阅5.9.4节：二进制日志。 每个从服务器都会收到主服务器记录在其二进制日志中的数据库更新情况，因此从服务器可以执行同样的数据库更新操作。 需要特别指出的是二进制日志只会在你开启该功能的那一刻起才开始记录。你建立的任何从服务器必须拥有你开启主服务器二进制日志功能之前主服务器数据库的拷贝。如果主从服务器的数据库在开启主服务器二进制日志功能之前不一致，从服务器会启动失败。 有一个办法可以把主服务器的数据拷贝到从服务器上，那就是命令：LOAD DATA FROM MASTER。注意这个命令只在MySQL 4.0.0可用，目前只适用于主服务器上的所有表均为MyISAM类型的情况之下。该命令获得一个全局读锁，也就是说在所有表被传送到从服务器之前不能执行任何数据更新操作。若在MySQL 5.0中我们实现表的免锁热备份后，这个全局读锁将不复存在。 由于以上种种限制，我们强烈建议您只有在主服务器上的数据量相对较小或是主服务器上 较长的读锁存在时间不会影响到系统的正常运行时才使用LOAD DATA FROM MASTER命令。 考虑到由于系统的不同，LOAD DATA FROM MASTER执行的速度也不一样，可以用传输1M数据所需的秒数来进行量化。当然这只是一个粗略估计，但我们可以建立实际环境测试一下，比如主从服务器都用700MHz Pentium处理器以及100M的网络连接。 在从服务器建立主服务器数据的拷贝后，它只是简单地与主服务器保持连接并等待处理数据更新。如果主服务器发生故障或从服务器失去与主服务器的连接，从服务器会不间断地定时去连接主服务器，直到重新建立连接并恢复监听数据更新情况。重试的时间间隔由选项--master-connect-retry决定，缺省为60秒。 每个从服务器主动跟踪主服务器。主服务器根本不会去了解有多少个从服务器或在某个时间有哪些从服务器是保持连接的。 附录： MySQL 3.23.xx的数据拷贝：mysql/mysqlsnapshot/ 6.3 复制实施细节 MySQL的复制通过三个线程实现，一个在主服务器上，另外两个在从服务器上。当参数START SLAVE设定后，从服务器会建立一个I/O线程。该线程与主服务器建立连接并请求主服务器发送二进制日志中的记录。同时，主服务器会建立一个线程来向从服务器发送二进制日志文件的内容，在主服务器命令 SHOW PROCESSLIST 的输出中该线程显示为 Binlog Dump线程。 从服务器的I/O线程读取该线程发送过来的数据，并将其拷贝到本地数据目录下的中间日志文件中。第三个线程是SQL线程，该线程读取中间日志文件并执行其中的数据更新语句。 如前所述。每个从服务器拥有三个线程。对一个主服务器而言，它为每个与之连接的从服务器都要建立一个线程；而每个从服务器则拥有自己的I/O和SQL线程。 在4.0.2版的MySQL以前，复制只涉及到两个线程，主/从服务器各一个。从服务器上的I/O和SQL线程合并为一个线程，并且其中没有用到中间日志文件。 在从服务器上使用两个线程的目的在于可以将读取和执行这两个操作完全独立执行——当执行的速度减慢不会使读取速度的也减慢。比方说：若由于某种原因从服务器有一会儿没有运行，则当从服务器启动时I/O线程会迅速从主服务器那里取得二进制日志的所有内容，而这时SQL线程可能会滞后很长时间并需要数小时才能跟上。如果在SQL线程未执行完成所读取的内容情况下从服务器突然停止运行，这时I/O线程至少已取得了所有的数据并把数据的拷贝存放到了本地的中间日志文件，这样从服务器在下次启动时会继续执行。这一特性允许主服务器可以删除其上的二进制日志文件，因为它已不用等待从服务器来读取该文件的内容。 语句SHOW PROCESSLIST可以为我们提供主从服务器上有关复制的信息。 下例说明了三个线程在SHOW PROCESSLIST语句执行后的显示情况。输出格式是MySQL 4.0.15版的，State一栏的内容与以前版本相比要更有意义。 主服务器的输出为： mysql>; SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 2 User: root Host: localhost:32931 db: NULL Command: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL 在这里，线程2对连接的从服务器而言是一个复制线程。输出结果表明更新都已传输到了从服务器，主服务器正在等待更多的更新发生。 主服务器的输出为： mysql>; SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 10 User: system user Host: db: NULL Command: Connect Time: 11 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 11 User: system user Host: db: NULL Command: Connect Time: 11 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL 输出表明：线程10是一个与主服务器进行通信的I/O线程；线程11是一个SQL线程， 它用于处理中间日志文件中的更新语句。目前两个线程都处于空闲状态，在等待进一步的更新动作。 注意Time栏是指该从服务器性对于主服务器的延迟，见6.9节：FAQ。 |
| 讨论 关于mysql细节的探讨 [code]该命令获得一个全局读锁，也就是说在所有表被传送到从服务器之前不能执行任何数据更新操作。[/code] 这个是一个关键 如果数据库要求24小时不间断服务 就会有一定困难 原来我们的做法 说出来跟这个原理一样 每天凌晨3点作更新 其实还是有隐患的 |
| 讨论 关于mysql细节的探讨 up |
| 讨论 关于mysql细节的探讨 --> 用master/slave的办法不行吗? |
| 讨论 关于mysql细节的探讨 --> 对阿 光看别人玩这个了 有空我也式式 :P |
讨论 关于mysql细节的探讨