SQL优化篇 - MySQL查询select语句优化

小果冻啦

已于 2024-08-14 10:20:30 修改

阅读量917

点赞数 15

文章标签： sql mysql 数据库

于 2024-07-24 15:35:41 首次发布

本文链接：https://blog.csdn.net/2301_76868495/article/details/140643014

版权

1. 将 find_in_set() 替换为 in

2. 将 JSON_UNQUOTE() 替换为 ->> 符号

前言：

目前，无论是运维，开发，测试，几乎都有接触到SQL，会使用SQL，会优化SQL，还是一项很重要的技能，多一份技能，就多一份责任，工作报告也多一项描述，薪资也会慢慢往上涨，美滋滋！！！

一、SQL语句优化

（一）SQL关键字执行顺序

想要优化SQL语句，先熟悉每个关键字执行的顺序；我们可以根据顺序选择性的得到多种方案优化

执行顺序：

FROM子句
ON子句
JOIN子句
WHERE子句
GROUP BY子句
HAVING子句
SELECT子句
DISTINCT子句
ORDER BY子句
LIMIT子句或者TOP子句

（二）避免索引失效

索引失效会带来一些可能存在的隐患：

1. 索引失效行锁会上升至表锁，其他需要访问该表需要等待释放，并发性会降低，可能还会发生死锁的情况

2. 查询性能下降，索引失效必定会全表扫描，增加查询时间

使用索引时，要注意避免索引失效：

1. 复合索引：
   1）必须遵循最左缀原则是用，否则会导致索引失效
   2）比如索引（a,b,c）查询顺序则是--》where a=1 and b=2 and c=3
3) 不要跨列或无序使用(最佳左前缀)；
4) 尽量使用全索引匹配，也就是说，你建立几个索引，就使用几个索引
   5）使用范围条件（如> < between like）之后自身和右侧的索引字段会消失

2. 不要在索引上进行任何操作(计算、函数、类型转换、操作符)，否则索引失效
1) 优化可以使用 between 等操作

3. 索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效(针对大多数情况)
复合索引中如果有>，则自身和右侧索引全部失效。

4. 模糊查询：like不要以’%'开头，否则索引失效；但是可以优化使用"x%"情况

5. 数据类型不一致：查询数据类型与索引字段的数据不一致，也会导致索引失效

6. 尽量不要使用类型转换（显示、隐式），否则索引失效
   1）示例：select * from table_name where varchar_column = 111;
   2）解决方法：显式转换查询条件的数据类型：select * from table_name where varchar_column = '111';

8. 尽量不要使用or，否则索引可能失效；or操作只要其中一条满足就会执行，会让自身索引和左右两侧的索引都失效
   在多列上使用OR条件，如果其中一个字段没有索引，整个查询会导致索引失效

9. 更新频繁的字段
   在频繁更新的列上建立索引，可能会导致索引的维护成本高，从而影响查询性能
   索引可以增加查询效率，降低写入效率
   每一次更新，都会更新一次索引

（三）explain执行计划分析

explain关键字的用处解释：

1. id

id的值越大，表示该SELECT子句或操作的优先级越高，越先被执行
如果id相同，执行顺序由上而下

2. select_type：查询类型

simple：简单查询
primary：包含子查询的主查询(最外层)
subquery：包含子查询的主查询(非最外层)
derived：衍生查询(用到了临时表)
union：union之后的表称之为union表
union result：告诉我们，哪些表之间使用了union查询

3. type关键字的使用说明：索引类型

system：源表只有一条数据
const：只能查到一条数据的SQL，对Primary key或unique索引类型有效的
eq_ref：唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0），并且查询结果和数据条数必须一致
ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（可以0，可以1，可以多）
range：检索指定范围的行 ,where后面是一个范围查询(between, >, <, >=, in)
index：查询全部索引中的数据(扫描整个索引)
ALL：查询全部源表中的数据(暴力扫描全表)

4. possible_keys和key

possible_keys：可能用到的索引。是一种预测，不准。了解一下就好。
key：指的是实际使用的索引（重要参考指标）

   注意：如果possible_key/key是NULL，则说明没用索引
5. key_len
   索引的长度，用于判断复合索引是否被完全使用，在不损失精确性的情况下，长度越短越好。
6. ref
   指明当前表所参照的字段，显示使用哪个列或常数与 key 一起从表中选择行
   与type中的ref值区分，在type中，ref只是type类型的一种选项值
7. rows
   被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)
   显示 MySQL 认为它执行查询时必须检查的行数，不代表实际执行的行数，MySQL可能会因为多种原因而检查比预期更多或更少的行

8. extra：解决查询的详细信息（至关重要的参考指标）
1）using filesort：

使用 ORDER BY 无法通过索引来完成排序操作，因此它需要对返回的数据进行额外的排序(即使字段是索引或复合索引下，也无法命中，走全表)
使用了 GROUP BY ，分组不使用索引字段，MySQL将不得不使用文件排序（filesort）
索引覆盖扫描：查询通过索引覆盖扫描来检索数据，但ORDER BY或GROUP BY的列不是索引，或者索引的顺序不满足排序需求

   针对单索引的情况。当出现了这个词，表示你当前的SQL性能消耗较大。表示进行了一次“额外”的排序。常见于order by语句
   2）using temporary
   表示当前SQL用到了临时表，当前的SQL性能消耗较大，常见于排序和分组查询
   由于排序没有走索引、使用union、子查询连接查询、使用某些视图等原因，因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小

   3. using index ：
   SQL性能提升，只利用索引获取数据，不需要回源表查询。
   只要使用到的列，全部出现在索引中，就是索引覆盖
   4. using where：
   1）全表扫描后过滤
   2）索引部分匹配，该索引只能帮助过滤掉部分行时，然后通过WHERE子句来进一步过滤这些行
   3）需要回表查询，表示既在索引中进行了查询，又回到了源表进行了查询

   5）impossible where(了解)：当where子句永远为False的时候，会出现impossible where

mysql> show create table class.m_class;
mysql> show status like "%last_query_cost%";
mysql> show index from class.students;
mysql> explain查询计划

6）Using sort_union(…), Using union(…), Using intersect(…)
       这些函数说明如何为index_merge联接类型合并索引扫描

   7）Backward index scan 是反向扫描，反向扫描的性能也是可以的
       Backward Index Scan是一种基于索引的查询优化技术，它可以在倒序查询时，利用索引的逆向指针来实现快速定位，从而提高查询性能。
       当我们使用DESC排序时，系统会自动选择这种算法，但前提是必须有合适的索引存在。如果没有索引或者使用升序排列，则无法享受Backward Index Scan带来的好处

       出现using filesort、using temporary, Using union,Range checked for each record字眼，说明影响系统的性能需要优化！！！