第十章：第22节 MySQL进阶篇——MyISAM并发插入和锁调度问题
更新于：2017-09-12 16:35:10
MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1、2或者NEVER、AUTO、ALWAYS。从5.5.3版本开始concurrent_insert参数采用枚举值，之前版本采用数值型。
当concurrent_insert设置为0或NEVER时，不允许并发插入。
当concurrent_insert设置为1或AUTO时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个session读表的同时，另一个session从表尾插入记录。这也是MySQL的默认设置。
当concurrent_insert设置为2或ALWAYS时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
做下演示：
前提条件：concurrent_insert采用默认值AUTO，“tt1”表暂时没有删除过数据。
分析：
1）（上边）1给tt1表加了读锁，（锁需要加“local”修饰符）。（下边）2插入数据成功，说明（下边）没受（上边）影响，实现了查询和插入操作的并发进行。
2）（上边）3插入数据失败，因为在读锁状态下不能写。（下边）4暂时不能更新数据操作，所以只有插入不受并发影响，更新或删除数据还是会被影响的。
上面的表是在没有删除数据的前提下才能实现并发插入的，假如删除一条数据，就不能实现并发插入了（可自己做下测试）。
一张表的数据我们不可能完全保证不被删除，如果中间真有数据被删除了，在 concurrent_insert为1 的前提下是不是就不能实现并发插入了？
其实也不是，可以用
OPTIMIZE TABLE 表名
整理空间碎片，收回因删除记录而产生的中间空洞。这样就可以继续实现并发插入了。当然也可以将concurrent_insert系统变量设为2，总是允许并发插入。
MyISAM的空间碎片
通过本章2、3节我们知道了MyISAM和InnoDB的数据存储方式不一样：
通常，在MyISAM里，新数据会被附加到数据文件的结尾，可如果时常做一些删除或更新操作之后，数据文件就不再是连续的，形象一点来说，就是数据文件里出现了很多洞洞。此时再插入新数据时，按缺省设置会先看这些洞洞的大小是否可以容纳下新数据，如果可以，则直接把新数据保存到洞洞里，反之，则把新数据保存到数据文件的结尾。之所以这样做是为了减少数据文件的大小，降低文件碎片的产生。
在InnoDB里，数据文件始终是按照主键排序的，如果使用自增ID做主键，则新数据始终是位于数据文件的结尾。
当然InnoDB也有碎片，可它不支持“OPTIMIZE TABLE 表名”清理碎片，这和它的数据储存方式有关系。可以通过执行语句ALTER TABLE table_name ENGINE = Innodb来整理碎片，提高索引速度。这其实是一个NULL操作，表面上看什么也不做，实际上重新整理碎片了。
当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间。
MyISAM的锁调度
MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个session请求某个 MyISAM表的读锁，同时另一个session也请求同一表的写锁，MySQL如何处理呢？答案是写（session）先获得锁。
不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！
这是因为MySQL认为写请求一般比读请求要重要。
这也正是MyISAM表不太适合于有大量更新操作和查询操作并用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！
MyISAM在读操作占主导的情况下是很高效的。可一旦出现大量的读写并发，同InnoDB相比，MyISAM的效率就会直线下降。
幸好我们可以通过一些设置来调节MyISAM 的调度行为：
通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。