数据分片
为何要分片?
1.单表数据过大 ,聚集索引树层树过高,导致查询性能不够。
2.写入性能达到瓶颈
数据如何分片?
1.按照业务ID分片。适用于写入性能达到瓶颈,需要根据某个业务ID将写入压力分散到不同分片中
2.按时间维度分片。适用于单表数据过大,但是写入压力不高且查询与写入均能维持在近期的某个范围内。
数据分片后如何查询?
1.短期方案。查询条件正好包括用于分片的字段,可以直接去指定的分片查询。涉及到排序的可以采用归并排序的方式。
2.长期方案。查询条件并不包括用于分片的字段,这时需要全部分片去查询,这时性能时常不能满足需求。这时需要做数据异构或者订单归档。
归档表可以采用不同的分片策略。例如订单表在下单时采用按userId分片,用户下单、查询时都只会去请求一个分片。订单订单支付完毕后可以同步到另一组订单表(订单历史表),订单历史表可以采用时间维度进行分片。订单生产、退款(根据订单号查询)绝大多数也只会去请求历史表的最近的一个分片。
归档也可以采用不用的存储中间件,例如可以采用es、hbase等
数据归档如何进行?
1.定时器同步,例如定时查询已经支付完毕的订单,将其同步到归档表。这里不是简单的拿到数据发个MQ就同步,需要考虑同步时消息不丢失、幂等、限流。
2.利用canal处理binlog进行归档。这里需要考虑的就是归档后主键不一致的问题,需要保证归档前的表之间关联是基于业务编号而不是主键。例如订单表和订单用户关联表,如果两表的关联是订单编号,则可以利用该方式归档,如果是订单ID,则不能。
SQL 优化
users表
字段 | 名称 | 是否建索引 |
---|---|---|
id | 主键 | 是 |
no | 编号 | 是 |
name | 名称 | 无 |
a | a | (a,b,c) 复合索引 |
b | b | (a,b,c) 复合索引 |
c | c | (a,b,c) 复合索引 |
Explain需要关注的参数?
1.type
type=ref | 查询用到索引等值查询 | 例如select * from users where id = 1 |
type=range | 查询用到索引范围查询, | 例如select * from users where id > 10 或者 select * from users where no like ‘123%’ |
type=index | 查询需要用到索引全扫描 | 例如select id from users order by id desc或者select id from users where no like ‘%123’(这里没用select * 是因为当索引扫描行数较大时,select * 如果用索引全扫描,还需要多次回表,所以mysql会认为这时全表扫描更快) |
type=ALL | 查询用到全表扫描 | 例如 select * from users where no like ‘%123’ (相对在no索引上扫描值后再回表,全表扫描少了回表,mysql认为这样全表扫描更快) 或者 select * from users where name = ‘123’ |
type=const | 指定主键查询 | select * from users where id = 1 |
type=ref_eq | 和type=ref的区别是前者使用的是唯一索引 |
2.extra
Using where | 引擎层返回数据到服务层,服务层会根据where条件过滤一次。 |
Using index | 覆盖索引,不回表 |
Using index Condition | 索引下推,会将原本需要在服务层根据where条件过滤的操作下推给引擎层操作。 select * from users where a=1 and c=1 |
3.Filtered 抽样统计出返回行数占总扫描行数的比例
4.rows 抽样统计出需要扫描的行数
5.ref type为ref时才有,取值有const, func。
其他的太简单,就不说了
如何优化SQL?
1.建表时,根据业务设计好索引。比如复合索引,区分度高的排在前面。区分度低字段,非必要不建。
2.写SQL时,尽量查询能用上索引,能用上覆盖索引减少回表,尽量保证排序能用上索引。
3.业务上唯一的字段比如订单编号、用户编号,在SQL结尾可以加上limit 1
4.join 语句,保证扫描行数小的当驱动表,关联被驱动表的字段保证用上索引,并且量大时可以打开MRR和BKA