MYSQL系列-SQL查询

站长

2023年07月06日 11:21 · 阅读数 39

前面已经介绍了MYSQL的索引和锁机制，本文主要讲述MYSQL查询/更新语句该怎么写

建索引的原则

前面已经有章节讲述索引优化使用,本章节略有重复，主要讲述建立索引原则

最左前缀匹配

指在使用复合索引（即由多个列组成的索引）进行查询时，只有按照索引中最左边的列开始依次匹配，才能充分利用索引的优势。比如有一个复合索引（a, b, c），那么在查询时，只有按照以下方式进行查询，才能充分利用该索引：

WHERE a = 'value_a'
WHERE a = 'value_a' AND b = 'value_b'
WHERE a = 'value_a' AND b = 'value_b' AND c = 'value_c'

又比如下面查询是用不到索引

WHERE b = 'value_b' AND c = 'value_c'

注意不要重复建立索引，有了复合索引（a, b, c），就不用再建立(a)、(a,b)索引

覆盖索引，避免回表

参考覆盖索引

选择区分度高列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0

=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

编写SQL技巧

基本技巧

查询时禁止使用*

使用select * 除了写起来较为简单，其他方面都不友好

如果新加了字段，代码不改动可能会报错，不利于灰度升级
返回不必要的字段，增加了网络开销和内存占用
分析成本变高，分析器解析时需要查询表上*对应哪些字段

不建议使用like左模糊和全模糊查询

%xxx、%xxx% 会导致索引失效，2c接口尽量不要使用此查询条件管理台等低频调用可以，否则就需要采用其他机制来查询，会增加额外硬件成本

查询时尽量不要对字段做空值判断

select * from xxx where yyy is null;
select * from xxx where yyy not is null;

当出现基于字段做空值判断的情况时，会导致索引失效，因为判断null的情况不会走索引，因此切记要避免这样的情况一般在设计字段结构的时候，请使用not null来定义字段，同时如果想为空的字段，可以设计一个0、""这类空字符代替

禁止在条件查询`=`前对字段做任何运算

select * from zz_users where user_id * 2 = 8;
select * from zz_users where trim(user_name) = "熊猫";

也是不走索引

!=、!<>、not in、not like、or...要慎用

也会导致索引失效 or可以使用union all来代替

联合索引查询确保字段的顺序性

遵循最左匹配原则

建议明确返回一条数据的语句使用limit 1

加上limit 1关键字后，当程序匹配到一条数据时就会停止扫描，如果不加的情况下会将所有数据都扫描一次。

多表查询优化

多表查询尽量不要关联太多表

主要原因如下：

数据量会随表数量呈直线性增长，数据量越大检索效率越低。
当关联的表数量过多时，无法控制好索引的匹配，涉及的表越多，索引不可控风险越大。
表越多，SQL逻辑越复杂，维护改造成本变大

多表查询时一定要以小驱大

以小驱大即是指用小的数据集去驱动大的数据集，说简单一点就是先查小表，再用小表的结果去大表中检索数据，其实在MySQL的优化器也会有驱动表的优化，当执行多表联查时，MySQL的关联算法为Nest Loop Join，该算法会依照驱动表的结果集作为循环基础数据，然后通过该结果集中一条条数据，作为过滤条件去下一个表中查询数据，最后合并结果得到最终数据集，MySQL优化器选择驱动表的逻辑如下：

如果指定了连接条件，满足查询条件的小数据表作为驱动表。
如果未指定连接条件，数据总行数少的表作为驱动表。
被驱动表字段尽量加上索引

//JOIN查询逻辑待继续细化

业务侧优化

必要情况下可以强制指定索引

在表中存在多个索引时，有些复杂SQL的情况下，或者在存储过程中，必要时可强制指定某条查询语句走某个索引，因为MySQL优化器面对存储过程、复杂SQL时并没有那么智能，有时可能选择的索引并不是最好的，这时我们可以通过force index

select * from t1 force index(a) where a = "a";

这样就能够100%强制这条SQL走某个索引查询数据

尽量将大事务拆分为小事务执行

一个事务在执行时，如果其中包含了写操作，会先获取锁再执行，直到事务结束后MySQL才会释放锁。

而一个事务占有锁之后，会导致其他要操作相同数据的事务被阻塞，如果当一个事务比较大时，会导致一部分数据的锁定周期较长，在高并发情况下会引起大量事务出现阻塞，从而最终拖垮整个MySQL系统。

show status like 'innodb_log_waits';查看是否有大事务由于redo_log_buffer不足，而在等待写入日志。

解决方案是将大事务改成小事务

从业务设计层面减少大量数据返回的情况

大量返回数据就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现，因此如果项目中存在这类业务，一定要记住拆分掉它，比如分批返回给客户端。

分批查询的方式也被称之为增量查询，每次基于上次返回数据的界限，再一次读取一批数据返回给客户端，这也就是经典的分页场景，通过分页的思想能够提升单次查询的速度，以及避免大数据量带来的一系列后患问题。

尽量避免深分页的情况出现

如下：

select xx,xx,xx from t1 limit 100000,10;

可以改成

select xx,xx,xx from t1 where id in (select id from t1 limit 100000,10);

如果字段有序并且连续，可以直接通过有序字段来判断

客户端的一些操作可以批量化完成

一些连续插入更新操作，可以采用适中事务批量完成

其他场景

避免频繁创建、销毁临时表

MySQL中的一些查询操作会产生临时表，主要包括以下情况：

排序操作：如果一个查询中包含ORDER BY子句，但是索引不能完全满足排序的条件，MySQL就会使用一个临时表来进行排序操作。
分组操作：如果一个查询中包含GROUP BY子句，MySQL会使用一个临时表来存储分组后的结果集，然后再进行聚合操作。
连接操作：如果一个查询中包含JOIN子句，MySQL会根据连接条件将两个表连接在一起，然后将结果保存到一个临时表中。
子查询操作：如果一个查询中包含子查询，MySQL会先执行子查询，然后将子查询的结果保存到一个临时表中，再进行外部查询操作。

慢查询优化-Explain

是MySQL自带的一个执行分析工具，可使用于select、insert、update、delete、repleace等语句上，需要使用时只需在SQL语句前加上一个explain关键字即可，然后MySQL会对应语句的执行计划列出

mysql> explain delete from t2 where a='a' and b='a' and c='c';
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
|  1 | DELETE      | t2    | NULL       | range | uniq_a_b_c    | uniq_a_b_c | 276     | const,const,const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
1 row in set (0.01 sec)

id

这是执行计划的ID值，一条SQL语句可能会出现多步执行计划，所以会出现多个ID值，这个值越大，表示执行的优先级越高，同时还会出现四种情况：

ID相同：当出现多个ID相同的执行计划时，从上往下挨个执行。
ID不同时：按照ID值从大到小依次执行。
ID有相同又有不同：先从大到小依次执行，碰到相同ID时从上往下执行。
ID为空：ID=null时，会放在最后执行。

select_type

当前执行的select语句其具体的查询类型:

SIMPLE：简单的select查询语句，不包含union、子查询语句。
PRIMARY：union或子查询语句中，最外层的主select语句。
SUBQUEPY：包含在主select语句中的第一个子查询，如select ... xx = (select ...)。
DERIVED：派生表，指包含在from中的子查询语句，如select ... from (select ...)。
DEPENDENT SUBQUEPY：复杂SQL中的第一个select子查询（依赖于外部查询的结果集）。
UNCACHEABLE SUBQUERY：不缓存结果集的子查询语句。
UNION：多条语句通过union组成的查询中，第二个以及更后面的select语句。
UNION RESULT：union的结果集。
DEPENDENT UNION：含义同上，但是基于外部查询的结果集来查询的。
UNCACHEABLE UNION：含义同上，但查询出的结果集不会加入缓存。
MATERIALIZED：采用物化的方式执行的包含派生表的查询语句。

table

表示当前这个执行计划是基于哪张表执行的，这里会写出表名，但有时候也不一定是物理磁盘中存在的表名，还有可能出现如下格式：

<derivenN>：基于id=N的查询结果集，进一步检索数据。
<unionM,N>：会出现在查询类型为UNION RESULT的计划中，表示结果由id=M,N...的查询组成。
<subqueryN>：基于id=N的子查询结果，进一步进行数据检索。
<tableName>：基于磁盘中已创建的某张表查询。

一句话总结就是：这个字段会写明，当前的这个执行计划会基于哪个数据集查询，有可能是物理表、有可能是子查询的结果、也有可能是其他查询生成的派生表。

partitions

用来显示分区,该列的值表示检索数据的分区

type

字段表示当前语句执行的类型:

all：全表扫描，基于表中所有的数据，逐行扫描并过滤符合条件的数据。
index：全索引扫描，和全表扫描类似，但这个是把索引树遍历一次，会比全表扫描要快。
range：基于索引字段进行范围查询，如between、<、>、in....等操作时出现的情况。
index_subquery：和上面含义相同，区别：这个是基于非主键、唯一索引字段进行in操作。
unique_subquery：执行基于主键索引字段，进行in操作的子查询语句会出现的情况。
index_merge：多条件查询时，组合使用多个索引来检索数据的情况。
ref_or_null：基于次级(非主键)索引做条件查询时，该索引字段允许为null出现的情况。
fulltext：基于全文索引字段，进行查询时出现的情况。
ref：基于非主键或唯一索引字段查找数据时，会出现的情况。
eq_ref：连表查询时，基于主键、唯一索引字段匹配数据的情况，会出现多次索引查找。
const：通过索引一趟查找后就能获取到数据，基于唯一、主键索引字段查询数据时的情况。
system：表中只有一行数据，这是const的一种特例。
null：表中没有数据，无需经过任何数据检索，直接返回结果。

访问数据的方式，性能从好到坏依次为：

完整的性能排序：null → system → const → eq_ref → ref → fulltext → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → all
常见的性能排序：system → const → eq_ref → ref → fulltext → range → index → all

一般在做索引优化时，一般都会要求最好优化到ref级别，至少也要到range级别，也就是最少也要基于次级索引来检索数据，不允许出现index、all这类全扫描的形式。

possible_keys

显示当前执行计划，在执行过程中可能会用到哪些索引来检索数据，但要注意的一点是：可能会用到并不代表一定会用，在某些情况下，就算有索引可以使用，MySQL也有可能放弃走索引查询。

key

前面的possible_keys字段表示可能会用到的索引，而key这个字段则会显示具体使用的索引，一般情况下都会从possible_keys的值中，综合评判出一个性能最好的索引来进行查询，但也有两种情况会出现key=null的这个场景：

possible_keys有值，key为空：出现这种情况多半是由于表中数据不多，因此MySQL会放弃索引，选择走全表查询，也有可能是因为SQL导致索引失效。
possible_keys、key都为空：表示当前表中未建立索引、或查询语句中未使用索引字段检索数据。

默认情况下，possible_keys有值时都会从中选取一个索引，但这个选择的工作是由MySQL优化器自己决定的，如果你想让查询语句执行时走固定的索引，则可以通过force index、ignore index的方式强制指定。

key_len

表示对应的执行计划在执行时，使用到的索引字段长度，一般情况下都为索引字段的长度，但有三种情况例外：

如果索引是前缀索引，这里则只会使用创建前缀索引时，声明的前N个字节来检索数据。
如果是联合索引，这里只会显示当前SQL会用到的索引字段长度，可能不是全匹配的情况。
如果一个索引字段的值允许为空，key_len的长度会为：索引字段长度+1。

ref

显示索引查找过程中，查询时会用到的常量或字段：

const：如果显示这个，则代表目前是在基于主键字段值或数据库已有的常量（如null）查询数据。
- select ... where 主键字段 = 主键值;
- select ... where 索引字段 is null;
显示具体的字段名：表示目前会基于该字段查询数据。
func：如果显示这个，则代表当与索引字段匹配的值是一个函数，如：
- select ... where 索引字段 = 函数(值);

rows

代表执行时，预计会扫描的行数，这个数字对于InnoDB表来说，其实有时并不够准确，但也具备很大的参考价值，如果这个值很大，在执行查询语句时，其效率必然很低，所以该值越小越好。

filtered

该字段表示查询结果中满足WHERE条件的行数占总行数的比例，其值范围为0到1。 "filtered"字段是一个估算值，其准确性取决于MySQL对索引和数据的统计信息的准确性，以及查询语句的具体执行计划。

extra

执行查询语句时的一些其他信息，这个信息对索引调优而言比较重要，可以带来不小的参考价值，但这个字段会出现的值有很多种，如下：

Using index：表示目前的查询语句，使用了索引覆盖机制拿到了数据。
Using where：表示目前的查询语句无法从索引中获取数据，需要进一步做回表去拿表数据。
Using temporary：表示MySQL在执行查询时，会创建一张临时表来处理数据。
Using filesort：表示会以磁盘+内存完成排序工作，而完全加载数据到内存来完成排序。
Select tables optimized away：表示查询过程中，对于索引字段使用了聚合函数。
Using where;Using index：表示要返回的数据在索引中包含，但并不是索引的前导列，需要做回表获取数据。
NULL：表示查询的数据未被索引覆盖，但where条件中用到了主键，可以直接读取表数据。
Using index condition：和Using where类似，要返回的列未完全被索引覆盖，需要回表。
Using join buffer (Block Nested Loop)：连接查询时驱动表不能有效的通过索引加快访问速度时，会使用join-buffer来加快访问速度，在内存中完成Loop匹配。
Impossible WHERE：where后的条件永远不可能成立时提示的信息，如where 1!=1。
Impossible WHERE noticed after reading const tables：基于唯一索引查询不存在的值时出现的提示。
const row not found：表中不存在数据时会返回的提示。
distinct：去重查询时，找到某个值的第一个值时，会将查找该值的工作从去重操作中移除。
Start temporary, End temporary：表示临时表用于DuplicateWeedout半连接策略，也就是用来进行semi-join去重。
Using MRR：表示执行查询时，使用了MRR机制读取数据。
Using index for skip scan：表示执行查询语句时，使用了索引跳跃扫描机制读取数据。
Using index for group-by：表示执行分组或去重工作时，可以基于某个索引处理。
FirstMatch：表示对子查询语句进行Semi-join优化策略。
No tables used：查询语句中不存在from子句时提示的信息，如desc table_name;。
......

具体的可参考《explain-Extra字段详解》，其中介绍了Extra字段可能会出现的所有值，最后基于Extra字段做个性能排序：

Using index → NULL → Using index condition → Using where → Using where;Using index → Using join buffer → Using filesort → Using MRR → Using index for skip scan → Using temporary → Strart temporary,End temporary → FirstMatch

索引优化参考项

explain工具中的每个字段值，字段数量也比较多，但在做索引优化时，值得咱们参考的几个字段为：

key：如果该值为空，则表示未使用索引查询，此时需要调整SQL或建立索引。
type：这个字段决定了查询的类型，如果为index、all就需要进行优化。
rows：这个字段代表着查询时可能会扫描的数据行数，较大时也需要进行优化。
filtered：这个字段代表着查询时，表中不会扫描的数据行占比，较小时需要进行优化。
Extra：这个字段代表着查询时的具体情况，在某些情况下需要根据对应信息进行优化。

在explain语句后面紧跟着show warings语句，可以得到优化后的查询语句，从而看出优化器优化了什么。