最近博主看完了《SQL进阶教程》这本书，看完后给博主打开了SQL世界的新大门，对于 SQL 的理解不在局限于以前的常规用法。借用其他读者的评论， 读完醍醐灌顶，对SQL做到了知其然更能知其所以然。全书从头到尾强调了 SQL的内在逻辑是基于集合论和谓词逻辑，而着两条主线恰恰在使用SQL起到了至关重要的指导作用。 本文给大家总结如何让SQL起飞（优化） 在SQL中，很多时候不同的SQL代码能够得出相同结果。从理论上来说，我们认为得到相同结果的不同SQL之间应该有相同的性能，但遗憾的是，查询优化器生成的执行计划很大程度上受到SQL代码影响，有快有慢。因此如果想优化查询性能，我们必须知道如何写出更快的SQL，才能使优化器的执行效率更高。 当IN的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图。很多情况下这种做法都非常耗费资源。使用EXISTS的话，数据库不会生成临时的工作表。但是从代码的可读性上来看，IN要比EXISTS好。使用IN时的代码看起来更加一目了然，易于理解。因此，如果确信使用IN也能快速获取结果，就没有必要非得改成EXISTS了。 这里用Class_A表和Class_B举例， 使用EXISTS时更快的原因有以下两个。 实际上，大部分情况在子查询数量较小的场景下EXISTS和IN的查询性能不相上下，由EXISTS查询更快第二点可知，子查询数量较大时使用EXISTS才会有明显优势。 在SQL语言中，除了ORDER BY子句会进行显示排序外，还有很多操作默认也会在暗中进行排序，如果排序字段没有添加索引，会导致查询性能很慢。SQL中会进行排序的代表性的运算有下面这些。 如上列出的六种运算（除了集合运算符），它们后面跟随或者指定的字段都可以添加索引，这样可以加快排序。 「实际上在DISTINCT关键字、GROUP BY子句、ORDER BY子句、聚合函数跟随的字段都添加索引，不仅能加速查询，还能加速排序。」 为了排除重复数据，我们可能会使用DISTINCT关键字。如1.2中所说，默认情况下，它也会进行暗中排序。如果需要对两张表的连接结果进行去重，可以考虑使用EXISTS代替DISTINCT，以避免排序。这里用Items表和SalesHistory表举例： 在一（Items）对多（SalesHistory）的场景下，我们需要对item_no去重，使用DISTINCT去重，因此SQL如下： 使用EXISTS代替DISTINCT去重，SQL如下： 这条语句在执行过程中不会进行排序。而且使用EXISTS和使用连接一样高效。 SQL中有UNION、INTERSECT、EXCEPT三个集合运算符。在默认的使用方式下，这些运算符会为了排除掉重复数据而进行排序。 MySQL还没有实现INTERSECT和EXCEPT运算 如果不在乎结果中是否有重复数据，或者事先知道不会有重复数据，请使用UNION ALL代替UNION。这样就不会进行排序了。 例如，这里继续用SalesHistory表举例，下面两条SQL语句返回的结果是一样的： 但是从性能上来看，第二条语句写法效率更高。原因有两个： 如下，col_1字段是char类型： 当查询条件左边和右边类型不一致时会导致索引失效。 如下： 在索引字段col_1上进行运算会导致索引不生效，把运算的表达式放到查询条件的右侧，就能用到索引了，像下面这样写就OK了。 如果无法避免在左侧进行运算，那么使用函数索引也是一种办法，但是不太推荐随意这么做。「使用索引时，条件表达式的左侧应该是原始字段请牢记」，这一点是在优化索引时首要关注的地方。 下面这几种否定形式不能用到索引。 这个是跟具体数据库的优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，他可以选择直接不走索引。平时我们用!=、<>、not in的时候，要注意一下。 例如下表： 使用OR条件进行查询 这个SQL的执行条件下，很明显id字段查询会走索引，但是对于OR后面name字段的查询是需要进行全表扫描的。在这个场景下，优化器直接进行一遍全表扫描就完事了。 使用联合索引需要满足最左匹配原则，即最左优先。如果你建立一个（col_1, col_2, col_3）的联合索引，相当于建立了 (col_1)、(col_1,col_2)、(col_1,col_2,col_3) 三个索引。如下例子： 联合索引中的第一列（col_1）必须写在查询条件的开头，而且索引中列的顺序不能颠倒。 并不是用了like通配符，索引一定会失效，而是like查询是以%开头，才会导致索引失效。 如果两张表进行连接，关联字段编码不一致会导致关联字段上的索引失效，这是博主在线上经历一次SQL慢查询后的得到的结果，举例如下，有如下两表，它们的name字段都建有索引，但是编码不一致，user表的name字段编码是utf8mb4，user_job表的name字段编码是utf8， 进行SQL查询如下： 在SQL中，子查询的结果会被看成一张新表，这张新表与原始表一样，可以通过代码进行操作。这种高度的相似性使得SQL编程具有非常强的灵活性，但是如果不加限制地大量使用中间表，会导致查询性能下降。 频繁使用中间表会带来两个问题，一是展开数据需要耗费内存资源，二是原始表中的索引不容易使用到（特别是聚合时）。因此，尽量减少中间表的使用也是提升性能的一个重要方法。 对聚合结果指定筛选条件时，使用HAVING子句是基本原则。不习惯使用HAVING子句的人可能会倾向于像下面这样先生成一张中间表，然后在WHERE子句中指定筛选条件。例如下面： 然而，对聚合结果指定筛选条件时不需要专门生成中间表，像下面这样使用HAVING子句就可以。 HAVING子句和聚合操作是同时执行的，所以比起生成中间表后再执行的WHERE子句，效率会更高一些，而且代码看起来也更简洁。 当我们需要对多个字段使用IN条件查询时，可以通过 || 操作将字段连接在一起变成一个字符串处理。 这样一来，子查询不用考虑关联性，而且只执行一次就可以。 连接和聚合同时使用时，先进行连接操作可以避免产生中间表。原因是，从集合运算的角度来看，连接做的是“乘法运算”。连接表双方是一对一、一对多的关系时，连接运算后数据的行数不会增加。而且，因为在很多设计中多对多的关系都可以分解成两个一对多的关系，因此这个技巧在大部分情况下都可以使用。 到此本文讲解完毕，感谢大家阅读，感兴趣的朋友可以点赞加关注，你的支持将是我更新动力❝
一、SQL写法优化
1.1 子查询用EXISTS代替IN
我们试着从Class_A表中查出同时存在于Class_B表中的员工。下面两条SQL语句返回的结果是一样的，但是使用EXISTS的SQL语句更快一些。--慢
SELECT *
FROM Class_A
WHERE id IN (SELECT id
FROM Class_B);
--快
SELECT *
FROM Class_A A
WHERE EXISTS
(SELECT *
FROM Class_B B
WHERE A.id = B.id);
1.2 避免排序并添加索引
❝
1.3 用EXISTS代替DISTINCT
我们思考一下如何从上面的商品表Items中找出同时存在于销售记录表SalesHistory中的商品。简而言之，就是找出有销售记录的商品。SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;
item_no
-------
10
20
30SELECT item_no
FROM Items I
WHERE EXISTS
(SELECT ＊
FROM SalesHistory SH
WHERE I.item_no = SH.item_no);
item_no
-------
10
20
301.4 集合运算ALL可选项
❝
1.5 WHERE条件不要写在HAVING字句
--聚合后使用HAVING子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';
--聚合前使用WHERE子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;
二、真的用到索引了吗
2.1 隐式的类型转换
SELECT * FROM SomeTable WHERE col_1 = 10; -- 走了索引
SELECT * FROM SomeTable WHERE col_1 ='10'; -- 没走索引
SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2)); -- 走了索引2.2 在索引字段上进行运算
SELECT *
FROM SomeTable
WHERE col_1 * 1.1 > 100;WHERE col_1 > 100 / 1.12.3 使用否定形式
2.4 使用OR查询前后没有同时使用索引
CREATE TABLE test_tb (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(55) NOT NULL
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;SELECT *
FROM test_tb
WHERE id = 1 OR name = 'tom'2.5 使用联合索引时，列的顺序错误
-- 走了索引
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
-- 走了索引
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
-- 没走索引
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
-- 没走索引
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
-- 没走索引
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;2.6 使用LIKE查询
-- 没走索引
SELECT * FROM SomeTable WHERE col_1 LIKE'%a';
-- 没走索引
SELECT * FROM SomeTable WHERE col_1 LIKE'%a%';
-- 走了索引
SELECT * FROM SomeTable WHERE col_1 LIKE'a%';2.7 连接字段字符集编码不一致
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER
SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `user_job` (
`id` int NOT NULL,
`userId` int NOT NULL,
`job` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;EXPLAIN
SELECT *
from `user` u
join user_job j on u.name = j.name
由结果可知，user表的查询没有走索引。想要user表也走索引，那就需要把user表name字段的编码改成utf8即可。三、减少中间表
3.1 使用HAVING子句
SELECT *
FROM (
SELECT sale_date, MAX(quantity) max_qty
FROM SalesHistory
GROUP BY sale_date
) tmp
WHERE max_qty >= 10SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) >= 10;3.2 对多个字段使用IN
SELECT *
FROM Addresses1 A1
WHERE id || state || city
IN (SELECT id || state|| city
FROM Addresses2 A2);3.3 先进行连接再进行聚合