mysql 分区 -

最新推荐文章于 2022-06-12 23:29:10 发布

TerryWater

最新推荐文章于 2022-06-12 23:29:10 发布

阅读量476

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/terry_water/article/details/50163741

版权

mysql 专栏收录该内容

73 篇文章 0 订阅

订阅专栏

为什么要分表和分区？

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

mysql分表和分区有什么联系呢？
1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。
2.分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。
3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。
4.表分区相对于分表，操作方便，不需要创建子表。

其他参考：http://www.2cto.com/database/201503/380348.html

1. 我要把 sales_order_info 进行分区

首先，导出表结构，然后新建一个表：sales_order_info_part

CREATE TABLE IF NOT EXISTS `sales_order_info_part` (
  `order_id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `increment_id` varchar(50) DEFAULT NULL COMMENT '递增个数',
  `erp_order_source_code` varchar(20) DEFAULT NULL COMMENT '订单来源简码，譬如ebay，onfancy',
  `order_status` varchar(35) DEFAULT NULL COMMENT '订单状态',
  `domain` varchar(130) DEFAULT NULL,
  `store` varchar(30) DEFAULT NULL COMMENT 'store name',
  `created_at` datetime DEFAULT NULL COMMENT 'B2C 网站的订单生成的创建时间，而不是erp插入的时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `is_active` int(5) DEFAULT '1' COMMENT '1代表激活，2代表关闭',
  `items_count` int(10) DEFAULT '0' COMMENT '订单中产品的总个数，默认为0个',
  `total_weight` decimal(12,4) DEFAULT '0.0000' COMMENT '总重量',
  `order_currency_code` varchar(20) DEFAULT NULL COMMENT '当前货币',
  `order_to_base_rate` decimal(12,4) DEFAULT NULL COMMENT '当前货币和默认货币的比率',
  `grand_total` decimal(12,4) DEFAULT NULL COMMENT '当前订单的总额',
  `base_grand_total` decimal(12,4) DEFAULT NULL COMMENT '当前订单的默认货币总额',
  `subtotal` decimal(12,4) DEFAULT NULL COMMENT '当前订单的产品总额',
  `base_subtotal` decimal(12,4) DEFAULT NULL COMMENT '当前订单的产品默认货币总额',
  `subtotal_with_discount` decimal(12,4) DEFAULT NULL COMMENT '当前订单的去掉的总额',
  `base_subtotal_with_discount` decimal(12,4) DEFAULT NULL COMMENT '当前订单的去掉的默认货币总额',
  `is_changed` int(5) DEFAULT '1' COMMENT '是否change，1代表是，2代表否',
  `checkout_method` varchar(20) DEFAULT NULL COMMENT 'guest，register，代表是游客还是登录客户。',
  `customer_id` int(15) DEFAULT NULL COMMENT '客户id',
  `customer_group` varchar(30) DEFAULT NULL COMMENT '客户组id',
  `customer_email` varchar(60) DEFAULT NULL COMMENT '客户邮箱',
  `customer_firstname` varchar(50) DEFAULT NULL COMMENT '客户名字',
  `customer_lastname` varchar(50) DEFAULT NULL COMMENT '客户名字',
  `customer_is_guest` int(5) DEFAULT NULL COMMENT '是否是游客，1代表是游客，2代表不是游客',
  `remote_ip` varchar(40) DEFAULT NULL COMMENT 'ip地址',
  `coupon_code` varchar(40) DEFAULT NULL COMMENT '优惠劵',
  `payment_method` varchar(80) DEFAULT NULL COMMENT '支付方式',
  `shipping_method` varchar(150) DEFAULT NULL COMMENT '货运方式',
  `shipping_total` decimal(12,4) DEFAULT NULL COMMENT '运费总额',
  `base_shipping_total` decimal(12,4) DEFAULT NULL COMMENT '默认货币运费总额',
  `customer_telephone` varchar(255) DEFAULT NULL COMMENT '客户电话',
  `customer_address_country` varchar(255) DEFAULT NULL COMMENT '客户国家',
  `customer_address_state` varchar(255) DEFAULT NULL COMMENT '客户省',
  `customer_address_city` varchar(255) DEFAULT NULL COMMENT '客户市',
  `customer_address_zip` varchar(255) DEFAULT NULL COMMENT '客户zip',
  `customer_address_street1` text COMMENT '客户地址1',
  `customer_address_street2` text COMMENT '客户地址2',
  `txn_type` varchar(20) DEFAULT NULL COMMENT 'translate类型',
  `txn_id` varchar(50) DEFAULT NULL COMMENT 'translate id',
  `payer_id` varchar(30) DEFAULT NULL COMMENT '交易号',
  `ipn_track_id` varchar(20) DEFAULT NULL,
  `receiver_id` varchar(20) DEFAULT NULL,
  `verify_sign` varchar(80) DEFAULT NULL,
  `charset` varchar(20) DEFAULT NULL,
  `payment_fee` decimal(12,4) DEFAULT NULL COMMENT '交易服务费',
  `payment_type` varchar(20) DEFAULT NULL COMMENT '交易类型',
  `correlation_id` varchar(20) DEFAULT NULL COMMENT '相关id，快捷支付里面的字段',
  `base_payment_fee` decimal(12,4) DEFAULT NULL COMMENT '交易费用，基础货币值，通过货币进行的转换',
  `protection_eligibility` varchar(20) DEFAULT NULL COMMENT '保护资格，快捷支付里面的字段',
  `protection_eligibility_type` varchar(255) DEFAULT NULL COMMENT '保护资格类型，快捷支付里面的字段',
  `secure_merchant_account_id` varchar(20) DEFAULT NULL COMMENT '商人账户安全id',
  `build` varchar(20) DEFAULT NULL COMMENT 'build',
  `paypal_payer_email` varchar(120) DEFAULT NULL COMMENT 'paypal账号的邮箱地址',
  `erp_order_create_datetime` datetime DEFAULT NULL COMMENT '订单创建，Paypal时间',
  `erp_global_status` int(5) DEFAULT '0' COMMENT '1 代表订单导入成功 2代表订单准备通过paypal 接口核对订单状态 3代表订单和paypal接口订单状态核对成功，4 订单财务确认成功，5 代表订单人工审核成功，6 确认货运公司状态 7 获取货运公司的 货运号 成功状态8 发货成功状态',
  `erp_order_paypalapi_sync_datetime` datetime DEFAULT NULL COMMENT '和paypal api核对订单状态完成的时间',
  `erp_finance_person` varchar(20) DEFAULT NULL COMMENT '订单财务审核人员',
  `erp_order_finance_person_datetime` datetime DEFAULT NULL COMMENT '订单财务审核时间',
  `erp_order_info_check_person` varchar(20) DEFAULT NULL COMMENT '订单内容审核人员',
  `erp_order_info_check_status` int(5) DEFAULT '0' COMMENT '订单信息人工核查后的状态，0代表默认，1代表核查成功，2代表核查失败',
  `erp_order_info_check_datetime` datetime DEFAULT NULL COMMENT '订单信息审核时间',
  `erp_order_get_freight_company_datetime` datetime DEFAULT NULL COMMENT '得到货运公司完成的时间',
  `erp_order_get_cargo_no_datetime` datetime DEFAULT NULL COMMENT '得到货运公司的货运编号时间',
  `erp_order_delivery_person` varchar(20) DEFAULT NULL COMMENT '订单发货人员',
  `erp_order_delivery_datetime` datetime DEFAULT NULL COMMENT '订单发货时间',
  `tracking_number` varchar(40) DEFAULT NULL COMMENT '货运号',
  `mail_no` varchar(40) CHARACTER SET utf8 COLLATE utf8_german2_ci DEFAULT NULL COMMENT '邮号',
  `get_shipping_company_remark` varchar(255) DEFAULT NULL COMMENT '获取货运公司备注',
  `get_shipping_company_datetime` datetime DEFAULT NULL COMMENT '获取货运公司的时间',
  `get_shipping_company_status` int(5) DEFAULT '0' COMMENT '0为默认值，1代表获取货运公司成功，2代表获取货运公司失败',
  `shipping_company` varchar(40) DEFAULT NULL COMMENT '货运公司编号，以及对应的类型',
  `get_tracking_number_status` int(5) DEFAULT '0' COMMENT '获取货运号后的状态，如果获取完成后成功，那么为1，如果获取货运号失败，那么为2',
  `get_tracking_number_fail_count` int(10) NOT NULL DEFAULT '0' COMMENT 'api获取物流号失败的次数',
  `get_tracking_number_datetime` datetime DEFAULT NULL COMMENT '获取货运号的时间',
  `get_tracking_number_return_code` text COMMENT '获取货运号后的返回值',
  `erp_order_source_api_get_type` int(5) DEFAULT NULL COMMENT '订单来源，1代表api导入，2代表csv表格导入',
  `erp_order_source_api_from_type` int(5) DEFAULT NULL COMMENT '订单类型，1代表B2C网站，2代表平台',
  `erp_order_prepare_shipping_person` varchar(100) DEFAULT NULL COMMENT '打印备货单人员',
  `erp_order_prepare_shipping_datetime` datetime DEFAULT NULL COMMENT '打印备货单时间',
  `erp_order_edit_person` varchar(50) DEFAULT NULL COMMENT 'erp order编辑人员',
  `erp_order_edit_datetime` datetime DEFAULT NULL COMMENT 'erp order 编辑时间',
  `order_before_cacel_global_status` int(5) DEFAULT NULL COMMENT '订单取消前的global status',
  `order_cacel_person` varchar(50) CHARACTER SET utf8 COLLATE utf8_german2_ci DEFAULT NULL COMMENT '订单取消人',
  `order_cacel_datetime` datetime DEFAULT NULL COMMENT '订单取消的时间',
  `order_out_stock_person` varchar(50) DEFAULT NULL COMMENT '订单缺货操作人',
  `order_out_stock_datetime` datetime DEFAULT NULL,
  `website_order_status` varchar(30) DEFAULT NULL COMMENT '网站订单状态，仅仅记录，不做任何处理',
  `order_purchase_datetime` datetime DEFAULT NULL COMMENT '订单已采购状态的时间',
  `order_first_sku` varchar(40) DEFAULT NULL COMMENT '订单中第一个产品的sku值',
  `order_send_delivery_email_datetime` datetime DEFAULT NULL COMMENT '订单发货后的通知查看邮件通知（2天后发送邮件）',
  `two_weak_get_order_shipping_datetime` datetime DEFAULT NULL COMMENT '发货2周后，抓取订单的货运状态的时间',
  `two_weak_get_order_shipping_status` int(5) DEFAULT NULL COMMENT '发货2周后，抓取订单的货运状态,1代表成功，2代表失败',
  `two_weak_get_order_shipping_info` text COMMENT '得到两周后货运的详细信息',
  `two_weak_get_order_shipping_short_info` varchar(255) DEFAULT NULL COMMENT '2周或25天后抓取货运状态的简短状态',
  `two_weak_send_email_status` int(5) DEFAULT '0' COMMENT '二周后，抓取了货运状态，对应的发送对应的邮件，默认为0，成功为1，失败为2',
  `two_weak_send_email_date_time` datetime DEFAULT NULL COMMENT '二周后，抓取了货运状态，对应的发送对应邮件的时间',
  `warehouse_no` int(10) DEFAULT '0' COMMENT '订单的仓库号',
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `increment_id` (`increment_id`),
  KEY `tracking_number` (`tracking_number`),
  KEY `increment_id_2` (`increment_id`),
  KEY `erp_global_status` (`erp_global_status`),
  KEY `erp_order_create_datetime` (`erp_order_create_datetime`),
  KEY `tracking_number_2` (`tracking_number`),
  KEY `customer_email` (`customer_email`),
  KEY `erp_order_source_code` (`erp_order_source_code`),
  KEY `erp_order_finance_person_datetime` (`erp_order_finance_person_datetime`),
  KEY `erp_order_create_datetime_2` (`erp_order_create_datetime`),
  KEY `order_first_sku` (`order_first_sku`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=63662

PARTITION BY  LINEAR HASH(order_id)
PARTITIONS 512;

会报错，需要把UNIQUE KEY `increment_id` (`increment_id`),去掉，关于这个限制，可以在程序中进行控制

由于是512个分区，时间好慢，复制数据过去也是很慢。

然后把数据插入过去：

Insert into sales_order_info_part select * from sales_order_info;

修改表名

alter table sales_order_info rename sales_order_info_history;
alter table sales_order_info_part rename sales_order_info;

完成。

其他：

sales_order_item_part:


PARTITION BY  LINEAR  HASH(item_id)
PARTITIONS 500;
Insert into sales_order_item_part select * from sales_order_item;
alter table sales_order_item rename sales_order_item_history;
alter table sales_order_item_part rename sales_order_item;




product_sku_part:

PARTITION BY  LINEAR  HASH(product_id)
PARTITIONS 500;
Insert into product_sku_part select * from product_sku;
alter table product_sku rename product_sku_history;
alter table product_sku_part rename product_sku;


product_spu_part:

PARTITION BY  LINEAR  HASH(spu_id)
PARTITIONS 500;
Insert into product_spu_part select * from product_spu;
alter table product_spu rename product_spu_history;
alter table product_spu_part rename product_spu;

分区后，在phpmyadmin里面会发现，总数不对

原来的表：显示行 0 - 29 (~62,696¹ 总计, 查询花费 0.0006 秒)

分区后的表：显示行 0 - 29 (~26,594¹ 总计, 查询花费 0.0012 秒)

但是在mysql上面运行 select * from sales_order_info_history 总数是没有问题的，这可能是phpmyadmin 老版本的一个bug

对比：

对于没有索引的字段，查询速度还是很快的。

其他参考：

将mysql非分区表转换为分区表 - http://blog.itpub.net/15480802/viewspace-689399/

mysql中的分区 - http://www.bkjia.com/Mysql/991216.html

分区文件放到不同的磁盘，

CREATE TABLE users (
uid INT UNSIGNED NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2 VALUES LESS THAN (9000000)
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);

每个分区都有自己独立的数据、索引文件的存放目录，与此同时，这些目录所在的物理磁盘分区可能也都是完全独立的，可以多大提高了磁盘IO吞吐量。