MySQL插入查询随机需要很长时间

Question

I am using MySQL to manage session data for my PHP application. When testing the app, it is usually very quick and responsive. However, seemingly randomly the response will stall before finally completing after a few seconds. I have narrowed the problem down to the session write query which looks something like this:

INSERT INTO Session VALUES('lvg0p9peb1vd55tue9nvh460a7', '1275704013', '') ON DUPLICATE KEY UPDATE sessAccess='1275704013',sessData='';

The slow query log has this information:

Query_time: 0.524446  Lock_time: 0.000046 Rows_sent: 0  Rows_examined: 0

This happens about 1 out of every 10 times. The query usually only takes ~0.0044 sec.

The table is InnoDB with about 60 rows. sessId is the primary key with a BTREE index.

Since this is accessed on every page view, it is clearly not an acceptable execution time. Why is this happening?

Update: Table schema is: sessId:varchar(32), sessAccess:int(10), sessData:text

Answer 1

Note that inserting into the middle of a BTree index does require a release of the page quite often, and a rebuild of a portion of the index. For a clustered index (your primary key is probably your clustered index), the actual row data must be moved too when the page is rebuilt.

If the row data is large, it will take some time.

For your case, it might be best to use an autoincrement primary key, and just use a unique index on sessId, so you're not inserting records into the middle of a clustered index.