数据库

作者：张延俊
链接：https://www.zhihu.com/question/29619558/answer/45805380
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

数据库执行sql的大致流程
粗略流程，所有关系型数据库都是这几步，具体前后顺序根据不同dbms不同配置下略有小差别，以下过程都需要耗时耗资源
1.应用程序与数据库服务器建立链接
2.sql发送到数据库，数据库验证是否有执行的权限
3.进入语法解析器，进行词法与语法分析
4.进入优化器生成执行计划，部分dbms会检查是否有可重用的执行计划
5.根据执行计划依次扫描相关表中的行，不在数据缓冲区的走io
6.同时对于被扫描的行可能加锁，同时也可能会被其他sql阻塞
7.扫描的行足够放入查询缓存则开始运算或直接返回，不够则生成临时表，可能消耗io
8.对sql结果进行计算（可能）
9.将计算完成的结果全部写入网络io（可能）
10.如果事务完成则同步事务日志并释放锁，具体方式取决于dbms和当前配置
11.关闭连接（可选）

如何优化
这么多步骤，每一步都有优化策略，我尽量用简单的语言来描述
1.应用程序与数据库服务器建立链接
引入数据库连接池，避免每次都与数据库建立连接，提高效率

2.sql发送到数据库，数据库验证是否有执行的权限
没撒好说的

3.进入语法解析器，进行词法与语法分析
也没撒好说的，想要数据库在这里少用点资源就把sql写的简单点，但是差别不大

4.进入优化器生成执行计划，部分dbms会检查是否有可重用的执行计划
哦也，最复杂的部分来了，任何数据库如何生成执行计划都可以写一本几百页的书，我就简单说说，复杂的我也说不出来，哈哈哈
关系型数据库选择走什么执行计划都是基于消耗最小化的思路来的，简单来说就是走什么索引，按什么顺序走表，被扫到的数据行最少。如果你的表结构很复杂，有各种混搭的索引，你的join很多，那执行计划分析的时间就会拉长。所以sql对应的表索引简单，join或子查询少就快，复杂了优化器也会得选择困难症。

5.根据执行计划依次扫描相关表中的行，不在数据缓冲区的走io
存储引擎扫描表的性能消耗参考下面的list，消耗从大到小
全表扫描>全索引扫描>部分索引扫描>索引查找>唯一索引/主键查找>常量/null
要走索引对于sql语句也有要求，不能在谓词上作任何运算，扫描行数一般不能超过表的17%左右，这对你数据分布又有要求，比如你查select xxx from human where sex ='man'，五五开，还是走扫描。这里我就不展开了，推荐题主一本书《Relational Database Index Design and the Optimizers》

6.同时对于被扫描的行可能加锁，同时也可能会被其他sql阻塞
如果扫描的行多，sql执行的时间长，被阻塞的概率就高，阻塞别人的概率也高，然后大家一起等，数据库就hung住了

7.扫描的行足够放入查询缓存则开始运算或直接返回，不够则生成临时表，可能消耗io
一次取的尽量少，这不单指返回服务端的行数，应该从嵌套最深的一个子查询开始算

8.对sql结果进行计算（可能）
少用各种复杂的函数啊，count啊，order by啊等等

9.将计算完成的结果全部写入网络io（可能）
请尽量少返回一点数据，如果不行请多次分批

10.如果事务完成则同步事务日志并释放锁，具体方式取决于dbms和当前配置
这里举两个代表性栗子:
sql渣：
for i in (1-1000):
start transaction;
insert into table values (1);
commit;
end for
sql赞：
start transaction;
for i in (1-1000):
insert into table values (1);
end for
commit;
sql赞爆：
insert into table values (1)()...........()(1000);
首先，sql语法是我临时自创的，这个不是关键，关键在sql渣先生是1000个事务插1000行，日志flush1000次。sql赞先生是一个事务插1000行，事务日志flush1次。sql赞爆最nice。
这个例子我想表达的意思是如果你要用sql做一件事，那就要尽量让这件事占用的事务总时间最少。

第二个例子
sql渣：
update table where id > 0 and id < 1000000;

sql赞:
update table where id > 0 and id < 1000;
update table where id >= 1000 and id < 2000;
update table where id >＝ 2000 and id < 3000;
....
....
这个例子我想表达的意思是如果你要用sql做一件很大的事，那就尽量让大事化成很多小事。
两个例子好好体会下，一点不矛盾哦。补充一下，这里每个update都是单独事务

11.关闭连接（可选）
同1，别每次都关，关了也许还要重连。不关的话记得commit就好了，千万要记得commit啊！