分享未来 ^Internet _Technology

1、 锁机制
当前MySQL已经支持 ISAM, MyISAM, MEMORY (HEAP) 类型表的表级锁了，BDB 表支持页级锁，InnoDB 表支持行级锁。
很多时候，可以通过经验来猜测什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁。
想要决定是否需要采用一个支持行级锁的存储引擎，就要看看应用程序都要做什么，其中的查询、更新语句是怎么用的。例如，很多的web应用程序大量的做查询，很少删除，主要是基于索引的更新，只往特定的表中插入记录。采用基本的MySQL MyISAM 表就很合适了。
MySQL中对表级锁的存储引擎来说是释放死锁的。避免死锁可以这样做到：在任何查询之前先请求锁，并且按照请求的顺序锁表。
MySQL中用于 WRITE（写） 的表锁的实现机制如下：
如果表没有加锁，那么就加一个写锁。
否则的话，将请求放到写锁队列中。
MySQL中用于 READ（读） 的表锁的实现机制如下：
如果表没有加写锁，那么就加一个读锁。
否则的话，将请求放到读锁队列中。
当锁释放后，写锁队列中的线程可以用这个锁资源，然后才轮到读锁队列中的线程。
这就是说，如果表里有很多更新操作的话，那么 Select 必须等到所有的更新都完成了之后才能开始。
从 MySQL 3.23.33 开始，可以通过状态变量 Table_locks_waited 和 Table_locks_immediate 来分析系统中的锁表争夺情况：
[code="sql"]
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
|Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
[/code]
在 MySQL 3.23.7（在Windows上是3.23.25）以后，在 MyISAM 表中只要没有冲突的 Insert 操作，就可以无需使用锁表自由地并行执行 Insert 和 Select 语句。也就是说，可以在其它客户端正在读取 MyISAM 表记录的同时时插入新记录。如果数据文件的中间没有空余的磁盘块的话，就不会发生冲突了，因为这种情况下所有的新记录都会写在数据文件的末尾（当在表的中间做删除或者更新操作时，就可能导致空洞）。当空洞被新数据填充后，并行插入特性就会自动重新被启用了。
如果想要在一个表上做大量的 Insert 和 Select 操作，但是并行的插入却不可能时，可以将记录插入到临时表中，然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现：
[code="sql"]
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> Insert INTO real_table Select * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;
[/code]InnoDB 使用行级锁，BDB 使用页级锁。对于 InnoDB 和 BDB 存储引擎来说，是可能产生死锁的。这是因为 InnoDB 会自动捕获行锁，BDB 会在执行 SQL 语句时捕获页锁的，而不是在事务的开始就这么做。
行级锁的优点有：
在很多线程请求不同记录时减少冲突锁。
事务回滚时减少改变数据。
使长时间对单独的一行记录加锁成为可能。
行级锁的缺点有：
比页级锁和表级锁消耗更多的内存。
当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的锁资源。
当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕。
使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了。
表级锁在下列几种情况下比页级锁和行级锁更优越：
很多操作都是读表。
在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：
[code="sql"]
Update tbl_name SET column=value Where unique_key_col=key_value;
Delete FROM tbl_name Where unique_key_col=key_value;
[/code]Select 和 Insert 语句并发的执行，但是只有很少的 Update 和 Delete 语句。
很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。
表级锁和行级锁或页级锁之间的不同之处还在于：
将同时有一个写和多个读的地方做版本（例如在MySQL中的并发插入）。也就是说，数据库/表支持根据开始访问数据时间点的不同支持各种不同的试图。其它名有：时间行程，写复制，或者是按需复制。
原文： Versioning (such as we use in MySQL for concurrent inserts) where you can have one writer at the same time as many readers. This means that the database/table supports different views for the data depending on when you started to access it. Other names for this are time travel, copy on write, or copy on demand.
按需复制在很多情况下比页级锁或行级锁好多了。尽管如此，最坏情况时还是比其它正常锁使用了更多的内存。
可以用应用程序级锁来代替行级锁，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它们是劝告锁（原文：These are advisory locks），因此只能用于安全可信的应用程序中。
2、 锁表
为了能有快速的锁，MySQL除了 InnoDB 和 BDB 这两种存储引擎外，所有的都是用表级锁（而非页、行、列级锁）。
对于 InnoDB 和 BDB 表，MySQL只有在指定用 LOCK TABLES 锁表时才使用表级锁。在这两种表中，建议最好不要使用 LOCK TABLES，因为 InnoDB 自动采用行级锁，BDB 用页级锁来保证事务的隔离。
如果数据表很大，那么在大多数应用中表级锁会比行级锁好多了，不过这有一些陷阱。
表级锁让很多线程可以同时从数据表中读取数据，但是如果另一个线程想要写数据的话，就必须要先取得排他访问。正在更新数据时，必须要等到更新完成了，其他线程才能访问这个表。
更新操作通常认为比读取更重要，因此它的优先级更高。不过最好要先确认，数据表是否有很高的 Select 操作，而更新操作并非很‘急需’。
表锁在一个线程在等待，因为磁盘空间满了，但是却需要有空余的磁盘空间，这个线程才能继续处理时就有问题了。这种情况下，所有要访问这个出问题的表的线程都会被置为等待状态，直到有剩余磁盘空间了。

表锁在以下设想情况中就不利了：
一个客户端提交了一个需要长时间运行的 Select 操作。
其他客户端对同一个表提交了 Update 操作，这个客户端就要等到 Select 完成了才能开始执行。
其他客户端也对同一个表提交了 Select 请求。由于 Update 的优先级高于 Select，所以 Select 就会先等到 Update 完成了之后才开始执行，它也在等待第一个 Select 操作。

下列所述可以减少表锁带来的资源争夺：
让 Select 速度尽量快，这可能需要创建一些摘要表。
启动 mysqld 时使用参数 --low-priority-updates。这就会让更新操作的优先级低于 Select。这种情况下，在上面的假设中，第二个 Select 就会在 Insert 之前执行了，而且也无需等待第一个Select 了。
可以执行 SET LOW_PRIORITY_UpdateS=1 命令，指定所有的更新操作都放到一个指定的链接中去完成。详情请看“14.5.3.1 SET Syntax”。
用 LOW_PRIORITY 属性来降低 Insert，Update，Delete 的优先级。
用 HIGH_PRIORITY 来提高 Select 语句的优先级。详情请看“14.1.7 Select Syntax”。
从MySQL 3.23.7 开始，可以在启动 mysqld 时指定系统变量 max_write_lock_count 为一个比较低的值，它能强制临时地提高表的插入数达到一个特定值后的所有 Select 操作的优先级。它允许在 WRITE 锁达到一定数量后有 READ 锁。
当 Insert 和 Select 一起使用出现问题时，可以转而采用 MyISAM 表，它支持并发的Select 和 Insert 操作。
当在同一个表上同时有插入和删除操作时，Insert DELAYED 可能会很有用。详情请看“14.1.4.2 Insert DELAYED Syntax”。
当 Select 和 Delete 一起使用出现问题时，Delete 的 LIMIT 参数可能会很有用。详情请看“14.1.1 Delete Syntax”
执行 Select 时使用 SQL_BUFFER_RESULT 有助于减短锁表的持续时间.详情请看“14.1.7 Select Syntax”。
可以修改源代码 `mysys/thr_lock.c'，只用一个所队列。这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助。
以下是MySQL锁的一些建议：
只要对同一个表没有大量的更新和查询操作混在一起，目前的用户并不是问题。
执行 LOCK TABLES 来提高速度（很多更新操作放在一个锁之中比没有锁的很多更新快多了）。将数据拆分开到多个表中可能也有帮助。
当MySQL碰到由于锁表引起的速度问题时，将表类型转换成 InnoDB 或 BDB 可能有助于提高性能。详情请看“16 The InnoDB Storage Engine”和“15.4 The BDB (BerkeleyDB) Storage Engine”。

在高访问量的网站下，MySQL自然成为瓶颈。因此MySQL 的优化成为我们需要考虑的问题，第一步自然想到的是 MySQL 系统参数的优化，作为一个访问量很大的网站（日20万人次以上）的数据库系统，不可能指望 MySQL 默认的系统参数能够让 MySQL运行得非常顺畅。

(1)、back_log： 要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。 back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效。 当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log的值了。默认数值是50，我把它改为500。

 (2)、interactive_timeout： 服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。 默认数值是28800，我把它改为7200。

(3)、key_buffer_size： 索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8M)，我的MySQL主机有2GB内存，所以我把它改为402649088(400MB)。

(4)、max_connections： 允许的同时客户的数量。增加该值增加mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 Too many connections 错误。 默认数值是100，我把它改为1024 。

(5)、record_buffer： 每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)，我把它改为16773120 (16M)

(6)、sort_buffer： 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)，我把它改为16777208 (16M)。

(7)、table_cache： 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。

(8)、thread_cache_size： 可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。

(9)、wait_timeout： 服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。 注：参数的调整可以通过修改/etc/my.cnf 文件并重启 MySQL 实现。这是一个比较谨慎的工作，上面的结果也仅仅是我的一些看法，你可以根据你自己主机的硬件情况（特别是内存大小）进一步修改。