背景, 大数据中查询用mysql时间太长, 使用clickhouse 速度快, 数据写入mysql后同步到clickhouse中
测试1千万数据模糊搜索 mysql 需要30-40秒 clickhouse 约 100ms
一 数据结构和存储引擎
1 查看clickhouse所有数据类型
select * from system.data_type_families;
2 常用数据类型及同mysql对比
MySQL | Clickhouse | 大小(字节) | 数据范围 |
tinyint | Int8 | 1 | [-128 : 127] |
smallint | Int16 | 2 | [-32768 : 32767] |
int | Int32 | 3 | [-2147483648 : 2147483647] |
bigint | Int64 | 4 | [-9223372036854775808 : 9223372036854775807] |
Int unsigned | UInt32 | 3 | [0 : 4294967295] |
MySQL | Clickhouse | 大小(字节) | 有效精度（位数） |
float | Float32 | 4 | 6~7 |
double | Flout64 | 8 | 15~16 |
3 表存储引擎 engine
TinyLog，不分区，不索引，磁盘，列数据文件（.bin）
Memory，不分区，不索引，内存 (内存中, clickhouse 重启后表结构存在但数据被清空)
Merge，合并查询，并行查询多张表，类似视图
MergeTree，分区，索引，稀疏索引文件（.idx），列标识文件（.mrk，建立.idx与.bin之间的映射关系），列数据文件（.bin）
ReplacingMergeTree，分区，索引，组内去重
SummingMergeTree，分区，索引，组内聚合
Distributed分片集群，分布式表二 sql操作和数据导入
1 sql操作
-- 新建数据库
CREATE DATABASE IF NOT EXISTS tmp_test;
-- 新建数据表
create table demo(id Int32,name String) engine=Memory;
-- 插入数据 , 注意 字符串使用单引号
insert into demo(id,name) values(110,'zhangsan');
-- 查询
select * from demo where name like '%san%' --字符串不能使用双引号
2 数据导入
a csv直接导入, 会新建表, 默认engine是Memory
b 先新建表, 再csv导入, 好处是可以指定engine是TinyLog
c 查询出数据数组, 使用代码插入
三 php操作clickhouse数据库
https://github.com/smi2/phpClickHouse
composer require smi2/phpclickhouse
$config = [
'host' => '192.168.1.1',
'port' => '8123',
'username' => 'default',
'password' => ''
];
$db = new ClickHouseDB\Client($config);
if (!$db->ping()) echo 'Error connect';
# 查看建表sql
$db->database('tmp_test');
echo $db->showCreateTable('demo');
# 插入数据
$db->insert('demo',
[
[1, 'A1'],
[2, 'A2'],
[3, 'A3'],
],
['id', 'name']
);
# 查询
$statement = $db->select('SELECT * FROM demo LIMIT 10');
var_dump($statement->rows());