mysql 官方说明文档请参考: 官方说明文档.
mysql sql 执行流程图
建立连接
mysql 服务端默认监听的端口是 3306,客户端可以使用不同的协议和服务端建立连接(常见的协议有:TCP、Unix socket 等)。
查看连接详情
- 命令
show global status like 'Thread%';
- 查询结果以及解释
字段 | 含义 |
---|---|
Threads cached | 缓存中的线程连接数 |
Threads connected | 当前打开的连接数 |
Threads created | 为处理连接创建的线程数 |
Threads running | 非睡眠状态的连接数,通常指并发连接数 |
客户端每产生一个连接或者一个会话,在服务端就会创建一个线程来处理,故可以通过线程来看连接情况。
几个线程的详细介绍可以参考:Mysql查看状态,连接数,线程数以及Mysql性能监控工具doDBA的使用以及优化.
连接关键参数
- 超时时间,默认都是28800秒,8小时
--非交互式超时时间,如 JDBC 程序
show global variables like 'wait timeout';
--交互式超时时间,如数据库工具
show global variables like 'interactive timeout';
- 最大连接数:在5.7版本中默认是151个,最大可以设置成100000
show variables like 'max connections';
查询缓存
mysql 查询缓存查询功能比较鸡肋,在 5.7 版本中默认是关闭的,8.+ 版本以后已经被弃用,主要原因有如下两点:
- 第一个是它要求SQL语句必须一 模一样,中间多一个空格,字母大小写不同都被认为是不同的的SQL。
- 第二个是表里面任何一条数据发生变化的时候,这张表所有缓存都会失效,所以对 于有大量数据更新的应用,不适合。
解析器
语法解析
将sql语句拆分成一个一个的单词
词法解析(Parser)
对SQL做一些语法检查,比如单引号有没有闭合, 然后根据MySQL定义的语法规则,根据SQL语句生成解析树,如下图所示:
预处理器(Preprocessor)
预处理器会检査生成的解析树,解决解析器无法解析的语义的问题。比如,它会检査表和列名是否存在,检査名字和别名,保证没有歧义。
预处理器处理的结果是得到一棵新的解析树。
优化器(Optimizer)
一条SQL语句可以有很多种执行方式,最终返回相同的结果,他们是等价的。优化器的目的就是根据解析树生成不同的执行计划(Execution Plan),然后选 择一种最优的执行计划,MySQL里面使用的是基于开销(cost)的优化器,选择开销最小的一种执行(有时候不能光从sql判断查询是否走索引也是这个原因)。
- 可以使用这个命令査看査询的开销:
show status like 'Last query cost';
优化器处理后主要得到执行计划。
MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN,就可以看到执行计划,例如:
EXPLAIN select name from user where id=1;
存储引擎
存储引擎主要负责数据的存储
不同的存储引擎提供了统一标准的api接口,供执行器操作。所以 mysql 的存存储引擎是动语选择的。
存储引擎存放文件详情
- 查看mysql的数据存放路径(每个数据库是一个文件夹)
show variables like 'datadir';
不同的存储引擎文件的存储形式如下:
- 不同的存储引擎存放文件说明
- frm 存放的是表结构定义的文件,每个存储引擎都有
- 不同的存储引擎存放数据的方式不一样,产生的文件也不一样
- innodb只有一个数据文件
- memory是放在内存中,故磁盘没有文件
- myisam 是两个文件,table_name.MYD 存放数据,table_name.MYI存放索引
各个存储引擎之间的对比
- MylSAM (3个文件)
应用范围比较小。表级锁定限制了读/写的性能,因此在Web和数据仓库配置中,它通常用于只读或以读为主的工作。
特点:- 支持表级别的锁(插入和更新会锁表)。
- 不支持事务。拥有较高的插入(insert)和查询(select)速度。
- 存储了表的行数(count速度更快)。
怎么快速向数据库插入100万条数据?
有一种先用MylSAM插入数据,然后修改存储引擎为InnoDB的操作。)
适合:只读之类的数据分析的项目。
-InnoDB (2个文件)
mysql 5.7中的默认存储引擎。InnoDB是一个事务安全(与ACID兼容)的MySQL 存储引擎,它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁(不升级 为更粗粒度的锁)和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将 用户数据存储在聚集索引中,以减少基于主键的常见查询的I/O。为了保持数据完整性, InnoDB还支持外键引用完整性约束。
特点:
- 支持事务
- 支持外键,因此数据的完整性、一致性更高。
- 支持行级别的锁和表级别的锁
- 支持读写并发写不阻塞读(MVCC)
- 特殊的索引存放方式,可以减少I/O,提升査询效率
详情请参考:https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
5.5.5之后,mysql默认的存储引擎是InnoDB
执行器(执行引擎)
执行引擎,利用存储引擎提供的相应的API来完成操作,将存储引擎返回的处理的数据返回给客户端
总结
MySQL分成三层:跟客户端对接的连接层,真正执行操作的服务层,和跟硬件打交道的存储引擎层。
- 连接层
我们的客户端要连接到MySQL服务器3306端口,必须要跟服务端建立连接,管理所有的连接,验证客户端的身份和权限,这些功能就在连接层完成。 - 服务层
连接层会把SQL语句交给服务层,这里面又包含一系列的流程:比如查询缓存的判断、根据SQL调用相应的接口,对我们的SQL语句进行词法和语 法的解析(比如关键字怎么识别,别名怎么识别,语法有没有错误等等)。然后就是优化器,MySQL底层会根据一定的规则对我们的SQL语句进行优化,最后再交给执行器去执行。 - 存储引擎
存储引擎就是我们的数据真正存放的地方,在MySQL里面支持不同的存储弓I擎。再往下就是内存或者磁盘。