利用InnoDB Memcached插件，大幅提升MySQL查询性能

MySQL自带有强大的Memcached插件，能够将InnoDB表中的数据直接映射到内存中，实现高性能的键值对存储。这意味着，我们可以像操作传统Memcached一样，通过简单的key来快速获取数据，而无需经过复杂的SQL解析过程。


关键优势：

  • 性能大幅提升: 直接访问内存中的数据，显著加快查询速度。
  • 持久化支持: 数据不仅缓存在内存中，还持久化到InnoDB表，确保数据安全。
  • 易于使用: 采用标准Memcached协议，与现有应用无缝集成。


InnoDB Memcached插件安装：

mysql> SOURCE /usr/local/mysql/share/innodb_memcached_config.sql
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
mysql> --需要重启mysqld服务进程才生效
  • 1.
  • 2.
  • 3.


操作演示:

  1. 配置Memcached容器: 将users表映射为一个Memcached容器。
INSERT INTO innodb_memcache.containers (
    name, db_schema, db_table, key_columns, value_columns,
    flags, cas_column, expire_time_column, unique_idx_name_on_key
) VALUES (
    '', 'test', 'users', 'username', 'password|email',
    'flags', 'cas_column', 'expire_time_column', 'username'
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
mysql> select * from innodb_memcache.containers;
+-------+-----------+-----------+-------------+----------------+-------+------------+--------------------+------------------------+
| name  | db_schema | db_table  | key_columns | value_columns  | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+-------+-----------+-----------+-------------+----------------+-------+------------+--------------------+------------------------+
| aaa   | test      | demo_test | c1          | c2             | c3    | c4         | c5                 | PRIMARY                |
| users | test      | users     | username    | password|email | flags | cas_column | expire_time_column | username               |
+-------+-----------+-----------+-------------+----------------+-------+------------+--------------------+------------------------+
2 rows in set (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.

注：username为key，password|email为value，并且使用竖线作为分隔符。


 2.将users表增加3个字段，增加对 Memcached 协议的支持

ALTER TABLE users  
    ADD COLUMN `flags` INT(10) UNSIGNED DEFAULT '0',
    ADD COLUMN `cas_column` BIGINT(20) UNSIGNED DEFAULT '0',
    ADD COLUMN `expire_time_column` INT(10) UNSIGNED DEFAULT '0';
  • 1.
  • 2.
  • 3.
  • 4.

让我们逐个解析这些新增的列：

  1. flags 列:
  • 类型: INT(10) UNSIGNED
  • 默认值: 0
  • 用途: 在 Memcached 中，flags 是与存储的数据项相关联的用户定义的 32 位整数。它通常用于存储有关数据的元信息，如数据的类型、压缩状态等。
  1. cas_column 列:
  • 类型: BIGINT(20) UNSIGNED
  • 默认值: 0
  • 用途: CAS 代表 "Check and Set"。这是 Memcached 用于实现乐观锁的机制。每次数据更新时，CAS 值都会增加。客户端可以在更新数据时提供一个 CAS 值，只有当这个值匹配服务器上的当前 CAS 值时，更新才会成功。
  1. expire_time_column 列:
  • 类型: INT(10) UNSIGNED
  • 默认值: 0
  • 用途: 这个列用于存储数据项的过期时间。在 Memcached 中，你可以为每个数据项设置一个过期时间，超过这个时间后数据就会自动失效。


  1. 直接访问Memcached端口:
shell> echo "stats" | nc localhost 11211
  • 1.


  1. PHP访问数据: 使用PHP客户端，通过key直接获取users表中的数据，就像操作传统Memcached一样。
# cat php_memcached.php 
<?php

// echo "get @@users.zhangsan" | nc localhost 11211

$memcache = new Memcache;             //创建一个memcache对象
$memcache->connect('localhost', 11211) or die ("Could not connect"); //连接Memcached服务器
//$memcache->set('key', 'test');        //设置一个变量到内存中，名称是key 值是test
$get_value = $memcache->get('@@users.lisi');   //从内存中取出key的值
echo $get_value;
?>

# php php_memcached.php 
123456|lisi@xxx.com
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.

如此简单！ InnoDB Memcached插件为我们提供了一种全新的数据访问方式，能够有效解决高并发、大数据量场景下的性能瓶颈。