面试官:请你说下对于一张很大的表应该如何做查询优化

Hello,大家好,我是阿粉,今天是周六,你是在放假还是跟阿粉一样在加班呢?今天的课题是跟大伙聊聊数据表的优化问题,这个问题小伙伴们在工作应该会遇到而且在面试中还经常会问到。

我们都知道当 MySQL 中的表很大的时候,我们的查询就会变得很慢,即使是在进行分页查询,使用 count(*) 计算总条数的时候也是很慢,MySQL 的数据一般达到千万级别的时候就会变得很慢,往往这个时候我们就需要进行优化了,那么优化主要从哪几个方面呢?请听阿粉慢慢道来。

控制数据量

对于线上大表进行查询的时候,我们控制允许查询的时间范围,常规的查询其实也不会使用很多数据,我们一般我们限定一个月的时间限制,如果要查询多个月的数据,就进行分月查询,不支持跨多月进行查询,保证系统的稳定。所以一般我们在创建相关业务表的时候都会加上创建时间字段,在数据量很大的情况下,我们就可以根据创建时间字段进行限定查询。

分库分表

从业务上进行分库分表,我们可以根据业务本身的特定将数据进行分库分表的存储,在查询的时候根据对应的字段或者服务从程序上分库分表查询,也可以使用中间件实现分库分表的查询,这样程序就不用做任何修改,直接实现分库分表查询动作。

分区

除了上面提到的分库分表还是对数据表进行分区,关于分区的内容可以看阿粉之前写的文章面试必问的 MySQL 知识点,你还有哪些没准备好,赶紧收藏脑图 后半部分有提到分区的内容,感兴趣的小伙伴可以去看下,阿粉这里就不重复说明了。分区还可以进行水平分区或者垂直分区,不同的策略对应不同的使用场景,根据需求可以自行选择。

索引优化

对于查询使用较多的字段我们必须设置索引,而且这个索引我们最好再刚开始的时候就考虑到,避免在后续表数据特别大的情况在进行创建索引,这样很不友好这种操作会有线上服务产生影响。其次索引也不是越多越好,创建索引是需要付出代价的,需要有针对性的创建才行,不能盲目创建。

主键索引一定要存在,联合索引的顺序要和查询字段的顺序保持一致,不要使用外键来维护外键关系,通过程序来控制。对于唯一值的列可以增加唯一索引。

SQL 优化

我们在进行查询的时候尽量指定需要查询的列名,不采用select * ,在 SQL 语句中不进行运算操作,在where 条件中避免使用不等于进行查询,容易造成全表扫描。一般情况下对于连续数值判断,采用between 不适用in。在MySQL 中尽量避免使用触发器和函数,相应的功能可以移到程序中进行处理。特别的在进行类型比较的时候,尽量采用相同类型的数据进行比较,不用整型和字符串类型进行比较。

总结

大表优化一直是一个高频的问题,而且在很多场景下都会存在,一个系统经过几年的使用必定会产生相关的大表,这种情况下我们就需要根据实际情况进行优化了。毕竟优化好了,我们 CRUD 起来才会更顺畅!

参考:https://segmentfault.com/a/1190000006158186

写在最后

最后邀请你加入我们的知识星球,这里有 1800+ 优秀的人与你一起进步,如果你是小白那你是稳赚了,很多业内经验和干货分享给你;如果你是大佬,那可以进来我们一起交流分享你的经验,说不定日后我们还可以有合作,给你的人生多一个可能。

Java Geek Tech wechat
欢迎订阅 Java 极客技术,这里分享关于 Java 的一切。