我有一张桌子，里面有大约 850 万行。该表是 tokudb，它具有下面描述的索引。尝试运行如下更新语句时，我遇到了糟糕的性能：
update retail.lw_item_discovery
set price = 'X',
prev_price = 'Y',
last_updated = '2016-04-13',
last_price_change = '2016-04-13'
where market = 'XX'
and sku = '123456'
执行此更新需要 40 秒以上。像这样的其他更新也经常发生，但是这台机器的 I/O 子系统丝毫没有受到压力（raided SSD），而且还有足够的 RAM 可用。
EXPLAIN产量：
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
| 1 | UPDATE | lw_item_discovery | NULL | index | cl_unique_idx,cl_mkt_sku_upd_avail_idx,market_sku_item_idx | PRIMARY | 4 | NULL | 100 | 100.00 | Using where; Using temporary |
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
1 row in set (0.00 sec)
基于此 - 它选择PRIMARY索引而不是其他索引之一，例如cl_unique_idx在前两个位置的 where 语句中有两列。所以我很困惑为什么规划者选择了PRIMARY而不是导致性能如此糟糕。以下是索引列表：
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lw_item_discovery | 0 | PRIMARY | 1 | itd_id | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 1 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 2 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 3 | upc | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 4 | model_num | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 5 | item_id | A | 82 | NULL | NULL | YES | BTREE | | |
| lw_item_discovery | 1 | update_idx | 1 | last_updated | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | update_idx | 2 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | update_idx | 3 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | description_idc | 1 | web_description | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | category_idx | 1 | web_category | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | category_idx | 2 | upc | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | category_idx | 3 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | upc_idx | 1 | upc | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | item_id_idx | 1 | item_id | A | 82 | NULL | NULL | YES | BTREE | | |
| lw_item_discovery | 1 | item_id_idx | 2 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | item_id_idx | 3 | available | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | cl_mkt_sku_upd_avail_idx | 1 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | cl_mkt_sku_upd_avail_idx | 2 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | cl_mkt_sku_upd_avail_idx | 3 | last_updated | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | cl_mkt_sku_upd_avail_idx | 4 | available | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | market_sku_item_idx | 1 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | market_sku_item_idx | 2 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | market_sku_item_idx | 3 | item_id | A | 82 | NULL | NULL | YES | BTREE | | |
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
24 rows in set (0.00 sec)
我不得不将tokudb_lock_timeout时间从 4 秒增加到 40 秒，以免出现大量的锁等待争用。我在这里错过了什么吗？
表定义
`lw_item_discovery` (
`item_id` bigint(20) unsigned DEFAULT '0',
`chain` varchar(12) NOT NULL DEFAULT 'lowes',
`market` varchar(4) NOT NULL DEFAULT '',
`available` varchar(1) NOT NULL DEFAULT 'y',
`last_updated` date NOT NULL DEFAULT '0000-00-00',
`itd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`web_description` varchar(255) NOT NULL DEFAULT '',
`model_num` varchar(100) NOT NULL DEFAULT '' COMMENT 'its only 1char cause its not currently used. Its here for consistency',
`price` decimal(6,2) NOT NULL DEFAULT '0.00',
`item_link_url` text NOT NULL,
`item_img_url` text NOT NULL,
`store_shopped` smallint(5) unsigned NOT NULL DEFAULT '0',
`sku` varchar(32) NOT NULL DEFAULT '0',
`upc` varchar(12) NOT NULL DEFAULT '',
`web_category` varchar(255) NOT NULL DEFAULT '',
`mfr` varchar(100) NOT NULL DEFAULT '',
`class` tinyint(3) unsigned NOT NULL DEFAULT '0',
`subclass` tinyint(3) unsigned NOT NULL DEFAULT '0',
`first_found` date NOT NULL DEFAULT '0000-00-00' COMMENT 'first time it was seen in market',
`last_price_change` date NOT NULL DEFAULT '0000-00-00' COMMENT 'the date of the last price change observed',
`discontinued` varchar(1) NOT NULL DEFAULT 'n',
`discontinued_date` date NOT NULL DEFAULT '0000-00-00',
`prev_price` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
`rating` decimal(4,2) NOT NULL DEFAULT '-1.00',
`review_count` int(11) NOT NULL DEFAULT '-1',
PRIMARY KEY (`itd_id`),
UNIQUE KEY `cl_unique_idx` (`sku`,`market`,`upc`,`model_num`,`item_id`),
KEY `update_idx` (`last_updated`,`market`,`sku`),
KEY `description_idc` (`web_description`),
KEY `category_idx` (`web_category`,`upc`,`sku`),
KEY `upc_idx` (`upc`),
KEY `item_id_idx` (`item_id`,`market`,`available`) USING BTREE,
KEY `cl_mkt_sku_upd_avail_idx` (`sku`,`market`,`last_updated`,`available`),
CLUSTERING KEY `market_sku_item_idx` (`market`,`sku`,`item_id`)
) ENGINE=TokuDB AUTO_INCREMENT=8858224 DEFAULT CHARSET=latin1
每次更新的行数最多应为 1-3。更新的速度大约为每秒 1 次到每秒 3-4 次，通常高达每秒几十次。
这是在 Percona Server 5.7 上。