mysql

young 1,256 2022-06-21

mysql中一条sql的执行过程

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不推荐使用。

原因:

  1. 场景有限,SQL语句必须一模一样才会被认为是同一个sql,对一个空格,字母大小写不同都不认为是同一个sql
  2. 当数据更新时,所有缓存都会失效,对有大量数据更新的应用也不适合

查询缓存在MySQL8.0版本中已被移除。

语法解析和预处理

这里是对语句基于SQL语法进行词法和语法分析以及语义的解析。

解析器(Parser)

词法解析

将一个完整的SQL语句打碎成一个一个的单词

select name from user where id = 1;

这个sql语句会被打碎成8个符号,每个符号是什么类型,从哪里开始到哪里结束。

语法解析

语法解析会对SQL进行语法检查,然后根据MySQL定义的语法规则,根据sql语句生成一个数据结构。这个数据结构称为解析数(select_lex)。

mysql语法分析器

任何数据库中间件,要解析sql完成路由功能,也必须要有词法和语法分析功能,比如MyCat,Sharding-JDBC(Druid Parser)。世面上也有许多开源的词法分析工具,如LEX,Yacc

预处理器(Preprocessor)

预处理器会检查解析器生成的解析数,解决解析器无法解析的语义,比如,他会检查表和列名是否存在,检查名字和别名,保证没有歧义。

预处理之后会得到一个新的解析树。

查询优化器(Query Optimizer)

一条SQL可以有很多种执行方式,最终返回相同的结果。

查询优化器的目的是根据解析树生成不同的执行计划(Execution Plan),然后选择一种最优的执行计划,MySQL里面使用的是基于开销(cost)的优化器,哪种执行计划开销最小,就用哪种。

查看查询的开销

show status like 'Last_query_cost';

优化器能处理哪些优化类型?

简单的例子:

  1. 多表关联查询时,以哪个表的数据作为基准表
  2. 当有多个索引可以使用时,选择哪个索引

优化器不是万能呢,不是所有语句都是自动优化,也不是每次都能选择带最优的执行计划。

优化器在优化完之后,会把解析树编程一个查询执行计划,查询执行计划是一个数据结构。

在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去执行执行计划,最后将结果返回给客户端。