【数据库运维 -- MySql】MySql 基础知识总结

【0】数据库基本概念总结

  • OLTP（on-line transaction processing）联机事务处理，主要处理数据库的增删改；
  • OLTP 主要用来记录某类业务事件的发生，数据会以增删改的方式在数据库中进行数据的更新处理操作，要求实时性高、稳定性强、确保数据及时更新成功；
  • OLAP（On-Line Analytical Processing）联机分析处理，主要处理数据库的查询；
  • 当数据积累到一定的程度，需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取想要的信息，为决策提供支持，这时候就是在做 OLAP；
  • SQL（Structured Query Language）结构化查询语言，一种特殊目的的编程语言，一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；SQL 是关系数据库系统的标准语言；
  • DQL（Data Query Language）数据查询语言
  • 对应 select 操作
  • DML（Data Manipulate Language）数据操作语言
  • 对应 insert、update、delete 操作
  • DDL（Data Define Languge）数据定义语言
  • 对应 create(创建一个新的表、表的视图、或者在数据库中的对象)、alter(修改现有的数据库对象)、drop(删除表、数据库对象或视图) 操作
  • DCL（Data Control Language）数据控制语言
  • grant(授予用户权限)，revoke(收回用户权限)
  • TCL（Transaction Control Language）事务控制语言
  • commit(提交事务)，rollback(回滚事务)

【1】MySql 体系结构与 CRUD 简介

【1.1】MySql 体系结构简介

mysql运维语句 mysql运维是什么_数据库

 MySQL 包含的组件

  • 连接池组件、管理服务和工具组件、SQL 接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件；

【1.2】MySql CRUD 简介

mysql运维语句 mysql运维是什么_数据_02

【2】MySql 存储引擎简介

【2.1】MySql 存储引擎分类

MyISAM 存储引擎

  • MyISAM 基于 ISAM 存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一，MyISAM 拥有较高的插入、查询速度，但不支持事务和外键；
  • 该存储引擎不支持事务、也不支持外键，所以访问速度较快，因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎；
  • MyISAM 存储引擎索引文件和数据文件是分离的，表相关文件有三个 : frm 存放表结构数据，MYD 存放表数据，MYI 存放索引
  • MyISAM 主要特性
  • 1) 大文件（达到 63 位文件长度）在支持大文件的文件系统和操作系统上被支持
  • 2) 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片，这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
  • 3) 每个 MyISAM 表最大索引数是 64，可以通过重新编译来改变，每个索引最大的列数是 16
  • 4) 最大的键长度是 1000 字节，可以通过编译来改变，对于键长度超过 250 字节的情况，使用超过 1024 字节的键
  • 5) BLOB 和 TEXT 列可以被索引，支持 FULLTEXT 类型的索引
  • 6) NULL 被允许在索引的列中，这个值占每个键的 0~1 个字节
  • 7) 所有数字键值以高字节优先被存储，以允许一个更高的索引压缩
  • 8) 每个 MyISAM 类型的表都有一个 AUTO_INCREMENT 的内部列，当执行 INSERT 和 UPDATE 操作的时候，同时更新 AUTO_INCREMENT 列
  • 9) 可以把数据文件和索引文件放在不同目录
  • 10) 每个字符列可以有不同的字符集
  • 11) 有 VARCHAR 的表可以固定或动态记录长度
  • 12) VARCHAR 和 CHAR 列可以多达 64KB
  • 存储格式
  • 1) 静态表（默认），字段都是非变长的（每个记录都是固定长度的），存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多
  • 2) 动态表，占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行 optimize table 或 myisamchk -r 命令来改善性能，而且出现故障的时候恢复比较困难
  • 3) 压缩表，使用 myisampack 工具创建，占用非常小的磁盘空间，因为每个记录是被单独压缩的，只有非常小的访问开支
  • 场景，如果表主要是用于插入新记录和读出记录，那么选择 MyISAM 能实现处理高效率

InnoDB 存储引擎

  • 该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，表数据文件就是按 B+Tree 组织的一个索引结构文件，主键索引叶节点包含了完整的数据记录；.frm : 存放表结构数据，.ibd : 存放的数据和索引；
  • InnoDB 主要特性
  • 1) InnoDB 给 MySQL 提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID 兼容） 存储引擎，InnoDB 锁定在行级并且在 SELECT 语句中提供非锁定读功能；在 SQL 查询中，可以自由地将 InnoDB 类型的表和其他 MySQL 的表类型混合起来，甚至在同一个查询中也可以混合
  • 2) InnoDB 是为处理巨大数据量的最大性能设计
  • 3) InnoDB 存储引擎完全与 MySQL 服务器整合，InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池，InnoDB 将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件），InnoDB 表可以是任何尺寸
  • 4) InnoDB 支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有在表定义时指定主键，InnoDB 会为每一行生成一个 6 字节的 ROWID，并以此作为主键
  • 5) InnoDB 被用在众多需要高性能的大型数据库站点上
  • 场景，由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制；如果对事务的完整性要求比较高，要求实现并发控制，那选择 InnoDB 有很大的优势；如果需要频繁的更新、删除操作的数据库，也可以选择 InnoDB，因为支持事务的提交（commit）和回滚（rollback）

MERGE 存储引擎

  • MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表结构必须完全相同，即 Merge 表就是几个相同 MyISAM 表的聚合器；Merge 表中并没有数据，对 Merge 类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的 MyISAM 表进行操作；
  • 场景，对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间相关；例如 : 可以用 12 个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名，当需要基于所有 12 个日志表的数据来生成报表，可以将这些表合并起来使用一条查询，之后再删除 Merge 表，而不影响原来的数据，删除 Merge 表只是删除 Merge 表的定义，对内部的表没有任何影响

MEMORY 存储引擎

  • MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速的访问
  • MEMORY 主要特性
  • 1) MEMORY 表的每个表可以有多达 32 个索引，每个索引 16 列，以及 500 字节的最大键长度
  • 2) MEMORY 存储引擎执行 HASH 和 BTREE 索引
  • 3) 可以在一个 MEMORY 表中有非唯一键值
  • 4) MEMORY 表使用一个固定的记录长度格式
  • 5) MEMORY 不支持 BLOB 或 TEXT 列
  • 6) MEMORY 支持 AUTO_INCREMENT 列和对可包含 NULL 值的列的索引
  • 7) MEMORY 表在所由客户端之间共享
  • 8) MEMORY 表被存储在内存中，内存是 MEMORY 表和服务器在查询处理时的空闲中，创建的内部表共享
  • 9) 当不再需要 MEMORY 表的内容时，要释放被 MEMORY 表使用的内存，应该执行 DELETE FROM 或 TRUNCATE TABLE，或者删除整个表（使用 DROP TABLE）
  • 场景，该数据库中需要一个用于查询的临时表

ARCHIVE 存储引擎

  • Archive 拥有很好的压缩机制，使用 zlib 压缩库，在记录被请求时会实时压缩，经常被用来当做仓库使用
  • 场景，由于高压缩和快速插入的特点 Archive 非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作

CSV 存储引擎

  • 使用该引擎的 MySQL 数据库表会在 MySQL 安装目录 data 文件夹和该表所在数据库名相同的目录中生成一个 .CSV 文件，这种文件是一种普通文本文件，每个数据行占用一个文本行；该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为 null；
  • 场景，这种引擎支持从数据库中拷入/拷出 CSV 文件，如果从电子表格软件输出一个 CSV 文件，将其存放在 MySQL 服务器的数据目录中，服务器就能够马上读取相关的 CSV 文件；同样，如果写数据库到一个 CSV 表，外部程序也可以立刻读取它，在实现某种类型的日志记录时，CSV 表作为一种数据交换格式，特别有用

BLACKHOLE 存储引擎

  • 黑洞存储引擎，写入的任何数据都会消失，可以应用于主备复制中的分发主库；该存储引擎支持事务，而且支持行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储；

PERFORMANCE_SCHEMA 存储引擎

  • 该引擎主要用于收集数据库服务器性能参数，提供以下功能 : 提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史事件汇总信息，对 MySQL 服务器性能做出详细的判断；监控新增和删除事件，改变 mysql 服务器的监控周期，MySQL 用户不能创建存储引擎为 PERFORMANCE_SCHEMA 的表；

Federated 存储引擎

  • 该存储引擎可将不同的 Mysql 服务器联合起来，逻辑上组成一个完整的数据库；这种存储引擎非常适合数据库分布式应用；Federated 存储引擎可以使你在本地数据库中访问远程数据库中的数据，针对 federated 存储引擎表的查询会被发送到远程数据库的表上执行，本地不存储任何数据；

【2.2】MySql 存储引擎相关命令

查看 MySql 引擎

show engines; -- 查看当前 mysql 的存储引擎
show variables like '%storage_engine%'; -- 查看当前 mysql 的默认存储引擎
show create table 表名; -- 查看特定表使用的引擎(在显示结果里参数 engine 后面的就表示该表当前用的存储引擎)
---------------------------------------------------------------------------------

MySql 设置存储引擎

// 创建表指定存储引擎
CREATE TABLE `mytest` (
    `id` int(11) NOT NULL AUTO_INCREMENT
) ENGINE=InnoDB(不写默认 InnoDB);

// 配置文件中指定默认引擎
-- default-storage-engine = engine

// 通过修改数据表命令实现存储引擎的切换
alter table mytest engine = MyISAM;

致谢
本博客为本人的学习笔记与实际操作分享，同时，参考网上众多博主的技术博客总结而成，在此向各位博主表示感谢，本博客若有不足请多多指正；