Mysql

来自智得网
跳转至： 导航、​ 搜索

简介

mysql的logo

mysql是一款开源数据库。1995年，Michael Monty Widenius、David Axmark和 Allan Larsson，在瑞典创立了MySQL AB公司。1996年，发布MySQL 1.0版本，“My”取自联合创始人Micheal Monty Widenius女儿的名字。同年10月，MySQL 3.11.1发布，没有2.x版本。

MySQL由于性能高、成本低、可靠性好，迅速成为最流行的开源数据库之一，被广泛地应用在Internet上的各类网站中。其中包括大规模网站和应用，比如维基中文镜像、Google和Facebook等。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

被甲骨文公司收购后，自由软件社群们对于Oracle是否还会持续支持MySQL社区版（MySQL之中唯一的免费版本）有所隐忧，MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB。而原先一些使用MySQL的开源软件逐渐转向MariaDB或其它的数据库。例如维基中文镜像已于2013年正式宣布将从MySQL迁移到MariaDB数据库。

Mysql至今重要的版本如下：

  • 2000年，MySQL对原存储引擎进行了整理，命名为MyISAM
  • 2001年，MySQL集成Heikki Tuuri的存储引擎InnoDB，实现了事务处理和行级锁的支持。该年发布的3.23 版本已支持大多数的基本的SQL 操作，并集成了MyISAM和InnoDB 存储引擎。
  • 2003年3月，发布MySQL 4.0 ，支持查询缓存、集合并、全文索引，正式支持InnoDB存储引擎。
  • 2004年10月，发布经典的MySQL 4.1 ，增加了子查询，utf8字符集等。
  • 2005年10月，发布MySQL 5.0 ，增加了视图、存储过程、游标、触发器、分布式事务，迈出了高性能数据库步伐。
  • 2008年1月，MySQL AB公司被Sun公司以10亿美金收购。
  • 2009年4月，Oracle公司以74亿美元收购Sun公司，MySQL转入Oracle麾下。
  • 2010年12月，发布MySQL 5.5 ，增加了半同步复制、信号异常处理、unicode字符集，InnoDB成为默认存储引擎。
  • 2011年4月，发布MySQL 5.6 ，增加了GTID复制，支持延时复制、行级复制。
  • 2013年2月，发布MySQL 5.7 ，支持原生JSON数据类型。
  • 2016年9月，发布MySQL 8.0.0 ，速度要比 MySQL 5.7 快 2 倍；可使用JSON数据的SQL机制；支持GIS；可靠性更高。
  • 2018年4月，发布MySQL 8.0.11 GA ，支持NoSQL文档存储、原子的奔溃安全DDL语句、扩展JSON语法，新增JSON表函数，改进排序、分区更新功能。

原理

Mysql系统架构

Mysql是CS架构的，目前主流的编程语言都有mysql的client。

Mysql的服务端由连接池、管理工具、SQL接口，分析器、优化器，缓存/缓冲、物理文件以及存储引擎构成，Mysql存储引擎是插件式的，有多种不同的实现，其中最为出名的存储引擎包括MyISAM，InnoDB等。 

存储引擎不是数据库级别的，而是基于表的。

逻辑层

mysql服务端的逻辑层主要分为以下几个模块：

  • 连接管理模块负责监听对server端的各类请求，并将请求转发到线程管理模块，每一个客户端的请求都会有一个线程为其服务，该线程负责转发请求到server端获取结果并且把数据返回给client。
  • Mysql接口层是服务端的门面层，接收到client发给server的Query之后，Mysql接口层主要负责将Query进行分类，然后转发给不同处理模块。
  • 解析器的主要工作是将Query进行语法以及语义分析。
  • 优化器模块，顾名思义，就是根据Query语句以及数据库中的一些统计信息，在一系列预置算法的基础上进行分析，得出一个最优的执行策略。
  • Cache是Mysql中一个非常重要的模块，它的主要功能是将客户端提交给Mysql的查询类请求的结果集缓存到内存中，与Query的Hash值进行对应，在改Query查询数据的基表发生任何变化，Mysql就会失效这些缓存。
cache主要用于查询缓存，buffer主要是缓冲的作用，在写入过程中，因为磁盘速度和内存速度的差异，不可能每次都把数据写入硬盘，为了解决速度的差异，数据会先写入缓冲区，之后批量写入磁盘，LSM树的memtable等也是使用缓冲的方式提高写入性能。
mysql读请求的调用流程

除了上述模块之外，mysql的逻辑层还包括用户模块，访问控制，表管理器，复制模块等。

物理文件

mysql在硬盘记录的文件可以分为以下几类：

  • 记录系统运行情况的日志，包括error日志，查询日志，慢查询日志。
  • 用于主备同步的日志，binlog
  • 和事务相关的日志，例如InnoDB引擎的redolog、undolog。
  • 数据文件，不同存储引擎会以不同的后缀存储数据文件，InnoDB的数据文件的扩展名是".ibd"，MyISAM以".MYD"作为扩展名。

引擎

mySql存储数据时，有不同的存储方式，这些方式都使用了不同的底层实现(如：存储机制，索引技巧，索引技巧，锁定水平)，底层实现的差异同时带来的功能的差异，例如MysISAM查询数据表的总数速度非常快，而InnoDB可以支持事务。

mysql的引擎有MyISAM ,   Mrg_Myisam,   Memory,   Blackhole,   CSV,   Performance_Schema,   Archive,   Federated ,  InnoDB等。

MyISAM

MyISAM是Mysql默认的存储引擎，该引擎下每一个表都有是三个文件，.frm，.MYD，.MYI三个文件分别存放了表结构，数据以及索引。在MyISAM引擎中，不论一个表存在多少索引，都保存在同一个MYI文件中。

MyISAM支持三种索引，B-Tree索引，Full-text索引以及R-索引。这三种索引中R-索引主要设计为多维字段的数据进行索引，使用场景较少，另外两种索引底层的数据结构都是B-tree（翻译为B树，不要翻译为B减树）。

MyISAM不支持事务，在主机异常崩溃，磁盘故障等场景会发生数据丢失的场景。

InnoDB

InnoDB是Mysql使用最广泛的引擎之一，因为其支持多个重要功能：

  • 支持事务安全
  • 支持多版本读取（MVCC）
  • 实现了外键。
Memory

Memery引擎所有的数据都存在内存里面，可以当作缓存使用。

使用

安装

Mysql在类linux系统下可以使用源码安装、包管理工具安装或者直接下载mysql的release版本。

下面以在centos上release包安装作为示例：

#下载经典的5.7版本
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.29-linux-glibc2.12-i686.tar.gz
#解压release包
tar -zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
#修改名字便于操作
mv mysql-5.7.29-linux-glibc2.12-x86_64 mysql
#增加mysql用户组以及mysql用户
groupadd mysql
useradd -r -g mysql mysql
#修改mysql配置文件，增加下列内容
#[mysqld]
#bind-address=0.0.0.0
#port=3306
#user=mysql
#basedir=/usr/local/soft/mysql  # mysql安装目录
#datadir=/data/mysql  # 数据存放目录
#socket=/tmp/mysql.sock
#log-error=/data/mysql/mysql.err
#pid-file=/data/mysql/mysql.pid
#character_set_server=utf8mb4
#explicit_defaults_for_timestamp=true
vi /etc/my.cnf
#初始化数据库，执行完之后会有临时密码，如果忘记记录，后续可以通过grep 'temporary password' /var/log/mysqld.log进行搜索
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/soft/mysql/ --datadir=/data/mysql/ --user=mysql --initialize
#启动mysql服务
cp /usr/local/soft/mysql/support-files/mysql.server /etc/init.d/mysql
#修改用户密码，登陆数据库之后操作
#SET PASSWORD = PASSWORD('123456');
#ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
#FLUSH PRIVILEGES; 
./mysql -uroot -p临时密码
#开放数据库访问权限
use mysql;     #访问mysql库
update user set host = '%' where user = 'root';   #使root用户能在任何IP进行访问
FLUSH PRIVILEGES;

管理

系统表

下面以Mysql 5.7版本介绍一下数据库自带的系统表，系统表分布在information_schema、performance_schema、mysql、sys等4个数据库中。

库名 表数量 表名 表用途
information_schema 61 SCHEMATA 提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表
TABLES 提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema、表类型、表引擎、创建时间等信息。是show tables from schemaname的结果取之此表
COLUMNS 提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表
STATISTICS 提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表
USER_PRIVILEGES 用户权限表:给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表
SCHEMA_PRIVILEGES 方案权限表:给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表
TABLE_PRIVILEGES 表权限表:给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表
COLUMN_PRIVILEGES 列权限表:给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表
CHARACTER_SETS 字符集表:提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表
COLLATIONS 提供了关于各字符集的对照信息
COLLATION_CHARACTER_SET_APPLICABILITY 指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS 描述了存在约束的表。以及表的约束类型
KEY_COLUMN_USAGE 描述了具有约束的键列
ROUTINES 提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列
VIEWS 给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息
TRIGGERS 提供了关于触发程序的信息。必须有super权限才能查看该表
performance_schema 87 setup_table 设置表，配置监控选项
current_events_table 记录当前那些thread 正在发生什么事情
history_table 发生的各种事件的历史记录表
summary_table 对各种事件的统计表
setup_consumers\setup_instruments 描述各种事件, 设置哪些事件能够被收集
setup_instruments 描述这个数据库下的表名以及是否开启监控
setup_timers 描述监控选项已经采样频率的时间间隔
threads 监控服务器所有连接
performance_timers 设置一些监控信息, 指定mysql服务可用的监控周期，CYCLE表示按每秒检测2603393034次, 目前 performance-schema 只支持’wait’时间的监控，代码树上 wait/ 下的函数都可以监控到
mysql 32 user 用户列、权限列、安全列、资源控制列
db 用户列、权限列
sys 1 sys_config 系统配置

性能优化

避免长事务

因为数据库操作过程中持有的锁基本要等事务结束之后才释放，所以尽量避免事务过长，不要在事务中引入rt较长的操作，例如rpc等。

事务内部将热点数据的操作放在后面，这样可以减少热点数据锁定的时间，提高系统的吞吐率。

取自“https://www.aigetting.com/index.php?title=Mysql&oldid=768”