Mysql架构与内部模块

演示环境: MySQL 5.7 存储引擎:InnoDB

一､一条查询SQL是如何执行的?

客户端/服务器

程序或者工具要操作数据库,第一步跟数据库建立连接｡

通信协议

MySQL必须要运行一个服务,监听默认的端口(3306)｡支持多种通信协议:

TCP/IP协议:编程语言的连接模块都是用TCP协议连接到MySQL服务器的。

Unix Socket:在Linux服务器,不用通过网络协议,可连接到MySQL服务器,需用到服务器上的一个物理文件(mysql.sock)。

还有命名管道(Named Pipes)和内存共享(Share Memory)的方式。

MySQL使用半双工的通信方式,意味着要么是客户端向服务端发送数据,要么是服务端向客户端发送数据,这两个动作不能同时发生。所以客户端发送SQL语句给服务端的时候,(在一次连接里面)数据是不能分成小块发送的,如果发送给服务器的数据包过大,需调整MySQL服务器配置max_allowed_packet参数的值(默认是4M)。服务端也是一次性发送所有的数据。

连接方式

MySQL既支持短连接,也支持长连接。短连接就是操作完毕以后,马上close掉｡长连接可以保持打开,后面的程序访问的时候还可以使用这个连接。长时间不活动的连接,MySQL服务器会断开。默认的最大连接数是151个(5.7版本),最大是16384(2^14)。

查询缓存(Query Cache)

MySQL内部自带了一个缓存模块,默认是关闭的。主要是因为MySQL自带的缓存的应用场景有限,要求SQL语句必须一模一样,且表里面任何一条数据发生变化的时候,这张表所有缓存都会失效。在MySQL 5.8中,查询缓存已经被移除了。

语法解析和预处理(Parser & Preprocessor)

对SQL语句进行词法和语法分析和语义的解析。

词法解析:把一个完整的SQL语句打碎成一个个的单词。

语法解析:对SQL做一些语法检查,根据MySQL定义的语法规则,根据SQL语句生成一个数据结构,叫解析树。

预处理器(Preprocessor):检查生成的解析树,解决解析器无法解析的语义,比如检查表和列名是否存在等。

查询优化(Query Optimizer)与查询执行计划

查询优化器的目的

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

如何查看优化器工作

启用优化器的追踪(默认是关闭的),执行一个SQL语句,优化器会生成执行计划,可查询相关系统表查看分析过程及结果,查看完之后要关闭它。

优化器能处理的优化类型

比如多表关联查询时基准表的选择,条件过滤顺序,恒等或恒不等等式的处理,能否从索引取数据等。

执行计划

优化器最终会把解析树变成一个查询执行计划,是一个数据结构。可通过在SQL语句前面加上EXPLAIN查看执行计划的信息。

存储引擎(Storage Engine)

基本介绍

在关系型数据库里面,数据是放在表里面的,表的存储结构由存储引擎决定,也可把存储引擎叫做表类型。MySQL支持多种存储引擎,是插件式的。不同的存储引擎通过提供不同的存储机制、索引方式、锁定水平等功能,来满足不同的业务需求。

查看存储引擎

查看数据库表的存储引擎可使用show table status from \training`;`命令。创建表时可指定存储引擎,创建表之后还可以修改存储引擎。

常见存储引擎比较

MyISAM:5.5版本之前默认的存储引擎,表级锁定,不支持事务,拥有较高的插入和查询速度,存储了表的行数,适合只读之类的数据分析的项目。

InnoDB:5.5版本之后默认的存储引擎,支持事务,支持外键,支持行级别的锁和表级别的锁,支持读写并发,写不阻塞读,特殊的索引存放方式,可以减少IO,提升查询效率,适合经常更新的表,存在并发读写或者有事务处理的业务系统。

Memory:将所有数据存储在RAM中,读写速度很快,但数据库重启或者崩溃,数据会全部消失,只适合做临时表,默认使用哈希索引。

CSV:表实际上是带有逗号分隔值的文本文件,不允许空行,不支持索引,格式通用,可以直接编辑,适合在不同数据库之间导入导出。

Archive:用于存储和检索大量很少引用的历史、存档或安全审计信息,不支持索引,不支持update delete。

执行引擎(Query Execution Engine),返回结果

执行引擎利用存储引擎提供的相应API来完成对存储引擎的操作,最后把数据返回给客户端,即使没有结果也要返回。

二､MySQL体系结构总结

架构分层

总体上可把MySQL分成三层:

连接层:管理连接,权限验证。

服务层:包括词法分析,语法分析,执行计划生成,索引选择,操作引擎,返回结果。

存储引擎层:InnoDB、MyISAM等存储数据,提供读写接口。

各模块功能

Connector(连接):管理需要缓冲的资源等。

SQL Interface:用来接收用户的SQL命令,返回用户需要的查询结果。

Parser:用来解析SQL语句。

Optimizer:查询优化器。

Cache and Buffer:查询缓存及其他缓存。

Pluggable Storage Engines:插件式存储引擎,提供API给服务层使用。

三､一条更新SQL是如何执行的?

基本流程

也要经过解析器、优化器的处理,最后交给执行器。区别在于拿到符合条件的数据之后的操作。

InnoDB中的操作

InnoDB里面有个内存的缓冲池(buffer pool),对数据的更新先写入到buffer pool里面,内存的数据页和磁盘数据不一致的时候叫脏页。InnoDB有专门把buffer pool的数据写入到磁盘的线程,叫刷脏。

为了保证数据的持久性,InnoDB引入了redo log(重做日志),记录对内存数据的修改操作,如果服务器出问题,从这个日志文件里面读取数据恢复数据。MySQL Server层也有一个日志文件binlog,以事件的形式记录了所有的DDL和DML语句,可用来做主从复制和数据恢复。

更新语句执行流程示例

以update user set name='jim'where name='666';为例:

先查询到这条数据,如果有缓存,也会用到缓存。

把name改成jim,然后调用引擎的API接口,写入这一行数据到内存,同时记录redo log,此时redo log进入prepare状态,然后告诉执行器,执行完成了,可以随时提交。

执行器收到通知后记录binlog,然后调用存储引擎接口,设置redo log为commit状态。

更新完成。

两阶段提交(XA)的原因

如果只写redo log,没写bin log时MySQL重启,会出现数据不一致的情况,所以在写两个日志的情况下,binlog充当事务的协调者,通知InnoDB来执行prepare或commit或者rollback。

四､MySQL索引原理详解

索引是什么?

是数据库管理系统(DBMS)中一个排序的数据结构,以协助快速查询、更新数据库表中数据。

索引类型

普通(Normal):也叫非唯一索引,没有任何的限制。

唯一(Unique):要求键值不能重复,主键索引是特殊的唯一索引,要求键值不能为空。

全文(Fulltext):针对比较大的数据,只有文本类型的字段才可以创建全文索引。

索引存储模型推演

二分查找:可考虑用有序数组作为索引的数据结构,但更新数据时会有问题,所以引入链表,又因为单链表查找效率不够高,诞生了二叉查找树(BST)。

二叉查找树(BST Binary Search Tree):特点是左子树所有的节点都小于父节点,右子树所有的节点都大于父节点,但在最坏情况下时间复杂度会退化成O(n)。

平衡二叉树(AVL Tree)(左旋、右旋):左右子树深度差绝对值不能超过1,通过左旋和右旋操作保持平衡,可作为索引结构,但存在节点存储数据少导致磁盘交互次数多的问题。

多路平衡查找树(B Tree)(分裂、合并):分叉数(路数)永远比关键字数多1,通过分裂和合并操作保持平衡,检索效率比AVL树高。

B+树(加强版多路平衡查找树):是B Tree的变种,关键字的数量是跟路数相等的,根节点和枝节点中都不会存储数据,只有叶子节点才存储数据,每个叶子节点增加了一个指向相邻叶子节点的指针,具有扫库、扫表能力更强,磁盘读写能力更强,排序能力更强,效率更加稳定等优势。

索引方式:在Navicat工具中,创建索引方式有HASH和B+Tree等,HASH索引有查询速度快但不能用于排序、只能支持等值查询、字段重复值多时效率会降低等特点,InnoDB中不能显示地创建一个哈希索引,Memory存储引擎可以使用Hash索引。

B+Tree落地形式

MyISAM:索引和数据是两个独立的文件,在B+Tree里面,叶子节点存储的是数据文件对应的磁盘地址。

InnoDB:以主键为索引来组织数据的存储,索引文件和数据文件是同一个文件,都在.ibd文件里面,主键索引的叶子节点上直接存储了数据,其他索引检索数据时需先找到主键值再到主键索引叶子节点拿数据。

索引使用原则

列的离散(sàn)度:列的重复值越多,离散度就越低,不建议在离散度低的字段上建立索引。

联合索引最左匹配:联合索引在B+Tree中是复合的数据结构,按照从左到右的顺序来建立搜索树,建立联合索引时要把最常用的列放在最左边,查询条件没有最左边的列时用不到索引。

覆盖索引:非主键索引先通过索引找到主键索引的键值,再通过主键值查出索引里面没有的数据叫回表,如果select的数据列只用从索引中就能够取得,不必从数据区中读取,这时候使用的索引就叫做覆盖索引,可提升查询效率。

索引的创建与使用

在什么字段上索引?

在用于where判断order排序和join的(on)字段上创建索引,索引的个数不要过多,区分度低的字段不要建索引,频繁更新的值不要作为主键或者索引,随机无序的值不建议作为主键索引,创建复合索引而不是修改单列索引。

什么时候索引失效?

索引列上使用函数、表达式计算,字符串不加引号出现隐式转换,like条件中前面带%,负向查询(部分情况),这些情况可能导致索引失效,最终是否使用索引由优化器说了算,优化器是基于cost开销的优化器。

五､MySQL性能优化思路和工具

优化思路

从客户端连接、缓存、解析器、优化器、存储引擎等环节入手优化。

连接--配置优化

服务端

可增加服务端的可用连接数,修改max_connections的大小,及时释放不活动的连接。

客户端

可减少从服务端获取的连接数,引入连接池,如ORM层面或使用专用的连接池工具。

缓存--架构优化

缓存

可引入第三方的缓存服务如Redis来减少数据库压力。

主从复制

主从复制可实现数据一致性,通过binlog实现,涉及I/O线程、log dump线程和SQL线程。可实现读写分离,减轻数据库服务器的访问压力。

分库分表

垂直分库减少并发压力,水平分表解决存储瓶颈。

优化器--SQL语句分析与优化

慢查询日志slow query log

打开慢日志开关,可设置执行超过多长时间的SQL才记录到慢日志,可通过mysqldumpslow工具分析慢查询日志。

SHOW PROFILE

可查看SQL语句执行的时候使用的资源,如CPU、IO的消耗情况。

EXPLAIN执行计划

通过EXPLAIN可模拟优化器执行SQL查询语句的过程,分析语句或者表的性能瓶颈,EXPLAIN的结果有很多字段,可从id、select_type、type等多个方面分析。

存储引擎

存储引擎的选择

为不同的业务表选择不同的存储引擎,如查询插入操作多的业务表用MyISAM,临时数据用Memory,常规的并发大更新多的表用InnoDB。

字段定义

原则是使用可以正确存储数据的最小数据类型,如整数类型选择合适的字节数,字符类型根据情况选择char或varchar,不要用外键、触发器、视图,大文件不要用数据库存储,可考虑表拆分或字段冗余。

总结:优化体系

从SQL与索引、存储引擎与表结构、数据库架构、MySQL配置、硬件与操作系统等维度优化,同时业务层面的优化也不能忽视。