系列文章:MySQL:架构、事务与优化 的第 1 篇。
Mysql基础架构图
简单介绍上图相关模块功能
连接器:管理连接,权限验证
查询缓存:命中缓存则直接返回结果
语法解析:词法分析,语法分析
查询优化:执行计划生成,索引选择
执行器:操作引擎,返回结果
存储引擎:存储数据,提供读写结构。
连接器
我们需要连接到mysql服务端才能进行各种操作。
# 使用密码登录到时候我们会使用这种格式。
mysql -h$ip -P$port -u$user -p
执行上述命令会提示输入密码。输入完密码后就进入了mysql。
如果用户名或者密码输入错误,会提示一个
Access denied for user到错误,然后客户端程序结束执行。如果用户名和密码认证通过,连接器会去权限表中查询该用户拥有的所有权限。此后该连接里面的权限逻辑判断都会依赖认证成功时候读取到的权限。
因此当修改用户的权限之后,如果被修改用户的在登录状态是不会修改已经连接的权限。需要让被修改权限用户从新登录。
- 实际上连接的方式有多种,上面通过密码的方式只是其中的一种方式。当客户端与服务端mysql进程进程建立连接,服务器的进程就会创建一个单独的线程来专门处理与这个客户端的交互,当该客户断开与服务端连接时,服务端并不会马上销毁对应的交互线程,而是缓存起来。当另一个新的客户端连接进来的时候,在把这个缓存的线程分配给新的客户端。这样就避免的频繁创建和修改线程,节省系统的开销。但是连接线程多了也会影响服务端,所以也有默认的参数限制客户端的连接数量。
# 这里是查询服务端存在的连接。
SHOW PROCESSLIST;
我使用的阿里云的数据库,这里展示下结果集。这里需要注意,登录的账号需要有PROCESS的权限否则只能看到自己的连接信息(线程),我这里采用的root所以看到全部到。

连接完成后如果没有执行后续操作则 Command会显示Sleep的状态,长时间Sleep会导致连接自动断开。默认的时间是8小时,也就是参数
wait_timeout。当客户端连接断开后,若客户端再次发出请求就会提示一个
Lost connection to MySQL server during query,此时就只能重新建立连接了。
半双工
- MySQL客户端/服务端通信协议是
“半双工”的:在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。一旦一端开始发送消息,另一端要接收完整个消息才能响应它,所以我们无法也无须将一个消息切成小块独立发送,也没有办法进行流量控制。 - 服务器响应给用户的数据通常会很多,由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。因而在实际开发中,尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量是一个非常好的习惯,这也是查询中尽量避免使用
SELECT *以及加上LIMIT限制的原因之一。
长连接与短连接
在数据库里面,长连接是指数据库建立连接,如果客户端有持续的请求则使用同一个连接,短连接是指每次执行几次短的查询就断开连接,下次查询再次创建连接。由于创建的过程复杂,一般来说尽量减少创建连接的动作,尽可能使用长连接。特别是在开发的时候出现大伙突然连接不上测试库,此时就检查下连接是不是太多了。因为连接多了会导致内存消耗特别快。
- mysql的执行过程中临时使用的内存管理是在连接线程对象里面,服务端断开的时候才会释放,为了避免内存消耗过大,每次执行较大的操作之后,可以通过
mysql_reset_connection来重新初始化连接资源。这个操作是不需要重新做权限,只是恢复到创建的初始状态。
- mysql的执行过程中临时使用的内存管理是在连接线程对象里面,服务端断开的时候才会释放,为了避免内存消耗过大,每次执行较大的操作之后,可以通过
查询缓存
一个不被建议使用的功能,在新版的8.0中已经被删除了。总结就是弊大于利。
- 建立完成后,假设执行一次
SELECT语句,执行逻辑就会到第二部查询缓存。mysql拿到一个查询之后,会先到缓存中寻找释放有完全对应的,因为之前执行的查询语句和结果集会直接被缓存起来,以Keys-value的形式。keys是查询语句-value是查询结果集。如果能命中这个key则直接返回value。咋一看挺有用的,但实际上查询缓存的实效的太频繁。并且keys的命中条件太不聪明了,如果两次查询语句在任何的字符上存在不通(空格、大小写)都不会缓存命中。为什么会频繁失效,因为如果对查询语句中的表进行更新,就会导致缓存失效。比如INSERT、UPDATE 、DELETE 、TRUNCATE TABLE 、ALTER TABLE等等就会导致缓存失效。
语法解析
如果没有命中查询缓存,就要开始执行sql语句了。mysql需要做什么,因此需要对sql进行解析。MySQL通过关键字将SQL语句进行解析,并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。
- 本质上发送过来的是一个文本信息,这里就需要对该文本信息进行编译,涉及到
词法解析、语法解析、语义解析等阶段。需要注意的是,这里会对sql语句进行一些检查,比如设计的相关表、表字段。这里检查通过的才会进入查询优化模块。 - 根据SQL语言的功能可以划分成4个部分
- DDL,英文叫做 Data Definition Language,也就是数据定义语言,它用来定义我们的数据库对象,包括数据库、数据表和列。通过使用 DDL,我们可以创建,删除和修改数据库和表结构。
- DML,英文叫做 Data Manipulation Language,数据操作语言,我们用它操作和数据库相关的记录,比如增加、删除、修改数据表中的记录。
- DCL,英文叫做 Data Control Language,数据控制语言,我们用它来定义访问权限和安全级别。
- DQL,英文叫做 Data Query Language,数据查询语言,我们用它查询想要的记录,它是 SQL 语言的重中之重。在实际的业务中,我们绝大多数情况下都是在和查询打交道,因此学会编写正确且高效的查询语句,是学习的重点。
查询优化
通过之前的语法解析,基本可以判定语法树是合法的,此时mysql就知道文本内容要做什么。
- mysql会对查询语句进行优化,优化的结果就是生成一个执行计划,这个计划会表明使用了那些查询索引,表之间的连接是什么样子的。有时候出现多种执行方式,只是效率不通,优化器会决定使用哪一个执行方案,这个时候就涉及到
EXPLAIN语句,该语句可以查看sql到执行计划,这将涉及到查询优化。 - MySQL的查询优化器是一个非常复杂的部件,它使用了非常多的优化策略来生成一个最优的执行计划。
- 重新定义表的关联顺序(多张表关联查询时,并不一定按照SQL中指定的顺序进行,但有一些技巧可以指定关联顺序)
- 优化函数
- 提前终止查询(比如:使用Limit时,查找到满足数量的结果集后会立即终止查询)
- 优化排序(在老版本MySQL会使用两次传输排序,即先读取行指针和需要排序的字段在内存中对其排序,然后再根据排序结果去读取数据行,而新版本采用的是单次传输排序,也就是一次读取所有的数据行,然后根据给定的列排序。对于
I/O密集型应用,效率会高很多。
执行器
进入到执行sql到环节。
- 通过之前到查询优化,进入执行阶段,此时会先判断该连接用户是否有相关表的权限,如果没有会返回权限错误。如果有权限就会打开表,执行器会根据表定义的引擎去使用这个引擎提供的接口完成流程。
存储引擎
关于存储引擎,这里明确提醒。不是三种也不是四种。而是多种因为随时可能出现新的引擎。
- 我们常用的就是
InnoDB和MyISAM,其它的不常见就不做过多介绍。mysql现在默认的引擎是InnoDB所以主要也是了解InnoDB
# 查看当前服务支持的存储引擎
SHOW ENGINES;

innodb
- 插入缓冲(insert buffer),
- 二次写(double write),
- 自适应哈希索引(ahi),
- 预读(read ahead)
- 支持事务操作,具有事务 ACID 隔离特性,默认的隔离级别是
可重复读(repetable-read)、通过MVCC(并发版本控制)来实现的。能够解决脏读和不可重复读的问题。 - InnoDB 支持外键操作。
- InnoDB 默认的锁粒度
行级锁,并发性能比较好,会发生死锁的情况。 - 和 MyISAM 一样的是,InnoDB 存储引擎也有
.frm文件存储表结构定义,但是不同的是,InnoDB 的表数据与索引数据是存储在一起的,都位于 B+ 数的叶子节点上,而 MyISAM 的表数据和索引数据是分开的。 - InnoDB 有安全的日志文件,这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题,保证数据的一致性。
- InnoDB 和 MyISAM 支持的索引类型相同,但具体实现因为文件结构的不同有很大差异。
- 增删改查性能方面,如果执行大量的增删改操作,推荐使用 InnoDB 存储引擎,它在删除操作时是对行删除,不会重建表。
myisam与innodb
- InnoDB支持事物,而MyISAM不支持事物
- InnoDB支持行级锁,而MyISAM支持表级锁
- InnoDB支持MVCC, 而MyISAM不支持
- InnoDB支持外键,而MyISAM不支持
- InnoDB不支持全文索引,而MyISAM支持。
索引
主键
- 数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。
超键
- 在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键
- 是最小超键,即没有冗余元素的超键。
外键
- 在一个表中存在的另一个表的主键称此表的外键。
为什么要使用自增列作为主键
自增ID有序,会按顺序往最后插入,而UUID无序,随机生成,随机插入,会造成频繁页分裂,内存碎片化,大量随机IO
如果没有显示的指定主键,则InnoDB会选择一个不包含NULL值的唯一索引作为主键索引。如果不满足条件,InnoDB会选择内置6字节长度的ROWID作为隐含的聚集索引。
InnoDB数据记录本身存放主索引(B+Tree)的叶子结点上面,同一个叶子结点内的各条数据是按照主键顺序存放。因此每次插入数据的时候,mysql会根据其主键插入适当的位置,如果页达到装载因子条件,则开辟一个新的页。
使用非自增,每次插入的值都是几乎无序的,导致叶节点不够紧凑,频繁移动分叶造成大量碎片。增加维护工作。
InnoDB引擎的自增值,其实是保存在了内存里,并且到了MySQL 8.0版本后,才有了“自增值持久化”的能力。
索引是什么
在了解索引之前,大概理解下mysql存储数据的大小划分
表空间 -> 段 -> 区 -> 数据页。
索引是对数据库表中一或多个列的值进行排序的结构,是帮助MySQL高效获取数据的数据结构
索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。
索引优缺点
- 索引加快数据库的检索速度。
- 索引降低了插入、删除、修改等维护任务的速度。
- 唯一索引可以确保每一行数据的唯一性。
- 通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能。
- 索引需要占物理和数据空间。
表空间、段、区、数据页
记录是按照行来存储的,但是数据库的读取并不以行为单位,否则一次读取(也就是一次 I/O 操作)只能处理一行数据,效率会非常低。因此在数据库中,不论读一行,还是读多行,都是将这些行所在的页进行加载。也就是说,数据库管理存储空间的基本单位是页(Page)。
- 一个页中可以存储多个行记录(Row),同时在数据库中,还存在着区(Extent)、段(Segment)和表空间(Tablespace)。行、页、区、段、表空间的关系如下图所示:

索引的优点
天生排序
快速查找
索引的缺点
- 占用空间
- 降低更新表的速度。
注意点
小表使用全表扫描更快,中大表才使用索引。超级大表索引基本无效。
索引实现,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引)
功能区分,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。
1、普通索引:最基本的索引,没有任何约束。 2、唯一索引:与普通索引类似,但具有唯一性约束。 3、主键索引:特殊的唯一索引,不允许有空值。 4、复合索引:将多个列组合在一起创建索引,可以覆盖多个列。 5、外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。 6、全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎(ES,Solr)。
注意:主键就是唯一索引,但是唯一索引不一定是主键,唯一索引可以为空,但是空值只能有一个,主键不能为空。
常见索引类型
索引是存储在一张表中特定列上的
数据结构,索引是在列上创建的。并且,索引是一种数据结构。
全局索引(FULLTEXT)
- 全局索引,目前只有 MyISAM 引擎支持全局索引,它的出现是为了解决针对文本的模糊查询效率较低的问题。
哈希索引(HASH)
- 哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构,很适合作为索引。HASH 索引具有一次定位的好处,不需要像树那样逐个节点查找,但是这种查找适合应用于查找单个键的情况,对于范围查找,HASH 索引的性能就会很低。
B-Tree索引
- B 就是 Balance 的意思,BTree 是一种平衡树,它有很多变种,最常见的就是 B+ Tree,它被 MySQL 广泛使用。
R-Tree索引
- R-Tree 在 MySQL 很少使用,仅支持 geometry 数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种,相对于 B-Tree 来说,R-Tree 的优势在于范围查找。
B+Tree与Hash索引的区别
- B+Tree是一个平衡多叉树,从根节点到每个叶子节点点高度差值不超过1,同层级的节点互相有指针互相链接,是有序的。
- Hash索引是采用一定的Hash算法,把健值换成哈希值,检索的时候不需要类似b+树从根节点逐渐查找,值需要一次哈希运算即可,是无序的。
- Hash索引适合等值查询,哈希索引具有绝对优势(前提是:没有⼤量重复键值,如果⼤量重复键值时,哈希索引的效率很低,因为存在所谓的 哈希碰撞问题。不使用范围查询、也不支持索引完成排序。不支持联合索引最左匹配原则。通过列的基数很大也不建议使用Hash索引。
InnoDB存储引擎的B+树索引
- 每个索引都对应一棵B+树,B+树分为好多层,最下边一层是叶子节点,其余的是内节点。所有用户记录都存储在B+树的叶子节点,所有目录项记录都存储在内节点。
- InnoDB存储引擎会自动为主键(如果没有它会自动帮我们添加)建立聚簇索引,聚簇索引的叶子节点包含完整的用户记录。
- 我们可以为自己感兴趣的列建立二级索引,二级索引的叶子节点包含的用户记录由索引列 + 主键组成,所以如果想通过二级索引来查找完整的用户记录的话,需要通过回表操作,也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
- B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表,而且每个页内的记录(不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话,则页面和记录先按照联合索引前边的列排序,如果该列值相同,再按照联合索引后边的列排序。
- 通过索引查找记录是从B+树的根节点开始,一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory(页目录),所以在这些页面中的查找非常快。
- 二者SELECT COUNT(*)哪个更快,为什么
- myisam更快,因为myisam内部维护了一个计数器,可以直接调取。
索引失效
- 两张表联合查询的时候表的编码集不一样会导致索引失效。
- 列类型是字符串,一定要在条件中将数据用
引号引用,否则失效(隐式转换) - or语句前后未使用索引字段 ,字段A做了索引 WHERE A = 1 OR B = 2 这时候也不回走索引。
- like未使用最左前缀,where A like "%China"。
- 在索引上使用函数 、!= 、<> 、not in 。
- where a = b + 1 查询条件做运算
- is null 、is not null
覆盖索引和回表
- 覆盖索引指的是在一次查询中,如果一个索引包含或者说覆盖所有需要查询的字段的值,我们就称之为覆盖索引,而不再需要回表查询。而要确定一个查询是否是覆盖索引,我们只需要explain sql语句看Extra的结果是否是“Using index”即可。
索引下推
在根据索引查询过程中就根据查询条件过滤掉一些记录,减少最后的回表操作
使用索引查询一定能提高查询的性能吗
通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.
- 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
- 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
- 基于非唯一性索引的检索
小结
SQL 经连接器、解析器、优化器、执行器到达存储引擎;InnoDB 以 B+ 树组织聚簇索引,二级索引需回表,覆盖索引与最左前缀决定能否走索引。