mysql偶尔查询慢_mysql – 偶尔查询速度慢的原因？

前两个问题(

Question1,

Question2)中的UPDATE查询通过PRIMARY KEY按行级锁定命中表’people’.这就是我在2011年6月6日上午10:03的问题1中所说的

所有事务都遍历PRIMARY键.由于PRIMARY是InnoDB中的聚簇索引,因此PRIMARY键和行本身在一起.因此,遍历一行和PRIMARY KEY是同一个.因此,PRIMARY KEY上的任何索引锁定也是行级锁定.

尚未考虑的其他因素可能导致索引缓慢：在InnoDB中使用NON-UNIQUE索引. InnoDB中使用非唯一索引的每个索引查找也将每行的rowID附加到非唯一键. The rowID basically eminates from the Clustered Index.如果表没有主键,更新非唯一索引必须始终与聚簇索引交互.

另一件需要考虑的事情是在索引中管理BTREE节点的过程.有时,它需要页面拆分节点.非唯一索引的BTREE节点中的所有条目都包含非唯一字段PLUS聚簇索引中的rowID.为了在不干扰数据完整性的情况下适当地减轻这种BTREE页面的拆分,与rowID相关联的行必须在内部经历行级锁定.

如果’people’表有很多非唯一索引,那么准备在表空间中放置大量索引页面,并且不时会有小小的行锁定在你身上.

还有一个不那么明显的因素：关键人口

有时,当索引填充时,构成索引的键值可能会随着时间的推移而变得不平衡,导致MySQL查询优化器从键控查找切换到索引扫描,最后切换到全表扫描.除非您使用新索引重新设计表以补偿键的不平衡,否则您无法控制.请提供“人员”表的表格结构,“人员”表的计数以及“人员”表的显示索引输出.

即使查询仅使用PRIMARY KEY,非唯一索引中键的不平衡仍然需要BTREE平衡和页面拆分.由于你不打算发生间歇性的行级锁定,这样的BTREE管理会产生显着的减速.

更新2011-06-14 22:19

来自问题1的查询

UPDATE people SET company_id = 1610, name = '', password = '',

temp_password = NULL, reset_password_hash = NULL, email = '@yahoo.com',

phone = NULL, mobile = '', iphone_device_id = 'android:-',

iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42',

location_lat = , location_long = -, gps_strength = 3296,

picture_blob_id = 1190,

authority = 1, active = 1, date_created = '2011-04-13 20:21:20',

last_login = '2011-06-06 05:35:09', panic_mode = 0,

battery_level = NULL, battery_state = NULL WHERE people_id = 3125

UPDATE people SET company_id = 1610, name = '', password = '',

temp_password = NULL, reset_password_hash = NULL, email = '@yahoo.com',

phone = NULL, mobile = '', iphone_device_id = 'android:---',

iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07',

location_lat = , location_long = -, gps_strength = 3296,

picture_blob_id = 1190,

authority = 1, active = 1, date_created = '2011-04-13 20:21:20',

last_login = '2011-06-06 05:35:09', panic_mode = 0,

battery_level = NULL, battery_state = NULL WHERE people_id = 3125

在事件中描绘序列

>按PRIMARY KEY查找行

>锁定行和聚集索引

>为要更新的所有列创建MVCC数据

>索引四列(email,company_id,iphone_device_id,picture_blob_id)

>每个索引都需要BTREE管理

>在同一个事务空间内,步骤1-5尝试在同一行重复,更新相同的列(两个查询中的电子邮件相同,两个查询中的company_id相同,两个查询中的picture_blob_id相同,iphone_device_id不同)

来自问题2的查询

UPDATE people SET iphone_device_id=NULL

WHERE iphone_device_id='iphone:' AND people_id<>666;

UPDATE people SET company_id = 444, name = 'Dad', password = '',

temp_password = NULL, reset_password_hash = NULL, email = '@gmail.com',

phone = NULL, mobile = NULL, iphone_device_id = 'iphone:',

iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47',

location_lat = , location_long = , gps_strength = 66,

picture_blob_id = 1661,

authority = 1, active = 1, date_created = '2011-03-20 19:18:34',

last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55,

battery_state = 'unplugged' WHERE people_id = 666;

这两个查询更加令人困惑,因为第一个查询正在更新除people_id 666之外的所有内容.仅使用第一个查询就会痛苦地锁定数百行.第二个查询是更新运行5个事件序列的people_id 666.第一个查询在涉及除了people_id 666之外的每一行上运行相同的5个事件序列,但iphone_device_id的索引在具有两个不同查询的intecept课程上.有人必须以先到先得的方式锁定BTREE页面.

面对碰撞过程中的这两对查询,可能会在一个索引中锁定相同的BTREE页面,这对InnoDB或任何符合ACID标准的RDBMS来说都是一种痛苦的经历.因此,索引减速是这些查询对的命运,除非您可以保证查询以AUTOCOMMIT = 1运行或允许脏读(尽管像这样的冲突使得READ-COMMITTED和READ-UNCOMMITED成为MVCC的噩梦).

更新2011-06-15 10:29

@RedBlueThing：在问题2的查询中,第一个查询是范围查询,因此正在获得许多行锁.还要注意两个查询都试图锁定相同的空间id 0页面没有4611 n位152被锁定在PRIMARY KEY中,即聚集索引.

为了确保您的应用程序至少基于您期望的一系列事件运行,您可以尝试两种不同的选项：

选项1)将此表转换为MyISAM(至少在开发服务器上).每个UPDATE,INSERT和DELETE都将按照先到先得的原则强加一个完整的表锁.

选项2)尝试使用SERIALIZABLE隔离级别.这将锁定SHARED模式下的所有预期行.

您期望的事件序列将使用这两个备选选项中断或成功.如果这两个选项均失败,则您需要查看应用程序并确定查询执行顺序的优先顺序.确定优先级后,您可以简单地撤消这些选项(对于选项1,返回到InnoDB,对于选项2,返回到默认隔离级别[停止使用SERIALIZABLE]).