InnoDB行级锁定性能-多少行? [英] InnoDB row level locking performance - how many rows?

问题描述

我不得不阅读很多有关MyISAM和InnoDB的内容，因为我必须决定使用哪种类型. 总是提到InnoDB的行级别锁定"支持.当然，这仅在一定数量的行上才有意义.

I just read a lot of stuff about MyISAM and InnoDB as I have to decide which type to use. There was always mentioned 'row level locking'-support for InnoDB. Of course this only makes sense at a certain amount of rows.

(大约)多少个?

显然我用错了我的问题的措辞.我知道表锁定和行锁定的含义，但是我想知道何时才有意义. 如果我每天只插入100行，那么表锁定当然就足够了，但是对于一种情况，比如说我认为每秒钟100行，InnoDB是更好的选择.

Apparently I mis-worded my question. I know what table locking and row locking mean but I wondered when this does matter. If I have just 100 rows inserted per day, of course table locking would be way enough but for a case of, let's say 100 rows per SECOND I think, InnoDB would be the better choice.

我的问题:行锁定还是每秒10行或每秒5行有意义吗?这种选择何时会显着影响性能?

My question: Does row also locking make sense for 10 rows per second or 5 rows per second? When does this choice significantly affect performance?

推荐答案

目前还不清楚您要问什么.锁定可确保只有一个用户在任何给定时间尝试修改给定行.行级锁定意味着只有他们正在修改的一行被锁定.通常的选择是在修改期间锁定整个表，或者锁定表的某些子集.行级锁定只是将行的该子集减少到仍可确保完整性的最小数目.

It's not entirely clear what you're asking. Locking ensures that only one user attempts to modify a given row at any given time. Row-level locking means only the one row they're modifying is locked. The usual alternatives are to either lock the entire table for the duration of the modification, or else to lock some subset of the table. Row-level locking simply reduces that subset of the rows to the smallest number that still ensures integrity.

这个想法是允许一个用户修改一件东西而又不阻止其他用户修改其他东西.但是，值得注意的是，在某些情况下，这可以说是错误的肯定.一些数据库支持行级锁定，但是使行级锁定比锁定表的大部分要昂贵得多-足够昂贵以至于适得其反.

The idea is to allow one user to modify one thing without preventing other users from modifying other things. It's worth noting, however, that in some cases this can be something of a false positive, so to speak. A few databases support row-level locking, but make a row-level lock considerably more expensive that locking a larger part of the table -- enough more expensive that it can be counterproductive.

您对原始帖子的编辑很有帮助，但效果不是很大.首先，行的大小和所涉及的硬件级别会产生巨大的影响(将8字节的行插入十几个带区卷的15K SAS硬盘驱动器中，比将一个1兆字节的行插入单个消费类硬盘驱动器快一点. ).

Your edit to the original post helps, but not really a lot. First of all, the sizes of rows and levels of hardware involved have a huge effect (inserting an 8-byte row onto a dozen striped 15K SAS hard drives is just a tad faster than inserting a one megabyte row onto a single consumer class hard drive).

第二，主要取决于同时使用的用户数，因此插入方式会产生很大的不同.可能根本不会注意到在凌晨3点插入的1000行.全天平均插入1000行意味着更多(但可能只有一点).当另外100个用户需要数据时立即批量插入1000行可能会被解雇(特别是如果这100个用户之一是公司的所有者).

Second, it's largely about the number of simultaneous users, so the pattern of insertion makes a big difference. 1000 rows inserted at 3 AM probably won't be noticed at all. 1000 rows inserted evenly throughout the day means a bit more (but probably only a bit). 1000 rows inserted as a batch right when 100 other users need data immediately might get somebody fired (especially if one of those 100 is the owner of the company).

这篇关于InnoDB行级锁定性能-多少行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！