根据公司数据库实际情况,订单表有可能会比预想中扩张速度快,这里可能需要预先准备下优化方案,传统方案是分表或者分库,不过目前最好的方案是使用mysql的表分区来优化。不过需要注意的是在表分区建立后mysql查询缓存会失效,那么可以说暂时分表带来的好处在于更新、删除以及锁处理的时间会减少,但是如果查询并非针对表分区字段进行,那么查询的时间由于查询缓存失效反而会增加,这点需要取舍。
第一步:由于表分区必须在表建立的时候创建规则,而已经存在的没有创建过表分区规则的表需要重新做导入处理。方法如下:
#这里使用HASH表分区,mysql会根据HASH字段来自动分配数据到不同的表分区,这种情况适用于没有表分区规则但是有需要分表来进行查询优化的情况。这里根据id字段hash规则创建2个表分区 CREATE TABLE `creater_bak` ( `id` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY HASH(id) PARTITIONS 2
创建完成后开始导入原表数据:
insert into creater_bak select * from creater;
导入以后的新表数据就是分布在不同的2个表分区中了。
如果数据量非常大,觉得预设的表分区数量太少,那么可以新增表分区,mysql会自动重新分配:
#这里新增8个表分区,加上新建表时候的2个,一共10个表分区了 ALTER TABLE `creater_bak` ADD PARTITION PARTITIONS 8;
最后修改表名为原表名即可。
PS:下面是使用RANGE形式表分区,其中一些注意点HASH表分区也一样要注意:
1.如果使用RANGE形式进行表分区,必须设定规则,例如:
CREATE TABLE `creater_bak` ( `id` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (500), PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN MAXVALUE )
2.如果想修改有规则的表分区,注意只能新增,不要随意删除,这里删除表分区会造成该表分区内部数据也一起被删除掉,千万注意。另外如果设定了MAXVALUE那么是不能新增的,虽然删除MAXVALUE那条表分区后可以新增,但是依然注意删除的MAXVALUE分区是否有数据,如果有则不能随意删除,最好的办法依然是重建一张新表,表在创建时候重新制定规则后把旧表导入新表,这样能保证不会丢失数据。虽然最好不要删除分区,但是依然下面介绍如何删除表分区以及新增表分区:
#删除上面的MAXVALUE规则表分区(如果该表分区有数据,请勿随便使用此操作) ALTER TABLE `creater_bak` drop PARTITION p2; #新增规则表分区,注意按规则步长来新增,否则会报错,这里步长为500 ALTER TABLE `creater_bak` add PARTITION(PARTITION p2 VALUES LESS THAN (1500)) ALTER TABLE `creater_bak` add PARTITION(PARTITION p3 VALUES LESS THAN MAXVALUE)
最后使用下面的语句可以查看分区搜索情况:
EXPLAIN PARTITIONS select * from `creater_bak` b1 where b1.`id`=11
最后附上官方中文文档:
相关推荐
当数据库数据量涨到一定数量时,性能就成为我们不能不关注的问题,如何优化呢? 常用的方式不外乎那么几种: 1、分表,即把一个很大的表达数据分到几个表中,这样每个表数据都不多。 优点:提高并发量,减小锁的...
4. 尽量控制单表数据量的大小,建议控制在 500 万以内。 5. 谨慎使用 MySQL 分区表 6.尽量做到冷热数据分离,减小表的宽度 7. 禁止在表中建立预留字段 8. 禁止在数据库中存储图片,文件等大的二进制数据 9. 禁止在线上...
在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了MySQL的分区机制。把数据按照时间进行分区。 分区类型 Range分区:最为常用,基于属于一个...
MySQL分区表概述 我们经常遇到一张表里面保存了上亿甚至过十亿的记录,这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里。所以只能是启用一个或多个带...
MySQL分区表概述 随着MySQL越来越流行,Mysql里面的保存的数据也越来越大。在日常的工作中,我们经常遇到一张表里面保存了上亿甚至过十亿的记录。这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个...
可以通过使用MySQL分区表来提高查询性能。分区表将数据分成多个分区,可以根据查询条件只扫描必要的分区,减少查询所需的数据量。 **73. 如何使用MySQL事件调度器?** MySQL事件调度器是一种用于在特定时间或间隔...
我们介绍了查询优化技术的各个方面,包括索引、查询语句、表结构等基本优化方法以及高级优化技术如查询缓存、数据库分区、使用覆盖索引和使用压缩技术等。通过合理应用这些优化技术,可以提高数据库的响应速度、降低...
01-MySQL优化大的思路.wmv 02-Awk简洁入门.wmv 03-观察服务器周期性变化.wmv 04-观察MySQL进程状态.wmv 05-列选取原则.wmv 06-多列索引生效规则.wmv 07-多列索引实验.wmv 08-商城多列索引实验.wmv 09-聚簇索引概念....
多列索引,覆盖索引,大量数据分页,索引优化,主从复制,索引碎片,分区
5.4. mysql_fix_privilege_tables:升级MySQL系统表 5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL...
在这次MySQL的实战中,我的数据库已经做了4个分片,分布在不同的4台机器上,每台机器上的数据量有1.7亿(1.7*4=6.8亿),占用空间260G(260*4=1040G),这次迁移的目的就是删除掉一些历史记录,减轻数据库压力,有人...
当数据库数据量涨到一定数量时,性能就成为我们不能不关注的问题,如何优化呢? 常用的方式不外乎那么几种:1、分表,即把一个很大的表达数据分到几个表中,这样每个表数据都不多。优点:提高并发量,减小锁的粒度...
5.4. mysql_fix_privilege_tables:升级MySQL系统表 5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL...
如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用...
5.4. mysql_fix_privilege_tables:升级MySQL系统表 5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL...
5.4. mysql_fix_privilege_tables:升级MySQL系统表 5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA ...
5.4. mysql_fix_privilege_tables:升级MySQL系统表 5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL...
需求:随着数据量的增加单表已经不能很好的支持业务,千万级别数据查询缓慢 Mysql数据优化方案: 方案一:使用myisam进行水平分表优化 方案二:使用mysql分区优化 一:Myisam水平分区 1、创建水平分表 user_1: -- ...
5.4. mysql_fix_privilege_tables:升级MySQL系统表 5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA ...