一、一些常见的SQL实践
（1）负向条件查询不能使用索引
select * from order where status!=0 and stauts!=1
not in/not exists都不是好习惯
可以优化为in查询：
select * from order where status in(2,3)
（2）前导模糊查询不能使用索引
select * from order where desc like ‘%XX’
而非前导模糊查询则可以：
select * from order where desc like ‘XX%’
（3）数据区分度不大的字段不宜使用索引
select * from user where sex=1
原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。
经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。
（4）在属性上进行计算不能命中索引
select * from order where YEAR(date) < = ‘2017’
即使date上建立了索引，也会全表扫描，可优化为值计算：
select * from order where date < = CURDATE()
或者：
select * from order where date < = ‘2017-01-01’
二、并非周知的SQL实践
（5）如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心
select * from user where uid=?
select * from user where login_name=?
原因：
B-Tree索引的时间复杂度是O(log(n))
Hash索引的时间复杂度是O(1)
（6）允许为null的列，查询有潜在大坑
单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集
select * from user where name != ‘shenjian’
如果name允许为null，索引不存储null值，结果集中不会包含这些记录。
所以，请使用not null约束以及默认值。
（7）复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致
用户中心建立了(login_name, passwd)的复合索引
select * from user where login_name=? and passwd=?
select * from user where passwd=? and login_name=?
都能够命中索引
select * from user where login_name=?
也能命中索引，满足复合索引最左前缀
select * from user where passwd=?
不能命中索引，不满足复合索引最左前缀
（8）使用ENUM而不是字符串
ENUM保存的是TINYINT，别在枚举中搞一些“中国”“北京”“技术部”这样的字符串，字符串空间又大，效率又低。
三、小众但有用的SQL实践
（9）如果明确知道只有一条结果返回，limit 1能够提高效率
select * from user where login_name=?
可以优化为：
select * from user where login_name=? limit 1
原因：
你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动
（10）把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果
select * from order where date < = CURDATE()
这不是一个好的SQL实践，应该优化为：
curDate=date(‘Y−m−d′);
res = mysql_query(
‘select * from order where date < = $curDate’);
原因：
释放了数据库的CPU
多次调用，传入的SQL相同，才可以利用查询缓存
（11）强制类型转换会全表扫描
select * from user where phone=13800001234
你以为会命中phone索引么？大错特错了，这个语句究竟要怎么改？
末了，再加一条，不要使用select *（潜台词，文章的SQL都不合格 =_=），只返回需要的列，能够大大的节省数据传输量，与数据库的内存使用量哟。
=======================================
举例，业务场景，用户表，表结构为：
t_user(
uid primary key,
login_name unique,
passwd,
login_time,
age,
…
);
聚集索引(clustered index)：聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引，一般用primary key来约束。
举例：t_user场景中，uid上的索引。
非聚集索引(non-clustered index)：它并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符row-locator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过这个指针，可以找到行数据。
举例，查找年轻MM的业务需求：
select uid from t_user where age > 18 and age < 26;
age上建立的索引，就是非聚集索引。
联合索引：多个字段上建立的索引，能够加速复核查询条件的检索
举例，登录业务需求：
select uid, login_time from t_user where
login_name=? and passwd=?
可以建立(login_name, passwd)的联合索引。
联合索引能够满足最左侧查询需求，例如(a, b, c)三列的联合索引，能够加速a | (a, b) | (a, b, c) 三组查询需求。
这也就是为何不建立(passwd, login_name)这样联合索引的原因，业务上几乎没有passwd的单条件查询需求，而有很多login_name的单条件查询需求。
提问：
select uid, login_time from t_user where
passwd=? and login_name=?
能否命中(login_name, passwd)这个联合索引？
回答：可以，最左侧查询需求，并不是指SQL语句的写法必须满足索引的顺序（这是很多朋友的误解）
索引覆盖：被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。
举例，登录业务需求：
select uid, login_time from t_user where
login_name=? and passwd=?
可以建立(login_name, passwd, login_time)的联合索引，由于login_time已经建立在索引中了，被查询的uid和login_time就不用去row上获取数据了，从而加速查询。
末了多说一句，登录这个业务场景，login_name具备唯一性，建这个单列索引就好。
作业：
假设订单有三种状态：0已下单，1已支付，2已完成
业务需求，查询未完成的订单，哪个SQL更快呢？
select * from order where status!=2
select * from order where status=0 or status=1
select * from order where status IN (0,1)
select * from order where status=0
union
select * from order where stauts=1
============================================
假设订单业务表结构为：
order(oid, date, uid, status, money, time, …)
其中：
oid，订单ID，主键
date，下单日期，有普通索引，管理后台经常按照date查询
uid，用户ID，有普通索引，用户查询自己订单
status，订单状态，有普通索引，管理后台经常按照status查询
money/time，订单金额/时间，被查询字段，无索引
…
假设订单有三种状态：0已下单，1已支付，2已完成
业务需求，查询未完成的订单，哪个SQL更快呢？
select * from order where status!=2
select * from order where status=0 or status=1
select * from order where status IN (0,1)
select * from order where status=0
union all
select * from order where status=1
结论：方案1最慢，方案2，3，4都能命中索引
但是…
一：union all 肯定是能够命中索引的
select * from order where status=0
union all
select * from order where status=1
说明：
直接告诉MySQL怎么做，MySQL耗费的CPU最少
程序员并不经常这么写SQL(union all)
二：简单的in能够命中索引
select * from order where status in (0,1)
说明：
让MySQL思考，查询优化耗费的cpu比union all多，但可以忽略不计
程序员最常这么写SQL(in)，这个例子，最建议这么写
三：对于or，新版的MySQL能够命中索引
select * from order where status=0 or status=1
说明：
让MySQL思考，查询优化耗费的cpu比in多，别把负担交给MySQL
不建议程序员频繁用or，不是所有的or都命中索引
对于老版本的MySQL，建议查询分析下
四、对于!=，负向查询肯定不能命中索引
select * from order where status!=2
说明：
全表扫描，效率最低，所有方案中最慢
禁止使用负向查询
五、其他方案
select * from order where status < 2
这个具体的例子中，确实快，但是：
这个例子只举了3个状态，实际业务不止这3个状态，并且状态的“值”正好满足偏序关系，万一是查其他状态呢，SQL不宜依赖于枚举的值，方案不通用
这个SQL可读性差，可理解性差，可维护性差，强烈不推荐
六、作业
这样的查询能够命中索引么？
select * from order where uid in (
select uid from order where status=0
)
select * from order where status in (0, 1) order by date desc
select * from order where status=0 or date <= CURDATE()
==========================================
一，为什么要冗余数据
互联网数据量很大的业务场景，往往数据库需要进行水平切分来降低单库数据量。
水平切分会有一个patition key，通过patition key的查询能够直接定位到库，但是非patition key上的查询可能就需要扫描多个库了。
此时常见的架构设计方案，是使用数据冗余这种反范式设计来满足分库后不同维度的查询需求。
例如：订单业务，对用户和商家都有订单查询需求：
Order(oid, info_detail);
T(buyer_id, seller_id, oid);
如果用buyer_id来分库，seller_id的查询就需要扫描多库。
如果用seller_id来分库，buyer_id的查询就需要扫描多库。
此时可以使用数据冗余来分别满足buyer_id和seller_id上的查询需求：
T1(buyer_id, seller_id, oid)
T2(seller_id, buyer_id, oid)
同一个数据，冗余两份，一份以buyer_id来分库，满足买家的查询需求；一份以seller_id来分库，满足卖家的查询需求。
如何实施数据的冗余，是今天将要讨论的内容。
二，服务同步双写
顾名思义，由服务层同步写冗余数据，如上图1-4流程：
业务方调用服务，新增数据
服务先插入T1数据
服务再插入T2数据
服务返回业务方新增数据成功
优点：
不复杂，服务层由单次写，变两次写
数据一致性相对较高（因为双写成功才返回）
缺点：
请求的处理时间增加（要插入两次，时间加倍）
数据仍可能不一致，例如第二步写入T1完成后服务重启，则数据不会写入T2
如果系统对处理时间比较敏感，引出常用的第二种方案。
三，服务异步双写
数据的双写并不再由服务来完成，服务层异步发出一个消息，通过消息总线发送给一个专门的数据复制服务来写入冗余数据，如上图1-6流程：
业务方调用服务，新增数据
服务先插入T1数据
服务向消息总线发送一个异步消息（发出即可，不用等返回，通常很快就能完成）
服务返回业务方新增数据成功
消息总线将消息投递给数据同步中心
数据同步中心插入T2数据
优点：
请求处理时间短（只插入1次）
缺点：
系统的复杂性增加了，多引入了一个组件（消息总线）和一个服务（专用的数据复制服务）
因为返回业务线数据插入成功时，数据还不一定插入到T2中，因此数据有一个不一致时间窗口（这个窗口很短，最终是一致的）
在消息总线丢失消息时，冗余表数据会不一致
不管是服务同步双写，还是服务异步双写，服务都需要关注“冗余数据”带来的复杂性。如果想解除“数据冗余”对系统的耦合，引出常用的第三种方案。
四，线下异步双写
为了屏蔽“冗余数据”对服务带来的复杂性，数据的双写不再由服务层来完成，而是由线下的一个服务或者任务来完成，如上图1-6流程：
业务方调用服务，新增数据
服务先插入T1数据
服务返回业务方新增数据成功
数据会被写入到数据库的log中
线下服务或者任务读取数据库的log
线下服务或者任务插入T2数据
优点：
数据双写与业务完全解耦
请求处理时间短（只插入1次）
缺点：
返回业务线数据插入成功时，数据还不一定插入到T2中，因此数据有一个不一致时间窗口（这个窗口很短，最终是一致的）
数据的一致性依赖于线下服务或者任务的可靠性
五，总结
互联网数据量大的业务场景，常常:
使用水平切分来降低单库数据量
使用数据冗余的反范式设计来满足不同维度的查询需求
使用服务同步双写法能够很容易的实现数据冗余
为了降低时延，可以优化为服务异步双写法
为了屏蔽“冗余数据”对服务带来的复杂性，可以优化为线下异步双写法
============================
一、双主保证高可用
MySQL数据库集群常使用一主多从，主从同步，读写分离的方式来扩充数据库的读性能，保证读库的高可用，但此时写库仍然是单点。
在一个MySQL数据库集群中可以设置两个主库，并设置双向同步，以冗余写库的方式来保证写库的高可用。
二、并发引发不一致
数据冗余会引发数据的一致性问题，因为数据的同步有一个时间差，并发的写入可能导致数据同步失败，引起数据丢失：
如上图所述，假设主库使用了auto increment来作为自增主键：
两个MySQL-master设置双向同步可以用来保证主库的高可用
数据库中现存的记录主键是1，2，3
主库1插入了一条记录，主键为4，并向主库2同步数据
数据同步成功之前，主库2也插入了一条记录，由于数据还没有同步成功，插入记录生成的主键也为4，并向主库1也同步数据
主库1和主库2都插入了主键为4的记录，双主同步失败，数据不一致
三、相同步长免冲突
能否保证两个主库生成的主键一定不冲突呢？
回答：
设置不同的初始值
设置相同的增长步长
就能够做到。
如上图所示：
两个MySQL-master设置双向同步可以用来保证主库的高可用
库1的自增初始值是1，库2的自增初始值是2，增长步长都为2
库1中插入数据主键为1/3/5/7，库2中插入数据主键为2/4/6/8，不冲突
数据双向同步后，两个主库会包含全部数据
如上图所示，两个主库最终都将包含1/2/3/4/5/6/7/8所有数据，即使有一个主库挂了，另一个主库也能够保证写库的高可用。
四、上游生成ID避冲突
换一个思路，为何要依赖于数据库的自增ID，来保证数据的一致性呢？
完全可以由业务上游，使用统一的ID生成器，来保证ID的生成不冲突：
如上图所示，调用方插入数据时，带入全局唯一ID，而不依赖于数据库的auto increment，也能解决这个问题。
至于如何生成全局唯一，趋势递增的ID，参见文章《分布式ID生成算法》。
五、消除双写不治本
使用auto increment两个主库并发写可能导致数据不一致，只使用一个主库提供服务，另一个主库作为shadow-master，只用来保证高可用，能否避免一致性问题呢？
如上图所示：
两个MySQL-master设置双向同步可以用来保证主库的高可用
只有主库1对外提供写入服务
两个主库设置相同的虚IP，在主库1挂掉或者网络异常的时候，虚IP自动漂移，shadow master顶上，保证主库的高可用
这个切换由于虚IP没有变化，所以切换过程对调用方是透明的，但在极限的情况下，也可能引发数据的不一致：
如上图所示：
两个MySQL-master设置双向同步可以用来保证主库的高可用，并设置了相同的虚IP
网络抖动前，主库1对上游提供写入服务，插入了一条记录，主键为4，并向shadow master主库2同步数据
突然主库1网络异常，keepalived检测出异常后，实施虚IP漂移，主库2开始提供服务
在主键4的数据同步成功之前，主库2插入了一条记录，也生成了主键为4的记录，结果导致数据不一致
六、内网DNS探测
虚IP漂移，双主同步延时导致的数据不一致，本质上，需要在双主同步完数据之后，再实施虚IP偏移，使用内网DNS探测，可以实现shadow master延时高可用：
使用内网域名连接数据库，例如：db.58daojia.org
主库1和主库2设置双主同步，不使用相同虚IP，而是分别使用ip1和ip2
一开始db.58daojia.org指向ip1
用一个小脚本轮询探测ip1主库的连通性
当ip1主库发生异常时，小脚本delay一个x秒的延时，等待主库2同步完数据之后，再将db.58daojia.org解析到ip2
程序以内网域名进行重连，即可自动连接到ip2主库，并保证了数据的一致性
七、总结
主库高可用，主库一致性，一些小技巧：
双主同步是一种常见的保证写库高可用的方式
设置相同步长，不同初始值，可以避免auto increment生成冲突主键
不依赖数据库，业务调用方自己生成全局唯一ID是一个好方法
shadow master保证写库高可用，只有一个写库提供服务，并不能完全保证一致性
内网DNS探测，可以实现在主库1出现问题后，延时一个时间，再进行主库切换，以保证数据一致性
315
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
