零、基础相关

1.char和varchar的区别？

①长度范围不同：char:0-255;varchar:0-65535;

②长度是否可变：char长度不可变；varchar长度是可变的；

③修改效率不同：char类型每次修改的数据数据长度相同，效率更高；varchar类型每次修改的长度不同，效率低；

④存储不同：char类型存储的时候是预计字符串+记录长度的字节，占用空间大；varchar存储的时候是实际的字符串+记录长度的字节，占用的空间小；

2.SQL优化的方式

①怎么优化sql?

a.Sql语句的优化

1.不要使用select*查询，尽量明确查询字段

2.or的查询尽量使用union或者union all来代替

3.尽量减少子查询，使用关联查询来代替子查询；

b.如果使用了索引，则可以进行索引优化

1.每张表索引数量尽量不超过5个

2.禁止给每个字段都建立索引

3.为查询最频繁的字段建立索引

4.频繁更改的字段不适合建立索引

5.不能有效区分数据的字段不适合建立索引等

c.数据库表的优化

1.字段尽量不要设置为null;

2.单表的数据量尽量不要太大，实践表明，单表的数量在300万以上时，性能会出现下降；

3.字段优化尽量选择符合要求的最小的数据类型；

4.禁止在表中建立预留字段

②怎么优化慢sql?

需要查询慢的原因，这里分为两种情况：一是偶尔很慢；二是一直比较慢；

针对偶尔很慢的情况：导致的原因可能有如下的原因：

原因一：数据库在刷新脏页；

原因二：我们要执行的这条语句，可能被加了行锁或者表锁，并且当前的查询事务没有获得锁；

针对一直很慢的情况，则有可能的原因如下：

开启慢查询，分析慢查询日志。

原因一：没有用到索引，导致全表扫描，从而查询慢；此时可以加上索引；

原因二：用了索引，但是索引因为某些原因失效，导致查询慢；

3.redis如何保证与数据库数据一致，并发性有什么样的设计？

写操作：先删除缓存，然后更新数据库；

读操作：先读缓存，如果缓存中没有，则读取数据库中数据，然后再更新至缓存，返回响应。

存在问题：还是可能存在不一致的情况：线程A首先删除了缓存中的数据，还没更新数据x,然后线程B过来读数据，发现缓存中没有数据，然后去数据库中取，获取了旧数据x,然后更新了缓存中的数据，此时线程A更新了数据库，造成数据不一致的现象；

解决方法：使得必须先更新数据库，然后再读数据，并且更新缓存；

准备一个队列，将更新数据库的任务放入队列中，读取数据的操作也放入同一个队列中，这样保证数据的一直性；

优化方向：

方式一：如果有多个读操作的话，则更新缓存只更新一次就可以了，避免重复无意义的重复更新，造成浪费

方式二：如果存在多个写操作的话，则可能会阻塞读操作，造成一直读不到数据的情况，可以通过扩机器的方式将更新操作尽快完成。

4.数据库里是如何查找一个数据的？

①首先会判断是否具备权限，如果不具备权限，则返回相应信息；

②如果具备权限，则会查看数据库缓存中是否存在数据，如果缓存中存在数据，则直接将数据缓存；

③如果缓存中没有命中数据，则会进入到解析器中，解析器会判定当前的语句是否合法，如果不合法，直接返回不合法信息；

④如果合法，则会进一步查看有没有权限，如果没有权限，则直接返回；

⑤如果存在权限，则执行优化器会对查询语句进行优化，然后调用存储引擎，返回查找的数据。

5.数据库的去重操作

①采用数据库的distinct关键字进行去重；

②也可以采用group by进行分组后，添加having筛选条件：判断数量>1,然后在此基础上删除多余的数据；

6.写sql语句

①查询

②删除满足条件的数据

delete from 表名 筛选条件

③建立索引

create index 索引名称[库名_表名_字段名] on 表（字段1，字段2......）;

7.左连接，右连接和全连接的区别？（已修改）

左（外）连接：除了匹配两张表中相关联的记录之外，还要匹配左表中剩余的记录，右表中未匹配的字段用NULL表示

右（外）连接：除了匹配两张表中相关联的记录之外，还要匹配右表中剩余的记录，左表中未匹配的字段用NULL表示

8.去重的方法有哪些？

①distinct关键字；

②group by +筛选条件+删除多余数据；

9.筛选的方法有哪些？

①where条件

②group by执行后的having条件

③order by排序

④limit条件：位置：最后；limit 10:取前十条数据；limit 2,3:取的是第3条开始的3条数据。top：位置：开始；取前n条记录；

10.drop和truncate和delete的区别

①delete是DML，执行delete操作时 ，每次从表中删除一行，并且将修改行的删除操作记录在redo和undo日志中，支持回滚；delete可以根据条件删除部分数据，如果不加条件的话，则删除表中所有的记录，不会删除表结构；

②truncate是DDL，执行truncate时，会删除表中所有的记录，不会删除表结构，不能回滚；

③drop是DDL，执行drop时，删除表的结构及所有数据，不能回滚。

11.MySQL数据库的数据类型

a.整数类型

1.tinyInt:很小的整数（8位二进制）（1字节）

2.smallInt：小的整数（16位二进制）（2字节）

3.mediumInt:中等大小的整数（24位二进制）（3字节）

4.int:普通大小的二进制（32位二进制）（4字节）

b.小数类型

1.float:单精度浮点型（4字节）

2.double:双精度浮点型（8字节）

c.日期类型

1.year:YYYY 1901-2155

2.date:YYYY-MM-DD

3.time：

d.文本、二进制类型

1.char(M):M为0-255之间的整数

2.varchar(M):M为0-65536之间的整数

3.blob:   ①tinyBlob；②smallBlob；③mediumBlob；④longBlob。

4.Text:    ①tinyText；②Text；③mediunText；④longText。

12.MySQL与mangoDB的区别？

①分类：MySQL是关系型数据库，mangDB是非关系型数据库；

②存储位置：MySQL数据存储在磁盘上，而mangDB数据存储在内存中；

③数据类型：MySQL可以存储多种数据类型，mangDB只能存储字符串型数据类型；

13.数据库的三大范式

第一范式：无重复的列

数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值；

第二范式：属性完全依赖于主键，不能依赖于属性的一部分

在第一范式的基础上，要求数据库表中的每个实例或行必须可以被惟一地区分，为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性；

第三范式：属性不依赖于其他非主属性

在第二范式的基础上，要求一个数据库表中不包含已在其它表中已包含的非主关键字信息；

一、MySQL查询

1.基础查询

①语法

selsect 查询列表 from 表名

②特点：

a.查询列表可以是单个字段，多个字段（中间用逗号隔开），常量值，表达式或者函数;

b.查询的结果是一个虚拟的表格，并不真实存储；

c.在进行查询之前，需定位到指定的数据库中，采用use 库名；

d.可以在字段上加着重号（` `）来区分是字段和关键字。

③查询列表

a.查询字段

b.查询常量(100，字符串，表达式)

select 100; select 'abc'; select 100%98;

c.起别名(AS)

1.方式一：在查询字段后面加上as+别名；

2.方式二：在查询字段后面加空格+别名；

select last_name as 姓名 from employees;  select last_name as from employees;

d.去除重复（distinct）

方式：在查询的字段前面加上DISTINCT关键字

#查询员工表中的所有的部门编号（因为存在重复的部门编号，因此要用distinct去除重复）  select distinct department_id from employees;

e.+号的作用（仅有运算符的功能，并不能连接字符串）

select 100+98; #会尝试将一方的字符转换为数值型，转换成功则进行数值相加，转换失败则会将字符串设置为0进行数字运算  select '123'+100;#结果为223  select 'tom'+100;#结果为100

f.连接字符串方法

concat('字符串1'，'字符串2'，。。。)进行字符串连接；

#查询员工名和姓连接成一个字段，并显示为姓名 select concat(last_name,first_name) as 姓名 from employees;

④SQL语言中细节问题

a.字符串：字符串都用单引号表示，即‘abc’;

2.条件查询

①语法

select 查询列表

form 表名

where 查询条件

执行顺序：from→where→select

②分类

a.按照条件表达式筛选

条件运算符：> < =(只有单个等号) !=(不等于) <>（MySQL中的不等于 ） >= <= <=>(MySQL中的安全等于)

select * from enployees where salary>12000; select last_name,department_id from employees where department_id<>90; 

b.按照逻辑表达式筛选(连接条件表达式)

逻辑运算符： && || ！[and or not](MySQL推荐使用)

#查询工资在10000到20000之间的员工名，工资以及奖金  select last_name,salary,commission_pct from employees where salary>=10000 And(&&) salary<=20000;  #查询部门编号不是在90到110之间，或者工资高于15000的员工信息  select * from employees where not(depatrment_id<=90 and department_id>=110) or salary>15000;

c.模糊查询：

关键词：like     between and     in        is null

1.like: 一般和通配符搭配使用

常见的通配符：

①%任意多个字符，包含0个字符（这里指的是在字符串中的位置，如下题中的a的前后任意位置上都可以有其他的字符）

#查询员工名中包含字符a的员工信息  select * from employees where last_name like '%a%';

②_表示任意单个字符（能够明确某个字符在字符串中的位置）

#查询员工名中第三个字符为e,第五个字符为a的员工名和工资 select * from employees where last_name like '__e_a%';  #查询员工名中第二个字符为_的员工名 #这里可以采用转义的方法实现 #方式一：采用java中的\进行转义 #方式二：采用MySQL中的escape “转义符”的方式 select last_name from employees where last_name like '_$_%' escape '$' ;

2.between and（在什么范围内）

#查询员工编号在100到200之间的员工信息 select * from employees where employee_id between 100 and 200;

②使用between and（需要保证前小后大），颠倒之后语法没有错误，但是不会有结果

3.in(属性1，属性2，属性3...)

#查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号 select last_name,job_id from employees where job_id in(IT_PROG,AD_VP,AD_PRES);

②in列表中的数据必须保持一致或兼容（）

③in列表中的数据不能够包含通配符

4.is （not） null关键字

#查询没有奖金的员工名 select * from employees where commission_pct is null;  #查询员工号为176的员工的姓名和部门号年薪  select last_name,  department_id，  salary*12*(1+ ifnull(commission_pct,0)) as 年薪 from employees where department_id=176;

3.排序查询

①语法

select 查询列表

from 表

[where 筛选条件](可省略)

order by 排序列表 [asc/desc]

执行顺序：from    where    select    order by

#查询员工信息，要求工资从高到低排序（降序） select * from eployees order by salary desc;  #查询部门编号>=90的员工信息，按照入职时间的先后进行排序  select * from employees where department_id>=90 order by hiredate asc; 

②注意事项

a.默认按照升序顺序；

b.按照表达式排序(可以用别名来代替表达式)

c.order 后面支持别名

#按照年薪的高低显示员工的信息和年薪 select *,salary*12*(1+ ifnull(commission_pct,0)) as 年薪 from employees order by salary*12*(1+ ifnull(commission_pct,0)) desc;  #按照年薪的高低显示员工的信息和年薪 select *,salary*12*(1+ ifnull(commission_pct,0)) as 年薪 from employees order by 年薪 desc;

d.按照函数进行排序

如：长度函数：length(字段名称)

e.按照多个字段排序时，把先按的字段放在前面，把后排序的放在后面。

因为有一样的字段，然后一样的字段按照后面的字段进行排序

#按照姓名的长度显示员工的姓名和工资  select last_name,salary from employees order by length(last_name) desc;  #查询员工信息，要求先按工资排序，再按员工的编号排序  select * from employees order by salary,hiredate;

f.order by后面可以跟单个字段，多个字段，函数，表达式，别名

order by一般放在查询语句的最后，limit子句除外

4.常用函数(select 函数名（实参列表） [from 表])

一、单行函数

① 字符函数

a.length获取参数值的字节个数（一个汉字占三个字节）

b.concat 拼接字符串

select concat(last_name,'_',first_name) from employees;

c.upper(变大写)，lower(变小写)

#将姓变大写，将名变小写，然后进行拼接 select concat(upper(first_name),lower(last_name)) from employees;

d.substr,substring （截取字符串）

注：MySQL中的索引从1开始

substr(str,index):截取从索引index开始的后面的所有字符

substr(str,index,end);截取从index到end的子字符（含头也含尾）

#姓名中首字符大写，其他字符小写然后用_拼接，显示出来  select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) from employees;

e.instr(str,substr);返回子串在大串中的起始索引值；

f.trim：去除前后的空格

②数值函数

a.round 四舍五入=floor(参数+0.5)

b.ceil 向上取整（大于等于参数的最小整数）

c.floor 向下取整（小等于参数的最大整数）

d.truncate 截断

select truncate(1.699,1);//直接截断小数点后1位

e.mod取余

mod(a,b)=a-a/b*b;

③日期函数

④其他函数

⑤流程控制函数

a.if(实现if else的效果)

select last_name,commission_pct,if(commission_pct is null,"没奖金，呵呵"，"有奖金，哈哈") as 备注 from employees;

b.case(case when then else end)

使用一：switch case的效果

case 要判断字段或者表达式

when 常量1 then 要显示的值1或语句1；（语句需要加分号，值不需要加分号）

when 常量2 then 要显示的值2或语句2；

else 要显示的值n或语句n;

end

#查询员工的工资，要求 #部门号=30，显示的工资为1.1倍 #部门号=40，显示的工资为1.2倍 #部门号=50，显示的工资为1.3倍 其他部门，显示的工资为原工资 select salary as 原工资，department_id, case department_id when 30 then salary*1.1 when 40 then salary*1.2 when 50 then salary*1.3 else salary end from employees;

使用二：（类似于java中多重if）

case        (这里什么都不加)

when    条件1     then    要显示的值1或语句1

when     条件2     then    要显示的值2或语句2

else        要显示的值n或语句n

end

#查询员工的工资的情况 #如果工资>20000,显示A级别 #如果工资>15000,显示B级别 #如果工资>10000,显示C级别 #否则显示D级别 select salary as 工资 case when salary>20000 then 'A' when salary>15000 then 'B' when salary>10000 then 'C' else then 'D' end as 工资级别 from employees;

二、分组函数

①作用：用作统计使用，又称为聚合函数

②分类：

a.sum求和

sum(单个字段)：

b.avg平均值

avg(单个字段)：

c.max最大值

max(单个字段)：

d.min最小值

min(单个字段)：

e.count计算非空字段值个数

count(单个字段)：

select sum(salary) as 工资和，avg(salary) as 平均工资，max(salary) as 最高工资，min(salary) as 最低工资，count(salary) as 工资个数 from employees;

③特点

a.count,max和min任何类型都支持。

b.sum和avg用于处理数值型；

④分组函数和distinct搭配实现去重 (distinct    需要去重的字段）

#查询去重前后的工资数量 select count(distinct salary)，count(salary) form employees;

⑤count函数具体介绍（统计非空个数）

用法一：count(字段)：统计字段中非空值的行数/个数

用法二：count(*):统计表中非空的行数；

#统计工资的数量 select count(salary) from employees;

⑥分组函数注意事项

a.和分组函数一同查询的字段要求是group by后的字段

5.分组查询（groud by）(每个部门每个工种)

①语法

Select 分组函数，按照什么分组的列 form 表 [where 筛选条件] group by 按照什么筛选字段 【order by 字段 desc/asc】 #查询每个部门的最高工资 select Max(salary),department_id from employees group by department_id;

②添加分组前筛选

#查询邮箱中包含a字符的，每个部门的平均工资 select avg(salary),department_id from employees where email like '%a%' group by department_id;

③添加分组后筛选（通过Having关键字对分组后的结果再次进行筛选）

#查询哪些部门的员工个数>2? #1.首先按照部门分组，查询出每个部门的员工个数 #2.然后在上面查询的基础上，再筛选超过2的部门 select count(*),department_id from employees group by department_id having count(*)>2;

④分组查询筛选条件总结：

a.分组前筛选：筛选前数据源是原始表，筛选条件放在group by前面

b.分组后筛选：筛选后数据源是分组后的结果集，筛选条件放在group by的后面。

d.注：分组函数做筛选条件的必然放在having子句中。

⑤按多个字段分组(将多个字段分别写在group by的后面即可)

#查询每个部门每个工种的员工的平均工资 select avg(salary),department_id,job_id form employees group by department_id,job_id;

⑥搭配分组查询适用

#查询每个部门每个工种的员工的平均工资，并且按照高低显示出来 select avg(salary),department_id,job_id form employees group by department_id,job_id order by avg(salary) desc;

⑦综合各种查询

#查询每个部门（部门不为空）每个工种的员工的平均工资，然后将大于10000的平均工资按照从高到底进行排列 select avg(salary) as 平均工资,department_id as 部门编号，job_id as 工种 from employees where department_id is not null group by department_id,job_id having 平均工资>10000 order by 平均工资 desc;

6.连接查询(☆)(join)(当需要查询的字段来自不同的表时，需要用多个表进行连接)

①笛卡尔积

a.定义：表1有m行，表2有n行，则结果有m*n行。发生原因：没有有效的连接条件，需要添加有效的连接条件才可以；

#查询女神表中女神对应男神 select name,boyName from beauty as 女神表,boys as 男神表 where 女神表.id=男神表.id; #或者是 where beauty.id=boys.id;

②连接查询的分类

a.内连接

等值连接

非等值连接

自连接

b.外连接

左外连接：

右外连接：

c.交叉连接

d.sql99语法

1.语法：

select    查询列表

from    表1    别名    【连接类型】

join    表2    别名

on    连接条件

【where    筛选条件】

【group    by    分组函数】

【having    筛选条件】

【order    by】

2.连接类型

①内连接(☆)：关键字 inner

②外连接(☆)：左外连接：left 【outer】 右外连接：right 【outer】

④交叉连接：关键字：cross

③内连接（inner join on）

语法：

select 查询列表

form 表1 别名1 inner

join 表2 别名2

on 别名1.字段=别名2.字段

【where 筛选条件】

【group by 分组函数】

【having 筛选条件】

【order by 排序条件】

a.等值连接

#查询员工名及对应的部门名（对应的部门编号相等，作为两张表的连接条件） select last_name,department_name from employees 员工表 inner join departments 部门表 on 员工表.department_id=部门表.department_id;

②添加筛选条件(where)

#查询名字中包含e的员工名和工种名，其中员工名位于employees表中，工种名在jobs表中 select last_name,job_title from employees as 员工表 inner join jobs as 工作表 on 员工表.job_id=工作表.job_id where 员工表.last_name like '%e%';

③分组加筛选

#查询部门个数>3的城市名和部门个数，其中城市名位于locations表,部门名位于departments表，存在共同的字段location_id； select city,count(*) as 部门个数 from departments as d inner join locations as l on d.location_id=l.location_id #查询出每个城市的部门个数，然后再进行二次筛选 group by city having count(*)[部门个数]>3;

④分组加筛选加排序

#查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）;部门名位于departments表中，员工位于employees表中，通过department_id建立连接。 #首先查询出每个部门的员工个数，然后在此基础上筛选出大于3的部门名及对应的员工个数 select department_name,count(*) from employees as 员工表 inner join departments as 部门表 on 员工表.department_id=部门表.department_id group by department_name having count(*)>3 order by count(*) desc;

b.非等值连接

1.与非等值连接的区别：连接条件不是等值，而是用条件运算符来表示。

2.举例子

#查询员工的工资级别,员工的工资字段来自employees表，工资级别grade_level字段来自job_grades表，连接条件是salary介于lowest_sal和highest_sal select salary,grade_level from employees 员工表 inner join job_grades 工资级别表 #非等值连接 on 员工表.salary between 员工级别.lowest_sal and 员工级别.highest_sal;

c.自连接

自连接是同一个表中不同字段，看做是两张表；

④外连接

a.左外连接：

1.定义及使用场景：

①用于查询一个表中有，另一个表中没有的记录；

②定义：

③特点：

A.外连接的查询结果=内连接的结果+主表中有的而从表中没有的记录

B.左外连接，left左边的是主表；右外连接，right右边的是主表；

C.要查询的字段主要在哪儿哪个就是主表

2.案例：

现有两张表，分别是beauty表和boys表，表结构如下：

beauty表 boys表

例子一：查询男朋友不在男神表中的女神名（女神表为左表，男神表为右表，题意相当于左表中存在，而整个右表中不存在的，相当于在左外连接的基础上，然后令右表为空）

select be.name from beauty as be left outer join boys as bo on be.boyfriend_id=bo.id where bo.id is null;

例子二：查询哪个部门没有员工（现有部门表(departments)和员工表(employees)，主表为部门表，从表为员工表，连接字段为department_id）

select d.* from departments as d left outer join employees as e on d.department_id=e.department_id where e.department_id is null;

b.右外连接：

右外与左外的转换：左外和右外交换两个表的顺序，可以实现相同的效果；

二、逻辑架构及存储引擎

1.MySQL的逻辑架构

①连接层

完成一些连接处理、授权认证及相关的安全处理等

②服务层

a.Management Serives&Utilltiles:系统管理和服务工具

b.Sql Interface:SQL接口

负责接收来自用户的SQL命令，返回用户查询的结果

c.Parser(解析器)

SQL命令传递到解析器，负责命令的验证与解析

d.Optimizer（查询优化器）

SQL语句在查询之前，查询优化器会对其进行优化，以提高查询效率。

e.缓存和缓冲

缓存负责读，缓冲负责写

如果缓存中有命中的查询结果，则查询语句会直接将缓存中的结果取出。

③引擎层

可以切换不同的执行引擎，提高运行效率，即负责MySQL的存储和提取；

④存储层

用于存储数据库的数据，并完成与存储引擎的交互。

2.存储引擎

①MyISAM和Innode的区别（表格整理）

a.外键

MyISAM不支持外键；InnoDB支持外键

b.事务

MyISAM不支持事务；InnoDB支持事务

c.行表锁

MyISAM只支持表锁，即使是操作某一个数据，也要对整张表进行加锁；不适合高并发的场景。

InnoDB不仅支持表锁，还支持行锁，对某一行加锁不影响其他行，适合高并发的场景。

d.缓存

MyISAM只缓存索引，不缓真实数据；

InnoDB不但缓存索引，还缓存真实数据，对内存性能要求较高

f.索引

MyISAM为非聚簇索引；Innodb是聚簇索引

e.唯一索引

MyISAM可以没有唯一索引；InnoDB表必须有唯一索引（如主键）

②Innodb与MyIsam的优缺点

MyIsam优势：

1.读取速度非常快，适合读多写少的场景，因为读操作不会阻塞，直接读就行。

2.占用资源少，服务器硬件不好时，可以考虑使用；

MyIsam劣势

1.不支持事务

2.对整张表进行锁定，因此修改很慢；

3.只对索引进行缓存，不缓存数据。

Innodb优势：

1.支持主键

2.支持事务

3.既缓存索引，又缓存数据；

Innodb劣势：

1.读速度没有MyIsam快，因为读操作可能被阻塞；

2.占用资源比较大

③Innodb与MyIsam的适用场景

MyIsam适用场景：

1.不需要事务支持的场景，如：读数据比较多的网站；

2.并发相对较低的业务，因为表级锁定的机制限制

3.数据修改相对较少的业务，存在阻塞问题

4.对数据要求一致性不高的业务；

Innodb适用场景

1.需要支持事务的场景

2.适合高并发的业务

3.数据更新比较频繁的场景

4.数据一致性要求较高

5.硬件设备内存较大；

④为什么有了Innodb还需要用MyIsam呢？

MySQL5.5及之前默认的存储引擎为MyISAM,5.5及以后，默认的存储引擎为Innodb。

因为MyIsam存储引擎占用资源较少，查询速度相对Innodb来说比较快；在读多写少的情况下，用户的体验感更佳。

⑤Innodb建表可以不用主键吗？

不可以，主键可以表征数据的唯一性；如果创建表时，没有定义主键的话，则Innodb内部会生成一个隐藏主键，是一个6字节的列，修改列的值，会随着数据的插入自增；

另外作用：1.主键递增，数据行写入可以提升插入性能，减少表碎片，提升空间和内存使用；

2.主键选择较短的数据类型，innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效减少索引的磁盘空间。

3.SQL性能

①SQL性能下降原因

a.查询语句需要进一步优化

可能查询语句写的不好，存在较多的关联

b.数据量过大，没有创建索引

存在上百万条的数据，然而并没有创建索引来进行查询

c.创建了索引，但是索引失效

创建了索引，但是数据进行了较多修改，并没有及时更新索引，导致索引失效

d.关联查询，存在太多的join;

②SQL语句的执行加载顺序    （一条SQL语句是如何执行的）

a.查询语句

1.查询该语句是否有权限，如果没有权限则直接返回，如果有权限，首先会查看命中缓存，如果命中缓存，则直接将缓存中的结果进行返回，否则进入到解析器环节；

2.解析器对该语句进行解析，并判断是否合法，如果不合法，则返回相关信息，如果合法的话，则进入到查询优化器环节；

3.查询优化器在其合法的情况下，会对其进行语句优化，并选择认为最有执行方案进行执行；

4.再次进行权限判断，如果不存在权限的话，则返回错误信息，如果存在权限，则调用引擎接口，返回查询结果

b.更新语句

1.首先在查询结果的基础上，先获取到查询结果；

2.调用执行引擎，将更新结果写入到内存中，更新重写日志，通知执行器；

3.执行器接到通知，然后更新归档日志，进行提交，更新完成。

③sql有哪些优化方式

a.Sql语句的优化

1.不要使用select*查询，尽量明确查询字段

2.or的查询尽量使用union或者union all来代替

3.尽量减少子查询，使用关联查询来代替子查询；

b.如果使用了索引，则可以进行索引优化

1.每张表索引数量尽量不超过5个；

2.禁止给每个字段都建立索引

3.为查询最频繁的字段建立索引

4.频繁更改的字段不适合建立索引

5.不能有效区分数据的字段不适合建立索引等

c.数据库表的优化

1.字段尽量不要设置为null

2.单表的数据量尽量不要太大，实践表明，单表的数量在300万以上时，性能会出现下降；

3.字段优化尽量选择符合要求的最小的数据类型；

4.join相关

a.join的七种结构理论

①表A和表B共有：

Select * from TableA inner join tableB on A.key=B.key；

②表A和表B共有+表A独有:

Select * from TableA left join tableB on A.key=B.key；

③表A和表B共有+表B独有

Select * from TableA right join tableB on A.key=B.key；

④表A独有

Select * from TableA left join tableB on A.key=B.key Where B.key=null;

⑤表B独有

Select * from TableA right join tableB on A.key=B.key Where B.key=null;

⑥表A和表B全部

select * from TableA full join table B on A.key=B.key；

因为MySQL没有全连接，因此为：“left join +union(可去除重复数据)+right join”

Select * from TableA left join tableB on A.key=B.key

Union

Select * from TableA right join tableB on A.key=B.key；

⑦表A独有+表B独有

表A独有+表B独有然后去除重复

Select * from TableA left join tableB on A.key=B.key Where B.key=null;

Union

Select * from TableA right join tableB on A.key=B.key Where B.key=null;

三、索引优化（☆）

1.索引介绍及分类

①索引是什么

索引是指帮助数据库快速获取数据信息的数据结构，索引的实现通常使用B树和B+树；通俗来讲，索引就相当目录，不用通过翻阅整本书就能比较快速的找到自己需要的内容。

②索引的优势

a.提高数据检索的效率，减少数据库的IO成本

b.通过索引列对数据进行排序，降低了数据排序的成本

③索引的劣势

a.实际上索引也是一张表，该表记录了主键和索引字段，并指向数据实体，因此索引占用空间；

b.虽然索引大大增加了查询速度，但是数据在增加，删除或者更新的时候，索引也需要进行动态调整，因此也需要消耗资源。

④索引分类

a.主键索引

1.设定为主键后，自动创建索引。数据列不允许重复，不允许为Null值，一个表只能有一个主键；

2.语法： Alter table table_name add Unique(列)；

b.唯一索引

1.数据列不允许重复，允许为Null值，一个表允许多个列创建唯一索引

2.语法：Alter table table_name add unique(列1，列2...)；

c.单列索引

1.一个索引只包含单个列，一个表可以有多个索引，但是一般不会超过5个。

2.语法：Alter table table_name add index index_name(列字段)；

d.复合索引

1.联合索引指的是MySQL可以使用多个字段同时建立一个索引，称之为联合索引。

2.语法：Alter table table_name add index index_name(列字段1，列字段2......)；

f.全文索引

1.是一种文本查询，类似于like查询方式，但是比其要快；

2.语法：Alter table table_name add FullText(列字段)

按照索引是否和数据绑定在一起，分为

a.聚簇索引

将数据存储与索引放在一起，B+树的叶子节保存了行数据；

innodb中，在聚簇索引之上创建的索引称为辅助索引，辅助索引叶子节点存储的不是行的物理地址，而是主键的值，辅助索引访问数据总是需要二次查找。

过程：Innodb使用的是聚簇索引，将主键组织到一颗B+树，叶子节点存储的是行数据，如果使用where id=14条件进行查找，则按照B+树的检索算法即可查到对应的叶节点，之后获得行数据。

如果对name列建立单独索引，进行条件搜索，需要两个步骤：第一步在辅助索引B+树的数据结构中检测name,达到其叶子节点获取所对应的主键，第二步：通过这个主键主键索引B+树的结构中再查找一次，最终即可获取到整行的数据。

b.非聚簇索引

将数据与索引分开存储，B+树叶子节点指向数据对应的位置；

MyISAM使用的是非聚簇索引，非聚簇索引的两颗B+树没什么区别，存储的内容不同，主键索引B+树的节点存储了主键，辅助索引B+树存储了辅助键。表数据存储在独立的位置，然后两颗B+树的叶子节点存储了指向数据的地址。因为索引树是独立的，通过辅助键检索无需访问主键的索引树。

按照数据结构的不同

a.B树索引

b.B+树索引

c.哈希索引

按照是否需要回表分为

a.覆盖索引：如果一个索引包含所有需要查询的字段的值，我们就直接从索引里取数据，不需要进行回表操作。

b.回表操作：通过非主键索引进行查找的时候，如果查询的数据索引不包含，就需要根据查找出来的主键，然后再到主键索引查询出需要的数据。

⑤索引越多越好吗？

不是，创建索引需要耗费资源；一是增加了数据库的存储空间；二是在插入和删除时需要动态维护索引，降低了增，删和改的速度；

⑥组合索引怎么实现的？

Alter table 表名 add index index_name(字段1，字段2……)；

⑦Innodb主键索引和非主键索引区别

Innodb主键索引，叶子结点存储的是行数据，因此主键索引非常高效；MyISAM索引的叶子结点存储的是行数据地址，需要再寻址一次找到数据；

Innodb非主键索引的叶子结点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引非常高效。

2.索引结构

0.说一下 MySQL 的索引是什么结构的

①B+树

1.B+树一般树高是多少？能存多少范围的数据量？

高度一般是3-5层，能存几千万的数据；

②B+树优势

1.B+树的磁盘读写代价更低

由于B+树的内部节点并没有指向具体信息的指针，因此相对于B树而言内部节点更小，一次性读入内存中的关键字就会更多，能够减少IO的读写次数。

2.B+树的查询效率更加稳定

因为只要叶子结点存储具体数据，因此任何查找都是从根结点到叶子结点，算法的复杂度都是logn，因此每次查询效率相当。

3.B+树的查询效率更快

因为B+树的内部节点都存放索引，数据都存储在叶子结点中，方便扫库，而且所有的叶子结点相当于一个有序链表更加方面查找，因此查询效率更高。

③哈希索引

1.定义：类似于数据结构中的散列表，我们在mysql中使用哈希索引时，主要是通过哈希算法将数据库的字段数据转换定长的哈希值，然后与这条数据的行指针一并存入哈希表的对应位置，出现哈希冲突的话，拉链法进行解决。

2.适应场景：绝大多数为单条查询记录的时候可以选择哈希索引。

3.缺点：不能用于范围查询，只能用于单条精确查询。

④B树与B+树比较（参考②）

1.结构层面：

2.查询稳定性：

3.磁盘读写代价：

⑤MySQL索引为什么用b+树？不用哈希表？不用平衡二叉树？不用B树？不用红黑树？

a.为什么不要哈希表

哈希表不稳定，某个键值存在大量的重复的时候，发生哈希碰撞，效率会变得较低；

b.为什么不用平衡二叉树？

平衡二叉树的在数据库值修改后，需要动态维持树的平衡，代价比较大；

c.为什么不要红黑树？

在数据库值被修改后，需要动态调整为新的红黑树，代价比较大，以便及时更新索引；

⑥B树，B+树和红黑树的区别？

红黑树：

1.红黑树的性质：

①要么是红色，要么是黑色；

②根节点一定是黑色；

③每个红节点都有两个黑色子节点；

④叶子节点都是黑色的，从任何节点到每一个叶子节点经过的路径中黑色节点都是相同的；

2.相对于AVL树的优势：

①不是严格控制左、右子树高度或节点数之差小于等于1，但红黑树高度依然是平均log(n)，且最坏情况高度不会超过2log(n)。

② 红黑树能够以O(logn)的时间复杂度进行搜索、插入、删除操作;任何不平衡都会在3次旋转之内解决；而AVL是严格的平衡树，因此在增加或者删除节点的时候，根据不同情况，旋转的次数比红黑树多。

AVL树：

首先是一棵二叉搜索树，在此基础上，每个节点的左右子树的高度差绝对值不超过1.即带有平衡功能的搜索二叉树。

总结：

AVL树更加平衡，查询效率比红黑树高，插入和删除之后维护慢；

红黑树查询效率相对低，因为比AVL树会不平衡多一层，但是插入和删除之后维护速度快，任何操作都能在3次旋转之内解决。

⑦AVL树和红黑树使用场景

如果查询远远大于插入和删除的话，则选择AVL树，如果查询与插入和删除差不多或者远远小于插入和删除的话，则选择红黑树。

3.索引的适用场景

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

a.主键自动建立唯一索引

b.频繁作为查询条件的字段应当建立索引(where后面的语句)

c.查询中与其他表建立关系的字段，外键关系建立索引

d.单键/复合索引的选择，在高并发下往往选择复合索引

e.查询中排序的字段，建立索引往往会提高效率（group by 和order by 后面的字段建立索引往往会提高效率）。

f.查询中统计或者分组字段

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

a.表记录太少（数据行在100万以下,300万以上就需要建索引了）

b.经常增删改的表（因为虽然查询快，但是增删改数据的同时，需要动态调整索引的数据结构）。

c.where条件用不到的字段不创建索引。

d.如果某个数据列包含许多重复的内容，则为其创建索引意义不大。

4.索引相关语法

①创建索引

create index index_name on table 表明

②查看索引

explain中的key字段

5.explain使用

①id:相同执行顺序由上到下，如果是子查询，则id会自增1；

②select_type:查询的具体操作类型；

③table:该行数据位于哪个表；

④type：访问类型

⑤possible_keys：显示可能用在这张表上的索引；

⑥key:实际使用到的索引；

⑦key_len:索引中使用的字节数；

⑧ref:显示索引的哪一列被使用了；

⑨大致估计查找到记录所需要的行数；

6.索引优化

1.数据库索引如何优化？

a.首先尽量避免索引失效；

e.避免冗余索引和重复索引；

b.每张表的索引数尽量不超过5个；

c.禁止给表中的每一列都建立单独索引；

d.将使用最频繁查询的列放在联合查询的最左侧；

7.最佳左前缀法则

①定义

如果索引了多列，需要遵循最佳左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引的中间列。

②破坏最左前缀匹配法则

8.索引添加的策略

a.最左前缀匹配原则

b.查询次数频繁的应当建立索引

c.频繁更改的字段不适合建立索引

d.不能有效区分的数据/重复性很高的数据不适合创建索引

e.定义有外键的字段应当创建索引

f.尽量扩展索引，不要新建索引

9.数据库建立索引的原则

a.最左前缀匹配原则

b.查询次数频繁的应当建立索引

c.频繁更改的字段不适合建立索引

d.不能有效区分的数据/重复性很高的数据不适合创建索引

e.定义有外键的字段应当创建索引

f.尽量扩展索引，不要新建索引

10.（非）聚簇索引及使用场景

a.聚簇索引指的是索引和数据绑定在一起，然后找到了索引就找到了数据；

b.非聚簇索引指的是索引和数据的地址绑定在一起，找到了索引，根据索引绑定的地址，找到对应的数据；

c.使用场景：

频繁更新/修改的索引列适合用非聚簇索引，因此不需要修改数据，只需要修改其对应的地址即可；

频繁查询大量数据的时候，聚簇索引的性能高于非聚簇索引；

主键索引是聚簇索引；

d.Innodb与MyIsam关于此类的对比

Innodb是聚簇索引；MyISAM是非聚簇索引；

11.导致索引失效的原因？

a.最左前缀法则：如果索引了多列，要遵守最左前缀法则，从左到右依次执行，直到遇到范围条件之后便停止，后面的索引会失效；

如果使用联合索引，则范围条件右边的索引会失效（范围条件右边与范围条件使用的同一个组合索引，右边的才会失效，如果不是同一个索引，则不会失效）。

如上图所示：当遇到age>11时，右侧的pos='mannager'字段索引将会失效，索引的长度78比第三次查询索引长度140小；

b.在使用不等于的时候会导致全表扫描,进而导致索引失效；

c.is not null也无法使用索引；

d.like以通配符开头，会导致全表扫描而出现索引失效；

但是如果以"王%"这样则不会导致索引失效。

e.字符串不加单引号会导致索引失效；

总结：

f.④尽量避免在where子句中使用！=操作符，否则引擎将放弃索引进行全表扫描

⑤尽量避免在where子句中对null值判断，否则引擎将放弃索引而进行全表扫描

12.索引的原理

整体概述:将无序的数据变成有序的查询；

a.把创建了索引的列的内容进行排序

b.对排序的结果生成倒排表

c.在倒排表内容上拼上数据地址

d.查询时，先拿到倒排表的内容，再取出地址链，从而拿到数据。

13.Explain命令

1.怎么查询一句SQL是否使用了索引

使用explain命令，查看其key字段，如果是Null，表示未使用索引；如果为非Null值，则为具体使用索引的字段；

14.哈希索引

1.定义

哈希索引基于哈希表实现，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

2.哈希索引的优缺点

优点：检索效率高，索引的检索可以一次定位。

缺点：

①Hash索引仅仅能满足等值查询，不能使用范围查询；

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

②哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序

③Hash 索引不能避免全表扫描；

④哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

4.哈希索引存在什么问题

①哈希索引仅仅能够满足等值查询，不能够采用范围查询

因为hash索引比较的是进行哈希运算之后的哈希值，索引只能用于等值过滤，不能基于范围的过滤。

②哈希索引数据并不是按照索引值的顺序存储的，因此无法用于排序。

③哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

四、MySQL锁机制(☆)

1.锁理论概述

a.分类

①从对数据操作的类型分为：读锁和写锁;

②从对数据操作的粒度分为：表锁和行锁;

③按照数据是否共享：排他锁及共享锁；

b.读锁和写锁

读锁：针对同一份数据，多个读操作可以同时进行而相互不受影响

写锁：当前写操作没有完成前，会阻断其他读和写的操作。

c.行锁和表锁

行锁：对某一行数据加锁，开销小，加锁快，不会出现死锁

表锁：对整个表加锁，开销大，加锁慢，会出现死锁

页锁：开销和加锁的时间介于行锁和页锁之间，会出现死锁；

d.Mysql都有哪些锁?

①.按照锁的力度

1.行锁：为需要的某一行数据加锁

2.表锁：为整个数据表加锁

3.页锁：介于行锁和表锁之间的一种锁。

②.按照锁的类别

1.共享锁

又称为读锁，当用户要进行数据读取时，对数据加上共享锁，也就是事务A对某个数据加上共享锁，其他事务可以读该数据，但是不能够对其进行修改；

2.排他锁

又称为写锁，当用户要进行数据写入时，对数据加上排他锁，排他锁只能加一个；也就是事务A对某个数据加排他锁，其他事务既不能修改，也不能读取；

2.表锁（偏读）

a.读锁

①语法

加锁：lock table 表名1 read,表名2 read......

查看：show open tables;

解锁：unlock tablses.

②注意事项：

b.写锁

①语法

加锁：lock table 表名1 read,表名2 write......

查看：show open tables;

解锁：unlock tablses.

②注意事项：

c.总结

对表加读锁，不会阻塞其他线程读，但是会阻塞写；对表加写锁，会阻塞其他事务读和写。

d.查询锁的信息

①show open tables;可以查看哪个表被加锁了；

②show status like 'table%';里面有两个参数：

table_locks_waited:表示被阻塞的线程的数量，数量越多，系统越阻塞

3.行锁（偏写）

a.行锁概述

行锁偏向于InnoDB存储引擎，InnoDB与MyISAM的最大不同两点为：一是前者支持事务；而是前者支持行锁

b.加行锁的案例演示

c.无索引/索引失效，行锁会升级为表锁

如：MySQL中存在自动类型转换，如varchar类型如果写成int类型，则会自动升为varchar类型，但是此时如果这个正好为where查询条件，该类还加了索引，但是索引并不是int类型，导致索引失效，从而将行锁变成标所，在另一个线程修改其他行的数据时会出现阻塞现象。

4.间隙锁

a.间隙锁

当我们用范围而不是相等条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但是不存在的记录，称为间隙（GAP）。InnoDB也会对这个记录加锁，这种机制称之为间隙锁。

如下：

b.间隙锁的危害

当锁定一个键值范围内时，对于该范围内不存在的键值也会被无辜锁定，而造成其他线程无法插入锁定范围内的任何数据，某些场景下可能会造成较大的影响。

5.如何锁定某一行

第一步：先设置一个起点：begin;

第二步：在需要设置的行后面加上for update;如：selsect * from table A where a=8 for update;此时这一行就被锁定了，直到提交之后，在此期间，如果其他线程来修改此行的数据，则会被阻塞。

第三步：提交：commit;

6.MySQL锁优化

①尽可能让所有检索都通过索引来完成，避免无索引造成行锁升级为表锁；

②尽量精确检索条件，避免间隙锁的发生

③尽量控制事务的大小，减少锁定的资源量和时长；

④在业务环境允许的情况下，尽可能降低事务的隔离级别

⑤在锁定某一行后，尽量不要去调别的行或者表，尽快完成该行操作释放掉锁。

7.乐观锁与悲观锁的区别?

①悲观锁：就是在读取数据的时候，为了不然别人修改自己读取的数据，就会先对自己读取的数据加锁，只有自己读取完了，才允许别人修改那部分数据。或者说自己修改某条数据的时候，不允许别人读取该数据，只有等自己整个事务提交之后，才释放自己的锁，才允许其他用户访问那部分数据。

②乐观锁：乐观锁认为一个用户读取数据的时候，别人不会去写自己读取的数据；

③时间戳：在数据库表中单独加一列时间戳，每次读出来的时候，就把该字段也读出来，当写回去的时候，就把该字段的值加1，提交之前，数据库的该字段比较一下，如果比数据库的值大的话，就允许保存，否则不允许保存。

8.数据库的并发场景

a.读-读：不存在任何问题，也不需要并发控制；

b.读-写：存在线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读；如：事务A读，然后事务B写，这时候就会出现各种并发问题；

c.写-写：存在线程安全问题，可能会存在更新丢失问题；如：事务A

9.数据库死锁

1.解释一下数据库死锁问题

数据库死锁描述的是这样的一个场景：多个事务同时被阻塞，他们中的一个或者多个都在等待着某个资源的释放，但是都同时被阻塞，无法释放资源，造成程序无法正常退出的现象，这种现象称为死锁。

2.如何解决数据库死锁问题

①无法破坏互斥条件

②破坏请求与保持条件：事务A一次性申请到所有的资源；

③破坏不可剥夺条件：事务A在等待获取资源时，如果长时间获取不到资源，就释放自己已经得到的资源；

④破坏首尾相连条件：事务按照获取资源的反向顺序释放资源。

3.举一个数据库死锁的场景

比如用户A在访问表A，对表A加了表锁，在访问过程中，又访问表B；而用户B在访问表B，对表B加了锁，然后中间又访问表A，这样用户A和用户B都在等待对方释放资源，于是产生了死锁现象。

10.数据库的分布式锁

1.分布式锁

分布式锁区别于线程锁，分布式锁是属于进程范畴的，而且进程可以在不同的机器上，它要保证的是多个进程对共享变量修改访问同步（如集群环境下同时修改缓存和数据库）。分布式锁也同样需要一个可以让所有进程访问到的标记（如数据库的字段，Redis的数据或着zookeeper的节点等）来控制同步。

2.分布式锁分类

①基于数据库的分布式锁

②基于缓存（Redis）的分布式锁

③基于Zookper的分布式锁

3.基于数据库的分布锁的实现

①基于版本号字段（乐观锁）

为表结构增加版本号字段，在读取数据时，会将版本号一同读出，之后更新时，对版本号+1。在更新的过程中，会对版本号进行比较，如果版本号一致，没有发生变化，则会成功执行本次操作，如果版本号不一致，则会更新失败。

弊端：要为每一张表增加版本号字段，对数据表的侵入较大；

4.基于Redis的分布式锁

实现过程：利用setnx（set if not exits）,如果key不存在，则将key的值设置为value；否则不做任何操作；因为Redis是单线程的，则并发请求会串行执行，只有第一个set值成功的线程才能获取到锁，其他的线程会失败，保证原子性，获取到锁之后，会执行后续的业务处理，如果出现异常或者过了锁的有效期，锁会自动释放。

5.基于zookeeper的分布式锁（待补充）

五、事务相关

1、定义

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单元，其执行的结构必须保证数据库从一个一致性的状态变为另一个一致性的状态。事务是逻辑上一组操作，要么都执行，要么都不执行。

2、四大特性（ACID）

a.原子性：事务是最小的执行单元，不允许分割。事务的原子性确保动作要么全部执行，要么全部不执行；

c.一致性：执行事务前后，数据保持一致；多个事务对同一个数据的读取结果是相同的；

i.隔离性：并发访问数据库时，一个用户的事务不能被其他事务所干扰，并发事务之间的数据库是独立的；

d.永久性：一个事务提交之后，对数据库的改变是永久性的；

3、如何保证四大特性

①MySQL如何保证原子性？

利用Innodb的undo log，即回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息；

undo log记录了这些需要回滚的信息，当事务执行失败或者调用了rollback时，可以利用undo log中的信息将数据回滚到修改之前的样子。

②MySQL如何保证一致性

从数据库层面来说，数据库通过原子性、隔离性及持久性来保证数据一致性，也就是四大特性中，一致性是目的，其他三大特性是手段，是为了保证一致性，数据库提供的手段；必须首先三大特性才有可能实现一致性；

③MySQL如何实现隔离性

利用锁和MVCC机制。

MvCC即多版本并发控制，一个行记录数据有多个版本对快照数据，这些快照数据存在undo log中；

④MySQL如何实现永久性

利用Innodb中的redo log

子问题：MySQL是先把磁盘上的数据加载到内存，在内存中对数据进行修改，然后再刷回磁盘。如果此时宕机了怎么办？

回答：事务提交之前直接把数据写入磁盘，这样有什么问题？

一、浪费资源

如果只修改很小的数据，然后就将整个页面刷回磁盘，造成资源浪费

二、速度慢

一个事务中的SQL语句可能涉及到多个数据页的修改，可能涉及到随机IO，操作随机IO,速度变慢

正确回答：采用redo log。

当做数据修改时，不仅在内存中操作，还会再redo log中记录这次操作，当事务提交的时候，会将redo log日志进行刷盘。当数据库宕机重启时，会将redo log中的内容恢复到数据库中，再根据undo log和bin log中的内容决定回滚还是提交；

子问题：redo log的好处？为什么比数据页刷盘的效率高？

一、redo log只记录修改了啥，梯级比数据页小多了，刷盘快；

二、redo log是一直在往末尾追加，属于顺序IO，效率比随机IO快的多；

4、事务存在问题

①脏读

事务A读取了事务B修改但是还没提交的数据，即事务A将某个字段进行了修改，但是还没提交，此时事务B读到了这个字段，但是此时事务A回滚，因此事务B读到的是无效的字段。

②不可重复读

事务A读取了事务B修改前后的数据，造成两次读取的数据不一致。即事务A读取了某个字段，然后此时事务B对该字段进行了修改，之后事务A再次读取该字段，发现前后读取的字段不一致。

③幻读

事务A读取到了某字段，此时事务B插入/删除了行数据，然后此时事务A再次读取该字段时，发现数据表已经被改变了，就像产生幻觉一样。

5、事务隔离级别及设置

a.读未提交

1.定义：事务A读取到事务B修改但是未提交的数据，存在脏读、不可重复读及幻读的问题；

2.实现：读取数据不需要加共享锁，事务之间没有任何限制，可以随意读取和修改；

b.读已提交

1.定义：事务A只能读取到事务B修改并且已经提交的数据。避免脏读问题；oragle的默认隔离级别。

2.实现：读操作需要加共享锁，但是在语句执行完之后释放共享锁；事务A对某个数据加了共享锁，然后事务B过来只能读不能修改。

c.可重复读

1.定义：不允许脏读和不可重复读，对同一字段的多次读取结果是一致的，MySQL的默认隔离级别

2.实现：读操作需要加共享锁，但是在事务提交之前并不会释放共享锁，必须等到事务执行完之后，才能释放共享锁；

d.序列化

1.锁定整个范围的表，并且会一直持有锁，直到事务完成。

e.设置隔离级别

set transection isolation level

6、事务的隔离级别是如何保证不出现相应问题

在读未提交的级别下，读取数据不需要加共享锁，因此会出现脏读等现象，因为事务之间没有任何限制，可以随便的读取与更改；

在读已提交的级别下，读操作需要加共享锁，但是在语句执行完之后释放共享锁；事务A对某个数据加了共享锁，此时事务B只能过来读取但是不能够修改，这样就能解决脏读问题；但是他在读取完会释放锁，此时事务B来修改之后，事务A再来读就造成不一致，出现不可重复读问题；

在可重复读级别下，读操作需要加共享锁，但是在事务提交之前并不会释放共享锁，也就是必须等到事务执行完成之后，才释放共享锁；另一种说法是利用MVCC（多版本并发控制）

在序列化的级别下，锁定整个范围的表，并且一直持有锁，直到事务完成。

7、Mvcc

a.Mvcc是什么？

多版本并发控制是用来解决读-写冲突的无锁并发控制。为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读取该事务开始前的数据库的快照。

b.当前读和快照读的区别？

当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁；

快照读：像不加锁的select操作就是快照读，即不加锁的非阻塞读，快照度可能读到的不一定是最新版本，而有可能读到的是历史版本；

c.MvCC的好处？

1.在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库的并发读写的性能

2.同时还解决了脏读、幻读及不可重复读等问题，但是不能解决更新丢失问题。

d.MvCC是如何解决脏读、幻读和不可重复读的问题的？

六、场景面试题

1.索引相关

1.假设现在有一张表，有两个字段，第一个字段name,第二个字段age; where name like '王%' and age>25;此时对两个字段建立联合索引，能不能实现；

这样两部分的索引都能够用到；

2.假设现在有一张表，有两个字段，第一个字段age,第二个字段name; where age>25 and name like '王%';此时对两个字段建立联合索引，能不能实现；

这样的话，只能用到前半部分的索引，不能够用到后半部分的索引。

3.假设现在有一张表，有两个字段，第一个字段name,第二个字段age; where name like '%帅' and age>25;此时对两个字段建立联合索引，能不能实现；

这样的话，两个字段的索引值均会失效，因为第一个字段是以通配符的形式，会导致索引失效。

4.组合索引a,b,c两个语句，一个是select * from table where b=1 and c=2;一个是select a from table where b=1 and c=2哪个会走索引？

第一个不走索引，第二个会走索引；

因为联合索引会建立a,ab，abc三个索引，而a索引相当与建立单独索引，mysql是可以不用读data,直接通过索引返回结果值；而一旦使用select*,就会有其他列需要读取，在读取完索引后，找不到其他的数据。

5.select a和select *有什么区别？

select * 表示查询满足条件的所有数据，表所有字段都能够进行显示；

select a表示满足条件的字段a的数据，表中只有a字段进行显示。

6.对a,b,c字段建立联合索引，则select * from table where c=4 and b=6 and a=3;能不能用到索引？

都能够走索引，因为where里面的条件顺序在查询之前会被mysql自动优化，因此会都用到索引。

7.sql中的in会走索引吗？

in的范围/条件较少时，会走索引；

in的范围/条件过大时，会导致索引失效。

8.sql中的or会走索引吗？有什么解决方法？

有or的话会导致索引失效，而进行全表扫描，因为无法进行优化；

解决方法：用union来代替where中的or或者是用in来替换or(只能针对小范围数据)。

9.为什么不使用最左前缀索引会失效？

因为假设建立（a,b,c）三个字段，相当建立了a,ab,abc三个B+树结构，而如果我判断条件为where b=1 and c=2的话，则根本没有b,c，bc这样的B+树结构来实现索引，因此只能是索引失效。

10.假如表有a,b,c三个单列索引，select a,c from tb where b=1,具体使用到的索引是哪个？以及数据查询的具体过程？

使用索引：b单列索引+主键索引

判断条件里面有b=1,又因为存在三个单独索引，因此会使用到b单列索引，具体的执行过程：首先是有三个单列索引<a>,<b>,<c>,默认id是主键索引，说明该表上有一个主键索引<id>，三个非主键单列索引。也就是建立了4棵B+树，然后查询语句：select a,c from tb where b=1;先去<b>这棵树找到对应的id，然后利用id，通过主键索引，查询到a,c；通过<id>这个主键索引B+树查询到a,c的过程就是回表。

2.自增主键

1.如果一个自增表有17条数据，删了两条，重启数据库，增加一条，id是多少？

如果删除的是最后两条数据：

如果采用的是Innodb存储引擎，则记录id为16，因为Innodb表只把自增主键的最大ID记录在内存中。重启数据库会是最大的ID丢失；

如果是MyIsam存储引擎，则id为18；因为MyISAM会把自增主键的最大ID记录到数据文件中，重启不丢失；

如果删除的是中间的两条数据：

则无论哪种引擎，重启不重启都是18，因为自增主键的最大ID都是最大ID。

3.如何保证数据库与缓存中的数据一致？

写操作：先删除缓存，再更新数据库；

读操作：先从缓存中读，如果缓存中没有，再去数据库中查找，然后更新缓存；

并且：需要将数据库更新操作加入到队列中后，然后将对同一个数据的读取操作和更新缓存添加到此队列中，确保数据的一致性；

4.设计一个数据库，你会从哪些方面考虑？

①满足数据库的三大范式：

b.第一范式

第一范式确保每列的原子性，如果每列是最小的执行单元，则满足第一范式

c.第二范式

第二范式要求首先满足第一范式，非主键列完全依赖于主键，不能依赖于主键的一部分；

d.第三范式

第三范式要求首先满足第二范式，非主键列只依赖于主键，而不依赖于其他非主键。

②选择合适的存储引擎

判断是否支持事务，如果支持事务，则选用Innodb执行引擎；

③考虑是否创建索引

④考虑并发操作下数据的一致性问题

合理设置事务的隔离级别

⑤命名的规范及字段的设置问题

增强可读性；

字段是否为空，字段是否为主键等；

⑥数据类型的选择

选择的话，在满足实际需求的情况下，尽量选择数据范围比较小的数据类型。

5.数据库过大，怎么处理？

采用分库分表的方法进行处理，采用数据切分的方式将数据分散存储到多个数据库中，提升数据库的性能。

1.分库：水平切分和垂直切分

2.分表：水平切分和垂直切分

6.在使用MySQL建表时应该考虑到什么？

①存放的数据类型

②命名的规范

③数据类型的选择

在满足需求的情况下，尽量选择小范围的数据类型；

④考虑设置主键、字段是否为空

⑤考虑索引相关

7.MySQL查询速度慢，分析原因并给出解决方案；

①没有创建索引或者是创建了索引没有用到（索引失效）

我们可以通过explain命令查看其中的key字段，能够发现是否真正用到了索引；

如果没有用到索引，可以使用索引，加快查询速度；

如果是索引失效，则优化查询条件，进行解决。

②数据量比较大，并且存在较多的子查询或关联查询；

我们可以采用分库分表的方式进行优化

③可能我们使用了锁，当前的查询语句被阻塞于锁，导致查询较慢；

采用主从复制读写分离的方式进行优化；

④没有用到Redis非关系型数据库

我么可以将热点的，查询频次比较高的数据放入到Redis中，加速查询

⑤硬件或者网络问题

1.磁盘：转速较低，读写速度慢；通过升级硬件：更换转速较高的磁盘或者使用固态

2.网络网速较慢：提升网速；

8.给定一张表，Table A(user_id,order_id,is_overdue,repaid_amount),分别对应用户ID，订单号，逾期标识，待还款金额

1.查询逾期次数最多的100名用户

#因为每个用户可能存在很多个订单，然后可能有的订单逾期了，有的没有逾期，因此需要通过用户id进行分组; select user_id,count(order_id) as c form table a where a.is_overdue=true group by user_id order by c desc limit 100;

2.查询逾期用户人均欠款数量

#因为有的用户存在逾期订单，有的不存在逾期订单，因此需要先确定欠款用户的数量，然后用总的待还款金额/欠款用户数量 select sum(repaid_amount)/count(distinct(user_id)) from table a where a.is_overdue=true; #采用avg函数进行编写 select avg(repaid_amount),count(distinct(user_id)) from table a where a.is_overdue=true;

七、JDBC相关

1.JDBC连接过程

1.加载JDBC驱动程序

首先需要加载想要连接的数据的驱动到jvm,通过java.lang.Class类的静态方法forName()实现

2.创建数据库的连接

a.要连接数据库，需要向java.sql.DriverManager请求并获得Connection对象，该对象就代表一个数据库的连接。

3.创建一个preparedStatement实例

要想执行SQL语句，必须获得java.sql.Statement实例

4.执行SQL语句

Statement接口提供了三种执行SQL语句的方法：executeQuery、executeUpdate和execute方法；

5.返回结果集

①执行更新返回的是本次操作影响到的记录数；

②执行查询返回的结果是一个ResultSet对象；

6.关闭数据库的连接

操作完成后要把所有的使用JDBC对象全部关闭，释放JDBC资源；

①首先关闭requestSet;

②关闭preparedStatement

八.Redis相关

1.redis的数据结构？

①String→char类型数组

②Hash→Hash Table

③list→LinkedList

④Set→Set集合

⑤Sorted Set→跳表

九.数据库的分库分表

1.数据切分

①目的：关系型数据库性能瓶颈，当单边的数据量达到1000W以后，即使添加了从库、优化索引，很多操作时性能依然下降，切分的目的就是在于减少数据库的负担，缩短查询时间。

②定义：数据切分就是将数据分散存储在多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能，从而达到提升数据库操作性能的目的。

2.垂直分库

垂直分库就是根据业务耦合度，将关联度低的不同表存储在不同的数据库，做法与大系统拆分为多个小系统类似；

3.垂直分表

垂直分表是基于数据库中的“列”进行，某个表字段较多，可以扩展为一张表，将不经常用的或者字段长度较大的字段拆分出去到扩展表中。

优点：通过大表拆小表的方式，避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销；

4.水平切分

①什么时候进行水平切分？

当无法进行垂直切分，或者切分后数据量行数巨大，存在单库性能瓶颈；

②定义

根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或者多个表中，每个表中只包含一部分数据，从而使得单表减小。

十、MySQL日志

1.日志的作用

redo log:确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久化。

undo log:保存了事务发生之前的数据的一个版本，以便在事务失败时，能够进行回滚。

bin log:用于复制，在主从复制中，从库利用主库上的bin log进行重播，实现主从同步。

2.常见日志

redo log（重做日志），bin log(归档日志)和undo log(回滚日志)

3.redo log

作用：用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交，都会记录下来。如果出现数据库掉电等情况，innodb存储引擎会使用redo log恢复到掉电前的时刻，确保数据的完整性。

执行过程：在一条语句更新的时候，Innodb引擎会把更新的记录写到redo log中，然后再更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘文件中。

redo log日志大小是固定的，记录满了以后就从头循环写。

4.undo log

作用：提供回滚和多版本并发控制（MVCC）

过程：当数据修改的时候，不仅需要记录redo,还记录了相对应的undo,如果因为某些原因导致事务失败，可以实现回滚。记录的是逻辑日志，如当delete一条记录时，undo log中会记录一条对应的insert记录。当执行回滚时，就可以从undo log中的逻辑记录读取到相应的内容进行回滚。

undo log采用segment的方式进行记录。

5.bin log

属于MySQL Server层面的，称为归档日志，是以二进制的形式记录的执行语句的原始逻辑。这样在数据库用别的存储引擎的时候可以达到一致性要求。

2.日志格式

①row:记录每行修改的记录，产生大量的日志内容

②statement:记录每一条修改的数据的SQL语句；

③mided:两种方式的结合。

6.redo log和undo log的区别

1.作用不同：

redo log主要是保证数据的持久化与完整性；undo log主要是提供回滚，当事务失败时，能够回到事务执行前的状态；

2.存储内容不同

redo log主要是记录的物理数据；而undo log记录的是逻辑数据；

3.记录方式不同

redo log记录满了从头开始写；而undo log采用segment的方式进行记录。