MySQL体系认识及SQL的执行
MySQL体系结构
client connectors
- 各个语言连接数据库的方式
- 如：JDBC、ODBC、.NET
Connection Pool
- 整个接入的连接池
- 连接的处理、安全和用户名的管控、线程的处理
SQL Interface
- SQL 语句的入门，负责接收用户的 SQL 语句，返回结果
Parser
- 进行词法和语法的分析，解析成MySQL能够识别的语法
Optimizer
- 查询优化器，用来查询优化的，执行最优的执行计划
- 找到执行SQL语句的最优执行计划
- 物理优化、逻辑优化
文件系统
- 数据落盘
- 日志
Cache
- 负责存储MySQL查询的索引
工作原理
将SQL语句以及执行的结果做为K-V键值对缓存在caches模块中，后续基于接收的SQL语句，先去查询缓存,判断是否存在可用的记录集
缓存配置
- query_cache_type 设置我似查询缓存开关
- query_cache_size
- query_cache_limit
限制条件
- 缓存失效与表数据是一致的，表数据发生任何改动都将让缓存失效
- 需要手工开启，在MySQL5.6以后默认关闭了查询缓存功能
- 判断条件苛刻，SQL语句必须完全一致
- 多个空格也不可以
- 将传进来的语句作为Key，每次必须要和Key相等才可以
- 带来额外的性能消耗，数据的返回过程中若开启了缓存需先缓存SQL和结果
各存储引擎
- 存储引擎用于修饰表，一个表只能有一个存储引擎
- 默认的存储引擎 Innodb
- 存储引擎是插拔式的，可以随时进行加载和卸载
CSV引擎【表格存储】
特点
- 不能定义索引、列定义必须定义NOT NULL、不能设置自增列
- CSV表的数据的存储格式用 “,” 隔开，可直接编辑文件进行数据的编排
- 数据安全性低
应用场景 - 不适用大表和数据的在线处理
- 数据的快速导入导出
- 表格直接转换成CSV
Archive存储引擎【压缩协议】
特点
- 压缩协议进行数据的压缩，占用磁盘的空间少
- 只支持 insert 和 select 两种操作
- 只允许自增ID列建立索引
应用场景
- 数据备份系统（日志系统、文档归档）
- 大量设备高频的数据采集
Memory存储引擎【存储内存中、热点数据】
特点
- 数据都是存储在内存中，处理效率高，表大小限定默认16M
- 不支持大数据存储类型的字段如 blog、text varchar(n)【可变的】 – > char(n)【不可变的】
- 支持Hash索引，等值查询效率高
- 数据的可靠性低，重启或系统崩溃时数据丢失
应用场景
- 热点数据快速加载（功能类似缓存中间件）
- MySQL临时表存储（查询结果于内存中计算数据）
Myisam【8.0淡出了历史舞台】
特点
- 较快的数据插入和读取性能
- 数据存储既有较小的磁盘空间占用
- 支持表级别的锁，不支持事务
- 数据文件与索引文件分开存储，无主键索引之分
应用场景
- 只读应用或者以读为主的业务
Innodb
特点
- 支持事务【ACID ： 原子性、隔离性、一致性、持久性】
- 行级锁
- 聚集索引【主键索引】
- 外键支持，保证数据的完成性【带来额外的性能开销】
应用场景
- 无脑选择
SQL执行过程
DQL语句的执行
- 由我们的 SQL Interface 拦截到用户的 DQL 语句
- 去我们的缓存中查看是否存在
- 不存在，则进入我们解析器、优化器、执行器，最后交给我们的存储引擎
通讯阶段
- 通讯协议：TCP/IP，Unix Socket
- 通讯方式：长连接、半双工
- 思考：若一个SQL语句发出去很久没有回应怎么办？【show processlist / kill 18(杀死某个端口)】
执行计划
查看执行计划
- explain/desc SQL：查看生成的最优的执行计划
- set global optimizer_trace=‘enable=on’：打开记录SQL的执行计划开关【以一个json记录至optimizer_trace】
深入理解执行计划
执行计划-id【SQL执行的顺序】
- id 相同：执行顺序由上到下
- id 不同：id 值越大的优先级越高，优先被执行
执行计划-select_type【执行类型】
- Simlpe：简单查询
- PRIMARY：最外层查询
- SUBQUERY：子查询
- UNION：连接查询
- UNION RESULT：连接查询的结果集
执行计划-Table【数据表】
- 查询涉及到的表或者表的别名
执行计划-type
SQL执行数据的获取方式
SQL执行优化中一个很重要的指标，评测SQL好坏最直观的参数
取值：
- system
- const：唯一性索引、常量比较
- eq_ref：唯一性索引扫描
- ref：普通索引扫描
- range：基于索引的范围查找
- index：full index scan【基于全量的一个扫描】
- all：full all scan【基于全量的一个扫描】
partitions【分区信息】
possible key【可能用到的索引】
key【真正用到的索引】
执行计划-key_len【索引的长度】
- 索引使用的包括列长度
rows
- 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
- 越少越好
filtered
- 指返回结果的行占需要读取的行（rows）的百分比
- 越大越好，扫描的数据的准确性越高
数据返回
- 配置了MySQL的缓存机制，执行缓存操作
- SQL执行返回结果以增量的返回方法进行返回
- 开始生成第一条结果时，MySQL就开始往请求方逐步返回数据
- MySQL服务器无须保存过多的数据，浪费内存
- 用户体验好，若无须等待所有数据可将拿到的数据展示
