[求助]MySql 此update语句应如何优化？

Question

有这样一张统计表：CREATE TABLE `sitems` (
  `date` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '日期',
  `key` varchar(255) NOT NULL DEFAULT '' COMMENT 'key',
  `value` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'value',
  `typeid` int(11) NOT NULL DEFAULT '-1',
  UNIQUE KEY `date` (`date`,`key`,`typeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
慢查询日志：Count: 556  Time=3.51s (1950s)  Lock=3.66s (2034s)  Rows=0.0 (0), root[root]@2hosts
update `tb`.`sitems` SET `value` = `value` + N where `typeid` = N and `date` = 'S' and `key` = 'S'
更新一条语句要3秒钟，有好的优化方法吗？

Answer 1

贴一下 show index 和 explain select * from tb where `typeid` = N and `date` = 'S' and `key` = 'S'

Answer 2

+----+-------------+-------------+-------+---------------+------+---------+-------------------+------+-------+
| id | select_type | table       | type  | possible_keys | key  | key_len | ref               | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+-------------------+------+-------+
|  1 | SIMPLE      | sitems   | const | date          | date | 775     | const,const,const |    1 |       |
+----+-------------+-------------+-------+---------------+------+---------+-------------------+------+-------+
1 row in set (0.00 sec)
show index+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sitems   |          0 | date     |            1 | date        | A         |         631 |     NULL | NULL   |      | BTREE      |         |               |
| sitems   |          0 | date     |            2 | key         | A         |       21146 |     NULL | NULL   |      | BTREE      |         |               |
| sitems   |          0 | date     |            3 | typeid      | A         |      169168 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (41.11 sec)

Answer 3

Count: 556  Time=3.51s (1950s)  Lock=3.66s (2034s) 从EXPLAIN上来看没有任何问题。 不过从日志上看好像是锁的时间等了很久，查一下并发的锁。

Answer 4

求教下，这个要怎么查，有什么工具吗？
这个表主要是update 操作，也只update `value`这个字段。 比如：update `tb`.`sitems` SET `value` = `value` + 1 where `typeid` = 10001 and `date` = '20150505' and `key` = 'daliy_online'

Answer 5

请查检是否有间隙锁问题 gap lock

Answer 6

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sitems   |          0 | date     |            1 | date        | A         |         631 |     NULL | NULL   |      | BTREE      |         |               |
| sitems   |          0 | date     |            2 | key         | A         |       21146 |     NULL | NULL   |      | BTREE      |         |               |
| sitems   |          0 | date     |            3 | typeid      | A         |      169168 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
创建联合索引，尽可能要把过滤条件高的字段放在最前面，这样比较过滤更加快 。上面Cardinality这字段就是反应选择性高低，越大选择性就越好。Cardinality：就是字段值不同的行数。重新创建索引 顺序（typeid,key,date）