本文主要是介绍你写的每条SQL都是全表扫描吗,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
你写的每条SQL都是全表扫描吗?如果是,那MySQL可太感谢你了,每一次SQL执行都是在给MySQL上压力、上对抗。MySQL有苦难言:你不知道索引吗?你写的SQL索引都失效了不知道吗?慢查询不懂啊?建那么多索引干嘛呢。。。
文章目录
- 1. 慢查询
- 2. SQL优化
- 2.1 表设计优化
- 2.2 SQL语句优化
- 2.3 索引如何设计
- 未完待续。。。
1. 慢查询
面试官:知道MySQL慢查询吗?
MySQL的慢查询日志可以记录执行时间超过阈值的SQL查询语句,所以我们可以利用该日志查找出哪些SQL语句执行效率差,从而对SQL语句进行优化。
MySQL5.7以上版本可以通过SET命令来开启慢查询日志。
SET GLOBAL slow_query_log=ON;SET GLOBAL long_query_time=2;SET SESSION long_query_time=2;
开启完慢查询日志,我们找到该日志的位置,打开文件即可查询慢查询的SQL。
# 查询慢查询日志文件位置SHOW VARIABLES LIKE '%slow_query_log_file%';
打开DESKTOP-ALU4BOC-slow.log文件,找到慢查询SQL为:select sleep(11)
。
D:\MySQL\bin\mysqld, Version: 5.5.40 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 220828 21:40:28
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 11.004454 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use mysql;
SET timestamp=1661694028;
select sleep(11);
2. SQL优化
2.1 表设计优化
面试官:在工作中你怎么优化SQL的?
业务开发中涉及数据库的第一步是表设计,要优化SQL就要从第一步开始做起。
MySQL表设计要尽可能满足数据库三大范式,帮助大家回顾下:
-
第一范式:数据库表中的每一列都是不可再分的属性,属性相近或相同的列应该合并。
-
第二范式:满足第一范式的条件下,一个表只能描述一个对象。如果某些列经常出现数据重复,应该把这些列作为另一个表。
-
第三范式:满足第二范式的条件下,表中的每一列都只能依赖于主键,即直接与主键相关。
我们在业务开发中遇到反第二范式的情况是最多的,例如以下订单明细表的设计,每一个订单明细都包含了重复的商品名称、商品单位、商品价格,这三个字段属于字段冗余存储。如果表的数据量级很大,那造成的冗余存储量是可想而知的,而且最要命的问题是如果要修改某一个商品名称,那所有的订单明细数据都要修改。
我们可以遵循第三范式,把冗余的字段抽出一个新的商品表,当要查询订单明细时只需要把两表通过商品id进行连接即可。
面试官:遵循第二范式就一定最优?
遵循第二范式的表设计不一定是最优的情况,还是那句话,要根据实际的业务场景权衡利弊。
虽然把冗余数据抽离出去了,但却增加了表的数量,也意味着查询数据时表之间的join连接操作也会变多。而join连接的性能是比较低的,有可能join操作会成为数据库性能的瓶颈。
2.2 SQL语句优化
面试官:还有呢?
SQL优化除了做好表设计的优化工作,还需要对SQL语句进行优化。而SQL查询语句的优化主要从覆盖索引、避免索引失效、减少不必要的查询三个方面入手。
一、从覆盖索引的角度。
order by排序的字段要尽量覆盖索引。如果使用非索引字段进行排序,MySQL会进行额外的文件排序,将查询结果根据非索引列在磁盘中再排序一次。当我们使用explain关键字分析SQL时会发现Extra会出现Using filesort
。
group by分组要尽量覆盖索引。如果使用非索引字段进行分组,MySQL只能进行全表扫描后建立临时表才能得出分组结果。
另外我们可以使用explain关键字来分析SQL语句的效率,查看SQL语句是否覆盖索引。
二、从避免索引失效的角度。
关于如何避免索引失效,大家可以阅读我出版的《JavaGetOffer》专栏关于【MySQL索引】的文章。
三、从减少不必要的查询的角度。
如果只需要查询部分列,尽量不要使用select *
查询,防止造成不必要的资源消耗、占用过多的网络带宽。
2.3 索引如何设计
面试官:在工作中,表索引你怎么设计的?
索引的设计有以下设计原则,大家在实际业务开发中应该尽量遵循这些原则,可以帮你避开不少坑。
-
经常进行order by排序、group by分组、join多表联结查询的字段应该建立索引。
-
经常在where子句中出现的字段应该建立索引。
-
尽量使用数据量小的字段建立索引。例如对于char(500)和char(10)两个字段类型来说,肯定是以后者进行索引匹配的速度更快。
-
如果需要建立索引的字段值比较长,可以使用值的部分前缀来建立索引。
例如varchar类型的name字段,我们需要根据前三个字符来建立前缀索引,可以使用以下SQL命令:
ALTER TABLE example_table ADD INDEX index_name (name(3))
。
面试官:那索引建立越多,查询效率就越高吗?
另外大家记住一点,索引不是建立越多越好。合理设计的索引确实能大大提高SQL效率,但每建立一个字段索引,MySQL就要为该索引多维护一棵B-Tree
,越多的索引会造成表更新效率变得低下。
🌱以【面试官面试】形式覆盖Java程序员所需掌握的Java核心知识、面试重点,本博客收录在我开源的《Java学习指南》中,会一直完善下去,希望收到大家的 ⭐ Star ⭐支持,这是我创作的最大动力: https://github.com/hdgaadd/JavaGetOffer
未完待续。。。
创作不易,不妨点赞、收藏、关注支持一下,各位的支持就是我创作的最大动力❤️
这篇关于你写的每条SQL都是全表扫描吗的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!