mysql 大量更新请求 updating 状态

[sujin190]

每秒大概 100 个更新， 200 万左右数据，每次都是有索引更新，每次更新一条记录，大量处于 updating ， mysql 更新操作有这么慢么？还好多都更新等待超时了，或者该怎么找出哪的问题么？

[sujin190]

@likuku buffer 调大了

[sujin190]

@likuku
innodb_data_home_dir = /data/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 5G
innodb_additional_mem_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120
innodb_max_dirty_pages_pct = 90

8 核 12g 机械硬盘

[huigeer]

主键是自增的么

[sujin190]

@huigeer 是的，但不是根据主键更新的

[yangqi]

目测是表的索引坏了，看过 show index from table 了么？ Cardinality 那一列

[billgreen1]

可能是 sql 语句有问题，我昨天做查询的时候，一个字段是股票代码， 600000 这样的，数据库里是文本格式，我查询的时候却用了数字格式，可以查的出来，但是要耗时 160+秒，后来发现这个问题，查询降到了 1 秒以下。

[sujin190]

@yangqi 什么样算坏的呢？
1964190 BTREE 索引

[sujin190]

@billgreen1 貌似不是这个问题

[yangqi]

@sujin190 看 cardinality 那列，如果有 null 的话就需要优化了。另外把 update 语句换成 select 然后 explain 一下看看。

[jwdstefanie]

注意吧 where 后面的字段设置索引 速度嗷嗷的

[sujin190]

@yangqi 没有
mysql> explain select id from order where order_id='201603010140117550200039';
+----+-------------+--------------+-------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | order_201603 | const | order_id | order_id | 72 | const | 1 | Using index |
+----+-------------+--------------+-------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
不过需要更新的那个字段是 varchar 类型，默认为 NULL 更新为 30 个字符左右的值，影响么？

[yangqi]

SET profiling=1;

然后执行 update 语句

最后 show profile;

[lecher]

如果 varchar 的长度原先为空， update 一个 30 字符的值进去， mysql 要做的是开辟新的空间把整行数据迁移过去，这个过程会锁表，这个并发高起来，性能都消耗在计算空间申请新空间上了，其实还不如 insert 。

要优化，可以考虑将 varchar 字段改成定长，这样在行记录创建的时候，就会预留固定长度的位置， update 的时候就不需要重新开辟新的空间，执行 update 的消耗就与 select 基本一致，付出的代价就是这个表的文件大小会变大，因为预留的空字符串长度，等于空间换时间。

[yangqi]

另外看下你的 key_buffer_size, 还有索引大小， order_id 是 varchar 索引应该不小，如果不能放在 ram 里面的话就只能从 disk 上读写，每次 update 操作都要更新索引，肯定会慢。

[sujin190]

@lecher 改成 char 型默认空字符串行么？

[sujin190]

@yangqi key_buffer_size 512M 索引大小为 400M ，这个 key_buffer_size 是每个 db 单独分配的么？需要更新有两个 db 的两张表，数据差不多大
mysql> SHOW STATUS LIKE 'key_read%';
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Key_read_requests | 1242558 |
| Key_reads | 639 |
+-------------------+---------+
2 rows in set (0.00 sec)

[realpg]

@sujin190
1. 本地服务器 or 云服务器 or RDS 服务？
2. 看你透露出来的表结构，有唯一自增主键，同时 orderid 有唯一索引，根据 orderid 去更新非索引列，这种模型对吧？那么， update 没问题，有问题的是其他查询导致的锁表，然后 update 就进队列了，发生三五十次，因为 update 非常频繁，堆积就很严重了，检查 slowlog 吧。

[sujin190]

@realpg 云服务器，通过看 show processlist;似乎不是查询锁表，而且平时是正常的，只是并发稍高的时候会这样

[lecher]

把线上数据导一份到本地测速一下便知。

[noahzh]

@sujin190 这是索引区分度的问题,key_buffer_size 只是对 myisam 有效,对 innodb 无效,这明显是并发锁的问题,索引对应列过多,加锁了.很正常.重新设计一个索引.