目录一MySQL体系结构二存储引擎简洁与操作命令语法格式实战示例三InnoDB 与 MyISAM 的特点对比一MySQL体系结构在开始之前我们必须要了解MySQL的体系结构知道它大概是怎么运作的。MySQL 的架构设计非常优秀它采用了插件式的存储引擎架构将“查询处理”与“数据存储/提取”完全解耦。通常MySQL 体系结构自上而下可以分为四层连接层、服务层、引擎层、存储层。层级名称核心职责它是干嘛的包含的核心组件 / 技术举个通俗的例子1. 连接层(Connection)安全看门人。负责客户端的连接、权限验证、线程分配。连接池 (Connection Pool)、用户鉴权系统、SSL 安全连接。就像去银行办事门口的保安核验你的身份证并给你取一个排队号。2. 服务层(Service)核心大脑。负责 SQL 语句的解析、优化、执行以及所有跨引擎的功能。SQL 接口、解析器 (Parser)、优化器 (Optimizer)。银行的业务经理。他负责看懂你的申请表解析想出最快办完的方案优化然后指挥柜员。3. 引擎层(Engine)干活的工人可插拔。负责具体数据的存储和提取决定了表的特性。InnoDB 引擎、MyISAM 引擎、Memory 引擎等。柜台里的具体办事员。有的办事员擅长办大额贷款InnoDB稳重安全有的擅长快速数钞票MyISAM速度快。4. 存储层(Storage)保险库。负责把数据真正持久化到磁盘上。操作系统文件系统、裸设备、.ibd文件、日志文件。银行后面的物理大金库钱数据最终锁在里面的铁皮柜里。在这一部分面试和实战中最喜欢考的就是“服务层”与“引擎层”的分工。只要记住一句话“服务层负责想引擎层负责做。”比如你写一句SELECT * FROM user WHERE id 1;服务层的优化器会思考我要不要用id这个索引用它需要花多少成本想好之后服务层对引擎层下达命令“去把id 1的这行数据给我拿过来。”引擎层比如 InnoDB就会去磁盘或内存里把这行数据找出来恭恭敬敬地递给服务层。二存储引擎简洁与操作命令MySQL 的灵魂就在于它的插件式存储引擎。这意味着MySQL 规定了一套统一的接口标准任何人都可以根据这套接口开发自己的存储引擎。在开发或排查问题时你首先需要知道你的 MySQL 到底支持哪些引擎。SHOWENGINES;执行这个命令后MySQL 会返回一张表包含以下关键列Engine引擎的名字如 InnoDB, MyISAM, MEMORY。SupportMySQL 对该引擎的支持状态DEFAULT当前默认使用的引擎现代版本基本都是 InnoDB。YES支持并已启用。NO不支持或未启用。Transactions极重要表示该引擎是否支持事务可以看到只有 InnoDB 是YES。XA是否支持分布式事务。Savepoints是否支持事务回滚点方便局部回滚。存储引擎是基于表的。也就是说在同一个数据库里你可以让张三表用 InnoDB李四表用 MyISAM。CREATETABLE表名(字段名1数据类型[约束],字段名2数据类型[约束])ENGINE引擎名称;-- 创建一个用于核心业务、需要事务支持的订单表指定为 InnoDBCREATETABLEt_order(order_idINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULL,moneyDECIMAL(10,2))ENGINEInnoDB;-- 创建一个仅用于记录日志、不需要事务的日志表指定为 MyISAMCREATETABLEt_log(log_idINTPRIMARYKEYAUTO_INCREMENT,contentVARCHAR(500),create_timeDATETIME)ENGINEMyISAM;注意如果你在建表时不写ENGINE ...MySQL 会自动使用默认引擎即上面SHOW ENGINES;查出来的DEFAULT引擎MySQL里就是InnoDB。如果线上的表以前用的是 MyISAM现在想改成 InnoDB不需要删表重打一条命令搞定。语法格式ALTERTABLE表名ENGINE新引擎名称;实战示例-- 将日志表的引擎从 MyISAM 修改为 InnoDBALTERTABLEt_logENGINEInnoDB;底层原理避坑指南 当你执行这条命令时MySQL 会在后台做三件事创建一张新表使用你指定的新引擎。把旧表的数据全部复制到新表中。复制完成后删除旧表重命名新表。代价如果这张表的数据量非常大比如几千万条这个操作会消耗大量的 IO 性能并锁表绝对不能在白天业务高峰期执行三InnoDB 与 MyISAM 的特点对比对比维度InnoDB 存储引擎MyISAM 存储引擎为什么重要大白话解释事务安全支持 (ACID)不支持InnoDB 断电能自动恢复数据MyISAM 断电数据可能损坏或丢失。锁的粒度行级锁(Row Lock)表级锁(Table Lock)InnoDB 几百人同时改不同行互不影响MyISAM 一人改数据整张表锁死别人只能排队。外键约束支持不支持InnoDB 能在数据库层面保证表与表之间的数据联动和一致性。磁盘文件1个核心文件.ibd(数据与索引合一)2个核心文件.MYD(存数据).MYI(存索引)InnoDB 索引和数据在一起查到索引就能直接拿到数据效率极高。崩溃恢复支持(通过 redo log)不支持InnoDB 具备金融级的安全保障 (Crash-Safe)MyISAM 崩溃后需要人工修复表。底层索引聚簇索引非聚簇索引决定了数据在磁盘上的物理排列方式。所以一般默认就是InnoDB不用改。
