若有收获,请记得分享和转发哦
当有人问你如何对数据库进行优化时,很多人第一反应想到的就是SQL优化,如何创建索引,如何改写SQL,他们把数据库优化与SQL优化划上了等号。
当然这不能算是完全错误的回答,只不过思考的角度稍微片面了些,太“程序员思维”化了,没有站在更高层次来思考回答。那今天我们就将视角拔高,站在架构的角度来聊聊这一问题,数据库优化可以从哪些维度入手?
正如上图所示,数据库优化可以从架构优化,硬件优化,DB优化,SQL优化四个维度入手。
此上而下,位置越靠前优化越明显,对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。
接下来我们再看看每种优化该如何实施。
架构优化
一般来说在高并发的场景下对架构层进行优化其效果最为明显,常见的优化手段有:分布式缓存,读写分离,分库分表等,每种优化手段又适用于不同的应用场景。
一主多从,读写分离,主动同步,是一种常见的数据库架构优化手段。
一般来说当你的应用是读多写少,数据库扛不住读压力的时候,采用读写分离,通过增加从库数量可以线性提升系统读性能。
主库,提供数据库写服务;从库,提供数据库读能力;主从之间,通过binlog同步数据。
当准备实施读写分离时,为了保证高可用,需要实现故障的自动转移,主从架构会有潜在主从不一致性问题。
水平切分
水平切分,也是一种常见的数据库架构优化手段。
当你的应用业务数据量很大,单库容量成为性能瓶颈后,采用水平切分,可以降低数据库单库容量,提升数据库写性能。
SQL优化
SQL优化很容易理解,就是通过给查询字段添加索引或者改写SQL提高其执行效率,一般而言,SQL编写有以下几个通用的技巧:
合理使用索引
索引少了查询慢;索引多了占用空间大,执行增删改语句的时候需要动态维护索引,影响性能 选择率高(重复值少)且被where频繁引用需要建立B树索引;一般join列需要建立索引;复杂文档类型查询采用全文索引效率更好;索引的建立要在查询和DML性能之间取得平衡;复合索引创建时要注意基于非前导列查询的情况
使用UNION ALL替代UNION
UNION ALL的执行效率比UNION高,UNION执行时需要排重;UNION需要对数据进行排序
SQL优化实战
这里为大家准备了一套SQL优化的综合实战,一步一步带你走一遍完整SQL优化的过程。
在执行优化之前我们需要先认识一下原始表及待优化的SQL。
初步优化SQL
alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
查看优化后的执行时间
SQL优化小结
这里给大家总结一下SQL优化的套路:
查看执行计划
explain sql
如果有告警信息,查看告警信息
show warnings;
查看SQL涉及的表结构和索引信息
根据执行计划,思考可能的优化点
按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
查看优化后的执行时间和执行计划
如果优化效果不明显,重复第四步操作
小结
我们今天分别从架构优化、硬件优化、DB优化、SQL优化四个角度探讨了如何实施优化,提升数据库性能。但是大家还是要记住一句话,数据库系统没有银弹, 要让适合的系统,做合适的事情。