Day130.MySQL高级：Liunx安装、三大范式、InnoDB、数据结构、B+树

目录

MySQL 高级

一、Linux 安装 Mysql

通过工具远程访问 (修改用户权限)

字符集问题

二、MySQL目录结构

三、用户与权限

四、数据库三大范式  ★

五、Mysql 架构

六、存储引擎 (InnoDB)

Profiles Mysql调优工具 1/3 

七、数据结构  ★★★

B 树  (Balanced 平衡)  ★★

B+ 树 (InnoDB 底层)  ★★

---

MySQL 高级

重点：1. Mysql底层数据结构：B+ 树 ； 2. 手写Sql

• MySQL的存储引擎：InnoDB
• 5.7的MySQL和 MariaDB 没有区别，是Mysql的一个分支，相互兼容。
• Mysql在开源界很强，闭源界很差。付费的Oracle、DB2性能要比Mysql好

MySQL高手是怎样炼成的（开发工程师，DBA，运维）

• 数据库内部结构和原理
• 数据库建模优化 (三大范式、索引) 员工表，需要哪些字段？
• 数据库索引建立
• SQL语句优化
• SQL编程(自定义函数、存储过程(sql复用)、触发器、定时任务)
• mysql服务器的安装配置
• 数据库的性能监控分析与系统优化
• 各种参数常量设定
• 主从复制 (高可用)
  1. 软冗余 避免多次查询
  2. 高可用，硬件冗余
• 分布式架构搭建、垂直切割和水平切割 - mycat
• 数据迁移
• 容灾备份和恢复
• shell或python等脚本语言开发
• 对开源数据库进行二次开发

一、Linux 安装 Mysql

官网：MySQL

下载地址：MySQL :: Download MySQL Community Server

查看系统版本：cat /proc/version

Centos7 使用的是Red Hat内核。

• 1、CentOS7系统，检查mysql依赖环境

查看是否安装过MySQL

rpm -qa | grep -i mariadb   CentOS7使用这个

-i忽略大小写

卸载MySQL; --nodeps 排除依赖，否则别的软件依赖mysql,无法删除

rpm -e --nodeps mariadb-libs

检查/tmp临时目权限；

chmod -R 777 /tmp

执行安装命令前，先执行查询命令检查依赖

rpm -qa|grep libaio

rpm -qa|grep net-tools

如果不存在需要到centos安装盘里进行rpm安装。安装linux如果带图形化界面，这些都是安装好的。

yum install -y perl-Module-Install.noarch

• 2、将安装程序拷贝到/opt目录下

解压缩：tar xvf mysql-8.0.29-1.el8.x86_64.rpm-bundle.tar

在mysql的安装文件目录下执行：（必须按照顺序执行）

rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-8.0.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-8.0.29-1.el7.x86_64.rpm
 

rpm 是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。

-i, --install      安装软件包

-v, --verbose    提供更多的详细信息输出

-h, --hash       软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

系统错误日志：/var/log/mysqld.log

• 3、Mysql5.7 服务的初始化 (sql8直接查看密码即可)

为了保证数据库目录为与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化：
mysqld --initialize --user=mysql 
另外 --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登录后你需要设置一个新的密码 
查看密码：cat /var/log/mysqld.log   root@localhost: 后面就是初始化的密码

• 4、启动Mysql

启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：systemctl restart mysqld.service
查看状态：systemctl status mysqld.service
查看进程：ps -ef | grep -i mysql

自启动：systemctl disable mysqld.service

不自启动：systemctl enable mysqld.service

• 5、首次登录

启动Mysql服务后，mysql -uroot -p 进行登录，在Enter password：输入初始化密码

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';  新密码太简单会报错*

(IDENTIFIED 指密码)

设置完密码就可以用新密码登录，正常使用数据库了，使用quit退出，重新登录。

修改 密码强度校验

SHOW VARIABLES LIKE 'vali%';
validate_password_length 8 # 密码的最小长度，此处为8。
validate_password_mixed_case_count 1 # 至少要包含小写或大写字母的个数，此处为1。
validate_password_number_count 1 # 至少要包含的数字的个数，此处为1。
validate_password_policy MEDIUM # 强度等级，其中其值可设置为0、1、2。分别对应：
【0/LOW】：只检查长度。
【1/MEDIUM】：在0等级的基础上多检查数字、大小写、特殊字符。
【2/STRONG】：在1等级的基础上多检查特殊字符字典文件，此处为1。
​
validate_password_special_char_count 1 # 至少要包含的个数字符的个数，此处为1。
set global validate_password.policy=0;

• 通过工具远程访问 (修改用户权限)

1. 先 ping 一下数据库服务器的ip地址确认网络畅通：ping 192.168.86.87 -t

2. 关闭防火墙 

systemctl stop firewalld.service &  

systemctl disable firewalld.service

3. 修改权限

远程访问，由于主机名不匹配，无法访问mysql服务器；需要在权限表添加或者修改host主机名。

查看权限表：select * from mysql.user \G;

修改 'root'@'root' 用户表方式 

Use mysql
update user set host = '%'  where user ='root';

添加用户方式
#Mysql8 创建用户，赋予远程主机访问权限
CREATE USER 'root'@'%' IDENTIFIED BY 'rooty';

grant all privileges on *.* to 'root'@'%' ;
#刷新用户权限
FLUSH PRIVILEGES;

 注意：Navicat 不支持 caching_sha2_password 加密方式，需要修改加密规则(或在登陆时指定)

1、登录Mysql：

mysql -u root -p

2、修改加密规则（可以直接复制）

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

注意：host 可能不是 localhost 是 %

//更新一下用户的密码（可以直接复制）

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

3、刷新权限（可以直接复制）

FLUSH PRIVILEGES;

4、重置密码

alter user 'root'@'localhost' identified by '123456';

5、查看用户信息

select host,user,plugin from user;

如果忘记密码怎么办？

强制修改密码：在/etc/my.cnf添加字段：skip_grant_tables=1，跳过权限验证

重启：systemctl restart mysqld

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; ，修改密码

修改密码强度校验：SHOW VARIABLES LIKE 'vali%';

 

validate_password_special_char_count 1 # 至少要包含的个数字符的个数，此处为1。
修改：set global validate_password.policy=0;

validate_password_length 8 # 至少所需的密码长度

详见mysql创建用户、用户授权（命令）_qq_42383787的博客-CSDN博客

• 字符集问题

5.7默认latin1字符编码，不支持中文
8.0 默认utf-8

查看编码：show variables like '%char%';

修改字符集

vim  /etc/my.cnf

[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

重启Mysql

systemctl restart mysqld

utf8mb4：utf8的加强，可以存放emjoy表情，以字符串存储，

utf8编码字符串每个字符占3字节，utf8mb4每个4字节

二、MySQL目录结构

binlog.xxx：记录存储的执行指令的日志；主从复制时，同步数据的文件。

undo_xxx：记录执行命令之前的命令；用于故障恢复

语法检查 sql_mode

sql_mode：在sql 执行前 进行语法检查，设置是否允许一些非法操作，比如允许一些非法数据的插入,数据查询等。可以进行修改，略。

三、用户与权限

权限管理可大可小，系统授权、

查看权限表：select * from mysql.user \G; 

ssl：加密方式 https中的s

Mysql8与Mysql5 加密区别：Mysql8：sha2 ；Mysql5：MP5。

1、创建用户：zhang3，密码123456

create user zhang3 identified by '123456';

2、登录张三，查看数据库

mysql -uzhang3 -p

3、授予张三test数据库下所有表的增删改查权限

grant all privileges on test.* to zhang3@'%';
4、登录张三用户，再次查看数据库

5、删除用户

drop user zhang3 ;  

• 授予权限：grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址
• 查看当前用户权限：show grants;
• 查看某用户的全局权限：select  * from mysql.user ;

• 收回权限：revoke  权限1,权限2,…权限n on 数据库名称.表名称  from  用户名@用户地址 ;

• 刷新权限：flush privileges; 

四、数据库三大范式  ★

1NF：原子性：确保表中每一列数据不可再分

2NF：唯一性：在1NF的基础上，每一行数据唯一性，确保列数据要跟主键有关联。(在1NF基础上消除非主属性对主码的部分函数依赖)

3NF：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第一范式（1NF）：单表字段不可分；
第二范式（2NF）：有主键，非主键字段依赖主键；(两表关联做引用)
第三范式（3NF）：非主键字段不能相互依赖。

但是，我们有时会故意添加冗余字段，减少多表查询。保证读的效率，牺牲写的效率。

阿里开发规范：多表联查不能超过三张表

冗余字段如何查询？：ES，建立反向索引。

单例模式：只有一个实例。Controller是单例的，需要高性能，舍弃状态

原因：文档数据库需要建立索引，不然查询效率极低。InnoDB 存储引擎 一条数据对应一条索引，外键也需要一条索引，会让查询变得复杂，效率变低。

五、Mysql 架构 (了解)

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

• 连接层 

Connectors 连接层：主要完成一些类似于连接处理、授权认证、及相关的安全方案。CS，需要进行网络上的适配

• 服务层

Connection Pool：内部连接池，接收请求。存储网络、线程等连接，节省资源。

Management Serveices & Utilities： 系统管理和控制工具；备份、恢复、管理、集群、元数据...

SQL Interface：SQL接口；接受SQL命令，并且返回需要查询的结果。

Parser：词法编辑器  (SQL命令传递到解析器的时候会被解析器验证和解析)

Optimizer：SQL语句在查询之前会使用查询优化器对查询进行优化。(指令重排，最优顺序)

Caches & Buffers：查询缓存(可能被复用的) & 缓冲(临时存储)，Mysql8去掉了这部分。

• 引擎层

数据如何在磁盘上进行存储与读取；服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同。

六、存储引擎 (InnoDB)

查看mysql提供什么存储引擎：show engines \G;

InnoDB 存储引擎

• 支持事物， 行级锁，外键
• 5.5之后，默认采用InnoDB引擎。
• 底层使用 B+树 数据结构存储

MyISAM 存储引擎

• 不支持外键，行锁，不支持事物；极其重要
• 适合海量的低价值数据，不需要改的数据。(如操作日志、用户行为)
• 5.5之前默认的存储引擎
• 底层使用 B树 数据结构存储

Federated引擎

• Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
• 服务器在数据库目录只创建一个表定义文件。文件由表的名字开始，并有一个frm扩展名。无其它文件被创建，因为实际的数据在一个远程数据库上。这不同于为本地表工作的存储引擎的方式。

Memory引擎

• 所有的数据只存在于内存当中。
• Memory表至少比MyISAM表要快一个数量级，但重启后数据会丢失

PERFORMANCE_SCHEMA

• 用于存储系统的环境变量、常量。Mysql内部系统使用。

Blackhole引擎

• 黑洞，没有实现任何存储机制，会丢弃所有插入的数据，不做任何保存。
• 适合集群，master和slave，master尽量不要干活，不存储也不提供对外的服务。
• 但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

CSV引擎 

• SCV excel文档形式，大数据使用较多。CSV引擎可以作为一种数据交换的机制，非常有用。
• CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

Archive引擎

• Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。适合日志和数据采集（档案）类应用。

Profiles Mysql调优工具 1/3 

利用show profiles 查看sql的执行周期

8.0

是否支持：select @@have_profiling;

是否打开：select @@profiling;

打开：set profiling=on;

查看sql执行信息：show profiles;

查看执行计划：show profile cpu,block io for query 9;

Mysql8.0不支持缓存，Mysql5.7支持缓存。(因为占用内存)

主要步骤： 

- checking permissions：检查权限   
- Opening tables：打开表
- init ： 初始化 
- System lock ：系统锁
- optimizing ： 优化sql
- statistics ： 统计
- preparing ：准备执行
- executing ：执行sql
- Sending data ：发送数据
- Sorting result ：排序
- end ：结束
- query end ：查询 结束
- closing tables ： 关闭表 ／去除TMP 表
- freeing items ： 释放
- cleaning up ：清理

七、数据结构  ★★★

现实中有一些想法和需求，我们需要在代码中实现。
数据结构是存储数据的过程、方式；算法是功能的具体实现方式，在bit世界解决问题的方案。

时间复杂度、空间复杂度是衡量算法好坏的标准。时空取舍需要权衡。

大O标记法：时间复杂度(运算次数)、空间复杂度(内存空间)

用数学的方式去描述复杂程度

Ο(1)＜Ο(log2N)＜Ο(n) ＜Ο(nlog2N)＜Ο(n^2)＜Ο(n^3)＜ Ο(n^k) ＜Ο(2^n)。

O(1)：数据量不管多少倍的增长，查找的执行效率和最初相同。

O(N)：数据流增长N倍，查询效率就会降低N倍

Hash最好，次之平衡数。

扩展：BT种子、比特币数据结构：默克尔树。先有子节点后有父节点。

• 二叉树

一个节点只能有两个子节点。左子节点 小于 本节点；右子节点大于等于 本节点。时间复杂度log(n)，数据量增长时可以保证查询效率。

存储字符串索引：ASCLL码

哈希算法(摘要算法)只取其中一段，容易产生冲突。

先序: 根左右；中序:左根右；后序:左右根。

缺点：Mysql索引id持续递增，容易退化成链表，因为无法控制根节点。(Log(n)变成了O(n))

树来存储索引，数据存储在最终的叶子节点上。磁盘上有顺序的链接了每一个值(因为树形结构范围查询效率低)。

• 平衡二叉树

二叉树的基础上，当左子树与子右树的高度差超过1级，就会引发自旋，保持数的平衡。 

数据存储在磁盘中，需要地址进行定位。当发生自旋时， 磁盘引用地址也需要改变。数据量越大，效率越低 (根节点变化，指针修改)，为了减少磁盘IO，还需要降低树的深度。

• 红黑树

使层级有了冗余。最低节点和最高节点的高度差可容忍两级，相比平衡树减少了自旋次数。底层算法实现十分复杂。

叶子节点存储了每一张表每一行的数据

• 多叉树 (N叉树)

多叉树允许每个节点有更多的数据项和更多的子节点。通过重新组织节点，减少节点数量；增加分叉，减少树的高度。对二叉树进行优化

每一个节点存储一个分页(默认16k) 。很多页连接在一起产生了文件。以页为单位访问磁盘系统。由于节点很多，层级结构还是非常高。分页也是操作系统、文件系统当中的概念，以页为单位去访问系统。

• B 树  (Balanced 平衡)  ★★

B树的每个节点拥有>=2的子节点，并且在树的建立上留有一定的余量，可以进行快速的内容填充而不需要重新调整树的平衡性(结构)，节约磁盘IO的使用 (文件引用地址真实存储在磁盘中的)。

节点里可以存储更多的数据，以减少树的高度 -》 旋转次数变少 -》 需要修改的数据变少。

注：文件的元数据信息，还包含了数据在磁盘中所处的 起始位置 和 文件大小 用于 寻址。

文件的元数据信息包含：
文件名、文件类型、大小、节点号、权限、所有者、所属组、链接数、时间。

黄色：指针P，存储子节点的信息地址；用于在磁盘上寻址，链接数据。类似Java引用地址。
红色：ID，表中记录的索引、主键。(键值)
蓝色：数据，记录表中除索引外的数据

以上图为例：若查询的数值为５：

　　第一次磁盘IO：根据根节点找到磁盘块1，读入内存，执行二分查找，比17小，根据指针P1，找左子树；

　　第二次磁盘IO：找到磁盘块2，读入内存，执行二分查找，比８小，根据指针P1，找左子树；

　　第三次磁盘IO：找到磁盘块5，读入内存，执行二分查找，找到5，终止。

整个过程中，我们可以看出：BTree相对于平衡二叉树降低了树的高度，缩减了节点个数，减少了I/O操作，提高了查询效率

还可以优化！让每个磁盘块存储更多的指针

B树 相比 普通树结构 优势在哪里？

平衡树 的建立又产生另外一个问题也就是节点的旋转，在插入/删除时会频繁的使用。因此，我们希望在树的建立上留有一定的余量，使得快速的插入/删除成为可能，而不需要每次都频繁的调整树的结构。

B树 的每个节点因为拥有>=2的子节点，其节点信息允许在子节点未完整填充时，可以进行快速的内容填充而不需要重新调整树的平衡性，也不会因为删除节点破坏树的平衡性，因此在插入/删除操作上更节约磁盘IO的使用。

• B+ 树 (InnoDB 底层)  ★★

B+树 的查找和Ｂ树一样，起始于根节点，自顶向下遍历。log(n)时间复杂度，能存储千万级数据

与B数的区别：B+树中间节点(非叶子节点)不存储数据，因此可存储的空间更大、索引更多。

所有 非叶子节点 只存放 索引和指针 (物理上指向磁盘位置)。(每一个节点存储一个分页(默认16k) ，如果一个索引包含(int+char)，为10字节 指针忽略不计，一个页就可以存储1600条，每个指针可以指向下一个磁盘页，两层就可以1600*1600)

所有的 叶子结点 存放了 数据、索引和指针，叶子节点之间通过指针相连！且叶子结点本身索引值的大小自小而大顺序链接。

底层会按照有序的方式去存储数据，数据存储是连续的(双向链表)，范围查找(ID、ASK码、日期)、比较查找时，可以达到连续读写的效果，使随机读写变成顺序读写，提高数据检索的效率，降低数据库的IO成本。(如分页查询，通过主键id作为唯一标识)(可以比作一盘糖果，每一次去取就是一次io，一次抓一把效率高)

补充：每个子节点都存储 父节点的索引(冗余字段)，因为只有叶子结点才存储数据，所以就算匹配到了也要定位到叶子结点。这也是聚簇索引效率高的原因，索引即数据，可以直接在非叶子节点取出数据，无需回表。

每一个节点至少需要两个索引，才能组成下一级的树(不然没法分叉)。

B-树中的旋转操作不同于自平衡二叉搜索树，并不改变树的结构，仅仅更改key的位置。
 

把一些记录放到页里的示意图就是（这里一页就是一个磁盘块，代表一次IO，注意：叶子节点之间通过指针相连，链表！）：

一个节点存储一个页，一个页默认16k，假设一个复合索引(int+char)+指针占用10节点，一个节点就可以存储1600个索引，每个指针又可以指向下一个磁盘页

上层不干活，底层干活，最终的叶子节点存储数据，假设一个数据1k指针索引忽略不计，每个叶子节点可存储16条数据

所以：1600 * 1600 * 16，三层就可以存储极大数据量的数据。 

简历怎么写？ 

精通sql语言，多年实战经验，可以快速开发复杂多表结构sql语句；

熟悉多种 mysql 存储引擎，对 Innodb 有深入研究，对b+数结构有深刻印象。

熟悉mysql索引，有过线上调优经验，熟练使用多种调试工具；

缺点：优化空间？

构建索引时耗时较长，每次创建索引重新构建一颗树；重复的索引键