Mysql优化总结

数据分片

为何要分片？
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