优秀SQL查询的11条设计指南

《数据库管理基础教程.Jeffrey A.Hoffer》

1. 理解索引在查询处理中是怎样使用的。

很多DBMS 一次查询只能使用一个索引，如mysql。—— 如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个，而使用复合索引因为索引本身就对应到两个字段上的，效率会有很大提高。

* 可以使用EXPLAIN命令查看查询计划。

use sqlteacher;
-- 创建表，带两个索引
CREATE TABLE `t_lesson` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `start_course_time` DATETIME NOT NULL COMMENT '预计直播课上课时间',
    `end_course_time` DATETIME NOT NULL COMMENT '预计直播课下课时间',
    `city_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_city_id` (`city_id`),
    INDEX `idx_start_course_time` (`start_course_time`)
);

-- 查看and查询使用的索引，只会用到一个索引
explain select * from t_lesson where start_course_time = '2019-08-25 06:00:00' and city_id=4;

2. 保持优化器(Query Optimizer)统计信息最新。

在一个特定的查询中，使用索引可能加快速度，也可能减慢速度，所以DBMS要对使用索引的效果做一个预测，预测的依据就是统计信息。一些DBMS不是自动更新统计信息。如果性能退化，就强制运行一个类似更新统计信息的命令。

* 学习查询优化器。

3. 对查询中的字段和文字使用兼容数据类型。避免查询处理中转换数据。

4. 编写简单查询。

5. 将复杂分离为多个简单的部分。

因为DBMS可能在每个查询中只使用一个索引，所以将复杂查询分离为多个简单的部分（每个查询都可以用到索引），然后将结果合并在一起会更好。

6. 不要把一个查询嵌套到另一个中（效率不会更高）。

7. 不要把表和它本身合并。

避免使用自连接。通常为表制作一个临时拷贝并关联原来的表和临时表会更好，查询更高效。

8. 为多个查询创建临时表。

这样做的不足是在查询运行中原始表的更新不会改变临时调。但是对于多个查询都要引用同样的数据子集的情况下，临时表高效。

9. 合并更新操作。

如果可能，可将多个更新命令合并到一个中。这将会减少查询处理的负载并允许DBMS找寻并行处理更新的方式。

-- 更新合并
update t_lesson set end_course_time = '2020-09-29 09:00:00', start_course_time = '2020-08-29 09:00:00' where city_id = 203;

-- 更新合并
update t_lesson set end_course_time = '2021-09-29 09:00:00' where city_id = 203;
update t_lesson set end_course_time = '2022-09-29 09:00:00' where city_id = 204;
update t_lesson set end_course_time=(case city_id when 203 then '2021-09-29 09:00:00' when 204 then '2022-09-29 09:00:00' end) where city_id in (203, 204) 

-- 插入和更新合并
ALTER TABLE t_lesson CHANGE id id BIGINT(20) unsigned not null; -- 去除主键自增属性
ALTER TABLE t_lesson DROP PRIMARY KEY ,ADD PRIMARY KEY ( `city_id` ); -- 修改主键
-- UPDATE和INSERT语句合并，先执行insert into插入语句，如果插入失败(UNIQUE索引或pk重复，说明该条记录已存在)，则执行update更新语句；
INSERT INTO t_lesson(id, start_course_time, end_course_time, city_id) VALUES(4, '2019-08-25 06:00:00','2020-09-29 06:00:00', 403) ON DUPLICATE KEY UPDATE start_course_time = '2021-11-11 09:00:00'; 

10. 只检索你需要的数据。

   SELECT * FROM EMP之类的简写尽量避免。

11. 排序时尽量使用索引。