mysql(一)

重学mysql

1、解决mysql5.7乱码的问题

show variables like 'character_%' //查看mysql的默认编码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JdWmtsT0-1661326196160)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20211124105415897.png)]

如果在创建数据库表的时候如果不指定数据库使用的编码，默认的就会使用mysql默认的编码，如果mysql的版本低于8.0的时，默认的编码是latin1，就会导致中文的乱码问题,“mb4”相当于4个字节表示一个字符，解决5.7的乱码需要进行修改my.ini文件中默认的字符集和比较的字符集

show variables like 'collation_%';//查看默认的字符比较的规则，

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4YBlgw9E-1661326196161)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20211124110312445.png)]

2、sql的分类

DDL:数据定义语言

CREATE\ ALTER\ DROP\ RENAME\ TRUNCATE

DML:数据操作语言

INSERT\ DELETE\ UPDATE\ SELECT

DCL:数据控制语言

COMMIT\ ROLLBACK\ SAVEPOINT //mysql在window下是不区分大小写的

3、简单的select查询

SELECT employee_id emp_id,first_name emp_first_name,last_name emp_last_name FROM atguigudb.employees;

#列的别名可以使用一对""引起来
select employee_id emp_id, last_name as lname,department_id "部门id" from employees;

# 去除重复行
select distinct department_id from employees;

#空值参与运算，结果也一定为空，null,不等同于0，''
select salary*(1+commission_pct) "员工工资"from employees;

#着重号 ``
select * from `order`;

# 查询常数，用于所有部门都属于一个公司下，是常量显示
select '小红毛' , employee_id from employees;

#显示表结构alter
describe employees;
desc employees;

#查询last_name为“king”的员工  ""可以用来起别名，''的话表示是字符串
select * 
from employees
where last_name = 'king';

#查询员工12个月的工资总和，并且起别名为ANNUAL SALARY
select salary*12 "ANNUAL SALARY" FROM employees;
#查询employees表中去重的job_id之后的数据alter
select distinct job_id from employees;
#查询工资大于12000的员工的姓名和工资alter
select first_name fname, salary from employees where salary >12000;
#查询员工号为176的员工的姓名和部门号alter
select last_name department_id where employee_id = 176;
#查询表departments的结构，并且查询其中的全部的数据
desc departments;

4、算术运算符

/**
运算符
*/
#sql中的+会将字符串自动的转换成数字，前提是字符串里存的是数字，如果是字母的话，会看作0处理
#如果分母为0，得到的结果为空
select 100+'a';

#查询员工id为偶数的员工信息
select * from  employees where employee_id % 2=0;

#<=>安全等于，与“=”相比最大的区别是可以去比较null值
select * from employees where commission_pct <=> null;

# is null \\ is not null \\isNull
select * from employees where commission_pct is null;
select * from employees where commission_pct is not null;
select * from employees where ISNULL(commission_pct);

# LEAST() \GRATEST(),如果比较的是字符，比较的是ASCLL码的值,比较长度的话可以使用length()函数
select least('a','b','c'),greatest('k','m','l') from dual;
select least(length(first_name),length(last_name)) from employees;

# between .... and
#查询出来工资6000到8000的员工的工资
select * from employees where salary between 6000 and 8000;

# in \\ not in (set集合)
# 查询部门为10，20，30的部门员工的信息alter
select * from employees where department_id in (10,20,30);

# like 模糊查询
#查询出来last_name中包含字符‘a’的员工,%代表不确定字符的个数
select * from employees where last_name like '%a%';

#查询出来第二个字符为a的员工信息alter
select * from employees where last_name like'_a%';

#如果名字中有_字符，可以使用转义字符'\','$'
select * from employees where lase_name like '_\_a%';

#正则表达式 REGEXP \ RLIKE
select 'shkstart' REGEXP '^shk','shkstart' regexp 't$','shkstart' REGEXP 'ab';

# 逻辑运算符： or || AND && NOT !  XOR(异或，满足前面的，不满足后面的，满足后面的，不满足前面的)
select last_name,salary,department_id from employees where department_id = 50 And salary>600;
select last_name,salary,department_id from employees where department_id = 50 XOR salary>6000;

#位运算符 按位与：&   按位或：|  按位异或：^ 按位取反：~  按位左移右移动 >> <<

练习题：

# 1.选择工资不在5000到12000的员工的姓名和工资
select last_name lname,salary s from employees where salary>12000 or salary <5000;  
select last_name ,salary from employees where salary not between 5000 and 12000;
# 2.选择在20或50号部门工作的员工姓名和部门号
select last_name, department_id from employees where department_id in (20,50);
select last_name,department_id from employees where department_id = 20 or department_id = 50;
# 3.选择公司中没有管理者的员工姓名及job_id
select last_name,job_id from employees where manager_id <=> null;
# 4.选择公司中有奖金的员工姓名，工资和奖金级别
select * from employees;
select last_name,salary,commission_pct from employees where commission_pct is not null;
# 5.选择员工姓名的第三个字母是a的员工姓名
select last_name from employees where last_name like '__a%';
# 6.选择姓名中有字母a和k的员工姓名
select last_name from employees where last_name like '%a%k%' or last_name like '%k%a%';
# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
select * from employees where first_name like '%e';
# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
select job_id,last_name from employees where  department_id between 80 and 100; 
# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
select last_name ,salary,manager_id from employees where manager_id in (100,101,110);

5、排序和分页

/**
排序和分页： 
1.如果没有使用排序操作，默认使用查询出来的操作，查询出来的是按照数据存储的先后进行查询
*/
# order by:  asc:升序  dsc: 降序,          如果没有指定显示的类型，默认为升序
select * from employees order by salary asc;
#我们可以使用列的别名进行排序,列的别名只能在orderby中去使用
select employee_id,salary*12 allsalary from employees order by allsalary;
#显示员工信息，department降序，salary升序排列
select * from employees order by department_id desc ,salary asc;
#mysql中使用limit实现分页操作alter， 第一个参数为偏移量，是具体到那条数据开始，第二个参数为显示的条数，显示多少条
select employee_id last_name from employees limit 0,5;
#mysql8.0新特性  limit .... offset....,跟之前的需要进行颠倒一下  
#oracel中的分页，oracel中共单独维护了一列字段为 RowNUM,我们可以使用该字段进行分页操作

练习题

#1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示
select last_name,department_id ,salary*12 s from employees order by s desc ,last_name asc;
#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
select last_name,salary from employees where salary not between 8000 and 17000 order by salary limit 20,20;
#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
select * from employees where email like '%e%' order by length(email) desc, department_id asc;

6、多表查询

/*
多表查询
一、为什么需要将表的设计进行拆分：
	1.字段的冗余，会出现很多null的现象
    2.字段太多，会出现io读写过高的现象
    3.如果多个人去操作这一张表，就会出现锁表的现象
二、笛卡尔积现象：
	如果需要员工的id，以及员工的部门，从两张表中去查
        select employee_id,department_id from employees , departments//会出现笛卡尔积现象
错误的原因：
    缺少了连接条件
如果一个字段在不同的表中都有出现，就需要明确的指明去哪张表中去查询
三、从sql优化层面来说，就需要指定每字段所在的表的名字   表名.字段名
	sql语句执行的顺序，先执行from 然后select 最后where，如果指定了表的别名，就必须要使用别名
四、多表查询的分类：
	1.等值连接vs 非等值连接
    2.自连接vs非内连接
    3.内连接vs外连接
*/
#查询员工名为'Abel'的人在哪个城市工作
select * 
from 
employees e,departments d,locations l
where
e.department_id = d.department_id and d.location_id = l.location_id
and e.last_name = 'Abel';
#查询员工的员工号，名字，部门编号，以及city
select employee_id ,last_name,e.department_id,city
from employees e, departments ds, locations ls 
where
e.employee_id = ds.department_id and ds.location_id =ls.location_id; 

#等值连接(两张表中有可以关联的关系)，和非等值连接（两张表中没有创建连接的关系字段）
select e.last_name,j.grade_level from 
job_grades j ,employees e
where
e.salary between j.lowest_sal and highest_sal;

# 自连接 vs非自连接
#查询员工姓名及其管理者的id和姓名
select e1.last_name,e2.last_name from employees e1, employees e2 where e1.manager_id = e2.employee_id;

/*内连接和外连接      查询的条件中一般会带有所有的字样
内连接：合并具有同一列的两个以上表的行，结果不包含一个表与另一个表不匹配的行
外连接：合并具有同一列的两个以上表的行，结果包含一个表与另一个表匹配的行之外，还包括左表或者右表不匹配的行
		1.左外连接 :两个表在连接的过程中，除了返回满足连接条件的行之外，还返回了左表中不满足条件的行
        2.右外连接：两个表在连接的过程中，除了返回满足连接条件的行之外，还返回了右表中不满足条件的行
        3.满外连接：两个表在连接的过程中，除了返回满足连接条件的行之外，还会返回两张表中不满足条件的行
*/        
#查询所有员工的last_name,department_name   左外连接 sql92   outer 可以省略
select last_name,department_name
from employees 
left join departments
on
employees.department_id = departments.department_id;
# 内连接 92语法   inner join inner 可以省略
select last_name, department_name
from employees join departments
on employees.department_id = departments.department_id;
# 右外连接   outer可以省略
select last_name,department_name
from employees es
right join departments ds
on es.department_id = ds.department_id;
# 满外连接 UNION 会去重，效率会低于UNIONALL，如果明确查询的结果不存在重复数据或者需要去重的数据的话，就可以使用UNIONALL

练习题

# 1.显示所有员工的姓名，部门号和部门名称。
select es.last_name,es.department_id,ds.department_name
from employees es left join departments ds
on es.department_id = ds.department_id;
# 2.查询90号部门员工的job_id和90号部门的location_id
select job_id,location_id,es.last_name
from employees es,departments ds
where es.department_id = ds.department_id
and es.department_id = 90;
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
select es.last_name,ds.department_name,ds.location_id,ls.city,es.commission_pct
from employees es left join departments ds on es.department_id = ds.department_id
left join locations ls on ls.location_id = ds.location_id
where es.commission_pct is not null;
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
select es.last_name,es.job_id,ds.department_id, ds.department_name,ls.city
from locations ls left join departments ds on ls.location_id = ds.location_id
left join employees es on es.department_id = ds.department_id
where ls.city = 'Toronto';
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
select ds.department_name,ls.street_address,es.last_name,es.salary,ds.department_name
from departments ds left join employees es on ds.department_id = es.department_id 
left join locations ls on ds.location_id = ls.location_id 
where ds.department_name = 'Executive';

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
/*employees Emp# manager Mgr#
kochhar 101 king 100*/
select es1.last_name "employees", es1.employee_id "Emp#",es2.last_name "manager", es2.employee_id "Mgr"
from employees es1 left join employees es2 on es1.manager_id = es2.employee_id
where es1.employee_id = 101;
# 7.查询哪些部门没有员工
select ds.department_id ,ds.department_name,es.department_id
from employees es right join departments ds on es.department_id = ds.department_id
where es.department_id is null;
# 8. 查询哪个城市没有部门
select ls.city,ls.country_id,ds.department_id
from locations ls left join departments ds on ds.location_id = ls.location_id
where ds.department_id is null;
# 9. 查询部门名为 Sales 或 IT 的员工信息
select * 
from departments ds left join employees es on ds.department_id = es.department_id
where ds.department_name in ('Sales','IT');

7、子查询

/*
1.select 中出现的非组函数的字段必须声明到group by中
	反之，group by 中声明的字段不可以出现在select中
2.group by 声明到 from，where的后面，limit，order by的前面
3.GROUP BY 还可以使用with rollup关键字，在查询出来的所有分组之后，增加一列统计查询出来的总和
4.count的使用的话，尽量使用count(*),count 1 ,不要使用具体的字段
5.Having:如果过滤条件使用了聚合函数，必须要使用having代替where,否则会报错
6.如果使用了having后，having必须声明到group by的后面，having不能单独使用，必须要和group by一起使用
*/
#查询员工的平均奖金率
select avg(commission_pct) from employees;

#avg会将空的员工直接过滤掉，导致数据会不精准的情况发生。
#如果统计表中的记录数，可以使用count(*)字段
select sum(commission_pct) / count(ifnull(commission_pct,0 )) from employees;


#查询各个部门的平均工资
select avg(ifnull(salary,0)),sum(salary),department_id 员工部门 from employees group by department_id with rollup;
#查询各个部门，各个工种的平均的平均工资

#查询出来各个部门中，最高工资比10000高的部门信息
select  department_id ,max(salary) max from employees group by department_id having max>10000;

#查询出来部门id为10，20，30，40，这四个部门中最高工资比10000高的部门信息alter
select department_id,max(salary) max from employees where department_id in (10,20,30) group by department_id
having max>10000;

/*
where 和having的对比：
	1.从适用范围上来说，where可以直接使用表中的字段作为筛选条件，但是不能使用分组中的计算函数作为筛选条件
		having必须要和group by 一起使用，可以将分组中的计算函数作为筛选条件
	2.如果需要通过关联表进行获取到所需要的数据的话，where是先筛选后链接，having是先连接后筛选
    3.如果过滤条件中没有聚合条件，where的执行的效率要高于having
    4.where不用去进行分组，上来的话就可以进行过滤，如果是having的话就会将数据先分组，然后过滤

*/			

/*
sql底层的执行原理：
	1.编写的顺序
		select ...........(聚合函数) 
        from .......... (left/right)join  .... on
        where 多表的连接条件 AND 不包含聚合函数的过滤条件
        group by ......
        having ..............(包含过滤条件的聚合函数)
        order by ....... (asc/desc)
        limit .....
	2.执行的顺序：
		先执行from中的内容，然后执行select，最后执行排序，和分页操作，中件可以理解成是一张虚拟表进行执行的
        from-> on -> (left/right join) -> where -> group by ->having ->select->distinct->order by ->limit
*/

#1.where子句可否使用组函数进行过滤?
#不可以
#2.查询公司员工工资的最大值，最小值，平均值，总和
select avg(ifnull(salary,0)),max(salary),min(salary),sum(salary) from employees;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
select avg(ifnull(salary,0)),max(salary),min(salary),sum(salary) ,job_id from employees group by job_id; 
#4.选择具有各个job_id的员工人数
select count(*),job_id from employees group by job_id;
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(salary)-min(salary) "DIFFERENCE" from employees;
# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
select max(salary),manager_id from employees where  manager_id is not null group by manager_id having min(salary)>=6000;
# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
select ds.department_name ds,ds.location_id,count(employee_id),avg(es.salary) ave
from departments ds left join employees es on ds.department_id = es.department_id group by ds.department_id order by ave desc;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
select es.job_id,ds.department_name,js.job_title,min(es.salary) 
from departments ds left join employees es on ds.department_id = es.department_id  left join jobs js on es.job_id = js.job_id
group by es.department_id, es.job_id having job_id is not null;

/*
子查询：
	1.子查询就是指一个查询语句嵌套到另一个查询语句的内部查询
    2.同一个表中先计算得到一个数据结果，然后与这个数据结果进行比较
	3.子查询要包含到括号内部，子查询在主查询之前就已经执行完成，子查询的结果被主查询使用
    4.将子查询放到比较条件的右侧
    5.单行操作符对应单行子查询，多行操作符对应多行操作符
    
子查询的分类：
角度1：内查询返回的结果的条目数
		1.单行子查询：子查询的结果只有一条数据
        2.多行子查询：子查询的结果有多条数据
角度2：内查询寻是否被执行多次
		1.相关子查询：内查询和子查询没有什么关联
        2.不相关子查询： 内查询和子查询相关连
*/
# 谁的工资比Abel的高
select salary 
from employees
where last_name = 'Abel' ;
select * from employees 
where salary > (select salary 
from employees
where last_name = 'Abel' ) ;

#自连接处理子查询alter
select es2.* from employees es1,employees es2 where es1.last_name = 'Abel' and es2.salary > es1.salary;

#1.单行子查询
# 查询工资大于149号员工工资的员工的信息alter
select * from employees 
where salary>(select salary from employees where employee_id = 149);

#返回job_id与141号员工相同 salary比143号员工多的员工的姓名，job_id 和工资alter
select job_id,salary from employees
where job_id = (select job_id from employees where employee_id = 141 )
and salary> (select salary from employees where employee_id = 143 );

#返回公司工资最少的员工的last_name,job_id 和salary
select last_name,job_id,salary from employees
where salary = (select min(salary) from employees );

# 查询与141号或者174号员工的manager_id和department_id 相同的其他的员工的employee_id,manager_id,department_id
select employee_id,manager_id,department_id from employees
where (manager_id =((select manager_id from employees where employee_id =141))and (department_id =
(select department_id from employees where employee_id = 141)) and (employee_id != 141)) 
or (manager_id =((select manager_id from employees where employee_id =174))and (department_id =
(select department_id from employees where employee_id = 174)) and (employee_id != 174)) ;

#查询出最低工资大于50号部门最低工资的部门id和其最低的工资
select department_id,min(salary) mins from employees where department_id is not null group by department_id having 
mins>(select min(salary) from employees where department_id = 50);
# 显示员工的employee_id,last_name和location,其中，若员工department_id 与location_id 为1800的department_id相同
#则location 为'Canada',其余的为'USA'
select employee_id,last_name,case department_id
when (select department_id from departments where location_id =1800) then 'Canada' else 'USA' end "location"  from employees;
# 多行子查询
#多行子查询的操作符：in any all some(any)

#返回其他job_id中比job_id为 'it_prog'部门任意一工资低的员工的员工号,姓名，job_id以及salary
select last_name,employee_id,job_id,salary from employees 
where job_id <> 'IT_PROG' AND salary < any(select salary from employees where job_id = 'IT_PROG');

#返回其他job_id中比job_id为 'it_prog'部门所有的工资低的员工的员工号,姓名，job_id以及salary
select last_name,employee_id,job_id,salary from employees 
where job_id <> 'IT_PROG' AND salary < all(select salary from employees where job_id = 'IT_PROG') ;

#查询平均工资最低的部门,查询出来的结果可以当作是一张表，然后在继续进行查询
select min(svg) from (select avg(salary) svg from employees group by department_id) a;
select department_id from employees group by department_id having avg(salary) = (select min(svg) from (select avg(salary) svg from employees group by department_id) a);

8、mysql中创建数据库

#尽量使用该方式创建数据库
create databases if not exists 数据库名字 character set utf8mb4;
#database 不能改名字，一些可视化的工具可以更改名子，原因是新建数据库，将原来的表复制到新库中，然后删除掉旧库
/*
表的创建，一种是直接进行创建，另外的就是通过使用as对现有的表进行查询，得到的结果作为表的信息进行创建
*/

Mysql中的数据类型：

类型	类型的列举
整数类型	INT,BIGINT,SMALLINT
浮点类型	FLOAT,DOUBLE
定点数类型	DECIMAL
位类型	BIT
时间日期类型	YEAR,TIME,DATE,DATETIME,TIMESTAMP
文本类型	CHAR,VARCHAR,TEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY
JSON类型	JSON对象，JSON数组
空间数据类型	POLYGON

创建表的两种方式

# 1.如何创建数据表
# 方式一alter:
create table if not exists myempl (
id Int,
#在使用VARCHAR来定义字符串的时候，必须在varchar后指定其长度
emp_name varchar(15),
hire_date date
);
# 查看表结构
desc myempl;
show create table myempl;
#方式二: 基于现有的表创建新的表,根据查询出来的结果就可以创建出来新的表，表中的数据也一同进复制过来
create table myempl2
As 
select * from employees;
# 查看表alter
select * from myempl2;

删除表，修改表，重命名表，清空表

#修改表 alter table
# 1. 添加一个字段alter
alter table myempl add salary double(10,2);#后面的2表示小数点后有两位，默认添加到表中的最后一个字段
select * from myempl;
desc myempl;
# 2. 修改一个字段,数据类型，长度，默认值
alter table myempl
Modify emp_name varchar(30) default 'lili';
# 3. 重命名一个表字段
alter table myempl 
change salary monthly_salary double(12,10);
# 4. 删除一个表字段
alter table myempl
drop column hire_date;


#重命名表
rename table myempl2 to mybaby;


#删除表alter
drop table mybaby;


#清空表alter
truncate myempl;

truncate 和 delete from 的区别

/*
truncate table 和delete from 的区别,truncate table,在使用的情况下，使用的资源比较的少
相同点:都可以实现对表中所有的数据的删除，对表的结构没有任何影响
不同点：
	1.truncate table 一旦执行该操作，表中的数据全部都背清除掉，同时，数据是不可以被回滚的alter
    2.delete from  一旦执行该操作，表中的数据可以全部的进行回滚
*/
truncate myempl;

# 7.DCL中COMMIT 和rollBACK
/*
COMMIT:提交数据，一旦执行了COMMIT，则数据就会被永久的保存到了数据库中，并且不能够进行回滚.
ROLLBACK：回滚数据，一旦执行回滚数据，就会回滚到上次最近的commit之后
*/

/*
8.DDL 和DML的说明
	1.DDL的操作一旦执行，就不可以进行回滚
    2.DML操作默认的情况，一旦执行，也是不可以进行回滚的
*/

9、MySQL的命名的规范

• 表名字段名必须使用小写字母或者数字，禁止出现数字开头，禁止两个下划线中间只出现数字，数据库字段名的修改代很大，因为无法进行预发布，所以字段名称要慎重考虑
• 禁用保留字，也就是mysql中的一些关键字，比如desc,range,match
• 表中必备的三个字段 id,gmt_create,gmt_modified
  • 其中id必为主键，类型为bigint unsigned、单表时自增，步长为1，gmt_create,gmt_modified的类型都为DATETIME类型，前者现在表示主动式的创建，后者表示过去分词被动式的更新
• 表的命名最好式遵循 "业务名称_表"的作用，如 alipay_task, force_project
• 库名与应用名称应当尽量的一致
• 适当的字符存储长度，不但可以节约数据库的空间，节约索引的存储，更重要的是可以提升检索速度

10、添加数据

# 添加数据,按照声明字段的先后顺序进行添加数据
#一条一条的插入
insert into emp1 values (1,'tom','2000-12-01',12346.1);
insert into emp1(id,`name`,hire_date,salary) 
values(12,'ddd','2222-2-01',15563.1522);
# 批量的插入
insert into emp1(id,`name`,hire_date,salary) 
values(13,'ddd','2222-2-01',15563.1522),(14,'ddd','2222-2-01',15563.1522);
# 更新数据，修改数据
# update....set .... where.....
#修改失败，是约束的影响造成的
update emp1 set name = 'lalall' where id = 12;

# mysql8的新特性：计算列
create table test1(
a int ,
b int,
c int generated always as (a+b) virtual # 字段c就是计算列
);
insert into test1(a,b) values(12,55);
select * from test1;

练习1

# 综合练习
# 1、创建数据库test01_library
create database if not exists test01_library character set utf8mb4;
use test01_library;
# 2、创建表 books表结构如下：
create table if not exists books(
id int,
name varchar(50),
`authors` varchar(100),
price float,
pubdate year,
note varchar(100),
num int
);
# 3、向books表中插入记录
# 1）不指定字段名称，插入第一条记录
insert into books values(1,'Story of Jane','Jane Tim',40,2001,'novel',0);
# 2）指定所有字段名称，插入第二记录
INSERT INTO books (id,name,`authors`,price,pubdate,note,num) VALUES
(2,'Story of Jane','Jane Tim',40,2001,'novel',0);
select * from books;
# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books (id,name,`authors`,price,pubdate,note,num) VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

# 4、将小说类型(novel)的书的价格都增加5。
update  books set price = price +5 where  note = 'novel';
# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
update books set price = 40,note = 'drama' where authors='EmmaT';
# 6、删除库存为0的记录。
delete from books where num = 0;
# 7、统计书名中包含a字母的书
select name from books where name like '%a%';
# 8、统计书名中包含a字母的书的数量和库存总量
select count(*),sum(num) from books where name like '%a%';
# 9、找出“novel”类型的书，按照价格降序排列
select * from books where note = 'novel' order by price desc;
# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
select * from books order by num desc, note asc;
# 11、按照note分类统计书的数量
select count(*) ,note from books group by note;
# 12、按照note分类统计书的库存量，显示库存量超过30本的
select sum(num) s,note from books group by note having  s >30;
# 13、查询所有图书，每页显示5本，显示第二页
select * from books limit 5,5;
# 14、按照note分类统计书的库存量，显示库存量最多的
select sum(num) s from books group by note order by s desc  limit 0,1;
# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name,' ',''))>=10;
# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
select name '书名', note , case note
 when 'novel' then '小说' 
 when 'law' then '法律' 
 when 'medicine' then '医药'
 when 'cartoon' then '显示卡通'
 when 'joke' then '笑话'
 end as '类型'
 from books;
# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
select name '书名',num '库存', case 
 when num>30 then '滞销'
when num>0 and num < 10 then '畅销'
when num = 0 then '无货'
else '正常'
end as '销售状态'
from books;
# 18、统计每一种note的库存量，并合计总量
select sum(num) note from books group by note;
# 19、统计每一种note的数量，并合计总量
select count(*) note from books group by note ;
# 20、统计库存量前三名的图书
select * from books order by num desc limit 0,3;
# 21、找出最早出版的一本书
select * from books order by pubdate asc limit 0,1;
# 22、找出novel中价格最高的一本书
select * from books order by price desc limit 0,1; 
# 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1;

练习2:

 数据操作之增删改algorithm
create database if not exists dbtest11 character set 'utf8mb4';
use dbtest11;
create table my_Employees(
id int(10),
first_name varchar(10),
last_name varchar(10),
userid varchar(10),
salary double(10,2)
);
create table users(
id int,
userid varchar(20),
department_id int

);
show tables;
insert into my_employees (id,first_name,last_name,userid,salary)
values
 (1,'patel','Ralph','Rpatel',895),
 (2,'Dancs','betty','Bdances',860),
 (3,'biri','ben','Bbiri',1100),
 (4,'Newman','chad','Cnewman',750),
 (5,'Ropeburn','Audrey','Aropebur',1550)
 ;
 select * from my_employees;
 
 #5. 向users表中插入数据
-- 1 Rpatel 10
-- 2 Bdancs 10
-- 3 Bbiri 20
-- 4 Cnewman 30
-- 5 Aropebur 40
insert into users (id,userid,department_id) 
values
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',10),
(4,'Cnewman',10),
(5,'Aropebur',10)
;

#6. 将3号员工的last_name修改为“drelxer”
update my_employees set last_name = 'drelxer' where id = 3;

#7. 将所有工资少于900的员工的工资修改为1000
update my_employees set salary = 1000 where salary <900;
#8. 将userid为Bbiri的user表和my_employees表的记录全部删除
delete
u,e  
from
users u, my_employees e 
where u.userid = 'Bbiri' or e.userid = 'Bbiri';
DELETE u,e
FROM users u
JOIN my_employees e ON u.`userid`=e.`Userid`
WHERE u.`userid`='Bbiri';

#9. 删除my_employees、users表所有数据
truncate my_employees;
truncate users;
#10. 检查所作的修正
select * from my_employees;
select * from users;
use dbtest11;
create table pet (
`name` varchar(20),
`owner` varchar(20),
species varchar(20),
sex char(1),
birth year,
death year
);
insert into pet(`name`,	`owner`,species,sex,birth,death)
values
('Fluffy','harold','Cat','f','2003','2010'),
('Claws','gwen','Cat','m','2004',null),
('Buffy',null,'Dog','f','2009','2010'),
('Fang','benny','Dog','m','2000',null),
('bowser','diane','Dog','m','2003','2009'),
('Chirpy',null,'Bird','f','2008',null)
;
select * from pet;

# 4. 添加字段:主人的生日owner_birth DATE类型。
alter table pet add column owner_brith year;
alter table pet drop column owner_brith;
# 5. 将名称为Claws的猫的主人改为kevin
update pet set owner = 'Kevin' where name = 'Claws';
# 6. 将没有死的狗的主人改为duck
update pet set owner = 'duck' where death is null;
# 7. 查询没有主人的宠物的名字；
select name from pet where owner is null;
# 8. 查询已经死了的cat的姓名，主人，以及去世时间；
select name,owner,death from pet where death is  null;
# 9. 删除已经死亡的狗
delete from pet where death is not null and species = 'Dog';
# 10. 查询所有宠物信息
select * from pet;

create table if not exists employee(
id int(10),
`name` varchar(20),
sex varchar(2),
tel varchar(11),
addr varchar(40),
salary double(10,2)
);
# 3. 查询出薪资在1200~1300之间的员工信息。
select * from employee where salary between 1200 and 1300;
insert into employee (id,`name`,sex,tel,addr,salary) 
values
(10001,'张一一','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13456789000','河北保定',1201.58),
(10003,'李四','男','13456789000','广东佛山',1004.58),
(10004,'刘小强','男','13456789000','广东深圳',1500.58),
(10005,'王艳','女','13456789000','广东广州',1405.58)
;
# 4. 查询出姓“刘”的员工的工号，姓名，家庭住址。
select id,`name`,addr from employee where name like '刘%';
# 5. 将“李四”的家庭住址改为“广东韶关”
update employee set addr = '山东菏泽' where name = '李四';
# 6. 查询出名字中带“小”的员工
select * from employee where name like '%小%';

11、MySQL中的约束

# mysql数据类型的整理
#1.创建数据库的时候指定字符集
create database if not exists dbtest12 character set 'utf8mb4';
#2.创建表的时候指定表的字符集
create table temp (
id int 
)character set 'utf8mb4';
#创建表，指定表名中的字段的时候，可以指定字段的字符集
create table temp1(
id int,
`name` varchar(15) character set 'utf8mb4'
);
show variables like 'character_%';


# 数据库中的约束
# 如何查看表中的约束
select * from information_schema.table_constraints where table_name = 'employees';

# 1.非空约束 not null
/*限定某个字段或者某个列的值不允许为空
	1.默认，所有的类型的的值可以是null,包括int,float等数据类型
    2.非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
    3.一个表可以有很多的列，都能分别限定了非空
    4.空字符串不等于null,0也不能等于null;

*/
# 1.1在创建表的时候添加约束
create database dbtest13;
use dbtest13;
create table test1(
id int not null,
last_name varchar(15) not null,
salary double,
email decimal(10,2)
);
#1.2修改表的时候添加约束
alter table test1
modify email varchar(25) not null;


# 2. unique 唯一约束 
/*
用来限制表中某一列或者某个字段的值不能重复,唯一约束允许值为空
*/


#2.1在创建表的时候添加约束
create table test2(
id int unique, # 列级的约束
last_name varchar(15) not null,
salary double,
email decimal(10,2),
# 在这里写是表级的约束,在创建约束的时候，如果不给唯一的约束命名，就默认和列名相同
CONSTRAINT uk_test2_emmmail unique(email)
);


#2.2在修改表的时候添加上约束
alter table test2
add constraint uk_test2_sal unique(salary);
alter table test2
modify last_name varchar(15) unique;
# 表级约束
create table user(
id int,
`name` varchar(20),
`password` varchar(25),
# 表级的约束在此处声明,如果不指定约束的名字的话，默认使用列名
constraint uk_user_name_paw unique(`name`,password)
);
# 2.3 删除唯一性约束
-- 添加唯一性约束的列上也会自动的创建唯一的索引
-- 删除唯一约束，只能通过删除唯一索引的方式删除
-- 删除时需要指定唯一索引名，唯一索引名和唯一约束名一样
-- 如果创建唯一约束的时候未指定名称，如果时单列和列名相同，如果是复合列，那就是复合列排在最前面的那一个的列名
alter table user
drop index `name`;
# 查看表中的索引
show index from 表名称，查看表中的索引;


# 主键索引  primary key   非空且唯一
/*
1. 主键索引相当于唯一约束+非空约束，主键约束列不允许重复，也不允许存在空值
2.一个表中最多只能有一个主键索引，简历主键索引可以在列级别创建，也可以在表级别创建
3.主键索引对应着表中的一列或者是多列
4.如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且都不允许重复
5.当创建了主键的约束的时候，系统会默认的在所在的列或者是列组合上面建立对应的主键的索引，根据索引进行查询，效率更高
*/
# 1.在创建表的时候如何声明主键约束
create table test4(
id int(5) primary key,  # 一个表中最多有一个主键约束，非空且唯一
last_name varchar(10)
# 2.表级添加约束
#constraint pk_test5 primary key(id)
);
show index from test4;

# 3.修改表添加约束
alter table test4
add primary key(id);

# 4. 删除表的主键约束
alter table test4
drop primary key;

#自增列 AUTO_INCREAMENT
/*
1. 一个表中最多只能有一个自增长列
2. 自增列的数据类型必须是整数类型
3.自增长列约束的列必须是键列(主键列，唯一键列)
4.自增列可以不用指定具体的值，自增长的进行赋值，如果指定值为0或者null的话，会被自增长的字段会被覆盖
*/
# 创建表的时候指定自增长列
create table test5(
id int primary key auto_increment,
last_name varchar(15)
);
desc test5;

# 在修改table的时候去添加
alter table test5
modify id int auto_increment;

11.2外键约束.

阿里的开发规范

不得使用外键和级联，一切外键概念都需要在应用层进行解决

外键和级联的更新适合于低并发，不适合分布式，高并发集群，外键影响数据库的插入速度

# 外键约束
/*
1.限定某个表某个字段的引用的完整性
2.从表的外键列，必须引用或者参考主表的主键或者唯一约束的列。因为被依赖的值必须是唯一的
2.在创建外键约束的时候，如果不指定约束的名字，会自动的产生一个外键名
3.在创建表的时候指定外键的时候，必须先创建主表，然后在关联从表
4.在删除表的时候，先删除从表，在删除主表
5.在从表中指定约束，可以指定多个约束
6.在创建外键约束的时候，会默认的在对应的列上创建普通的索引
7.在开发中不经常使用，外键的约束会消耗系统的资源，在开发过程中，经常使用的是应用层面进行解决
*/
# 主表和从表，父表和子表
# 先创建主表
create database dbtest14;
use dbtest14;
create table dept(
dept_id int primary key auto_increment,
dept_name varchar(15)
);
# 创建从表alter
create table emp(
emp_id int primary key auto_increment,
emp_name varchar(15),
department_id int,
constraint fk_emp_dept_id foreign key(department_id) references dept(dept_id)
);
show index from emp;
# 查看表中的约束
select * from information_schema.table_constraints where table_name = 'emp';

# 演示外键的效果
insert into dept values(10,'it');
insert into emp values(1001,'da',10);

show create table departments;
# 删除外键,如果不指定的话，删除所有的外键，还需要手动的删除外键对应的索引
alter table emp
drop primary key;
/*
在mysql中，外键的约束是有成本的，需要消耗系统的资源，对于大并发的sql，不适合使用外键操作
*/

12、常见的数据库对象

表	表就是存储数据的逻辑单元，以行和列的形式存在
数据库字典	就是系统表，存放数据库相关的表，系统表的数据通常由数据库系统维护，程序员只能查看，不能修改
约束	执行数据校验的规则，用于保证数据的完整性的规则
视图	一个或者多个数据表中数据逻辑的显示，视图不存储数据
索引	用于提高查询的性能，相当于书的目录
存储过程	用于一次完成的业务处理，可以通过传出参数将多个值传递给环境
存储函数	用于完成一次特定的计算，具有一个返回值
触发器	相当于一个事件监听器，当数据库发生特定的事件的时候，触发器会被动的触发，完成相应的处理

13、视图

视图一方面可以帮助我们使用表中的一部分，而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图，视图是一种虚拟的表，本身不具有数据，占用的空间很少，引用的还是原表中的数据

# 视图 create view  as '子查询'
# 创建视图 
# create view 视图名称
# AS 查询语句
/*
1.视图的创建和删除只影响视图的本身，不会影响对应的基表，但是对视图中的数据进行操作，
数据库中表中的数据也会相应的发生变化
2.向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句
3.视图，是向用户提供基表数据的另一种表现形式，通常情况下，小型的项目的数据库可以不使用视图
但是在大型项目中，它可以帮我们把查询出来的结果集放入到虚拟表中，提升使用的效率
4.简化查询，控制数据的访问
*/
# 创建视图，基于单表操作，可以在视图名字后面添加上对应的字段名字，与查询的字段一一对应
use atguigudb;
create view view_emp1 as 
select employee_id,last_name,first_name, salary
from employees;
select * from employees;

# 查看视图
show tables;
#查看视图的结构
desc view_emp1;
# 查看视图的属性信息
show table status like 'view_emp1'

14、批量插入数据

// 第一步：创建函数
delimiter //

DROP PROCEDURE
IF
	EXISTS proc_buildata;
CREATE PROCEDURE proc_buildata ( IN loop_times INT ) BEGIN
DECLARE var INT DEFAULT 0;
WHILE
	var < loop_times DO
	
	SET var = var + 1;
INSERT INTO `order` ( `id`, `user_code`, `goods_name`, `order_date`, `city` , `order_num`)
VALUES
	( var, var + 1, '有线耳机', '2021-06-20 16:46:00', '杭州', 1 );

END WHILE;

END // delimiter;

// 第二步：调用上面生成的函数，即可插入数据，建议大家造点随机的数据。比如改改城市和订单数量
CALL proc_buildata(4000);

# 创建过程函数的时候，我们需要开启下面的命令，允许创建函数设置
set global log_bin_strust_fuction_creators=1 # 不加global只是当前窗口有效

15、存储引擎

# 存储引擎
/*
存储引擎就是指的是表的类型，他的功能就是接收上层下来的指令，然后对表中的数据进行提取或者写入操作
mysql8.0默认的存储引擎是innodb
	1.innodb介绍
		mysql的版本在大于5.5之后，系统默认的存储引擎是innodb
        他被设计用来处理大量短期的事务，可以保证事务的完整提交和回滚,innodb支持事务的
        如果由大量的更新和删除的话，应该优先选择innoDB存储引擎
		缺点，写的处理效率差一点，对内存的要求比较高
        innodb的存储包括数据和索引，所以占用的内存比较高，mylsam主要包括的是索引
		innodb支持行级锁，表级锁，事务，外键
*/
# 查看mysql提供的存储引擎
show engines;
# 修改mysql中的存储引擎,修改完成之后需要重启mysql中的服务
set DEFAULT_STORAGE_ENGINE=MyISAM;

#修改表的存储引擎
alter table emp4 engine = MyISAM

/*
MyISAM引擎，主要的非事务处理存储引擎
1. MyISAM不支持事务，行级锁，外键，支持表锁，只缓存索引，内存占用少
2.访问速度快，对事物的完整性没有要求，以读和写为主的话，可以使用MyISAM存储引擎
*/
/*
archive引擎，用于数据的存储
achive是归档的意思，仅仅支持插入和查询两种功能
*/
/*
MEMORY存储引擎
memory采用的逻辑介质是内存，响应的速度很快，
*/

16、数据库的索引和调优相关

1. 为什么要使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构，相当于书种的目录，通过目录我们可以快速的找到需要的文章，MySQL也是一样，在查询数据的时候首先查看查询的条件是否命中某条索引，符合索引查找的话，就可以找到相关的数据，如果不符合，则需要进行全表的扫描。

2、索引的概述

• 索引是帮助mysql高效获取数据的数据结构，就是排好序的快速查找的数据结构，索引是在存储引擎中进行实现的

3、索引的优点：

• 降低数据库的io成本
• 通过创建唯一的索引，可以保证数据库中表的每一行的数据唯一性
• 可以加速表与表之间的连接，对于有依赖关系的子表和父表联合查询的时候，可以提高查询的效率和速度
• 再分组和排序子句中进行数据查询的时候，可以显著的减少分组和排序的时间，降低cpu的消耗

4、索引的缺点

• 创建索引和维护索引需要耗费时间，并且随着数据量的增加，所消耗的时间也会增加
• 索引需要占用磁盘的空间，除了数据表中数据占用空间之外，每个索引还会占用一定的物理空间，存储在磁盘上面，如果有大量的索引，索引文件也是很大的
• 虽然索引大大的提高了查询的速度，但是同时会降低更新表的速度，当对表中的数据进行增加和删除，修改的时候，索引也要动态的进行维护，降低了数据维护的速度

5、InnoDB中索引的演进

4.1使用索引之前的查找方式

数据在mysql底层中存储使用到的基本的单位是数据页，时间复杂度为o(n)

4.2、mysql中数据页和目录页得标识

MySQL中得目录页和数据页长得差不多，目录页中的两个列是主键和页号，我们把用来标识目录项得记录称为目录项记录，record_type属性用来标识区分数据页和目录页，数据页记录具体的数据，而目录页记录了与之关联数据页最小的主键值，以及对应数据页的地址值，页目录可以让我们快速的使用二分法进行查找，不管是数据页还是目录页，彼此之间都是通过双向链表进行连接，当目录页为两个以上的时候，将会向上再次抽取出来一个目录页。数据和数据之间是单向链表进行相连的

record_type不同值所代表的含义

• 0：普通得用户记录
• 1：目录项得的记录
• 2：最小项的记录
• 3：最大记录

6、B+树

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A8ayqMNn-1661326196164)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20220224170542894.png)]

• 目录页中：
  • 2代表着本页最小值，3代表着本页最大值，目录页中存的是与之关联的数据页或者是目录页的地址以及下个节点(数据页或者是目录页的最小值)，目录页超过两个的时候就会向上抽取出来一个目录页结构，然后目录页与目录页之间通过双向链表进行连接，目录和目录之间通过单项链表进行连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jocyhZhW-1661326196165)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20220224170719633.png)]

• 数据页中：
• 2代表着本页的最小值，3代表着本页的最大值，0代表着普通的数据，当插入数据的键没有顺序插入的时候，mysql会直接进行排序，数据页和数据页之间使用双向链表进行连接，数据和数据之间使用单项链表进行连接，mysql底层使用的数据页进行存储

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EFRAZo80-1661326196165)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20220224171202497.png)]

B+树：

不管事存放用户记录的数据页，还是存放目录项的数据页，我们都将其放入到了b+树这个结构中，我们称这些数据页为节点，我们实际用户的数据都是在b+树最底层的节点上面，这些节点被称为是叶子节点，其余的用来存放目录项的节点称为非叶子节点，或者内节点，b+树最上层的节点我们也称之为根节点

一个b+树的节点其实可以分为好多层，规定最下层的那层，也就是我们存放我们用户的那一层称为0层，之后依次上加，一般情况下b+树的树高不会超过四层，树的层数（高度）越低，然后io的次数就会越少，4层最多加载4次io

聚簇索引，非聚簇索引，以及联合索引：

1. 聚簇索引：

• 聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式，主键构成的索引就叫做聚簇索引,所有的数据都存储到了叶子节点中，也就是索引就是数据，数据就是索引（聚簇就表示数据行和相邻的键值聚簇存储到了一起）

• 数据页的记录是按照主键的大小顺序排列成一个单项的链表进行连接

• 各个数据页之间按照主键大小顺序排列成一个双项的链表

• 目录页也是一个升序的关系，双向的链表

• b+树叶子节点存储的是完整的用户记录，这种聚簇索引并不需要我们在mysql中显示的使用Index语句进行创建，Innodb存储索引会自动的为我们创建聚簇索引

• 优点：

  • 数据访问更快，因为聚簇索引将索引和数据保存在同一个b+树中，因此聚簇索引比非聚簇索引更快
  • 聚簇索引对于主键的排查和范围查找速度会更快
  • 按照聚簇索引的排列顺序，显示查询一定范围的数据的时候，数据都是紧密相连的，数据库不用从多个数据块中提取数据，节省了大量的io操作

• 缺点

  • 插入速度严重依赖于插入的顺序，按照主键顺序插入时最快的方式，否则会出现页分裂，严重的影响性能，因此我们对于Innodb表，我们可以定义一个自增的id为主键
  • 主键更新的代价很高，会导致被更新的行进行移动，对于innodb我们一般定义主键不能更新
  • 二级索引需要两次索引进行查找，第一次找到主键，第二次根据主键找到数据

• 限制

  • 对于存储引擎，Innodb数据支持聚簇索引，myIsam不支持聚簇索引
  • 每个mysql中的表只能有一个聚簇索引，一般情况下由主键来作为聚簇索引，如果没有定义主键，一般会选择非空唯一的索引代替，Innodb中会隐式的定义一个主键来作为聚簇索引
  • 为了充分的利用聚簇索引的特性，innodb表中的列尽量选择由顺序的id,而不采用无序的id,比如UUID，Md5,hash,字符串作为主键无法保证数据的顺序增长

2. 二级索引(辅助索引，非聚簇索引)

• 聚簇索引时以主键进行创建的，非聚簇索引就不是主键列进行创建的，想要以其他的列作为搜索的条件，我们可以多建几个b+树，不同的b+树种采用的排列的顺序时不同的，叶子节点不存储表中完整的记录，只是记录了c2字段的值，以及c1(主键字段)字段的值,如果查询语句种要查询的字段不在聚簇索引中的话，就会出现回表的操作，但是索引并不会失效

3、联合索引

• 联合索引就是非聚簇索引，

4、InnoDB的b+树索引的注意事项

4.1、根页面位置万年不变

B+树形成的过程：

• 每当为某个表创建一个B+树索引的时候，聚簇索引不是认为创建的，而是默认就有的，都会为这个索引创建一个根节点页面，最开始的时候表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项的记录
• 当向表中添加记录的时候，会先将用户的记录存储到根节点中
• 当根节点中的可用的空间用完之后，继续插入数据的时候哦，就会得到另一个新的数据页，这个时候数据就会迁移分配到新生成的数据页中，而根节点就会成为目录项记录的页
• 自B+树创建的时候开始，根节点就不会再移动，只要我们对某个表建立一个索引，那么他的根节点的页号便会被记录到某个地方，如果InnoDB存储引擎需要用到这个索引的时候，都会从固定的地方取出根节点的页号，然后来访问这个索引

4.2、内节点的目录项记录唯一性

• 内节点的目录记录必须唯一，必要的时候可以加上主键，也就是组成联合索引
• 一个页面最少存储两条记录

5、MyIsam中索引的方案

MyISAM存储引擎使用的是B树作为索引结构，叶子节点的data存放的是数据记录的地址

Innodb中的索引就是数据，也就是聚簇索引的那颗B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyIsam索引虽然也是树形结构，但是却将索引和数据进行分开存储：

• 将表中的记录按照记录的插入顺序单独的存储在一个文件中，称之为数据文件，这个文件不会划分为若干个数据页，有多少记录就往这个文件中塞多少记录，没有刻意的按照主键的大小进行排序，因此的不能在这些数据上使用二分法进行查找
• MyISAM存储引擎会将索引的信息另外的存储到一个称为索引文件的另一个文件中，MyISAM会单独的为表创建一个索引，只不过在索引的叶子节点中不是存储的完整的用户记录，而是主键值+数据记录地址的组合
• MyISAM仅仅保存的是数据记录的地址

6、MyISAM和InnoDB做对比

• myisam的索引方式是非聚簇的
• 在InnoDB中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，在MyISAM中却要进行一次回表操作，意味着MyISAM中建立的索引都是二级索引
• innoDB中的数据本身就是索引文件，而MyISAM中的文件和数据是分离的，索引文件中保存的是地址
• InnoDB中的非聚簇索引data域中存储相应记录主键的值，而myIsam记录的是地址，InnoDB中所有的非聚簇索引都引用主键作为data域
• MyISAM的回表操作十分的快速，直接拿着地址偏移量去文件中去取数据，反观InnoDB则是通过获取到主键之后再去聚簇索引中找到记录
• MyISAM可以没有主键
• 不要使用过长的字段作为主键，因为所有二级索引都引用主键索引，过长的主键索引会使二级索引变大

索引的代价：

索引是个好东西，但是不能乱加，他在时间和空间上都有消耗

• 空间上的代价
  • 每一个索引建立都要为他建立一个b+树，每个b+树的每一个节点都是一个数据页，一页默认会使用16kb的存储空间，如果很多数据页的话就会占用很大的一片空间
  • b+树的每层节点都是按照索引从小到大进行排序的，增加，删除修改维护索引就会花费大量的时间

7、Mysql数据结构选择的合理性

Mysql根本上来说选择数据结构解决的问题就是如何减少磁盘的IO操作，磁盘的IO操作对索引的使用效率十分的重要

7.1、Hash结构

• Hash本身就是一个函数，又被称为散列函数，它可以帮助我们提高检索数据的效率
• Hash算法是通过某种确定性的算法，比如MD5，SHA1，将输入变成输出，相同的数据永远可以得到相同的输出，如果要验证两个文件是否一致，我们可以将其进行hash计算，然后通过对比hash得到这两个文件是否一至的结论
• hashMap，查询修改删除的平均时间复杂度都是o(1),树，二叉树，查询修改删除的平均时间复杂度都是o(log2N)
• hash算法查询的效率比B+树快，一次检索就可以找到数据，而b+树的话需要自顶而下，多次访问节点才能找到数据
• hash碰撞，明明放到数组中的数据通过hash的话是不一致的，但是经过计算的话，放到数组中的位置是一致的，这样的话就叫做hash碰撞，数据库使用的是链接发(就是使用链表)
• hash结构的缺点：
  • Hash索引只能满足"=“,”<>"以及in的操作，如果进行范围查找，时间复杂度就会退化为o(n),而b+树依然可以保持o(log2N)的效率
  • hash索引数据存储是没有顺序的，在order by的情况下，Hash索引需要对数据进行重新的排列
  • 对于联合索引，hash值是将联合索引键合并在一起来计算的，无法单独的对某个键或者索引进行查询
  • 对于等值查询来说，通常hash索引的效率不高，如果索引列重复的值有很多，效率就会很低，会出现hash冲突
• hash索引使用的场景，比如Redis中，数据存储的核心就是Hash表，Mermory存储引擎的话支持hash索引，如果经常进行等值查询的话，就可使用该存储引擎
• B+树的话可以提供自适应的Hash索引，如果一个数据经常性的被访问的话，当满足一定条件的时候，这个数据页的地址就会存放到hash表中

7…2、二叉搜索树

如果我们利用二叉搜索树作为索引结构，那么磁盘io的次数和索引树的高度是相关的

二叉搜索树的特点

• 一个节点只能有两个子节点，也就是一个节点的度不能超过2
• 左子节点<右子节点；右子节点<本节点

查找的规则

如果搜索的数据为k

1. 如果key大于根节点的话，就会去右子树中去查找
2. 如果key小于根节点的话，就去左子树中进行查找
3. 如果key等于根节点的话，也就是找到了这个节点，返回根节点

7.3、AVL(平衡二叉树)

性质：

• 他是一个空树，或者说他的左右两个子树的高度差绝对值不超过1，并且左右两个子树都是一颗平衡二叉树

7.4、B树

B树也叫做多路平和查找树，他的高度远远小于平衡二叉树的高度,每个节点可以包括M个子节点，M称为B树二点阶，每块磁盘中包括关键字和子节点的指针，如果一个磁盘中包含了x个关键字，那末指针的数量就是x+1

• B树在插入和删除节点的时候如果导致树不平衡，就会通过自动调节节点位置来保持树的自平衡
• 关键字集合分布在整个树种，叶子节点和非叶子节点都可以存放数据，搜索有可能在非叶子节点结束
• 搜索的性能，等于在关键字全集内做一次二分查找
• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UnRb6VFJ-1661326196166)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20220225115214245.png)]

8、B+树和B树的异同:

1. 有k个孩子的节点就有k个关键字，孩子的数量等于关键字的数量，b树中孩子的数量=关键字+1
2. 非叶子节点的关键字也会同时存在于子节点中，并且在子节点中所有关键字最大或者最小
3. 非叶子节点仅用于索引，不保存数据结构，跟记录有关的信息都存放在叶子节点中，而b树中，非叶子节点即保存索引（关键字），也保存数据记录
4. 在所有关键字在叶子节点中出现，叶子节点构成一个有序的链表，而非叶子节点按照关键字的大小从小到大的顺序进行链接
5. B+树的查询效率更稳定更高，B+树每次只有访问到叶子节点才能找到对应的数据，而b树中，非叶子节点也会存储数据，造成查询效率不稳定的情况，有时候访问到非叶子节点就可以找到数据，有时候需要访问到叶子节点才能访问到数据,B+树比B树更矮更胖，阶数大，深度更低，查询所需要的Io次数也会更小，同样的磁盘大小，B+树可以存储更多的关键字，在查询范围的效率上，B+树也是比较高的，因为所有的关键字都是出现在B+树的叶子节点上，叶子节点之间会有指针，数据也是递增的，因此的话范围查找可以通过指针进行连接查找
6. 使用索引的目的是为了帮助我们从海量的数据中快速的定位要查找的数据，不过也存在着一些不足，比如占用的存储空间，降低数据库写操作的性能，如果有多个索引还会增加索引的选择的时间，当我们使用索引的时候需要平衡索引的利弊

9、InnoDB数据存储的结构

mysql中以页作为磁盘和内存之间交互的基本单位

索引的创建与设计原则

1.索引的声明和使用

mysql中索引包括普通索引，唯一索引，全文索引，单列索引，多列索引，以及空间索引，从物理的实现方式来说，索引分为两种，聚簇索引和非聚簇索引

1. 普通索引
   1. 在创建普通索引的时候，不附加任何限制条件，只是用于提高查询的效率
2. 唯一索引
   1. 使用UNIQUE可以设置索引为唯一索引，一个表中可以有多个唯一索引
3. 主键索引
   1. 主键索引是特殊的索引，一个表中只能有一个主键索引
4. 单列索引
   1. 在表中单个字段上创建索引，单个索引只根据该字段进行索引，一个表中可以有多个单列索引
5. 多列索引
   1. 多列索引是在表的多个字段上创建的一个索引，该索引指向创建时的多个对应的字段，通过这几个字段进行查询，只有查询的条件中使用了这些字段的第一个字段的时候才会被使用，尽量满足最左匹配规则
6. 全文索引
   1. 全文检索，一般使用es

# 创建索引
# 1.创建表的时候添加索引
/*
1.1.隐式的创建索引，在声明有主键约束，唯一性约束，外键约束的时候会自动的创建索引
*/
create table dept(
# 主键索引，主键自动创建索引
dept_id  int primary key auto_increment,
dept_name varchar(10)

);
#查看表中的索引
show index from dept;
#创建非主键索引
create table emp(
emp_id int primary key auto_increment,
#unique关键字会自动创建索引
emp_name varchar(15) unique,
dept_id int,
#外键约束也是索引
constraint emp_dept_id_fk foreign key(dept_id) references dept(dept_id)
);
show index from emp;
/*
1.2.显式的创建索引
语法格式：
create table table_name [col_name data_type]
[unique|fulltext|spatial] [index|key] [index_name] (col_name[length]) [ASC|DSC]
   UNIQUE FILLTEXT SPATIAL 为可选参数，表示唯一索引，全文索引，空间索引
   INDEX,KEY 两者的含义相同，用来指定创建索引
   index_name 指定创建索引的名称，为可选参数，如果不指定创建索引的名字，默认为列名
   col_name为要创建索引的字段列，该列必须从数据表中定义多个列中选择
   length 表示索引的长度，只有字符串类型的字段才能指定索引的长度
   ASC或DESC指定升序或者降序的索引值进行存储
*/
create table book(
book_id int,
book_name varchar(100),
info varchar(100),
COMMENT varchar(100),
year_publication year,
#声明普通的索引
index idx_bname(book_name)
);
show index from book;
# explain关键字，性能分析工具，
explain select * from book where book_name = 'ada';
# 创建唯一索引,声明唯一索引的字段在添加数据的时候，要保证数据的唯一性
create table book1(
book_id int,
book_name varchar(100),
info varchar(100),
COMMENT varchar(100),
year_publication year,
#声明普通的索引
unique index comment_index(COMMENT)
);
show index from book1;
 
# 主键索引的话是通过在主键约束的方式定义主键索引
create table book3(
# 声明成 auto_increment的字段必须是 unique 或者是主键字段
book_id int primary key auto_increment,
book_name varchar(100)  ,
info varchar(100),
COMMENT varchar(100),
year_publication year
);

#删除主键索引，通过删除主键约束的方式删除主键索引
alter table book2
drop primary key;

# 创建联合索引
create table book4(
# 声明成 auto_increment的字段必须是 unique 或者是主键字段
book_id int primary key auto_increment,
book_name varchar(100),
info varchar(100),
COMMENT varchar(100),
year_publication year,
index id_name (book_id,book_name,info)
);
show index from book4;
/*
表已经创建好的情况下添加索引

alter table .... add ....

create index .... on ... 
*/
alter table book4 add index idx_cmt(COMMENT);
#最左匹配原则
alter table book4 add index mul_bid_bname_info(book_id,book_name,info);


/*
删除索引,添加 auto_increment约束字段的唯一索引不能进行删除
删除表中的列的时候，如果要删除的列为索引的组成部分，则该列也会从索引中进行删除，如果alter
组成索引的所有列都被删除点的话，则整个索引都会被删除掉
alter table ... drop index ...
drop index ...  on table_name
*/
# 第一种方式删除
alter table book5
drop index idx_bk_id;
#第二种方式删除
drop index uk_idx_bname on book5;
#当将联合索引的某个字段进行删除的时候，联合索引就会重新进行重排



/*
从mysql8.0开始支持隐藏索引，只需要将待删除的索引设置为隐藏的索引，使得查询
的优化器不再使用该索引，确认将索引设置称为隐藏索引的后，不会收到任何的响应就可以
彻底的删除掉索引，通过将索引设置成隐藏索引，然后删除索引的方式称为软删除
要注意的是主键不能设置成为隐藏索引，非空索引也不能设置称为软索引
在创建index或者alter table 时可以通过使用visible或者INVISIBLE关键词设置索引的可见性

*/
# 创建的时候添加上隐藏索引
create table book3(
# 声明成 auto_increment的字段必须是 unique 或者是主键字段
book_id int ,
book_name varchar(100)  ,
info varchar(100),
COMMENT varchar(100),
year_publication year,
# 创建不可见索引
index idx_cmt(COMMENT) invisible
);
show index from book3;
# 创建表之后
alter table book3
add unique uk_idx_bname(book_name) invisible;

#修改索引的可见性,创建表的时候是索引是可用的，修改表的时候是不可见的
alter table book7 alter index idx_year_pub invisible;#可见->不可见
alter table book7 alter index idx_year_pub visible;#不可见->可见

适合添加索引的情况

1. 业务上就具有唯一特性的字段，即使是组合字段，也必须建成唯一索引

2. 频繁作为where查询条件的字段

3. 经常group by 和order by的字段

4. distinct字段需要创建索引

5. 多表join连接操作的时候，创建索引的注意事项

   1. 连接表的数量尽量不要超过三张，每增加一张表就相当于增加一层嵌套循环
   2. 对where条件创建索引
   3. 对于连接字段的创建索引，该字段在多张表中的类型必须一直

6. 使用列类型小的创建索引

   1. 数据类型越小，在查询的时候进行比较操作越快
   2. 数据类型越小，索引占用的存储空间就越小，在一个数据页中就可以放下更多的记录，从而减少磁盘的I/O带来的性能的损耗，也就可以将更多的数据页缓存在内存中，从而加快读写效率

7. 使用字符串前缀创建索引

   1. create table shop(address(120) not null)
      #该字段的长度比较长，因此的话，可以将该字段进行截取，取前12个字符进行创建索引
      alter table shop add index(address(12));
      

   2. 在varchar字段上建立索引的时候必须指定索引的长度，没必要对全字段建立索引，一般字符串类型的数据，区分度高达90%，可以使用count(distince left(列名，索引长度))/count(*)来计算，当计算出来的区分度越接近1的时候，就表示区分度是越高的

8. 将区分度高的列作为索引

   1. 列的基数越小说明该列就越集中，列的基数越大，说明该列就越分散

9. 将最频繁使用的列放到索引的左边，最左前缀原则

   1. where条件中哪个字段放到了最前面的时候，我们就可以将该字段当作联合索引的最前面

10. 多个字段都要创建索引的时候，联合索引优于单个的索引

    1. 在创建索引的时候我们要注意好平衡，索引的数目不是越多越好，我们需要先治好每张表中索引的数量不超过6个
       1. 每个索引在创建的时候都需要占用磁盘的空间，索引越多，需要的磁盘空间就越大
       2. 索引加快的是查询的速度，对于插入，删除，修改等操作，表中数据量过大的时候，索引进行更新，会消耗大量的时间
       3. 优化器在选择如何优化查询的时候，会根据统一的消息，对每个可以使用到的索引进行评估，生成最好的执行计划，如果有多个索引用于查寻的时候，会增加mysql优化器执行计划的时间，降低查询的性能

哪些情况下不适合创建索引

1. where 中不使用不到的字段，不要设置索引
2. 数据量小的表最好不要使用到索引，数据量低于1000行的时候，是不需要进行创建索引的
3. 有大量重复数据的列上创建索引
4. 避免对经常更新的表创建索引
5. 不建议使用无序的值作为索引
6. 删除使用很少的索引
7. 不要定义冗余或者重复的索引

性能分析工具的使用

​ 在数据库的调优中，我们的目标是响应的时间更快，吞吐量更大，利用宏观的监测工具和微观的日志分析，可以帮助我们快速的找到调优的思路和方式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-16zpOiwY-1661326196166)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20220319180008344.png)]

1.查看系统性能参数

1. 在mysql中，可以使用SHOW STATUS语句查询一些mysql数据库服务器性能参数，执行的频率

show [global|session] status like '参数'

一些常用的性能的参数

• Connections: 连接mysql服务器的次数
• uptime: mysql服务器上线的时间
• slow_queries：慢查询的次数,默认的是10秒，可以修改监测的阈值
• Innodb_rows_read: select 查询返回的行数
• innodb_rows_inserted: 执行insert插入操作的行数
• innodb_rows_updated: 执行update 操作更新的行数
• com_select:查询操作的次数
• com_insert: 插入操作的次数，对于批量插入的Insert，只累加一次
• com_update:更新操作的次数
• com_delete：删除操作的次数

2. 查看上次查询的成本，主要是看取到的数据页的大小

show status like 'last_query_cost';

• sql查询是一个动态的过程，从页的加载的角度来看，我们可以得到以下的结论：
  • 位置决定效率，如果页就在数据库的缓冲池当中，那么效率是最高的，否则还需要从内存中或者磁盘中进行读取，对于单个的页读取来说，在内存中读取的速度会比在磁盘中读取的效率更高效
  • 批量决定效率，我们从磁盘中对单个的页进行读取的话，效率是很低的，如果采用顺序读取的话，批量对页进行读取，平均读取的效率就会提升很多，比单页在内存中读取的效率还要高
  • 所以，我们要首先考虑数据存放的位置，如果是经常使用的数据尽量要放在缓冲池中，充分的利用磁盘的吞吐能力，批量的读取数据，这样单个的页读取的效率也就很高了

2.定位执行慢的sql,慢查询日志

• mysql的慢查询日志，用来记录在mysql中响应时间超过阈值的语句，具体的·话指的是运行时间超过long_query_time值得sql，就会被记录到慢查询得日志中，long_query_Time得默认值是10s,就是运行时间超过了10秒得sql语句就会被记录下来
• 他的主要作用是帮助我们发现执行时间特别长得sql查询，并且有针对性得进行优化，从而提高系统得效率，当数据库服务发生阻塞，运行变慢得时候，我们可以进行慢日志得查询，对解决问题很有帮助，如果一条sql查询得时间超过5s,我们就将其标记成为慢sql,可以结合explain进行全面的分析
• 一般情况下mysql数据库没有开启查询慢日志,需要我们手动的进行设置这个参数，如果不是调优的话，一般情况下不会启动该参数，因为慢日志查询的开启会对性能产生很大的影响
• 慢日志支持将日志记录写入到文件中去

分析查询语句EXPLAIN

定位了慢查询的sql之后，我们就可以使用explain或者describe工具做针对性的分析查询语句，DESCRIBE语句的使用方法和explain语句是一样的，并且分析的结果也是一样的

explain select * from dept;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tCtlU1Wr-1661326196167)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20220320171557280.png)]

列名	描述
id	在一个大的查询语句中每个select关键字都对应一个唯一的id
select_type	select关键字对应的查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方式
key	实际上是用到的索引
key_len	实际上是用到索引的长度
ref	当使用索引列等值查询的时候，与索引列进行等值匹配的对象信息
row	预估的需要读取的记录的条数
filtered	某个表经过搜索条件过滤之后剩余的记录条数的百分比
Extra	一些额外的信息,表的信息

• id对应的select关键字出现的次数,自连接查询，优化器会将其改为多表连接查询

• select_Type,查询语句中不包含‘union或者是子查询的查询’，都算是simple类型，对于包含‘union’或者是‘union all’的大查询来说,最左变得select_type叫做‘primary’，其余的叫做union

• type标识对某个表执行查询的时的访问方法，又叫做访问类型，其中的type就表明了这个访问的方法是啥，是比较重要的一个指标，看到type的值为ref,表明mysql中将使用到ref来进行查询 system->const->eq_ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->all,sql性能优化的目标，至少要达到range级别，最好是range级别

• possible_keys: 标识可能使用到的索引，key实际使用到的索引

• ref: 当使用索引列等值查询的时候，与索引列等值匹配的对象的信息

• rows:预估要读取的记录条数，越少表示越精准

• filtered：经过搜索条件过滤后剩余的记录条数所占的百分比

1. Explain不考虑各种cache
2. Explain不能显示mysql再执行查询时所做的优化工作
3. Explain不会高所你关于触发器，存储过程的信息或者用户自定义的信息

trace的使用

它可以跟踪优化器做出各种决策，并将跟踪结果记录到INFORMATION_SHEMA.OPTIMIZER_TRACE表中，默认关闭，

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=10000;

索引失效的案列

mysql中提高性能的一个有效的方式是对数据库表进行设计合理的索引，索引提供了高效的访问数据的方法，加快索引对查询的速度有重要的影响

• 使用索引可以快速的定位表中的某条记录，提高数据库查询的速度，提高数据库的性能
• 如果查询的时候没有使用到索引，查询的语句就会进行扫描表中的所有的记录，查询的效率就会很慢

大多数情况下都默认使用B+树来构架索引，空间类型的索引使用R树，MEMORY表支持hash索引

是否使用索引是优化器说了算，优化器是基于cost开销的，不是基于规则的，也不是基于语义的，怎么开销小就怎么来，sql语句是否使用索引，跟数据库的版本，数据量，数据的选择度都有关系

1.全值匹配

当使用where去构建多个查询的条件的时候，使用联合索引的查询的效率要比单个的索引查询的效率要更快

2.最左前缀匹配

在使用where去构造多个查询条件的时候，我们应当将添加索引的字段的查询条件放到最左边，mysql可以为多个字段创建索引，一个索引可以包括16个字段，对于多列索引，过滤条件要使用到索引，就必须要按照索引建立的顺序，依次的进行满足，一旦跳过某个字段，索引后面的字段都无法被使用到。如果查询条件中没有使用这些字段中的一个字段的时候，多列索引就不会被使用,索引文件具有B-Tree的最左匹配原则的特性，如果左边的值还未确定，那就无法使用到该索引。

3.主键插入的顺序

对于使用innoDB的存储引擎的表来说，在我们没有显示的创建索引的时候，表中的数据实际上都是存储在聚簇索引的叶子节点的，而记录又是存储在数据页中，数据页和记录又是按照记录主键值从小到大的顺序进行排序，我们插入的记录是主键值依次增大,因此的话我们在创建的时候最好让插入的记录的主键值依次的递增。

4.计算，函数类型转化，导致索引失效

在查询条件中添加函数，更改的话很大程度上会导致索引失效

5.类型转换索引失效

如字符串“123” 和int的转化123

6.范围条件右边的列索引失效

where构造多个查询条件的时候，在范围的右边索引会失效，一般情况下，会将范围条件放在最后

比如，<,<=,>=在实际开发中，金额查询，日期查询往往都是范围查询，应当将查询的条件放置到where的后面(构造索引的时候通常将用于范围比较的字段放到最后面);

7.不等于(!=或者<>)索引失效

where 构造查询条件的时候，!= or <>会导致索引失效不走索引

8.is null 可以使用到索引，is not null不能使用索引

做好在设计数据库表的时候将字段设计成为NOT NULL ，not like 也无法使用到索引，导致全盘扫描

9.like以通配符%开头的索引失效

在like进行模糊查询的时候，如果第一个字符为’%'的开头的时候，索引就会失效，就是不走索引，直接进行全盘扫描，页面搜索严禁左模糊或者全模糊，如果需要就走搜索引擎来解决

10.or 前后存在非索引的列，索引就会失效

在where子句中，如果or前的条件列进行了索引，而在or后的条件没有进行索引，那么索引就会失效，or前后的两个条件都是索引的时候，查询的时候才使用到了索引

11.数据库和表的字符集统一使用utf8mb4

统一使用utf-8mb4兼容性更好，统一字符集可以避免由于字符集转化产生的乱码，不同的字符集进行比较的时候会进行转化造成索引失效

• 对于单列索引，尽量选择针对当前query过滤性比较好的索引
• 在选择组合索引的时候，当前query过滤性最好的字段在索引字段的顺序中，位置越靠前越好
• 在选择组合索引的时候，尽量的选择能够包含当前query中的where子句中更多字段的索引
• 在选择组合索引的时候，如果某个字段可能出现范围查询的时候，尽量的把这个字段放在索引次序的最后面

关联连接的查询的优化

1. 左外连接
2. 内连接

• 对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现
• 如果连接条件中只有一个字段有索引，那么有索引字段所在的表一定是会被作为被驱动表
• 对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择数据量小的表作为驱动表，数据量大的表作为被驱动表

1.驱动表和被驱动表

驱动表就是主表，被驱动表是从表，从explain的结果来看，上面的是驱动表，下面的是被驱动表

• join小的结果集来驱动大的结果集
• 为驱动表匹配条件增加索引
• 增大join buffer size的大小
• 减少驱动表不必要的查询

子查询的优化

使用子查询可以进行select语句的嵌套查询，即一个select查询的结果作为另一个select语句的条件，但是子查询的执行的效率并不高 原因如下：

1. 执行完子查询之后，mysql会为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中进行查询记录，查询记录完毕之后，然后再撤销这些临时表，会消耗大量的cpu和IO资源，产生大量的慢查询
2. 子查询的结果集存储的临时表，无论是内存临时表还是磁盘临时表，都不会存在索引，因此，查询的性能会受到一定的影响
3. 对于返回结果集比较大的子查询，其对查询的性能的影响页比较大
4. 在mysql中，可以使用连接查询来代替子查询，连接查询不需要建立临时表，其比子查询速度要快，如果使用索引的话，性能会更好
5. 尽量不要使用not in或者not exists,

排序优化

1. 在where条件上添加上索引，还可以在order by上面添加上索引，在mysql中支持两种排序方式，分别是fileSort和Index排序
   • index排序中，索引可以保证数据的有序性，不需要进行排序，效率会更高
   • FileSort排序一般需要在内存中进行排序，占用的cpu会更多，如果排序的结果较大，会产生临时的文件I/O到磁盘中进行排序的情况，效率比较低下
2. sql中，可以在where子句和order by中使用到索引，where中使用索引是为了避免出现全表扫描，在order by中使用索引是为了避免使用FileSort进行排序，因为Filsor使用的是内存进行排序，占用的内存的空间比较高，因此的话，当数据量比较大的时候，排序的效率就会比较的低
3. 如果where和order by中使用的索引是同一个的话，就使用单列索引，如果使用到的不是同一列就使用联合索引

1.排序索引失效的问题

#创建索引
create index id_age_index on student (age,classid,name);
#对查询的字段没有限制，导致排序的时候索引会失效，不走索引进行排序（走完索引，还是要进行回表进行操作查询其他的字段，因此的优化器的话会选择不走索引）
select * from student order by age,classid;
#因为查询的时候添加了limit进行限制作用，数据量比较小，因此的优化器会选择走索引
select * from student order by age,classid limit 10;

order by 时，顺序错，不索引，方向反，不索引（比如按照字段desc降序排列）

order by的时候尽量不要使用select * ，最好只选用需要query的字段，决定filesort使用单路算法还是双路算法的参数时 ‘%max_length_for_sort_data%’;

show variables like  '%max_length_for_sort_data%'; #默认时1024个字节

group by的优化

• group by 使用索引的原则和order by 一致，group by 即使没有过滤条件使用到索引，也可以直接使用索引
• group by 先排序然后再分组，遵循最左匹配原则
• 当无法使用索引列的时候，增大max_length_for_sort_data 和 sort_buffer_size参数设置
• where的效率要高于having,能写在where限定条件就不要写在having中
• 减少使用order by,能不排序就不排序，order by, group by,distinct这些语句比较耗费cpu
• 包含了order by,group by,distinct的这些查询语句，where 条件过滤出来的结果集尽量保持在1000行之内，否则sql就会很慢

优化分页查询

一般情况下分页查询的时候，通过创建覆盖索引，会比较好的提高性能，当limit 200000,10的时候，查询的代价就是十分高的

#第一种优化方案
select * from student where id>2000000 limit 10;
#第二种优化方案
select * from student s (select * from student order by id limit 200000,10) a where a.id = s.id;

覆盖索引

一个索引包含了满足查询结果的数据就叫做覆盖索引，覆盖索引简单来说就是 索引列+主键包含到select到from之间查询的列，优化器会基于成本进行考虑是否使用索引，<>的话不是绝对的不使用索引，而是在一定的情况下会造成索引失效。

覆盖索引的好处：

1. 避免innodb进行索引的二次查询(回表操作)

   innodb是以聚集索引的顺序来存储的，对于innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果使用二级索引查询数据，在找到相应的键值之后，还需要通过主键进行二次查询才能获取到我们所真实需要的数据，在覆盖索引中，二级索引的简直中可以获取到所要的数据，避免了对主键的二次查询，减少了io操作，提升了查询的效率

2. 可以把随机IO变成顺序IO加快查询的效率

   由于覆盖索引是按照键值顺序进行存储的，对于io密集的范围查找来说，对比随机从磁盘中读取每一行的数据IO要少的多，因此利用覆盖索引在访问的时候可以把磁盘的随机读取的IO转变成索引查找的顺序io

3. 覆盖索引可以减少树的搜素次数，显著的提升查询的性能，所以覆盖索引是一个常用的性能优化手段

索引下推

原理：

索引下推是一种在存储引擎层使用索引过滤数据优化的方式,索引下推针对的更多的是联合索引，索引下推可以减少回表的次数，在我们的索引中有这个字段，然而不能使用到该索引，这个时候就可以使用索引下推，icp使用的条件必须要有回表操作，覆盖索引不能回表操作

#打开索引下推优化服务
set optimizer_switch = 'index_condition_pushdown=on';

索引下推使用的条件

1. 如果访问的类型为range,ref,eq_ref和ref_or_null的时候就可以使用
2. icp可以用于Innodb和Myisam表
3. 对于InnoDB，索引下推仅用于二级索引
4. 当sql使用覆盖索引的时候，不支持索引下推，

淘宝数据库主键如何设计

1.自增id的问题

1. 可靠性不高

   存在自增id回溯得到问题

2. 安全性不高：

   对外暴露接口可以非常容易的猜测对应的信息，也很容易通过接口对数据进行爬取

3. 性能比较差

   自增id的性能比较差，需要在服务器端自己生成

4. 交互多

   业务需要额外执行一次类似的 last_insert_id的函数才能知道插入的自增值，

5. 局部唯一性

   自增id是局部唯一的，只在数据库实例中唯一，而不是全局唯一的，在任意服务器之间都是唯一的

在实际开发中，尽量不要使用跟业务相关的字段做主键，毕竟作为项目设计的技术人员，我们无法预测在项目的生命周期中，哪个业务字段会因为项目的业务需求有重复，或者重用之类的情况出现，推荐使用UUID全局唯一的实现

订单id=时间段+去重字段+用户id的后六位尾号

推荐的主键设计

非核心业务：对应表的主键自增id,如警告日志，监控等信息

核心任务：主键设计至少应该是全局唯一并且是单调递增的，全局唯一保证在各个系统之间都是唯一的，单调递增是希望插入的时候不影响数据库的性能

推荐使用UUID

特点：全局唯一，占用36个字节，数据无序，插入性能差

• uuid的组成：

  UUID = 时间 + UUID版本(16) - 时钟序列(四个字节) - MAC地址(12个字节)

1. 为什么UUID是全局唯一的：

   在UUID中时间部分占用60位，存储类似TIMESTAMP得时间戳，时钟序列是为了避免时钟被回拨导致得时间重复得可能性，MAC地址用于全局唯一

2. 为什么UUID占用36个字节:

   UUID根据字符串进行存储，设计得时候带有"-"相连接，因此得话需要36个字节

3. 为什么说UUID是无序得

   因为UUID设计中，将时间得最低位放在最前面，而这部分数据也是在不断得变化的，并且是无序的

mysql中的uuid改造

• mysql8.0中将时间的低位和时间的高位进行更换，这样的话就是有序的UUID了
• mysql8.0还解决了UUID存在的空间占用问题，除了UUID字符串无意义的“-”，并且将字符串转换成二进制的类型进行保存，降低了存储的空间位16个字节
• 可以通过mysql8.0提供的uuid_to_bin函数实现上面的功能，

#设置一个全局唯一的变量
set @uuid = UUID();
# uuid_to_bin(@uuid,TRUE),该函数将uuid转化成全局唯一单调递增的的有序的uuid
select @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

范式

1、范式的介绍

​ 在关系型数据库中关于数据表设计的基本原则，规则就叫做范式，一张数据表的结构需要满足某种设计标准级别，要想满足一个结构合理的关系型数据库，必须要满足一定的范式

2、范式包括哪些

​ 目前关系型数据库有六种常见的范式，按照范式的级别，从低到高分别是：第一范式，第二范式，第三范式，巴斯-科德范式，第四范式，第五范式，数据库的设计越高阶，用于度就会月底，高阶的范式一定满足符合低阶的要求，满足要求的范式是第一范式，在此基础之上，依次的类推，

3、键和相关的属性的概念

​ 范式的定义会使用到主键和候选键，数据库中的键由一个或者是多个属性组成，数据库中常见的几种键和属性的定义：

• 超键：能唯一标识元组的属性集的叫做超键
• 候选键：如果超键不包括多余的属性，那么这个超键就是候选键
• 主键： 用户可以从候选键中选择一个作为主键
• 外键：如果数据表r1中的某个属性集不是r1的主键，而是另一个数据表r2的主键，那么这个属性集就叫做数据表R1的外键
• 主属性：包含在任一候选键中的属性就成为主属性
• 非主属性：与主属性相对

通常情况下，我们可以将候选键称为码，把主键称为主码，因为键可能是由多个属性组成的，针对单个属性我们还可以使用主属性和非主属性进行区分

4、第一范式

​ 第一范式主要是确保数据表中的每个字段的值必须具有原子性，每个字段都是不可差分的最小单位

5、第二范式

第二范式要求在满足第一范式的基础之上，还要满足数据库表中的每一条数据记录是唯一的标识，并且所有的非主键字段都必须要完全依赖主键，不能只是依赖主键的一部分，如果不完全依赖，那么这个属性和主关键字这一部分应该分离出来形成一个新的实体，新的实体和实体之间是一对多的关系

6、第三范式

​ 第三范式在第二范式的基础之上，确保数据库表中的每一个非主键字段和主键字段直接相关，要求数据库表中的所有非主键字段不能依赖于其他的非主键字段

• 范式的优点：
  • 降低了数据的冗余，扩展性和数据完整性达到了平衡
• 范式的缺点：
  • 范式的使用会降低查询的效率，范式的等级越高，设计出来的表的就越详细，数据的冗余度就越低，在进行查询的时候就需要关联多张表，可能会出现索引失效的问题
  • 在开发中，我们为了性能和读取的效率违背了范式化，增加少量的冗余，或者重复的数据提高数据库的读取效率，减少关联查询，join表的次数，用空间换取时间

7、反范式化

​ 有时候不能简单的按照规范进行设计数据表，有些数据看起来很冗余，但是对业务来说十分重要，我们要在遵循业务优先的原则之上，首先满足业务需求，然后尽量的减少冗余

规范化vs性能

1. 为了满足某种商业目标，数据库性能比规范化数据库要更重要，
2. 在规范化数据库的同时，要综合考虑数据库的吸能
3. 在通过给指定表添加额外的字段，以大量减少从搜索信心所需要的时间
4. 在给定表中插入计算列，以方便查询

反范式化应用的场景：

1. 当冗余信息有价值，或者可以大幅度提高查询的效率的时候，我们可以使用反范式化进行优化处理
2. 增加冗余字段一定要符合下面的两个条件
   1. 冗余字段不需要进行经常修改
   2. 冗余字段查询的时候不可或缺
3. 历史快照，历史数据需要

ER模型

​ 数据库的设计是牵一发而动全身的，ER模型也叫做实体关系模型，用来描述现实生活中客观存在的事物，事物的属性，以及事务之间的关系的一种数据模型

ER模型中包括哪些要素？

• 实体，可以看作是数据对象，对于现实生活中真实存在的个体，在ER模型中，使用矩形来表示
• 属性，指的是实体的特性，在ER模型中使用椭圆形来表示
• 关系，指的是实体之间的关联，在ER模型中使用菱形来表示。

实体和属性不容易区分，可以独立存在的是实体，不可再分的是属性

关系类型

​ 在ER模型中，关系又可以分为三种类型，分别是一对一，一对多，多对多

• 一对一，指实体之间的关系是一一对应的
• 一对多：指实体通过关系，可以对应多个另一边的实体
• 多对多：指关系两边的实体多可以通过关系对应多个对方的实体，比如供货商和超市之间就是多对多的关系

ER模型图转化成数据表

1. 一个实体通常转化成一个数据库表
2. 一个多对多的关系，通常也转化成一个数据库表
3. 一个一对一或者一对多的关系，往往通过表的外键来表达，而不是设计一个新的数据表
4. 属性转换成表的字段

数据库表的设计原则

1. 数据库的表的个数越少越好
2. 数据库表中的字段个数越少越好
3. 数据表中联合主键的字段个数越少越好(联合主键越多，占用的索引空间就越大)
4. 使用主键和外键越多越好

数据库对象编写的建议

1、关于库的编写

1. 库的名字必须要控制在32个字符以内，只能用应为字母、数字和下划线，建议用英文字母开头
2. 库名中英文一律小写，不同的单词采用下划线分割，需要见名知意
3. 库的命名格式 业务系统名称_子系统名
4. 库名禁止使用关键字(type,order)
5. 创建数据库的时候必须指定字符集，并且字符集只能是utf-8和utf8m64
6. 对于程序连接数据库账号，遵循权限最小原则(防止删库跑路)
7. 临时库使用tmp_为前缀，并以日期为后缀，备份库以bak_为前缀，并以日期为后缀

2、关于表和列

1. 表和列的名称必须要控制在32个字符以内，表名只能使用英文字母，数字和下划线，建议使用英文字母开头

2. 表名和列名一律小写，不同单词采用下划线分割，需要见名知道意思

3. 表名要求和模块名强相关，同一个模块的表名尽量使用统一前缀

4. 创建表的时候必须指定显式的字符集为utf-8或者utf8m64

5. 表名和列明禁止使用关键字

6. 创建表的时候必须显式的指定表的存储引擎，无特殊的要求一律使用innodb

7. 创建表的时候必须要有comment，就是对字段的内容的描述

8. 字段的命名尽量使用表达实际含义的英文单词或者是缩写

9. 布尔类型的字段的命名为is_描述，is_enabled

10. 禁止在数据库中存储图片，文件等大的二进制数据，文件很大的时候会产生大量的随机的io操作

11. 建表的时候必须要有主键

    • 强制的要求主键为id,类型为int或者是bigint,为auto_increment自增长 unsigned无符号类型
    • 标识表里每一行的主体字段不要设置为主键，建议设置其他的字段如user_id,order_id等，并且建立unique key索引，因为如果设置为主键并且主键值为随机插入，则会导致innodb内部页分裂和大量的随机的io，导致性能下降

12. 建议核心表如用户表，必须要有行数据的创建时间字段(create-time)和最后更新时间字段，便于查找问题

13. 表中所有字段尽量都是not null的属性，业务可以根据需要定义为default值，null值存在每一行都会占用额外的存储空间，数据迁移容易出错

14. 所有的存储相同的数据的列名和列类型必须一致，(关联列中，如果查询时关联列类型不一致自动的进行数据类型的隐式的转化，会造成索引的失效，导致查询的效率降低)

15. 建议使用中间表或者临时表时，表名使用tmp_开头

    • 本份表的时候以bak_开头，中间表和备份表定时清理

关于索引

1. innodb表主键必须为 id int/bigint auto_increment,并且主键值禁止被更新
2. innodb和mylsqm存储的引擎表，索引类型必须为btree
3. 主键的名称以pk_开头，唯一键以uni_或者uk_开头,普通索引以idx_开头，一律使用小写格式，以字段的名称或者缩写作为后缀
4. 多单词组成的列名，取前几个单词的首字母，加上末单词组成column_name,如sample表 member_id上的索引 idx_sample_mid
5. 建议单个表上的索引的个数不能超过6个
6. 在建立索引的时候，多考虑建立联合索引，把区分度最高的字段放在前面
7. 在多表join的sql中，保证被驱动表的连接列上有索引，这样join的执行效率最高
8. 建表或者加索引的时候，保证表里互相不存在冗余索引

关于sql编写

1. 程序端select语句必须指定具体字段的名称，禁止写成*
2. 程序端insert语句指定具体字段的名称，不要写成insert into t1 values(…)。
3. 除了静态表或者小表，dml语句必须要有where条件并且使用索引进行查找
4. insert into …values(xx)…,批量操作的值不要超过500个，值过多虽然上线很快，但是会引起主从同步延迟
5. select 语句不要使用union,推荐使用union all， 并且每个union子句个数限制在5个以内
6. 线上环境，多表join不要超过5个表
7. 减少使用order by 能不排序就不排序，或者将排序放到程序端去做，order by, group by, distinct 这些语句比较耗费cpu,数据库的cpu资源十分的宝贵
8. 包含order by, group by,distinct这些查询语句，where 条件过滤出来的结果集请保持在1000行以内，否则sql就会很慢
9. 对单表的多次alter操作必须合并为一次，对超过100w行的大表进行alter table，必须要经过审核，并且在业务低峰期的时候进行执行，多个alter需要整合在一起，因为alter table会产生表锁，期间阻塞对于该表的所有写入，对业务会产生极大的影响
10. 批量操作数据的时候，需要控制事务处理的间隔时间，进行必要的sleep
11. 是物理包含的sql不超过5个,过长的事务会导致锁数据会比较久，mysql内部缓存，连接消耗过多的问题
12. 事务中更新的语句尽量基于主键或者unique key,否则会产生间隙锁，内部扩大锁定的范围，导致系统性能下降，产生死锁

第十二章_数据库的其他调优

1、数据库的调优的措施

1.1、调优的目标

• 尽可能的节省系统的资源，以便系统可以提升更大负荷的服务
• 合理的结构设计和参数调整，提高用户操作响应的速度
• 减少系统的瓶颈，提
• 数据库的整体性能

第十三章_事务的基础知识

1、数据库事务的概述

​ 事务时数据库区别于文件系统的重要特性之一，当我们有了事务，就会让数据库保持一直性，同时我们还可以通过事务的机制恢复到某个时间节点，保证已经提交到数据库的修改不会因为系统的崩溃而丢失

​ 事务，一组逻辑操作单元，使数据从一种状态转变为另一种状态

​ 事务的处理原则： 保证事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式，在一个事务中执行多个操作的时候，要么所有的事务都被提交，然后被永久的进行保存，要么就放弃所有的修改，整个事务回滚到最初的状态

2、查看存储引擎的支持的情况

SHOW ENGINES 查看当前mysql支持的存储引擎有哪些，以及这些存储引擎是否支持事务

3、事务的ACID特性

• 原子性

  原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚，不存在中间的特性

• 一致性

  转账前后，钱的总额时不会发生变化的,从一个合法的状态转化成另一个合法的状态

• 隔离性：

  一个事务的执行不会被其他的事务进行干扰，即同一个事务内部操作和使用数据对并发的其他的事务时隔离的，并发执行的各个事务之间不能互相的干扰

• 持久性：

  可以将数据持久化到硬盘中

4、数据并发的问题

1. 脏写：

   对于两个sessionA,sessionB,如果sessionA修改了另一个未提交事务sessionB修改过的数据，那就意味着发生了脏写

2. 脏读：

   对于两个sessionA,sessionB,如果sessionA读取到了另一个sessionB更新还没有提交的字段，那就意味着发生了脏读

3. 不可重复读：

   对于两个sessionA,sessionB,如果sessionA读取了一个字段，sessionB更新了该字段，然后再次读取同一个字段，值发生了变化，那就意味着发生了不可重复读

4. 幻读：

   对于两个sessionA,sessionB,如果sessionA读取了一个字段，sessionB插入了一些新的字段，然后再次读取这个表，就会多出几行，那就意味着发生了幻读

4.1、SQL的四种隔离的级别

• 读未提交，在该隔离级别，所有的事务都可以看到其他未提交事务的执行的结果，不能避免脏读，不可重复读，幻读
• 读已提交：它满足了隔离的简单的定义，一个事务只能看到已经提交事务所作出的改变，可以避免脏读，不可重复读，幻读现象仍然存在
• 可重复读： 事务A在读到一条数据之后，此时事务B对该数据进行了修改并且提交，那么事务A再去读取该数据，读到的还是原来的内容，可以避免脏读，不可重复读，幻读仍存在，这是mysql默认的隔离级别
• 可串行化：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他的事务对该表执行插入删除和更新操作，所有的并发问题都可以解决，但是性能十分的低下

查看隔离级别

show variables like 'transaction_isolation';
show @@transaction_isolation;

修改事务的隔离级别：

set [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
#隔离级别的格式
READ-UNCOMMITTED
READ-COMMITTED

• 如果加的关键字是global关键字(在全局范围内进行影响)

  • 当前已经存在的会话无效
  • 只对执行完该语句之后的会话起到作用

• 如果使用的是session关键字(在会话范围内影响)

  • 只对当前会话的所有后续的事务有效
  • 如果在事务之间执行，则对后续的事务有效
  • 该语句可以在已经开启事务中间执行，但是不会影响当前正在执行的事务

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vygiTcY0-1661326196168)(C:\Users\guanbin.yang\AppData\Roaming\Typora\typora-user-images\image-20220410105839165.png)]

• 通过undo 和redo日志来体现出来 原子性，一致性和持久性，通过锁的机制来体现出来事务的隔离性

5、事务的类型

• 扁平事务： 扁平事务时设计事务操作中最常见的，也是最简单和常用的一种事务，在数据库中，通常是使用begin或者是start transaction字段开始，由commit或者rollback字段结束，在这之间所有的操作要么全部提交成功，要么全部提交失败
• 带有保存点的扁平事务： 扁平事务如果在事务内部设置了保存点，就是带有保存点的扁平事务，带有保存点的扁平事务通常是用来在事务内部的某个位置设置savepoint，来达到事务回滚的目的，解决的问题是扁平事务，要么全部成功，要么全部失败的问题，普通事务的保存点是隐式的保存点，事务开始的时候，默认是开始位置
• 链式事务： 链式事务是在带有保存点的事务基础之上，自动的将当前的事务的上下文隐式的传递给下一个事务，也就是说一个事务的提交操作和下一个事务开始操作具有原子性，上一个事务的处理结果对下一个事务是可见的，事务和事务之间就像是链条一样传递下去
• 嵌套事务就是有多个事务处于嵌套状态，共同完成一项任务的处理过程，整个任务具有原子性，嵌套事务最外层有一个顶层事务，这个顶层事务控制着所有内部子事务，内部子事务在提交完成之后整体事务并不会进行提交，只有等待最外层的事务提交完成之后，整个事务才算是提交完成
• 分布式事务是指事务的参与者，事务所在的服务器，涉及到的资源服务器，以及事务管理器分别位于不同的分布式心痛中不同的服务或者数据库的节点上

并发问题的解决方案

• 方案一：

  读操作使用多版本并发控制(MVCC),写操作进行加锁，所谓的MVCC,就是生成一个ReadView，通过read view找到符合条件的记录版本，历史版本由undo日志构成，查询语句只能读到生成readview之前已经提交事务的所有更改，在readview之前未提交的事务，或者是之后才开启的事务的更改时看不到的，写操作肯定时针对的最新版本的记录，读记录的历史版本和改动记录的最新版本本身不冲突，采用MVCC时，读写操作不会冲突

• 方案二：

  读操作和写操作都采用加锁的方式

mysql中的锁

• 按照操作类型划分，分为读锁/共享锁，写锁/排他锁
• 按照锁的粒度进行划分，划分为表级锁，行级锁和页级锁
• 对待锁的态度进行划分，悲观锁和乐观锁
• 加锁的方式进行划分，隐式锁和显式锁
• 其他进行划分，全局锁和死锁

锁的结构：

• trx信息：代表着这个锁结构是哪个事务生成的
• is_waiting:代表着当前事务是否需要等待

1、读操作

对读取的记录加上s锁(共享锁)

select .... lock in share mode;

对读取的记录加上x锁(写锁)：

select .... for update

在mysql5.7之前，select…for update,如果获取不到锁，就会一直等待，直到 'innodb_lock_wait_timeout’超时。在8.0版本中，select … for update, select …for share，添加’nowait,skip locked’语法，就会跳过锁的等待，或者跳过锁定。

• 通过添加NOWAIT,SKIP LOCKED语法，能够立即返回，如果查询的行已经加锁：
  • 那么NOWAIT会立即报错返回
  • skip locked 也会立即返回，只是返回结果中不包含被锁定的行

2、写操作

平时使用到的增删改这三种操作，没有商量的余地，只能使用排他锁

• delete:

  对一条记录做删除操作，其实是先在B+树上定位到这条记录的位置，然后再获取到这条记录的X锁，然后再执行delete mark操作

• update:再对一条记录做update操作的时候分为三种情况：

  1. 未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化，则先在b+树上定位到这条记录的位置，然后再获取一下X锁，最后在原记录的位置进行修改操作，我们也可以把这个定位修改记录在b+树中的位置看作是一个获取X锁的锁定读
  2. 未修改该记录的键值，并且至少有一个被更新的列占用的存储空间在修改前后发生了改变，则先在b+树中定位到这条记录的位置，然后再获取一下记录的X锁，将该记录进行彻底的删除掉(就是将该记录彻底的移动到垃圾链表中)，最后再插入一条新的记录，这个定位待修改记录在b+树中的位置过程堪称为是一个获取x锁的锁定所，新插入的记录由INSERT提供的隐式锁进行保护
  3. 修改了该记录的键值，则相当于在原纪录上做了delete操作之后再进行一次insert操作，加锁的操作就需要delete和insert规则进行

• insert:

  一般情况下，新插入的一条记录操作并不加锁，通过一种称为隐式锁的结构来保护这条新插入的记录再本事务提交之前不会被别的事务进行访问

3、从数据操作的粒度划分：表级锁，页级锁，行锁

为了尽可能的提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只能缩影当前操作的方案会得到最大的并发度，但是管理锁是很耗费资源的事情，涉及到获取锁，检查锁，释放锁，数据库系统再高并发响应和系统性能两方面平衡，就产生了锁粒度的概念

对一条记录加锁也只是这条记录影响而已，我们就说这个锁的粒度比较的细，一个事务可以在表级别进行加锁，就称为表级锁，对一个表加锁就影响整个表中的记录，我们就说这个锁的粒度比较粗，锁的粒度分为表级锁，页锁和行锁

3.1、表锁

该锁会锁定整张表，他是mysql中最基本的锁，并不依赖于存储引擎，并且表锁是开销最小的策略，因为锁的粒度比较大，由于表级锁一次会锁定整张表，所以可以很好的避免死锁的问题，锁的粒度大带来的负面的影响就是出现锁资源争用的概率也会比较的高，并且并发率会很低，innodb在进行增删改的时候，不会为表添加表级别的x或者S锁的，但是在并发执行的情况下，会发生阻塞现象，这个过程叫做元数据锁

一般情况下我们不会使用Innodb存储引擎提供的表级别的s锁和X锁，只会在崩溃恢复的过程中使用到，手动的获取到innodb存储引擎提供表的s锁或者是x锁可以这么写

lock tables t read;#对表加表级别的s锁，t表示表名
lock tables t write; #对表添加表级别的X锁

尽量不要使用lock table这样的手动锁表语句，innodb厉害的是实现了更细粒度的行锁，

查看表上加过的锁

show open tables 
# in_use>0 表式上锁
show open tables where in_use >0
#释放锁
unlock tables;

mysam在执行查询语句的时候，会给涉及到的所有表添加读锁，在执行增删改操作的时候，会给涉及到的表加上写锁，innodb存储引擎不会为这个表添加表级别的读锁或者是写锁

3.2、意向锁

innodb支持多粒度锁，它允许行级锁和表级锁共存，而意向锁就是一种表锁

1. 意向锁的存在就是为了协调行锁和表锁之间的关系，支持多粒度的锁的并存
2. 意向锁是一种不与行级锁冲突的表级锁
3. 表命了某个事务正在某些行持有了锁或者该事务准备去持有锁

意向锁分为两种：

• 意向共享锁： 事务有意向对表中的某些行加共享锁

-- 事务要获取某些行的S锁，必须要获得表得IS锁
select column from table ... lock in share mode;

• 意向排他锁：事务有意向对表中得某些行加上排他锁

-- 事务要想获取到某些行的X锁，必须要先获取到IX锁
select column from table ... for　update;

多版本并发控制

1、什么是MVCC

MVCC 多版本并发控制，顾名思义，MVCC就是通过多个版本管理来实现数据库的并发控制，这项技术使得在innodb的事务隔离级别下执行一致性读操作有了保证，换而言之，就是为了在查询一些正在被另一个事务更新的行，并且可以看到他们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁，所谓的MVCC,就是生成一个ReadView，通过read view找到符合条件的记录版本，历史版本由undo日志构成，查询语句只能读到生成readview之前已经提交事务的所有更改，在readview之前未提交的事务，或者是之后才开启的事务的更改时看不到的，写操作肯定时针对的最新版本的记录，读记录的历史版本和改动记录的最新版本本身不冲突，采用MVCC时，读写操作不会冲突

2、MVCC的实现的原理 ReadView

MVCC的实现依赖于：隐藏字段，undo log, read view。MVCC主要解决的内容时读写冲突

2.1、什么是Readview

在MVCC中，多个事务对同一个记录进行更新会产生多个历史快照，这些历史快照保存在undo log 里， 如果一个事务要想查询这个行记录，需要使用到readView,它帮助我们解决了行可见性的问题

ReadView 就是事务在使用MVCC机制进行快照读取时产生的读视图，当事务启动的时候，会生成数据库系统当前的一个快照，innodb为当前的每个事务构造了一个数组，用来记录并且维护当前活跃事务的id，活跃表示事务启动了还没有进行提交 readView 就是指的是事务

在mysql中默认的隔离级别时可重复读，可以解决脏读和不可重复读的问题，如果从定义的角度来看，他并不能解决幻读的问题，如果我们要想解决幻读的问题，就需要使用串行化的方式，将事务的隔离级别提高到最高，但是这样会大幅度的降低数据库的并发能力

MVCC可以不采用锁机制，而是通过乐观锁的方式来解决不可重复读和幻读的问题，大多情况下可以代替行级锁，降低系统的开销

2.2、设计的思路

使用read uncommitted 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好，使用serializable隔离级别的事务，innodb规定使用加锁的方式来访问记录，上面的两个隔离级别的事务无法使用到MVCC

使用read committed和 repeatable read隔离级别的事务，都必须保证读到了已经提交了的事务修改过的记录，假如另一个事务已经修改了记录但是还没有进行提交，是不能直接读取最新的版本记录的，核心的问题就是需要判断一下版本链中的哪个版本是当前事务可见的

readview 中包含的重要内容：

1. creator_trx_id,创建这个readview的事务id,只有在增删改的时候才会为事务分配事务id，在只读事务中id值都默认为0
2. trx_ids,表示生成readview时当前系统中活跃的读写事务的事务id列表
3. up_limit_id,活跃事务中最小的事务的id
4. low_limit_id,表示生成readView时系统中应该分配给下一个事务的id值，low_limit_id是系统中最大的事务id值，是系统事务的id，需要跟正在活跃事务id进行区分，不在事务活跃id的列表中(trx_id)

2.3、readview 的访问规则

• 如果访问的版本trx_id的属性值和readview中的creator_trx_id值是相同的话，就意味着当前事务在访问他自己修改的记录，所以该版本可以被当前的事务进行访问
• 如果访问的事务的trx_id小于up_limit_id的值，表名生成该版本的事务在当前事务生成readview之前的时候就已经提交了，所以该版本是可以被当前的事务进行访问的
• 如果被访问的trx_id小于readview 中的low_limit_id,表名生成该版本的事务在当前事务生成readview之后才开启的，所以该版本不能被当前的事务进行访问
• 如果trx_id在up_limit_id和low_limit_id之间，就需要进行判断一下trx_id的属性是不是在trx_ids列表中
  • 如果在的话，说明创建readview时生成的该版本的事务还是活跃的，该版本就不能够被访问
  • 如果不在，说明创建readview时生成的该版本的事务已经被提交，该版本可以被访问

2.4、隐藏字段，Undo log版本控制链

undo 日志的版本链，对于Innodb来说，他的聚簇索引记录中包含两个必要的隐藏列

• trx_id: 每次一个事务对某条聚簇索引记录进行改动的时候，都会将该事务的id赋值给trx_id的隐藏列
• roll_pointer：每次对某条聚簇索引记录进行改动的时候，都会将旧的版本写入到undo日志中，然后隐藏这个列就相当于一个指针，通过它就可以找到记录修改前的信息

2.5、MVCC的执行的流程

1. 首先通过事务获取到事务的版本号，也就是事务的id
2. 然后获取到readview
3. 查询得到的数据，跟readview中事务的版本号进行比较
4. 如果符合readview中的规则，然后就从undolog中获取到历史的快照
5. 最后返回符合规则的数据

在隔离级别为读已提交的时候，事务中的每一次select查询都会获取一下readview，为了防止不可重复读的现象出现

当隔离级别为不可重复读的时候，只需要获取一次readview