MySql

最新推荐文章于 2024-07-31 22:46:13 发布

趙妏斌

最新推荐文章于 2024-07-31 22:46:13 发布

阅读量726

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/z13546335640/article/details/115534206

版权

MySQL

1、MySQL介绍
2.体系结构

MySQL 图标

1、MySQL介绍

1.1 MySQL简介

MySQL（MySQL官方文档）是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，2008年1月16日被Sun公司收购，2009年4月中，IBM收购Sun的谈判彻底破裂，4月20日，Sun又被Oracle公司收购，成为了 Oracle 旗下的另一个数据库产品。
MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一，MySQL在DB-Engines数据库流行度排行榜仅低于Oracle，。
MySQL 既然这么重要，为什么还会出现越来越多的核心 MySQL 产品的高端衍生产品？这是因为 MySQL 是免费的开源应用程序，所以开发人员总是可以获得其代码，并按照自己的想法修改代码，然后再自行分发代码。在很长的一段时间里，在开发人员自己的生产环境中，没有任何值得信任的 MySQL 分支。但是，这种情况很快就发生了改变。有几个分支引起了许多人的关注。
MariaDB 是由 MySQL 创始人之一 Monty 分支的一个版本。在 MySQL 数据库被 Oracle 公司收购后，Monty 担心 MySQL 数据库发展的未来，从而分支出一个版本。这个版本和其他分支有很大的不同，其默认使用崭新的 Maria 存储引擎，是原 MyISAM 存储引擎的升级版本。
Percona Server 是 Percona 公司分支的一个 MySQL 数据库版本。该版本对高负载情况下的InnoDB 存储引擎进行了一定的优化，为 DBA 提供一些非常有用的性能诊断工具，另外有更多的参数和命令可以用来控制服务器行为。Percona 公司最大的贡献是发布了免费开源的 XtraBackup工具，可实现对 InnoDB 存储引擎表的在线热备份操作。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。 MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

1.2 MySQL版本

针对不同的用户，MySQL分为两个不同的版本：

版本	是否收费	简介
MySQL 社区版	开源、非商业
MySQL Cluster 社区版	开源、非商业	MySQL群集建立在NDB存储引擎上，并提供高度可扩展的，实时的，符合ACID的事务数据库，结合了99.999％的可用性和较低的开源TCO。MySQL Cluster是围绕分布式，多主控架构设计的，没有单点故障，它可以在商用硬件上水平扩展以服务于通过SQL和NoSQL接口访问的读写密集型工作负载。 MySQL Cluster的实时设计提供了可预测的毫秒响应时间，并能够每秒处理数百万次操作。支持内存中的数据和基于磁盘的数据，具有负载平衡的自动数据分区（分片）功能以及在停机时间为零的情况下将节点添加到正在运行的群集中的能力，从而使线性数据库可伸缩性能够处理最不可预测的基于Web的工作负载。
MySQL 经典版	商业	仅供 ISV、OEM 和 VAR 用作嵌入式数据库。
MySQL 标准版	商业	高性能、可扩展的联机事务处理 (OLTP) 应用、MySQL Workbench Standard Edition（也叫 MySQL Workbench SE，商业版本）
MySQL 企业版	商业	企业功能：监控、备份、可伸缩、安全性、高可用 Oracle产品集成
MySQL Cluster 运营商级版本	商业	高可用、容错数据库 MySQL Cluster Manager MySQL Cluster Geo-Replication(简称geo-replication)是一种异地灾备技术

1.3 MySQL 连接器

连接器	API
ODBC	C
JDBC	PHP
NET	Perl
Python	Python
C	Ruby
C++	Tcl

2.体系结构

2.1 MySQL进程

通过ps -ef | grep mysql ¹，可以查看到mysql的进程，MySQL只有一个进程，因为MySQL是单进程多线程的数据库。
mysqld为核心程序，用于管理mysql的数据库文件以及用户的请求操作，可以读取配置文件中的[mysqld]的部分。
mysqld_safe 为守护进程，mysqld挂了会自动把mysqld进程拉起来，可以读取的配置部分[mysqld],[server],[myslqd_safe], 为了兼容mysql_safe也会读取[safe_mysqld]中的配置，可以在[mysqld_safe]中用-mysqld, --mysqld-version指令调用mysqld，但是在安装了对MySQL的systemd支持的平台上，不需要安装诸如mysqld_safe和System V初始化脚本之类的脚本。
例如，mysqld_safe可以处理服务器的重启，但是systemd提供了相同的功能，并且以与其他服务的管理一致的方式进行操作，而不是通过使用特定于应用程序的方式进行。
由于systemd能够在安装了MySQL的systemd支持的平台上管理多个MySQL实例，因此mysqld_multi和 mysqld_multi.server是不必要的，也不会安装。

-- 查看服务程序读取的选项组
mysqld --help --verbose | grep "following groups"

在这里插入图片描述

2.2 客户机程序

支持多种平台连接到MySQL服务器用于查询、修改、添加或删除数据。
使用MySQL Workbench官方程序管理数据库。
使用客户机程序执行以下操作：
mysql：发出查询以及查看结果

-- 查看客户程序读取的选项组
mysql --help | grep "following groups"
mysql --help --verbose | grep "following groups"
-- 登陆
mysql -uroot -p123456 -h192.168.1.5

在这里插入图片描述
mysqladmin：管理服务器

-- 查看程序读取的选项组
mysqladmin --help | grep "following groups"
mysqladmin --help --verbose | grep "following groups"
-- 判断mysql是否活着
mysqladmin -uroot -p123456 -h192.168.1.5 ping
-- 查看mysql状态信息
mysqladmin status
-- 查看mysql详细信息
mysqladmin extended-status
-- 查看mysql进程信息
mysqladmin processlist

在这里插入图片描述
mysqlcheck：检查数据库表的完整性。

-- 查看程序读取的选项组
mysqlcheck --help | grep "following groups"
mysqlcheck --help --verbose | grep "following groups"

在这里插入图片描述
mysqldump：数据库备份程序。

-- 查看程序读取的选项组
mysqldump --help | grep "following groups"
mysqldump --help --verbose | grep "following groups"

在这里插入图片描述
mysqlimport：导入文本数据文件。

-- 查看程序读取的选项组
mysqlimport --help | grep "following groups"
mysqlimport --help --verbose | grep "following groups"

在这里插入图片描述
mysqlpump ：数据库备份程序，支持并行、压缩

-- 查看程序读取的选项组
mysqlpump --help | grep "following groups"
mysqlpump --help --verbose | grep "following groups"

在这里插入图片描述
mysqlshow：显示数据库、表和列信息，与正常登陆后使用show显示一样。

-- 查看程序读取的选项组
mysqlshow --help | grep "following groups"
mysqlshow --help --verbose | grep "following groups"

-- mysqlshow命令,可以让我们在不连接到MySQL客户端的情况下查看MySQL的一些参数、数据库、表、列、索引等信息，其使用方法如下： 
-- 查看都有哪些库
mysqlshow -uroot -p
-- 查看某个库里（wyzc）都有哪些表
mysqlshow -uroot -p wyzc
-- 加上-v参数后可以显示每张表有多少列
mysqlshow -uroot -p -v wyzc
-- 加2个-v参数，可以显示出每张表有多少行
mysqlshow -uroot -p -v -v wyzc
-- 在库名（wyzc）后面加上表名（City），可以查看该表的详细信息。注：加上-i参数，可以查看该表的所有详细信息
mysqlshow -uroot -p wyzc City
-- 查看某张表的某一个列的信息
mysqlshow -uroot -p wyzc City ID
-- 加上-k参数查看该表的索引和列信息
mysqlshow -uroot -p -k wyzc City
-- 只查看某表索引信息
mysqlshow -uroot -p -k wyzc City invalid_col_name

-- 常用show命令
-- 显示mysql中所有数据库的名称。
show databases;
-- 显示指定数据库的create database 语句。
show create database `database_name`;
-- 显示当前数据库中所有表的名称。
show tables;
show tables from `database_name`;
-- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
show table status;
show table status from `database_name`;
-- 显示指定表的create table 语句。
show create table `database_name`.table_name;
-- 显示表中列名称。
show columns from table_name from `database_name`;
show columns from `database_name`.table_name;
desc `database_name`.table_name;
describe `database_name`.table_name;
-- 显示表的索引。
show index from `database_name`.table_name;
-- 显示一个用户的权限，显示结果类似于grant 命令。
show grants for user_name;
-- 显示一些系统特定资源的信息，例如，正在运行的线程数量。
show status;
-- 显示系统变量的名称和值。
show variables;
-- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
show processlist;
-- 显示服务器所支持的不同权限。
show privileges;
-- 显示安装以后可用的存储引擎和默认引擎。
show engies;
show storage engines;
-- 显示innoDB存储引擎的状态。
show innodb status;
-- 显示BDB存储引擎的日志。
show logs;
-- 查看所有的二进制日志文件
show binary logs;
-- 查看当前使用的二进制日志文件
show master status;
-- 显示最后一个执行的语句所产生的错误、警告和通知。
show warnings;
-- 只显示最后一个执行语句所产生的错误。
show errors;

在这里插入图片描述
mysqlslap：模仿客户机负载。

-- 查看程序读取的选项组
mysqlslap --help | grep "following groups"
mysqlslap --help --verbose | grep "following groups"

-- 显示使用方法
mysqlslap --help
-- engines代表要测试的引擎，可以有多个，用分隔符隔开。
-- iterations代表要运行这些测试多少次。
-- auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
-- auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
-- number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
-- debug-info 代表要额外输出CPU以及内存的相关信息。
-- number-int-cols ：创建测试表的 int 型字段数量
-- auto-generate-sql-add-autoincrement : 代表对生成的表自动添加auto_increment列，从5.1.18版本开始
-- number-char-cols 创建测试表的 char 型字段数量。
-- create-schema 测试的schema，MySQL中schema也就是database。
-- query 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
-- only-print 如果只想打印看看SQL语句是什么，可以用这个选项。
mysqlslap -uroot -p123456 -h192.168.1.5
-- 测试 100 个并发线程，测试次数 1 次，自动生成 SQL 测试脚本，读、写、更新混合测试，自增长字段，测试引擎为 innodb，共运行 5000 次查询
mysqlslap -uroot -p123456 -h192.168.1.5 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000
-- 自动测试时，创建的表结构非常简单，只有两列，实际的产品环境肯定会更复杂，可以使用参数指定列的数量和类型，例如
mysqlslap -uroot -p123456 -h192.168.1.5 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000 --number-int-cols=5 --number-char-cols=20

在这里插入图片描述
测试 100 个并发线程，测试次数 1 次，自动生成 SQL 测试脚本，读、写、更新混合测试，自增长字段，测试引擎为 innodb，共运行 5000 次查询

2.3 服务端程序

直接访问数据文件，而无需使用客户机连接到服务器
使用非客户机程序
innochecksum: 脱机检查InnoDB 表空间文件
mysglaccess:检查访问特权。
mysaldumps1ow:汇总慢速查询日志文件。
mysalbinlog:显示二进制日志文件。
某些应用程序在运行之前必须满足以下要求：关闭服务器、备份当前表。
实施之前查看程序要求。

2.4 服务器进程

在这里插入图片描述

2.5 连接层

在这里插入图片描述

2.6 通信协议

协议	连接类型	支持的操作系统
TCP/IP	本地、远程	所有
UNIX套接字文件	仅本地	仅 UNIX
共享内存	仅本地	仅Windows
命名管道	仅本地	仅Windows

协议在客户机库和驱动程序中实现。
连接协议的速度随本地设置不同而不同。
在这里插入图片描述
mysql -uroot -p1 --socket=/tmp/mysql3307.sock
对 mysql.sock 来说，其作用是程序与 mysqlserver 处于同一台机器，发起本地连接时可用。
例如你无须定义连接 host 的具体 IP 得，只要为空或 localhost 就可以。
在此种情况下，即使你改变 mysql 的外部 port 也是一样可能正常连接。

2.7 SQL 层

在这里插入图片描述

授权和解析器：解析器验证语法是否正确，然后，授权验证是否允许所连接的用户运行特定查询。
优化器：创建每个查询的执行计划，这是有关如何以最优化的方式执行查询的分步指令集。确定
要使用哪些索引以及采用何种顺序处理表是此步骤的最重要部分。
查询执行：完成每个查询的执行计划。
查询高速缓存：（可选）可配置的查询高速缓存，可用于存储（并立即返回）执行的查询和结果。
查询日志记录：可以启用以跟踪执行的查询。

-- 查看查询缓存配置
show variables like '%query_cache%';

MySQL8：废弃查询缓存
在这里插入图片描述
以下都为MySQL5.7，MySQL缓存区分大小写

-- 控制着查询缓存功能的开关
show variables like '%query_cache_type%';
select @@query_cache_type;
-- 查询缓存的大小
show variables like '%query_cache_size%';
select @@query_cache_size;
-- 开启缓存
set global query_cache_type = 1; 
-- 设置缓存大小
set global query_cache_size=1048576;

在这里插入图片描述

如果设置的 query_cache_size 小于 41984，则会设置失败，并自动把它置为 0。如果设置为 0 则表示不使用缓冲区。当query_cache_size 大于 0 的情况下，并不能保证缓冲区会被使用，还必须根据变量query_cache_type 来决定，该变量有 3 中状态，分别代表 3 中不同缓冲方式：

0 或 OFF--------将阻止缓存或查询缓存结果。

1 或 ON---------将允许缓存，以 SELECT SQL_NO_CACHE 开始的查询语句除外。

2 或 DEMAND--------仅对以 SELECT SQL_CACHE 开始的那些查询语句启用缓存。

-- 是否支持查询缓存
show variables like '%have_query_cache%';
-- 单个查询能够使用的缓冲区大小
show variables like '%query_cache_limit%';

在这里插入图片描述

如果（查询结果的大小=query_cache_min_res_unit），则直接把结果存入到该缓冲区中。
如果（查询结果的大小<query_cache_min_res_unit），则直接把结果存入到该缓冲区中，然后把多分配的空间释放掉，这种情况容易产生内存碎片。
如果（查询结果的大小>query_cache_min_res_unit），则需要再分配内存空间来存放结果

query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
查询缓存使用长度可变块，因此 Qcache_total_blocks 和 Qcache_free_blocks 可以显示查询缓存内存碎片。
执行flush query cache 后，只保留一个空闲块。

-- 清理查询缓存内存碎片
flush query cache;
-- 从查询缓存中移出所有查询
reset query cache;
-- 关闭所有打开的表，同时该操作将会清空查询缓存中的内容
flush tables;
-- 显示查询缓存区状态
show status like 'qcache%';

MySQL 提供了一系列的 Global Status 来记录 Query Cache 的当前状态，具体如下：
Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目
Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量
Qcache_hits：Query Cache 命中次数
Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数
Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL
Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量
Qcache_total_blocks：Query Cache 中总的 Block 数量
在这里插入图片描述

2.8 SQL 语句处理

在这里插入图片描述

2.9 存储层

在这里插入图片描述
InnoDB 是默认存储引擎。它可提供事务、全文索引和外键约束，因此适用于各种混合查询。它具有多种用途，支持读密集型工作负荷、读/写工作负荷和事务工作负荷。
其他存储引擎包括：
MyISAM：适用于频繁读取但很少更新的数据
MEMORY：在内存中存储所有数据
NDB：供 MySQL Cluster 用来为高可用性数据提供冗余的可伸缩拓扑
注：存储引擎可扩展，超越存储层，而不只包含存储。它们还包括其他结构和实现机制。
Archive: 只是支持 select、insert，压缩，适合日志和数据采集的英语，不支持事务。
CSV：不支持索引，可以直接读取。
说到这里可以看出 MySQL 与其他数据库的区别就是他是插件式的表存储引擎。存储引擎是基于表的不是基于数据库的。

MySQL 群集是个比较另类的东西，它本质上只是标准 MySQL 服务器的一种存储引擎而已——名称叫 ndb 或者 ndbcluster。该引擎必须显式的编译进 MySQLd 服务器中才可以使用
ndb 支持较为广泛的平台及操作系统，且经过多年的发展，已经可以应用于生产环境
ndb 引擎通过 tcp 协议交换群集数据
ndb 不需要传统的共享存储设备，而是通过专门的 ndbd 数据存储引擎来模拟共享存储
ndbd 存储引擎支持自动配置、多个数据复本、数据分区、自动分组等功能
ndb 提供专门的管理工具集
当使用时候，有可能我们通常所说的数据库文件不是操作系统文件而是内存中的文件

下图一个数据库可能被多个实例使用
在这里插入图片描述
国内用 NDB 集群的公司非常少
NDB 集群不需要依赖第三方组件，全部都使用官方组件，能保证数据的一致性
某个数据节点挂掉，其他数据节点依然可以提供服务
管理节点需要做冗余以防挂掉
缺点是：管理和配置都很复杂，而且某些 SQL 语句例如 join 语句需要避免。
因为他的 join 是底层完成的，网络压力大

每种方案都有不同的特点，配置和应用场景也各有不同
有些偏向于成本低的，有些偏向于成本高的，有些偏向于数据的可靠性，有些则偏向于数据库的可用性
反正各个方案都各有优缺点，DBA 要结合自己公司的业务情况进行选择合适自己业务情况的高可用方案

2.10 依赖于存储引擎的功能

存储介质
表存储引擎可以在磁盘上、在内存中或通过网络存储数据。
事务功能
某些存储引擎支持全面的 ACID (atomicity：原子性或称不可分割性）、consistency：一致性、isolation：隔离性又称独立性、durability：持久性)事务功能，而其他存储引擎可能不具有事务支持。
锁定
存储引擎可能使用不同的锁定粒度（例如表级别锁定或行级别锁定）和机制来提供与并发事务的一致性。
备份和恢复
可能会受到存储引擎存储和操作数据的方式的影响。
优化
不同的索引实现可能会影响优化。存储引擎以不同的方式使用内部高速缓存、缓冲区和内存以优化性能。
特殊功能（全文搜索、引用完整性、空间数据处理）
某些引擎类型具有提供全文搜索和引用完整性的功能以及处理空间数据的能力。

2.11 MySQL 如何使用磁盘空间

在这里插入图片描述
程序文件随数据目录一起存储在服务器安装目录下。执行各种客户机程序、管理程序和实用程序时将创建程序可执行文件和日志文件。首要使用磁盘空间的是数据目录。
服务器日志文件和状态文件包含有关服务器处理的语句的信息。日志可用于进行故障排除、监视、复制和恢复。
InnoDB 日志文件（适用于所有数据库）驻留在数据目录级别。
InnoDB 系统表空间包含数据字典、撤消日志和缓冲区。
每个数据库在数据目录下均具有单一目录（无论在数据库中创建何种类型的表）。
数据库目录存储以下内容：
数据文件（.ibd）：特定于存储引擎的数据文件。这些文件也可能包含元数据或索引信息，具体取决于所使用的存储引擎。
格式文件 (.frm)：包含每个表和/或视图结构的说明，位于相应的数据库目录中。
触发器：与某个表关联并在该表发生特定事件时激活的命名数据库对象。
数据目录的位置取决于配置、操作系统、安装包和分发。典型位置是 /var/lib/mysql。

2.12 MySQL 如何使用内存

在这里插入图片描述
内存分配可以划分为以下两种类别：
全局（每实例内存）：服务器启动时分配一次并在服务器关闭时释放。此内存在所有会话间共享。当所有物理内存用尽时，操作系统开始交换。这会对 MySQL 服务器性能具有不利影响，可能会导致服务器崩溃。
innodb_buffer_pool_size为缓冲池大小，在MySQL 5.7.5版本后，可以动态的设置，这意味着你可以在不启动服务器的情况下，重新设置缓冲区的大小，但是在数据库服务重启后恢复为设置前的大小。如果是只有数据库，最好设置为操作系统的75%。
会话（每会话内存）：基于每个会话（有时称为“线程”）动态进行分配。此内存可在会话结束时或不再需要会话时释放。此内存多用于处理查询结果。所使用的缓冲区大小基于每个连接。例如，read_buffer 为 10 MB 且具有 100 个连接意味着可能总共有 100*10 MB 同时用于所有读取缓冲区。
在服务器中每个连接所分配的内存主要由下面四个参数控制：
（1）sort_buffer_size 连接进行排序时候分配该配置参数大小的内存进行排序操作，比如该大小设置为 100M，如果有 100 个连接同时进行排序将分配 10G 的内存，很容易造成服务器内存溢出；
（2）join_buffer_size 定义 mysql 的每个线程所使用连接的缓冲区的大小，对于这个参数需要注意的是，如果一个查询中关联了多张表，那么就会为每个关联分配一个连接缓存，所以每个查询可能会有多个连接缓冲；
（3）read_buffer_size 对 MyISAM 表进行全表扫描时分配的读缓存池的大小，mysql 只会在有查询需要时为该缓存分配内存，分配的内存为配置参数指定内存的大小，大小一般为 4K 的倍数；
（4）read_rnd_buffer_size 索引缓冲区的大小，有查询需要时才分配内存，分配的大小为需要内存的大小，而不是配置参数的大小；
上面四个参数全部是为每个线程分配的，如果有一百个连接可能会分配 100 倍以上内存的和。