数据库查询优化-查询语句优化

最新推荐文章于 2024-07-29 09:52:01 发布

大·风

最新推荐文章于 2024-07-29 09:52:01 发布

阅读量1.4k

点赞数 2

分类专栏：数据 # 查询优化文章标签：查询优化数据库优化

本文链接：https://blog.csdn.net/qq330983778/article/details/100108598

版权

数据同时被 2 个专栏收录

16 篇文章 0 订阅

订阅专栏

查询优化

4 篇文章 1 订阅

订阅专栏

查询语句的优化

注意，实际中发现很多说法和网络上文章都不太一样，所以这里注明我本人使用的是MySQL5.7。我会尽量验证所有说法，但是难免有疏漏或者限于个人水平无法解释的部分，各位在看的时候可以尽量使用EXPLAIN做一些模拟测试。

建表SQL

DROP TABLE IF EXISTS `device_apply`;
CREATE TABLE `device_apply` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `device_type` int(2) NOT NULL DEFAULT '1' COMMENT '申请类型',
  `user_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'userid',
  `status` int(2) DEFAULT '1' COMMENT '状态',
  PRIMARY KEY (`id`),
  KEY `device_user_id` (`user_id`) USING BTREE,
  KEY `status_index` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=431 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `device_order`;
CREATE TABLE `device_order` (
  `device_order_id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(255) NOT NULL COMMENT '订单号',
  `device_apply_id` bigint(11) NOT NULL COMMENT '申请id',
  `status` int(11) DEFAULT '1' COMMENT '状态',
  PRIMARY KEY (`device_order_id`),
  KEY `index` (`device_apply_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8;

查询语句优化主要有几种方式

查询条件的优化
查询字段的优化
关联查询的优化

分析查询语句

首先我们可以用EXPLAIN或DESCRIBE命令分析一条查询语句的执行信息.

查询条件的优化

between和in的选择

能够用BETWEEN的就不要用IN

SELECT * FROM device_apply t1
WHERE t1.id IN (210,211,212,213,214,215,216,217,218,219,220,221,222,223);

上门这种进行连续内容的IN筛选，可以使用BETWEEN替代的就使用下面方式

SELECT * FROM device_apply t1
WHERE t1.id BETWEEN 210 AND 223;

需要注意的是！！！网上很多人解释因为使用in会导致全表检索，而放弃使用索引。然而在实际使用中使用IN和使用BETWEEN返回内容,type皆为range。这里本人使用的是5.7的MySql，不知道是否因为5.7进行了优化

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

排除了索引的原因，至于为什么between比in好，这是我在网上找到的回答：连续数值当然between好了，减少解析，并且in的范围默认超过一定数目就会走全表，这个数量是个比例，大概25%-35%左右，然而这个30左右的比例也并不是说一定走全表扫描，因为mysql还有一个索引扫描，就是说如果select的内容在你的索引里面就能找到的话当然不会去扫全表，然后还想说一下between的情况，为什么好是因为除了索引段上连续存取减少解析以外，还有一个情况就是在磁盘寻址检索数据的时候，会默认读取第一次取值附近的部分数据（有这么一个概率算法说的是当一个数据被检索到的时候，他附近的数据也很大概率会被用到）所以就有了这么一个一次性取出冗余数据避免多次寻址的情况，这时候使用between的连续取值就正适用了

这是回答的地址

distinct和group by的选择

能够用DISTINCT的就不用GROUP BY。distinct简单来说就是用来去重的，而group by的设计目的则是用来聚合统计的

SELECT status FROM device_apply t1
GROUP BY t1.status;

这个时候应该使用DISTINCT替代GROUP BY;

SELECT DISTINCT status FROM device_apply t1;

Limit

对于常用于分页处理，对于我们查询时候假如存在大量数据，而你一次并不需要如此多的数据的时候，可以使用Limit来只获取需要的部分。

exists

~~很多文章中会描述 exists比in 效率高所以要使用exists~~。但实际发现在关联两个表相同的时候，exists和in相比没有太大差别。两者使用在不一样的场景，假如存在两个表一小一大，则子查询表大的用exists，子查询表小的用in。

两者真正的区别在于not exists 和 not in。not extsts 的子查询依然能用到表上的索引，而not in则不能。

EXISTS在SQL中的作用是：检验查询是否返回数据。当 where 后面的条件成立，则列出数据，否则为空。

SELECT * FROM device_apply t1 
WHERE t1.id NOT IN ( SELECT t2.device_apply_id FROM device_order t2 WHERE t2.STATUS = '1' );

这个时候应该修改为：

SELECT * FROM device_apply t1 
WHERE NOT EXISTS ( SELECT t2.device_apply_id FROM device_order t2 WHERE t2.STATUS = '1' AND t2.device_apply_id = t1.id );

使用join

在MySQL中,尽量使用JOIN来代替子查询.因为嵌套的子查询效率会比较低，因为每一条记录都要进行匹配，如果记录长度比较大的话，那么我们的查询就有可能非常的耗时。我们应该尽量避免使用子查询，而用表连接。

使用子查询

EXPLAIN SELECT * FROM device_apply t1 
WHERE t1.id IN ( SELECT t2.device_apply_id FROM device_order t2 WHERE t2.STATUS = '1' );

输出的内容

+----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key     | key_len | ref                         | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                        | NULL |   100.00 | NULL        |
|  1 | SIMPLE       | t1          | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | <subquery2>.device_apply_id |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                        |   72 |    10.00 | Using where |
+----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------------+

使用join

EXPLAIN SELECT * FROM device_apply t1 
LEFT JOIN device_order t2
ON t1.id = t2.device_apply_id
WHERE t2.STATUS = '1';

输出的内容

+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                          |   72 |    10.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | myDataName.t2.device_apply_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+```

查询条件的优化-索引

SQL优化中索引占据了很重要的一部分，关于SQL语句中对于索引需要优化的内容主要有两部分

添加索引
避免索引失效

添加索引

对于经常用来作为查询条件的字段，最好添加索引
对于多表关联查询的时候，作为表关联的字段最好添加索引

避免索引失效

主要还是避免一些查询条件导致数据库放弃使用索引，而改为全文检索。下面主要介绍导致索引失效的情况

where中使用!=、<>对索引字段进行筛选操作会导致索引失效

此种写法不会使用索引

SELECT * FROM device_apply t1 
WHERE t1.id <> 221

~~where中对索引字段使用null判断会导致索引失效~~

网上很多资料说明在where中使用null判断会导致索引失效，但是实际发现where中使用is null ， EXPLAIN中返回的type为ref还是使用的索引，而使用not null 则会导致全文检索。

where中对索引字段使用not null判断会导致索引失效

此种写法不会使用索引,所以这就要求我们在数据库创建的时候制定字段不能为空

SELECT * FROM device_apply t1 
WHERE t1.STATUS is not null

~~where中使用or对索引字段进行筛选会导致索引失效~~

注意！！！按照网络上的说法使用此写法会导致索引失效，但实际中OR中两个条件都使用了索引，会使用index_merge.假如OR左右两边的条件为同一个字段，且使用此字段使用了索引会使用range。当然也可能是5.7MySQL的优化。但是当你OR左右两边的条件中有一方没有使用索引的确会导致全文检索。

EXPLAIN SELECT * FROM device_apply t1 
WHERE t1.id = 221 OR t1.id = 220;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

可以使用这种方式替代。使用下面方式输出的Type为const

EXPLAIN SELECT * FROM device_apply t1 
WHERE t1.id = 221
UNION ALL
SELECT * FROM device_apply t1 
WHERE t1.id = 220;

模糊查询可能会导致索引失效
条件中使用’%{query}%’,’%{query}‘模糊查询，但是使用’{query}%'查询依然可以使用索引。

EXPLAIN SELECT * FROM device_apply t1 
WHERE t1.apply_no LIKE '%95%';

EXPLAIN SELECT * FROM device_apply t1 
WHERE t1.apply_no LIKE '%95';

使用上面内容返回的内容,都进行了全文检索

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  172 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

使用{query}%查询

EXPLAIN SELECT * FROM device_apply t1 
WHERE t1.apply_no LIKE '95%';

使用上面内容返回的内容,都进行了全文检索

+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | index2        | index2 | 153     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+

使用上面内容返回的内容,检索范围为range;

在where条件中尝试使用表达式对字段进行操作会导致索引失效

此种写法不会使用索引

SELECT * FROM device_apply t1 
WHERE t1.id + 1 = 221

但是这种就可以

SELECT * FROM device_apply t1 
WHERE t1.id = 220

使用复合索引的时候需要保证使用该索引中第一个字段作为条件
~~使用不同的数据类型进行做条件判断~~

此处需要知道一个知识点：MYSQL在数据类型转换的时候会优先向INT转。

当你的索引为int的时候，和字符串比较会被MYSQL转为int

SELECT * FROM device_apply t1 
WHERE t1.id = '221'

所以等价于下面内容，并不会导致索引失效

SELECT * FROM device_apply t1 
WHERE t1.id = 221

但是索引字段为字符串，而将字符串和int数据进行比较，则会导致字符串索引失效。

查询字段的优化

不要使用*进行字段查询

进行数据查询的时候，需要哪些字段就返回哪些字段，不要使用*来进行字段查询。

新建临时表

在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

关于锁表

之前有一个说法：SELECT INTO 语句会导致表锁定，阻止其他用户访问该表。根据网友的验证结果是：

对于myisam的表select 是会锁定表的，会导致其他操作挂起，处于等待状态。
对于innodb的表select 是不会锁表的。其实这里使用到了快照。快照这里不作讨论。

避免返回大数据

如果返回数据量过大，要么分批返回，要么需要考虑业务设计是否合理

尽量使用union all替换union

union去重代价非常高，尽可能的放在程序中去重

关联查询

关联查询原理

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，
然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
如果存在多个join，则会将前面的结果集作为循环数据，再次作为循环条件到后一个表中查询。

确定驱动表

确定驱动表的方式：

指定了联接条件时，满足查询条件的记录行数少的表为驱动表
未指定联接条件时，行数少的表为驱动表

或者使用使用EXPLAIN，第一行出现的表就是驱动表


EXPLAIN SELECT * FROM device_apply t1
LEFT JOIN device_order t2
ON t1.id = device_order_id

返回内容

+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             |  172 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | myDataName.t1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+

join优化的原则

小结果集驱动大结果集，将筛选结果小的表首先连接，再去连接结果集比较大的表。
对被驱动表的join字段上建立索引
对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序
优先优化Nested Loop的内层循环（也就是最外层的Join连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；

对驱动表排序

目前下面两张表数据是相同的，根据条件指定t1为驱动表，先对驱动表进行排序


EXPLAIN 
SELECT * FROM device_apply t1
LEFT JOIN device_order t2
ON t1.id = device_order_id
ORDER BY t1.id;

返回内容

+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | index  | NULL          | PRIMARY | 8       | NULL             |  172 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | myDataName.t1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+

对非驱动表排序

现在我们对t2进行排序，t2是被驱动表必然导致对连接后结果集进行排序Using temporary

EXPLAIN 
SELECT * FROM device_apply t1
LEFT JOIN device_order t2
ON t1.id = device_order_id
ORDER BY t2.order_no;

返回内容

+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra                           |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             |  172 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | myDataName.t1.id |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+---------------------------------+

关联查询的条件where和on

ON 条件用来决定如何从 B 表中检索数据行，在匹配阶段 WHERE 子句的条件都不会被使用。在匹配阶段完成以后，WHERE 子句条件才会被使用。这样ON在匹配阶段就进行数据过滤。在使用Left (right) join的时候，一定要在ON中尽可能多的提供过滤条件，减少匹配阶段的数据。

总结

每次整理完知识点之后会习惯的验证下，自己记忆中的内容是否准确。当然实际上每次都会发现一些记忆中以为是对的，实际上是错的内容。但是都不算很多，但是今天整理这篇内容的时候，发现曾经记忆中很多认为是对的知识点，并不是那么严谨。果然还是“授人以鱼不如授人以渔”，去了解这些技术的原理远比了解这些被加工过后的内容来的靠谱。

大·风

关注

2
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库查询优化-查询语句优化

查询语句的优化注意，实际中发现很多说法和网络上文章都不太一样，所以这里注明我本人使用的是MySQL5.7。我会尽量验证所有说法，但是难免有疏漏或者限于个人水平无法解释的部分，各位在看的时候可以尽量使用EXPLAIN做一些模拟测试。建表SQLDROP TABLE IF EXISTS `device_apply`;CREATE TABLE `device_apply` ( `id` big...
复制链接

扫一扫