quicklz php,MariaDB 使用TokuDB存储引擎

INSERT authors_tokudb

SELECT

author_id,

author_canonical,

author_lastname, author_birthplace,

author_birthdate, author_deathdate

FROM authors;

3.查看索引

SHOW INDEXES FROM authors_tokudb;

4.调优

OPTIMIZE TABLE authors_tokudb;

五.转换存储引擎到TokuDB

ALTER TABLE pub_authors ENGINE=TokuDB;

ALTER TABLE pub_content ENGINE=TokuDB;

ALTER TABLE pub_series ENGINE=TokuDB;

ALTER TABLE publishers ENGINE=TokuDB;

ALTER方式将某张表转换存储引擎，实际上做的事情如下，

CREATE TABLE notes_tokudb LIKE notes;

ALTER TABLE notes_tokudb ENGINE=TokuDB;

SELECT * FROM notes INTO OUTFILE '/tmp/notes.tmp';

LOAD DATA INFILE '/tmp/notes.tmp' INTO TABLE

notes_tokudb;

(jlive)[isfdb]>CREATE

TABLE notes_tokudb LIKE notes;

Query OK,

0 rows affected (0.15 sec)

(jlive)[isfdb]>ALTER

TABLE notes_tokudb ENGINE=TokuDB;

Query OK,

0 rows affected (0.02 sec)

Records:

0 Duplicates: 0 Warnings:

0

(jlive)[isfdb]>SELECT

* FROM notes INTO OUTFILE '/tmp/notes.tmp';

Query OK,

417609 rows affected (0.42 sec)

(jlive)[isfdb]>LOAD

DATA INFILE '/tmp/notes.tmp' INTO

TABLE notes_tokudb;

Query OK,

417609 rows affected, 51940 warnings (4.32 sec)

Records:

417609 Deleted: 0 Skipped:

0 Warnings: 51940

六.添加index到TokuDB表

SHOW INDEXES FROM authors;

ALTER TABLE authors

DROP KEY canonical,

ADD

CLUSTERING KEY canonical (author_canonical(50)),

ENGINE=TokuDB;

CREATE CLUSTERING INDEX birthdate ON authors

(author_birthdate);

七.压缩TokuDB表

相较其它存储引擎，TokuDB最大的特点就是在数据高压缩率的情况下仍然具有非常高的性能

ALTER TABLE titles ENGINE=TokuDB

ALTER TABLE titles ENGINE=TokuDB ROW_FORMAT=default;

在没有指定压缩率时，压缩率默认为default

ALTER TABLE pub_content ENGINE=TokuDB

ROW_FORMAT=tokudb_small;

TokuDB官方建议

<=6核 压缩率选default

>6核 压缩率选tokudb_small

tokudb_fast是tokudb_quicklz的别名，默认的压缩选项目前是tokudb_fast

ALTER TABLE canonical_author ENGINE=TokuDB

ROW_FORMAT=tokudb_fast;

tokudb_small是tokudb_lzma的别名

ALTER TABLE notes ENGINE=TokuDB ROW_FORMAT=tokudb_lzma;

不压缩

ALTER TABLE pubs ENGINE=TokuDB ROW_FORMAT=tokudb_uncompressed;

OPTIMIZE TABLE titles, pub_content, canonical_author, notes,

pubs;