先来了解下MySQL 发展历史
1996年，MySQL 1.0发布。它的历史可以追溯到1979 年，作者Monty 用BASIC 设计的一个报表工具
1996年10月，3.11.1发布。MySQL没有2.x版本
2000年，ISAM 升级成MyISAM 引擎。MySQL开源。
2003年，MySQL 4.0发布，集成InnoDB存储引擎
2005年，MySQL 5.0版本发布，提供了视图、存储过程等功能
2008年，MySQL AB公司被Sun 公司收购，进入Sun MySQL 时代
2009年，Oracle 收购Sun公司，进入Oracle MySQL时代
2010年，MySQL 5.5发布，InnoDB 成为默认的存储引擎
2016年，MySQL 发布 8.0.0版本
因为MySQL 是开源的(也有收费版本)，所以在稳定版本基础上发展了很多分支，最为熟悉的就是MariaDB
Percona Server 是 MySQL重要的分支之一，它基于InnoDB 存储引擎的基础上，提升了性能和易管理性，最后形成了增强版的XtraDB 引擎，可以用来更好地发挥服务器硬件上的性能
MySQL 工作流程
通信协议
首先，MySQL 必须先要运行一个服务，监听默认3306 端口
MySQL 是支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接
通信类型：同步或者异步
同步通信依赖于被调用方，应用操作数据库，线程会阻塞，等待数据库返回
一般是一对一，很难做到一对多的通信
异步跟同步相反：
异步可以避免应用阻塞等待，但是不能节省SQL 执行的时间
如果异步存在并发，每一个SQL 执行都要单独建立一个连接，避免数据混乱，但是这儿样会给服务器带来巨大的压力，一般不建议使用。如果要异步，必须使用线程池，排队从连接池获取连接而不是创建新连接
一般来说我们连接数据库都是同步连接
连接方式：长连接或者短连接
MySQL 既支持短连接，也支持长连接。短连接就是操作完毕以后马上close 掉。 长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接
保持长连接会消耗内存。长时间不活动的连接，MySQL服务器会断开
show global variables like 'wait_timeout'; --非交互式超时时间，如JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具
默认都是8个小时
MySQL 有多少连接？
show global status like 'Thread%';
Threads_cached:缓存中的线程连接数
Threads_connected:当前打开的连接数
Threads_created:为处理连接创建的线程数
Threads_running:非睡眠状态的连接数，通常指并发连接数
每产生一个连接或者一个会话，在服务端就会创建一个线程来处理
查看当前连接的状态
Sleep 线程正在等待客户端，以向它发送一个新语句
Query 线程正在执行查询或往客户端发送数据
Locked 该查询被其它查询锁定
Copying to tmp table on disk 临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式，以节约存储器
Sending data 线程正在为 SELECT 语句处理行，同时正在向客户端发送数据
Sorting for group 线程正在进行分类，以满足 GROUP BY要求
Sorting for order 线程正在进行分类，以满足 ORDER BY要求
MySQL 服务允许的最大链接数是多少？
在 5.7 版本中默认是 151个，最大可以设置成16384
MySQL 支持的通信协议
第一种Unix Socket，比如我们在Linux 服务器上，如果没有指定-h 参数，用的就是socket 方式登录,不用通过网络协议也可以连接MySQL 服务器
第二种TCP/IP 协议 mysql -h192.168.8.211 -uroot -p123456
在代码连接模块中用的都是TCP 协议连接到MySQL 服务器
另外还有两种方式，只能在Windows 上面使用，一般用的很少。命名管道和内存共享
通信方式
MySQL 使用的是半双工通信方式
要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，两个动作不能同时发生。所以客户端发送SQL 语句给服务端的时候，在一次连接里面数据是不能分成小块发送的，不管你的SQL 语句有多大，都是一次性发送
例如我们用mybatis 动态sql 生成了一个批量插入的语句，插入10 万条数据，values 后面跟了一长串内容，或者where 条件in 里面的值太多，会出现问题。这个时候必须要调整MySQL 服务器配置max_allowed_packet 参数值(默认是4M)，把它调大否则报错。另外，对于服务端来说也是一次性发送所有数据，不能因为你已经取到了想要的数据就中断操作，这个时候会对网络和内存产生较大的消耗。所以我们一定要在程序里面避免不带limit 这种操作。
以上是客户端与服务器建立连接的流程操作
查询缓存
MySQL 内部自带了一个缓存模块，默认是关闭的；MySQL 是不推荐使用这个缓存的
查询缓存的失效率非常频繁，只要有对一个表进行更新，这个表上的所有查询缓存都会被清空。因此极大可能缓存起来的结果集，还没有使用就被一个更新清空了。对于更新频率大的数据库来说，查询缓存的命中率比较低
需要注意，MySQL 8.0 版本直接把查询缓存这个模块删掉了！！！
语法解析和预处理
一条SQL 语句如何被识别的？或者它是怎么被识别的？
这块需要MySQL 的parser 解析器和Preprocessor 预处理模块，会对SQL 语法进行词法、语法分析和语意的解析
词法解析
词法解析会把一个完整的SQL 语句拆解成一个个单词
select name from user from id = 1;
-- 它会拆解成8个符号，每个符号是什么类型，从哪里开始到哪里结束语法解析
语法解析会对SQL 做一些语法检查，根据MySQL 定义的语法规则，生成一个数据结构，这个结构叫做解析树(select_lex)
预处理器
SQL 语句中如果表名或者字段不存在时，会报错，预处理器就是检查这部分，它会检查生成的解析树，解决解析器
预处理之后得到一个新的解析树
查询优化器
一条SQL 语句可以有很多种执行方式，最终返回的结果是等价的。查询优化器会根据解析树生成把不同的执行计划,然后选择一种最优的执行计划
可以使用这个命令：show status like 'Last_query_cost'; 查看查询的开销
优化器处理哪些优化类型？例如：
1. 当我们对多张表进行关联查询的时候，以哪个表数据作为基准表
2. 有多个索引可以使用的时候，选择哪个索引
优化器是怎么得到执行计划的
优化器的追踪默认是关闭的，需要开启，之所以默认关闭因为其会消耗性能，因为它要把优化分析的结果写到表里面，所以不要轻易开启
SHOW VARIABLES LIKE 'optimizer_trace';
set optimizer_trace='enabled=on';执行一条SQL 语句，然后查看优化器分析的过程，它是一条json 数据
通过： select * from information_schema.optimizer_traceG 查看
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `order_master`.`order_id` AS `order_id`,`order_master`.`buyer_name` AS `buyer_name`,`order_master`.`buyer_phone` AS `buyer_phone`,`order_master`.`buyer_address` AS `buyer_address`,`order_master`.`buyer_openid` AS `buyer_openid`,`order_master`.`order_amount` AS `order_amount`,`order_master`.`order_status` AS `order_status`,`order_master`.`pay_status` AS `pay_status`,`order_master`.`create_time` AS `create_time`,`order_master`.`update_time` AS `update_time` from `order_master` limit 1"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"table_dependencies": [
{
"table": "`order_master`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"rows_estimation": [
{
"table": "`order_master`",
"table_scan": {
"rows": 8,
"cost": 1
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`order_master`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 8,
"access_type": "scan",
"resulting_rows": 8,
"cost": 2.6,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 8,
"cost_for_plan": 2.6,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`order_master`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"table": "`order_master`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}主要分文三部分，准备阶段、优化阶段和执行阶段；
expanded_query 是优化后的SQL 语句；
considered_execution_plans 里面列出了所有的执行计划
优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。这个查询执行计划不一定是最优执行计划，因为MySQL 也有可能覆盖不到所有的执行计划
MySQL 提供了一个执行计划的工具，在SQL 语句前面加上EXPLAIN 就可以看到执行计划的额信息
*注意 Explain 的结果也不一定最终执行的方式。
存储引擎
在关系型数据库里面，数据是放在Table 里面的，可以把它理解成Excel 电子表格形式，所以我们的表存储数据的同时，还要组织数据的存储结构，这个存储结构是由存储引擎决定的，也可以把存储引擎叫做表类型
在MySQL 里面支持多种存储引擎，它们是可以替换的，索引叫做插件式存储引擎
查看存储引擎
show table status from `数据库名字`
在 MySQL 里面，我们创建的每一张表都可以指定它的存储引擎，而不是一个数据库只能使用一个存储引擎
存储引擎的使用是以表为单位的。而且创建表之后还可以修改存储引擎。
存储引擎在服务器上是怎么存储的？
先要找到数据库存放数据的路径：show variables like 'datadir';
常见的存储引擎
MyISAM 和InnoDB 是用的最多的两个存储引擎，在MySQL 5.5 版本之前，默认的存储引擎是MyISAM，它是MySQL 自带的。创建表的时候不指定存储引擎，它就会使用MyISAM 作为存储引擎
MyISAM 的前身是ISAM(Indexed Sequential Access Method:利用索引，顺序存取数据的方法)。5.5 版本之后默认的存储引擎改成了InnoDB，它是第三方公司为MySQL 开发的。InnoDB支持事务，支持行级别的锁，对于业务一致性要求高的场景来说更适合
通过这个命令查看数据库对存储引擎的支持： show engines ;
XA 协议用来实现分布式事务(分为本地资源管理器，事务管理器)。
Savepoints 用来实现子事务(嵌套事务)。创建了一个 Savepoints之后，事务就可以回滚到这个点，不会影响到创建Savepoints 之前的操作
特性
MyISAM
应用范围比较小，表级锁定限制了读/写的性能，因此在Web 和数据仓库配置中，它通常用于只读或以读为主的工作。
InnoDB
mysql 5.7 中的默认存储引擎。InnoDB是一个事务安全(与ACID 兼容)的MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据
Memory
将所有数据存储在RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎
CSV
它的表实际上是带有逗号分隔值的文本文件。csv表允许以 csv 格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据
Archive
不支持索引，使用的很少
如何选择存储引擎
如果对数据一致性要求比较高，需要事务支持，可以选择 InnoDB。
如果数据查询多更新少，对查询性能要求比较高，可以选择 MyISAM。
如果需要一个用于查询的临时表，可以选择 Memory
执行引擎
执行引擎使用执行计划去操作存储引擎，利用存储引擎提供的相应API 来完成操作
总结
一条查询SQL 的执行顺序
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
