SQL在大家日常开发的过程中用得很多。仔细想一想,数据库真的是一个很强大的东西,撑起了软件行业的半壁江山。学会怎么用其实很简单,知道怎么优化是更高的水平啦,再深入一些就该去读它的开源代码,像庖丁解牛的专家一样。

The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.

发成“My Ess Que Ell”真的还挺奇怪的......

mysql> select * from T where ID = 10

第一步,你执行SQL语句的客户端会连接到数据库所在的服务器上的连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

#连接命令

mysql -h$ip -P$port -u$user -p

长连接VS短连接

长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接是指每次执行完很少的几次查询后就断开连接,下次查询再重新建立一个。建立连接有性能消耗,所以尽量用长连接。但是长连接用久了会导致内存占用太大,被系统强行杀掉(OOM),MySQL会发生异常重启。

解决方案:

1.定期断开长连接。在程序里判断执行过一个内存占用较大的查询后,断开连接,之后要查询再重连。

2.MySQL 5.7 以后的版本,可以在每次执行一个比较大的操作后,通过执行Mysqlresetconnection来重新初始化连接资源,连接会恢复到刚刚创建的状态。

第二步:查询缓存。

MySQL拿到一个请求后,会现在查询缓存里看看,如果之前执行过这条语句(key会查询语句,value为查询结果),则直接返回查询结果给客户端。如果查询命中缓存,查询效率会很高。但是查询缓存的弊大于利:它的失效非常频繁,只要有对一个表进行更新,这个表所有的查询缓存都会被清空。

建议:将querycachetype设置成DEMAND,这样默认没有查询缓存。需要的时候开业在SQL语句中加上SQL_CACHE。MySQL 8.0版本后将去掉查询缓存的功能。

第三步:分析器。 大学的时候学习过编译原理的话,那对这个分析器的概念应该会熟悉。SQL语句会经历词法分析,语法分析最后转化成计算机可以理解的语法树。如果会修改这个分析器的话,那么我们就可以定制化我们自己的SQL语法啦。

第四步:优化器。 对,在真正执行查询之前,我们的MySQL还很善良地帮我们把查询优化了一遍。比如说有多个索引的时候用哪个索引,多表关联的时候决定各个表的连接顺序等等。但有时候,优化器的选择不会是最优的,我们也可以强制制定使用哪个索引等等。

第五步:执行器。 执行器首先会判断有没有对这个表的执行权限。有的话就会根据表使用了哪个引擎,去调用这个引擎的接口。基本上是一行一行数据地去判断是否满足查询条件。可以再慢查询日志的rows_examined字段里看到到底 大约扫了多少行。

update T set c=c+1 where ID=2;

上面说的五个步骤同样要走一遍。其中还涉及到两个重要的日志模块:redo log(重做日志)和binlog(归档日志)。redo log是InnoDB引擎特有的日志,负责存储的相关事宜;binlog是server层的日志,所有引擎都可以使用。

redo log 如果来一条更新语句就执行一次更新,磁盘读写和IO的成本是非常高的。WAL(Write-Ahead Logging)技术使用了先将需要更新的操作写到日志里,在系统比较空闲的时候,再写磁盘。

InnoDB 的 redo log 是固定大小的,比如可以配置为一组4个文件,每个文件大小1GB。

write pos是当前记录位置,一边写一边后移,写到尾部又重头开始。checkpoint是当前要擦除的位置,同样往后移循环。有了redo log,数据库发生异常重启之前提交地记录不会丢失。这个能力称为crash-safe。

bin log是逻辑日志,记录的是这个语句最原始的逻辑,而且是“追加写”,写完这个一个文件写下一个。

redo log 和 binlog 都可以用于表示事务的提交状态,两阶段提交是让两个状态保持逻辑一致。

极客时间《MySQL实战25讲》