innodb的锁到底占用多少内存

举个简单的例子：

CREATE TABLE `sample` (

  `i` int(10) unsigned NOT NULL auto_increment,

  `j` varchar(255) default NULL,

  PRIMARY KEY  (`i`),

  KEY `j` (`j`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show table status like "sample" \G;

*************************** 1. row ***************************

           Name: sample

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 1638757

 Avg_row_length: 61

    Data_length: 100253696

Max_data_length: 0

   Index_length: 128974848

      Data_free: 0

 Auto_increment: 1638401

    Create_time: 2006-07-12 07:31:51

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options:

        Comment: InnoDB free: 1591296 kB

1 row in set (0.27 sec)

这个表有1638400条记录，如果通过加只读锁看看：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql>  select count(i) from sample lock in share mode;

+----------+

| count(i) |

+----------+

|  1638400 |

+----------+

1 row in set (7.02 sec)

show engine innodb status看下：

History list length 5

Total number of lock structs in row lock hash table 6092

LIST OF TRANSACTIONS FOR EACH SESSION:

—TRANSACTION 0 42306982, ACTIVE 89 sec, process no 10099, OS thread id 1878960

6093 lock struct(s), heap size 503104

有503104字节用来锁1638400条记录，每行小于3bits,那么互斥锁占用多少内存呢？

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql>  select count(i) from sample for update;

+----------+

| count(i) |

+----------+

|  1638400 |

+----------+

1 row in set (8.60 sec)

show engine innodb status的结果是：

History list length 5

Total number of lock structs in row lock hash table 6092

LIST OF TRANSACTIONS FOR EACH SESSION:

—TRANSACTION 0 42306989, ACTIVE 195 sec, process no 10099, OS thread id 1878960

6093 lock struct(s), heap size 503104

MySQL thread id 7429, query id 24542 localhost root

和前一个占用的内存一样，模糊匹配呢？

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from sample where j like "5%" lock in share mode;

+----------+

| count(*) |

+----------+

|   102216 |

+----------+

1 row in set (4.44 sec)

show engine innodb status显示：

LIST OF TRANSACTIONS FOR EACH SESSION:

—TRANSACTION 0 42306999, ACTIVE 133 sec, process no 10099, OS thread id 1878960

441 lock struct(s), heap size 44352

100w行大概消耗44KB，每行小于4bits。

整个上面来看占用的内存还是很少的。

文章来源：

https://www.percona.com/blog/2006/07/13/how-much-memory-innodb-locks-really-take/

巴特西

innodb的锁到底占用多少内存

最新文章

热门文章