欢迎访问我的新主页：http://huoding.com/

作者：老王

周末重读了一遍《高性能MySQL》，发现有些知识点看过便忘了，没有实际动手操作一遍就是记不牢，所以今天动手操作了一下“找出谁持有锁”，并把实验步骤记录下来，有兴趣的网友可以参照一二。

问题的背景：在实际使用MySQL时，如果访问量比较大，那么很可能会出现大量Locked状态的进程，但是却不能方便的识别是哪条SQL引起的问题，很多人遇到此类问题时，多半是通过PhpMyAdmin查询可疑SQL，然后KILL掉，但问题是可疑SQL可能会很多，这样逐一尝试太过笨拙，有的人一怒之下很可能会重启MySQL，但如此治标不治本的方法肯定更不可取。

开始实验，在test数据库先建立一个测试表foo（注意：是MyISAM表类型），添加若干数据：

CREATE TABLE IF NOT EXISTS `foo` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`str` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO `foo` (`id`, `str`) VALUES
(1, 'a'),
(2, 'b');

打开一个MySQL命令行终端：

mysql> USE test;
mysql> SELECT SLEEP(12345) FROM foo;

再打开一个MySQL命令行终端：

mysql> USE test;
mysql> UPDATE foo SET str='bar';

此时执行SHOW PROCESSLIST，可以看到已经出现Locked现象了：

10  User sleep  SELECT sleep(12345) FROM foo
20  Locked      UPDATE foo SET str = 'bar'

当然，我们知道是SLEEP堵塞了UPDATE，但如果不是这个实验，面对同样的情况，比如说几百个SQL查询同时映入眼帘，我们如何来判断呢？此时没人能打包票，只能瞎蒙了，经验有时候很重要，但我们还需要明确的命令，在这里就是：

mysqladmin debug

注意：如何你没有设定“.my.cnf”配置文件的话，可能需要输入用户名和密码参数

命令执行后，不会有任何明确的输出，不要着急，有价值的东西此时已经被保存到了错误日志里：

mysql> SHOW VARIABLES LIKE 'log_error';

找到错误日志的具体路径后，打开，查看日志的最后部分：

10  test.foo    Locked - read       Low priority read lock
20  test.foo    Waiting - write     High priority write lock

如此，我们就能看到id是10的SQL堵塞了id是20的SQL，至于具体的SQL，到SHOW PROCESSLIST里对照一下就能看到了。

欢迎访问我的新主页：http://huoding.com/

作者：老王

我是从《MySQL Admin Cookbook》一书中看到这个问题的，有一定的隐蔽性，遂记之。

友情提示：本文测试所用的MySQL版本是5.1.44-community，其它版本未测试，结果可能有差异。

先看看InnoDB中的情况：

CREATE TABLE enumerator (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
textvalue VARCHAR(30),
PRIMARY KEY (id)
) ENGINE=InnoDB;

然后执行SQL：

INSERT INTO enumerator
VALUES (0,'Zero'),(1,'One'),
(2,'Two'),(3,'Three');

报错：

#1062 - Duplicate entry '1' for key 'PRIMARY'

此时一行也没有被执行。

再次执行同样的SQL：

INSERT INTO enumerator
VALUES (0,'Zero'),(1,'One'),
(2,'Two'),(3,'Three');

结果成功了，表数据如下：

1 One
2 Two
3 Three
5 Zero

再看看MyISAM中的情况：

同样的表结构，只是类型变成了MyISAM，使用同样的SQL测试两次，每次都会报错：

#1062 - Duplicate entry '1' for key 'PRIMARY'

表数据如下：

1 Zero
2 Zero

总结：当表建立后，Autoincrement列的初始值是1，当我们第一次执行SQL，用0插入的时候，实际上就是被当1插入，而后面的数据又存在1， 所以重复报错，不过此时Autoincrement的当前值却已经被更新成了，只是InnoDB把它更新成了5，而MyISAM把它更新成了2，所以当我们执行第二次操作时产生了差异。从结果看，在更新Autoincrement的当前值时，InnoDB把多行插入SQL作为一个整体来看待，而MyISAM则把多行插入SQL中的每一行独立看待。

友情提示：这是引擎处理的差异，不算是Bug，但可能会造成一些非预期的结果，所以还是值得注意的。

补充内容：在InnoDB中，这个特性是可配置的，就是innodb_autoinc_lock_mode，缺省值是1，当值为1或者2时，会发生文中情况，否则不会。

欢迎访问我的新主页：http://huoding.com/

作者：老王

分页程序一般由两条SQL组成：

SELECT COUNT(*) FROM ... WHERE ....
SELECT ... FROM ... WHERE LIMIT ...

如果使用SQL_CALC_FOUND_ROWS的话，一条SQL就可以了：

SELECT SQL_CALC_FOUND_ROWS ... FROM ... WHERE LIMIT ...

在得到数据后，通过FOUND_ROWS()可以得到不带LIMIT的结果数：

SELECT FOUND_ROWS()

看上去，似乎SQL_CALC_FOUND_ROWS应该快于COUNT(*)，但实际情况并不是这样简单，请看：

To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

用数据说话，证明了COUNT(*)相对SQL_CALC_FOUND_ROWS来说更快。

不过我觉得这个结论也不全面，某些情况下，SQL_CALC_FOUND_ROWS更有优势，看我的实验：

表结构如下：

CREATE TABLE IF NOT EXISTS `foo` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` int(10) unsigned NOT NULL,
`c` varchar(100) NOT NULL,
PRIMARY KEY (`a`),
KEY `bar` (`b`,`a`)
) ENGINE=MyISAM;

导入一些测试数据：

for ($i = 0; $i <10000; $i++) {
mysql_query("INSERT INTO foo SET b=ROUND(RAND()*10), c=MD5({$i})");
}

先测试COUNT(*)方式：

$start = microtime(true);
for ($i = 0; $i < 1000; $i++) {
mysql_query("SELECT SQL_NO_CACHE COUNT(*) FROM foo WHERE b = 1");
mysql_query("SELECT SQL_NO_CACHE a FROM foo WHERE b = 1 LIMIT 100, 10");
}
$end = microtime(true);
echo $end - $start;

结果输出（数据大小视测试机性能而定）：0.75777006149292

再测试SQL_CALC_FOUND_ROWS方式：

$start = microtime(true);
for ($i = 0; $i < 1000; $i++) {
mysql_query("SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10");
mysql_query("SELECT FOUND_ROWS()");
}
$end = microtime(true);
echo $end - $start;

结果输出（数据大小视测试机性能而定）：0.6681969165802

有数据有真相，那为什么我的实验结论和MySQL Performance Blog的结论相悖呢？这是因为：

在MySQL Performance Blog的实验里，COUNT(*)查询是执行的的Covering Index，而SQL_CALC_FOUND_ROWS是执行的表查询；而在我的实验里，因为我定义了适当的索引，COUNT(*)和SQL_CALC_FOUND_ROWS都是执行的Covering Index，所以结论出现了差异。

既然使用了Covering Index，就意味着不能再使用SELECT *的形式了，只能使用类似SELECT id这样的形式了，用的列在索引里都能查到，如此说来，我们需要的实际数据从哪来呢？这个很简单，有了主键之后，实际数据可以通过Key/Value形式的缓存获得，这样的架构很常见。

结论：SQL_CALC_FOUND_ROWS如果执行的是Covering Index的话，是很快的！换个角度看，如果COUNT(*)和SQL_CALC_FOUND_ROWS都只能通过表查询来检索，那么分页时，SQL_CALC_FOUND_ROWS同样会快于COUNT(*)，读者可自行测试。

补充：Fast paging in the real world

欢迎访问我的新主页：http://huoding.com/

作者：老王

一直以来，多数人在使用MyISAM时都是按照增大Key_read_requests / Key_reads的原则来设置key_buffer_size的，没想到这竟然是错误的！这次给大家醍醐灌顶的仍然是MySQL Performance Blog，详细描述参考：Why you should ignore MySQL’s key cache hit ratio。

Key_read_requests和Key_reads就是两个计数器，它们的含义如下：

Key_read_requests：从缓存读取索引的请求次数。
Key_reads：从磁盘读取索引的请求次数。

通常人们认为Key_read_requests / Key_reads越大越好，否则就应该增大key_buffer_size的设置，但通过计数器的比例来调优有两个问题：

问题一：比例并不显示数量的绝对值大小
问题二：计数器并没有考虑时间因素

虽说Key_read_requests大比小好，但是对于系统调优而言，更有意义的应该是单位时间内的Key_reads：

Key_reads / Uptime

你可以通过命令行得到一个实时的数据结果，比如：

# mysqladmin ext -ri10 | grep Key_reads

| Key_reads                         | 83777189     |
| Key_reads                         | 211          |
| Key_reads                         | 177          |
| Key_reads                         | 202          |


提示：命令里的mysqladmin ext其实就是mysqladmin extended-status，你甚至可以简写成mysqladmin e。

其中第一行表示的是汇总数值，所以这里不必考虑，下面的每行数值都表示10秒内的数据变化，从这份数据可以看出每10秒系统大约会出现200次Key_reads访问，折合到每1秒就是20次左右，至于这个数值到底合理与否，就由服务器的磁盘能力而定了。

顺便说一句，为啥数据按10秒取样，而不是直接按1秒取样？这里看看按1秒的结果：

# mysqladmin ext -ri1 | grep Key_reads

| Key_reads                         | 83776743     |
| Key_reads                         | 7            |
| Key_reads                         | 7            |
| Key_reads                         | 38           |

可以看到，由于时间段过小，数据变化比较剧烈，不容易直观估计大小，所以通常数据按照10秒或者60秒之类的时间段来取样是更好的。

忘记：Key_read_requests / Key_reads
牢记：Key_reads / Uptime

补充链接：http://www.pythian.com/news/9035/database-tuning-ratio-vs-rate/

欢迎访问我的新主页：http://huoding.com/

作者：老王

三国演义开篇一语道破：合久必分！MySQL的使用亦是如此，面对应用中越来越庞大的数据量，最时髦的解决方案是Shard，不过它的复杂性并不是每个程序员都能驾驭的，如果把架构的演变比作生命的进化，那么Shard可以看做是哺乳动物，很多架构最后之所以失败，就是因为它们步子迈得太大，想从原始生命直接进化成哺乳动物，殊不知这中间还有爬行动物等必经阶段。

在MySQL没有支持Partition之前，如果想把数据分而治之，可以使用MySQL提供的Merge的引擎，例子：

先建立两个结构一样的MyISAM表：

CREATE TABLE foo_1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME
) ENGINE=MyISAM;

CREATE TABLE foo_2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME
) ENGINE=MyISAM;

再建立MERGE表：

CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME
) ENGINE=MERGE UNION=(foo_1, foo_2) INSERT_METHOD=LAST;

对调用者而言，MERGE表就像一个UNION语句一样，这样确实很方便，不过它有很多弱点：

首先，它不是完全透明的，需要有若干基础表（foo_1，foo_2）的存在，而且基础表必须是MyISAM表类型，另外，对于MERGE来说，不支持约束，比如上面的foo表定义中，虽然把id定义为主键，但是如果我们在foo_1和foo_2分别插入一个相同id的话，foo表也不会报错。

言归正传！MySQL从5.1.3开始支持Partition，你可以使用如下命令来确认你的版本是否支持Partition：

mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+

MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用：

CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
created DATETIME,
PRIMARY KEY(id, created)
) ENGINE=INNODB PARTITION BY RANGE (TO_DAYS(created)) (
PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')),
PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01'))
)

即便创建完分区，也可以在后期管理，比如说添加一个新的分区：

ALTER TABLE foo ADD PARTITION (
PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2011-01-01'))
)

或者删除一个分区：

ALTER TABLE FOO DROP PARTITION foo_3;

通过检索information_schema数据库，能看到我们刚刚创建的分区信息：

SELECT * FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL

此时，打开MySQL的数据目录（SHOW VARIABLES LIKE 'datadir'）：

如果MySQL配置设置了innodb file per table为ON的话，由于上面定义的是INNODB，则会发现：

foo#p#foo_1.ibd
foo#p#foo_2.ibd

如果创建的是MyISAM表类型的话，则会发现：

foo#P#foo_1.MYD
foo#P#foo_1.MYI
foo#P#foo_2.MYD
foo#P#foo_2.MYI

由此可知通过分区，MySQL会把数据保存到不同的数据文件里，同时索引也是分区的，相对未分区的表来说，分区后单独的数据文件和索引文件的大小都明显降低，效率则明显提升。为了验证这一点，我们做如下实验：

INSERT INTO `foo` (`id`, `created`) VALUES
(1, '2008-01-02 00:00:00'),
(2, '2009-01-02 00:00:00');

然后执行SQL：

EXPLAIN PARTITIONS SELECT * FROM foo WHERE created = '2008-01-02';

会看到MySQL仅仅在foo_1分区执行这条查询。理论上效率肯定会快一些，至于具体多少，就看数据量了。实际应用分区的时候，我们还可以通过DATA DIRECTORY和INDEX DIRECTORY选项把不同的分区分散到不同的磁盘上，从而进步一提高系统的IO吞吐量。

重要提示：使用分区功能之后，相关查询最好都用EXPLAIN PARTITIONS过一遍，确认分区是否生效。

到底应该采用哪种分区类型呢？通常来说使用range类型是个不错的选择，不过也不尽然，比如说在主从结构中，主服务器由于很少使用SELECT查询，所以在主服务器上使用range类型的分区通常并没有太大意义，此时使用hash类型的分区相对更好一些，假设使用PARTITION BY HASH(id) PARTITIONS 10，那么当插入新数据时，会根据id把数据平均分散到各个分区上，由于文件小，所以效率高，更新操作会变得更快。

到底应该按哪个字段来分区呢？通常来说按时间字段分区是个不错的选择，不过还是应该按需求而定，通常有很多种划分应用的方式，比如说按时间，或者按用户，哪种用的多，就选哪种来分区。如果使用主从结构的话，还可能用的更灵活些，有的从服务器使用时间分区，有的从服务器使用用户分区，不过如此一来，当执行查询时，程序里应该负责选择正确的从服务器去查询，写个MySQL Proxy脚本应该可以透明实现。

分区虽然很爽，但目前的实现还有很多限制：

主键或者唯一索引必须包含分区字段：如PRIMARY KEY(id, created)，不过对INNODB来说，大主键不爽。
很多时候，使用了分区就不要再使用主键，否则可能影响性能。
只能通过int类型的字段或者返回int类型的表达式来分区：通常使用YEAR或TO_DAYS等函数。
每个表最多1024个分区：不可能无限制的扩展分区，而且过度使用分区往往会消耗大量系统内存。
采用分区的表不支持外键：相关的约束逻辑必须通过程序来实现。

希望看了上面的简单介绍，大家可以明白应该如何使用分区功能了，不要仅仅把眼光放在Shard等流行技术之上，而忽视了原本使用更简单的Partition，恐龙虽然仅仅是爬行动物，却统治了地球长达千万年，比作为哺乳动物的人类统治地球的时间长得多。

MySQL5.5优化了分区功能，具体信息参考：A deep look at MySQL 5.5 partitioning enhancements。

补充链接：Why you don’t want to shard.