MYSQL系列-SQL查询

- 日理万妓 2023-10-13 12:32 21阅读 0赞

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

## 建索引的原则 ##

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

### 最左前缀匹配 ###

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

1.  WHERE a = 'value\_a'
2.  WHERE a = 'value\_a' AND b = 'value\_b'
3.  WHERE a = 'value\_a' AND b = 'value\_b' AND c = 'value\_c'

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

1.  WHERE b = 'value\_b' AND c = 'value\_c'

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

### 覆盖索引，避免回表 ###

参考 [覆盖索引][Link 2]

### 选择区分度高列作为索引 ###

区分度的公式是`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接口尽量不要使用此查询条件  
管理台等低频调用可以，否则就需要采用其他机制来查询，会增加额外硬件成本

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

sql

复制代码

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

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

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

sql

复制代码

`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`

sql

复制代码

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

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

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

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

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

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

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

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

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

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

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

如下：

sql

复制代码

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

可以改成

sql

复制代码

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

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

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

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

### 其他场景 ###

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

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

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

## 慢查询优化-Explain ##

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

mysql

复制代码

### 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字段详解》][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`语句，可以得到优化后的查询语句，从而看出优化器优化了什么。

## 慢查询优化案例 ##

## 相关参数配置 ##

### JOIN 相关配置 ###

1.  join\_buffer\_size：该参数控制连接操作中用于存储临时数据的缓冲区大小。如果JOIN操作涉及的数据量较大，可以适当增加该参数的值来提高性能。
2.  sort\_buffer\_size：该参数控制排序操作中用于存储临时数据的缓冲区大小。如果JOIN操作涉及的数据量较大，可以适当增加该参数的值来提高性能。
3.  max\_join\_size：该参数控制MySQL在执行JOIN操作时，允许连接的表的最大大小。如果超出该大小限制，MySQL将会返回错误信息。该参数的默认值为4GB。

### 临时表设置 ###

1.  tmp\_table\_size：该参数控制MySQL在创建临时表时，分配的内存大小。如果临时表的数据量较大，可以适当增加该参数的值来提高性能。
2.  default\_tmp\_storage\_engine：指定创建临时表时使用的默认存储引擎。该参数的默认值为InnoDB。
3.  internal\_tmp\_disk\_storage\_engine：该参数用于设置创建基于磁盘的临时表时使用的存储引擎。如果创建的临时表需要存储到磁盘上，则会使用该参数指定的存储引擎。默认值为InnoDB。
4.  tmp\_table\_size：该参数控制MySQL在创建临时表时，分配的内存大小。如果临时表的数据量较大，可以适当增加该参数的值来提高性能。
5.  max\_tmp\_tables：是用于控制允许创建的最大临时表数量的参数
6.  tmpdir：是用于指定MySQL在创建临时表时使用的临时目录的参数

[Link 1]: https://juejin.cn/post/7250776775937720378#heading-43
[Link 2]: https://juejin.cn/post/7250776775937720378#heading-44
[explain-Extra]: https://link.juejin.cn?target=http%3A%2F%2Fblog.wingflare.com%2F2019%2F10%2Fy2x1d5wv3q6prl3m.html