MySQL存储引擎性能压测
一、表结构:
CREATE TABLE `huayu_tokudb` (
`waybill_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '?????',
`waybill_no` varchar(20) NOT NULL COMMENT '???',
`post_date` datetime NOT NULL COMMENT '????????',
`order_id` varchar(50) DEFAULT NULL COMMENT '?????????',
`batch_no` varchar(50) DEFAULT NULL COMMENT '???',
`biz_product_id` varchar(20) NOT NULL COMMENT '??????',
`biz_product_no` varchar(20) NOT NULL COMMENT '??????',
`biz_product_name` varchar(50) NOT NULL COMMENT '??????',
`sender_type` varchar(20) DEFAULT NULL COMMENT '0 ?? 1????',
`sender_id` varchar(50) DEFAULT NULL COMMENT '??????',
`sender_no` varchar(50) DEFAULT NULL COMMENT '??????(??????)',
`sender` varchar(200) DEFAULT NULL COMMENT '??????',
`sender_linker` varchar(200) DEFAULT NULL,
`sender_fixtel` varchar(50) DEFAULT NULL COMMENT '??????',
`sender_mobile` varchar(50) DEFAULT NULL COMMENT '??????',
`sender_addr` varchar(200) DEFAULT NULL COMMENT '??????',
`sender_country_no` varchar(20) DEFAULT NULL COMMENT '??????',
`sender_country_name` varchar(50) DEFAULT NULL COMMENT '??????',
`sender_province_no` varchar(20) DEFAULT NULL COMMENT '??????',
`sender_province_name` varchar(50) DEFAULT NULL COMMENT '??????',
`sender_city_no` varchar(20) DEFAULT NULL COMMENT '??????',
`sender_city_name` varchar(50) DEFAULT NULL COMMENT '??????',
`sender_county_no` varchar(20) DEFAULT NULL COMMENT '??????',
`sender_county_name` varchar(50) DEFAULT NULL COMMENT '??????',
`sender_notes` varchar(1000) DEFAULT NULL COMMENT '????',
`receiver_no` varchar(50) DEFAULT NULL COMMENT '??????????????',
`receiver` varchar(200) DEFAULT NULL COMMENT '??????',
`receiver_linker` varchar(200) DEFAULT NULL,
`receiver_fixtel` varchar(50) DEFAULT NULL COMMENT '??????',
`receiver_mobile` varchar(50) DEFAULT NULL COMMENT '??????',
`receiver_addr` varchar(200) DEFAULT NULL COMMENT '??????',
`receiver_country_no` varchar(20) DEFAULT NULL COMMENT '??????',
`receiver_country_name` varchar(50) DEFAULT NULL COMMENT '??????',
`receiver_province_no` varchar(20) DEFAULT NULL COMMENT '??????',
`receiver_province_name` varchar(50) DEFAULT NULL COMMENT '??????',
`receiver_city_no` varchar(20) DEFAULT NULL COMMENT '??????',
`receiver_city_name` varchar(50) DEFAULT NULL COMMENT '??????',
`receiver_county_no` varchar(20) DEFAULT NULL COMMENT '??????',
`receiver_county_name` varchar(50) DEFAULT NULL COMMENT '??????',
`insurance_flag` char(1) DEFAULT NULL COMMENT '????????????1:?? 2:?? 3:??',
`insurance_amount` decimal(12,2) DEFAULT NULL COMMENT '??????',
`pickup_type` varchar(20) DEFAULT NULL COMMENT '?????0 ???????1 ??????',
`payment_mode` varchar(20) DEFAULT NULL COMMENT '????(???) 1:??? 2:??? 3:??? 4:??????? 5:?? 6:?/??? 7:???',
`real_weight` decimal(8,0) DEFAULT NULL COMMENT '????',
`fee_weight` decimal(8,0) DEFAULT NULL,
`volume_weight` decimal(8,0) DEFAULT NULL,
`length` decimal(8,0) DEFAULT NULL,
`width` decimal(8,0) DEFAULT NULL,
`height` decimal(8,0) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`packaging` varchar(20) DEFAULT NULL,
`package_material` varchar(20) DEFAULT NULL,
`goods_desc` varchar(200) DEFAULT NULL,
`contents_quantity` int(11) DEFAULT NULL,
`cod_flag` char(1) DEFAULT NULL,
`cod_amount` decimal(12,2) DEFAULT NULL,
`receipt_flag` char(1) DEFAULT NULL,
`receipt_waybill_no` varchar(20) DEFAULT NULL,
`receipt_fee_amount` decimal(12,2) DEFAULT NULL,
`insurance_premium_amount` decimal(12,2) DEFAULT NULL,
`valuable_flag` char(1) DEFAULT NULL,
`cargo_total_price` decimal(12,2) DEFAULT NULL,
`cargo_total_purchasing_price` decimal(12,2) DEFAULT NULL,
`allow_fee_flag` char(1) DEFAULT NULL,
`is_feed_flag` char(1) DEFAULT NULL,
`manual_fee_type` char(1) DEFAULT NULL,
`fee_date` datetime DEFAULT NULL,
`discount_rate` decimal(6,2) DEFAULT NULL,
`settlement_mode` varchar(20) DEFAULT NULL,
`payment_state` char(1) DEFAULT NULL,
`payment_date` datetime DEFAULT NULL,
`payment_id` varchar(50) DEFAULT NULL,
`manage_org_code` varchar(20) DEFAULT NULL,
`postage_suite_code` varchar(20) DEFAULT NULL,
`fee_area_suite_code` varchar(20) DEFAULT NULL,
`fee_area_code` varchar(20) DEFAULT NULL,
`fee_area_name` varchar(20) DEFAULT NULL,
`is_advance_flag` char(1) DEFAULT NULL,
`deliver_type` char(1) DEFAULT NULL,
`deliver_sign` varchar(50) DEFAULT NULL,
`deliver_date` char(1) DEFAULT NULL,
`deliver_notes` varchar(1000) DEFAULT NULL,
`deliver_pre_date` date DEFAULT NULL,
`battery_flag` char(1) DEFAULT NULL,
`is_jinguan` varchar(20) DEFAULT NULL,
`workbench` varchar(20) DEFAULT NULL,
`electronic_preferential_no` varchar(50) DEFAULT NULL,
`electronic_preferential_amount` decimal(12,2) DEFAULT NULL,
`pickup_attribute` char(1) DEFAULT NULL,
`adjust_type` varchar(20) DEFAULT NULL,
`postage_revoke` decimal(12,2) DEFAULT NULL,
`print_flag` char(1) DEFAULT NULL,
`print_date` datetime DEFAULT NULL,
`print_times` int(11) DEFAULT NULL,
`declare_source` varchar(20) DEFAULT NULL,
`declare_type` varchar(20) DEFAULT NULL,
`declare_curr_code` varchar(20) DEFAULT NULL,
`create_user_name` varchar(50) DEFAULT NULL,
`modify_user_name` varchar(50) DEFAULT NULL,
`volume` decimal(8,0) DEFAULT NULL COMMENT '??',
`contents_type_no` varchar(20) DEFAULT NULL COMMENT '??????',
`contents_type_name` varchar(200) DEFAULT NULL COMMENT '??????',
`contents_weight` decimal(8,0) DEFAULT NULL COMMENT '??????',
`transfer_type` varchar(20) DEFAULT NULL COMMENT '????',
`postage_total` decimal(12,2) NOT NULL COMMENT '???=????+????',
`postage_standard` decimal(12,2) DEFAULT NULL COMMENT '????',
`postage_paid` decimal(12,2) DEFAULT NULL COMMENT '????',
`postage_other` decimal(12,2) DEFAULT NULL COMMENT '????',
`is_deleted` char(1) DEFAULT '0' COMMENT '?????\n0??\n1??',
`create_user_id` bigint(20) DEFAULT NULL COMMENT '???id',
`gmt_created` datetime DEFAULT NULL COMMENT '????',
`modify_user_id` bigint(20) DEFAULT NULL COMMENT '???id',
`gmt_modified` datetime DEFAULT NULL COMMENT '????',
`reserved1` bigint(20) DEFAULT NULL COMMENT '????1',
`reserved2` bigint(20) DEFAULT NULL COMMENT '????2',
`reserved3` bigint(20) DEFAULT NULL COMMENT '????3',
`reserved4` varchar(200) DEFAULT NULL COMMENT '????4',
`reserved5` varchar(200) DEFAULT NULL COMMENT '????5',
`reserved6` varchar(200) DEFAULT NULL COMMENT '????6',
`reserved7` varchar(200) DEFAULT NULL COMMENT '????7',
`reserved8` varchar(200) DEFAULT NULL COMMENT '????8',
`reserved9` datetime DEFAULT NULL COMMENT '????9',
`reserved10` text COMMENT '????10',
`logistics_order_no` bigint(50) DEFAULT NULL,
`inner_channel` varchar(20) DEFAULT NULL,
`base_product_id` bigint(20) DEFAULT NULL,
`base_product_no` varchar(20) DEFAULT NULL,
`base_product_name` varchar(20) DEFAULT NULL,
`is_special_marketing` char(1) DEFAULT NULL,
`product_type` varchar(20) DEFAULT NULL,
`biz_product_type` varchar(20) DEFAULT NULL,
`product_reach_area` char(1) DEFAULT NULL,
`contents_attribute` char(1) DEFAULT NULL,
`contents_cargo_no` varchar(1000) DEFAULT NULL,
`cmd_code` varchar(20) DEFAULT NULL,
`manual_charge_reason` varchar(200) DEFAULT NULL,
`time_limit` char(1) DEFAULT NULL,
`io_type` varchar(20) DEFAULT NULL,
`ecommerce_no` varchar(20) DEFAULT NULL,
`waybill_type` varchar(20) DEFAULT NULL,
`pre_waybill_no` varchar(50) DEFAULT NULL,
`post_batch_id` varchar(50) DEFAULT NULL,
`biz_occur_date` datetime DEFAULT NULL,
`post_org_id` bigint(20) DEFAULT NULL,
`post_org_no` varchar(50) DEFAULT NULL,
`org_drds_code` varchar(50) DEFAULT NULL,
`post_org_simple_name` varchar(50) DEFAULT NULL,
`post_org_product_name` varchar(20) DEFAULT NULL,
`post_person_id` bigint(20) DEFAULT NULL,
`post_person_no` varchar(50) DEFAULT NULL,
`post_person_name` varchar(50) DEFAULT NULL,
`post_person_mobile` varchar(50) DEFAULT NULL,
`sender_warehouse_id` bigint(20) DEFAULT NULL,
`sender_warehouse_name` varchar(200) DEFAULT NULL,
`sender_safety_code` varchar(50) DEFAULT NULL,
`sender_im_type` varchar(20) DEFAULT NULL,
`sender_im_id` varchar(50) DEFAULT NULL,
`sender_id_type` varchar(20) DEFAULT NULL,
`sender_id_no` varchar(50) DEFAULT NULL,
`sender_id_encrypted_code` varchar(50) DEFAULT NULL,
`sender_agent_id_type` varchar(20) DEFAULT NULL,
`sender_agent_id_no` varchar(50) DEFAULT NULL,
`sender_id_encrypted_code_agent` varchar(50) DEFAULT NULL,
`sender_addr_additional` varchar(200) DEFAULT NULL,
`sender_district_no` varchar(20) DEFAULT NULL,
`sender_postcode` varchar(20) DEFAULT NULL,
`sender_gis` varchar(20) DEFAULT NULL,
`registered_customer_no` varchar(50) DEFAULT NULL,
`receiver_type` char(1) DEFAULT NULL,
`receiver_id` bigint(20) DEFAULT NULL,
`receiver_warehouse_id` bigint(20) DEFAULT NULL,
`receiver_warehouse_name` varchar(200) DEFAULT NULL,
`receiver_safety_code` varchar(50) DEFAULT NULL,
`receiver_im_type` varchar(20) DEFAULT NULL,
`receiver_im_id` varchar(50) DEFAULT NULL,
`receiver_addr_additional` varchar(200) DEFAULT NULL,
`receiver_district_no` varchar(20) DEFAULT NULL,
`receiver_postcode` varchar(20) DEFAULT NULL,
`receiver_gis` varchar(20) DEFAULT NULL,
`receiver_notes` varchar(1000) DEFAULT NULL,
`customer_manager_id` bigint(20) DEFAULT NULL,
`customer_manager_no` varchar(50) DEFAULT NULL,
`customer_manager_name` varchar(50) DEFAULT NULL,
`salesman_id` bigint(20) DEFAULT NULL,
`salesman_no` varchar(50) DEFAULT NULL,
`salesman_name` varchar(50) DEFAULT NULL,
`order_weight` decimal(8,0) DEFAULT NULL,
`post_org_name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`waybill_id`),
KEY `auto_shard_key_post_date` (`post_date`),
KEY `auto_shard_key_waybill_no` (`waybill_no`)
) ENGINE=InnoDB row_format=compressed AUTO_INCREMENT=568964219 DEFAULT CHARSET=utf8 COMMENT='???????';
二、innodb表的数据大小:
mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='qps_waybill_base_02';
+------------------------------------------------------+
| concat(round(sum(data_length/1024/1024/1024),3),'G') |
+------------------------------------------------------+
| 18.636G |
+------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select count(*) from qps_waybill_base_02;
+----------+
| count(*) |
+----------+
| 15861881 |
+----------+
1 row in set (5.91 sec)
mysql>
三、存储引擎压缩说明
压缩的理念:
通过提高CPU利用率和节约成本,降低数据库容量及I/O负载,从而使数据吞吐率得到显著提高
压缩表减少了磁盘上数据库的大小,使得用户不必频繁地操作写入和读取便可以访问数据。对于 InnoDB的工作量以及传统的用户表而言(特别是在某些读取密集型的应用中,内存有足够的空间存储常用数据),数据压缩不仅大大减少了数据库所需的存储空间,而且还减少了 I/O的工作量,提高了数据吞吐率,从而节约开销处理成本。节省存储成本固然重要,但是减少 I/O成本更为关键。
通常情况下表结构中包含字符型数据列如char, varchar, text或blob等时,具有较高的压缩率,而一些二进制数据,如整形或浮点型数据列,和一些已经压缩的多媒体文档,如jpeg、jpg、png等格式图片及mp4、avi等格式视频,其压缩率都不会好,再对其进行压缩就是纯粹浪费CPU资源
1、tokudb存储引擎
阿里云数据库 MySQL 5.6 版支持通过 TokuDB 存储引擎压缩数据。经过大量测试表明,数据表从 InnoDB 存储引擎转到 TokuDB 存储引擎后,数据量可以减少 80% 到 90%,除了数据压缩外,TokuDB 存储引擎还支持事务和在线DDL 操作,可以很好兼容运行于 MyISAM 或 InnoDB 存储引擎上的应用。
Tokudb压缩模式:
tokudb_fast: 使用quicklz 库的压缩模式。
tokudb_small: 使用 lzma 库的压缩模式。
tokudb_zlib: (默认)使用 zlib 库的压缩模式,提供了中等级别的压缩比和中等级别的CPU消耗。
tokudb_quicklz: 使用 quicklz 库的压缩模式, 提供了轻量级的压缩比和较低基本的CPU消耗。
tokudb_lzma: 使用lzma库压缩模式,提供了高压缩比和高CPU消耗。
tokudb_uncompressed: 不使用压缩模式。