MySQL数据库

1 MySQL复制功能

1.1复制的作用

        基于语句的复制和基于行的复制两种。

    复制是让一台服务器的数据与其他服务器保持同步，通过复制可以让读操作指向备库来获得更好的读扩展。

• 数据分布
• 负载均衡
• 备份
• 高可用和故障切换
• MySQL升级测试

1.2复制如何进行工作

1. 在主库上把数据更改记录到二进制文件。
2. 备库将主库中的日志复制到自己的中继日志中。
3. 备库读取中继日志事件，将其放到备库数据之上。

1.3 复制的原理

1、基于语句的复制
在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。实现简单。主备可能存在不同，存在无法被正确复制的SQL。主数据库除了执行语句外，还可能依赖提前因素。例如函数、触发器和存储过程。

2、基于行的复制
把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持。好处是可以正确处理每一行

3、混合类型的复制
默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制

2 MySQL建立高性能索引与查询优化

2.1 建立索引

1 建立覆盖索引

利用覆盖索引查询数据，避免回表

2 前缀索引

对应varchar类型的字符，建立前缀索引，通常是以索引开始的部分字符，这样可以大大节约索引空间，提供索引效率

3 多列索引（联合索引）

当where后面跟多个条件时，可以考虑建多列索引，将选择性高的放在前面

2.2查询优化

1 优化排序

SELECT * FROM `order_assets` WHERE `asset_type` = 'device' ORDER BY `id` desc  limit 200000 ,10;

随着偏移量的增加，MySQL需要扫描大量需要丢弃的数据，优化策略是使用延迟关联，通过覆盖索引查询需要的主键

SELECT * FROM `order_assets` INNER JOIN (
SELECT id FROM `order_assets` WHERE `asset_type` = 'device' ORDER BY `id` desc  limit 200000 ,10) as x using(id);

2 慢SQL的原因

是否访问了太多不需要的数据行，或者列

扫描了过多的数据

2.4  数据库主从不一致

数据库主库和从库不一致，常见有这么几种优化方案：

（1）业务可以接受，系统不优化

（2）强制读主，高可用主库，用缓存提高读性能

（3）在cache里记录哪些记录发生过写请求，来路由读主还是读从

数据库主从不一致，怎么解？

2.5 分表后数据查询问题

     对于基于shardingkey的查询我们可以很简单的解决，对于非shardingkey的查询可以通过落双份数据和数仓、ES的方案来解决，当然，如果分表后数据量很小的话，建好索引，扫全表查询其实也不是什么问题

阿里的订单选择了三个column作为三个独立的sharding column，即：order_id，user_id，merchant_code。user_id和merchant_code就是买家ID和卖家ID，冗余3张全量表，其他查询用ES解决

分库分表后跨分片查询与Elastic Search - twoheads - 博客园