5-Mysql

数据库概念

数据库（DB: DataBase ）是按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

数据库类型

• 关系型数据库
  • 数据之间是一对一、一对多、多对多的关系
  • 关系模型包括数据结构、操作指令集合、完整性约束
• 非关系型数据库（NoSQL）
  • redis

数据库管理系统（DBMS）：是为了管理数据库而设计的电脑软件系统，常见的MySQL，Oracle、DB2等。

什么是SQL？结构化查询语言（Structured Query Language），是一种特殊目的的编程语言，用于存取数据以及查询、更新和管理关系数据库系统。是高级的非过程化编程语言

SQL的分类

• DQL：查询，进行数据查询(SELECT)
• DDL：定义，负责数据结构定义与数据库对象定义(CREATE, ALTER, DROP)
• DML：操纵：负责数据库对象运行数据访问（INSERT, UPDATE, DELETE）
• DCL：控制：数据访问权进行控制（GRANT, REVOKE）
• TPL：事务：能确保被DML语句影响的表的所有行及时进行更新（BEGIN, TRANSACTION, COMMIT, ROLLBACK）
• CCL：指针：存储过程，游标，指针(DECLARE CURSOR, FETCH INTO, UPDATE WHERE CURRENT)

MySQL是一个关系型数据库管理系统，使用标准的SQL数据库语言形式，对java，python都有很好的支持

mysql下载

下载zip文件，解压后配置path

path    D:\Project\MySQL\mysql-8.0.29-winx64\bin

管理员身份运行cmd，初始化data命令：mysql – initialize --console

运行后会产生一个初始化密码，将这个零食临时密码保存下来

执行mysql -install

启动mysql服务：net start mysql

修改密码：ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;

卸载mysql

停止mysql服务

卸载mysql

删除mysql目录

登录mysql

本地登录
mysql -u用户名 -p密码
远程登录
mysql -u用户名 -hIP地址 -p密码

默认情况下不允许使用root身份进行远程登录

开启权限方式（危险动作）：

1. 本地使用root登录mysql
2. use mysql;
3. update user set host = ‘%’ where user =‘root’;
4. flush privileges;

初始化数据

1. 列出当前数据库管理系统中有哪些数据库

show databases;

2. 创建数据库

create database test;

3. 使用 test数据库

use test;

4. 查看当前数据库有那些表

show tablse;

5. 查看当前用的是那个数据库

select databse();

6. 删除数据库

drop test;

7. 查看当前mysql版本

select version();

mysql --version

8. 退出

exit;
quit;
ctrl+c

9. 导入数据

source 路径

10. 查看表结构

desc tablename;

Mysql不区分大小写

表示数据库存储数据的基本单元，因为表存储数据非常直观

行：记录

列：字段

java类型	sql类型
long	bigint
int	int
String	varchar

DQL

简单查询

1. 查一个字段

select name from table;

2. 查多个字段

select name1, name2 from table;

字段是可以参与运算的
查询年薪
select ename, sal*12 from emp;

3. 起别名

select sal*12 as 年薪 from emp;
as可以省略，如果想起的别名有空格，用单引号括起来

条件查询

语法格式

select ... from ... where

1. 查询月薪3000的员工姓名

select ename from emp where sal=3000;

2. 查询岗位是manager的员工姓名

select ename from emp where job='manager'

3. 查询岗位是manager和salesman的员工姓名

select ename from emp where job='manager' or job='salesman'

4. 查询薪资小于1500，并且部门编号是20或30的员工姓名

select ename from emp where sal<1500 and (deptno = 20 or depno = 30)

5. 找出工作岗位是manager和salesman的员工姓名

select name from emp where jon in('manager', 'salesman');
#如果查不是，有not in()
# in是忽略null的，当条件为in(null,'manager')时，null没有用
# not in是不忽略空的

6. 模糊查询

select * from emp where ename like '_A%'
# _是一个字符
# %是任意个字符
# \转义

排序操作

语法

select ... from ... order by 字段 asc/desc
# asc升序
# desc降序

1. 按照员工编号升序排序

select ename from emp order by ename asc;

2. 查询员工编号、姓名、薪资，按照薪资升序排，如果薪资相同，再按照姓名升序

select empni,ename,sal from emp order by sal asc, ename asc;
# 直接在后面添加，考前的优先级更高，只有前面的排序相等了才会执行后面的排序

3. 找出工作岗位是manager和salesman的员工姓名，并按照工资降序排列

select ename from emp where job in('manager', 'salesman') order by sal desc;

4. 查询所有工作（去重）

# 使用在所有字段的最前方
select distinct job from emp;

5. 查找公司中不同部门的不同工作岗位（联合去重）

select distinct deptno,job from emp;

数据处理函数

字符串转换

1. 转大写

select ucase('abc');
# upper()

select sal from emp where upper(ename)='SMITH';

2. 转小写

select lower(ename) from emp;
# lcase()

3. 截取字符串

# substr('被截取字符串',起始下标,截取长度)
# substr('被截取字符串',起始下标)
# 如果为负数，从后往前数
select ename from emp where substr(ename, 2 ,1) = 'A';

4. 获取字符串长度

char_length(str)
length(str)

5. 拼接

concat(str)

6. 去除字符串前后空白

trim(str)
trim(leading 'x' from 'xxxxx11111')

数字相关

1. 生成0-1的随机数

rand()
# rand(1000)相当于传入一个key值，不是指定范围

2. 四舍五入

round() # 四舍五入 默认不保留小数
round(x,y) # y为小数保留位数
ceil() # 向上取整
floor() # 向下取整

3. 空处理

# 默认情况下，但凡有null参与的运算，结果都为null
ifnull(null,100) # null视为100

日期时间

1. 获取当前日期和时间

now() # 获取select执行的时间点
sysdate() # 获取sysdate()函数执行的时间点
# 返回格式 2024-08-03 22:33:33

2. 获取当前日期

curdate()
current_date

3. 获取当前时间

current_time

4. 获取单独的年、月、日、时、分、秒

select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());

5. date_add函数

# 给指定的日期添加间隔时间，进行偏移
select date_add('2020-10-11',interval 3 day)
# interval 3 month
# interval -3 year
# 复合型单位
select date_add('2020-10-11',interval '3,2' year_month)

6. 日期格式化

# 将日期转换成具有某种格式的日期字符串
# %Y 4位年
# %y 2位年
# %m 月份
# %d 日
# %H 小时
# %i 分
# %s 秒

select date_format(now(),'%H:%i:%s %Y-%m-%d');

7. str_to_date（将字符串转换为日期）

insert into t_student(name,birth) values('zhangsan',str_to_date('10/01/1999'，'%m/%d/%Y'));

# 没有指定时会默认调用,但是要按照日期的默认格式写

8. 一周、一年、一月中的第几天

dayofweek(now());
dayofmonth(now());
dayofyear(now());

9. last_day
10. datediff

# 日期差  返回日期单位
select datefiff('2010-10-10 10:10:10','2010-07-10 10:10:10');
# 时间差  返回小时单位
select timefiff('2010-10-10 10:10:10','2010-07-10 10:10:10');

11. if

select if(500<100,'yes','no');
# 如果名称是smith，工资上涨10%
select enmae,sal,if(ename='smith',sal*1.1, sal) as newsal from emp;
# 如果工作是manager，工资上涨10%，如果是salesman，上涨20%
select ename,sal,if(job='manager',sal*1.1, if(job='salesman',sql*1.2, sal)) as newsal from emp;

# case when...then...when...then...else...end
selsct ename,job,(
	case job
	when 'manager' then sal*1.1
	when 'salesman' then sal*1.2
	else sal end) as newjob from emp;

12. cast

将值从一种数据类型转换为另一种类型

# 语法 case(值 as 数据类型)
date 日期
time 时间
datatime 日期时间
signed 有符号的int（负数或正数）
char 定长字符串
decimal 浮点型

select cast('2010-10-10 10:10:10' as time);

13. 加密

# ms5是一种加密算法，不能逆推,加密后生成一个固定长度32位的字符串
select md5('powernode');

分组函数

多行处理函数，会自动忽略null，分组函数不能使用在where后面的子句中，因为他是查询出结果之后才分组的，有5个分组函数

• max()：最大值
• min()：最小值
• sum()：和
• avg()：平均值
• count()：数量

count(*)和count(具体某个字段)的区别？

• count(*)是统计数据的个数
• count(具体某个字段)是统计该字段不为空的个数

如果没有写group by，则查出的所有数据为一组

分组查询

group by

语法

group by 字段;# 按照一个字段分组
group by 字段1，字段2;# 按照多个字段分组

当一个语句中出现了group by时，select后只能出现参与分组的字段，或分组函数

1. 查询每个部门不同岗位的平均工资

select deptno,job,avg(sal) from emp group by deptno,job;

having

having写在group by的后面，当对分组后的数据不满意，可以继续使用having进行分组

where的过滤是在分组前进行过滤

1. 查询每个部门的平均薪资，找出平均薪资大于2000的

select deptno,avg(sal) as newsal 
	from emp 
	group by deptno 
	having newsal>2000;

组内排序

• substring_index
• group_concat

1. 查询每个岗位的工资前两名

select group_concat(empno order by sal desc) from emp group by job;

单表DQL语句执行顺序

select ...5
from...1
where...2
group by...3
having...4
order by...6

连接查询

什么时连接查询？

1. 从一张表中查询数据称为单表查询

2. 从多个表中联合查询称为连接查询

连接方式分类

1. 内连接
   1. 等值连接
   2. 非等值连接
   3. 子连接
2. 外连接
   1. 左连接
   2. 右连接
3. 全连接（mysql不支持）

笛卡尔积现象

当两张表进行连接查询时，如果没有任何限制，则最终查询到的数据条数是两个表数量的乘积

内连接

1. 等值连接（查询员工的姓名和所属部门名）

select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno
# inner可以省略

2. 非等值连接（查询员工的工资等级，显示员工名，工资，工资等级）

select e.ename,e.sal,s.grade
    from emp e
    join salgrade s
    on e.sal between s.losal and s.hisal;

3. 自连接（找出员工的直属领导）

select e.enamem m.ename
	from emp e
	join emp m
	on e.mgr=m.empno;

外连接

1. 查询所有员工的部门，并将员工全部展现出来

select e.*,d.*
	from dept d
	left outer join emp e
	on e.deptno=d.deptno;
# outer也可以省略

多张表连接

三张表或更多张表进行连接查询

1. 查询员工的部门，并显示员工的薪资等级

select e.ename,d.dname,s.grade
	from emp e
	join dept d
	on e.deptno=d.deptno
	join salgrade s
	on e.sal between s.losal and s.hisal;
# 挨个写就行了

子查询

什么是子查询？

select语句中嵌套select愉快v

select语句可以嵌套在where，from，select后面都可以

where后面使用子查询

找出高于平均薪资的员工姓名和薪资

select ename,sal
    FROM EMP
    WHERE sal>(select avg(sal) from emp);

from后面使用子查询

查找每个部门的平均工资的等级

select t.*, s.grade
	from (select deptno, avg(sal) as avgsal from emp group by deptno) t
	join salgrade s
	on t.avgsal between s.losal and s.hisal;
# 就是将子查询当作一个临时的表

select后面使用子查询

查找员工及所在部门

select e.ename, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

exists、not exists

exists用于检查记录数是不是大于0

select * from t_customer c
	where exists(select * from t_order 0 where c.customer_id=o.customer_id);

in 和exists的区别

1. in操作符根据指定列表中的值来判断是否满足条件，exists根据子查询的结果是否有记录来判断
2. exists的效率高一些，尤其是子查询返回记录数很大的情况下
3. in操作可以同时匹配多个值，而exists只能匹配一组条件

union&union all

可以将两个查询结果合并

查询工作是manager或salesman的员工姓名和工资

select ename,sal from emp where job='manager'
union
select ename,sal from emp where job='salesman';

区别在于union会去重，union all不会去重

limit

limit在语句的最后面

limit x, y 从第x个数据开始取，取y条数据

limit y 默认从第0条数据开始

表操作

创建表

create table 表名(
	字段名1 数据类型,
	字段名2 数据类型 default 'str'
);

插入数据

insert into 表名(字段名1,字段名2,...) values(值1,值2);

字段名可以省略，但是如果省略了，需要把所有的字段值都写上

删除表

drop table 表名;
或加上if exists避免报错
drop table if exists 表名

数据类型

整数类型

tinyint：1个字节

smallint：2个字节

mediumint：3个字节

int：4个字节

bigint：8个字节

浮点数类型

float：4个字节（单精度，最多5位小数）

double：8个字节（双精度，最多16位小数）

定点数

decimal，底层实际上采用字符串的形式存储数字

使用格式：decimal(x,y)，x个有效数字，y位小数

有效数字最多65位，小数最多30位

日期和时间

year：1个字节，存储年，格式：YYYY

time：3个字节，只存储时间，格式：HH:MM:SS

date：3个字节，存储年月日，格式：YYYY-MM-DD

datetime：8个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1000年~9999年）

timestamp：4个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS（从公元1980年~2040年）

字符串

char：定长（0~255）。浪费空间，但是效率更高

varchar：可变长（0~16383）。节省空间，但是因为会计算长度，效率较低

text：特别长

• tinytext，表示长度位255字符的text列
• text：表示长度为65535字符的text列
• mediumtext：长度为16777215字符的text列
• longtext：长度为4294967295字符（4GB）的text列

enum

• 语法：<字段名>enum(‘值1’，‘值2’)
• 该字段插入值时，只能是指定的枚举值

set

• 语法：<字段名>set(‘值1’，‘值2’,…)
• 该字段插入值时，只能是指定的值

二进制类型

blob类型，可以存储图像，声音，视频等文件

• blob：小的，最大长度65535字节
• mediumblob：中等的，最大长度16777215字节
• longblob：长的，最大4GB的字节

该类型需要用java或其他语言进行插入，没法在数据库直接插入

增删改表结构DDL

1. 创建一个学生表

create table student(
	'no' bigint;
    'name' varchar(255),
    'age' int comment '年龄'
)

2. 查看建表语句

show carete table student;

3. 修改表名

alter table 表名 rename 新表名;

4. 新增字段

alter table 表名 add 字段名 数据类型;

5. 修改字段名

alter table 表名 change 旧字段名 新字段名 数据类型;

6. 修改字段数据类型

alter table 表名 modify column 字段名 数据类型;

7. 删除字段

alter table 表名 drop 字段名;

DML语句

数据操作语言，insert，delete，update

insert

语法格式

insert into 表名(字段名1，字段名2,...) values(value1,value2,...);
# 一次插入多条记录
insert into 表名(字段名1，字段名2,...) values(value1,value2,...),(value1,value2,...);

delete

delete from 表名 where 条件
truncate table 表名

delete删除后，空间是不会释放的，方便后续回滚

truncate效率很高，且不可恢复，它不属于DML语句

update

update 表名 set 字段名1=value1,字段名2=value2,...where 条件;

约束constraint

创建表时，可以给表的字段添加玉树，可以保证数据的完整性，有效性

非空约束：not null

检查约束：check

唯一性约束：unipue

create table student(
	no int,
    name varchar(20) not null,
    age int,
    email varchar(255) unique not null,
    check(age>18),
    unique(name,age)
)
# unique(name,age)是表级约束
# email varchar(255) unique是列级约束

# 表级约束可以通过constraint起名
constraint t_stu_name_email unique(name,email)

主键约束：primary key

1. 简称PK

2. 不能为NULL，且不能重复

3. 任何一张表都应该有主键，没有主键的表可以视为无效表

4. 主键值是每行记录的唯一标识

5. 主键分类

   1. 根据字段数量分类
      1. 单一主键
      2. 复合主键

   # 单一主键
   create table student(
   	id int primary key;
   )
   # 表级
   create table student(
   	id int,
       primary key(id)
   )
   # 复合主键
   create table student(
   	id int,
       name varchar(20),
       primary key(id,name)
   )
   

   1. 根据业务分类
      1. 自然主键（主键和任何业务都无关，只是一个淡出的自然数据）–建议的
      2. 业务主键（主键和业务挂钩）

6. 主键自增

create table student(
	no int primary key auto_increment,
    name varchar(20)
)

外建约束：foreign key

1. 外建约束，简称FK
2. 添加了外键约束的字段中的数据必须来自其他字段，不能随便填
3. 假设给a字段添加了外键约束，要求a字段中的数据必须来自b字段，b字段不一定是主键，但至少要求有唯一性
4. 外建约束可以给单个字段添加，也可以给多个字段联合添加
5. a表如果引用b表中的数据，可以把b表叫做父表，把a表叫做子表
   1. 创建表时，先创建父表，再创建子表
   2. 插入数据时，，先插入父表，再插入子表
   3. 删除数据时，先删除子表，再删除父表

create table t_school(
	tno int primary key
)
create table student(
	sno int primary key,
    constraint t_school_son_fk foreign key(sno) references t_school(sno) on delete set null
)

# 级联删除
on delete cascade
# 级联更新
on update cascade
# 级联置空
on delete set null

三范式

数据库表设计的原则

1. 第一范式

任何一张表都应该有主键，每个字段是原子性的，不可再分

2. 第二范式

在第一范式的基础之上，要求所有非主键字段完全依赖主键，不能产生部分依赖

（即不建议使用复合主键）

3. 第三范式

在第二范式的基础上，非主键字段不能传递依赖于主键字段

视图

1. 只能将select语句创建为视图
2. 创建语法

create or replace view v_emp as select empno,ename,sal from emp;
# or replace可以不写

3. 试图作用
   1. 如果开发过程中在多处用到了相同的一条很长的语句，可以将这条语句创建为视图
4. 修改视图

alter view v_emp as select empno,ename,sal from emp;

5. 删除视图

drop view if exists v_emp;

6. 面向视图进行的改动，原表中也会相应更改

事务

1. 最小的工作单元，在数据库中，表示一件完整的事（比如转账业务，一个账户减少，一个账户增加）
2. 可以保证多个语句同时成功，同时失败
3. 只针对insert、delete、update有效

四大特性

1. 原子性：要么全部成功，要么同时失败。
2. 一致性：事务开始前和完成后数据总量不变
3. 隔离性：多用户并发访问数据库是，数据库会为每一个用户开启一个事务。及并发事务之间相互隔离
4. 持久性：一个事务一旦提交，对数据库中数据的改变是永久性的，不会因数据库系统故障而丢失（在过程中数据的改变可能是存储在临时变量中）

开启或关闭事务

# 开启
start transaction   或   begin
# 回滚
rollback
# 提交
commit

默认情况下mysql采用的事务机制是自动提交，每执行一条DML语句则提交一次。

事务隔离级别

查看隔离级别

• 当前会话：select @@transaction_isolation
• 全局：select @@global.transaction_isolation

设置隔离级别

• 当前会话：set session transaction level read committed;
• 全局：set global transaction level read committed;

---

• 脏读：读取了其他事务未提交的数据
• 不可重复读：一个事务前后两次读取同一行数据，结果可能不同
• 幻读：一个事务前后进行读操作，读到的数据量不同

可重复读的幻读问题

解决方案有两个：

• 快照读（针对select语句）,读的是记忆（某个时刻的数据），底层是通过MVCC方式解决了幻读问题，在执行了第一个查询语句后，会创建一个Read View，后续的查询语句利用这个Read View就可以在undo log版本链找到事务开始时的数据，所以每次查询的数据都是相同的。

• 当前读（select…for update），读的是当前数据，加的锁叫做：next-key lock。使用当前读之后，会给数据添加记录锁（不能删除，针对delete）和间隙锁（不能插入，针对insert），不允许其它事务对这个范围内的数据进行增删改。

都用快照读或都用当前读就不会产生问题。

在执行update时会默认使用一次当前读的操作，所以执行顺序（快照读、更新、当前读）会产生幻读问题

想要完全解决幻读问题可以在开启事务之后立即执行一次当前读

DBA命令

新建用户

create user 'name'@'localhost' identified by 'password';

给用户授权

# 本地授权
grant [权限1,权限2] on 库.表 to '用户名'@'localhost'
# 外网授权
grant [权限1,权限2] on 库.表 to '用户名'@'%'
# 所有权限
all privileges
# 细粒度权限
select,insert,delete,update,alter,drop,create,index,usage...
# 库名、表名可以用*

# 授权后必须书信权限，才会生效
flush privileges
# with grant option 表示可以将权限授予其它用户

撤销用户权限

revoke insert,delete,update,select on test.* from chushiyan@localhost;
revoke all on test.* from chushiyan@localhost;
revoke all ,grant option from chushiyan@localhost;

修改用户密码

alter user 'name'@'localhost' identified by 'password'

修改用户名

rename user 'name'@'localhost' to 'newname'@'localhost'

删除用户

drop user 'name'@'localhost'
drop user 'name'@'%'

备份数据

# 在登陆mysql数据库之前进行
mysqldump powernode > path-urot -p123 --default-character-set=utf8;

mysqldump powernode emp > path-urot -p123 --default-character-set=utf8;

导入

create database powernode;
mysql powernode < path-urot -p123 --default-character-set=utf8;

#方法2，登录mysql之后操作
create database powernode;
use powernode;
soource path;

存储过程

过程化sql语言，在普通sql语言基础上增加了编程语言的特点。数据库内置编程语言，将数据操作语言和查询语句组织在过程代码中。可以将多条sql语句以逻辑代码的方式将其串联起来，每个存储过程都有自己的名字，通过名字进行调用。

优点

• 降低了应用服务器和数据服务器之间网络铜鼓县你的开销，尤其是在数据量庞大的情况下

缺点

• 移植性差，编写难度差，维护性差（不同数据库的存储过程一般是不同的，所以用的比较少，除非遇到了性能瓶颈）

创建

create procedure pl()
begin
	select empno from emp;
end;

调用

call p1();

删除

drop procedure if exists p1;

查看创建存储过程的语句

show create procedure p1;

# 通过系统表查看存储过程的状态信息
information_schema.routines 这个系统表中存储的不仅包括存储过程的状态信息，也包括函数对象、触发器对象等的状态信息
select * from information_schema.routines;
routine_name  # 名称
routine_type  # 类型

# case

# while循环
while 条件 do
...
end while

# repeat循环
repeat
	...
	until 条件
end repeat

# loop循环
mylp:loop
	...
	条件
	leave mylp
end loop

游标cursor

# 声明变量、游标（游标必须要在普通变量下面）
declare dep_no int;
declare dep_name varchar(255);
declare dept_cursor for select语句(eg:select dept_no, dept_name from dept);
# 打开
open dept_cursor;
# 通过游标取数据
# 课题配合其他语句
fetch dept_cursor into dep_no, dep_name;
# 关闭游标
close dept_cursor;

捕获异常

语法格式

declare handler_name handler for condition_value action_statement


declare 

1. handler_name表时异常处理程序的名称，重要取值包括
   1. continue：发生异常后程序不会终止，会正常执行后续的过程。（捕获）
   2. exit:发生异常后，终止存储过程
2. condition_value是指捕获的异常
   1. sqlstatement

存储函数

带有返回值的存储过程，参数只允许是in，没有out和inout

格式

create function 名称(参数列表) returns 数据类型 [特征]
begin
	...
	return ...;
end;

特征可以选取几个指

• determinsitic：标记该函数为确定性函数，这是一种优化策略，这种情况下整个函数体的执行就会省略，直接返回之前缓存的结果。
• no sql：标记该函数执行过程中不会查询数据库，告诉mysql优化器不需要考虑使用查询缓存和优化器缓存来优化这个函数，可以避免不必要的查询消耗
• reads sql data：标记该函数会进行查询操作，告诉mysql优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果

触发器

是一种数据库对象，与表相关联的特殊程序，可以再特殊的数据库操作触发时自动执行

作用：

1. 强制试试业务规则
2. 数据审计
3. 执行特定业务操作

语法

create trigger 名称
before/after insert/update/delete on table名称 for each row
begin
	...
end;

存储引擎

mysql存储引擎绝对了数据在磁盘上的存储方式和访问方式。不同的存储引擎实现了不同的存储和检索算法，因此在处理和管理数据的方式上有差异。

常见的存储引擎包括InnoDB，MyISAM，Memory，Archive，CSV等

• InnoDB支持事务和行级锁定，具有较好的并发性能和数据完整性，适用于需要高并发读写的应用。（mysql默认使用的）
• MyISAM不支持事务，支持全文索引和表记锁定，适用于读操作较多的
• Memory将数据全部存储在内存中，适用于对读写速度要求很高的应用
• CSV将数据以纯文本格式存储，适合于需要处理和导出csv格式数据场景
• Archive将数据进行压缩和存储，适用于需要长期存储大量历史数据但不经常使用的场景

show engines查看mysql所有的存储引擎

指定存储引擎

create table mytable(...) engine=InnoDB

修改存储引擎

alter table mytable engine=InnoDB

索引 index

索引是一种能够提高检索效率的提前排好序的数据结构。是解决sql慢查询的一种方式。

没有索引时时全表扫描，如果要查询的字段存在索引，会查询索引

索引的创建删除

主键会自动添加索引

被称为主键索引

unique约束的字段自动添加索引

查看索引

show index from table名;

给指定字段添加索引

# 建表时添加索引
create table t_user(
	id int,
    name varchar(255),
    index t_user_name_index(name)
)
# 方式二
alter table t_user add index t_user_name_index(name)
# 方式三
create index 名称 on emp(name)

删除索引

drop index 名称

分类

按数据结构分类

• B+树索引：采用B+树的数据结构
• Hash索引（仅memoy存储引擎支持）：采用哈希表

按照物理存储分类

• 聚集索引：索引和表中数据存在一起
• 非聚集索引：索引和表数据分开，索引独立于表空间

一张表可以对应对多个非聚集索引，只能有一个聚集索引

按照字段特性

• 主键索引
• 唯一索引
• 普通索引
• 全文索引

按照字段个数

• 单列索引、联合索引

mysql索引采用了B+树的数据结构

常见的树结构有二叉树、红黑树、B树、B+树。树的高度不同，树的高度越低，性能越高。

二叉树在极端情况下会形成类似链表一样的结构，会降低查询效率。因此引入了红黑树和平衡二叉树来进行自旋降低树的高度，但是当数据量很大的情况下，因为每个节点只能有两个子树，因此树的高度也会很高。为了进一步降低树的高度，引入了B树（平衡多路查找树），每一个节点中存储多个数据，B树种每个阶段不仅存储了索引值，还存储该索引值对应的数据行，B树不适合做区间查找，对区间中的每一个值都要再检索一遍，因此引入了B+树。Mysql采用的是16阶B+树

B+树将叶子节点都存储再叶子节点上，同时叶子阶段通过双向指针进行链接

B+树的非叶子节点只存储索引值，因此能够在相同空间中存储更多节点信息

mysql为什么选择b+树作为索引的数据结构，二不是b树？

1. 非叶子节点上可以存储连续的键值，阶数可以更大，磁盘io次数少
2. 所有数据都是有序存储在叶子节点上，可以进行范围查找
3. 数据页之间，数据记录之间采用链表链接，让升序降序更加方便

如果一张表没有主键索引，还会创建B+树吗？

当一张表中没有主键索引时，会默认使用一个隐藏的内置的聚集索引，这个聚集索引是基于表的物理存储顺序构建的，也通过B+树来实现。

其他索引及相关调优

Hash索引

支持hash索引的存储引擎有innodb和memory，但是InnoDB不支持手动创建Hash索引，系统会维护一个自适应的Hash索引，

Hash索引底层的数据机构就是哈希表，和java中的hashmap相同

优点：在等值比较重，效率很高

去点：不适合范围查找

聚集索引和非聚集索引

聚集索引：索引值和数据是一起的

存储引擎是InnoDB的，主键上的索引是聚集索引

存储引擎是MyISAM的，任意字段上的索引都是聚集索引

聚集索引的优点：可以减少一次查询

缺点：对数据进行修改或删除时需要更新索引树

二级索引

二级索引也属于非聚集索引

叶子节点上存储的是主键的值，而不是这一行数据

回表：查询到的是数据的id，所以需要再查找一次

避免回表是提高SQL执行效率的手段，减少使用select * …

覆盖索引

指某个查询语句可以通过索引的覆盖来完成，而不需要回表查询真实数据。

覆盖值得是执行查询语句时，查询需要的所有列都可以从索引中得到，而不需要再去查询实际数据行

创建语句

create index idx_user_username_email on user (username, email)

创建覆盖索引需要考虑查询的字段选择，如果查询需要的字段较多，就需要创建包含更多列的覆盖索引

索引下推

一般情况下，索引下推是mysql优化器自动处理的，可以将查询中的过滤条件下推到索引层级中处理，从而减少回表次数，优化查询性能。

会在索引的叶节点层级执行查询的过滤条件，过滤掉无用的索引记录，仅返回符合条件的记录的主键。

相当于mysql会根据查询条件创建一个索引，查询到索引时直接进行判断。

单列索引

对某一列进行索引

复合索引

对多列值组合起来进行索引，因为有了复合索引，所以才有了索引下推以及覆盖索引

索引的优缺点

优点：

1. 索引能够减少数据库查询的数据量，提高查询的速度
2. 当查询需要按照某个字段进行排序的时候，索引可以加速排序的过程
3. 能够减少磁盘的io次数

缺点：

1. 占据了额外的存储空间
2. 增删改操作需要更新索引，增加了性能损耗
3. 要占用内存和cpu资源，再大规模并发访问的场景下会对系统性能造成影响

什么时候使用索引？

1. 频繁执行查询操作的字段
2. 大表，表的数据量很大
3. 需要排序或分组的字段
4. 外建关联的字段

不建议：

1. 频繁执行更新
2. 小标
3. 唯一性很差的字段

mysql优化

优化方式

1. sql查询方式：最低成本的优化手段，但效果显著
2. 库表结构优化：库表结构、索引、字段类型
3. 系统配置优化：调整最大连接数、内存管理等等
4. 硬件优化：省级硬盘，扩容

通过命令查看数据库运行情况

show global status like 'Com_select';
show global status like 'Com_update';
show global status like 'Com_insert';
show global status like 'Com_delete';

这些结果能够查看数据库当前的整体状态：读密集型还是写密集型

慢查询日志

将查询较慢的DQL语句记录下来，便于定位查询较慢的sql语句

默认情况下会将记录记录到data目录的log文件里

# 查看慢查询日志功能是否开启
show variables like 'slow_query_log'

# 可以在my.ini进行配置
[mysqld]
show_query_log=1
long_query_time=2
# 配置完后要重启mysql

show profiles

# 产看profiling是否开启(mysql默认是开启的)
select @@profiling
# 开启profiling
set profiling=1

# 查看某个语句在执行过程中，每个阶段的耗时情况
show query for query 19;

# 查看某个语句在执行过程中，cpu的占用情况
show profile cpu for query 19

explain

加在正常的语句前面

# 查看某个selecy语句的执行计划
explain select * from emp where empno=7306;

# id越大优先级越高，id相同则遵循自上而下的顺序执行

select_type反映了查询语句的类型，常用之包括：

• simple：不包含子查询或union操作
• primary：表时当前查询时一个著查询·
• union：表时查询中包含union操作
• subquery：子查询
• derived：派生表（表时出现在from后面）

type反应了查询表中数据时的访问类型，常见的值有（查询方式）

• null：效率最高，一般不可能优化到这个基表，只有查询时没有查询表的时候，访问类型是null
• system：访问系统表的时候
• const：根据主键或唯一索引查询，索引值是常量值时
• eq_ref：根据主键或唯一索引查询，索引值不是常量值时
• ref：使用了非唯一的索引
• range：使用了索引，扫描了索引树的一部分
• index：使用了索引，但是也需要遍历整个索引树
• all：全表扫描

效率最高的时null，最低的是all，从上到下降低

possible_keys

这个查询可能会用到的索引

key

实际用到的索引

key_len

反应索引中使用的列所占的总字节数

rows

查询扫描的预估计行数

extra

给出了与查询相关的额外信息和说明

索引优化

最左前缀原则：想要让索引生效，必须遵循最左索引原则。

• 当创建了一个复合索引时，在查找时第一个索引名称必须要使用才会让索引生效

eg：索引(name, age, gender),想要让这个索引生效，条件中必须出现name字段

• 如果断开了，后面就无法使用索引

• 在范围查询时，范围条件右侧的列不会使用索引。可以加=来避免这个问题

索引失效情况

1. 索引列参与了运算
2. 索引列进行模糊查询时以%开头
3. 索引列是字符串类型，但是查找时省略了单引号
4. 查询条件中有or，只要存在未添加索引的字段，索引就失效
5. 复合条件的记录在表中的占比较大
6. is null 和is not null在表中占比较大

指定索引

当一个字段上既有单列索引，又有复合索引时，可以指定用哪个索引

use index(索引名称): #建议使用
ignore index(索引名称): #忽略
force index(索引名称): #强行使用

select * from t_customer use index(...) where name='zhangsan';

覆盖索引

可以避免回表

前缀索引

varchar和text这种类型他的数据可能会很长，建立索引会占用大量的空间

# 对ename字段前两个字符建立索引
create index idx_emp3_ename2 on emp4(enmae(2));

# 越接近1，索引效果越好
select count(distinct substring(ename, 前几个字符)) / count(*) from emp4;

单列索引和复合索引怎么选择

当查询语句中有多个条件，建议将这些字段建立为复合索引

复合索引排序规则：按照左边的索引升序排，相同情况下按后面的字段排。建议将唯一性强的字段排在第一位

索引创建原则

1. 表的数据量很大
2. 经常出现在where，order by，group by后面的字段
3. 有较强的唯一性
4. 如果字段存储文本，内容较大，一定要创建前缀索引
5. 尽量使用复合索引，避免回表
6. 如果一个字段中的数据不会为null，建议建表时添加not null约束，这样优化器会知道使用哪个索引更有效
7. 不要创建太多的索引，当对数据增删改时，索引需要重新排序
8. 如果使用很少的查询，经常的增删改不建议加索引

SQL优化

order by优化

对排序字段加索引

在复合索引中，默认都是升序排。如果要一个升序，一个降序，需要在建立索引时进行声明

1. 要遵循最左索引
2. 尽量使用覆盖索引
3. 针对条件建立不同的索引
4. 如果无法避免filesort，要注意排序缓存的大小，默认缓存大小是256kb，可以修改系统变量sort_buffer_size

group by优化

最寻最左前缀原则，不一定要出现在group by后面，出现在where后面也有效

limit优化

当数据特别大时，越向后取，效率越低

可以通过表连接进行按照索引查询出子表，再进行查询

主键优化

1. 主键值不要太长，耳机索引叶子节点存储的是主键值，太长会导致占用空间较大
2. 尽量是以哦那个auto_increment生成主键，尽量不要使用uuid做主键
3. 尽量不使用业务主键，因为业务会导致主键值的频繁更改，而主键值不建议修改，因为主键修改之后它作为聚集索引会重新排序
4. 插入数据时，主键最好是顺序插入，不要乱序插入，乱序会导致B+树叶子节点频繁进行页分裂与页合并操作

insert优化

• 数据量较大时，可以使用批量插入
• mysql默认是自动提交事务，当插入大量数据时，建议开启事务和手动提交事务。不建议自动提交机制
• 主键值采用顺序插入，效率会更高
• 超大数据量插入可以考虑mysql提供的load指令，将csv文件中的数据批量导入到数据库表中，并且效率很高

mysql --local-infile -uroot -p1234

set global local_infile

load data local infile 'path' into table t_temp fields terminated by ',' lines terminated by '\n';

count(*)优化

count(*)的效率相比其他的count(其他)是最高的

update优化

当存储引擎是InnoDB时，表的行级锁是针对索引添加的锁，如果索引失效了，会提升为表级锁。
称): #建议使用
ignore index(索引名称): #忽略
force index(索引名称): #强行使用

select * from t_customer use index(…) where name=‘zhangsan’;

#### 覆盖索引

可以避免回表

#### 前缀索引

varchar和text这种类型他的数据可能会很长，建立索引会占用大量的空间

```sql
# 对ename字段前两个字符建立索引
create index idx_emp3_ename2 on emp4(enmae(2));

# 越接近1，索引效果越好
select count(distinct substring(ename, 前几个字符)) / count(*) from emp4;

单列索引和复合索引怎么选择

当查询语句中有多个条件，建议将这些字段建立为复合索引

复合索引排序规则：按照左边的索引升序排，相同情况下按后面的字段排。建议将唯一性强的字段排在第一位

索引创建原则

1. 表的数据量很大
2. 经常出现在where，order by，group by后面的字段
3. 有较强的唯一性
4. 如果字段存储文本，内容较大，一定要创建前缀索引
5. 尽量使用复合索引，避免回表
6. 如果一个字段中的数据不会为null，建议建表时添加not null约束，这样优化器会知道使用哪个索引更有效
7. 不要创建太多的索引，当对数据增删改时，索引需要重新排序
8. 如果使用很少的查询，经常的增删改不建议加索引

SQL优化

order by优化

对排序字段加索引

在复合索引中，默认都是升序排。如果要一个升序，一个降序，需要在建立索引时进行声明

1. 要遵循最左索引
2. 尽量使用覆盖索引
3. 针对条件建立不同的索引
4. 如果无法避免filesort，要注意排序缓存的大小，默认缓存大小是256kb，可以修改系统变量sort_buffer_size

group by优化

最寻最左前缀原则，不一定要出现在group by后面，出现在where后面也有效

limit优化

当数据特别大时，越向后取，效率越低

可以通过表连接进行按照索引查询出子表，再进行查询

主键优化

1. 主键值不要太长，耳机索引叶子节点存储的是主键值，太长会导致占用空间较大
2. 尽量是以哦那个auto_increment生成主键，尽量不要使用uuid做主键
3. 尽量不使用业务主键，因为业务会导致主键值的频繁更改，而主键值不建议修改，因为主键修改之后它作为聚集索引会重新排序
4. 插入数据时，主键最好是顺序插入，不要乱序插入，乱序会导致B+树叶子节点频繁进行页分裂与页合并操作

insert优化

• 数据量较大时，可以使用批量插入
• mysql默认是自动提交事务，当插入大量数据时，建议开启事务和手动提交事务。不建议自动提交机制
• 主键值采用顺序插入，效率会更高
• 超大数据量插入可以考虑mysql提供的load指令，将csv文件中的数据批量导入到数据库表中，并且效率很高

mysql --local-infile -uroot -p1234

set global local_infile

load data local infile 'path' into table t_temp fields terminated by ',' lines terminated by '\n';

count(*)优化

count(*)的效率相比其他的count(其他)是最高的

update优化

当存储引擎是InnoDB时，表的行级锁是针对索引添加的锁，如果索引失效了，会提升为表级锁。