mysql架构介绍

mysql简介：

  • MySQL是最流行的开源SQL数据库管理系统，由Oracle Corporation开发，分发和支持。
  • MySQL是一个数据库管理系统。
  • MySQL数据库是关系型的。
  • MySQL软件是开源的。
  • MySQL数据库服务器非常快速，可靠，可扩展且易于使用。

  • ySQL Server在客户端/服务器或嵌入式系统中运行。

    高手的 mysql 是怎样炼成的?

    完整的mysql优化需要很深的功底,大公司甚至有专门的DBA写上述

  • 数据库内部结构和原理

  • 数据库建模优化
  • 数据库索引的建立
  • sql语句优化
  • mysql 服务器的安装配置
  • 数据库的性能监控分析与系统优化
  • mysql服务器的优化
  • 各种参数常量设定
  • 查询语句优化
  • 主从复制
  • 分布式架构搭建,垂直切割和水平切割
  • 软硬件升级
  • 容灾备份与恢复
  • shell 或 python 等脚本语言开发
  • 对开源数据库进行二次开发

  • sql编程(自定义函数,存储过程,触发器,定时任务)

    mysql linux的安装(5.7)

    检查当前系统是否安装过mysql

  • 查看服务是否启动：service mysql status

  • 查看进程服务ps -aux | grep mysql

  • 查看端口netstat -apn | grep 3306

    安装mysql服务端

    安装mysql客户端

    查看mysql安装时创建的mysql用户和mysql组

  • cat /etc/group |grep mysql (查看mysql用户组)

    mysql服务的启动和停止

  • service mysql start 启动服务

  • service mysql stop 停止服务

    mysql服务启动后,开始连接

    mysql服务状态

  • 启动 mysql

systemctl start mysqld

  • 重启 mysql

systemctl restart mysqld

  • 停止 mysql

systemctl stop mysqld

  • 查看mysql 状态

systemctl status mysqld

修改配置文件的位置

路径 解释 备注
/var/lib/mysql mysql数据库文件的存放路径 默认路径
/usr/share/mysql 配置文件目录
/usr/bin 相关命令目录
/etc/init.d/mysql 启停相关脚本
/var/run/mysql 进程pid文件
/etc/systemctl/system/multi-user.target.wants/mysqld.server 服务器启停脚本

问题

  • linux 向表中插入一条中文字符报错误

    Incorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column ‘name’ at row

需要以下步骤：

  • 修改全局配置文件(vim etc/my.cnf)

character-set-server=utf8

  • 表修改类型字符集

alter table xs modify name varchar(30) character set utf8;

  • 修改已有数据库的字符集

alter database 库名 character set ‘utf8’;

mysql的用户与权限管理(远程访问mysql)

mysql 用户管理

  • 创建用户

    create User zs identified by ‘123123’

创建一个 用户名为: zs 密码为: 123123

  • 授权

    grant all privileges on . to root @’%’ identified by ‘123123’

  • 修改用户密码

    set password = password(‘123456’)

修改的是硬盘里面的数据,内存中的数据并没有修改,索引需要刷新内存中的数据使用 flush privileges;

  • 如果navite 远程连接失败 注意:防火墙要开放3306端口

    • 查看防火墙状态
      • systemctl status firewalld
    • 开启防火墙
      • service firewalld start
    • 关闭防火墙
      • service firewalld stop
    • 重启防火墙
      • service firewalld restart
    • 如果开启防火墙则需要开放端口
      • 开放3306
      • firewall-cmd —zone=public —add-port=3306/tcp —permanent
    • 重启防火墙
      • firewall-cmd —reload
    • 查看防火墙开放端口
      • firewall-cmd —list-ports

        mysql一些杂项配置

        sql_mode

  • 创建表 ```sql //创建table create table mytbl12( id int, NAME varchar(200), age int , dept int );

— 添加自增长 alter table mytbl12 modify column id int primary key auto_increment;

INSERT into mytbl12 values(null,’zs1’,1,11),(null,’zs2’,2,22), (null,’zs3’,3,33),(null,’zs4’,1,44),(null,’zs5’,5,55);

— 查找每个机构年龄最大的人 select name,dept,max(age) from mytbl12 group by dept;

结果为 zs1 11 3 zs4 44 5

  2. - 问题
  3. ```sql
  4. 11部门的 最高年龄的用户名为 zs3
  5. 44部门的 最高年龄的用户名为 zs5
  7. 我们发现上面的sql是有错误的。
  9. 正确的sql应该这样写:
  11. select * from mytbl12 m 
  12. inner join (select dept , max(age) mx from mytbl12 group by dept) e 
  13. on m.dept=e.dept and m.age=e.mx;
  16. 结果为
  17. 3    zs3 3   11  11  3
  18. 5    zs5 5   44  44  5
  • 结论: group by 使用原则 select 后面只能放函数 和 group by 后的字段

如何查看sql_mode

show variables like ‘%sql_mode%’;

把公司服务器的sql_mode验证修改为本地的服务器的sql_mode

mysql 配置文件

二进制日志 log-bin

  • 主从复制

    错误日志 log-err

  • 默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息

    查询日志 log

  • 默认是关闭的,记录查询的sql语句,如果开启会降低mysql的整体性能,因为记录日志会增加服务器负担

    数据文件

    两系统

  • win

mysql安装路径/dat

  • linux

看看当前系统中的全部库后再进去 默认路径：/var/lib/mysql
进入 var/lib/mysql文件夹内

ls -lf |grep ^d

frm文件

存放表结构

myd文件

存放表数据

myi文件

存放表索引

配置文件

windows配置文件

  • my.ini

    linux配置文件

  • /etc/my.conf

    mysql 逻辑架构介绍

    总体概览

    image.png

  • 和其他数据库相比,mysql有点与众不同,它的架构可以在多种不同场景中应用发挥良好作用,主要体现在存储引擎架构上。

  • 插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。
  • 这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

分为四层
image.png
image.png

执行步骤为以下几步

  1. mysql之外类型Java 程序访问 (Connectors)
  2. 和连接池进行沟通 ( Connection Pool )
  3. 缓存,缓冲查询 ( Caches )
  4. SQL接口分析sql ( SQL Interface )
  5. 解析器复杂sql 解析 ( Parser )
  6. 优化器,不影响结果进行优化,生成执行计划 ( Optimizer )
  7. 存储引擎按执行计划分类执行 ( Pluggable Storage Engines )
  8. 存入缓存 ( Buffers )

    利用 show profile 查看sql的执行计划

  • 修改配置文件 /etc/my.cnf
    • query_cache_type=1
    • 开启缓存
  • 开启 profiling
    • 查看 profiling 是否开启
      • show variables like ‘%profiling%’;
      • 开启这样设置 set profiling =1 ;
  • select * from 表名
  • show profiles; 查看执行计划
    • show profile cpu,block io for query ?

mysql 存储引擎

查看命令

  1. show engines; 
  3. show variables like '%storage_engine%'

innodb 和 myisam比较

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁,即使操作一条数据也会锁住整个表,不适合高并发操作 行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作
缓存 只缓存索引,不缓存真实数据 不仅缓存索引还缓存真实数据,对内存要求较高,而且内存大小对性能有决定的影响
表空间 小 大
关注点 (节省资源,小号少,简单业务)性能 事务(并发写,事务,更大资源)
默认安装 Y Y

行表锁谁会发生死锁?

行锁

什么情况下会用到 MyISAM存储引擎?

Mysql 自带的表都是用 MyISAM存储引擎,不会出现高并发,节省资源

各个存储引擎介绍

  • InnoDB

    InnoDB 是mysql 的默认事务型引擎,它被用来处理大量短期事务,除非有非常特别的原因需要使用其它的存储引擎,否则应该优先考虑 InnoDB 引擎。

  • MyISAM

    MyISAM 提供了大量的特性,包含全文索引,压缩,空间函数等,但 MyIsam 不支持事务和行级锁,有一个缺陷就是崩溃后无法安全恢复。

  • Archive

    Archive 档案存储引擎只支持 insert 和 select 操作,在 mysql 5.1之前不支持索引。 Archive 表适合日志和数据采集类应用。

  • Blackhole

    Blockhole 引擎没有实现任何的存储机制,它会丢弃所有插入的数据,不做任何保存。但服务器会记录 Blackhole 表的日志,所以可以用于复制数据库到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此不推荐使用

  • CSV

    CSV 引擎可以将普通的csv文件作为mysql的表来处理,但不支持索引,CSV引擎可以作为一种数据交换机制,非常有用,CSV存储的数据直接可以在操作系统中,用文本编译器,或者 excel 读取

  • memory

    如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用 memory 表是非常有用的 moemory 表至少比 myisam 表要快一个数量级别

  • federated

    federated 引擎是访问其它服务器的一个代理,尽管该引擎看起来提供了很好的跨服务器的灵活性,但也经常带来问题,因此默认禁止

阿里，淘宝使用的哪个？
image.png