mysql高级（尚硅谷-夏磊）

目录

内容介绍

Linux下MySQL的安装与使用

Mysql逻辑架构

Mysql存储引擎

Sql预热

索引简介

---

内容介绍

1、Linux下MySQL的安装与使用 2、逻辑架构 3、sql预热

Linux下MySQL的安装与使用

1、docker安装docker run -d \-p 3309:3306 \-v /atguigu/mysql/mysql8/conf:/etc/mysql/conf.d \-v /atguigu/mysql/mysql8/data:/var/lib/mysql \-e MYSQL_ROOT_PASSWORD=123456 \--name atguigu-mysql8 \--restart=always \mysql:8.0.29 *查看容器 2、远程连接问题 （1）问题 （2）解决方案 #进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码 docker exec -it atguigu-mysql8 env LANG=C.UTF-8 /bin/bash #进入容器内的mysql命令行 mysql -uroot -p #修改默认密码校验方式 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 3、字符集 MySQL 8版本之前，默认字符集为 latin1（ISO-8859-1） ，不支持中文，使用前必须设置字符集为utf8（utf8mb3）或utf8mb4。从MySQL 8开始，数据库的默认字符集为 utf8mb4 ，从而避免中文乱码的问题。 4、sql_mode （1）是什么 Mysql提供的sql语法规范 （2）实例 CREATE DATABASE atguigudb; USE atguigudb; CREATE TABLE employee(id INT, `name` VARCHAR(16),age INT,dept INT); INSERT INTO employee VALUES(1,'zhang3',33,101); INSERT INTO employee VALUES(2,'li4',34,101); INSERT INTO employee VALUES(3,'wang5',34,102); INSERT INTO employee VALUES(4,'zhao6',34,102); INSERT INTO employee VALUES(5,'tian7',36,102); 需求：查询每个部门年龄最大的人 #查询每个部门年龄最大的人(错误写法) SELECT e.`dept`,MAX(e.`age`),e.`name` FROM employee e GROUP BY e.`dept`; #查询每个部门年龄最大           的人 SELECT e.`dept`,MAX(e.`age`)maxage FROM employee e GROUP BY e.`dept`; SELECT ee.*,e.`name` FROM employee e INNER JOIN ( SELECT e.`dept`,MAX(e.`age`)maxage FROM employee e GROUP BY e.`dept` )ee ON e.`dept` =ee.dept AND e.`age`= ee.maxage; ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，SELECT子句中只能包含函数和 GROUP BY 中出现的字段。

Mysql逻辑架构

1、逻辑架构图 下面是MySQL5.7使用的经典架构图，MySQL 8中去掉了Caches&Buffers部分： 2、客户端 MySQL服务器之外的客户端程序，与具体的语言相关，例如Java中的JDBC，图形用户界面SQLyog等。本质上都是在TCP连接上通过MySQL协议和MySQL服务器进行通信。 3、服务层 （1）连接层 第一件事就是建立 TCP 连接、身份认证、权限获取 （2）服务层 Management Serveices & Utilities： 系统管理和控制工具 SQL Interface：SQL接口：接收用户的SQL命令，并且返回用户需要查询的结果。 Parser：解析器：解析器中SQL 语句进行`词法分析、语法分析、语义分析`，并为其创建`语法树`。 Optimizer：查询优化器：  不改变查询结果前提下，调整sql顺序，生成执行计划 Caches & Buffers： 查询缓存组件：在MySQL 8之后就抛弃了这个功能。 （3）引擎层 负责MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信 4、存储层 所有的数据、数据库、表的定义、表的每一行的内容、索引，都是存在文件系统 上，以文件的方式存在，并完成与存储引擎的交互。 5、执行顺序 6、SQL执行流程（MySQL8） （1）.开启profiling SET profiling = 1; （2）显示查询` *执行sql SELECT * FROM atguigudb.employee; SELECT * FROM atguigudb.employee WHERE id = 5; *查看计划 SHOW PROFILES; （3）查看某个查询计划流程 SHOW PROFILE cpu,block io FOR QUERY 3;

Mysql存储引擎

1、MyISAM和InnoDB的区别

Sql预热

创建测试数据 CREATE TABLE `t_dept` (`id` INT NOT NULL AUTO_INCREMENT,`deptName` VARCHAR(30) DEFAULT NULL,`address` VARCHAR(40) DEFAULT NULL,PRIMARY KEY (`id`));CREATE TABLE `t_emp` (`id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(20) DEFAULT NULL,`age` INT DEFAULT NULL,`deptId` INT DEFAULT NULL,`empno` INT NOT NULL,PRIMARY KEY (`id`),KEY `idx_dept_id` (`deptId`)#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`));INSERT INTO t_dept(id,deptName,address) VALUES(1,'华山','华山');INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010); 2、常见七种JOIN查询 （1）查询所有有部门的员工信息以及他所在的部门信息SELECT * FROM t_emp a INNER JOIN  t_dept b ON a.`deptId` = b.`id`;（2）需求2：查询所有用户，并显示其部门信息（如果员工没有所在部门，也会被列出） => 查询A的全集SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.`deptId`= b.`id`;（3）需求3：列出所有部门，并显示其部门的员工信息（如果部门没有员工，也会被列出）=> 查询B的全集SELECT * FROM t_dept b LEFT JOIN t_emp a ON a.`deptId`= b.`id`;（4）**需求4：**查询`没有加入任何部门的员工`SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.`deptId`= b.`id` WHERE b.`id` IS NULL;（5）查询没有任何员工的部门SELECT * FROM t_dept b LEFT JOIN t_emp a ON a.`deptId`= b.`id`WHERE a.`id` IS NULL;（6）查询所有员工和所有部门 => AB全有SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.`deptId`= b.`id`UNION ALLSELECT a.*,b.* FROM t_dept b LEFT JOIN t_emp a ON a.`deptId`= b.`id`WHERE a.`id` IS NULL;（7）查询没有加入任何部门的员工，以及查询出部门下没有任何员工的部门SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.`deptId`= b.`id`WHERE b.`id` IS NULLUNION ALLSELECT a.*,b.* FROM t_dept b LEFT JOIN t_emp a ON a.`deptId`= b.`id`WHERE a.`id` IS NULL; 3、修改表，增加难度 （1）增加掌门人字段ALTER TABLE t_dept ADD CEO INT(11);UPDATE t_dept SET CEO=2 WHERE id=1;UPDATE t_dept SET CEO=4 WHERE id=2;UPDATE t_dept SET CEO=6 WHERE id=3;UPDATE t_dept SET CEO=8 WHERE id=4;UPDATE t_dept SET CEO=9 WHERE id=5;（2）求各个门派对应的掌门人SELECT * FROM t_emp a INNER JOIN t_dept bON b.`CEO` = a.`id`;（3）求所有掌门人的平均年龄SELECT AVG(a.`age`) FROM t_emp a INNER JOIN t_dept bON b.`CEO` = a.`id`;（4）求所有人物对应的掌门名称（4种写法分析）#1 NO3SELECT ab.name,c.`name` ceoname FROM(SELECT a.`name`,b.`CEO` FROM t_emp aLEFT JOIN t_dept b ON a.`deptId`=b.`id`)abLEFT JOIN t_emp c ON ab.ceo=c.`id`;#2 NO2SELECT c.name,ab.name ceoname FROM t_emp c LEFT JOIN(SELECT a.`name`,b.`id` FROM t_emp aINNER JOIN t_dept b ON b.`CEO` = a.`id`)abON c.`deptId`= ab.id;#3  NO1SELECT a.`name`,c.`name` ceoname FROM t_emp aLEFT JOIN t_dept b  ON a.`deptId`= b.idLEFT JOIN t_emp c ON b.`CEO`= c.`id`;#4  NO4SELECT a.`name`,(SELECT c.name FROM t_emp c WHERE c.id =b.`CEO`)ceonameFROM t_emp aLEFT JOIN t_dept b ON a.`deptId`=b.`id`;

索引简介

1、是什么 索引（Index）是帮助MySQL高效获取数据的数据结构。 排好序的快速查找数据结构 2、索引优缺点 （1）优点：查询快、排序快 （2）缺点：所有写操作变慢                  占用大量磁盘空间 3、索引分类 从功能逻辑上划分，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。 按照作用字段个数划分，索引可以分为单列索引和联合索引。 按照物理实现方式划分 ，索引可以分为 2 种，分别是聚簇索引和非聚簇索引。 4、树 （1）二叉树 对于二叉排序树的任何一个非叶子节点，要求左子节点的值比当前节点的值小，右子节点的值比当前节点的值大。 *最好情况 *最坏情况 （2）平衡二叉树（AVL） 具有以下特点： 它是一棵空树或它的左右两个子树的高度差的绝对值不超过1 并且左右两个子树都是一棵平衡二叉树。 缺点 那么磁盘的IO次数和索引树的高度是相关的。平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。 解决问题，可以使用平衡三叉树