体验MySQL5.6的Mencached插件

MySQL应用检索时为了提高查询效率,一般都会在MySQL前边加Memcache做查询缓存,这样用户需要先查询自己需要的Key是否已经缓存,如果没有,则会去MySQL中进行查询.在MySQL5.6时InnoDB集成Memched插件.

结构图如下:

上面提到的add.incr等操作完成依赖MySQL Server不需要人为干预.这样带来的好处主要是:
1、存储引擎自动做Memcached到数据文件的持久化.
2、数据自动加载Memcached
3、Memcached消耗CPU不多,内存占用可以控制.
4、因为InnoDB的一致性,不需要担心Memcached的事务性操作
其它请看官方文档

下面来安装Memcached For MySQL插件

mysql> select @@plugin_dir;
+---------------------------------+
| @@plugin_dir                    |
+---------------------------------+
| /usr/local/mysql5.6/lib/plugin/ |
+---------------------------------+
1 row in set (0.00 sec)

需要innodb_engine.so、libmemcached.so这两个文件
[mysql@localhost ~]$ ls /usr/local/mysql5.6/lib/plugin/
adt_null.so  auth_socket.so       daemon_example.ini  innodb_engine.so      libmemcached.so  qa_auth_client.so     qa_auth_server.so   semisync_slave.so
auth.so      auth_test_plugin.so  debug               libdaemon_example.so  mypluglib.so     qa_auth_interface.so  semisync_master.so  validate_password.so
[mysql@localhost ~]$ 

创建Memcached需要的表结构
mysql> source /usr/local/mysql5.6/share/innodb_memcached_config.sql
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.03 sec)
........
........
........
........

激活Memcached插件
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so"; 
Query OK, 0 rows affected (0.03 sec)

如果卸载执行下面的命令
mysql> UNINSTALL PLUGIN daemon_memcached; 

查看Memcached相关的参数配置
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          |                  |
| daemon_memcached_r_batch_size    | 1                |
| daemon_memcached_w_batch_size    | 1                |
+----------------------------------+------------------+

daemon_memcached_enable_binlog此参数不支持动态修改需要重启MySQL
注意:参数daemon_memcached_r_batch_size、daemon_memcached_w_batch_size,这两个参数对性能影响较大,表示控制事物读写的频率,默认是1.

刚才执行的innodb_memcached_config.sql脚本会产生innodb_memcache库和三个表cache_policies, config_options, containers.
mysql> use innodb_memcache
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> show tables;
+---------------------------+
| Tables_in_innodb_memcache |
+---------------------------+
| cache_policies            |
| config_options            |
| containers                |
+---------------------------+
3 rows in set (0.00 sec)

mysql> desc cache_policies;
+---------------+-------------------------------------------------------+------+-----+---------+-------+
| Field         | Type                                                  | Null | Key | Default | Extra |
+---------------+-------------------------------------------------------+------+-----+---------+-------+
| policy_name   | varchar(40)                                           | NO   | PRI | NULL    |       |
| get_policy    | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| set_policy    | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| delete_policy | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| flush_policy  | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
+---------------+-------------------------------------------------------+------+-----+---------+-------+
5 rows in set (0.09 sec)

mysql> desc config_options;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | NO   | PRI | NULL    |       |
| value | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc containers;
+------------------------+--------------+------+-----+---------+-------+
| Field                  | Type         | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+-------+
| name                   | varchar(50)  | NO   | PRI | NULL    |       |
| db_schema              | varchar(250) | NO   |     | NULL    |       |
| db_table               | varchar(250) | NO   |     | NULL    |       |
| key_columns            | varchar(250) | NO   |     | NULL    |       |
| value_columns          | varchar(250) | YES  |     | NULL    |       |
| flags                  | varchar(250) | NO   |     | 0       |       |
| cas_column             | varchar(250) | YES  |     | NULL    |       |
| expire_time_column     | varchar(250) | YES  |     | NULL    |       |
| unique_idx_name_on_key | varchar(250) | NO   |     | NULL    |       |
+------------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

以上这三张表分别存放cache 策略、配置和容器信息

如何用Memcached访问表呢..那么就拿demo_test举例,首先MySQL已经帮助我们添加demo_test的信息了.
mysql> desc demo_test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c1    | varchar(32)         | NO   | PRI |         |       |
| c2    | varchar(1024)       | YES  |     | NULL    |       |
| c3    | int(11)             | YES  |     | NULL    |       |
| c4    | bigint(20) unsigned | YES  |     | NULL    |       |
| c5    | int(11)             | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select * from demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from 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                |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
1 row in set (0.00 sec)

现在我们用Memcached来查看一下
[mysql@localhost ~]$ echo "get @@aaa.AA" | nc localhost 11211 
VALUE @@aaa.AA 8 12
HELLO, HELLO
END

[mysql@localhost ~]$ echo "set @@aaa"; echo "get AA" | nc localhost 11211 
set @@aaa
VALUE AA 8 12
HELLO, HELLO
END

此时set、get可以正常使用了.

现在我们来实际测试一下

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo_test      |
+----------------+
1 row in set (0.00 sec)
mysql> create table emp (id int unsigned not null auto_increment,user varchar(20) not null,city varchar(20) not null,email varchar(20),work varchar(20),flags int unsigned default '0',cas_column bigint unsigned default '0', expire_time_column int unsigned default '0', primary key(id),unique key (user));
Query OK, 0 rows affected (0.28 sec)

mysql> insert into emp (user,city,email,work) values ('efg','beijing','[e-mail]','farmer');
Query OK, 1 rows affected (0.00 sec)

mysql> insert into emp (user,city,email,work) values ('edc','shenzhen','[e-mail]','teacher');
Query OK, 1 rows affected (0.00 sec)

mysql> 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','emp','user','city|email|work','flags', 'cas_column', 'expire_time_column', 'user');
Query OK, 1 row affected (0.11 sec)

mysql> select * from 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                |
| default | test      | emp       | user        | city|email|email | flags | cas_column | expire_time_column | user                   |
+---------+-----------+-----------+-------------+------------------+-------+------------+--------------------+------------------------+
2 rows in set (0.00 sec)

现在策略已经设置完毕,现在我们通过Memcached查看一下
[mysql@localhost ~]$ echo "get efg"| nc 127.0.0.1 11211
VALUE efg 0 26
beijing|[e-mail]|farmer
END
[mysql@localhost ~]$ echo "get edc"| nc 127.0.0.1 11211
VALUE edc 0 25
shenzhen|[e-mail]|teacher
END

现在来看看从Memcached写入数据的效果
[mysql@localhost ~]$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
get @@aaa.AA
VALUE @@aaa.AA 8 12
HELLO, HELLO
END
set test 0 0 4
ss
STORED
get test
VALUE test 0 4
ss

END

进入MySQL查看刚才的数据
mysql> select * from test.demo_test\G
*************************** 1. row ***************************
c1: AA
c2: HELLO, HELLO
c3: 8
c4: 0
c5: 0

为什么没有呢?刚才我们明明test的啊.嘿嘿,这是由于我们没有设置transcaction级别,之后就才能正常显示了.

sql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

在看看
mysql> select * from test.demo_test\G
*************************** 1. row ***************************
c1: AA
c2: HELLO, HELLO
c3: 8
c4: 0
c5: 0
*************************** 2. row ***************************
c1: test
c2: ss
c3: 0
c4: 30
c5: 0
2 rows in set (0.00 sec)

OK了..刚才我们的刚才显示正常.在配置过程中有一张表我们需要注意.这就是cache_policies表.首先我们看看它的建表语句.
mysql> show create table cache_policies\G
*************************** 1. row ***************************
       Table: cache_policies
Create Table: CREATE TABLE `cache_policies` (
  `policy_name` varchar(40) NOT NULL,
  `get_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `set_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `delete_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `flush_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  PRIMARY KEY (`policy_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

这些策略都有四个选项:innodb_only, cache_only, caching 和disabled.分表代表的意思如下: 
innodb_only:数据直接存储到innodb engine.
cache_only:跟传统的memcache server一样，数据存在memory中.
caching:memory中缓存,如果没有,再向innodb engine寻找数据
disabled就不解释了

结尾:在实验的过程中碰到不少壁,比如添加自定义表的时候，containers表的字段,除key_columns和value_columns外，其它的字段，如：flags，cas_column，expire_time_column也要设定,如果原表没有,那么就要新建了.这个比较恶心.个人感觉这还是一个不怎么成熟的产品同时还要颇多的限制,但是随着MySQL版本更新会迅速改进.不管怎样MySQL5.6引入Memcached支持NoSQL可以看到对NoSQL的强烈需求.今天先到此吧.^_^