赞
踩
笔者上家公司是某电商公司,每天的订单量达到三百万,每个订单对应一个运单,每个运单对应约10~20条物流轨迹,平均每天产生的轨迹数量就达到三千万至六千万,如此庞大的数量,单表无法支撑,单库也难以为继,所以我们做了以下优化。
1.分库
当前库使用MYSQL,历史库使用HBASE
2.分表
对运单号取模,分成16个表来存放物流轨迹,同个运单号的物流轨迹存放到同个表。
表中只存放一个月的数据,超过一个月的数据归档到HBASE中,这样单表的数据量大约保持在五千万左右。
3.关键字段添加索引
3.1 轨迹主表
对运单号字段waybill_num和承运商编码字段carrier_code建联合索引,轨迹状态字段status、创建时间字段create_time和修改时间字段update_time建普通索引。
KEY `idx_waybill_num_carrier_code` (`waybill_num`,`carrier_code`)
KEY `idx_status` (`status`)
KEY `idx_create_time` (`create_time`)
轨迹主表归档时,查询出已签收或拒收,并且是一个月前的数量,
select * from waybill where status in(1,2) and create_time<DATE_SUB(CURDATE(), INTERVAL 30 DAY)
3.2 轨迹详情表
比如运单号字段waybill_num和承运商编码字段carrier_code建联合索引
KEY `idx_waybill_num_carrier_code` (`waybill_num`,`carrier_code`)
1)使用运单号和承运商编码去查询运单轨迹
select waybill_num,carrier_code,status,remark,create_time
from waybill_detail_xx
where waybill_num='123456' and carrier_code='shunfeng' and is_deleted=0
2)数据归档
select b.* from waybill a, waybill_detail_xx b
where a.status in(1,2) and a.create_time<DATE_SUB(CURDATE(), INTERVAL 30 DAY) and a.waybill_num=b.waybill_num and a.carrier_code=b.carrier_code
4.常用的SQL优化方法
4.1 联合索引要符合左前缀规则
KEY `idx_waybill_num_carrier_code` (`waybill_num`,`carrier_code`)
select * from waybill where waybill_num='12345' and carrier_code='shunfeng'; //索引有效
select * from waybill where waybill_num='12345'; //索引有效
select * from waybill where carrier_code='shunfeng'; //索引失效
4.2 索引字段作为查询字段时,不能添加数据类型转换
SELECT * from users where DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-04-17'; 索引失效
4.3 索引字段作为查询字段时,参数类型要一致
SELECT * FROM users FORCE WHERE age = 11; 索引有效
SELECT * FROM users FORCE WHERE age = '11'; 索引失效
4.4 模糊查询,%符号不能放在前面,只能放在中间或最后,否则索引失效
SELECT * FROM users FORCE WHERE first_name like '%John'; 索引失效
SELECT * FROM users FORCE WHERE first_name like 'Jo%hn'; 索引有效
SELECT * FROM users FORCE WHERE first_name like 'John%'; 索引有效
4.5 ID索引和其他索引同时使用时,MYSQL只会默认优先使用ID索引
要分析是通过ID索引扫描的数据量少,还是其他索引扫描的数据量少,如果是其他索引扫描的数据量少,则可以去掉ID查询字段,或者强制走其他索引。
SELECT * FROM users FORCE INDEX (first_name) WHERE id > 0 and first_name = 'John';
4.6 使用explain关键字可以模拟优化器执行SQL查询语句
详情请看笔者之前写的另外一篇文章
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。