MySQL

Introduction • MySQL is an open source RDBMS supported by ORACLE • Initially released 1995 by MySQL AB (Swedish Company) • Founded by David Axmark, Allan Larsson, Michael Widenius • Main Features — Server-client architecture — Available for a variety of platforms including Windows, macOS, Solaris, Linux and Android — Multiple storage engines ◦ Users/applications can select the storage engine for their database (e.g., Based on their requirements) ◦ Can even mix and match different engines within the same database — Supports ANSI SQL — Version 8.0.18 is the current stable release (2019)主要特点 - 服务器-客户端架构 - 可用于多种平台 用户/应用程序可为其数据库选择存储引擎 可以在同一个数据库中混合和匹配不同的引擎 - 支持 ANSI SQL 

Features • Interface (GUI and SQL) • ACID • Referential integrity • Transactions • Fine-grained locking • Multiversion concurrency control • Unicode

Central Component • Written in C/C++ • Central component in many stacks — LAMP (Linux) — XMAPP (cross-platform) — WAMP (Windows) — MAMP (macOS) • NoSQL Document Store (Version 8)

MySQL Architecture • Primary subsystems — The Query Engine — The Storage Manager — The Buffer Manager — The Transaction Manager — The Recovery Manager • Support components — The Process Manager ◦ Connectivity functionality — Function Libraries ◦ General functions used by all the subsystemsQuery Engine Three components • Syntax parser — Translates SQL query to an internal form, RAT — Correctness of the syntax is established — User privileges are used to verify if the query makes legal references to tables (or fields) • Query optimizer — Selects the most efficient query execution plan • Execution component — Executes the query by making calls to other modules查询引擎三个部分 语法分析器 - 将 SQL 查询转换为内部形式 RAT - 确定语法的正确性 - 使用用户权限验证查询是否对表（或字段）进行了合法引用 - 查询优化器 - 选择最有效的查询执行计划 - 执行组件 - 通过调用其他模块来执行查询

Responsible for file organization and indexing • Interfaces with the OS to write to disk all of the data, such as — User tables, indexes and logs • The component that makes MySQL special by — Offering different types of storage engines (or table types) • Advantages of multiple storage engines — When new engines are added to the server, the server architecture allows supporting older file formats — Changes to storage engines do not cause changes elsewhere in MySQL — Different users/applications have a choice in the storage engine used — Easier to introduce new storage media (compact flash) which may require different approach 存储管理器 The Storage Manager •- 负责文件组织和索引 - 与操作系统交互，将所有数据（如用户表、索引和日志）写入磁盘 - 通过提供不同类型的存储引擎（或表类型），使 MySQL 与众不同 - 多种存储引擎的优点 - 当新引擎添加到服务器时，服务器架构允许支持旧文件格式 - 存储引擎的更改不会导致 MySQL 其他地方的更改 - 不同用户/应用程序可以选择使用的存储引擎 - 更容易引入新的存储介质（闪存），可能需要不同的方法

• MyISAM (Old default) — Extensions to the traditional ISAM — No support for transactions — No referential integrity • InnoDB (Transactional, Default in > MySQL 5.7) — Supports transactions with ‘ACID’ properties — Supports referential integrity • Memory (In-memory) — Tables of this type are stored in RAM; therefore fast — But data will be lost in the event of a crash • NDB (Clustered) – advanced storage method – not here • You will be using mostly InnoDB or MyISAM存储引擎 - MyISAM（旧版默认设置） - 传统 ISAM 的扩展 - 不支持事务 - 不支持参照完整性 - InnoDB（事务型，> MySQL 5.7 中的默认设置） - 支持具有 "ACID "属性的事务 - 支持参照完整性 - 内存（内存中） - 这种类型的表存储在 RAM 中，因此速度很快 - 但数据在崩溃时会丢失 - NDB（集群） - 高级存储方法 - 不在此处 - 您将主要使用 InnoDB 或 MyISA

、Buffer Manager • Manages memory management issues — Between query engine and memory manager • Maintains Query Cache which — Stores the result sets of queries — Repeated queries are answered directly from query cache • New records are cached in before being written to disk • Query cache is unique to MySQL • Is responsible for enhanced response times — Studies show that MySQL works as fast as Oracle and SQL server (Microsoft’s RDBMS server)缓冲管理器Buffer Manager - 管理内存管理问题 - 介于查询引擎和内存管理器之间 - 维护查询缓存 - 存储查询结果集 - 直接从查询缓存回答重复查询 - 新记录写入磁盘前缓存在其中 - 查询缓存是MySQL独有的 - 负责提高响应时间 - 研究表明，MySQL的运行速度不亚于Oracle和SQL服务器（微软的RDBMS服务器）

Recovery Manager • Performs data recovery in the event of loss of data — From a DBAdmin perspective a very important function — The whole DBMS purpose is defeated if the data is not safe — We only discussed recovery management very briefly in this course • Different for different storage engines (or table types) • InnoDB table provides recovery management — Mysqldump is a simple file based backup — You are encouraged to try recovery features of InnoDB on your own • MyISAM table provides fixes to data inconsistencies due to server outage恢复管理器Recovery Manage - 在数据丢失的情况下执行数据恢复 - 从 DBAdmin 的角度来看，这是一项非常重要的功能 - 如果数据不安全，整个 DBMS 的目的就落空了 - 我们在本课程中只是非常简要地讨论了恢复管理 - 不同的存储引擎（或表类型）有不同的恢复管理 - InnoDB 表提供恢复管理 - Mysqldump 是一种基于文件的简单备份 - 我们鼓励你自己尝试 InnoDB 的恢复功能 - MyISAM 表提供服务器中断导致的数据不一致修复功能

Transaction Management • Performs locking and transaction management • Different for different storage engines (or table types) • InnoDB table supports — Transaction management where transactions obey ‘ACID’ requirements — Row and table level locking — All isolation levels • MyISAM table supports — Table level locking事务管理Transaction Management - 执行锁定和事务管理 - 不同存储引擎（或表类型）不同 - InnoDB 表支持 - 符合 "ACID "要求的事务管理 - 行和表级锁定 - 所有隔离级别 - MyISAM 表支持 - 表级锁定

Specifying the Table Type • When you create a table you can specify the table type (storage engine) for that table. For example:• You can alter the type of an existing table to a different type • For example:

• A single MySQL database can have tables of different types • If you don’t require transactions, it is best to use MyISAM • You can use SHOW TABLE STATUS to display information about a table • For example to display information about table called test 一个 MySQL 数据库可以有不同类型的表 - 如果不需要事务，最好使用 MyISAM - 可以使用 SHOW TABLE STATUS 显示表的信息 - 例如，显示名为 test 的表的信息Performance Related Issues • MySQL allows users to ask for information about how their select queries are processed using EXPLAIN • EXPLAIN is the command to be added before your select statement • For example 性能相关问题--MySQL 允许用户使用 EXPLAIN 询问有关如何处理其选择查询的信息--EXPLAIN 是在选择语句之前添加的命令--例如- 请注意，"行 "字段为 4 - 这意味着有 4 行（客户表的所有行）需要检查 - 请注意，"可能键 "字段为 NULL - 这意味着没有定义索引

现在为客户表添加索引

现在重新运行之前的选择查询，询问解释• Please note that now only one row needs to be examined and the query therefore runs faster • Please also note that this table now has an index 请注意，现在只需检查一条记录，因此查询运行速度更快。

Further Performance Enhancements • You can improve the performance of your query further by defining smaller indexes • In the previous example ClientNo is used as the index — it is five characters long — therefore defining only part of the ‘clientNo’ as the index may not make much difference • When longer fields are used as indexes — It helps to define the index on part of the field • Query Optimizer in MySQL inspects all the indexes before selecting one for use in query processing • You can help MySQL by running the following statement to help in the process of selecting the correct index进一步提高性能--你可以通过定义较小的索引来进一步提高查询的性能--在前面的例子中，ClientNo被用作索引--它有5个字符长--因此，只定义 "clientNo "的一部分作为索引可能不会有太大的影响--当较长的字段被用作索引时--在部分字段上定义索引是有帮助的--MySQL中的查询优化器在选择一个索引用于查询处理之前会检查所有的索引--你可以通过运行下面的语句来帮助MySQL选择正确的索引

• You can also instruct MySQL to reclaim space lost due to deletion of records (holes)

- 您还可以指示 MySQL 恢复因删除记录（洞）而丢失的空间

Example Showing the Use of Shorter IndexesMySQL/ORACLE Differences • MySQL available on more operating systems • Schema – Oracle (user owned) MySQL (database owned) • Data Types and partitioning (similar) • Authentication (MySQL uses location parameter) • Privileges (Oracle uses roles, MySQL only grants individual users) • Data storage (Oracle uses tablespaces, MySQL uses storage engines) • Oracle more powerful, enterprise solution — Database links, indexes and views (both Temporary table and Materialized view) — Joins (i.e., Intersect and Except), access control and stored procedures using PL/SQL — PL/SQL conditions/loops/constants/variables, etc. — Oracle Forms and Reports (build basic front-ends) • MySQL easier to get going and widely supported • SQL syntax differences IMPORTANT: when converting between database systemsMySQL/ORACLE差异 - MySQL可用于更多操作系统 - 模式 - Oracle（用户所有）MySQL（数据库所有） - 数据类型和分区（类似） - 身份验证（MySQL使用位置参数） - 权限（Oracle使用角色，MySQL仅授予单个用户） - 数据存储（Oracle使用表空间，MySQL使用存储引擎） - Oracle功能更强大，是企业级解决方案 - 数据库链接、索引和视图（包括临时表和物化视图） - 连接（即：相交和相除）、访问控制和使用PL/SQL的存储过程 - PL/SQL条件/循环/常量/变量等 - Oracle窗体和报表（构建基本前端） - MySQL使用PL/SQL、 PL/SQL条件/循环/常量/变量等 - Oracle表单和报表（构建基本前端） - MySQL更容易上手，支持广泛 - SQL语法差异 重要：在数据库系统之间转换时

Limits • Dbase size – unlimited • Table size – MyISAM (256 TB), InnoDB (64 TB) • Row size – 64 KB • Columns per row – 4096 • Blob size – 4 GB (longtext, longblob) • Char size – 64 KB (text) • Number size – 64 bits • Column name size – 64 Limitations • Does not always comply with full SQL standard • Some built in functions flawed (UNIX TIMESTAMP will return 0 after 2038)

Cloud-based Platforms • Microsoft Azure • Amazon EC2 • Oracle Cloud Infrastructure Graphical User Interfaces • MySQL Workbench • phpMyAdmin (handle administration) • etc

Summary • MySQL is an open-source and very popular RDBMS — You are more likely to use MySQL at work and home than any other RDBMS • Offers several unique features – multiple table types/storage engines • With PHP, offers a powerful solution for online information (e-commerce) systems • You could learn a lot using your own copy of MySQL on your laptop/desktop — Database admin activities — Query optimization • Learn MySQL from a command line console — Not phpMyAdmin