了解这些SQL优化技巧，面试时横着走

2024-03-10 05:04:11 阅读 0

来源平台：CSDN

大公司的面试越来越难，但无论从大公司到小公司，一个没有改变的重点就是对SQL优化经验的考察。说到数据库，首先是“谈谈你对SQL优化的见解？”。 SQL优化已经成为衡量程序员优秀程度的硬指标。甚至在各大厂商的工作职能上都有明确的标注。如果是你，你能在这个问题上打败面试官还是会被打败？

sql语句中的不等于_sql语句‖的用法_sql等于

有朋友问，SQL优化真的那么重要吗？如下图所示，SQL优化是提高系统性能（成本最低、优化效果最明显）的方式。如果你的团队在SQL优化方面表现出色，那无疑会对你整个大型系统的可用性产生质的飞跃，而且确实可以为你的老板节省很多钱。

sql语句中的不等于_sql语句‖的用法_sql等于

String result = "嗯，不错，";
 
if ("SQL优化经验足") {
    if ("熟悉事务锁") {
        if ("并发场景处理666") {
            if ("会打王者荣耀") {
                result += "明天入职" 
            }
        }
    }
} else {
    result += "先回去等消息吧";
} 
 
Logger.info("面试官：" + result );

别看它，上面是一个命题。

sql语句中的不等于_sql语句‖的用法_sql等于

好吧，让我们言归正传。首先，我对MySQL层优化一般遵循五个原则：

减少数据访问：设置合理的字段类型、启用压缩、通过索引访问等减少磁盘IO 返回数据少：只返回需要的字段和数据分页处理减少磁盘IO和网络IO 减少交互次数：批量DML操作、函数存储等减少数据连接数，降低服务器CPU开销：尽量减少数据库排序操作和全表查询，减少CPU内存占用，利用更多资源：使用表分区增加并行操作，最大化利用CPU资源。

总结SQL优化有三点：

要了解SQL优化的原理，首先要了解SQL的执行顺序：

陈述 - 语法顺序：

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>

语句-执行顺序：FROM

# 选择一张表，通过笛卡尔积将多张表数据转换为一张表。

在

# 过滤笛卡尔积虚表

加入

#指定join，用于on后向虚表添加数据。例如，左连接会将左表的剩余数据添加到虚拟表中。

在哪里

# 过滤上述虚拟表

通过...分组

＃团体

# 用于子句判断。写起来，这种类型的聚合函数是写在判断中的。

# 对分组结果进行聚合和过滤

# 返回的单列必须在group by子句中，聚合函数除外

# 重复数据删除

订购依据

＃种类

限制

SQL优化策略声明：以下SQL优化策略适用于数据量大的场景。如果数据量较小，则无需以此为标准，避免增加不必要的不必要的歧义。

1.避免不使用索引的场景

1、尽量避免在字段开头进行模糊查询，这会导致数据库引擎放弃索引而进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%陈%'

优化方法：尽量在字段后使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '陈%'

如果需求是前面使用模糊查询，

2、尽量避免使用in和not in，这会导致引擎进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3);

优化方法：如果是连续值，可以用代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3;

如果是子查询，可以用子查询代替。

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

3、尽量避免使用or，这会导致数据库引擎放弃索引而进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方法：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4、尽量避免判断空值，这会导致数据库引擎放弃索引而进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方法：可以给字段添加默认值0，并判断0值。如下：

SELECT * FROM t WHERE score = 0

5、尽量避免在where条件中等号左边进行表达式和函数操作，这会导致数据库引擎放弃索引而进行全表扫描。

表达式和函数运算可以移到等号的右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

6. 当数据量较大时，避免使用1=1的条件。通常，为了方便组装查询条件，我们会默认使用该条件，数据库引擎会放弃索引，进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1

优化方法：在组装SQL时使用代码进行判断。如果没有where条件，则去掉where。如果有where条件，则添加and。

7、查询条件不能使用or !=

使用索引列作为查询条件时，需要避免使用or!=等判断条件。如果业务需要，并且使用了不等号，则需要重新评估索引创建，避免在此字段建立索引，并在查询条件中替换为其他索引字段。

8、where条件仅包含复合索引的非前面列

如下：复合（联合）索引包含三列、、、三列，但SQL语句不包含索引前导列“”。根据MySQL联合索引的最左匹配原则，不会使用联合索引。

select col1 from table where key_part2=1 and key_part3=2

9、隐式类型转换导致索引无法使用

以下SQL语句无法正确运行索引，因为索引列类型是，但给定值是数值，这涉及到隐式类型转换。

select col1 from table where col_varchar=123;

10、order by条件必须和where中的条件一致，否则order by不会使用索引进行排序。

-- 不走age索引
SELECT * FROM t order by age;
 
-- 走age索引
SELECT * FROM t where age > 0 order by age;

对于上述语句，数据库处理顺序为：

当where条件中出现order by的字段时，会使用索引，而不是二次排序。更准确地说，当order by的字段使用执行计划中的索引时，不需要进行排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也同样有效。如group by、union等。

11.正确使用提示优化语句

MySQL 中可以使用 Hint 来指定优化器在执行过程中选择或忽略特定索引。一般来说，建议避免因版本变更导致表结构索引发生变化而使用提示，而是通过表收集更多的统计信息。但在某些情况下，指定hint可以消除其他索引的干扰，指定更好的执行计划。

USE INDEX 在查询语句中的表名后面添加 USE INDEX 以提供您希望 MySQL 引用的索引列表，以便 MySQL 将不再考虑其他可用索引。示例： col1 FROM table USE INDEX (, name)... INDEX 如果你只是想让 MySQL 忽略一个或多个索引，你可以使用 INDEX 作为提示。示例： col1 FROM table INDEX () ...FORCE INDEX 要强制 MySQL 使用特定索引，请在查询中使用 FORCE INDEX 作为提示。示例：col1 FROM 表 FORCE INDEX () ...

查询时，数据库系统会自动分析查询语句并选择最合适的索引。但很多时候，数据库系统的查询优化器可能并不总是使用最优索引。如果我们知道如何选择索引，我们可以使用FORCE INDEX 来强制查询使用指定的索引。

2、语句的其他优化

1. 避免*

首先，*操作在任何类型的数据库中都不是一个好的SQL编写习惯。

使用*删除所有列会阻止优化器完成索引覆盖扫描等优化，从而影响优化器对执行计划的选择，增加网络带宽消耗，并导致额外的I/O、内存和CPU消耗。。

建议建议业务实际需要的列数，并指定列名，而不是*。

2. 避免结果不确定的函数

专门针对主从复制等业务场景。由于原则上从库复制主库执行的语句，使用now()、rand()、()、()等结果不确定的函数很容易导致从库之间对应数据不一致。主库和从库。另外，具有不确定值的函数生成的SQL语句不能使用查询缓存。

3、查询多表时，小表在前，大表在后。

在MySQL中，执行from后的表关联查询是从左到右执行的（相反）。第一个表会涉及到全表扫描，所以把小表放在前面，先扫描小表。扫描速度更快、效率更高。高，扫描后续大表时，可能只扫描大表的前100行就满足返回条件。

例如：表1有50条数据，表2有30亿条数据。如果表2把整张桌子都扫完了，你很失望，那你先去吃饭吧？

4.使用表别名

在 SQL 语句中连接多个表时，请使用表别名并在每个列名前添加别名。这可以减少解析时间并减少由于列名不明确而导致的语法错误。

5. 将单词替换为where单词

避免使用using子句，因为只有在检索完所有记录后才会对结果集进行过滤，而where用于在聚合之前选择记录。如果可以通过where子句限制记录数量，则可以减少这种开销。中的条件一般用于过滤聚合函数。另外，条件应该写在where子句中。

where和的区别：where后面不能使用分组函数

6.调整Where子句中的连接顺序

MySQL从左到右、从上到下解析where子句。根据这个原则，过滤数据量大的条件应该放在前面，以尽快减少结果集。

3、增删改DML语句优化

1.批量插入数据

如果同时执行大量插入，建议使用多值语句（方法2）。这比使用单独的语句（方法 1）更快。一般来说，批量插入的效率相差好几倍。

方法一：

insert into T values(1,2); 
 
insert into T values(1,3); 
 
insert into T values(1,4);

方法二：

Insert into T values(1,2),(1,3),(1,4);

选择后一种方法有三个原因。

2.正确使用

适当的使用可以释放交易占用的资源，减少消耗。可以释放的资源如下：

3.避免重复查询更新数据

对于业务中经常出现的更新行、获取行变化信息的需求，MySQL不支持这样的语法，可以通过MySQL中的变量来实现。

例如，如果您更新了一行记录的时间戳，并且想要查询当前记录中存储的时间戳，则可以通过简单的方式实现：

Update t1 set time=now() where col1=1; 
 
Select time from t1 where id =1;

使用变量，可以重写如下：

Update t1 set time=now () where col1=1 and @now: = now (); 
 
Select @now;

两者都需要两次网络往返，但使用变量可以避免再次访问数据表。尤其是当t1表数据量较大时，后者比前者快很多。

4. 应该优先查询还是更新(,,)？

MySQL还允许更改语句调度的优先级，这可以让多个客户端的查询更好地配合，从而使单个客户端不会因为锁而长时间等待。更改优先级还可以确保更快地处理某些类型的查询。我们首先应该确定应用程序的类型，确定应用程序是基于查询的还是基于更新的，是保证查询效率还是更新效率，并决定是否优先查询或更新。下面提到的改变调度策略的方法主要针对只有表锁的存储引擎，例如MERGE、MERGE。对于存储引擎来说，语句的执行是由获取行锁的顺序决定的。 MySQL可用的默认调度策略总结如下：

1) 写操作优先于读操作。

2）对某个数据表的写操作一次只能发生一次，写请求按照到达的顺序进行处理。

3）可以同时对某个数据表进行多次读操作。 MySQL提供了几个语句修饰符，允许您修改其调度策略：

如果写操作是一个（低优先级）请求，那么系统不会认为它比读操作具有更高的优先级。在这种情况下，如果在写入器等待时第二个读取器到达，则允许第二个读取器在写入器之前插入。只有当没有其他读者时，写入者才可以开始操作。此调度修改可能会导致写入操作永远被阻止。

对于查询的（高优先级）关键字也是如此。它允许在挂起的写操作之前插入，即使写操作通常具有更高的优先级。另一个影响是高优先级的语句会先于普通语句执行，因为这些语句会被写操作阻塞。如果您希望默认情况下以低优先级处理所有支持选项的语句，请使用 --low-- 选项启动服务器。通过使用将语句提高到正常写入优先级，可以消除此选项对各个语句的影响。

4.查询条件优化

1、对于复杂的查询，可以使用中间临时表来临时存储数据；

2.优化group by语句

默认情况下，MySQL会对GROUP BY组中的所有值进行排序，例如“GROUP BY col1, col2, ....;” 查询方法与指定“ORDER BY col1, col2, ...;”相同在查询中。如果显式包含包含相同列的 ORDER BY 子句，MySQL 可以对其进行优化而不会减慢速度，但仍会执行排序。

因此，如果查询包含 GROUP BY 但您不想对分组值进行排序，则可以指定 ORDER BY NULL 来抑制排序。例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

3.优化join语句

在MySQL中，您可以使用子查询创建单列查询结果，然后在另一个查询中使用该结果作为过滤条件。使用子查询可以完成许多逻辑上需要多个步骤一次完成的 SQL 操作。它还可以避免事务或表锁，而且也很容易编写。然而，在某些情况下，子查询可以被更有效的连接（JOIN）替代。

示例：假设要检索所有没有订单记录的用户，可以使用以下查询来完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

连接（JOIN）..效率更高，因为MySQL不需要在内存中创建临时表来完成这个逻辑上的两步查询。

4.优化联合查询

MySQL 通过创建和填充临时表来执行联合查询。除非你确实想消除重复行，否则建议使用 union all。原因是如果没有关键字all，MySQL会在临时表中添加选项，这会导致整个临时表中数据的唯一性验证，这是相当昂贵的。

高效的：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
 
UNION ALL 
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

效率低下：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
 
UNION 
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

5、将复杂的SQL拆分成多个小SQL，避免大事务

6. 代替使用

当删除全表记录时，使用该语句的操作会记录在undo块中，删除的记录也会被记录。当确认需要删除整个表时，会产生大量的undo数据块，占用量很大。这时，没有很好的效率也占用了大量的资源。

使用替换，不会记录可恢复的信息，并且数据无法恢复。因此，use操作的资源占用很少，时间也极快。另外，使用可以回收表的水位将自增字段值重置为零。

7、采用合理的分页方式，提高分页效率

采用合理的分页方式，提高分页效率。针对显示等分页需求，适当的分页方式可以提高分页效率。

情况1：

select * from t where thread_id = 10000 and deleted = 0 
   order by gmt_create asc limit 0, 15;

上面的例子根据过滤条件一次性提取所有字段，排序并返回。数据访问开销=索引IO+所有索引记录结果对应的表数据IO。因此，越往后转，这种写法的执行效率就会越差，执行时间也会越长，尤其是当表数据量很大的时候。

适用场景：适用于中间结果集很小（小于10000行）或者查询条件复杂（指多个不同查询字段或多表连接）的情况。

案例2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0
   order by gmt_create asc limit 0, 15) a, t 
      where a.id = t.id;

上面的例子必须满足表t的主键是id列，并且有一个覆盖索引键：(, , )。首先利用覆盖索引根据过滤条件提取主键id进行排序，然后进行join操作提取其他字段。数据访问开销=索引IO+索引分页结果对应的表数据IO（示例中为15行）。所以，这种写法每次翻页消耗的资源和时间基本和翻第一页一样。

适用场景：适用于查询和排序字段（即where子句和order by子句涉及的字段）有对应的覆盖索引，且中间结果集较大的情况。

5.建表优化

1、在表中创建索引，优先考虑where和order by使用的字段。

2. 尽量使用数字字段（如性别，男：1，女：2）。如果字段只包含数字信息，尽量不要将其设计为字符字段。这会降低查询和连接的性能，并增加存储开销。

这是因为引擎在处理查询和连接时会逐一比较字符串中的每个字符，而对于数字类型只需比较一次就足够了。

3、查询数据量大的表会导致查询速度慢。主要原因是扫描线太多。这时就可以通过程序进行分段、分页查询，循环遍历，将结果合并显示。需要查询的数据如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4.使用/代替char/nchar

尽量用/代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间。其次，对于查询来说，在相对较小的领域内搜索效率明显更高。

不要以为NULL不需要空间，例如：char(100)类型。创建字段时，空间是固定的。无论是否插入值（也包括NULL），都会占用100个字符的空间。如果是变长字段，null不占用空间。

照片背后的故事（自娱自乐角）

这是姜玉树先生拍摄的《小店》照片

位于四川凉山

妈妈暂时很忙

让你女儿帮忙照看商店

突然开始下雪了

在这间只有一堵墙的小店里

女孩搓着手

在欣赏美丽风景的同时

上一篇：《王者荣耀》是如何从C++代码变成游戏的？

下一篇： C语言使用char转换大小写字母详解

排行榜

259℃1hustoj比赛作业里面的时间是什么含义，OI排名跟普通排名有何区别？
214℃2Ubuntu18.04搭建LAMP环境完成帝国CMS安装
212℃3LAMP环境如何搭建多站点
210℃4 linux 下基本文件操作
197℃5hustoj脚本把OJ装在哪里了？可以卸载么？
187℃6hustoj如何限制未登录用户访问？
170℃7hustoj比赛后题目看不见了怎么弄？
160℃8Apache如何做301重定向

图文教程

技术标签