工作实战: 让 SQL 飞起来

工作实战: 让 SQL 飞起来

1 使用高效的查询

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class_a
-- ----------------------------
DROP TABLE IF EXISTS `class_a`;
CREATE TABLE `class_a` (
  `name` varchar(16) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `city` varchar(16) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class_a
-- ----------------------------
INSERT INTO `class_a` VALUES ('伯杰', '21', '千叶');
INSERT INTO `class_a` VALUES ('布朗', '22', '东京');
INSERT INTO `class_a` VALUES ('拉里', '19', '埼玉');

/*
Navicat MySQL Data Transfer
Target Server Type    : MYSQL
Target Server Version : 50723
File Encoding         : 65001

Date: 2019-03-24 18:47:42
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class_b
-- ----------------------------
DROP TABLE IF EXISTS `class_b`;
CREATE TABLE `class_b` (
  `name` varchar(16) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `city` varchar(16) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class_b
-- ----------------------------
INSERT INTO `class_b` VALUES ('和泉', '18', '千叶');
INSERT INTO `class_b` VALUES ('武田', '20', '千叶');
INSERT INTO `class_b` VALUES ('石川', '19', '神奈川');

使用 EXISTS 时更快的原因有以下两个。
●  如果连接列（id）上建立了索引，那么查询 Class_B 时不用查实
际的表，只需查索引就可以了。
●  如果使用 EXISTS，那么只要查到一行数据满足条件就会终止查询，
不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样。

当 IN 的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图。很多情况下这种做法都非常耗费资源。使用 EXISTS 的话，数据库不会生成临时的工作表。但是从代码的可读性上来看， IN 要比 EXISTS 好。使用 IN 时的代码看起来更加一目了然，易于理解。因此，如果确信使用 IN 也能快速获取结果，就没有必要非得改成 EXISTS 了。而且，最近有很多数据库也尝试着改善了 IN 的性能 A。也许未来的某一天，无论在哪个数据库上， IN 都能具备与 EXISTS 一样的性能。

EXPLAIN SELECT *
FROM `class_a`
WHERE NAME IN (SELECT NAME
FROM `class_b`);



EXPLAIN SELECT *
FROM class_a A
WHERE EXISTS
(SELECT *
FROM class_b B
WHERE A.name = B.name);

2 避免排序

会进行排序的代表性的运算有下面这些。
●  GROUP BY 子句
●  ORDER BY 子句
●  聚合函数（SUM、 COUNT、 AVG、 MAX、 MIN）
●  DISTINCT
●  集合运算符（UNION、 INTERSECT、 EXCEPT）
●  窗口函数（RANK、 ROW_NUMBER 等）

使用 EXISTS 代替 DISTINCT

在极值函数中使用索引（MAX/MIN）
SQL 语言里有 MAX 和 MIN 两个极值函数。使用这两个函数时都会进行排序。但是如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表.这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响.

能写在 WHERE 子句里的条件不要写在 HAVING 子句里
原因通常有两个。第一个是在使用 GROUP BY 子句聚合时会进行排序，如果事先通过 WHERE 子
句筛选出一部分行，就能够减轻排序的负担。第二个是在 WHERE 子句的条
件里可以使用索引。 HAVING 子句是针对聚合后生成的视图进行筛选的，
但是很多时候聚合后的视图都没有继承原表的索引结构

在 GROUP BY 子句和 ORDER BY 子句中使用索引
一般来说， GROUP BY 子句和 ORDER BY 子句都会进行排序，来对行
进行排列和替换。不过，通过指定带索引的列作为 GROUP BY 和 ORDER
BY 的列，可以实现高速查询。特别是，在一些数据库中，如果操作对象
的列上建立的是唯一索引，那么排序过程本身都会被省略掉。如果各位有
兴趣，可以确认一下自己使用的数据库是否支持这个功能.

真的用到索引了吗

使用索引时，条件表达式的左侧应该是原始字段!!!

请牢记，这一点是在优化索引时首要关注的地方!!!

进行默认的类型转换

■ 对 char 类型的“col_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));

默认的类型转换不仅会增加额外的性能开销，还会导致索引不可用，
可以说是有百害而无一利。虽然这样写还不至于出错，但还是不要嫌麻烦，在需要类型转换时显式地进行类型转换吧（别忘了转换要写在条件表达式的右边）

减少中间表
在 SQL 中，子查询的结果会被看成一张新表，这张新表与原始表一样，
可以通过代码进行操作。这种高度的相似性使得 SQL 编程具有非常强的
灵活性，但是如果不加限制地大量使用中间表，会导致查询性能下降。
频繁使用中间表会带来两个问题，一是展开数据需要耗费内存资源，
二是原始表中的索引不容易使用到（特别是聚合时）。因此，尽量减少中
间表的使用也是提升性能的一个重要方法