MySQL进阶学习笔记。

MySQL进阶-1-架构介绍

1. MySQL简介

1.1 概述

  1. MySQL是一个关系型数据库管理系统(RDBMS),由瑞典MySQL AB 公司开放,目前属于ORACLE公司。
  2. MySQL是一种关系数据库管理系统,将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样增加了速度并提高了灵活性。
  3. MySQL是开源的,所以不需要支付额外的费用。
  4. MySQL支持大型的数据库,可以出来拥有成千上万条记录的大型数据库。32位系统表文件最大支持4GB、64位系统最大表文件支持8TB。
  5. MySQL使用标准的SQL数据语言形式。
  6. MySQL可以运行于多个系统,并支持多语言。包括:C、C++、Java、Python、Perl、PHP、Ruby等等。
  7. MySQL是可以定制的,采用了GPL协议,你可以修改源码来开发自己的系统。

1.2 MySQL进阶

  1. 内核
  2. SQL优化
  3. 服务器的优化
  4. 查询语句优化
  5. 主从复制
  6. 软硬件升级
  7. 容灾备份
  8. SQL编程

2. Linux下MySQL使用

2.1 MySQL5.5安装

  1. 下载地址:https://dev.mysql.com/downloads/mysql/

  2. 检查是否安装过MySQL

  3. 安装MySQL客户端、服务器端

    1
    2
    3
    4
    # 安装服务器端
    rpm -ivh --nodeps MySQL-server-5.5.54-1.linux2.6.x86_64.rpm
    # 安装客户端
    rpm -ivh --nodeps MySQL-client-5.5.54-1.linux2.6.x86_64.rpm
  4. 启动MySQL服务

    1
    service mysql start
  5. 设置root密码

    1
    sqladmin -u root password 123456
  6. 设置MySQL自启动

    1
    2
    3
    4
    # 设置自启动
    chkconfig mysql on
    # 检验是否设置成功
    ntsysv
  7. 修改配置文件位置

    1
    cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

    将share下单my-huge.cnf配置文件复制到etc目录下,并且注意文件名。

    不同版本的配置文件名可能不一样,如5.6的为my-defalut.cnf

  8. 设置字符集编码

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    # 查看字符集 [默认客户端和服务端动用了Latin1,使用中文会乱码]
    show variales like '%char%'

    # 修改配置文件
    vim /etc/my.cnf

    # 添加下面内容

    [client]
    default-character-set=utf8 # 设置编码

    [mysqld]
    lower_case_table_names=1 # 设置大小写区分情况
    character_set_server=utf8
    character_set_client=utf8
    collation-server=utf8_general_ci
    max_connections=1000 # 设置最大连接数

    [mysql]
    default-character-set=utf8
  9. MySQL一些目录

    linux下查看

    1
    ps -ef | grep mysql
    路径 解析 备注
    /var/lib/mysql mysql数据库文件的存放路径 /var/lib/mysql/xxx.pid
    /usr/share/mysql 配置文件目录 mysql.server命令及配置
    /usr/bin 相关命令目录 mysqladmin mysqldump等命令
    /etc/init.d/mysql 启停脚本目录

2.2 MySQL配置文件

文件 类型 备注
log-bin 二进制配置文件 主从复制用到
log-error 错误日志文件 默认关闭
log 查询日志 默认关闭
frm 数据文件:表结构
myd 数据文件:表数据
myi 数据文件:表索引
my.ini或my.cnf 配置文件

log-errror:默认关闭,记录严重的警告信息和错误信息,每次启动关闭的详细信息。

log:默认关闭,记录查询的sql语句,开启会降低mysql整体性能。

数据文件在windows和Linux平台下不同

windows:..\MySQL\MySQL Server 5.7\data

linux:/var/lib/mysql

3. MySQl逻辑架构介绍

3.1 总体概述

​ 和其他数据库相比,MySQL有点与众不同,他的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上。插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的引擎。

​ 下图展示了MySQL的框架图,包含了:连接层、服务层、引擎层、存储层

1596985484052

  1. 连接层

    ​ 最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务器端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、以及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供了线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

  2. 服务层

    ​ 第二层架构主要完成大多数的核心服务功能,如SQL接口、并完成缓存的查询,SQL的分析和优化部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。在该层,服务器会解析擦好像并创建相应的内部解析树,并对其完成相应的优化,如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读操作的环境能很好提升系统性能。

  3. 引擎层

    ​ 存储引擎层,存储引擎真正负责MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同存储引擎的功能不同,这样有利于我们根据实际生产环境进行选择。

  4. 存储层

    ​ 数据存储层,主要将数据存储在运行与裸设备的文件系统上,并完成与存储引擎的交互。

  5. Connector

    ​ 指的是不同语言中与SQL的交互

  6. Managemen Service & Utilities

    ​ 系统管理和控制工具

  7. Connection Pool

    ​ 连接池,管理缓冲用户连接,线程处理等需要缓存的需求。

    ​ 负责监听对MySQL Server的各种请求,接受连接请求,转发所有连接请求到线程管理模块。每一个连接上Server的客户端请求都会被分配一个线程来为其单独服务。而连接线程的主要工作就是负责MySQL Server与客户端的通信。

  8. SQL Interface

    ​ SQL接口,接受用户的SQL命令,并且返回用户需要的查询结果。比如select 就是调用SQL Interface。

  9. Parser:解析器

    ​ SQL命令传递到解析器会被解析器验证和解析。解析器有Lex和YACC实现,是一个很长的脚本。

    ​ 在Mysql中我们习惯将所有Client端发送给Server端的命令都成为query,在Mysql Server里面,连接线程接收到客户端的一个 Query 后,会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。

    ​ 主要功能:
    ​ a . 将SQL语句进行语义和语法的分析,分解成数据结构,然后按照不同的操作类型进行分类,然后做出针对性的转发到后续步骤,以后SQL语句的传递和处理就是基于这个结构的。
    ​ b. 如果在分解构成中遇到错误,那么就说明这个sql语句是不合理的

  10. Optimizer:查询优化器

    ​ SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query(sql语句) ,根据客户端请求的 query 语句,和数据库中的一些统计信息,在一系列算法的基础上进行分析,得出一个最优的策略,告诉后面的程序如何取得这个 query 语句的结果
    他使用的是“选取-投影-联接”策略进行查询。
    ​ 用一个例子就可以理解: select uid,name from user where gender = 1;
    ​ 这个select 查询先根据where 语句进行选取,而不是先将表全部查询出来以后再进行gender过滤
    ​ 这个select查询先根据uid和name进行属性投影,而不是将属性全部取出以后再进行过滤
    ​ 将这两个查询条件联接起来生成最终查询结果

  11. Cache和Buffer

    ​ 他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中,与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后, MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中, Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
    ​ 如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等

  12. 存储引擎接口

    ​ 存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中,基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类,但正是因为它成功地将各种数据处理高度抽象化,才成就了今天 MySQL 可插拔存储引擎的特色。
    ​ 从上图还可以看出,MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持,这些标准与存储引擎本身无关,可能是每个数据库系统本身都必需的,如SQL分析器和优化器等,而存储引擎是底层物理结构的实现,每个存储引擎开发者都可以按照自己的意愿来进行开发。
    ​ 注意:存储引擎是基于表的,而不是数据库。

3.2 MySQL存储引擎

  1. 查看存储引擎

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 查看提供的引擎
    show engines;

    -- 查看当前默认引擎
    show variables like "%storage_engine%";
    +------------------------+--------+
    | Variable_name | Value |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    | storage_engine | InnoDB |
    +------------------------+--------+
  2. MyISAM和InnoDB

    ​ 虽然Mysql提供了很多存储引擎,但我们常用的主要是:MyISAM和InnoDB,下面是两个存储引擎的对比。

    对比项 MyISAM InnoDB
    主外键 不支持 支持
    事务 不支持 支持
    行表锁 表锁,即使操作一条记录也会锁住整个表,不利于高并发 行锁,操作时只锁一行,不影响其他行,适合高并发。
    缓存 只缓存索引,不缓存真实数据 不仅缓存索引,还缓存数据,堆内存要求较高
    表空间
    关注点 性能 事务
    默认安装 Y Y
  3. 大厂使用的存储引擎

    Percona、阿里巴巴大部分Mysql数据库其实使用的percona的原型加以修改。

4. 总结

​ 这部分主要介绍了MySQL概述,MySQL的高级技术有哪些,MySQL5.5在Linux环境下的安装配置,MySQL5.5的配置文件结构,还结束了MySQL的逻辑架构。

​ MySQL逻辑架构可分为4层:连接层、服务层、引擎层、存储层。

评论