相亲网站怎么做的,网页微信二维码怎么扫,潍坊百度seo,移动建站公司数据库 mysql 单表数据量达到千万、亿级#xff0c;可以通过分表与表分区提升服务性能。 现有表accounting#xff0c;4亿条数据#xff0c;数据访问方式为insert与select#xff0c;select为按月访问#xff0c;为减少数据与程序变更#xff0c;采用表分区的方式。 数据…数据库 mysql 单表数据量达到千万、亿级可以通过分表与表分区提升服务性能。 现有表accounting4亿条数据数据访问方式为insert与selectselect为按月访问为减少数据与程序变更采用表分区的方式。 数据表create语句 CREATE TABLE accounting ( date datetime NOT NULL DEFAULT 0000-00-00 00:00:00, nas varchar(16) NOT NULL DEFAULT , uid varchar(20) NOT NULL DEFAULT , terminal varchar(20) DEFAULT NULL, client_ip varchar(16) NOT NULL DEFAULT , type varchar(20) DEFAULT NULL, service varchar(20) DEFAULT NULL, priv_lvl int(2) DEFAULT NULL, cmd varchar(255) DEFAULT NULL, elapsed_time int(6) DEFAULT NULL, bytes_in int(10) DEFAULT NULL, bytes_out int(10) DEFAULT NULL, id bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id, date), 按月分表date需要为primary key KEY date_index (date), KEY acct_index (uid), KEY nas_index (nas), KEY client_index (client_ip), KEY nas_date (date,nas)) ENGINEMyISAM AUTO_INCREMENT1 DEFAULT CHARSETlatin1 PARTITION BY RANGE (to_days(date)) ( PARTITION p201409 VALUES LESS THAN (to_days(2014-10-01)),PARTITION p201410 VALUES LESS THAN (to_days(2014-11-01)), PARTITION p201411 VALUES LESS THAN (to_days(2014-12-01)), PARTITION p201412 VALUES LESS THAN (to_days(2015-01-01)), PARTITION p201501 VALUES LESS THAN (to_days(2015-02-01)), PARTITION p201502 VALUES LESS THAN (to_days(2015-03-01)), PARTITION p201503 VALUES LESS THAN (to_days(2015-04-01)), PARTITION p201504 VALUES LESS THAN (to_days(2015-05-01)), PARTITION p201505 VALUES LESS THAN (to_days(2015-06-01)), PARTITION p201506 VALUES LESS THAN (to_days(2015-07-01)), PARTITION p201507 VALUES LESS THAN (to_days(2015-08-01)), PARTITION p201508 VALUES LESS THAN (to_days(2015-09-01)), PARTITION p201509 VALUES LESS THAN (to_days(2015-10-01)), PARTITION p201510 VALUES LESS THAN (to_days(2015-11-01)), PARTITION p201511 VALUES LESS THAN (to_days(2015-12-01)), PARTITION p201512 VALUES LESS THAN (to_days(2016-01-01)), PARTITION p201601 VALUES LESS THAN (to_days(2016-02-01)), PARTITION p201602 VALUES LESS THAN (to_days(2016-03-01)), PARTITION p201603 VALUES LESS THAN (to_days(2016-04-01)), PARTITION p201604 VALUES LESS THAN (to_days(2016-05-01)), PARTITION p201605 VALUES LESS THAN (to_days(2016-06-01)), PARTITION p201606 VALUES LESS THAN (to_days(2016-07-01)), PARTITION p201607 VALUES LESS THAN (to_days(2016-08-01)), PARTITION p201608 VALUES LESS THAN (to_days(2016-09-01)), PARTITION p201609 VALUES LESS THAN (to_days(2016-10-01)), PARTITION p201610 VALUES LESS THAN (to_days(2016-11-01)), PARTITION p201611 VALUES LESS THAN (to_days(2016-12-01)), PARTITION p201612 VALUES LESS THAN (to_days(2017-01-01)), PARTITION p201701 VALUES LESS THAN (to_days(2017-02-01)), PARTITION p201702 VALUES LESS THAN (to_days(2017-03-01)), PARTITION p201703 VALUES LESS THAN (to_days(2017-04-01)), PARTITION p201704 VALUES LESS THAN (to_days(2017-05-01)), PARTITION p201705 VALUES LESS THAN (to_days(2017-06-01)), PARTITION p201706 VALUES LESS THAN (to_days(2017-07-01)), PARTITION p201707 VALUES LESS THAN (to_days(2017-08-01)), PARTITION p201708 VALUES LESS THAN (to_days(2017-09-01)), PARTITION p201709 VALUES LESS THAN (to_days(2017-10-01)), PARTITION p201710 VALUES LESS THAN (to_days(2017-11-01)), PARTITION p201711 VALUES LESS THAN (to_days(2017-12-01)), PARTITION p201712 VALUES LESS THAN (to_days(2018-01-01)), PARTITION p201801 VALUES LESS THAN (to_days(2018-02-01)), PARTITION p201802 VALUES LESS THAN (to_days(2018-03-01)), PARTITION p201803 VALUES LESS THAN (to_days(2018-04-01)), PARTITION p201804 VALUES LESS THAN (to_days(2018-05-01)), PARTITION p201805 VALUES LESS THAN (to_days(2018-06-01)), PARTITION p201806 VALUES LESS THAN (to_days(2018-07-01)), PARTITION p201807 VALUES LESS THAN (to_days(2018-08-01)), PARTITION p201808 VALUES LESS THAN (to_days(2018-09-01)), PARTITION p201809 VALUES LESS THAN (to_days(2018-10-01)), PARTITION p201810 VALUES LESS THAN (to_days(2018-11-01)), PARTITION p201811 VALUES LESS THAN (to_days(2018-12-01)), PARTITION p201812 VALUES LESS THAN (to_days(2019-01-01)), PARTITION p2019 VALUES LESS THAN (MAXVALUE) ); 为了使用分区功能进行查询的语句需与创建分区方式的一致 错误explain select * from accounting where date2014-09-25 15:01:59; 发现访问方法为 ALL效率最低索引等也是NULL 正确explain select count(*) from accounting where date date(2014-09-26); rows代表将要扫描的条数如过选择了某个月的条件那么将扫描具体月份的MYD。此时如果where中再有条件column有索引type将变为ref,或者 explain select * from accounting where date date(2015-05-01 01:23:34); ((1L, SIMPLE, accounting, ref, date_index,nas_date, date_index, 8, const, 1L, ),) 照猫画虎欢迎批评转载于:https://www.cnblogs.com/gengwudaxia/p/5961367.html
