MySQL ORDER BY 的实现分析
Saturday, November 22nd, 2008
总的来说，在 MySQL 中的ORDER BY有两种排序实现方式，一种是利用有序索引获取有序数据，另一种则是通过相应的排序算法，将取得的数据在内存中进行排序。
下面将通过实例分析两种排序实现方式及实现图解：
假设有 Table A 和 B 两个表结构分别如下：
sky@localhost : example 01:48:21> show create table A\G
*************************** 1. row ***************************
Table: A
Create Table: CREATE TABLE `A` (
`c1` int(11) NOT NULL default ‘0′,
`c2` char(2) default NULL,
`c3` varchar(16) default NULL,
`c4` datetime default NULL,
PRIMARY KEY (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
sky@localhost : example 01:48:32> show create table B\G
*************************** 1. row ***************************
Table: B
Create [...]
Innodb 索引结构了解 - Innodb Index Structure
Tuesday, November 18th, 2008
Innodb 作为 MySQL 中使用最为广泛的 事务型存储引擎，不仅在事务实现数据版本控制方面和其他存储引擎有一定的区别，其数据结构也是以非常有特点的方式存储的。
每个Innodb表的数据其实可以说就是以一个树型（B-Tree）结构存储的，表的数据和主键（Primary Key）共同组成了一个索引结构，也就是我们常说的Innodb的Clustered Primary Key。在这个Clustered Primary Key中，Leaf Nodes其实就是实际的表记录，我们常规理解上的索引信息全部在Branch Nodes上面。
除了Clustered Primary Key之外的其他所有索引在Innodb中被称为Secondary Index。Secondary Index就和普通的B-Tree索引差不多了，只不过在Secondary Index的所有Leaf Nodes上面同时包含了所指向数据记录的主键信息，而不是直接指向数据记录的位置信息。
所以，在 Innodb 中，如果主键值占用存储空间较大的话，会直接影响整个存储 Innodb 表所需要的物理空间，同时也会直接影响到 Innodb 的查询性能。
下面是画的一张 Innodb 索引基本结构图，包括 Primary Key 和 Secondary Index 两种索引的比较。
原文出自： Innodb 索引结构了解 - Innodb Index Structure
MySQL 的 DW 解决方案(MySQL + Infobright)
Friday, November 14th, 2008
随着 BI (DW) 在各个企业中重要性的不断提升，各个数据库厂家都希望能搭上这辆班车。这不，MySQL 也联合 Infobright 一起推出了开源的 数据仓库解决方案，而且是开源的。
其实现的各种DW该有的功能就不多说了，但是 Infobright 有一点非常吸引人的技术特点不能不提，那就是以列为导向的架构设计。
以列为导向的架构设计是非常适合于DW应用场景的，对于大多数DW的分析场景中，实际关注的数据很多时候都只有那么一列或者少数几列的数据。所以在以列为导向的设计中，大部分的分析查询都只需要读取某一个（或者几个）表的几列，而不需要像传统以行为导向的数据库（或者存储引擎）那样需要扫描整个表的数据，这两者IO量的差距是非常大的。除了以列为导向的架构设计之外，Infobright 和很多其他的DW解决方案一样，也会进行数据压缩，而且由于其以列为导向的存储方式，压缩比率在很多情况下都会比以行为导向的存储方式更高，效果更理想。有人通过测试比较，常规的以行为导向的存储数据压缩比率较高的时候也就 3:1 左右，但是 Infobright 的却很容易就做到 10:1 的压缩比率。
此外，从MySQL 以及 Infobright 的官方报道中除了上述技术特点（或者说优势）之外，还有很多其他的被描绘的非常神奇的功能，如被称为 “知识网格” (Knowledge Grid) 的自我管理功能，完全不需要索引或者分区，神奇的自我查询优化器等等。
这里是官方给出的一张 Infobright 的架构图：
感兴趣的朋友可以通过自行阅读其 技术白皮书 获取更多的细节
原文链接：MySQL 的 DW 解决方案(MySQL + Infobright)
MySQL数据库优化与架构设计一书写作计划调整
Thursday, October 23rd, 2008
开始着手写这本书也有40来天了，期间有不少热心网友不断的送来非常好的建议，也得到了身边同事和朋友的支持，目前进展基本还算顺利。
由于多方面原因的考虑，和出版社商量之后决定暂停正在前面两“基础”和“维护”这两篇的写作，而先转向第三篇“优化”的写作，完成第三篇之后也会继续往后直接进行“架构设计”部分的写作，然后再转回完成第一篇和第二篇的内容完善。
谢谢各位朋友的支持！
原文首发： Sky.Jian 朝阳的天空
原文链接：MySQL数据库优化与架构设计一书写作计划调整
Free Oracle Database Monitoring Tool
Monday, October 13th, 2008
前几天看到支付宝黄忠搞了个Free Oracle Database Monitoring Tool
URL： http://www.oramon.org/
我有空也搞了下，这个工具相当于自己建了个schema，然后用crontab去定时到各个数据库去探测，服务是否可用。
技术层面是：LINUX+APACHE+PHP+ORACLE来实现的。
我截张图给大家，这个工具实现的功能：
Silverlight技术
Monday, October 6th, 2008
Silverlight是一种全新的web展示方式。
最早的网络，是以静态页面为主，后来有了动态（如ASP,ASP.NET,JAVA,PHP等）。在后期，在动态上做了扩展，就是有了ajax。ajax最大的好处，就是页面无刷新，用户体会不到每个post和fresh。ajax的后台其实是一堆js脚本实现的。
但是想想，现在的网络技术，还是不能做出类似flash那种东西。因此有了Silverlight，它的好处在于程序员能够轻松的写出各种各样的花哨界面。感觉在未来会比较流行。目前Silverlight在国内，应用不多，但是已经有很多国外的demo网站了。
我觉得比较适合51，开心网之类的交友网站。因为他们要“炫”为主！
MySQL Replication(复制)基本原理
Wednesday, September 24th, 2008
1、复制进程
Mysql的复制（replication）是一个异步的复制，从一个Mysql instace（称之为Master）复制到另一个Mysql instance（称之Slave）。实现整个复制操作主要由三个进程完成的，其中两个进程在Slave（Sql进程和IO进程），另外一个进程在 Master（IO进程）上。
要实施复制，首先必须打开Master端的binary log（bin-log）功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。
复制的基本过程如下：
1)、Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
2)、Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；
3)、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪 个位置开始往后的日志内容，请发给我”；
4)、Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。
实际上在老版本的Mysql的复制实现在Slave端并不是两个进程完成的，而是由一个进程完成。但是后来发现这样做存在较大的风险和性能问题，主要如下：
首先，一个进程就使复制bin-log日志和解析日志并在自身执行的过程成为一个串行的过程，性能受到了一定的限制，异步复制的延迟也会比较长。
另外，Slave端从Master端获取bin-log过来之后，需要接着解析日志内容，然后在自身执行。在这个过程中，Master端可能又产生了大量 变化并声称了大量的日志。如果在这个阶段Master端的存储出现了无法修复的错误，那么在这个阶段所产生的所有变更都将永远无法找回。如果在Slave 端的压力比较大的时候，这个过程的时间可能会比较长。
所以，后面版本的Mysql为了解决这个风险并提高复制的性能，将Slave端的复制改为两个进程来完成。提出这个改进方案的人是Yahoo!的一位工程 师“Jeremy Zawodny”。这样既解决了性能问题，又缩短了异步的延时时间，同时也减少了可能存在的数据丢失量。当然，即使是换成了现在这样两个线程处理以后，同 样也还是存在slave数据延时以及数据丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在一个事物中，这些问题都是会存在的。如果要完全避免 这些问题，就只能用mysql的cluster来解决了。不过mysql的cluster是内存数据库的解决方案，需要将所有数据都load到内存中，这 样就对内存的要求就非常大了，对于一般的应用来说可实施性不是太大。
2、复制实现级别
Mysql的复制可以是基于一条语句（Statement level），也可以是基于一条记录（Row level），可以在Mysql的配置参数中设定这个复制级别，不同复制级别的设置会影响到Master端的bin-log记录成不同的形式。
Row Level：日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。
优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或function，以及 trigger的调用和触发无法被正确复制的问题。
缺点：row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条update语 句：update product set owner_member_id = ‘b’ where owner_member_id = ‘a’，执行之后，日志中记录的不是这条update语句所对应额事件（mysql以事件的形式来记录bin-log日志），而是这条语句所更新的每一条 记录的变化情况，这样就记录成很多条记录被更新的很多个事件。自然，bin-log日志的量就会很大。尤其是当执行alter table之类的语句的时候，产生的日志量是惊人的。因为Mysql对于alter table之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表。那么该表的每一条记录都会被记录到日志中。
Statement Level:每一条会修改数据的sql都会记录到 master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。
优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能。因为他只需要记录在Master上所执行的语句的细节，以及执行语句时候的上下文的信息。
缺点：由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信 息，以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于Mysql现在发展比较快，很多的新功能不 断的加入，使mysql得复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比 如：sleep()函数在有些版本中就不能真确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到 不一致的id等等。由于row level是基于每一行来记录的变化，所以不会出现类似的问题。
从官方文档中看到，之前的Mysql一直都只有基于statement的复制模式，直到5.1.5版本的Mysql才开始支持row level的复制。从5.0开始，Mysql的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给Mysql的复制又带来 了更大的新挑战。另外，看到官方文档说，从5.1.8版本开始，Mysql提供了除Statement Level和Row Level之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。在Mixed模式下，Mysql会根据执行的每一条具体的sql语句来区分对 待记录的日志形式，也就是在Statement和Row之间选择一种。新版本中的Statment level还是和以前一样，仅仅记录执行的语句。而新版本的Mysql中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句， 那么还是会记录所有行的变更。
3、复制常用架构
Mysql复制环境90%以上都是一个Master带一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只 要master和slave的压力不是太大（尤其是slave端压力）的话，异步复制的延时一般都很少很少。尤其是自slave端的复制方式改成两个进程 处理之后，更是减小了slave端的延时。而带来的效益是，对于数据实时性要求不是特别的敏感度的应用，只需要通过廉价的pc [...]
MySQL单表到底可以多大
Thursday, September 18th, 2008
前天突然收到Monitor Center发出来的报警：
PROBLEM:CN_DSL_***4/MAXFILE is CRITICAL,SNMP CRITICAL - *These File size is larger than 18932735283: /abc/def/ghi/***/***.MYD
刚看到这个报警的时候，还吓了一跳。数据文件超过最大文件限制？不会吧？好像才18G嘛，怎么会就开始报警了？
于是开始查找报警来源，经过一番查找，总算找到监控脚本。这台机器之前是SA管理维护的，所以相关规范也是按照SA管理的web服务器来设计的。所以他们 在监控系统中添加了某些特定的目录下文件大小的限制，防止某些意外出现大文件的情况。最后我只得在监控脚本中过滤掉了MySQL的“.MYD”和 “.MYI”文件。
说到文件大小，刚好常有人问我说MySQL是否有单个表的大小限制？限制多大呢？这里做一个简单的介绍吧。
在老版本的MySQL 3.22中，MySQL的单表限大小为4GB，当时的MySQL的存储引擎还是ISAM存储引擎。但是，当出现MyISAM存储引擎之后，也就是从 MySQL 3.23开始，MySQL单表最大限制就已经扩大到了64PB了（官方文档显示）。也就是说，从目前的技术环境来看，MySQL数据库的MyISAM存储 引擎单表大小限制已经不是有MySQL数据库本身来决定，而是由所在主机的OS上面的文件系统来决定了。
而MySQL另外一个最流行的存储引擎之一Innodb存储数据的策略是分为两种的，一种是共享表空间存储方式，还有一种是独享表空间存储方式。
当使用共享表空间存储方式的时候，Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所 以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单 表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。
而当使用独享表空间来存放Innodb的表的时候，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了。
以下是从收集到的一点信息，不一定全部准确：
操作系统 大小限制
win32 w/ FAT/FAT32 2GB/4GB
win32 w/ NTFS 2TB（可能更大）
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ 4TB(ext3)
Solaris 9/10 16TB
NetWare w/NSS filesystem 8TB
MacOS X w/ HFS+ 2TB
以下是MySQL文档中的内容：
Windows用户请注意： FAT和VFAT (FAT32)不适合MySQL的生产使用。应使用NTFS。
在默认情况下，MySQL创建的MyISAM表允许的最大尺寸为4GB。你可以使用SHOW TABLE STATUS语句或myisamchk -dv tbl_name检查表的最大尺寸。请参见13.5.4节，“SHOW语法”。
如果需要使用大于4GB的MyISAM表（而且你的操作系统支持大文件），可使用允许AVG_ROW_LENGTH和MAX_ROWS选项的CREATE TABLE语句。创建了表后，也可以使用ALTER TABLE更改这些选项，以增加表的最大允许容量。
原文首发： Sky.Jian 朝阳的天空
原文链接：MySQL单表到底可以多大
