mysql中一条sql的执行过程
连接
mysql服务的默认端口是3306
查看mysql当前有多少连接
show global status like 'Thread%'
字段 | 含义 |
---|---|
Threads_cached | 缓存中的线程连接数 |
Threads_connected | 当前打开的线程数 |
Threads_created | 为处理连接创建的线程数 |
Threads_running | 非睡眠状态的连接数,通常指并发连接数 |
查看mysql连接超时时间
客户端每产生一个连接或者一个会话,服务端就会创建一个线程来处理。MySQL会把长时间不活动的连接自动断开。
show global variables like 'wait_timeout'; -- 非交互式超时时间,如JDBC程序
show global variables like 'interactive_timeout'; -- 交互式超时时间,如数据库工具
默认都是28800秒,即8小时
查看MySQL允许的最大连接数(并发数)
5.7版本默认是151,最大可以设置为100000
show variables like 'max_connections';
参数级别说明
myslq中的参数分为session和global级别,分别是当前会话和全局生效,并不是所有参数都有两个级别。
当语句不带参数时,默认是session级别,包括查询和修改
show variables like 'autocommit';
set autocommit = on;
临时修改建议使用session级别,如果要在其他会话中生效,需显式的加上global
查询缓存
mysql中自带了一个缓存模块,默认是关闭状态。
show variables like 'query_cache%';
默认关闭即表示mysql不推荐使用。
原因:
- 场景有限,SQL语句必须一模一样才会被认为是同一个sql,对一个空格,字母大小写不同都不认为是同一个sql
- 当数据更新时,所有缓存都会失效,对有大量数据更新的应用也不适合
查询缓存在MySQL8.0版本中已被移除。
语法解析和预处理
这里是对语句基于SQL语法进行词法和语法分析以及语义的解析。
解析器(Parser)
词法解析
将一个完整的SQL语句打碎成一个一个的单词
select name from user where id = 1;
这个sql语句会被打碎成8个符号,每个符号是什么类型,从哪里开始到哪里结束。
语法解析
语法解析会对SQL进行语法检查,然后根据MySQL定义的语法规则,根据sql语句生成一个数据结构。这个数据结构称为解析数(select_lex)。
任何数据库中间件,要解析sql完成路由功能,也必须要有词法和语法分析功能,比如MyCat,Sharding-JDBC(Druid Parser)。世面上也有许多开源的词法分析工具,如LEX,Yacc
预处理器(Preprocessor)
预处理器会检查解析器生成的解析数,解决解析器无法解析的语义,比如,他会检查表和列名是否存在,检查名字和别名,保证没有歧义。
预处理之后会得到一个新的解析树。
查询优化器(Query Optimizer)
一条SQL可以有很多种执行方式,最终返回相同的结果。
查询优化器的目的是根据解析树生成不同的执行计划(Execution Plan),然后选择一种最优的执行计划,MySQL里面使用的是基于开销(cost)的优化器,哪种执行计划开销最小,就用哪种。
查看查询的开销
show status like 'Last_query_cost';
优化器能处理哪些优化类型?
简单的例子:
- 多表关联查询时,以哪个表的数据作为基准表
- 当有多个索引可以使用时,选择哪个索引
优化器不是万能呢,不是所有语句都是自动优化,也不是每次都能选择带最优的执行计划。
优化器在优化完之后,会把解析树编程一个查询执行计划
,查询执行计划是一个数据结构。
在SQL语句之前加上EXPLAN
就可以看到执行计划的信息
EXPLAN select name from user where id = 1;
如果要得到详细的信息,可以使用FORMAT=JSON
或者optimizer trace
EXPLAN FORMAT=JSON select name from user where id = ;1
存储引擎
存储引擎介绍
任何一个存储引擎都有一个frm文件,这个是表结构定义的文件
不同的存储引擎存放数据的方式不一样,产生的文件也不一样,InnoDB是一个(ibd),memory没有,myisam是两个(MYD,MYI)
存储引擎选择
一张表的存储引擎,是在创建的时候指定的,使用关键字ENGINE
CREATE TABLE 'user_innodb' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(255) DEFAULT NULL,
'phone' varchar(11) DEFAULT NULL,
PRIMARY KEY ('id'),
KEY 'comidx_name_phone' ('name','phone')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
在不指定的时候,数据库会使用默认的存储引擎,5.5.5之前默认的存储引擎是MyISAM,之后是InnoDB。
存储引擎比较
https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
MyISAM(3个文件)
应用范围小。表级锁限制了读写的性能。通常用于只读或以读为主的工作。
特点:
- 支持表级别的锁(插入和更新会锁表)。不支持事务
- 拥有较高的插入和查询速度
- 存储了表的行数(count速度更快)
适合:
- 只读之类的数据分析项目
InnoDB(2个文件)
MySQL5.7的默认存储引擎。是一个事务安全(兼容ACID)的存储引擎,具有提交、回滚、崩溃回复功能来保护数据。
行级锁和Oracle一致,非锁读提高了多用户并发性和性能。
将用户数据存储在聚集索引中,以减少基于主键的常见查询的I/O。
支持外键引用保证数据完整性。
特点:
- 支持事务、外键,数据的完整性和一致性更高
- 执行行级锁和表级锁
- 支持读写并发,写不阻塞读(MVCC)
适合:
- 经常更新的表,存在并发读写或者有事务处理的业务系统
Memory(一个文件)
将所有数据存储在RAM中,以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。
这个引擎的使用案例正在减少,InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中,而ndbcluster为大型费不是数据集提供了快速的键值查找。
特点:
- 把数据放在内存中,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失,只适合做临时表
如何选择存储引擎
- 对数据一致性要求比较高,需要事务支持,可以选择InnoDB
- 数据查询多更新少,对查询性能要求比较高,可以选择MyISAM
- 需要一个用于查询的临时表,可以选择Memory
- 也可以根据自己的需求,根据开发规范自己开发存储引擎
每个存储引擎都有自己的服务
show engine innodb status;
执行引擎
执行引擎利用存储引擎提供的API去执行执行计划,最后将结果返回给客户端。