MySQL 进阶01

最新推荐文章于 2022-04-25 20:19:47 发布
最新推荐文章于 2022-04-25 20:19:47 发布
阅读量350 收藏
点赞数
分类专栏： python笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wangjunwen1017/article/details/102891037
版权

文章目录


-----转自 【王者-YCZ】的笔记

知识体系介绍

数据库介绍
体系结构及管理
SQL开发规范和建议
索引及执行计划(查询优化)
存储引擎
日志和备份恢复
主从
架构

一、数据库介绍

1.数据库产品介绍
第一代: RDBMS , Oracle, DB2,MSSQL ,PG,MySQL 
第二代: RDBMS+NoSQL+数据库中间件
	NoSQL : redis ,MongoDB,Hadoop,Hbase,ES,CD
	数据库中间件: 读写分离(proxySQL,maxscale,MyCAT),分布式(MyCAT,DBLE)
	云数据库产品
第三代: NewSQL: PolarDB , TDB ,TiDB 	

RDBMS：关系数据库管理系统
查看数据库排名：http://www.db-engines.com/ranking
2.MySQL版本介绍与区别
1.推荐使用版本
5.6 : 5.6.33 5.6.34 5.6.36 5.6.38 5.6.40 
5.7 : 5.7.18 5.7.19 5.7.20 5.7.22 5.7.24 5.7.26
8.0 : 8.0.11之后的版本均可

较为推荐使用：GA后6-12月之间的版本
较为推荐二进制的版本：二进制版的好处是所有的软件都装在一个目录下，都在/usr/local里面，不像yum，四零八散。

2.版本之间的区别（5.6与5.7与8.0）
a.初始化数据:
	（5.7的命令） 
/usr/local/mysql/bin/mysqld --initialize-insecure  --user=mysql  --basedir=/usr/local/mysql --datadir=/data/mysql/data 
	（5.6的命令）
/usr/local/mysql/scripts/mysql_install_db --user=mysql  --basedir=/usr/local/mysql --datadir=/data/mysql/data

b.安全方面:
	5.7版本安全方面得到了加强，具体体现在(1)密码长度、(2)密码复杂度、(3)密码过期时间、
	(4)两种初始化: --initialize-insecure 无安全模式, --initialize 安全模式

c.用户管理方面：
	8.0版本以前可以直接使用grant创建用户并且授权；8.0版本以后必须先创建用户，再授权。

d.严格模式SQL_MODE:
	5.7及其之后的版本拥有严格模式，在存储数据时，存储超出长度的数据不会存进去，会直接报错；
	而5.6版本由于没有严格模式，在存储超出长度的数据时，会将数据截断为可以存储的最大长度存储。

二、体系结构和基础管理

1.基于C/S 模型
Linux下的mysql可以使用两种协议方式建立数据库连接：TCP/IP、Socket
Windows下的mysql可以使用两种方式建立数据库连接：TCP/IP、sharememory（共享内存）

示例（Linux）：
TCP/IP（远程、本地）：
mysql -uroot -p123 -h 10.0.0.51 -P 3306
Socket（仅本地）： 
mysql -uroot -p123 -S /tmp/mysql.sock
2.mysql实例

MySQL独占内存，是基于线程管理的

实例: mysqld + MasterThread + 干活Thread + 预分配内存
类比
公司: 老板+经理+员工+办公区

实例中的mysqld是守护进程，先启动它，再通过他再来启动其他干活的线程，并预分配内存。
3.mysqld的程序结构(一条SQL语句的执行过程) ---------重要

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t4efXXaU-1572831208517)(assets/1.png)]

连接层

  1. 提供连接协议
    TCP/IP
    socket文件
  2. 用户验证
  3. 分配连接线程
    接受用户SQL
    返回用户结果

SQL层

  1. 语法语义检查
  2. 对象权限检查
  3. SQL_MODE检查
  4. 解析---->解析树
  5. 预处理
  6. 优化器(代价最低)
  7. 执行,得出结果
  8. 缓存到query_cache,生产中实际是用第三方，如Redis,memcached,Tair缓存产品替代。
  9. 日志记录(binlog):变更类语句日志记录，备份恢复和主从复制。

存储引擎层
通过执行的结果，从磁盘上拿数据，结构化成表，显示出来。

流程解析：

连接层部分：
1.数据库一启动，连接层就会提供两种连接协议（TCP/IP，Socket）；
2.然后就验证用户的ip，端口，密码；
3.验证完成后，立马会给用户分配的一个专门的连接线程（每一个用户都会分配一个新的连接线程），
线程是用来接收用户请求的，也就是接收用户的SQL语句命令，然后提供返回结果；
4.连接层是不具体负责SQL语句处理的，要交给SQL层来处理；

SQL层部分：
1.SQL层拿到连接层传过来的SQL语句，先做语法分析，分析语法正确与否；
2.然后再做语义检查，检查是属于什么类型的语句，如DDL，DCL等等；
3.然后做用户对象的权限检查，查看该用户是否拥有权限；
4.接着是进行SQL_MODE的检查，严格、标准化数据的插入；
	5.6及之前的版本没有严格模式，如时间字段依然可以录入毫无意义的时间；
	5.7及之后的版本拥有严格模式，只要发现是违反数据类型的值，就会报SQL_MODE的错误；
总的来说，SQL_MODE检查就是检查插入数据有没有意义，合不合法，符不符合科学常识。
5.然后，是对SQL语句进行解析，解析时会生成解析树；
6.解析完后再进行预处理，也就是计算各个处理方式的代价模型，即消耗的资源；
7.然后交给优化器选出代价最低的处理方式，即开销最低的处理方式；
8.最后，通过优化器选择的处理方式执行该SQL语句，并且得出结果。

存储引擎层：
1.通过获取SQL层执行的结果，从磁盘上拿取数据（拿取后的数据是二进制的数据），再将二进制的数据结构化成表，显示出来。

最后，其实在SQL层部分还做了两件事情：
第一件事是查询缓存query_cache：
	第一次执行SQL语句，会将它生成一个哈希值，带着哈希值和查询结果，缓存到query_cache中。
第二件事是日志记录（binlog）:
	Mysql日常工作中会对变更类语句进行日志记录。
	
关于缓存：
	缓存功能是默认关闭，8.0版本以前可以手动开起来用，8.0版本以后取消了；
	因此，生产环境中，一般通过Redis，memcached，Tair缓存产品来替代。
关于日志记录（binlog）：
	默认情况下，日志记录功能是关闭的；
	binlog是备份恢复和主从复制所必须的一个日志。
4.MySQL的逻辑结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3s9nIc4X-1572831208519)(assets/2.png)]

库: 库名 + 库属性(字符集+校对规则)
表: 表名 + 表列(列名+数据类型+约束+其他属性) + 表行(记录) + 表属性(引擎+字符集+校对规则)
5.MySQL的物理结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hBNYSzzD-1572831208520)(assets/3.png)]

宏观物理结构

InnoDB :  
xxx.ibd  数据行和索引
xxx.frm  表结构

MyISAM : 
xxx.MYI   索引信息
xxx.MYD   数据行
xxx.FRM   表结构

5.5.6版本及之后的版本,默认的存储引擎是InnoDB

InnoDB微观物理结构

表空间数据文件: 
	段 : 一个表(非分区表)就是一个段。
	页 : 一个page默认是16KB,是MySQL最小的IO单元
  区 : 64个连续页,默认1M


系统磁盘扇区    512b
block         4kb
mysql_页      16kb
mysql_分区    1Mb，64个连续页

数据库优化：归根结底就是优化io，一方面是优化次数（IOPS），一方面是优化顺序。

常规优化:  人 ----> SQL语句  ----> IO(顺序，次数)  ----> 底层
架构思维:  分布式架构
6.用户，权限，连接，初始化配置，启动
用户
1.定义:
	名字@'白名单' 
白名单:可以允许登录MySQL的IP
oldguo@'localhost'											本地socket登录的用户
oldguo@'%'															所有远程用户
oldguo@'192.168.195.%'									允许某个网段ip(255.255.255.0)的用户
oldguo@'192.168.195.0/255.255.254.0'	 	允许某个子网的IP的用户
oldguo@'192.168.195.5%'  								允许192.168.195.50-59IP的用户

2.操作:
crete user 用户信息 identified by '密码';											创建用户
drop user 用户信息;																						删除用户
alter user 用户信息 identified by '新密码';										更改用户密码
select user,host ,authentication_string from mysql.user;		查询用户信息
权限
1.用户授权命令：
	grant 权限 on 范围 to 用户 identified by '密码'

2.用户收回权限命令：
	revoke 权限 on 范围 from 用户;
	
3.权限种类（ALL）： 
	SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
	
4.在授权命令加上with grant option，可以让授权的用户也可以进行授权的操作：
	grant 权限 on 范围 to 用户 identified by '密码' with grant option；

5.范围：
  *.* 					所有库所有表
  oldguo.*			某个库
  oldguo.t1			某个库中的某个表
 
6.mysql中权限的授权是相加操作，因此才会有收回权限的操作；
mysql中权限是用户的属性，linux中权限是文件和目录的属性。
连接
1.mysql自带的工具：
	mysql
	mysqldump

2.第三方工具：
	navicat
	powerdesigner（建模工具）
	
3.mysql连接常用参数
  -u         用户
  -p         密码
  -h         IP
  -P         端口
  -S         socket文件
  -e         免交互执行命令
  <          导入SQL脚本
初始化配置
1.初始化配置的方法：
  a.配置文件 (常规)
  b.命令行   (维护性操作)
  c.源码编译时

2.配置文件的格式：
[标签]
配置=xxxx

3.标签: 
  a.服务器端标签
  [server]  [mysqld]  [mysqld_safe]
  b.客户端标签
  [client]  [mysql] [mysqldump]
	c.常用
  [mysqld] : 主要控制服务器启动,正常工作模式
  [mysql]  : 主要影响本地客户端

4.配置文件的示例：
  vim /etc/my.cnf
  
  [mysqld]
  user=mysql 
  basedir=/usr/local/mysql
  datadir=/data/mysql/data 
  server_id=10
  port=3306
  socket=/tmp/mysql.sock 
  [mysql]
  socket=/tmp/mysql.sock
启动
1.脚本启动（自带）
	cd /usr/local/mysql/support-files
	./mysql.server stop				停止
	./mysql.server start			开启
	./mysql.server restart		重启

2.特殊启动方式（维护操作）
	mysqld_safe
	mysqld
示例：忘记本地管理员密码的处理方法
	1.关闭MySQL
		cd /usr/local/mysql/support-files
    ./mysql.server stop、

  2.启动到安全模式
  	mysqld_safe --skip-grant-tables --skip-networking &
  	
  3.修改密码
  	flush privileges;
  	alter user root@"localhost" identified by "123";

	4.重启数据库到正常模式
		mysqld restart

解析：
	--skin-grant-tables   跳过连接层密码验证；
	--skip-networking 		停掉连接层远程连接；
	登陆后没加载授权表，改不了密码；
	因此要flush privileges手动刷新授权表，才能修改密码。


3.多实例
	1.准备目录
		mkdir -p /data/3307/data
		
	2.授权
		chown -R mysql.mysql /data
		
	3.配置文件 
		vim /data/3307/my.cnf 
      [mysqld]
      user=mysql
      basedir=/usr/local/mysql
      datadir=/data/3307/data 
      server_id=20
      port=3307
      socket=/tmp/mysql3307.sock

	4.初始化数据 
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data

	5.启动3307端口的mysql
		mysqld_safe --defaults-file=/data/3307/my.cnf &

	6.本地连接
		mysql -S /tmp/mysql3307.sock
some command
查看mysql当前会话信息
show processlist  

查看非innodb引擎的数据表
select table_schema,table_name,engine from information_schema.tables 
where engine != 'innodb';
豆浆多放点糖
关注
专栏目录
oldguo DBA 的精品课程介绍
05-27 1265
课程介绍 本课程，适合具备一定Linux运维或者开发基础的朋友，课程定级中、高级DBA。 只要掌握80%，轻松助力薪资15k-25K。 课程内容均来自与MySQL官网+MySQL源码。 配套精品视频(2021 5月全新录制，版权所有：郭加磊 oldguo。)，获取方法私聊。 新版视频Demo已在Bzhan发布 1. 作者信息 姓名：郭加磊（Oldguo） 简介： 数据库内核技术专家、数据库解决方案架构师，多家培训机构数据库课程负责人。 2009年工作至今。 精通数据库产品：MySQL、Orac.
李玉婷老师MYSQL进阶01-基础查询
weixin_50374542的博客
04-13 251
李玉婷老师MYSQL02 DQL语言的学习 基础查询 ★ 条件查询 ★ 排序查询 ★ 常见函数 ★ 分组函数 ★ 分组查询 ★ 连接查询 ★ 子查询 √ 分页查询 ★ union联合查询 √ 进阶一：基础查询： select语法： SE
二.一条SQL更新语句是如何执行的
fengbird的博客
05-28 182
redo log 怎么理解？ 答：由于在 MySQL 里，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，InnoDB引入了redo log的设计，用的是WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，先用redo log将数据写入内存，将本次操作写入物理日志（持久化到硬盘），然后再在空闲的时候将数据同步到磁盘中，注意，redo log是将更新的数据直接的先写入.
Mysql核心基础入门
08-24
1. 配合产品、开发团队进行数据库设计和调优，对数据库整体架构提出建议；2. 负责数据库管理维护，监控及性能优化，维护生产环境数据库的稳定性和扩展性；3. 与开发团队紧密配合、跟进各项目的数据库需求，对各业务人员的SQL语句提供指导意见；4. 进行业务系统数据库的规划、设计、实施，设计并优化数据库物理建设方案
mysql运维必备知识点（转载至其他作者）
weixin_30362083的博客
05-30 256
（1）基础笔试命令考察 1.开启MySQL服务 /etc/init.d/mysqld start service mysqld start systemctl start mysqld 2.检测端口是否运行 lsof -i :3306 netstat -lntup |grep 3306 3.为MySQL设置密码或者修改密码 设置密码 ...
老男孩mysql参数配置_oldboy-MySQL数据备份多种参数详细介绍并实践
weixin_32390279的博客
01-26 157
linux培训班部分内容，培训班学员无需购买2016年新MySQL DBA课程实战视频，由互联网实战运维架构大牛老男孩主讲！2016新老男孩MySQL数据库备份与恢复实战讲解：oldboy-1-MySQL课前企业面试题考察oldboy-2-重要运维思想oldboy-3-数据备份的意义和重要性oldboy-4-数据库备份初步实践及备份原理oldboy-5-MySQL数据备份多种参数详细介绍并实践ol...
mysql进阶学习
10-15
MySQL 进阶学习 MySQL 进阶学习旨在帮助开发者更好地掌握 SQL 语言，提高数据库管理和开发能力。本资源摘要将涵盖 MySQL 进阶学习的主要知识点，包括数据库设计、SQL 优化、查询分析、索引优化、视图和存储过程等。...
MySQL进阶
12-14
DQL:查询语句 1. 排序查询 语法：order by 子句 order by 排序字段1 排序方式1 ， 排序字段2 排序方式2… 排序方式： ASC：升序，默认的。 DESC：降序。 注意： 如果有多个排序条件，则当前边的条件值一样时，才会...
MySQL-进阶.pdf
最新发布
05-04
### MySQL进阶知识点详解 #### 一、存储引擎 存储引擎是MySQL中非常重要的组成部分，它们决定了数据如何被存储和管理。不同的存储引擎提供了不同的功能和特性，可以根据具体的应用需求来选择最适合的存储引擎。 #...
mysql进阶（二）.pdf
09-01
MySQL是一种广泛使用的开源关系型数据库管理系统，其在企业级应用中占据重要地位。本篇主要探讨多表查询、交集、并集、差集、...总之，了解和熟练掌握这些MySQL进阶知识，对于提升数据库管理和应用开发的效率至关重要。
linux下mysql命令
oldboy
07-28 429
一、总结一下： 1.linux下启动mysql的命令：    mysqladmin start /ect/init.d/mysql start (前面为mysql的安装路径) 2.linux下重启mysql的命令：    mysqladmin restart /ect/init.d/mysql restart (前面为mysql的安装路径) 3.linux下关闭mysql的命令： ...
不登陆数据库执行mysql命令小结
weixin_34378922的博客
08-06 173
不登陆数据库执行mysql命令小结 （本文适合有一定mysql数据库经验的读者） ######################################################### #《老男孩linux运维培训学校》--mysql课程内容之 #不需登陆mysql数据库执行mysql命令的简单总结 #date:2011-08-06 #作者：老男孩 #QQ:31333...
MySQL 进阶02
OldBoy Python学习笔记
11-04 251
文章目录三、SQL开发的规范和建议1.SQL语句分类2.DDL规范3.DCL规范4.DML规范5.DQL规范6.关键字逻辑执行顺序7.关于删表的面试题8.group by四、索引原理和优化索引作用：提供了类似于书中目录的作用,目的是为了优化查询。1.MySQL的索引类型2.B树查找算法3.MySQL中innodb引擎的索引4.聚集索引构建B树结构5.辅助索引构建B树结构6.聚集索引和辅助索引最大的...
老男孩MysqlDBA专家班
wechat_fee1024的博客
04-25 1174
MySql 数据库配置优化 表示缓冲池字节大小。 推荐值为物理内存的50%~80%。 innodb_buffer_pool_size 用来控制redo log刷新到磁盘的策略。 innodb_flush_log_at_trx_commit=1 每提交1次事务同步写到磁盘中，可以设置为n。 sync_binlog=1 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。 innodb_max_dirty_pages_
mysql官方world数据库city、country表t100w数据库及表，oldguo老师案例数据库
ty3251778的博客
04-19 1716
mysql官方world数据库city、country表 完整版 mysql> use world; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in
mysql数据库常用语法
weixin_34192993的博客
05-16 128
1）登录mysql数据库。mysql –uroot –poldboy123mysql2)查看当前登录的用户。selectuser();3)创建数据库oldboy，并查看已建库完整语句。create database oldboy;show databases;show create database oldboy;4)创建用户oldboy，使之可以管理数据库oldboy...
linux+32+mysql_Linux运维必会MySQL30道基础命令
weixin_42280315的博客
01-18 142
Linux运维必会MySQL 30道基础命令老男孩老师教学与培训核心思想：重目标、重思路、重方法、重实践、重习惯、重总结。版权声明：余连辉本系列文档为《老男孩 Linux 运维实战培训中心》内部教学用教案，只允许 VIP 学员 个人使用，为保护大家的学习利益，禁止私自传播，违者将取消 VIP 学员资格。严重者我们将法律起诉。如果你已经参加本培训，即视为你已无条件接受上述内容说明！联系方式：意见投诉...
Mysql试题
weixin_34097242的博客
05-01 546
1.登陆MySQL数据库。mysql -uroot -poldboy1232.查看当前登录的用户。select user();3.创建数据库oldboy，并查看已建库完整语句。create database oldboy;show databases;show create database oldboy;4.创建用户oldboy，使之可以管理数据库oldboy。create...
oldboy.25课--Linux常用150个命令
weixin_30340353的博客
12-09 157
1.线上查询及帮助命令（2个） 帮助命令man，用法man ls;命令 --help，用法ls --help；查看内置命令，用法help cd。 2.文件和目录操作命令（19个） ls list directory 列出目录下文件 cd 进入目录 cp copy 复制、粘贴一起 cp yum.log /root find 查找 find / -name "*.log" mkdir m...
MySQL进阶实践：查询优化与高可用性策略
"MySQL进阶：优化查询性能与高可用性的实践" MySQL数据库在当今的互联网环境中扮演着核心角色，尤其在大数据量的场景下，优化查询性能和保证高可用性显得尤为重要。本篇将深入讨论这两个关键方面。 一、查询优化 ...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值