你写的每条SQL都是全表扫描吗

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 你写的每条SQL都是全表扫描吗?如果是,那MySQL可太感谢你了,每一次SQL执行都是在给MySQL上压力、上对抗。MySQL有苦难言:你不知道索引吗?你写的SQL索引都失效了不知道吗?慢查询不懂啊?建那么多索引干嘛呢。。。

你写的每条SQL都是全表扫描吗?如果是,那MySQL可太感谢你了,每一次SQL执行都是在给MySQL上压力、上对抗。MySQL有苦难言:你不知道索引吗?你写的SQL索引都失效了不知道吗?慢查询不懂啊?建那么多索引干嘛呢。。。

在这里插入图片描述

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表设计要尽可能满足数据库三大范式,帮助大家回顾下:

  1. 第一范式:数据库表中的每一列都是不可再分的属性,属性相近或相同的列应该合并

  2. 第二范式:满足第一范式的条件下,一个表只能描述一个对象。如果某些列经常出现数据重复,应该把这些列作为另一个表

  3. 第三范式:满足第二范式的条件下,表中的每一列都只能依赖于主键,即直接与主键相关。

我们在业务开发中遇到反第二范式的情况是最多的,例如以下订单明细表的设计,每一个订单明细都包含了重复的商品名称、商品单位、商品价格,这三个字段属于字段冗余存储。如果表的数据量级很大,那造成的冗余存储量是可想而知的,而且最要命的问题是如果要修改某一个商品名称,那所有的订单明细数据都要修改

在这里插入图片描述

我们可以遵循第三范式,把冗余的字段抽出一个新的商品表,当要查询订单明细时只需要把两表通过商品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 索引如何设计

面试官:在工作中,表索引你怎么设计的?

索引的设计有以下设计原则,大家在实际业务开发中应该尽量遵循这些原则,可以帮你避开不少坑。

  1. 经常进行order by排序、group by分组、join多表联结查询的字段应该建立索引。

  2. 经常在where子句中出现的字段应该建立索引。

  3. 尽量使用数据量小的字段建立索引。例如对于char(500)和char(10)两个字段类型来说,肯定是以后者进行索引匹配的速度更快。

  4. 如果需要建立索引的字段值比较长,可以使用值的部分前缀来建立索引。

    例如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

未完待续。。。

创作不易,不妨点赞、收藏、关注支持一下,各位的支持就是我创作的最大动力❤️

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7月前
|
SQL 算法 Oracle
哪些SQL语句会引起全表扫描
哪些SQL语句会引起全表扫描
|
SQL 缓存 NoSQL
ALL 代表这条 SQL 语句全表扫描了
ALL 代表这条 SQL 语句全表扫描了
94 0
|
SQL 关系型数据库 MySQL
这个大表走索引字段查询的 SQL 怎么就成全扫描了
这个大表走索引字段查询的 SQL 怎么就成全扫描了
这个大表走索引字段查询的 SQL 怎么就成全扫描了
|
SQL 关系型数据库 MySQL
PolarDB-X 1.0-SQL 手册-Hint-扫描全部/部分分库分表
本文适用于PolarDB-X 5.3 及以上版本,其他版本请参见PolarDB-X 5.2 HINT。
270 1
|
SQL 索引
|
SQL 关系型数据库 MySQL
SQL优化之避免全表扫描
在mysql5.6官方文档中关于full table scan的介绍如下: An operation that requires reading the entire contents of a table, rather than just selected portions using an index.
1892 0
|
SQL 安全 Linux
kali linux 网络渗透测试学习笔记(二)OWASP ZAP工具扫描SQL injection漏洞失败
按照惯例,利用OWASP ZAP工具扫描SQL injection漏洞时,应该很快就可以扫描出来,但是在笔者进行扫描的时候,却遇到了以下状况: 这说明了该工具根本就没能够扫描出SQL注入的漏洞,不知道该如何解决。
2238 0
|
SQL 存储 关系型数据库
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 11 章 索引_11.11. 只用索引的扫描
11.11. 只用索引的扫描 PostgreSQL中的所有索引都是二级索引,表示每一个索引都被存储在表的主数据区域(在PostgreSQL术语中被称为该表的堆)之外。这意味着在一次普通索引扫描中,每次取一行需要从索引和堆中取得数据。
1060 0
|
SQL 索引 存储
Sql Server 聚集索引扫描 Scan Direction的两种方式------FORWARD 和 BACKWARD
原文:Sql Server 聚集索引扫描 Scan Direction的两种方式------FORWARD 和 BACKWARD 最近发现一个分页查询存储过程中的的一个SQL语句,当聚集索引列的排序方式不同的时候,效率差别达到数十倍,让我感到非常吃惊由此引发出来分页查询的情况下对大表做Cluster...
1358 0
|
SQL 索引 存储
SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
原文:SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句 原文出处:http://www.cnblogs.com/wy123/p/5933734.html     先看常用的一种表结构设计方式:   那么可能会遇到一种典型的查询方式,主子表关联,查询子表中的某些(或者全部)Key点对应的Value,横向显示(也即以行的方式显示)   这种查询方式很明显的一个却显示多次对字表查询(暂时抛开索引)   相比这种查询方式很多人都遇到过,如果子表是配置信息之类的小表的话,问题不大,如果字表数据量较大,可能就会有影响了。
1186 0