InnoDB memcached Plugin要了解一下

￡神魔★判官ぃ 2022-01-22 11:53 241阅读 0赞

InnoDB memcached Plugin在2012年[NoSQL Memcached API for MySQL][]中提出 ，可谓达到mysql的最高水准，据说MySQL 5.7版本中单机已经支持到每秒百万级，很多时候我们在研究新技术，更多的时候还是借鉴它的设计理念，并从中得到启发。

**测试基准**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70][]

**InnoDB memcached Architecture**

innodb memcached插件将memcached实现为一个mysql插件守护进程，该守护进程直接访问innodb存储引擎，绕过mysql sql层。它的好处在于提高单个数据库服务器的效率，而不是利用未使用的内存或将查找分布在大量服务器上。

为了更好的吞吐量，innodb memcached通过控制较低提交新数据和更改数据的频率。当memcached操作插入、更新或删除基础innodb表中的数据时，可能会立即将更改提交到innodb表（如果守护进程memcached的批处理大小=1）或稍后某个时间（如果守护进程memcached的批处理大小值大于1）。无论哪种情况，都不能回滚更改。如果增加守护进程memcached的批处理大小的值以避免繁忙时间的高I/O开销，那么当工作负载减少时提交可能变得不频繁。作为安全措施，后台线程会定期自动提交通过memcached API所做的更改。同步间隔由innodb\_api\_bk\_commit\_interval 配置项控制，默认设置为5秒。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70 1][]

**InnoDB Memcached与传统Memcached的区别**

可以参阅[相关文章][Link 1]

**谁在用？**

Facebook、阿里tddl

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70 2][]

**使用场景**

类 KeyValue 查询（例如，SELECT id, name FROM users WHERE id=1002），这类 SQL 已经非常简单，但使用比率非常高。

**memcached Commands and Associated DML or DDL Operations**

<table> 
 <thead> 
  <tr> 
   <th style="width:235px;">memcached Command</th> 
   <th style="width:613px;">DML or DDL Operations</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:235px;"><code>get</code></td> 
   <td style="width:613px;">a read/fetch command</td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>set</code></td> 
   <td style="width:613px;">a search followed by an&nbsp;<code>INSERT</code>&nbsp;or&nbsp;<code>UPDATE</code>&nbsp;(depending on whether or not a key exists)</td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>add</code></td> 
   <td style="width:613px;">a search followed by an&nbsp;<code>INSERT</code>&nbsp;or&nbsp;<code>UPDATE</code></td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>replace</code></td> 
   <td style="width:613px;">a search followed by an&nbsp;<code>UPDATE</code></td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>append</code></td> 
   <td style="width:613px;">a search followed by an&nbsp;<code>UPDATE</code>&nbsp;(appends data to the result before&nbsp;<code>UPDATE</code>)</td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>prepend</code></td> 
   <td style="width:613px;">a search followed by an&nbsp;<code>UPDATE</code>&nbsp;(prepends data to the result before&nbsp;<code>UPDATE</code>)</td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>incr</code></td> 
   <td style="width:613px;">a search followed by an&nbsp;<code>UPDATE</code></td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>decr</code></td> 
   <td style="width:613px;">a search followed by an&nbsp;<code>UPDATE</code></td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>delete</code></td> 
   <td style="width:613px;">a search followed by a&nbsp;<code>DELETE</code></td> 
  </tr> 
  <tr> 
   <td style="width:235px;"><code>flush_all</code></td> 
   <td style="width:613px;"><code>TRUNCATE TABLE</code>&nbsp;(DDL)</td> 
  </tr> 
 </tbody> 
</table>

**相关参数**

使用 innodb\_api\_trx\_level配置项控制事务隔离级别。  
使用 innodb\_api\_disable\_rowlock 选项禁用行锁。  
使用 innodb\_api\_enable\_mdl来禁用这些表上的DDL操作

**使用**

最适合简单GET请求的查询类型是那些在WHERE子句中具有单个子句或一组AND条件的查询：![82818753][]

--SQL:
    SELECT col FROM tbl WHERE key = 'key_value';
    
    --memcached:
    get key_value
    
    --SQL:
    SELECT col FROM tbl WHERE col1 = val1 and col2 = val2 and col3 = val3;
    
    --memcached:
    -- Since you must always know these 3 values to look up the key,
    -- combine them into a unique string and use that as the key
    -- for all ADD, SET, and GET operations.
    key_value = val1 + ":" + val2 + ":" + val3
    get key_value
    
    --SQL:
    SELECT 'key exists!' FROM tbl
      WHERE EXISTS (SELECT col1 FROM tbl WHERE KEY = 'key_value') LIMIT 1;
    
    --memcached:
    -- Test for existence of key by asking for its value and checking if the call succeeds,
    -- ignoring the value itself. For existence checking, you typically only store a very
    -- short value such as "1".
    get key_value

**引用资料**

*  [InnoDB memcached Plugin][]
 *  [Facebook团队测试基准][Facebook]
 *  [阿里TDDL的KV 查询优化][TDDL_KV]

[NoSQL Memcached API for MySQL]: https://blogs.oracle.com/mysql/nosql-memcached-api-for-mysql%3A-latest-updates
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70]: /images/20220122/27e7d3ca35ea4b218e073777e2448f77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70 1]: /images/20220122/0e0972ed74d641f78ff42f1f7a667248.png
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-intro.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70 2]: /images/20220122/149eac5337cb4b2aaa5de00d1f2efc29.png
[82818753]: /images/20220122/f030c12b97324484a01c5d2e3ec285a4.png
[InnoDB memcached Plugin]: https://dev.mysql.com/doc/refman/5.7/en/innodb-memcached.html
[Facebook]: https://www.oschina.net/translate/mysql-5-7-innodb-memcached-plugins?print
[TDDL_KV]: http://jm.taobao.org/2017/12/27/post%E2%80%9C20172704%E2%80%9D/