[文章作者:张宴 本文版本:v1.1 最后修改:2010.09.03 转载请注明原文链接:http://blog.zyan.cc/tcsql/]

  曾经有人提出,一般数据库缓存分为四种。第一种:单个对象的缓存(一个对象就是数据库一条记录),对于单个对象的临时缓存或永久缓存,用HashMap就可以了,Key-Value方式的Memcached、Memcachedb、Tokyo Tyrant都可以,或者直接对查询数据库的网页采用Squid做缓存,没什么太难的;第二种:列表缓存,就像论坛里帖子的列表;第三种:记录条数的缓存,比如一个论坛板块里有多少个帖子,这样才方便实现分页。第四种:复杂一点的group,sum,count查询,比如一个论坛里按点击数排名的最HOT的帖子列表。第一种比较好实现,后面三种比较困难,虽然可以通过各种方法来解决,但截至目前,似乎还没有使用即简单、并发处理能力又强、实时性又高的解决办法。



  TCSQL为列表页的实时缓存而生,是金山逍遥网技术支持部平台组以Tokyo Cabinet DBM为底层存储与索引,结合类似Memcached的Key-Value内存对象缓存,借鉴SQL语句的SELECT、INSERT、UPDATE、DELETE思想与功能开发的实时列表缓存数据库,能够较好地解决上述前三种类别,特别是第二种、第三种类别的高并发读写问题。

  TCSQL采用HTTP GET/POST协议+JSON数据交换格式在客户端、服务器端之间进行数据交互,支持HTTP协议的任何客户端或语言(例如JavaScript、PHP、JSP、Perl、Python等),都能够连接TCSQL服务器进行操作。这就意味着,一些查询量非常大的应用,甚至可以直接使用运行在用户浏览器端的JavaScript代码访问TCSQL数据库,当然,为了安全起见,你可以在中间用Nginx配以rewrite规则,对TCSQL做个反向代理,限制一下查询权限。

  利用开源的MySQL UDF自定义函数扩展lib_mysqludf_urlencode、lib_mysqludf_urlencode,以及我们平台组周洋同学编写的lib_mysqludf_http_post扩展,配以MySQL触发器,我们可以在MySQL的某张表发生插入、更新、删除操作时,自动将数据同步到TCSQL数据库,使得TCSQL可以当MySQL从库一样使用。

  TCSQL实时列表缓存数据库单机能够支撑1万以上的并发连接,QPS(每秒查询率)能够达到5000~15000次。

点击在新窗口中浏览此图片

  以下是TCSQL在10000并发连接情况下的查询速度(服务器为浪潮NF190服务器,两颗双核Xeon(TM) CPU 2.80GHz、4GB内存、1万转SCSI硬盘。):

  1、第一种类型A:主键查询并取出倒序第1条记录(“=”运算):12155次请求/秒

  查询内容:http://192.168.8.34:3888/?command=select&type=*&where=pkey:NUMEQ:随机数值&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=1

  测试结果:
引用
Benchmarking: 10000 clients, running 60 sec.

Speed=729324 pages/min, 8031913 bytes/sec.
Requests: 60777 susceed, 0 failed.


  2、第一种类型B:其他索引键查询并取出倒序第1条记录(“=”运算):11897次请求/秒

  查询内容:http://192.168.8.34:3888/?command=select&type=*&where=uid:NUMEQ:随机数值&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=1

  测试结果:
引用
Benchmarking: 10000 clients, running 60 sec.

Speed=713856 pages/min, 7865884 bytes/sec.
Requests: 59488 susceed, 0 failed.


  3、第二种类型:根据复合条件查询并取出倒序前10条记录:8778次请求/秒(相当于SELECT * FROM table WHERE dateline >= 随机时间戳 AND idtype = '变换的文本' ORDER BY pkey DESC LIMIT 0,10)

  查询内容:http://192.168.8.34:3888/?command=select&type=*&where=dateline:NUMGE:随机时间戳|idtype:STREQ:变换的文本&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=10

  测试结果:
引用
Benchmarking: 10000 clients, running 60 sec.

Speed=526680 pages/min, 8971878 bytes/sec.
Requests: 43890 susceed, 0 failed.


  4、第三种类型:统计符合查询条件的记录数量:9160次请求/秒(相当于SELECT count(*) FROM table WHERE dateline >= 随机时间戳 AND idtype = '变换的文本')

  查询内容:http://192.168.8.34:3888/?command=select&type=count&where=dateline:NUMGE:随机时间戳|idtype:STREQ:变换的文本

  测试结果:
引用
Benchmarking: 10000 clients, running 5 sec.

Speed=549648 pages/min, 714542 bytes/sec.
Requests: 45804 susceed, 0 failed.





  一、TCSQL数据库编译安装
  1、编译安装TCSQL需要的扩展库
wget http://www.monkey.org/~provos/libevent-1.4.12-stable.tar.gz
tar zxvf libevent-1.4.12-stable.tar.gz
cd libevent-1.4.12-stable/
./configure --prefix=/usr
make && make install
cd ../

wget http://oss.metaparadigm.com/json-c/json-c-0.9.tar.gz
tar zxvf json-c-0.9.tar.gz
cd json-c-0.9/
./configure --prefix=/usr/local/json-c-0.9
make && make install
cd ../

wget http://www.1978th.net/tokyocabinet/tokyocabinet-1.4.33.tar.gz
tar zxvf tokyocabinet-1.4.33.tar.gz
cd tokyocabinet-1.4.33/
./configure --prefix=/usr/local/tokyocabinet-1.4.33
make && make install
cd ../

echo "/usr/local/json-c-0.9/lib/" > /etc/ld.so.conf.d/json-c-0.9.conf
echo "/usr/local/tokyocabinet-1.4.33/lib/" > /etc/ld.so.conf.d/tokyocabinet-1.4.33.conf

/sbin/ldconfig


  2、编译TCSQL数据库
  注:二进制程序及源码目前只对金山公司内部开放。
gcc -o tcsql tcsql.c -levent -ljson -I/usr/local/json-c-0.9/include/json/ -L/usr/local/json-c-0.9/lib/ -ltokyocabinet -lz -lbz2 -lrt -lpthread -lm -lc -I/usr/local/tokyocabinet-1.4.33/include/ -L/usr/local/tokyocabinet-1.4.33/lib/




  二、TCSQL数据库的启动

  点击在新窗口中浏览此图片

  1、以自定义方式,作为守护进程启动:
ulimit -SHn 65535
./tcsql -l 192.168.8.34 -p 3888 -x /data0/tcsql/data -t 3 -d


  2、以默认值,作为守护进程启动:
ulimit -SHn 65535
./tcsql -d


  注:请确保文件描述符数量够用(系统默认值为1024),以便TCSQL承担上万连接数的并发访问。



  三、TCSQL访问协议
  TCSQL采用HTTP GET/POST协议进行查询、插入、更新、删除、设置索引等操作。TCSQL为单表数据库,一个端口的数据库只有一张表。TCSQL表只有两种数据类型,字符型和数值型,其中主键字段的名称固定不可修改,为pkey,数值型索引。对应日期、时间等类型,请使用数值型的UNIX时间戳存放。

  TCSQL无需事先建表,插入第一条记录后,第一条记录拥有的字段即为该表的字段格式。各字段统一按照文本信息存放数据,无需指定数据类型。如果为字段建立数值型索引,Where条件的数值型运算符可以用到索引;如果为字段建立文本型索引,Where条件的文本型运算符可以用到索引。

  1、命令类别:
  查询:http://127.0.0.1:3888/?command=select
  插入:http://127.0.0.1:3888/?command=insert
  更新:http://127.0.0.1:3888/?command=update
  删除:http://127.0.0.1:3888/?command=delete
  设置索引:http://127.0.0.1:3888/?command=setindex



  2、SELECT查询命令:
  ①、完整参数的查询命令示例:
  A、以Linux下的curl命令访问:
curl "http://127.0.0.1:3888/?command=select&type=*&where=pkey:NUMGE:0|title:STRINC:keyword&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=10"


  B、用PHP访问:
<?php
$result = json_decode(file_get_contents('http://127.0.0.1:3888/?command=select&type=*&where=pkey:NUMGE:0|title:STRINC:keyword&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=10'), true));
print_r($result);
?>


  ②、相当于MySQL的SQL语句:
SELECT * FROM table WHERE pkey >= 0 AND title LIKE '%keyword%' ORDER BY pkey DESC LIMIT 0,10


  ③、必填参数说明:
引用
command=select
(命令类别为:select)


引用
&type=*
(查询类型为:* 或 count,相当于MySQL的select * 和 select count(*)语句)


  ④、可选参数说明:
引用
&where=pkey:NUMGE:0|title:STRINC:keyword
(where查询条件,格式为“字段名1:运算符:查询值|字段名2:运算符:查询值|...”,多个条件之间以“|”分割)

Where条件运算符:
数值型运算符:

NUMGT:表示比右边的数值要大。
NUMGE:表大于或等于右边的数值。
NUMLT:表示比右边的数值要小。
NUMLE:表示小于或等于右边的数值。
NUMBT:表示其大小处于右边文字段中被逗号分开的两个数值的中间。
NUMOREQ:表示同右边文字段中被逗号分开的两个数值中的其中一个是相同的。

文本型运算符:
STREQ:表示与右边的文字行列完全相同。
STRINC:表示含有右边文字行列的内容。
STRBW:表从右边的文字行列开始。
STREW:表示到右边的文字行列结束。
STRAND:表示包含右边的文字行列中右逗号分开部分的字段的全部。
STROR:表示包含右边文字段中逗号分开部分的其中一部分。
STROREQ:表示与右边文字段中逗号分开部分的其中某部分完全相同。


引用
&order_by=pkey&order_sort=NUMDESC
(排序字段与排序方式,order_by=pkey表示按照字段pkey排序,order_sort=NUMDESC表示排序方式为数值型倒序排序。)

可选的排序类型如下:
STRASC:表示按照文本型字段内的文本内容在字典中排列顺序的升序。
STRDESC:表示按照文本型字段内的文本内容在字典中排列顺序的降序。
STRASC:表示按照数值大小的升序。
STRDESC:表示按照数值大小的降序。


引用
&limit_skip=0&limit_max=10
(显示的偏移量和记录数,相当于SQL语句中的limit 0,10)


  ⑤、查询返回值(JSON输出):
  无数据时:
引用
[]


  有数据时:
引用
[{ "pkey": "3847", "appid": "3", "icon": "friend", "uid": "188", "username": "独孤拔剑", "dateline": "1253858842", "friend": "0", "hash_template": "cb26e9a5a87778b51eb9b36c38f2616b", "hash_data": "ca4a76f8444363e8215e19f8468be415", "title_template": "{actor} 和 {touser} 成为了好友", "title_data": "a:1:{s:6:\"touser\";s:44:\"<a href=\"space.php?uid=395\">江湖粉丝<\/a>\";}", "body_template": "", "body_data": "a:0:{}", "body_general": "", "image_1": "", "image_1_link": "", "image_2": "", "image_2_link": "", "image_3": "", "image_3_link": "", "image_4": "", "image_4_link": "", "target_ids": "", "id": "0", "idtype": "", "hot": "0" },{ "pkey": "3846", "appid": "3", "icon": "profile", "uid": "591", "username": "七~夜~魔~君", "dateline": "1253858783", "friend": "0", "hash_template": "3a7101a64ea7927f0b3f5179b7a457b3", "hash_data": "ec7d775d9211880bca2ba1d401e3bcb9", "title_template": "{actor} 开通了自己的个人主页", "title_data": "a:0:{}", "body_template": "", "body_data": "a:0:{}", "body_general": "", "image_1": "", "image_1_link": "", "image_2": "", "image_2_link": "", "image_3": "", "image_3_link": "", "image_4": "", "image_4_link": "", "target_ids": "", "id": "0", "idtype": "", "hot": "0" },{ "pkey": "3845", "appid": "3", "icon": "comment", "uid": "247", "username": "freespy", "dateline": "1253858601", "friend": "0", "hash_template": "2e512d5ffebbfb6b596df942871e413a", "hash_data": "eae2c0aebf53cebbe3e39a9da1072ddf", "title_template": "{actor} 评论了 {touser} 的图片", "title_data": "a:1:{s:6:\"touser\";s:44:\"<a href=\"space.php?uid=404\">小营居士<\/a>\";}", "body_template": "{pic_title}", "body_data": "a:1:{s:9:\"pic_title\";s:51:\"20090901_024374553bbea77f21199Vx54Z93Nzjd.jpg.thumb\";}", "body_general": "", "image_1": "http:\/\/pic.xoyo.com\/hu\/attachment\/200909\/24\/404_1253763392qQB4.jpg.thumb.jpg", "image_1_link": "space.php?uid=404&do=album&picid=364", "image_2": "", "image_2_link": "", "image_3": "", "image_3_link": "", "image_4": "", "image_4_link": "", "target_ids": "", "id": "0", "idtype": "", "hot": "0" },{ "pkey": "3844", "appid": "3", "icon": "album", "uid": "404", "username": "小营居士", "dateline": "1253763392", "friend": "0", "hash_template": "405cd24a70f8c85bf9f2affd3aadf790", "hash_data": "6c2921a232ef155f1397c93f68ee6e40", "title_template": "{actor} 上传了新图片", "title_data": "N;", "body_template": "{title}", "body_data": "a:1:{s:5:\"title\";s:51:\"20090901_024374553bbea77f21199Vx54Z93Nzjd.jpg.thumb\";}", "body_general": "", "image_1": "http:\/\/pic.xoyo.com\/hu\/attachment\/200909\/24\/404_1253763392qQB4.jpg.thumb.jpg", "image_1_link": "space.php?uid=404&do=album&picid=364", "image_2": "", "image_2_link": "", "image_3": "", "image_3_link": "", "image_4": "", "image_4_link": "", "target_ids": "", "id": "364", "idtype": "picid", "hot": "1" }]


  以上的JSON信息,通过PHP的print_r(json_decode($data, true))函数解码后得到的数组为:
引用
Array
(
    [0] => Array
        (
            [pkey] => 3847
            [appid] => 3
            [icon] => friend
            [uid] => 188
            [username] => 独孤拔剑
            [dateline] => 1253858842
            [friend] => 0
            [hash_template] => cb26e9a5a87778b51eb9b36c38f2616b
            [hash_data] => ca4a76f8444363e8215e19f8468be415
            [title_template] => {actor} 和 {touser} 成为了好友
            [title_data] => a:1:{s:6:"touser";s:44:"江湖粉丝";}
            [body_template] =>
            [body_data] => a:0:{}
            [body_general] =>
            [image_1] =>
            [image_1_link] =>
            [image_2] =>
            [image_2_link] =>
            [image_3] =>
            [image_3_link] =>
            [image_4] =>
            [image_4_link] =>
            [target_ids] =>
            [id] => 0
            [idtype] =>
            [hot] => 0
        )

    [1] => Array
        (
            [pkey] => 3846
            [appid] => 3
            [icon] => profile
            [uid] => 591
            [username] => 七~夜~魔~君
            [dateline] => 1253858783
            [friend] => 0
            [hash_template] => 3a7101a64ea7927f0b3f5179b7a457b3
            [hash_data] => ec7d775d9211880bca2ba1d401e3bcb9
            [title_template] => {actor} 开通了自己的个人主页
            [title_data] => a:0:{}
            [body_template] =>
            [body_data] => a:0:{}
            [body_general] =>
            [image_1] =>
            [image_1_link] =>
            [image_2] =>
            [image_2_link] =>
            [image_3] =>
            [image_3_link] =>
            [image_4] =>
            [image_4_link] =>
            [target_ids] =>
            [id] => 0
            [idtype] =>
            [hot] => 0
        )

    [2] => Array
        (
            [pkey] => 3845
            [appid] => 3
            [icon] => comment
            [uid] => 247
            [username] => freespy
            [dateline] => 1253858601
            [friend] => 0
            [hash_template] => 2e512d5ffebbfb6b596df942871e413a
            [hash_data] => eae2c0aebf53cebbe3e39a9da1072ddf
            [title_template] => {actor} 评论了 {touser} 的图片
            [title_data] => a:1:{s:6:"touser";s:44:"小营居士";}
            [body_template] => {pic_title}
            [body_data] => a:1:{s:9:"pic_title";s:51:"20090901_024374553bbea77f21199Vx54Z93Nzjd.jpg.thumb";}
            [body_general] =>
            [image_1] => http://pic.xoyo.com/hu/attachment/200909/24/404_1253763392qQB4.jpg.thumb.jpg
            [image_1_link] => space.php?uid=404&do=album&picid=364
            [image_2] =>
            [image_2_link] =>
            [image_3] =>
            [image_3_link] =>
            [image_4] =>
            [image_4_link] =>
            [target_ids] =>
            [id] => 0
            [idtype] =>
            [hot] => 0
        )

    [3] => Array
        (
            [pkey] => 3844
            [appid] => 3
            [icon] => album
            [uid] => 404
            [username] => 小营居士
            [dateline] => 1253763392
            [friend] => 0
            [hash_template] => 405cd24a70f8c85bf9f2affd3aadf790
            [hash_data] => 6c2921a232ef155f1397c93f68ee6e40
            [title_template] => {actor} 上传了新图片
            [title_data] => N;
            [body_template] => {title}
            [body_data] => a:1:{s:5:"title";s:51:"20090901_024374553bbea77f21199Vx54Z93Nzjd.jpg.thumb";}
            [body_general] =>
            [image_1] => http://pic.xoyo.com/hu/attachment/200909/24/404_1253763392qQB4.jpg.thumb.jpg
            [image_1_link] => space.php?uid=404&do=album&picid=364
            [image_2] =>
            [image_2_link] =>
            [image_3] =>
            [image_3_link] =>
            [image_4] =>
            [image_4_link] =>
            [target_ids] =>
            [id] => 364
            [idtype] => picid
            [hot] => 1
        )
)




  3、INSERT插入命令
  ①、完整参数的插入命令示例(以Linux下的curl命令访问):
curl -d "urlencode编码后的JSON数据" "http://127.0.0.1:3888/?command=insert"


  一个PHP的一维数组经过json_encode编码后,形成以下JSON数据:
引用
{"pkey":1,"appid":3,"icon":"profile","uid":2,"username":"wangduo","dateline":1251768576,"friend":0,"hash_template":"3a7101a64ea7927f0b3f5179b7a457b3","hash_data":"ec7d775d9211880bca2ba1d401e3bcb9","title_template":"{actor} 开通了自己的个人主页","title_data":"a:0:{}","body_template":"","body_data":"a:0:{}","body_general":"","image_1":"","image_1_link":"","image_2":"","image_2_link":"","image_3":"","image_3_link":"","image_4":"","image_4_link":"","target_ids":"","id":0,"idtype":"","hot":0}


  将以上的JSON数据urlencode后,通过HTTP POST发送到TCSQL(在PHP中不urlencode也行):
引用
%7B%22pkey%22%3A1%2C%22appid%22%3A3%2C%22icon%22%3A%22profile%22%2C%22uid%22%3A2%2C%22username%22%3A%22wangduo%22%2C%22dateline%22%3A1251768576%2C%22friend%22%3A0%2C%22hash_template%22%3A%223a7101a64ea7927f0b3f5179b7a457b3%22%2C%22hash_data%22%3A%22ec7d775d9211880bca2ba1d401e3bcb9%22%2C%22title_template%22%3A%22%7Bactor%7D%20%E5%BC%80%E9%80%9A%E4%BA%86%E8%87%AA%E5%B7%B1%E7%9A%84%E4%B8%AA%E4%BA%BA%E4%B8%BB%E9%A1%B5%22%2C%22title_data%22%3A%22a%3A0%3A%7B%7D%22%2C%22body_template%22%3A%22%22%2C%22body_data%22%3A%22a%3A0%3A%7B%7D%22%2C%22body_general%22%3A%22%22%2C%22image_1%22%3A%22%22%2C%22image_1_link%22%3A%22%22%2C%22image_2%22%3A%22%22%2C%22image_2_link%22%3A%22%22%2C%22image_3%22%3A%22%22%2C%22image_3_link%22%3A%22%22%2C%22image_4%22%3A%22%22%2C%22image_4_link%22%3A%22%22%2C%22target_ids%22%3A%22%22%2C%22id%22%3A0%2C%22idtype%22%3A%22%22%2C%22hot%22%3A0%7D


  注:INSERT插入命令如果遇到数据库中已经重复的主键,将会用新数据替换旧记录的数据,这一点跟MySQL的REPLACE INTO操作类似。



  4、UPDATE更新命令
  ①、完整参数的更新命令示例(以Linux下的curl命令访问):
curl -d "urlencode编码后的JSON数据" "http://127.0.0.1:3888/?command=update&type=*&where=pkey:NUMGT:195"


  一个PHP的一维数组经过json_encode编码后,形成以下JSON数据:
引用
{"pkey":1,"appid":3,"icon":"profile","uid":2,"username":"wangduo","dateline":1251768576,"friend":0,"hash_template":"3a7101a64ea7927f0b3f5179b7a457b3","hash_data":"ec7d775d9211880bca2ba1d401e3bcb9","title_template":"{actor} 开通了自己的个人主页","title_data":"a:0:{}","body_template":"","body_data":"a:0:{}","body_general":"","image_1":"","image_1_link":"","image_2":"","image_2_link":"","image_3":"","image_3_link":"","image_4":"","image_4_link":"","target_ids":"","id":0,"idtype":"","hot":0}


  将以上的JSON数据urlencode后,通过HTTP POST发送到TCSQL:
引用
%7B%22pkey%22%3A1%2C%22appid%22%3A3%2C%22icon%22%3A%22profile%22%2C%22uid%22%3A2%2C%22username%22%3A%22wangduo%22%2C%22dateline%22%3A1251768576%2C%22friend%22%3A0%2C%22hash_template%22%3A%223a7101a64ea7927f0b3f5179b7a457b3%22%2C%22hash_data%22%3A%22ec7d775d9211880bca2ba1d401e3bcb9%22%2C%22title_template%22%3A%22%7Bactor%7D%20%E5%BC%80%E9%80%9A%E4%BA%86%E8%87%AA%E5%B7%B1%E7%9A%84%E4%B8%AA%E4%BA%BA%E4%B8%BB%E9%A1%B5%22%2C%22title_data%22%3A%22a%3A0%3A%7B%7D%22%2C%22body_template%22%3A%22%22%2C%22body_data%22%3A%22a%3A0%3A%7B%7D%22%2C%22body_general%22%3A%22%22%2C%22image_1%22%3A%22%22%2C%22image_1_link%22%3A%22%22%2C%22image_2%22%3A%22%22%2C%22image_2_link%22%3A%22%22%2C%22image_3%22%3A%22%22%2C%22image_3_link%22%3A%22%22%2C%22image_4%22%3A%22%22%2C%22image_4_link%22%3A%22%22%2C%22target_ids%22%3A%22%22%2C%22id%22%3A0%2C%22idtype%22%3A%22%22%2C%22hot%22%3A0%7D


  注:UPDATE更新命令是SELECT查询命令和INSERT插入命令的结合,URL查询部分使用SELECT查询命令的语法,HTTP POST使用INSERT插入命令的语法。



  5、DELETE删除命令
  ①、完整参数的删除命令示例(以Linux下的curl命令访问):
curl "http://127.0.0.1:3888/?command=delete&type=*&where=pkey:NUMGT:195"




  6、SetIndex索引设置命令
  ①、创建文本型字段索引示例(以Linux下的curl命令访问):
curl "http://127.0.0.1:3888/?command=setindex&type=TDBITLEXICAL&field=需要建文本型索引的字段名"


  ②、创建数值型字段索引示例(以Linux下的curl命令访问):
curl "http://127.0.0.1:3888/?command=setindex&type=TDBITDECIMAL&field=需要建数值型索引的字段名"


  ③、优化索引示例(以Linux下的curl命令访问):
curl "http://127.0.0.1:3888/?command=setindex&type=TDBITOPT&field=需要优化索引的字段名"


引用
“type=”可填的参数为:
TDBITLEXICAL:创建文本型索引
TDBITDECIMAL:创建数值型索引
TDBITTOKEN:创建标记倒排索引
TDBITQGRAM:创建q-gram倒排索引
TDBITOPT:优化索引
TDBITVOID:删除索引




  四、让TCSQL从MySQL同步数据
  1、编译安装三个MySQL UDF函数扩展(json、urlencode、http_post)
wget http://mirrors.xoyo.com/xoyo/tcsql/mysql_to_tcsql_udf.tar.gz
tar zxvf mysql_to_tcsql_udf.tar.gz
cd mysql_to_tcsql_udf/lib_mysqludf_json/
make
make install
cd ../lib_mysqludf_urlencode/
echo "/usr/local/webserver/mysql/lib/mysql/" > /etc/ld.so.conf.d/mysql.Conf
/sbin/ldconfig
./configure --prefix=/usr/local/webserver/mysql/lib/mysql/plugin/ --with-mysq=/usr/local/webserver/mysql --with-mysql_config=/usr/local/webserver/mysql/bin/mysql_config --with-mysql-include=/usr/local/webserver/mysql/include
make && make install
cp -f /usr/local/webserver/mysql/lib/mysql/plugin/lib/* /usr/local/webserver/mysql/lib/mysql/plugin/
make
make install

cd ../lib_mysqludf_http_post/
make
make install


  2、通过命令行登入MySQL,执行以下语句创建MySQL自定义函数。
drop function lib_mysqludf_json_info;
drop function json_array;
drop function json_members;
drop function json_object;
drop function json_values;
create function lib_mysqludf_json_info returns string soname 'lib_mysqludf_json.so';
create function json_array returns string soname 'lib_mysqludf_json.so';
create function json_members returns string soname 'lib_mysqludf_json.so';
create function json_object returns string soname 'lib_mysqludf_json.so';
create function json_values returns string soname 'lib_mysqludf_json.so';

drop function http_post;
create function http_post returns string soname 'lib_http_post.so';

drop function urlencode;
create function urlencode returns string soname 'libmysqludf_urlencode.so';


  3、利用触发器自动同步MySQL数据到TCSQL数据库
  示例:通过MySQL命令行连接到MySQL服务器,执行以下SQL,对sns_feed表创建三个MySQL触发器:sns_feed_insert、sns_feed_update、sns_feed_delete,当MySQL的sns_feed表发生增、删、改操作时,自动将修改的记录内容通过HTTP POST到TCSQL数据库(192.168.8.34:3888)。
DELIMITER |
DROP TRIGGER IF EXISTS sns_feed_insert;
CREATE TRIGGER sns_feed_insert
AFTER INSERT ON sns_feed
FOR EACH ROW BEGIN
    SET @tcsql_result_json = (SELECT json_object(feedid as pkey,appid,icon,uid,username,dateline,friend,hash_template,hash_data,title_template,title_data,body_template,body_data,body_general,image_1,image_1_link,image_2,image_2_link,image_3,image_3_link,image_4,image_4_link,target_ids,id,idtype,hot) FROM sns_feed WHERE feedid = NEW.feedid limit 1);
    SET @tcsql_result_eval = (SELECT http_post('192.168.8.34', '3888', 'command=insert', urlencode(@tcsql_result_json)));
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS sns_feed_update;
CREATE TRIGGER sns_feed_update
AFTER UPDATE ON sns_feed
FOR EACH ROW BEGIN
    SET @tcsql_result_json = (SELECT json_object(feedid as pkey,appid,icon,uid,username,dateline,friend,hash_template,hash_data,title_template,title_data,body_template,body_data,body_general,image_1,image_1_link,image_2,image_2_link,image_3,image_3_link,image_4,image_4_link,target_ids,id,idtype,hot) FROM sns_feed WHERE feedid = OLD.feedid limit 1);
    SET @tcsql_result_eval = (SELECT http_post('192.168.8.34', '3888', 'command=insert', urlencode(@tcsql_result_json)));
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS sns_feed_delete;
CREATE TRIGGER sns_feed_delete
AFTER DELETE ON sns_feed
FOR EACH ROW BEGIN
    SET @tcsql_result_eval = (SELECT http_post('192.168.8.34', '3888', concat('command=delete&where=pkey:NUMEQ:', OLD.feedid), ''));
END |
DELIMITER ;




  五、TCSQL的PHP客户端Class
下载文件 (已下载 1950 次)


  以下是一段SELECT查询TCSQL,并逐行显示内容的PHP示例程序:
  您也可以参考以下网址,使用JavaScript对TCSQL数据库进行操作:
  http://www.json.org/JSONRequest.html
  http://www.devpro.it/JSON/files/JSONRequest-js.html



  六、TCSQL在实际项目中的应用
  金山游戏官网逍遥网内测中的SNS非实名制垂直类互动游戏社区“逍遥江湖”(相关新闻:http://tech.qq.com/a/20090924/000316.htm),使用TCSQL存放读写非常频繁的、记录用户每一步操作的Feed信息(好友动态、我的动态、热门推荐、全站动态)。

  2009-09-29 补充介绍:
引用
  关于Feed信息,数据写入MySQL主库,同步到N台MySQL从库。在其中的一台无其他查询的备用MySQL从库上,按照第四部分的内容建立了触发器,将数据实时同步到TCSQL。查询部分,直接修改UCH的代码,将原来查询MySQL的SQL语句,改成以下的TCSQL查询语句即可。MySQL单表能实现的大部分功能,TCSQL都能够实现,迁移起来很简单。

  【好友动态】
  TCSQL的查询语句:
  http://192.168.8.34:3888/?command=select&type=*&where=uid:NUMOREQ:0,57,48,60263,43,89163,42,74,91,93,94,105,106,109&order_by=dateline&order_sort=NUMDESC&limit_skip=0&limit_max=100

  等同于原MySQL的SQL查询语句:
  SELECT * FROM sns_feed USE INDEX(dateline) WHERE uid IN ('0',57,48,60263,43,89163,42,74,91,93,94,105,106,109) ORDER BY dateline DESC LIMIT 0,100


  【我的动态】
  TCSQL的查询语句:
  http://192.168.8.34:3888/?command=select&type=*&where=uid:NUMEQ:55&order_by=dateline&order_sort=NUMDESC&limit_skip=0&limit_max=100

  等同于原MySQL的SQL查询语句:
  SELECT * FROM sns_feed WHERE uid='55' ORDER BY dateline DESC LIMIT 0,100


  【热门推荐】
  TCSQL的查询语句:
  http://192.168.8.34:3888/?command=select&type=*&where=hot:NUMGE:3&order_by=dateline&order_sort=NUMDESC&limit_skip=0&limit_max=50

  等同于原MySQL的SQL查询语句:
  SELECT * FROM sns_feed WHERE hot >= '3' ORDER BY dateline DESC LIMIT 0,50


  【全站动态】
  TCSQL的查询语句:
  http://192.168.8.34:3888/?command=select&type=*&where=&order_by=dateline&order_sort=NUMDESC&limit_skip=0&limit_max=100

  等同于原MySQL的SQL查询语句:
  SELECT * FROM sns_feed ORDER BY dateline DESC LIMIT 0,100





技术大类 » 数据库技术 | 评论(109) | 引用(0) | 阅读(113481)
cheaperoakleysunglasses Email Homepage
2012-6-30 09:31
If you are not a movement, but would like a pair of sunglasses that will capture others attention, discount oakley sunglasses is still option is ideal for you. You like sports style was low-key and vintage look, if I decide to buy oakley sunglasses will give you every option in the Sun. Once you know that Oakley Active Sunglasses protect you from the Sun's glare, you can style and fashion--you decide, it is at the top of Oakley Asian Fit Sunglasses in this market.
lirourou77 Email Homepage
2012-9-5 14:08
Lvjing Hai, the flexibility to delay the basic pension policy, if implemented, will do the elderly occupied by young people to work. In this regard, countries <b>discount designer clothes<b>      
should be to stimulate economic growth, new jobs, to alleviate this problem.

His analysis, private enterprises and foreign enterprises, joint ventures, to attract a large number of employment staff in order to enable flexibility to delay in receiving the basic pension policies can be implemented smoothly, it seems, or should the broad-brush approach to enforce appropriate.

Chu Fu-ling: each year involving tens of millions <b>wholesale designer handbags<b>      
of people

Chu Fu Ling told reporters in the country to implement the flexibility to delay the basic pension policies each year may involve tens of millions of people, and thus lead to the problem of young people unable to enter these positions. This massive amount of posts can not be vacated, may be <b>designer handbags for less<b>      
difficult through the newly developed position, such as to resolve all of a sudden.
shulizhao Email
2012-9-28 13:40
tcsql现在有开源吗?stenve@sina.com
免费资源 Email Homepage
2013-3-4 13:07
o !很深奥哦!学习了!
wapffu
2013-11-7 06:47
我想问下  在 高并发 但是又要求数据完整的情景中,mysql同步到tc的过程中 如果mysql突然卡机 或者死机 也或者mysql发送过POST后 因为网络问题 也或者 tc所在的服务器突然故障 导致 发送来的POST同步内容没有同步到tc服务器内  那这种情况请问该怎么处理呢
蓝色的土土
2013-12-14 02:54
啥时候能公布一下啊?  等到花儿也谢了! 哈哈! 都等了 2年了!
Steven Email Homepage
2015-3-19 02:48
Thank you for this post Weight Loss Tea
rodrigo Email Homepage
2015-3-20 22:37
Thank you asphalt 8 cheats
Robert Wadera Email
2018-9-27 15:27
I hope you continue to have such quality articles to share with everyone! I believe a lot of people will be surprised to read this article!run 3
camike Email
2018-10-24 16:31
Your friend's blog I read, I am impressed by your blog, I hope you will have more blogs or more articles to bring to the reader.color switch
hotmail login Email Homepage
2019-2-21 11:27
Hotmail is the second most used emailing service behind Google’s Gmail, with more than half a billion users worldwide. Click Hotmail login to create new account free.
John Lennon Email
2019-3-6 14:55
Instagram is available for free on iOS and Android devices. Get start with Instagram video downloader now.
johnlennon Email Homepage
2019-3-6 14:58
Instagram is available for free on iOS and Android devices. Get start with Instagram video downloader now.
Five Nights at Freddy's Email Homepage
2019-3-14 17:13
Thanks for sharing these useful information! This is really interesting information for me.  Five Nights at Freddy's
hotmail login english
2019-5-9 11:22
thank you for sharing this informatic post, it's very useful and detailed, hotmail login english
helensten Email
2019-6-20 10:53
I appreciate it!. I really like it when people get together and share ideas. Great website, continue the good work super smash flash 2
Artificial Intelligence Email Homepage
2019-8-9 19:41
tricks to keep phone safe Email Homepage
2019-8-9 19:50
First Java Class Email Homepage
2019-8-9 19:53
I read many articles but this one is awesome.<a href="https://www.instructables.com/id/My-First-Java-Class/#comments-section">Java Class</a>
Online Survey Email Homepage
2019-8-9 19:55
分页: 3/6 第一页 上页 1 2 3 4 5 6 下页 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]