MariaDB Columnstore引擎使用注意事项
一、字段属性限制
1、varchar最大8000
2、不支持bit类型
3、不支持Reserved keywords保留关键字user、comment、match、key、update、status作为表名、字段名或用户定义的变量、函数或存储过程的名称。
4、不支持zerofill
5、不支持enum枚举类型
6、comment不能携带''引号
create table t1(id int comment '主键''ID')engine=Columnstore;
7、不支持主键自增
二、SQL语句限制
1、查询的字段不在group by里，就不能分组统计
错误写法：
MariaDB [test]> select id from t1 group by name; ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`t1`.`id`' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause. |
MariaDB [test]> select id,name from t1 group by name; ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`t1`.`id`' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause. |
正确写法：
MariaDB [test]> select name from t1 group by name; |
2、alter不支持多列操作和不支持after
MariaDB [test]> alter table t1 add age tinyint,add address varchar(100); ERROR 1178 (42000): The storage engine for the table doesn't support Multiple actions in alter table statement is currently not supported by Columnstore. |
3、字段类型不同 join 关联查询报错，比如表1的id字段为int，表2的字段id为varchar，进行关联查询join就会报错
MariaDB [test]> select t1.id from t1 join t2 on t1.id=t2.cid; ERROR 1815 (HY000): Internal error: IDB-1002: 't1' and 't2' have incompatible column type specified for join condition. |
4、alter不支持change/modify更改字段属性
MariaDB [test]> alter table t1 change id id bigint; ERROR 1815 (HY000): Internal error: CAL0001: Alter table Failed: Changing the datatype of a column is not supported |
建表范例：
CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) DEFAULT '', `pad` char(60) NOT NULL DEFAULT '' ) ENGINE=Columnstore DEFAULT CHARSET=utf8; |
从InnDB 迁移到 Columnstore 列式存储
第一步、只导出表结构
/usr/local/mysql-5.7.42/bin/mysqldump -S /tmp/mysql_mysql57_1.sock test sbtest1 --compact -d -q --set-gtid-purged=OFF > ./sbtest.sql
第二步、更改表结构字段属性，改为Columnstore
./convert.sh sbtest.sql sbtest_new.sql
sed -e 's/InnoDB/Columnstore/;s/NOT NULL//g;/.*KEY.*/d;s/AUTO_INCREMENT=[0-9]*//g;s/AUTO_INCREMENT//g;s/\<timestamp\>.*TIMESTAMP\|\<timestamp\>/datetime /g;s/\<COLLATE.*utf8_bin\>//g;s/\<CHARACTER.*SET.*[utf8|utf8mb4]\>//g;s/decimal([0-9]*/decimal(18/g;s/ROW_FORMAT=COMPACT//g;s/bit/int/g;s/COLLATE.* utf8mb4_unicode_ci//g;s/DEFAULT CURRENT_TIMESTAMP//g;s/\<text\>/varchar(8000)/g;s/\<longtext\>/varchar(8000)/g;s/\<mediumtext\>/varchar(8000)/g;s/COLLATE utf8mb4_bin//g;s/ON UPDATE CURRENT_TIMESTAMP//g' /data/bak/$1 | sed '/,/{:loop; N; /,\s*)/! bloop; s/,\s*)/\n)/}' > /data/bak/$2第三步、用mydumper多线程导出数据(CSV格式)
/usr/bin/mydumper -S /tmp/mysql_mysql57_1.sock --regex 'test.sbtest1' -t 16 --csv -v 3 --rows 100000 --no-schemas -o ./
第四步，恢复到MariaDB Columnstore列式存储里
myloader -S /tmp/mysql_mariadb.sock -t 1 -v 3 -B test -d ./
