MemSQL 入门体验小记
- WBOYOriginal
- 2016-06-07 17:34:511869browse
看到MemSQL 的介绍后，花了几分钟浏览网站，阅读getting started并下载安装启动，在此小记。
1 看到MemSQL 的介绍后，花了几分钟浏览网站，阅读getting started并下载安装启动，在此小记。
2 MemSQL
其宣传Slogan 是 THE WORLD'S FASTEST DATABASE.YOUR DATA. ACCELERATED.
我们看到最多的中文介绍是 “由前Facebook工程师创办的MemSQL，号称世界上最快的分布式关系型数据库，兼容MySQL但快30倍，能实现每秒150万次事务。原理是仅用内存并将SQL预编译为C++。”
3 如何开始？
- 点击右上角的下载链接 ，它会要求你提供email等信息，提交后返回一个license key和下载链接，我们下载developer版的即可。
- 安装
$ tar -xzf memsqlbin_amd64.tar.gz
$ cd memsqlbin
- 启动
$ ./check_system
$ ./memsqld
注：
- check_system会告诉系统是否满足需求，比如内核版本，OS版本，64位 等等
- 启动的时候，要加 -u root，否则会报fatal错误；如果你已经有mysql在运行，那么可能需要更改默认启动端口3306为其他端口，，比如 3307
./memsqld -u root --port 3307- 如果你的RAM没有8G，启动的时候会有警告，但可以忽略。
- 测试
$ mysql -u root -h 127.0.0.1 -P 3306 --prompt="memsql> "
注： 这里-h不要使用localhost，用 127.0.0.1
进入命令行后就是标准的mysql客户端了。
memsql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| memsql |
+--------------------+
2 rows in set (0.00 sec)
建库：
memsql> create database chris001;
Query OK, 0 rows affected (0.08 sec)
memsql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| memsql |
| chris001 |
+--------------------+
3 rows in set (0.00 sec)
建表：
memsql> use chris001;
Database changed
memsql>
memsql> create table tab001 (id int primary key, name varchar(100), memo varchar(200),num int);
Query OK, 0 rows affected (6.71 sec)
memsql>
memsql>
memsql> show tables;
+--------------------+
| Tables_in_chris001 |
+--------------------+
| tab001 |
+--------------------+
1 row in set (0.00 sec)
memsql> desc tab001;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(100) | YES | | NULL | |
| memo | varchar(200) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
插入数据后查询：
memsql> insert into tab001 values (1,'chris','xxx',100);
Query OK, 1 row affected (1.82 sec)
memsql>
memsql>
memsql> select * from tab001;
+----+-------+------+------+
| id | name | memo | num |
+----+-------+------+------+
| 1 | chris | xxx | 100 |
+----+-------+------+------+
1 row in set (0.71 sec)
memsql> select * from tab001;
+----+-------+------+------+
| id | name | memo | num |
+----+-------+------+------+
| 1 | chris | xxx | 100 |
+----+-------+------+------+
1 row in set (0.00 sec)
memsql> select * from tab001;
+----+-------+------+------+
| id | name | memo | num |
+----+-------+------+------+
| 1 | chris | xxx | 100 |
+----+-------+------+------+
1 row in set (0.00 sec)
memsql> select * from tab001;
+----+-------+------+------+
| id | name | memo | num |
+----+-------+------+------+
| 1 | chris | xxx | 100 |
+----+-------+------+------+
1 row in set (0.00 sec)
memsql> select count(0) from tab001;
+----------+
| count(0) |
+----------+
| 1 |
+----------+
1 row in set (0.63 sec)
memsql>
memsql> select count(0) from tab001;
+----------+
| count(0) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
memsql> select count(0) from tab001;
+----------+
| count(0) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
查询的时候，服务端会打出日志：
136674795 2012-06-19 10:02:31 INFO: Table chris001.tab001 compiled in 6709 miliseconds (3185 miliseconds for header)
136678436 2012-06-19 10:02:31 INFO: Table chris001.tab001 row size 80 bytes (base overhead 32 bytes, column id 4 bytes, column name 12 bytes, column memo 12 bytes, column num 8 bytes)
186966810 2012-06-19 10:03:21 INFO: Query chris001.'insert into tab001 values (?,?,?,?)' compiled in 1825 miliseconds
194366714 2012-06-19 10:03:29 INFO: Query chris001.'select * from tab001' compiled in 712 miliseconds
283342115 2012-06-19 10:04:58 INFO: Query chris001.'select count(0) from tab001' compiled in 632 miliseconds
从以上测试我们看到，两次查询的首次和之后时间对比：
select * from tab001; (0.71 sec) (0.00 sec)
select count(0) from tab001; (0.63 sec) (0.00 sec)
从日志可以知道，首次查询的耗时基本全都花在编译上了 。
- 关闭
$ killall memsqld
4 总结
引用 MemSQL 网站的介绍