MySQL高级——数据库优化（上）

mysql基础学习视频：https://www.bilibili.com/video/BV1xW411u7ax?p=1

一、MySQL的架构介绍

MySQL简介

概述

高级MySQL

MySQL 安装

这里用docker来安装，版本是8.0.18。

# 拉取镜像
docker pull mysql:8.0.18

# 运行一（现在我用的mysql之前用的运行命令）
docker run -p 33060:3306 --name mysql8_33060 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.18

# 容器内mysql重要文件/目录位置
# /etc/mysql/my.cnf  -> 映射配置文件
# /var/lib/mysql -> 映射数据

# 运行二		-v 对容器内的目录进行挂载
docker run -p 33060:3306 --name mysql8_33060 \
-v /home/docker/mysql8_33060/conf.d/my.cnf:/etc/mysql/my.cnf \
-v /home/docker/mysql8_33060/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.18

#在本地打开一个终端，进入容器的mysql（-h填linux的ip地址）
mysql -uroot -p123456 -h10.211.55.26 -P33060

#查看所有数据库
show databases;

#创建数据库
create database db01;

#切换数据库
use db01;

#查看此数据库下的所有表
show tables;

#创建表
create table user(id int not null,name varchar(20));

#插入数据
insert into user values(1,'张三');

#查看user表所有数据（显示的数据没有出现乱码）
select * from user;

MySQL逻辑架构介绍

总体概览

MySQL逻辑架构

1. 连接层

2. 服务层

3. 引擎层

4. 存储层

MySQL存储引擎

查看命令

show engines;

show variables like '%storage_engine%';

对比 MyISAM 和 InnoDB

MyISAM关注的是性能。
InnoDB关注的是事务。

阿里与淘宝

二、索引优化分析

性能问题

性能下降SQL慢：执行时间长，等待时间长。

1. 查询语句写的不好

2. 索引失效

   • 单值
     

   • 复合
     

3. 关联查询太多join（设计缺陷或不得已的需求）

4. 服务器调优及各个参数设置（缓冲、线程数等）

常见通用的Join查询

SQL执行顺序

手写

机读

对于mysql而言，机读的时候从from开始。

总结

Join图

内连接

左右表共有的部分
select <select_list> from tableA A inner join tableB B on A.key = B.key

左连接

左表的全部：左右表共有的和左表独有的部分
select <select_list> from tableA A left join tableB B on A.key = B.key

左连接（不包括公共部分）

左表独有的部分
select <select_list> from tableA A left join tableB B on A.key = B.key where B.key is null

右连接

右表的全部：左右表共有的和右独有的部分
select <select_list> from tableA A right join tableB B on A.key = B.key

右连接（不包括公共部分）

右表独有的部分
select <select_list> from tableA A right tableB B on A.key = B.key where A.key is null

全连接

全部
select <select_list> from tableA A full outer join tableB B on A.key = B.key

全连接（不包括公共部分）

左表独有的和右表独有的部分
select <select_list> from tableA A full outer join tableB B on A.key = B.key where A.key is null or B.key is null

建表SQL

CREATE DATABASE db02;

USE db02;

#部门表
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 DEFAULT CHARSET=utf8;

#员工表
CREATE TABLE `tbl_emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	PRIMARY KEY(`id`)
	#CONSTRAINT `fk_dept_id` FOREIGN KEY(`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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);

7种JOIN

1. 内连接：select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
   

2. 左连接：select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
   

3. 左连接（不包括公共部分）：select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
   

4. 右连接：select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
   

5. 右连接（不包括公共部分）：select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
   

6. 全连接：select * from tbl_emp a left join tbl_dept b on a.deptId = b.id union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
   union自带去重。
   

   mysql不支持全连接这种语法：
   

7. 全连接（不包括公共部分）：select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
   

索引简介

索引是数据结构，可以简单理解为“排好序、查找快的数据结构”，所以，对查找和排序都有影响。

详解（重要）

结论

优势

劣势

mysql索引分类

1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。索引列的值必须唯一，但允许有空值。
2. 唯一索引：即一个索引包含多个列。
3. 复合索引：

基本语法：

使用ALTER命令：

mysql索引结构

1. BTree索引
   索引原理：
   
   
   
   
2. Hash索引
3. full-text全文索引
4. R-Tree索引

需要创建索引的情况

不需要创建索引的情况

性能分析

MySQL Query Optimizer

MySQL常见瓶颈

Explain

是什么（查看执行计划）

官方文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

能干嘛

怎么玩

各字段解释

id（重要）

有三种情况：

1. id相同，执行顺序由上至下
   
2. id不同，如果是子査询，id的序号会递增，id值越大优先级越高，越先被执行。
   
3. id相同的和不同的，同时存在。
   

select_type

table

显示这一行的数据是关于哪张表的。

type（重要）

访问类型排列：

常用的类型排列：

1. system（系统表，基本不会出现）

2. const（单表主键查询，结果唯一）
   
   

3. eq_ref（多表主键联合查询，结果唯一）
   
   
   const是单表唯一，where用主键中查询单条记录，而eq_ref是多表关联，上面多表查询的where条件是t1.id=t2.id，而此id只有一个，只能查到一条记录，而得到两张表的字段。
   所以，const是单表查询结果唯一，而eq_ref是多表查询结果唯一。（如查找条件为主键）

4. ref（查询指定值，结果可能不唯一）
   
   
   如查找条件为姓名这种可能重复，查找后可能得到多条数据。

5. range（查询一个范围的值，范围查询）
   
   
   查询条件指定一个范围（between、>、<、in），而不是单个值。

6. index（全索引查询）
   
   
   只扫描全索引。

7. ALL（全表查询）
   
   
   扫描全表，百万级别的数据时要避免全表扫描，需要优化。

possible_keys

理论上会用到的索引，null为没使用索引。

key（重要）

实际上使用的索引，null为没使用索引，或索引失效。

key_len

ref

rows（重要）

越少越好。

filtered

5.7之后的版本就有了这个字段，不需要使用explain extended。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例（百分比，不是具体记录数）。

Extra（重要）

1. Using filesort：
   
   
   文件内排序，内部重新进行排序。

2. Using temporary：
   
   
   内部创建临时表。

3. Using index：
   
   
   
   

4. Using where：
   

5. Using join buffer：
   

6. impossible where：
   
   

7. select tables optimized away：
   

8. distinct：
   

热身Case

因为接下来的索引优化篇幅有点长，所以单独放到了下一篇里。

下一篇笔记：MySQL高级——数据库优化（中）

学习视频（p1-p30）：https://www.bilibili.com/video/BV1KW411u7vy?p=1