mysql锁的问题

Question

版本5.5
表的引擎为innodb
在执行一个很简单的update语句时经常会锁住，如下表。表里的数据很少，不到2000，如果说特别也就是有一个blob字段，别的没什么特别的了。并发量也不大。
我是不明白这么简单的一个语句为什么会卡在这？求教 求教~

+-------+-----------------+-----------------+------+---------+------+-----------------------------+----------------------------------------------------------------------------+
| Id    | User            | Host            | db   | Command | Time | State                       | Info                                                                       |
+-------+-----------------+-----------------+------+---------+------+-----------------------------+----------------------------------------------------------------------------+
| 20959 | event_scheduler | localhost       | NULL | Daemon  |   16 | Waiting for next activation | NULL                                                                       |
| 72840 | root            | localhost       | **   | Query   |    0 | NULL                        | show full processlist                                                      |
| 72901 | root            | localhost:57842 | **   | Query   |   39 | Updating                    | update table_name set views = ifnull(views,0)+1 where 1=1 and id = '2349' |
| 72902 | root            | localhost:57843 | **   | Sleep   |    5 |                             | NULL                                                                       |
| 72903 | root            | localhost:57844 | **   | Sleep   |   48 |                             | NULL                                                                       |
| 72904 | root            | localhost:57845 | **   | Sleep   |   48 |                             | NULL                                                                       |
| 72906 | root            | localhost:57846 | **   | Query   |   22 | Updating                    | update table_name set views = ifnull(views,0)+1 where 1=1 and id = '2349' |
| 72907 | root            | localhost:57847 | **   | Query   |   37 | Updating                    | update table_name set views = ifnull(views,0)+1 where 1=1 and id = '2262' |
| 72911 | root            | localhost:57849 | **   | Sleep   |   60 |                             | NULL                                                                       |
| 72912 | root            | localhost:57850 | **   | Sleep   |   47 |                             | NULL                                                                       |
| 72919 | root            | localhost:57852 | **   | Sleep   |    0 |                             | NULL                                                                       |
| 72920 | root            | localhost:57853 | **   | Query   |   26 | Updating                    | update table_name set views = ifnull(views,0)+1 where 1=1 and id = '2349' |
| 72922 | root            | localhost:57854 | **   | Sleep   |   72 |                             | NULL                                                                       |
| 72923 | root            | localhost:57855 | **   | Sleep   |   72 |                             | NULL                                                                       |
| 72924 | root            | localhost:57856 | **   | Sleep   |   72 |                             | NULL                                                                       |
+-------+-----------------+-----------------+------+---------+------+-----------------------------+----------------------------------------------------------------------------+

Answer 1

据说 IFNULL 如果在 WHERE 里使用的话，效率会极低，具体原因我不太清楚，还请其他人补充。

另外你这个需求不需要使用 IFNULL，只需要将 views 的 DEFAULT 设置为 0 就可以了。

Answer 2

可能是由于IFNULL的缘故吧

可以把你的SQL分成几个SQL来执行
先select然后程序判断，然后做update

这样是否还慢