Mysql 进阶学习.zip

# Mysql 进阶 # 第一章 架构介绍 ## 1.2 Linux 下安装 mysql 1. 通过 `wget` 在线下载对应的文件 ```shell wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.16-2.el7.x86_64.rpm-bundle.tar ``` 2. [环境监测]：检查是否按照过 mysql ```shell rpm -qa | grep -i mariadb ``` 如果有就去除对应的依赖 ```shell rpm -ev --nodeps {mysql 依赖名} ``` Centos 中默认安装了 mariadb，需要卸载，避免冲突 ```shell rpm -qa | grep mysql rpm -ev --nodeps {mariadb 依赖名} ``` 3. 解压对应的 mysql 压缩文件 ```shell tar -xvf mysql-8.0.16-2.el7.x86_64.rpm-bundle.tar ``` 4. 根据指定的安装对应的资源包(common -> libs -> client -> server) ```shell rpm -ivh mysql-community-common-8.0.16-2.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.16-2.el7.x86_64.rpm rpm -ivh mysql-community-client-8.0.16-2.el7.x86_64.rpm rpm -ivh mysql-community-server-8.0.16-2.el7.x86_64.rpm ``` 5. 启动和查看 mysql 服务 ```shell systemctl start mysqld systemctl status mysqld ``` 6. 查看 root 用户初始随机密码 ```shell cat /var/log/mysqld.log | grep password ```  7. 使用 root 用户登录 mysql ```shell [root@localhost ~]# mysql -u root -p Enter password: # 为了安全，这里并不会显示密码 ``` 8. 修改 root 用户的密码(第一次使用随机密码登录后，必须修改密码，否则不能进行任何操作) ```shell mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root.123456'; ``` 9. 修改 mysql 为开机启动 ```shell chkconfig mysqld on ``` 10. mysql 字符集编码 ```shell mysql> show variables like '%char%'; #查看字符集编码 ```  [修改字符集编码](https://blog.csdn.net/weixin_45591980/article/details/108358544?utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.control) 11. [Centos7 中 mysql8 的配置文件配置](https://blog.csdn.net/xm393392625/article/details/103136537) 12. mysql 数据文件目录: `/var/lib/mysql` ## 1.3 配置文件 - 二进制日志 log-bin: 主要用于 **主从复制** - 错误日志 log-error: (默认关闭)记录严重的警告/错误信息，以及每次启动关闭的信息 - 查询日志 log: (默认关闭)，记录查询的 sql 语句，如果开启会降低 mysql 的整体性能 - 数据文件: `/var/lib/mysql` 1. 数据库文件 - 对应数据库名的文件名 2. .frm 文件 - 存放对应的表结构 3. .myd 文件 - 存放对应的表数据 4. .myi 文件 - 存放对应的表索引 ## 1.4 逻辑架构介绍 和其他数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景应用并发挥良好作用，主要体现在**存储引擎**上 **插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离**，这种架构可以根据业务的需求和实际需求选择合适的存储引擎  1. 连接层 客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具的类似于 tcp/ip 的通信，主要完成一些类似于连接处理、授权认证、及相关的安全方案 在该层中引入了**线程池**的概念，为通过认证安全的客户端提供线程，同样在该层上可以实现基于 SSL 的安全链接，服务器也会为客户端验证它所具有的操作权限 2. 服务层 主要完成大多数和核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化及部分内置函数的执行 所有跨存储引擎的功能也在这一层实现(如过程、函数等)，在这里，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化(如确定查询表的顺序，是否利用索引等)，最后生成相应的执行操作 如果是 select 语句，服务器还会查询内部的缓存，如果缓存空间足够大，就能在具有大量读操作的环境中能够很好的提升系统的性能 3. 存储引擎层 真正负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎通信，不同的存储引擎具有的功能不同，可以根据自己的实际需要进行选取 4. 存储层 数据存储层，将数据存储在设备的文件系统之上，并完成与存储引擎的交互 ## 1.5 存储引擎 使用 `show engines` 可以查看 mysql 支持的存储引擎  **MyISAM 和 InnoDB 的对比： **  # 第二章 索引优化分析 ## 2.1 SQL 性能下降原因 1. **查询语句没有优化** 2. **索引失效** 3. **关联查询较多 join(设计缺陷/不得已的需求)** 4. 服务器调优及各个参数设置(缓冲，线程数等) 5. 等等 ## 2.2 常见通用的 Join 查询 ### SQL 执行顺序 程序员: SQL 语句编写顺序 数据库解析: 1. from 2. join 3. on 4. where 5. group by(开始使用select中的别名，后面的语句中都可以使用) 6. avg,sum.... 7. having 8. select 9. distinct 10. order by 11. limit ### Join 图 <img src="README.assets/image-20210613084352777.png" alt="image-20210613084352777" style="zoom:200%;" /> ### 建表 SQL ```sql CREATE TABLE `tbl_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `deptId` int(11) DEFAULT NULL, PRIMARY KEY (`id`) , KEY `fk_dept_id`(`deptId`) )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8; CREATE TABLE `tbl_dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deptName` varchar(30) DEFAULT NULL, `locAdd` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8; ``` ```sql INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD', 11); INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR', 12); INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK', 13); INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS', 14); INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD', 15); INSERT INTO tbl_emp(NAME, deptId) VALUES('z3', 1); INSERT INTO tbl_emp(NAME, deptId) VALUES('z4', 1); INSERT INTO tbl_emp(NAME, deptId) VALUES('z5', 1); INSERT INTO tbl_emp(NAME, deptId) VALUES('w5', 2); INSERT INTO tbl_emp(NAME, deptId) VALUES('w6', 2); INSERT INTO tbl_emp(NAME, deptId) VALUES('s7', 3); INSERT INTO tbl_emp(NAME, deptId) VALUES('s8', 4); INSERT INTO tbl_emp(NAME, deptId) VALUES('s9', 51); ``` ### 七种 JOIN 演示 1. 内连接 <img src="README.assets/image-20210613085734698.png" alt="image-20210613085734698" style="zoom:150%;" /> 2. 左外连接 <img src="README.assets/image-20210613090007317.png" alt="image-20210613090007317" style="zoom:150%;" /> 3. 右外连接 <img src="README.assets/image-20210613090219636.png" alt="image-20210613090219636" style="zoom:150%;" /> 4. 左不包括连接: 和右表做左外连接后，排除交集 <img src="README.assets/image-20210613090446924.png" alt="image-20210613090446924" style="zoom:150%;" /> 5. 右不包括连接：和左表做右外连接后，排除交集 <img src="README.assets/image-20210613090620214.png" alt="image-20210613090620214" style="zoom:150%;" /> 6. 全外连接: 由于 mysql 中并不支持 **FULL** **OUTER** 所以需要通过 `union` 使用(合并&去重) - 左外连接 + 右外连接 <img src="README.assets/image-20210613091354485.png" alt="image-20210613091354485" style="zoom:150%;" /> 7. 全不包括连接: 在全