mysql memcached 初探

--mysql 5.7 已经把memcache 与 innodb 结合在了一起
The InnoDB memcached plugin provides an integrated memcached daemon that can automatically store and retrieve data from InnoDB tables, turning the MySQL server into a fast “key-value store

--此时通过memcached数据已可以持久化
Data modified through memcached operations such as ADD, SET, INCR are stored to disk

--安装依赖包
 yum install libevent-dev 

--当编译数据库时要指定 DWITH_INNODB_MEMCACHED 选项
When you build MySQL server, you must build with -DWITH_INNODB_MEMCACHED=ON.
 libmemcached.so: the memcached daemon plugin to MySQL.
 innodb_engine.so: an InnoDB API plugin to memcached

 --创建依赖表
 mysql> source MYSQL_HOME/share/innodb_memcached_config.sql

 --其创建的表
mysql> use innodb_memcache;
mysql> show tables;
+---------------------------+
| Tables_in_innodb_memcache |
+---------------------------+
| cache_policies            |
| config_options            |
| containers                |
+---------------------------+  

cache_policies定义了缓存策略，包含如下选择：
innodb_only：只使用InnoDB作为数据存储。
cache-only：只使用传统的Memcached引擎作为后端存储。
caching：二者皆使用，如果在Memcached里找不到，就查询InnoDB。

config_options定义了分隔符号：
separator：Memcached只识别单值，使用此分隔符（|）来连接多个字段的值。
table_map_delimiter：通过此分隔符（.）来确认表和键，如：@@table.key。

 --按装插件
 install plugin daemon_memcached soname "libmemcached.so";

 --在my.cnf可以指定的 memcached 参数
 daemon_memcached_option
 daemon_memcached_engine_lib_name
 daemon_memcached_engine_lib_path
 daemon_memcached_r_batch_size
 daemon_memcached_w_batch_size

--daemon_memcached_r_batch_size和daemon_memcached_w_batch_size，这两个选项对性能影响较大，简单点说就是控制事务提交的频率，MySQL的缺省值均为1，也就是说每次都提交
When a memcached operation causes an insert, update, or delete in the underlying InnoDB table, that change might be committed to the underlying table instantly
(if daemon_memcached_w_batch_size=1) or some time later (if that configuration option value is greater than 1). In either case, the change can not be rolled back

--控制事务隔离级别
innodb_api_trx_level 

--默认 innodb_memcache 使用的表级别的锁
By default, innodb_api_disable_rowlock is set to OFF which means that memcached requests row locks for get and set operations. When innodb_api_disable_rowlock is set to ON, memcached requests a table lock instead of row locks.

--查看 innodb_memcache 参数
mysql> SHOW VARIABLES LIKE '%memcached%';
+----------------------------------+------------------+
| Variable_name                    | Value            |
+----------------------------------+------------------+
| daemon_memcached_enable_binlog   | OFF              |
| daemon_memcached_engine_lib_name | innodb_engine.so |
| daemon_memcached_engine_lib_path |                  |
| daemon_memcached_option          | -p11222          |
| daemon_memcached_r_batch_size    | 1                |
| daemon_memcached_w_batch_size    | 1                |
+----------------------------------+------------------+

--下面进行一些测试模拟
USE `test`

CREATE TABLE `users` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(15) NOT NULL,
    `password` VARCHAR(32) NOT NULL,
    `email` VARCHAR(50) NOT NULL,
    `flags` INT(10) UNSIGNED DEFAULT '0',
    `cas_column` BIGINT(20) UNSIGNED DEFAULT '0',
    `expire_time_column` INT(10) UNSIGNED DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB;

--然后添加几行测试数据：
INSERT INTO `users` (`username`, `password`, `email`)
VALUES
('foo', 'ffffffffffffffffffffffffffffffff', 'foo@domain.com'),
('bar', 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', 'bar@domain.com');

--接着在containers里配置这个表：
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 (
    'default', 'test', 'users', 'username', 'password|email',
    'flags', 'cas_column', 'expire_time_column', 'username'
);

--这里我们定义了多个字段（password和email）作为value_columns，并且使用竖线作为分隔符，实际上使用空格，分号，逗号之类分隔符也可以
--如果你的字段内容里包含了竖线，那么就会和缺省值发生冲突，此时你可以更新separator的定义，比如改成三个竖线等等，需要提醒的是，修改后别忘了重启Memcached插件
--因为们在配置的时候把表命名为default，所以在请求的时候不用传递表名。如果不存在default，那么会把名字按照字母顺序正序排列，排在第一位的就是缺省
--通过终端访问
[root@rudy ~]# telnet rudy 11222
get bar
VALUE bar 0 47
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb|bar@domain.com
END
get foo
VALUE foo 0 47
ffffffffffffffffffffffffffffffff|foo@domain.com
END

--通过在sql中修改数据，memcached端可以感知到数据变化
mysql>  update users set password='123534rudy' where id=1;
Query OK, 1 row affected (0.02 sec)

get foo
VALUE foo 0 25
123534rudy|foo@domain.com

 --当mysql关闭，memcached 也自动关闭了
 shut down the MySQL server, which also shuts off the integrated memcached server. Further attempts to access the memcached data now fail with a connection error

 Memcached服务器使用基于Slab的内存管理方式，有利于减少内存碎片和频繁分配销毁内存所带来的开销。
 各个Slab按需动态分配一个page的内存（和4Kpage的概念不同，这里默认page为1M），page内部按照不同slab class的尺寸再划分为内存chunk供服务器存储KV键值对使用
（slab机制相当于内存池机制, 实现从操作系统分配一大块内存, 然后 memcached 自己管理这块内存, 负责分配与回收。）

 --memcached一些限制
 1. Memcached keys cannot contain spaces or newlines, because those characters are used as separators in the ASCII protocol
 2. In any query that performs arithmetic using the column values, use the CAST() function to convert from string to integer or other numeric type
 3. You cannot use a partitioned table for data queried or stored through the memcached interface.
 4. If the InnoDB column you use as a key can be longer than 250 bytes, hash it to a value that is less than 250 bytes
 5. To use multiple MySQL column values with memcached key/value pairs, in the innodb_memcache.containers entry associated with the MySQL table,
 specify in the value_columns field several column names separated by comma, semicolon, space, or pipe characters; for example, col1,col2,col3 or col1|col2|col3.
 6. In any query that performs arithmetic using the column values, use the CAST() function to convert from string to integer or other numeric type
 7. If the InnoDB column you use as a key can be longer than 250 bytes, hash it to a value that is less than 250 bytes

 --因为 innodb_memcache 会持久化数据，所以其使用原生的memcached性能有所下降
 Because using InnoDB in combination with memcached involves writing all data to disk, whether immediately or sometime later

 --如果要让 innodb_memcache 支持主从复制，需要配置innodb_api_enable_binlog参数
 To use the InnoDB memcached plugin with the MySQL binary log, enable the innodb_api_enable_binlog configuration option on the master server.

 --The memcached flush_all command is mapped to the TRUNCATE TABLE command