docker run -d -p 3306:3306 --privileged=true -v /usr/local/mysql/log:/var/log/mysql -v /usr/local/mysql/data:/var/lib/mysql -v /usr/local/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=root --name mysql mysql:8.0
Mysql概述
数据库
- 数据库的英文，DataBase 简称DB
- 数据库的管理系统：操作管理数据的大型软件 DBMs
- SQL: 操作关系型数据库的编程语言
主流的关系型数据库管理系统
- Oracle
- MySql [开源免费]
- Microsoft SQL Server
- PostgreSQL
统一标准SQL
- 可以操作各种关系型数据库
Mysql数据库的版本
- 社区版
- 商业版
关系型数据库
通过表结构存储的数据【关系型数据库】
不通过表结构存储数据【非关系型数据库】
数据模型
客户端 -> DBMS[MySql管理系统] -> 数据库 -> 表 -> 数据[行列]
SQL
SQL的通用语法
- SQL语句可以单行或多行书写，以分号结尾
- SQL语句可以使用空格、缩进来增强语句的可读型。
- Mysql数据库的SQL语句不区分大小写，关键字建议使用大写
- 主解：
- 单行注释 - - 或 #
- 多行注释 / /
SQL语言的分类
- DDL:数据定义语言，用来创建数据库，表，字段 , 索引
- DML:数据操作语言, 用来对数据库表中的数据进行增删改
- DQL:数据查询语言，用来查询数据库中表的记录
- DCL:数据控制语言，用来创建数据库用户，控制数据库访问权限
DDL[数据库操作]
-
数据库操作
查询所用数据库 show databases; 创建数据库[可选：if not exists 如果不存在创建，存在不报错] create database [if not exists] 数据库名; 创建数据库[可选：default charset utf8mb4 指定字符集] create database 数据库名 [default charset utf8mb4] 删除数据库[可选：if exists 如果存在删除，不存在不报错] drop database if exists 数据库名; 使用切换数据库 use 数据库名 查询当前所在的数据库 select database(); -
表操作
查看当前数据库中的所有表 show tables; 查看表结构 desc 表名; 查看表的建表语句[存储引擎，字符编码，排序方式，注释] show create table 表名 创建表[comment：表示注释，可以给字段和表加注释] create table 表名( id int comment '编号', name varchar(50) comment '姓名', age int comment '年龄'， gender varchar(1) comment '性别' ) comment '用户表'; 添加字段[指定:类型 varchar 字段注释 comment] alter table 表名 add 字段 varchar(20) comment '昵称' 修改表名 alter table 表名 rename to 新表名; 删除字段 alter table 表名 drop 字段名 删除表[指定：if exists 存在删除，不存在不报错] drop table if exists 表名 删除指定表，并重新创建该表[指删除数据，只剩表结构] truncate table 表名; -
修改字段名和数据类型
修改字段[指定修改：数据类型] alter table 表名 modify 字段名 新数据类型(长度) comment 注释; alter table 表名 change 久字段名 新字段名 类型(长度) comment 注释 [约束]; -
删除字段
alter tble 表名 drop 字段名;
DML[增删改]
添加(insert)
添加单条数据[指定字段，添加全部字段]
给指定字段添加数据
insert info 表名 (字段1,字段2 ...) values (值1,值2 ...);
给全部字段添加数据
insert info 表名 values (值1,值2);
批量添加数据
给指定字段添加多条数据[不指定字段名]
insert info 表名 (字段1,字段2 ...) values (值1,值2),(值1,值2)...
给全部字段添加多条数据
insert info 表名 (值1,值2),(值1,值2)...
注意事项：
插入数据时，指定的字段顺序需要与添加值的顺序是一一对应
字符串和日期数据应该包含在引号中
插入数据大小，应该在字段的规定范围内
插入多条数据时，多个值之间需要逗号分隔
修改(update)
修改指定字段的值[指定：where修改条件]
update 表名 set 字段1=值1 , 字段2=值2 ... where 修改条件
注意事项：
修改条件可以有，也可以没有，如果没有，则会修改整张表的所有数据
删除(delete)
删除指定条件的数据
delete from 表名 where 删除条件
注意事项：
删除条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
delete语句不能删除某个字段(可以使用update)
DQL[查询]
-
DQL语句，数据查询语言，用来查询数据库种表的记录
-
查询关键字: select
-
查询条件，范围查询，分页展示，排序
-
DQL语句的语法
DQL语法 SELECT 字段列表 FROM 表名 WHERE 条件列表 [] GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMT 分页参数 条件查询 [where] 聚合查询 [count、max、min、avg、sum] 分组查询 [group by] 排序查询 [order by] 分页查询 [limit] -
基本查询
查询多个字段 select 字段1,字段2 ... from 表名; 查询全部字段 select * from 表名; 设置别名[字段] select 字段1 [AS 别名1] , 字段2 [AS 别名2] ... from 表名; 去除重复字段[字段不重复] select distinct 字段 from 表名; -
条件查询
-
比较运算符
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 BETWEEN…AND… 在某个范围之内(含最小、最大值) IN(…) 在in之后的列表中的值，任意一个成立 LIKE 占位符 模糊匹配(_匹配单个字符, %匹配任意个字符) IS NULL 是null -
逻辑运算符
逻辑运算符 功能 AND 或 && 并且(多个条件同时成立) OR 或 || 或者(多个条件任意一个成立) NOT 或 ！ 非，不是
-
-
聚合函数
-
将一列数据作为一个整体，进行纵向计算。
-
作用于某一列数据
-
常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
语法
select 聚合函数(字段列表) from 表名; -
注意：null值不参与聚合函数运算
-
聚合函数的执行，在where条件之后
-
-
分组查询
-
语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]; -
where 与having区别
- 执行时机不同：where 是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同，where不能对聚合函数进行判断，而having可以。
-
根据字段进行分组
-
[ having ] 分组之后的过滤条件
-
分组一般和聚合一起配合使用
-
注意：
- 执行顺序：where > 聚合函数 > having
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
-
-
排序查询
-
语法：
select 字段列表 from 表名 order by 字段1 排序方式1 , 字段2 排序方式2; -
排序方式
- ASC : 升序 (默认值) 可以省略
- DESC : 降序
-
注意：如果是多字段排序，当第一字段值相同，才会根据第二个字段进行排序。
-
-
分页查询
-
语法
select 字段列表 from 表名 limit 起始索引 , 查询记录数; -
注意：
-
起始索引从0开始，起始索引 = (查询页码 - 1) * 每页记录数。
-
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
-
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。
-
-
-
DQL语句执行顺序
-
编写顺序
select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页字段 -
执行顺序
from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 select 字段列表 order by 排序字段列表 limit 分页参数
-
-
总结
select 字段列表 字段名 [AS] 别名 from 表名 where 条件列表 > >= < <= = <> like between...and in and or 【分组之前过滤】 group by 分组字段列表 having 分组后条件列表 【分组之后过滤】 order by 排序字段列表 升序ASC , 降序DESC limit 分页参数 起始索引(从0开始) ，每页展示记录数
DCL[访问权限]
-
DCL全称数据库控制语言，用来管理数据库 用户、控制数据库的访问权限。
-
控制范围
- 主要控制有哪些用户可以访问
- 每个用户具有什么样的访问权限
-
用户的信息都存在系统自带 mysql 库的 user表中
-
管理用户
-
查询用户
use mysql; select * from user; -
创建用户[任意主机%]
sreate user '用户名' @ '主机名' [任意主机%] identified by '密码'; -
修改用户密码
alter user '用户名' @ '主机名' identified with mysql_native_password by '新密码'; -
删除用户
drop user '用户名' @ '主机名';
-
-
注意
- 主机名可以使用%通配符
- 这类SQL开发人员操作的比较少，主要是DBA（数据库管理员）使用。
-
权限控制
权限 说明 ALL,ALL PRIVILEGES 所有权限 SELECT 查询数据 INSERT 插入数据 UPDATE 修改数据 DELETE 删除数据 ALTER 修改表 DROP 删除数据库 / 表 / 视图 CREATE 创建数据库 / 表 -
查询权限
show grants for '用户名' @ '主机名'; -
授予权限
grant 权限列表 on 数据库名.表名 to '用户名' @ '主机名'; -
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名' @ '主机名';
-
函数
函数是之一段可以直接被另一端程序调用的程序代码。
字符串函数
| 函数 | 功能 |
|---|---|
| concat(S1,S2,S3,…Sn) | 字符串拼接，将S1,S2, … 拼接成一个字符串 |
| lower(str) | 将字符串str全部转为小写 |
| upper(str) | 将字符串str全部转为大写 |
| lpad(str , n , pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| rpad(str , n , pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| trim(str) | 去掉字符串头部和尾部的空格 |
| substring(str , start , len) | 返回总字符串str从start位置起的len个长度的字符串 |
# concat[字符串拼接]
select concat('hello' , ' Mysql');
#lower[将全部字符串转换为小写]
select upper('HOLLE');
#upper[将全部的字符串转化为大写]
select upper('holle');
#lpad[左侧填充，第一个参数填充的字符串，第二个参数到达的长度，第三个参数填充的内容]
select lpad('01',10,'$');
#rpad[右侧填充，第一个参数填充的字符串，第二个参数到达的长度，第三个参数填充的内容]
select rpad('02',10,'￥');
#trim[去除头部和尾部的空格]
select trim(' h e l l o ');
#substring[截取字符串，下标从1开始，第一个参数截取的字符串，从哪开始截取，截取几个字符串]
select substring('hello Mysql',1,7);
数值函数
| 函数 | 功能 |
|---|---|
| ceil(x) | 向上取整 |
| floor(x) | 向下取整 |
| mod(x , y) | 返回x / y 的模 |
| rand() | 返回0-1内的随机数 |
| round(x , y) | 求参数x的四舍五入的值，保留y位小数 |
#ceil[向上取整]
select ceil(1.1);
#floor[向下取整]
select floor(1.9);
#mod[取模]
select mod(7,4);
#rand[0-1随机小数]
select rand();
#round[需要保留的小数位，四舍五入]
select round(1.225,2);
日期函数
| 函数 | 功能 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| year(date) | 获取指定date的年份 |
| month(date) | 获取date的月份 |
| day(date) | 获取指定date的日期 |
| date_add(date , interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| datediff(date1 , date2) | 返回起始时间date1和结束时间date2之间的天数 |
#当前日期
select curdate();
#当前时间
select curtime();
#year month day
#当前年份
select year(now());
#当前月份
select month(now());
#当前天
select day(now());
#当前时间往后推多少时间
select date_add(now(),interval 70 year );
#两个指定的时间相差的天数[前面的减去后面的]
select datediff('2089-12-12','2020-12-12');
流程函数
- 流程函数也是很常用的一类函数，可以在sql语句中实现条件筛选，从而提高语句的效率。
| 函数 | 功能 |
|---|---|
| if(value , t ,f) | 如果calue为true，则返回t , 否则返回f |
| ifnull(value , value2) | 如果value1不为空，返回value1,否则返回value2 |
| case when [val1] then [res1] … else [efault] end | 如果val1为true , 返回res1 ，否则返回default默认值 |
| case [expr] when [val1] then [res1] … else [default] end | 如果expr的值等于val1 , 返回res1 ，否则返回default默认值 |
约束
-
概念：约束是作用于表中字段上的规则，用于限制储存在表中的数据。
-
作用：保证数据库中数据的正确、有效性和完整性。
-
分类：
约束 描述 关键字 非空约束 限制字段的数据不能为null not null 唯一约束 保证该字段的所有数据都是唯一、不重复的 unique 主键约束 主键是一行数据的唯一标识，要求非空且唯一 primary key 默认约束 保存数据时，如果未指定该字段的值，则采用默认值 default 检查约束 保证字段值****满足某一个条件 check 外键约束 用来让两张表的数据之间建立连接，**保证数据的一致性和完整性 foreign key create table user( id int primary key auto_increment comment '主键', name varchar(10) not null unique comment '名字', age int check ( age > 0 and age <= 120 ) comment '年龄', status char(1) default '1' comment '状态', gender char(1) comment '性别' ) comment '用户表'; -
注意：约束是作用域表中字段上的，可以在创建表/修改表的时候添加约束。
外键约束
-
概念：未见用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
-
通常将具有外键的表称为子表，外键所关联的表称为父表(从表)
-
注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。
-
语法：
-
添加外键
方式一： create table 表名( 字段名 数据类型, ... [constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名) ) 方式二： alter table 表名 add constraint 外键名称 forelgn key (外键字段名) references 主表(主表列名); -
删除外键
alter table 表名 drop foreign key 外键名称; -
删除/更新行为
行为 说明 NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与RESTRICT一致） RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致） CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录 SET NULL 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null） SET DEFAULT 父表有变更时，子表将外键设为一个默认值（Innodb不支持） -
语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为; 示例： alter table emp1 add constraint fk_emp1_dept_id foreign key (dept_id) references dept(id) on update CASCADE on delete CASCADE ;
-
多表查询
多表关系
- 概述：项目开发中在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多(多对一)
- 案例：部门 与员工的关系
- 关系：一个部门对应对个员工，一个员工对应一个部门
- 实现：在多的一方建立外键，指向一的一方的主键
- 多对多
- 案例：学生 与课程的关系
- 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
- 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
- 一对一
- 案例：用户 与 用户详情的关系
- 关系：一对一关系，用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
- 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的约束(unique)
- 一对多(多对一)
多表查询概述
-
概述：指从多张表中查询数据
-
笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)
-
多表查询分类
- 连接查询
-
内连接：相当于查询A、B交集部分数据
-
外连接：
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
-
自连接：当前表与自身的连接查询，自连接必须使用表别名
-
子查询
-
总结：
- 内连接查询，两张表交集的数据
- 外连接
- 左外连接：查询两张表，左表的数据和两张表关联的数据
- 右外连接：查询两张表，右表的数据和两张表关联的数据
- 自连接：使用别名的方式，将一张表查询两次。条件是这一张表中关联的两个字段。
内连接
-
内连接查询的是两张表交集的部分
-
内连接查询语法：
-
隐式内连接
select 字段列表 from 表1 , 表2 where 条件...; -
显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
-
外连接
-
外连接查询两张表独有和公共的部分
-
外连接查询语法：
-
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件...; 相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
-
右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
右外连接也可以改成左外连接
-
自连接
-
自连接查询，可以是内连接查询，也可以是外连接查询。
-
自连接查询语法：
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...; -
自连接，一张表自己连接自己
-
自连接查询，表必须起别名
联合查询-union , union all
-
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
-
联合查询的语法：
select 字段列表 from 表A ... unon [all] select 字段列表 from 表B ...; -
注意：
- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起， union 会对合并之后的数据去重。
子查询
-
概念：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
-
语法：
select * from t1 where column1 = (select column1 from t2); -
子查询外部的语句可以是insert / update / delete / select 的任何一个。
-
根据子查询结果不同，分为：
- 标量子查询(子查询返回结果为单个值)
- 列子查询(子查询返回结果为一列)
- 行子查询(子查询返回结果为一行)
- 表子查询(子查询结果返回为多行多列)
-
根据子查询位置，分为：where 之后、from之后、select之后。
标量子查询
- 子查询返回的结果是单个值 (数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。
- 常用的操作符：= <> > >= < <=
列子查询
-
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
-
常用的操作符：in 、not in 、any 、some 、all
操作符 描述 in 在指定的集合范围之内，多选一 not in 不在指定的集合范围之内 any 子查询返回列表中，有任意一个满足即可 some 与any等同，使用some的地方都可以使用any all 子查询返回列表的所有值都必须满足
事务
执行增删改操作
事务介绍
- 事务是一组操作的集合，他是一个不可分割的工作单位
- 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求
- 即这些操作要么同时成功，要么同时失败
- Mysql的事务是默认自动提交的，也就是说，当执行一条DML语句，Mysql会隐式的提交事务。
事务操作
-
查看/设置事务提交方式
//查看：1表示自动提交，0表示手动提交 select @@autocommit; //设置提交方式，修改0或1 set @@autocommit=0; -
提交事务
//执行成功，提交事务 commit; -
回滚事务
//执行出错，回滚事务 rollback; -
方式二
开启事务 start transaction 或 begin;//手动提交，数据不会发生变化 提交事务 commit; 回滚事务 rollback;
事务的四大特性（ACID）
-
原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
在事务内的操作要么全部成功，要么全部失败
-
一致性：事务完成时，在一个事务内，必须所有的数据都保持一致状态。
在事务完成时，所有在事务内的操作过的数据，他们直接都是一致的
-
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
事务的操作，不受并发的影响，只能有一个线程操作。
-
持久性：事务一旦提交或回滚，它对数据库中的数据的改变时永久的。
事务一旦提交或回滚，它就对数据库中的数据进行永久的改变。
事务并发问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读取到另一个事务还没有提交的数据 |
| 不能重复读 | 一个事务先后读取同一条数据，但两次读取的数据不同，称之为不可重复读 |
| 幻读 |
- 脏读
- 不可重复读
- 幻读
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | 会 | 会 | 会 |
| Read committed 【解决脏读】 | 不会 | 会 | 会 |
| Repeatable Read(默认) 【解决不可重复读】 | 不会 | 不会 | 会 |
| Serializable 【串行化】 | 不会 | 不会 | 不会 |
事务的隔离级别越高，性能越差。开发中要权衡选择【一般选默认】
事务串行化：一个事务进行对数据库操作，只有它操作成功，另一个事务才能对数据库进行操作。
总结
字符集
utf8：占3个字节
utf8mb4 占4个字节[推荐：因为有些字符占4个字节]
存储引擎
存储引擎的作用：数据库当中数据存储、更新、查询的方式
不同的存储引擎，获取的方式是不同的
Mysql服务的体系结构
- 连接层、服务层、引擎层、存储层
存储引擎介绍
# 查看数据库中索引支持的存储引擎
show engines;
# 创建表时指定存储引擎
create table xxx(...) engine=innodb;
INNODB与MyISAM的区别
- INNODB：支持事务、外键、行级锁
- MyISAM：不支持事务和外键，使用表锁
存储引擎实际应用
- 存储业务中对于事务、数据完整要求较高核心数据。
- MyISAM：存储业务系统的非核心事务。
索引
索引概念
-
介绍
索引是一种有序的数据结构
他是帮助Mysql高效获取数据的
在数据库表结构中，除了要保存原始数据值之外，还需要维护索引这种数据结构
通过这种数据结构来指向原始数据
这样我们就可以在这种数据结构当中，通过高级的算法来查找数据
-
索引的概念：索引是帮助Mysql高效获取数据的数据结构
-
数据库的索引好比一本书的目录，能加快数据库的查询速度。
-
一般来说索引占用的地方也挺大，因此索引存储在磁盘中。可能和数据存储在一起，也可能单独的存储在索引文件中。
-
索引的优势和劣势
优点 缺点 可以帮助我们快速的获取数据，降低io成本【因为数据都是存储在磁盘当中的】 索引是占用磁盘空间的 如果通过索引列对数据进行排序，降低排序成本，降低CPU的消耗 索引虽然大大提升了查询的效率，却降低了更细表的速度【对表进行，INSETR、UPDSTE、DELETE时，需要维护索引的结构】
索引结构
Mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构是哈希表实现的，只支持精确匹配，不支持范围查询 |
| R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型，在存储地理位置时，用到的相关索引 |
| Full-text(全文索引) | 是一种通过建立倒排索引，快速匹配文档的方式。类是与Lucene,Solr,ES |
不同的存储引擎，有哪些索引
数据结构
二叉树
- 二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据情况下，层级较深，检索速度慢。
红黑数-自平衡
- 自平衡的二叉树
- 红黑树缺点：大数据情况下，层级较深，检索速断慢。
B-Tree(多路平衡查找树)
-
解决了红黑树二叉树，大数据量下，层级较深的问题【一颗树下面，有多个子节点】
-
多路：一个节点下面，可以包含有多个子节点
-
树的度数：一个节点的子节点个数
-
如果一个节点下面有个5节点，就叫5阶 B树
- 每个节点，最多储存4个key ，5个指针
-
每个节点上都储存数据
B+Tree
- 和B-Tree的区别：
- 所有的节点元素都会再出现在叶子节点
- 叶子节点形成了一个单向链表
Mysql B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化，在原来B+Tree的基础基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
Hash索引
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就会产生hash冲突(也称为hash碰撞)，可以通过链表来解决。
Hash索引特点
- Hash索引只能用于对等比较(=,in),不支持范围查询（between，>，<，…）
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
存储引擎支持
- 在Mysql中，支持hash索引的是Memory引擎，而IooDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对与表中主键创建的索引 | 默认自动创建 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键字，而不是索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引（默认主键） | 将数据存储与索引放到了一块，索引结构的叶子节点保存行数据 | 必须有，而且只有一个 |
| 非聚簇索引（二级索引） | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引的选取规则
- 如果存在主键：主键索引就是聚集索引
- 如果不存在主键：将使用第一个唯一索引作为聚集索引。
- 如果没有主键，或没有合适的唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引。
索引语法
创建索引
# UNIQUE 创建的是一个唯一索引，要求字段是不能重复的
# FULLTEXT 创建的是一个全文索引
create [ UNIQUE|FULLTEXT ] index 索引名 on 表名 (字段,...); #关联多个字段创建的是组合索引
查看索引
#查看指定表中的所有索引
show index from 表名;
删除索引
drop index 索引名 on 表名;
SQL性能分析
SQL执行频率
MySQL 客户端连接成功后，通过show [ session| global ] status 命令可以提供服务器状态信息。
通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT的访问频次。
查询SQL执行频率
SHOW GLOBAL STATUS LIKE 'Com______';
慢查询日志
慢查询日志记录了所有执行时间超过指定单数（long_qeery_time，单位：秒，默认10秒）的所有SQL语句的日志。
慢查询日志
showvariables like 'slow_query_log';
MySQL的慢查询日志默认没有开启，需要在Mysql的配置文件(/etc/my.cnf) 中配置如下信息：
配置完毕之后，通过以下指令重新启动Mysql服务器进行测试，查看慢查询日志文件中记录的信息/var/lib/mysql/localhost-slow.log.
profile详情
show profiles能够在做到SQL优化时帮助我们了解时间都耗费到哪里去了。
通过have_profiling参数，能够看到当前Mysql是否支持
profile操作：
#当前数据库是否支持
select @@have_profiling;
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：
#查看当前profile开关状态
select @@profiling;
#设置为1，表示开启
set profiling = 1;
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时情况：
如果对数据库表进行 insert/update/delete 操作时，就不会走Mysql缓存了。
每一条sql 语句执行的时候：至少用到了一个索引！主键
explain执行计划
EXPLAIN 或者 DESC命令获取 Mysql 如何执行 SELECT 语句的信息，包含在 SELECT 语句执行过程中表如何连接和连接的顺序。
是否用到索引，表的连接情况，表的连接顺序
EXPLAIN执行计划各字段含义：
| 字段 | 含义 |
|---|---|
| id | 表结构的连接顺序，操作表的顺序 (id相同，执行顺序从上往下。 id不同，id值越大，优先级越高，越先执行) |
| select_type | 查询类型，主要用于区别普通查询， 联合查询，子查询等复杂查询 |
| type | 表示连接类型，性能由好到差连接类型为NULL、system、const、eq_ref、ref、range、index、all |
-
id
- 表的执行顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
-
select_type
-
type 【访问的类型】
- NULL的性能最高，一般查询时不访问任何表，才会出现NULL
- system 一般访问一张系统表会出现system
- const 根据主键，唯一索引访问一般会出现const
- ref 使用非唯一性索引查询时，会出现ref
- index 表示使用索引，但扫描了整个索引树
- all 性能最差，表示全表扫描
-
possible_key 【可能用到的索引】
- 显示可能应用在这张表上的索引，一个或多个
-
key 【实际用到索引】
- 实际使用索引，如果为NULL，则没有使用索引
-
key_len 【索引中使用的字节数】
- 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
-
rom 【执行查询的行数】
- Mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估值，可能并不总是准确的。
-
filtered
- 表是查询的行数占总读取行数的百分比，filtered的值越大越好。
-
Extra 【额外信息】
索引使用原则
最左前缀法则
- 联合索引，要遵循最左前缀法则。
- 最左前缀法则指的是：查询必须包含索引最左边的列。如果最左边的列不存在，索引将全部失效
- 如果查询的时候跳过某一列，索引将部分失效（后面的字段索引失效）。
注意：最左前缀法则和查询语句的顺序是没有关系的
范围查询
- 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
- 业务允许的情况下，查询条件尽量使用>= 这样的运算符
索引列运算
- 不要在索引列上进行运算操作，索引将失效。
字符串不加引号
- 字符串类型字段使用时，不加引号，索引将失效。
模糊查询
- 如果是尾部模糊匹配，索引不会失效。如果头部模糊匹配，索引失效。
- 如果在大数据量，情况下，**一定要规避头部加%**的情况，会导致全表扫描
or连接的条件
- or表示任意一个条件成立
- 使用or查询数据，会导致索引失效
- 用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到。
- 当or连接的条件，左右两侧字段都有索引时，索引才会生效
数据分布影响
- 如果Mysql评估使用索引比全表扫描更慢，则不会使用索引。
- 使用不使用索引，是根据数据的分布情况
- 就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。
SQL提示
-
查询条件同时满足两个索引
-
Mysql会自动选择合适的索引
-
也可以通过在SQL语句中加入一些提示来达到优化操作的目的。
-
use index： 建议MySQL使用哪一个索引完成此次查询
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程'; -
ignore index：忽略指定的索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程'; -
force index：强制使用索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
-
覆盖索引
尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到
| Extra | 含义 |
|---|---|
| Using where; Using Index | 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 |
| Using index condition | 查找使用了索引，但是需要回表查询数据 |
解释：
- 根据聚集索引查询数据，因为叶子节点下是行数据，不需要回表查询
- 根据非聚集索引查询数据，叶子节点下是id地址,如果要查询的字段，不在非聚集索引的覆盖范围，就需要根据id地址去聚集索引中回表查询
覆盖索引使用原则：
- 尽量减少使用select * 查询，会导致要使用的索引无法全覆盖
- 如果想使用到索引，创建的联合索引，使用字段越多越好,但影响查询效率
- id不需要创建索引，因为二级索引叶子节点就是id地址
前缀索引
当字段类型为字符串（varchar，text）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
-
创建前缀索引语法
n表示要提取几个字符建立su create idex idx_xxxx on 表名(字段(n)); -
前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
# count(字段) 表示该字段不为NULL的个数 # distinct 字段 表示给该字段去重 # substring(字段，起始位置，截取个数) 表示将字符类型的字段截取字符 # 计算区分度选择性 # 字段截取10位，过滤掉不重复，统计不为空的个数 除 表的总记录数 select count(distinct substring(email,1,10)) / count(*) from tb_user;
单列索引与联合索引
-
单列索引：既一个索引只包含单个列
-
联合索引：既一个索引包含了多个列
在业务中，如果存在多个查询条件，考虑针对查询字段建立索引，建议建立联合索引，而不是单列索引
创建联合索引时要考虑字段顺序，因为要遵循最左前缀法则
索引的设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引【100万以上数据】
- 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引【建议创建联合索引】
- 尽量选择区分度高的字段建立索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串的字段，并且字符串长度较长，大文本字段，建议建立前缀索引。【需要考虑前缀区分度】
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。【要遵循最左前缀法则】
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也越大，占用磁盘空间，会影响增删改的效率。【只建立有必要的索引】
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。优化器会知道每列是否包含NULL值，它可以更好地确定哪个索引最有效地用于查询。
总结
-
索引定义
- 索引是高效获取数据地数据结构【有序的数据结构】
-
索引的数据结构有哪些？
- B+Tree
- 所有的数据都会出现在叶子节点
- 叶子节点形成一个双向列表
- Hash
- 检索性能高
- 只需要计算出hash值，然后定位到对应的数据
- 如果出现hash碰撞，就需要沿着链表找到对应的数据
- 只支持精确匹配，不支持范围查询，既索引的排序
- B+Tree
-
索引的分类
- 主键索引
- 在创建表时，只要指定了主键，主键默认会建立主键索引
- 唯一索引
- 如果某一列，它的字段值不重复，可以针对这类字段建立唯一索引
- 常规索引
- 正常建立的索引
- 全文索引
- es
- 聚集索引
- 在Innodb引擎当中，根据索引存储结构：分为两类，聚集索引、二级索引
- 聚集索引：
- 在Innodb的表结构中，是必须存在的。
- 而且只能有一个聚集索引。
- 特点：B+Tree叶子节点下面挂的是行数据
- 聚集索引在一张表中必须存在，默认主键索引就是聚集索引
- 如果没有主键，会选择第一唯一索引作为聚集索引
- 如果没有主键和唯一索引，Mysql会生成一个roid作为聚集索引
- 聚集索引：
- 在Innodb引擎当中，根据索引存储结构：分为两类，聚集索引、二级索引
- 二级索引：特点：B+Tree叶子节点下面挂的是对应的主键
- 主键索引
-
如何操作索引
-
创建索引
# 如果创建唯一索引加 unique create [unique] index xx on xx(xx); -
查看索引
# 查询某张表上有哪些索引 show index form xxx; -
删除索引
删除指定索引 drop index xxx on xxx;
-
-
SQL性能分析工具
- 执行频次：
- 通过Mysql提供的参数，可以看到当前数据库服务器每个SQL的执行频次
- 可以判定：当前数据库是插入为主，还是查询为主，还是删除为主，更新为主
- 优化SQL的时候，主要针对查询频次比较高的数据库进行优化
- 慢查询日志
- 可以定位哪些sql语句执行过程比较耗时
- 慢查询日志里只会记录，执行时间，超过预设时间的SQL
- profile
- 可以监控SQL语句的耗时，具体耗时在哪个阶段
- explain 【最常使用】
- 查看SQL的执行计划，判断SQL的性能
- 执行频次：
-
索引使用原则
- 联合索引
- 要遵循最左前缀法则
- 最左边的列必须存在，整个联合索引失效
- 如果左边的列存在，中间跳过某一列，后面的列索引会失效
- 范围查询时注意
- 进行范围查询时，范围查询右侧的列会失效
- 尽量使用>=,<=这样的操作符
- 要遵循最左前缀法则
- 索引在什么情况下会失效
- 不要在索引列上进行函数运算，否则索引会失效
- 字符串不加引号，会造成隐式类型转换，索引会失效
- like模糊匹配，如果在前面加了%，表示前面要模糊匹配，索引会失效
- on连接的条件，如果一侧字段有索引，另一侧没有索引，也会导致索引失效
- 如果Mysql评估，走全表扫描比走索引还快，此时索引也会失效，这种称为数据分布的影响
- SQL提示
- 在执行select语句时，如果符合很多个索引，Mysql会自己判断选择哪个索引
- 我们也可以干预，给Mysql一些提示告诉它使用哪个索引
- 建议使用哪个索引
- 忽略哪些索引
- 必须使用哪些索引
- 覆盖索引
- 解释：查询返回的结果，索引都包含了，不需要回表查询了
- 回表查询：查询的过程中先根据二级索引，查找的这一行数据的id。再根据id到聚集索引中查找这一行的行数据
- 前缀索引
- 什么情况下使用前缀索引？
- 遇到一些字符串长度较长，或一些大文本字段时，我们可以使用字符串截取，建立前缀索引。
- 缩小索引的体积，提高检索效率
- 什么情况下使用前缀索引？
- 单列索引、联合索引
- 推荐联合索引
- 联合索引性能较高，如果使用得当可以避免回表查询
- 联合索引
-
索引设计原则
- 需要针对哪些表建立索引
- 数据量大，查询频率高的表建立索引
- 针对这些表的哪些字段建立索引
- 一般针对 查询条件（where）、排序（order by）、分组（group by）后出现的字段建立索引
- 建立什么样的索引
- 如果这一列是唯一的，尽量建立唯一索引，区分度高
- 如果可以建立联合索引，尽量建立联合索引
- 如果涉及到一些字符串长度较长，大文本字段，尽量建立前缀索引
- 需要针对哪些表建立索引
SQL优化
插入数据优化
单条数据插入，每次插入都需要与Mysql建立连接，进行网络传输，性能低，建议批量插入
-
批量插入
- 如果一次性插入多条数据，可以通过批量插入语句完成
- 如果选择批量插入，一次插入数据不建议超过1000条
- 如果需要插入大量的数据，执行多条批量插入
insert into 表名 values(1,'Tome'),(2,'Cat'),(3,'jerry'); -
手动提交事务
-
Mysql中的事务提交方式默认是自动提交
-
每执行一次操作都需要 执行前，开启事务，执行完，提交事务。涉及到事务的频繁开启频繁提交
-
建议手动控制事务。执行多条insert语句前，开启事务，执行完提交事务
-
开启事务
start transaction;
insert into tb_test values(1,‘Tom’),(2,‘Cat’),(3,‘Jerry’);
insert into tb_test values(4,‘Tom’),(5,‘Cat’),(6,‘Jerry’);
insert into tb_test values(7,‘Tom’),(8,‘Cat’),(9,‘Jerry’);
提交事务
commit;
- 主键顺序插入【建议顺序插入】
- 主键顺序插入
- 主键乱序插入
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
- 一次性插入大批量数据，使用insert语句插入性能较低，可以使用Mysql数据库提供的**load指令**进行插入
- 使用批量插入的步骤：
### 主键优化
在InnDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）.
#### 页分裂
1. 页可以为空，也可以填充一半，也可以填充100%。每个页包含2-Nn行数据(如果一行数据多大，会行溢出)，根据主键排列。
2. 主键乱序插入，会导致页分裂。
#### 页合并
1. 当删除一条记录时，实际上并没有被物理删除，只是记录被标记（flaged）为删除，并且他的空间变得允许被其他记录声明使用。
2. 当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
3. MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定。
#### 主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。
### order by排序优化
Mysql的排序方式：
1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓存区sort buffer中完成排序操作，**所有不是通过索引直接返回排序结果的排序**都叫FileSort排序。
2. Using index：**通过有序索引顺序扫描直接返回有序数据**，这种情况既为using index，**不需要额外排序**，操作效率高。
- 根据排序的字段，建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。【没有使用到覆盖索引，需要回表查询，**在排序缓存区当中对数据进行排序**】
- 多字段排序，一个升序，一个降序，此时**需要注意联合索引在创建时的规则**（ASC/DESC）。
- 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区sort_buffer_size(默认256k)。
### group by分组优化
- Using temporary：分组时产生了零时表。
- 在分组操作时，可以通过索引来提高效率。
- 在分组操作时，索引的使用也是满足最左前缀法则的。
- where条件和group by分组，组合满足最左前缀法则也可以使用到联合索引，可以避免零时表产生。
#where和group by 组合满足最左前缀法则，也可以避免产生零时表
explain select age,count(*) from tb_user where profession = ‘软件工程’ group by age;
### limit分页查询优化
- 在大数据的情况下，进行分页查询，越往后效率越低，耗时越长。
- 耗时原因：
- 例：问题就是limit 2000000,10，此时Mysql排序2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
- 优化思路：可以通过覆盖索引加子查询形式进行优化。
### update优化
在执行update语句时，一定根据索引字段进行更新，否者就会出现行锁升级为表锁。一旦锁住整张表，并发性能就会降低。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。
在执行更新时，更新的条件一定要有索引，如果没有索引会出现行锁升级为表锁。并且索引也不能失效。
##### 总结
1. 插入数据
inset:批量插入、手动提交事务、主键顺序插入
大批量插入：load data local infile
2. 主键优化
主键长度尽量短、顺序插入 AUTO_INCREMENT
3. order by优化
using index:直接通过索引返回数据，性能高
using filesort:需要将返回的结果在排序缓冲区排序
4. group by优化
索引，多字段分组满足最左前缀法则
5. limit优化
覆盖索引+子查询
6. count优化
性能：count(字段) < count(主键 id) < count(1) ≈ count(*)
7. update优化
尽量根据主键/索引字段进行数据更新
### 视图/存储过程/触发器
##### 视图
介绍：
视图（View）**是一种虚拟存在的表**。视图中的**数据并不在数据库中实际存在**，行和列数据来自自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲：视图只保存了**查询的SQL逻辑**，**不保存查询结果**。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
创建：
OR REPLACE表示：替换某个视图,可以不加
CREATE [OR REPLACE] VIEW 视图名称[(列名字段)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION]
查询：
查询视图语句: SHOW CREATE VIEW 视图名称;
查询视图数据: SELECT * FROM 视图名称 where…;
修改：
修改视图OR REPLACE 必须加，表示：覆盖视图
方式一: CREATE [OR REPLACE] VIEW 视图名称[(列名字段)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION]
方式二:
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPION]
删除：
IF EXISTS如果存在执行删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称]…
#### 视图检查选项
- 当使用**视图检查选项子句创建视图时**，Mysql会通过视图检查正在更改的每行，例如：插入、更新、删除、**是否符合创建视图时定义的条件**。
- **Mysql允许基于另一个视图创建视图**，它还会检查依赖视图中的规则以保持一致性。
- 为了确定检查的范围，Mysql提供给了两个选项：CASCADED和LOCAL，默认值为CASCADED。
**视图不存储数据**，**它是一张虚拟表**。它是基于查询真实表而创建的。
视图插入的数据，都会存储到为创建视图，而查询的那张真实的表当中
视图中插入数据时，检查创建视图时定义的查询条件
with cascaded check option
with lacal check option
CASCADED：
当基于一个视图创建视图时，当我们进行操作时，不仅会检查创建的视图，还会检查基与视图创建的视图
### 日志
日志记录了数据库的各个方面
通过日志可以，**追踪数据库**曾经发生的事件
#### 错误日志
- 错误日志是Mysql中**最重要的日志之一**，它记录了**mysql启动和停止时**，以及**服务器在运行过程中发生任何严重错误时的相关信息**。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
- 该日志是默认开启的，默认存放目录/var/log/，默认日志文件名为mysql.log
- 查看日志位置：
查看系统变量
show variables like ‘%log_error%’
#### 二进制日志
#### 查询日志
#### 慢查询日志
使用的技术栈工具类有石墨
项目进行道社么阶段了
目前项目组人员
前后端是否分离
我没有用过的，可不可以让我学习一下
我试用期转正的考核机制是什么
未来公司是否有人才培训计划
公司的企业文化
5险一金，
12k正常
5% %12
基本薪资不能低于1320
7179
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
