MySQL进阶

  • MySQL进阶（一）
  • 一、MySQL的系统架构
  • 1.1 数据库和数据库实例
  • 1.2 MySQL基架包括的模块组件
  • 1.3 MySQL查询过程
  • 二、数据库的存储引擎
  • 2.1 MySQL常用存储引擎及特点
  • 2.1.1 InnoDB存储引擎
  • 2.1.2 MylSAM存储引擎
  • 2.2 两种引擎的对比
  • 2.3 选择合适的存储引擎


MySQL进阶（一）

一、MySQL的系统架构

1.1 数据库和数据库实例

在MySQL的学习研究中，存在两个非常容易混淆的概念，即数据库和数据库实例。在MySQL中，数据库和数据库实例定义如下：

  • 数据库：存储数据的；
  • 数据库实例：操作数据库的。

如上定义很清楚了，数据库是用来存储数据的，数据库实例是用来操作数据的，从操作系统的角度，数据库实例表现为一个进程，对应多个线程，

在非集群数据库架构中，数据库与数据库实例存在一 一对应关系，在数据库集群中，可能存在多个数据库实例操作一个数据库情况，即多对一的关系

1.2 MySQL基架包括的模块组件

复杂的架构是为了更好的工作，架构中的每一个角色都可以高效的单独处理一类事件，举个例子。

你去拜访你朋友当然朋友自己迎接你即可。

但是你要拜访市长，可能就要门卫处做身份认证、传达室负责接通电话确认可以、市长办公室负责接待、你可能需要排队等候、你的事情如果办公室就能解决可能就不用见市长了，最后轮到你了，你才能见上市长。

对于MySQL来说，虽然经历了多个版本迭代（MySQL5.5,MySQL 5.6,MySQL 5.7,MySQL 8）,但每次的迭代，都是基于MySQL基架的，MySQL基架大致包括如下几大模块组件：

  1. MySQL向外提供的交互接口（Connectors）

Connectors组件是MySQL向外提供的交互组件，其他语言可以通过该组件来操作SQL语句，实现SQL的交互。

  1. 管理服务组件和工具组件（Management Service & Utilities）

提供对MySQL的继承管理，如备份(Backup)，恢复(Recovery)，安全管理(Srcurity)等。

  1. 连接池组件（Connection Pool）

负责监听对客户端向MySQL Server端的各种请求，接受请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，改线程负责客户端与MySQL Server端的同学，接收客户端发送的命令，传递服务端的结果信息等。

  1. SQL接口组件（SQL Interface）

接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户。

  1. 查询分析器组件（Parser）

首先分析AQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句的不合理。

  1. 优化器组件（Optimizer）

对SQL命令安装标准流程进行优化分析

  1. 缓存组件（Caches & Buffers）
缓存和缓冲组件

mysql 有tonumber嘛 mysql有实例吗_mysql 有tonumber嘛

1.3 MySQL查询过程

mysql 有tonumber嘛 mysql有实例吗_MySQL_02

二、数据库的存储引擎

2.1 MySQL常用存储引擎及特点

2.1.1 InnoDB存储引擎

MySQL5.5版本后，MySQL的默认存储引擎是InnoDB，特点：

  1. 支持事务。默认的事务隔离级别为可重复，通过MVCC（并发版本控制）来实现；
  2. 使用的锁粒度为行级锁，可以支持更高的并发；
  3. 支持外键；
  4. 存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
  5. 其数据的物理组织形式的聚簇表，所有的数据安装主键来组织。数据和索引放在一块，都位于B+数的叶子节点上。
2.1.2 MylSAM存储引擎

MySQL5.5版本前，MySQL的默认存储引擎是MylSAM，该存储引擎并发性差，不支持事务，使用场景较少，特点为：

  1. 不支持事务；
  2. 不支持外键，如果强行增加外键，不提示错误，外键也不起作用；
  3. 对数据的查询缓存只会缓存索引，不像InnoDB一样存储数据，而是利用操作系统本身的缓存；
  4. 默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，不太容易发生死锁；
  5. 支持全文索引（MySQL5.6之后，InnoDB也支持），但是MySQL的全文索引基本不会使用，对于全文索引，有其他更成熟的方案（ElasticSearch,Solr,Sphinx等）；
  6. 数据库所在主机如宕机，数据文件容易损坏，而且难恢复。

2.2 两种引擎的对比

  1. 由于锁粒度的不同，InnoDB比MylSAM支持更高的并发；
  2. InnoDB为行级锁，MylSAM为表级锁，所以InnoDB相对更容易发生死锁，锁冲突概率更大，而且上锁的开销也更大，因为需要为每一行加锁；
  3. 在备份容灾上，InnoDB支持在线热备，有很成熟的热备解决方案；
  4. 查询性能上，MylSAM的效率要高于InnoDB，在InnoDB查询过程中，需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后从数据块中定位到要查找的行，而MylSAM可以直接定位到数据所在的内存地址，可以直接找到数据；
  5. SELECT COUNT(*)语句，如果行数在千万级别以上，MylSAM可以快速查出，而InnoDB非常慢，因为MylSAM将行数单独存储了，而InnoDB需要逐行去统计；所以InnoDB需要查询行数，需要对行数做特殊处理，如离线查询并缓存；
  6. MylSAM的表结构文件包括：.form(表结构定义), .MYI(索引), .MYD(数据); 而InnoDB的表数据文件为：.ibd和.frm(表结构定义)；

2.3 选择合适的存储引擎

  1. 使用场景是否需要事务支持；
  2. 是否需要支持高并发；
  3. 是否需要支持外键；
  4. 高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MylSAM只缓冲了索引；
  5. 索引，不同存储引擎索引并不太一样。