MySQL update (too long)

Question

After some expected growth on our service, suddenly some updates take a very long time, they were pretty fast until the table reached about 2 million records, now they take about 40-60 seconds each.

update table1 set field1=field1+1 where id=2229230;
Query OK, 0 rows affected (42.31 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The following are the types of fields:

`id` bigint(20) NOT NULL auto_increment,
`field1` int(11) default '0',

The result from profiling, for context switches, which is the only one that, apparently, has a large number of results:

mysql> show profile context switches
    -> ;
+----------------------+-----------+-------------------+---------------------+
| Status               | Duration  | Context_voluntary | Context_involuntary |
+----------------------+-----------+-------------------+---------------------+
| (initialization)     | 0.000007  |                 0 |                   0 |
| checking permissions | 0.000009  |                 0 |                   0 |
| Opening tables       | 0.000045  |                 0 |                   0 |
| System lock          | 0.000009  |                 0 |                   0 |
| Table lock           | 0.000008  |                 0 |                   0 |
| init                 | 0.000056  |                 0 |                   0 |
| Updating             | 46.063662 |             75487 |               14658 |
| end                  | 2.803943  |              5851 |                 857 |
| query end            | 0.000054  |                 0 |                   0 |
| freeing items        | 0.000011  |                 0 |                   0 |
| closing tables       | 0.000008  |                 0 |                   0 |
| logging slow query   | 0.000265  |                 2 |                   1 |
+----------------------+-----------+-------------------+---------------------+
12 rows in set (0.00 sec)

The table is about 2.5 million records, the identifier is the primary key and has one unique index in another field (not included in the update).

This is the innodb table.

any pointers to what could be causing?

Any specific variables that can help track the problem?

Is there an explanation for updates?

EDIT: , :

`modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

:

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | table1        | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.02 sec)

Answer 1

, , id ( , 2229230?). sqls :

show create table table1;
explain select * from table1 where id = 2229230;

: ,

select count(*) from table1 where id = 2229230;

Answer 2

:

OPTIMIZE TABLE table1;

, , , . , ( ),

REPAIR TABLE table1;

Answer 3

, , " ", , , , :

• x
• x

, , , , , < 1 .

+1 , .

Answer 4

, , "innodb" "myisam". 100 0,1 .

, , InnoDB , .

InnoDB 1000- .

Answer 5

, - . , , , . ​​ . .

Answer 6

, , : , ?

In my case, the transaction turned the update statement from incredibly fast execution time to extremely slow. The more lines affected the performance loss. My statements work with user-defined variables, but don’t know if this part is a “problem”.