转自：http://www.lanecn.com/article/main/aid-12
现在有很多的项目，对计数器的实现甚是随意，比如在实现网站文章点击数的时候，是这么设计数据表的，如：”article_id, menu_id, article_name, article_content, article_author, article_view……在article_view中记录该文章的浏览量。诈一看似乎没有问题。对于小站，比如本博客，就是这么做的，因为小菜的博客难道会涉及并发问题吗？答案显而易见，一天没多少IP，而且以后不会很大。
言归正传，对文章资讯类为主的项目，在浏览一个页面的时候不但要进行大量的查（查询上文的记录，已经所属分类的名字、热门文章资讯评论、TAG等），还要进行写操作（更新浏览数点击数）。把文章的详细内容和计数器放在一张表尽管对开发很方便，但是会造成数据库的压力过大（不然为什么大项目都要分库分表呢）。
那么，分两张表存放就好了么？一张表存文章详细信息，另一张表单独存计数器。
CREATE TABLE `article_view`(
`article_id` int(11) NOT NULL,
`view` int(11) NOT NULL,
PRIMARY KEY (`article_id`)
)ENGINE=InnoDB;
这种方式，虽然分担了文章表的压力，但是每当有一个进程请求更新的时候，都会产生全局的互斥锁，只能串行，不能并行。在高并发下会有较长的等待时间。
另一种比较好的办法是对每一个文章的计数器不是一行，而是多行，比如吧，一百行。每次随机更新其中一行，该文章的浏览数就是所有行的和。
CREATE TABLE `article_view`(
`article_id` int(11) NOT NULL,
`pond` tinyint(4) NOT NULL COMMENT '池子，就是用来随机用的',
`view` int(11) NOT NULL,
PRIMARY KEY (`article_id`, `pond`)
)ENGINE=InnoDB;
小访问量的随机池子100个肯定多了，三五个足矣。每次访问的时候，随机一个数字（1-100）作为pond，如何该pond存在则更新view+1，否则插入，view=1。借助DUPLICATE KEY，不然在程序里是实现得先SELECT，判断一下再INSERT或者UPDATE。
INSERT INTO `article_view` (`article_id`, `pond`, `view`) VALUES (`123`, RAND()*100, 1) ON DUPLICATE KEY UPDATE `view`=`view`+1
获取指定文章的总访问量的时候：
Ps：凡事都是双刃剑。为了更快的读我们通常要牺牲一些东西。在读比较多的表要加快读的速度，在写较多的表要加快写的速度。各自权衡。在加快读的速度的时候，我们牺牲的并不仅仅是写的性能，还有开发成本，开发变的更复杂，维护成本等。所以并不是读的速度越快越好，需要找一个平衡点。
注：这里仅仅是Mysql方面，有人会说高并发下你这是直接读写Mysql啦，项目的瓶颈本来就在数据库啦。。。其实。。。这里只是说Mysql的表怎么去设计而已。你完全可以在这个地方用队列去写表，你也可以把计数器在内存中保存，一直来累加，1个小时持久化一次。你也可以去用号称每秒读写十万次的Redis。
作者使用多行表示一篇文章的计数器，是为了加快更新速度，因为Innodb引擎默认是行级锁（即更新一行内容时会将该行锁定，另一个进程更新此行时会因为锁的原因无法更新），采用多行表示一篇文章的计数器时由于每个进程更新的行不同，因此可以同时进行更新。
———————————————————————————————————————————————–
ps:从上面文章中可以学习到两个知识点：
0x01、INSERT … ON DUPLICATE KEY UPDATE的使用
当需要实现“先判断是否存在数据库中，不存在则添加，存在则更新” 的功能时，可以不用“先select，判断一下再insert或update”的方式，而是可以使用DUPLICATE KEY这种更方便的方式。
INSERT INTO `article_view` (`article_id`, `pond`, `view`) VALUES (`123`, RAND()*100, 1) ON DUPLICATE KEY UPDATE `view`=`view`+1
mysql中的INSERT … ON DUPLICATE KEY UPDATE 语法可以参考：http://blog.csdn.net/ghsau/article/details/23557915。
向数据库插入记录时，有时会有这种需求，当符合某种条件的数据存在时，去修改它，不存在时，则新增，也就是saveOrUpdate操作。这种控制可以放 在业务层，也可以放在数据库层，大多数数据库都支持这种需求，如Oracle的merge语句，再如本文所讲的MySQL中的INSERT … ON DUPLICATE KEY UPDATE语句。
该语句是基于唯一索引或主键使用，比如一个字段a被加上了unique index，并且表中已经存在了一条记录值为1，下面两个语句会有相同的效果：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1;
ON DUPLICATE KEY UPDATE后面可以放多个字段，用英文逗号分割。使用ON DUPLICATE KEY UPDATE，最终如果插入了一个新行，则受影响的行数是1，如果修改了已存在的一行数据，则受影响的行数是2。
如果字段b也被加上了unique index，则该语句和下面的update语句是等效的：
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2匹配了多行，则只有一行会被修改。通常的，在ON DUPLICATE KEY UPDATE语句中，我们应该避免多个唯一索引的情况。如果需要插入或更新多条数据，并且更新的字段需要根据其它字段来运算时，可以使用如下语句：
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
在ON DUPLICATE KEY UPDATE后面使用VALUES()方法，这个语句等同于下面的两个语句：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;--1+2
INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;--4+5
如果一个表中包含了一个auto_increment的字段，每次insert数据后，可以通过last_insert_id()方法返回最后自动生成 的值，如果通过INSERT … ON DUPLICATE KEY UPDATE语句修改了一条数据，那么再通过last_insert_id()方法获取的值将不正确，实际测试中是多了一个数，比如向表中增加了3条数 据，那么通过last_insert_id()方法得到的值是3，但是通过该语句修改了一条数据后，通过last_insert_id()方法得到的值是 4。如果想解决该问题，可以通过如下语句：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
重点是这句id=LAST_INSERT_ID(id)。
英文原文：https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
参考资料：http://blog.csdn.net/ghsau/article/details/23557915
0x02、Mysql常用存储引擎的锁机制
Mysql常用存储引擎的锁机制
MyISAM和MEMORY采用表级锁（table-level locking）
BDB采用页面锁（page-leve locking）或表级锁，默认为页面锁
InnoDB支持行级锁（row-level locking）和表级锁，默认为行级锁
各种锁特点
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生冲突的概率最高，并发度最低
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
InnoDB存储引擎的锁
InnoDB存储引擎实现了如下两种锁
1、共享锁（S Lock），允许事务读一行数据
2、排他锁（X Lock)，允许事务更新或者删除一行数据
共享锁和排他锁的兼容如下图所示
一致性的非锁定读
一致性的非锁定行读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE、UPDATE操作，这是读取操作不会因此而会等待行上锁的释放，相反，InnoDB会去读取行的一个快照数据。
之所以称其为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指改行之前版本的数据，该实现是通过undo段来实现的。但是在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。
例如：
对于read committed的事务隔离级别，他总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照。
对于repeatable read(innoDB存储引擎的默认隔离级别),总是读取事务开始时的行数据。
非锁定读的机制大大提高了数据读取的并发性，在Innodb存储引擎默认设置下，这是默认的读取方式，但是在某些情况下，可以对读进行加锁，比如：
1、显式对读进行加锁，如使用 select — for update ;select — lock in share mode
2、在外键的插入和更新上，因为在外键的插入和更新上，对于数据的隔离性要求较高，在插入前需要扫描父表中的记录是否存在，所以，在外键的插入删除上，InnoDB会使用加S锁的方式来实现。
InnoDB锁的算法
1、Record Lock：单个行记录上的锁
2、Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
3、Next-key Lock:Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身
Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnodB存储引擎会使用隐式的主键来进行锁定，在Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法。
锁带来的问题
1、丢失更新
如何避免丢失更新：让事务变成串行操作，而不是并发的操作，即对每个事务开始—对读取记录加排他锁。
2、脏读
脏读即一个事务可以读到另一个事务中未提交的数据，这违反了数据库的隔离性。
脏读发生的条件是需要事务的隔离级别为Read uncommitted。
3、不可重复读
不可重复读与脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的是已经提交的数据。
一般来说，不可重复读是可以接受的，在InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。
值得注意的是，默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。
死锁的相关问题
1、死锁发生的条件
互斥条件：一个资源每次只能被一个进程使用；请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺；循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
2、死锁检测（根据网上的经验）
Innodb检测死锁有两种情况，一种是满足循环等待条件，还有另一种策略：锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度时，innodb也会判断为死锁（这是提高性能方面的考虑，避免事务一次占用太多的资源）。
因循环等待条件而产生的死锁只有可能是四种形式：两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。
3、死锁避免（根据网上的经验）
1.如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定
2. 一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。
3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。
4. sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。
5. 对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。
4、死锁解决
1)先执行show processlist找到死锁线程号.然后Kill pid
2)Show innodb status检查引擎状态 ,可以看到哪些语句产生死锁
3)查看information_schema架构下的innodb_locks、innodb_trx、innodb_lock_waits等表
转载请注明：jinglingshu的博客 » MySql实现高性能高并发的计数器与INSERT…ON DUPLICATE KEY UPDATE