数据库性能优化方案---锁和事务

在对数据表的索引进行一系列的细致处理之后
我们又面临了其它问题，比如锁的问题，事务的问题
尤其是在做ERP的时候，或者是在做大型交互系统的时候，
这种问题就尤为的明显和显著
而在处理中，MyISAM表和InnoDB又是两种完全不同的引擎
它们所带来的锁方案和锁特性又不尽相同。

表锁等待时间

我们首先来写一段PHP程序，数据表就用我们在做索引优化时建立的两张表

?

update.php

1 2	$pdo = new PDO( "mysql:host=localhost;dbname=dotest" , "root" , "123456" ); $pdo -> exec ( "UPDATE `mystable` SET `school` = `school` + 1;" );

然后用ab来模拟20个人的更新
ab -n 200 -c 10 http://local.com:8080/debug/test/mysqlupdate.php
在它慢慢压的时候，你在另一个窗口的mysql命令里输入select查询，会发现查不了，这就是被锁起来了
我们在mysql命令里输入很有用的一条指令：show processlist;
会出现下面这个表格
+-----+------+-----------+--------+---------+------+------------------------------+---------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+--------+---------+------+------------------------------+---------------------------------------------+
| 254 | root | localhost | dotest | Query | 33 | Waiting for table level lock | select * from mystable where `name`='211' |
| 618 | root | localhost | dotest | Query | 0 | NULL | show processlist |
| 626 | root | localhost | dotest | Query | 7 | Updating | UPDATE `mystable` SET `school` = `school`+1 |
| 627 | root | localhost | dotest | Query | 6 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
| 628 | root | localhost | dotest | Query | 5 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
| 629 | root | localhost | dotest | Query | 5 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
| 630 | root | localhost | dotest | Query | 4 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
| 631 | root | localhost | dotest | Query | 3 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
| 632 | root | localhost | dotest | Query | 2 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
| 633 | root | localhost | dotest | Query | 2 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
| 634 | root | localhost | dotest | Query | 1 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
| 635 | root | localhost | dotest | Query | 0 | Waiting for table level lock | UPDATE `mystable` SET `school` = `school`+1 |
+-----+------+-----------+--------+---------+------+------------------------------+---------------------------------------------+
这是在说，当下面的都执行完，才会执行可怜的select。
上述结果就是在大量写入时，锁对性能的影响
不过我们在mysqlreport里可以查询，看看写操作又占多少的分量，来确定这个表是否合适

行锁

行锁能让在写入一行的时候其它行可读，这是InnoDB事务表的特有性能
我们把上面那个文件稍微改写一下，让它适用于行锁
众所周知，用主键做唯一约束条件时，就会自动加上行锁，其它的约束都会直接给innoDB加上表锁

?

update.php

1 2 3 4

$pdo = new PDO( "mysql:host=localhost;dbname=dotest" , "root" , "123456" ); for ( $i =0; $i < 100; $i ++) {      $pdo -> exec ( "UPDATE `stable` SET `school` = `school`+1 WHERE id=" . $i . ";" ); }

运行命令：ab -n 20 -c 20 http://local.com:8080/debug/test/mysqlupdate.php
Concurrency Level: 20
Time taken for tests: 11.159 seconds
然后我们稍微动一下手脚，让它的目标表变为MyISAM表
Concurrency Level: 20
Time taken for tests: 0.111 seconds
好吧，居然相差了这么多
我们再加大压力：ab -n 2000 -c 200 http://local.com:8080/debug/test/mysqlupdate.php
MyISAM的表现：
Concurrency Level: 200
Time taken for tests: 12.483 seconds
InnoDB的表现：
Concurrency Level: 200
Time taken for tests: 108.422 seconds
从100倍的差距缩到了10倍，说明行锁对搜索的表现并不是一直都很致命
最后，我们直接去进行一次全表搜索
在不用索引的情况，我们对school进行搜索
MyISAM用了0.0002秒，InnoDB用了0.007秒
由此可见，在某种程度上来说，InnoDB在数据量增大时性能也在提升

事务

在上面的比较中，发现中小型应用里，都是MyISAM更占优势，那么我们为什么又经常选用InnoDB呢？
InnoDB它有事务，回滚和崩溃修复能力的事务型表，提供行锁和外键约束。
InnoDB的涉及目标是处理大容量数据库系统，它的CPU利用率很高，也是MySQL后台的完整数据库系统，有专用的告诉缓冲池。
这一系列特性让我们有足够的理由选择它。
所以事务的性能提升，就必须是我们关注的课题

预写日志方式（WAL）是Innodb实现事务的方法。
当有事务提交时，Innodb首先将它写到内存中的事务日志缓冲区，然后当事务日志写进磁盘时，Innodb才更新实际数据和索引。
这里有一个关键点，就是事务日志何时写入磁盘
MySQL提供了一格配置项，它有三个可选值
innodb_flush_log_at_trx_commit = 1
表示事务提交时立即将事务写入磁盘，同时更新数据和索引
innodb_flush_log_at_trx_commit = 0
事务提交时不直接写入磁盘文件（内存），而是每隔1秒写入磁盘文件并刷新到磁盘，同时更新数据和索引，如果mysqld崩溃，那么一秒钟的数据全没了
innodb_flush_log_at_trx_commit = 2
事务提交立刻写入磁盘文件（在内存中），但不立刻刷新到磁盘，而是每一秒刷一次，同时更新数据和索引，如果操作系统崩溃，那么一秒钟的数据全没了
将这个变量设置为0时将获得最大性能，但它的数据丢失可能性也最大
或许我们可以考虑折中的设置2,不过这都是要经过考虑的

另一个重要配置是Innodb的索引内存缓冲池大小
我们可以通过Innodb_buffer_pool_size选项来设置这个数值
如果MySQL中大量使用Innodb类型表，可以将缓冲池大小设置为物理内存的80%,并使用mysqlreport关注使用率
__ InnoDB Buffer Pool __________________________________________________
Usage 48.72M of 128.00M %Used: 38.06
Read hit 94.04%

另外，我们还可以设置innodb_flush_method = O_DIRECT来跳过文件系统缓冲区来提高I/O性能