mysql高级篇学习笔记

前言

基于尚硅谷宋红康康师傅的mysql高级篇的学习。感谢康师傅。感谢尚硅谷。

---

1 mysql安装及运行（linux环境）

1.1 安装前检查

• 如果你是用rpm安装, 检查一下RPM PACKAGE:

rpm -qa | grep -i mysql # -i 忽略大小写 

• 检查mysql service:

  systemctl status mysqld.service

1.2 MySQL卸载

①关闭 mysql 服务

systemctl stop mysqld.service

②查看当前 mysql 安装状况

rpm -qa | grep -i mysql

#或

yum list installed | grep mysql

③卸载上述命令查询出的已安装程序

yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

务必卸载干净，反复执行 rpm -qa | grep -i mysql 确认是否有卸载残留

④删除 mysql 相关文件

• 查找相关文件

find / -name mysql

• 删除上述命令查找出的相关文件

rm -rf xxx 

⑤删除 my.cnf

rm -rf /etc/my.cnf

1.3 安装

① 下载

下载地址

② 安装、运行及登录

安装方式

安装方式	特点
rpm	安装简单，灵活性差，无法灵活选择版本、升级
rpm repository	安装包极小，版本安装简单灵活，升级方便，需要联网安装
通用二进制包	安装比较复杂，灵活性高，平台通用性好
源码包	安装最复杂，时间长，参数设置灵活，性能好

注意：
rpm -ivh
i:install
v:提示
h:进度条

1.4 字符集问题

1.5 SQL书写规范（建议）

1.6 宽松模式和严格模式

2 数据目录

2.1 主要目录结构

2.2 查看默认数据库

2.3 数据库在文件系统中的表示

2.4 表在文件系统中的表示（InnoDB和MyISAM）

描述表结构：.frm文件
存储数据和索引（独立表空间）：.ibd文件

2.5 小结

---

3 用户权限与管理

3.1 用户管理

3.1.1 登录

mysql数据库中有一个user表，表中存放的是用户。

3.1.2 创建用户

3.1.3 修改用户名

3.1.4 删除用户

3.1.5 设置当前用户密码

注意修改密码的时候host是localhost还是%。

3.1.6 修改其他用户密码

3.1.7 mysql8密码管理（了解）

3.2 权限

3.2.1 权限列表及授权权限原则

3.2.2 授予权限

3.2.3 查看权限

3.2.4 收回权限

3.3 权限表

3.3.1 user表

3.3.2 db表

3.3.3 tables_priv表和columns_priv表

3.3.4 procs_priv 表

3.4 访问控制（了解）

3.5 角色管理

3.5.1 理解

3.5.2 创建角色

3.5.3 给角色赋予权限

举例：

3.5.4 查看角色的权限

3.5.5 回收角色的权限

3.5.6 删除角色

3.5.7 给用户赋予角色

3.5.8 激活角色、撤销角色及设置强制角色

4 逻辑架构

4.1 逻辑架构剖析

4.1.2 三大层（连接层、服务层、引擎层）

4.1.3 存储层

4.1.4 小结

4.2 SQL执行流程

4.2.1 MySQL中的SQL执行流程

4.2.2 MySQL8.0执行原理

4.2.3 MySQL5.7执行原理

4.2.4 oracle中SQL执行流程

4.3 数据库缓冲池

4.3.1 缓冲池 vs 查询缓存

4.3.2 缓冲池如何读取数据及查看/设置大小

5 存储引擎

5.1 查看及设置默认存储引擎

5.2 设置表的存储引擎

5.3 引擎介绍

5.3.1 InnoDB 引擎：具备外键支持功能的事务存储引擎

5.3.2 MyISAM 引擎：主要的非事务处理存储引擎

5.3.3 Archive 引擎：用于数据存档

5.3.4 引擎对比

5.4 MyISAM和InnoDB

5.5 课外补充

6 索引的数据结构

6.1 使用索引的原因

6.2 索引优缺点

6.3 InnoDB中索引的推演

6.3.1 设计索引

[1] 一个简单的索引设计方案

[2] InnoDB中的索引方案（迭代3次）

[3] B+树

6.4 常见索引概念

6.4.1 聚簇索引

6.4.2 二级索引（辅助索引、非聚簇索引）

6.4.3 联合索引

6.5 注意事项

6.6 MyISAM中的索引方案

6.6.1 MyISAM 与 InnoDB对比

6.6 MySQL数据结构选择的合理性

6.6.1 Hash结构

6.6.2 二叉搜索树

6.6.3 AVL树

6.6.4 B-Tree

6.6.5 B+Tree

6.6.6 几道思考题

6.6.7 R树

7 InnoDB数据存储结构

7.1 数据页内部结构

7.1.1 文件头部

7.1.2 文件尾部

7.1.3 页目录

7.1.4 页面头部

7.2 InnoDB行格式（或记录格式）

7.2.1 指定行格式的语法

7.2.2 COMPACT行格式

7.2.3 Dynamic和Compressed行格式

[1] 行溢出

[2] Dynamic和Compressed行格式

7.2.4 Redundant行格式

8 索引的创建与设计原则

知识补充：
constraint：表级约束，作用于一个列或者多个列。有四种可定义的约束：（主键、外键、唯一、检查）。

列级约束：作用于一个列，定义在列的后面，可以有多个约束，用空格隔开，有六种可定义的约束。

[constraint <外键约束名称>] foreign key(从表的某个字段) references 主表名(被参考字段)

#（从表的某个字段）的数据类型必须与主表名（被参考字段）的数据类型一致，逻辑意义也一样。

#（从表的某个字段）的字段名可以与主表名（被参考字段）的字段名一样，也可以不一样。

– foreign key :在表级指定子表中的列

– references :标示在父表中的列

• MySQL数据库中，unsigned表⾯含义是 '⽆符号’的意思,unsigned既为⾮负数，⽤此类型可以增加数据长度。

8.1 索引的声明与使用

8.1.1 索引的分类

8.1.2 创建索引

[1] 创建表的时候创建索引

[2] 在已经存在的表上创建索引

[3] 删除索引

8.2 MySQL 8.0索引新特性

8.2.1 支持降序索引

8.2.2 隐藏索引

8.3 索引的设计原则

8.3.1 准备数据

8.3.2 哪些情况适合创建索引

8.3.3 哪些情况不适合创建索引

9 性能分析工具的使用

9.1 数据库服务器的优化步骤

9.2 查看系统性能参数

9.3 统计SQL的查询成本：last_query_cost

9.4 定位执行慢的sql：慢查询日志

9.4.1 开启慢查询日志参数

9.4.2 查看慢查询数目及案例演示