sql优化之:数据库索引创建原则,or/in/union与索引优化,聚集索引/非聚集索引/联合索引/索引覆盖,MySQL冗余数据的三种方案,MySQL双主一致性架构优化(来源:架构师之路)

数据库 waitig 539℃ 百度已收录 0评论

一、一些常见的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(Ymd);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出现问题后,延时一个时间,再进行主库切换,以保证数据一致性


点赞 (0)分享 (0)