终于看了一直景仰的High Performance MySQL Second Edition一书，看了一些章节并把其中一些观点记录了下来，本文是整理 chapter 5. Advance MySQL features 部分观点所得。

1. 何时cache
a) mysql query cache内容为 select 的结果集, cache 使用完整的 sql 字符串做 key, 并区分大小写，空格等。即两个sql必须完全一致才会导致cache命中。
b) prepared statement永远不会cache到结果，即使参数完全一样。据说在 5.1 之后会得到改善。
c) where条件中如包含了某些函数永远不会被cache, 比如current_date, now等。
d) date 之类的函数如果返回是以小时或天级别的，最好先算出来再传进去。
select * from foo where date1=current_date -- 不会被 cache
select * from foo where date1='2008-12-30' -- 被cache, 正确的做法
e) 太大的result set不会被cache (< query_cache_limit)

2. 何时invalidate
a) 一旦表数据进行任何一行的修改，基于该表相关cache立即全部失效。
b) 为什么不做聪明一点判断修改的是否cache的内容？因为分析cache内容太复杂，服务器需要追求最大的性能。

3. 性能
a) cache 未必所有场合总是会改善性能
当有大量的查询和大量的修改时，cache机制可能会造成性能下降。因为每次修改会导致系统去做cache失效操作，造成不小开销。
另外系统cache的访问由一个单一的全局锁来控制，这时候大量>的查询将被阻塞，直至锁释放。所以不要简单认为设置cache必定会带来性能提升。
b) 大result set不会被cache的开销
太大的result set不会被cache, 但mysql预先不知道result set的长度，所以只能等到reset set在cache添加到临界值 query_cache_limit 之后才会简单的把这个cache 丢弃。这并不是一个高效的操作。如果mysql status中Qcache_not_cached太大的话, 则可对潜在的大结果集的sql显式添加 SQL_NO_CACHE 的控制。
query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

4. 内存池使用
mysql query cache 使用内存池技术，自己管理内存释放和分配，而不是通过操作系统。内存池使用的基本单位是变长的block, 一个result set的cache通过链表把这些block串起来。因为存放result set的时候并不知道这个resultset最终有多大。block最短长度为 query_cache_min_res_unit, resultset 的最后一个block会执行trim操作。
(引用：High Performance MySQL 原书Figure 5-1 插图)

定长：空间浪费
变长：需清理碎片
block 小： 链表超长，访问大块数据效率低。

另外发现 surfchen 的 MySQL的Query Cache 对这方面的内容描述也不错，可以和本文互为补充。

1. HiveDB是在2007年5月"Bay Area Community Meetup"首次出现，底层基于Hibernate shards基础实现。Hibernate shards 则是 Google 的开发工程师在"20%工作时间可以干别的有兴趣事情"环境下开发出来的一个 Hibernate extension，贡献给开源社区希望发扬光大；

2. HiveDB推出到现在也不算短，开发进度相对平缓。功能上已经处于一个相对稳定1.0状态，核心功能已经基本没大的问题。作者声称已经在一个每秒请求数达数千次的，包含海量数据的生产环境稳定运行；

3. HiveDB/Hibernate shards所适用的典型场合就是一个海量记录的表，可以根据某个规则分开存到多个相同表结构的数据库服务器上。和HSCALE功能差不多，但HSCALE当前版本实际上还不能跨服务器的；

4. 可以查询跨服务器数据，但不能做 order/join；

5. 具有类似mysql proxy之类多服务器容错功能，单独服务器发生故障不影响系统正常运行，通过类似ha-jdbc思想实现；

6. 目前只支持Java语言，有支持各种语言如php/python/perl/ruby hive client的计划，但是目前只有一个python hive client测试版可用。


图片：按字段(Partition Key)切分典型场合
(图片来源：hivedb.org)

在大型的应用中，我们经常碰到MySQL的表数据需要无限扩充的情形。我们通常有以下一些解决方案，但是现成的方案都不是完美的。

比如，
MySQL master/slave: 只适合大量读的情形，未必适合海量数据。
MySQL cluster: 提供的可能不是大家想要那种功能。
MySQL proxy: MySQL master/slave配合
MySQL 5.1 partition: 只是将一个表存储上逻辑分开，部分改善了性能，但是可扩展性仍然是问题。
MySQL 按应用逻辑分表和分数据库，通过程序来决定数据存放的表，目前很多公司都是这么做的。它的主要问题是跨区查询，可参考Tim以前的文章MySQL分表实现上百万上千万记录分布存储的批量查询设计模式

使用程序来分表分服务器最大的问题是比较繁琐，需要程序做很多特殊处理，需要程序员了解数据存放在哪个服务器哪个表，这样，几乎所有的程序员都牵涉了进来， 也容易出错。那如果我们把分表的逻辑放到中间层则上层的应用就简单很多，而且可以单点控制分表的逻辑，方便调整与扩展。

HSCALE分表分数据库的思路

HSCALE就是这样一个产品，它是在MySQL proxy的 基础上，在MySQL proxy的层面将上层的请求分配到实际的表上。实际的原理是通过拦截SQL进行替换和服务器重定向再将SQL传递到目标服务器上。它的分表算法可以由自定义的Lua脚本来实现，非常灵活。目前已经能支持同数据库分表，跨数据库的实现也将增加，因为在MySQL proxy的框架下，这并不是很困难的事情。现在的版本或许不是很成熟，但是在原理上我觉得是基本上没多大障碍，发展下去将是一个不错的选择。

HSCALE具体的性能测试简单介绍如下。

• 测试

使 用HSCALE有2个开销，一是网络层面的，下面的测试环境大约MySQL proxy对每个SQL会增加0.02ms的网络延迟，如果增加了HSCALE, 则会增加到0.3ms，第2个开销则是MySQL proxy, Lua, SQL解析，HSCALE算法等造成，可看下面数据。

• 测试数据一

(图片来源：pero.blogs.aprilmayjune.org)

结论是最极端的情况下，在10个线程的情况下，使用MySQL proxy会需要大约3倍时间，HSCALE则是10倍。
注意结论是MySQL方面最优化的情况，查找一个三条记录的表。在实际环境中的latency和这个没有直接比例关系(比如1:3)。测试结果不太令人满意，幸好后面新版本MySQL proxy的测试数据得到了改善。

• 测试数据二

使用了MySQL proxy 的 svn版本，性能提升很大。MySQL/MySQL proxy从1:3提升到1:2, HSCALE同样也提升比较大。具体结果见连接。但是仍然迫切希望作者再有提升。

今天说到大部分技术Blog都以介绍国外技术与产品的文章为主，没有深度，当然我这篇也不例外。:)

mysql conference现场提问的一个资料，网上见过很多版本了，这个表格版本看起来最直观

Â	How many servers	Number of DBAs	How many web servers	Number of caching servers	Version of MySQL	Language, platform	Operating System
MySQL	1 M, 3 S	1/10	2	2	5.1.23	Perl,php and bash	Linux fedora
Sun	2 clustered, 2 individual	1.5	160+	8	5.0.21	Lots of stuff (java mostly)	Open Solaris
Flickr	166	At present 0	244	14	5.0.51	Php and some Java	Linux
Fotolog	140 databases on 37 instances	10 instances a DBA	70	40 ( 2 on each, 80 total)	4.11 and 4.4	Php, 90% Java	Solaris 10
Wikipedia	20	None, but everybody is kind of a DBA	70+200	40 ( 2 on each, 80 total)	Â	Php, c++, python	Fedora / Ubuntu
Facebook	30000 databases, 1800 db servers	2	1200	805	5.0.44 with relay log corruption patch	Php, python, c++ and erlang	Fedora / RHEL
Youtube	I can not say	3	I can not say say	I can not say	5.0.24	Python	SuSE 9

• 基本上用MySQL 5.0, 操作系统则是Linux的天下，开发语言用php,python,java,c++,另外facebook还用erlang的
• MySQL对DBA的需求较小，程序员就是dba
• facebook平均每个db server有20个数据库
• 前2个公司没多少参考价值(传统行业)，youtube 啥也不愿意说, I can not say...

表格原文在：

http://venublog.com/2008/04/16/notes-from-scaling-mysql-up-or-out/

mysql memory存储引擎(也称heap table)是一种高效的内存数据库，速度非常快，或可当作cache使用，适合需要对cache作关系查询的数据，可参考我以前的文章：
memcached vs MySQL Memory engine table 速度比较

但是mysql memory table有一些性能问题，其中之一就是varchar数据类型内存利用率不高，ebay也在使用这个特性，所以他的一位工程师解决了这个问题，并凭此和另外一个工具获得了 2008 MySQL Application of the Year

项目地址：http://code.google.com/p/mysql-heap-dynamic-rows/

As of March 2008, MySQL Heap Engine of any version is limited to fixed row format. It allocates fixed memory size for each record stored in a given Heap Table. For example, if table A has a VARCHAR(4000) column, MySQL will use at least 4000 bytes (plus other columns and per-record overhead) for every record regardless of whether it has that much user data. In this example, the table will use 4GB memory per 1M records.

去年写了篇MySQL分表实现上百万上千万记录分布存储的批量查询设计模式的文章，思路是基于MySQL手动分表的。缺点是增加了程序的复杂性。现在有一个更简单和方便的现成的产品了，那就是lbpool

lbpool适合以READ为主的应用场合。它的思路是建立在MySQL replication(master/slave)模式基础上的。当使用这种模式的时候，你的程序无需关心现在连接哪台服务器，它可以直接根据服务器的繁忙程度自动选择服务器。可以运行时候动态管理所有MySQL服务器的失败和重新连接。

自然MySQL Replication不适合的场合(master 有单点失败的问题:) lbpool 现在的版本可能也不适合。据说要增加其他数据库的支持。

lbpool: http://code.tailrank.com/lbpool

收到MySQL发来的5.1RC版发布的通知邮件。RC版相当于预览版，说明离正式版发布已经不远，有需要5.1的应用可以开始热身。

MySQL 5.1 的主要新特性有：Partition 和 Disk based cluster等，这对大型的网络应用非常有用。

虽然MySQL 5.1有很多cool features，但是在生产环境一直只敢用5.0，对于许多有实际需求的用户来说，5.1的发布是一个极诱惑又痛苦的选择。因为要面临现有数据的迁移，特别是对需要在线服务不能长时间停机的网站而言。

5.0 => 5.1 Upgrade 文档：
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html

另外推荐朋友的mysql中文网，对MySQL研究比较全面，目前上面还在招聘MySQL DBA :)
http://www.imysql.cn/

下载:MySQL 5.1.22 RC

公告邮件原文如下：

Dear MySQL users,

we are proud to present to you the MySQL Server 5.1.22-rc release,
the first 5.1 "release candidate" version of the popular open source
database.

Bear in mind that this is still a "candidate" release, and as with any
other pre-production release, caution should be taken when installing on
production level systems or systems with critical data. For production
level systems using 5.0, we would like to direct your attention to the
product description of MySQL Enterprise at:

http://mysql.com/products/enterprise/

The MySQL 5.1.22-rc release is now available in source and binary form
for a number of platforms from our download pages at

http://dev.mysql.com/downloads/

and mirror sites. Note that not all mirror sites may be up to date at
this point in time, so if you can't find this version on some mirror,
please try again later or choose another download site.

Please also note that some of our mirrors are currently experiencing
problems that may result in serving corrupted files. We are working with
the mirror maintainers to resolve this.

We welcome and appreciate your feedback, bug reports, bug fixes,
patches etc.:

http://forge.mysql.com/wiki/Contributing

The following section lists the changes from version to version in the
MySQL source code since the latest released version of MySQL 5.1, the
MySQL 5.1.21-beta release. It can also be viewed online at

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-22.html

在 JavaEye 上看到一篇对 MySQL FUD(Fear, uncertainty and doubt) 的文章
用MySQL InnoDB Benchmark 性能测试来说明

http://www.javaeye.com/topic/34676

文中提到："InnoDB 的磁盘性能很令人担心，MySQL 缺乏良好的 tablespace 真是天大的缺陷！……网上有用户反映存在同样的插入性能问题，百万行记录插入之后，插入速度下降到了 1/30，从开始的 1600行/秒衰退到 50行/秒……"

50行/秒这么慢我从来没见过，见也没人反驳，所以自己再专门验证一下。

Tim's 环境：
MySQL 5.0.26 x86_64
Linux x86_64

iddata 文件大小17G,未使用innodb_file_per_table分表选项
表1: 1000万行左右，表大小 2.2G，含主键有6个索引字段
表2: 3000万行左右，表大小 6.3G，含主键有6个索引字段
MySQL还有其他一些数据也在iddata中，但测试时候其他数据未访问。


MySQL Server Hardware 环境
双 XEON 3G
4G 内存， SCSI 硬盘
非专业非名牌，普通组装的服务器。


表结构就是一般的用户表，包含 int, varchar, datetime 字段类型，无 text,blob 类型字段。
单行长度 0.1K 左右

对表1测试2分钟插入操作，在1千万的表中每个调用插入1行，通过Java JDBC在另外一台测试机上调用。


服务器 CPU 30%

JDBC Result:
INSERT OK /ERR: 73824/0
Java HEAP:  7.12MB of 63.56 MB (11.2%) used
TIME elapse(sec): 129
AVG平均/CUR当前/MAX最大 Inserts/SEC: 572/620/620


再测试一个3000万的表，通过Java JDBC在另外一台测试机上调用。
可能上一个测试2分钟会让人觉得没有说服力，3000万的表二就测试久一点吧。测试了10多分钟，又插入了100万条新的数据
一个调用只插入1行，8个线程。使用了连接池。

测试结果速度非常稳定。
服务器 CPU 30%

OK /ERR: 1,006,907/0
HEAP:  6.57MB of 63.56 MB (10.3%) used
TIME elapse(sec): 1683
AVG/CUR/MAX Inserts/Sec: 598/586/647

测试结果是：3000万行的表再插入100万行速度持续在将近 600行/秒

论坛文章中还提到"看着文件尺寸 100KB 100KB 的增长，是没法体会痛苦的"，
根据MySQL文档，这个增长的size可以配置的，如果数据库大量INSERT, 可以把innodb文件设成每次增长100M或更大。

测试后打算回复一下，可惜JavaEye上新注册了的账号没有论坛发文的权限，只好先放在自己家了。感兴趣的朋友可以去那边讨论。

第二天补充：今天觉得文章说服力不够，因为我的测试表没有TEXT/BLOB字段，所以再测试下大字段，加了个TEXT字段，每行TEXT插入10K，整体速度比上个肯定会低一点，
 但可以证明没有线性下降的问题，也可以给大家提供更多参考。
 
表中已有1000万行记录，采用上面相同的程序。
服务器 CPU 持续在 30%左右，测试结果见下图



测试结果每行插入10k数据，速度维持在300行/秒左右。数据库写入速度大致为: 3M/S

下次有时间再试试单表1亿条记录的。我觉得速度应该还会保持。
 
 

SelectThreadManager 分表数据查询管理器
它为分表的每个database or server 建立一个 thread pool

addTask() - 添加任务
stopTask() - 停止任务
getResult() - 获取执行结果

最快的执行时间 = 最慢的 MySQL 节点查询消耗时间
最慢的执行时间 = 超时时间

某个 ThreadPool 忙时候处理流程
1. 假如 ThreadPoolN 非常忙，(也意味 DB N 非常忙)；
2. 新的查询任务到来，addTask(), 新的任务的一个thread加到ThreadPoolN任务排队中
3. 外层应用已经获得其他 thread 返回结果，继续等待
4. 外层应用等待超时的时间到，调用 stopTask() 设置该任务全部 thread 中的停止标志, 外层应用返回。
5. 若干时间后，ThreadPoolN取到该排队 Thread, 因为设置了停止位，线程直接运行完成。


2. JDBC 层实现
做一个 JDBC Driver 的包装，拦截 PreparedStatement, Statement 的 executeQuery()
然后调用 SelectThreadManager 完成

3. MySQL partition
MySQL 5.1 的 partition 功能由于单张表的数据跨文件，批量查询时候同样存在上述问题，不过它是在 MySQL 内部实现的，不需要外部调用者关心。其查询实现的原理应该大致类似。
但 partition 只解决了 IO 的瓶颈，并不能解决 CPU 计算的瓶颈，因此无法代替传统的手工分表方式。

有个几千万条记录的表 on MySQL 5.0.x，现在要读出其中几十万万条左右的记录

常用方法，依次循环：
select * from mytable where index_col = xxx limit offset, limit;

经验：如果没有blob/text字段，单行记录比较小，可以把 limit 设大点，会加快速度
问题：头几万条读取很快，但是速度呈线性下降，同时 mysql server cpu 99%
速度不可接受。

调用 explain select * from mytable where index_col = xxx limit offset, limit;
显示 type = ALL

在 MySQL optimization 的文档写到"All"的解释
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

看样子对于 all, mysql 就使用比较笨的方法，那就改用 range 方式？

因为 id 是递增的，也很好修改 sql

select * from mytable where id > offset and id < offset + limit and index_col = xxx

explain 显示 type = range, 结果速度非常理想，返回结果快了几十倍。