这里是引用 MySQL数据库面试题（2020最新版）
这里是引用MySQL常用知识点详解
提升mysql性能可从，索引，锁，缓存入手；特大数据分表分库
一、索引
1.索引是什么
索引：帮助数据库高效获取数据的排好序的数据结构，主要用B+树(哈希范围查找不行)，原因是它矮胖；索引像目录，便于查询，是个文件，占物理空间
索引覆盖：要查询的字段都有索引，引擎就不用访问原始数据，直接在索引表查询，在select后加要查询的字段
2.索引类型
按照存储结构分为：BTree B+Tree Hash索引
按照索引行是否等值：唯一和普通
按照叶子节点存储内容划分为：聚集索引和非聚集索引
索引分类
主键索引：一表一个，不空，不重（可以有多个字段，列；主键：用于唯一标识表中某条记录（列或属性的组合））
唯一索引：一表可多个，不空，不重
alter table student add unique (column1,column2)
普通索引：可多可空 unique----index
全文索引：fulltext
组合索引：两个或多个列
主键索引：B+树中，内部节点都是键，叶子节点放所有的键和值（整行数据信息，id name age）
辅助索引：B+树中，内部节点放的不是主键id，而是普通列name ，叶子节点上放的是所有的name 和相应的主键，name不是命中字段需要进行回表，普通索引，找到主键，主键查询到信息
select * from table1 where name = “zhangsan” 需要回表，通过name索引到主键，再通过回表查张三的所有信息
select id from table1 where name = “zhangsan” 命中，不需要回表
创建索引：create table, alter table, create index on(不能创主键)
删除索引：alter table student drop (primary)key name;
3.索引优点
提速，随机IO变顺序，服务器避免排序和建临时表
4.索引用处
可以加快数据访问
5.最左前缀法匹配，使用组合索引时，从最左列开始匹配
使用多个字段做索引，组合索引，从最左列索引开始匹配，遇到范围查询停止匹配（字段顺序随便写，优化器会调节）
select * from table1 where name = “zhangsan” and age = 20;
where后面的顺序随便写，但必须包含最左列，否则不走索引，比如组合索引
name,age,gender
select * from table1 where age = 20,gender = 女
不走索引，因为不是从name开始
select * from table1 where name = 20,gender = 女
命中一个字段，只会使用索引name
没有age，对于gender来说不符合最左前缀，因此gender不走索引
6.有没有使用索引—explain很关键
用explain查看执行计划，如果key为null没走索引
type:
访问类型
ALL: 全表扫描
index: 索引扫描，如果extra是using index 正在使用覆盖索引
range: 范围扫描
extra:
using index:使用覆盖所有
using where:检索后再过滤
7.索引失效
单列：
对索引列操作或者判断，索引写错，字符串所以要加引号，模糊查询like 后先写%
多列：
不符合最左匹配时索引会失效，遇到范围查找，or会失效
8.批量快速插删
待续……
B和B+
B:所有节点都有键和值，叶子节点相独立；把频繁访问的数据放树根效率大大提高，因此，便于特定数据重复查询
B+：内部节点只放键，叶子节点有键和值，且互通；一次读取，可在内存叶获取更多键，可快速缩小范围，且叶子互通，遍历数据更方便;且索引文件占空间，以索引文件存储在磁盘，会产生I/O消耗，B+中内部节点无值，内部节点比B树小，可容更多键，一次性读入内存键更多，I/O读写次数少。叶子互通使它查找效率更稳
哈希和B+
哈希表：通过键值找数据，更快高效，但无法范围查找
B+:从根节点出发，由于左小右大，二分判断，快速缩小范围，找到目标范围和值
9. innobd和Myisame终结者
innobd的聚集和非聚集
聚集：叶子节点上有索引和全部数据信息
非聚簇（辅助，二级）：叶子节点上是索引和主键行号，根据行号再回表进行聚集索引叫回表
如果有覆盖索引，并且命中需要查的字段，就不用回表了。
innodb：支持事务，行级锁和外键
myisame的主键和辅助都是非聚集
索引和数据信息分开存储，叶子节点是索引和数据在磁盘上的地址
10.索引目的，性能分析，原则，适宜场景
通过索引查询要比全表扫描快，但也要付出代价
创建费时
占内存，索引是文件，占用物理空间
需维护，当表中增删改时，索引也要修改
原则：
为使用频繁的、可以缩小查询范围的字段创建索引
选择唯一索引
为经常需要排序、分组、联合操作的字段创建索引
适用于频繁的，可以缩小查询范围的字段，或者
二、事务
不可分割的数据库操作序列，数据库并发控制的基本单位，逻辑上的一组操作，要么什么都不执行，要么都执行
ACID
原子性： 最小的执行单位，事务所有的操作不可再分，要么全成功，要么全失败
一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性： 多事务并发执行，互不影响
持久性： 事务被提交后。对数据库中数据的修改被永久保存，即使数据库发生故障也不应该对其有任何影响。
原子性实现：通过undo log实现回滚，操作数据前，先备份到undo log,出错后用rollback可以利用undo log将数据恢复到事务开始前的状态，undo log记录的是历史版本状态，逻辑日志，当你增删改的时候，它同时记录删增反向改
隔离性实现：MVCC
持久性实现：通过redo log
脏读，幻读，不可重复读
脏读：一个事务读到另一个事务没有提交的数据 read uncommitted
幻读：两个事务操作一张表，事务A前后两次读取数据不同，由于B在中间插或删了一番；Repeatable Read导致
不可重复读：两个事务操作一张表，事务A前后两次读取数据不同，由于B在中间更改数据；
幻读和不可重复读：都是一个事务多次读到不同数据；幻读是由于另外一个事务插删引起；不可重复是另一个事务对数据更改引起
四个隔离级别
READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
三、锁
出现并发时，保证有序进行，锁机制
分类：
锁粒度：行级，页级、表级；加锁开销由大到小，速度由快到慢，并发程度也是；发生锁冲突由低到高；只有表级锁不会出现死锁
锁类别：共享锁（读锁可多）和排他锁（写锁仅一）
死锁：多个事务执行时因抢夺资源而造成的相互等待的现象
解决办法：
超时等待：事务等待时，超过设定的阈值就回滚，另外事务继续，缺点回滚费时
等待图：死锁碰撞检测，根据数据库所保存的锁信息链表和事务等待链表两部分构造一张图，出现回路，则说明死锁了，innodb存储引擎选择回滚undu量最小的事务
乐观锁和悲观锁
主键、外键、超键、候选键
主键：一表一个，不空，不重（可以有多个字段，列；主键：用于唯一标识表中某条记录（列或属性的组合））
外键：一个表中含有另一个表的主键
超键：包括候选键和主键，可以唯一标识元组属性集合——可一可多，比如（id）(id + 姓名)； （姓名) 就不是了
候选键：最小的超键，没有冗余的超键
约束
1.主键约束——非空、不重、唯一 ； 用于控件字段内容不能重复，但它在一个表只允许出现一个。
alter table student add primary key (id,name)
2.外键约束 ——预防破坏表之间连接的动作
alter table student add foreign key (id) reference teacher(id)
3.唯一约束——唯一，不重，可空、可多；控件字段内容不能重复，一个表允许有多个 Unique 约束。
4.非空约束—— 用于控制字段的内容一定不能为空
5.默认约束
三、查询
要查的数据在多个表里
内联 inner join 返回两表联接字段相等的行
select table1.colum ,table2.colum2 from table1 inner join table2 on 1.id = 2.id
外联 left join 以左表为基准，去匹配右表的数据
多表查询
union
select id,name from tb1 union select id, name from tb2;
inner join
select a.id a.name b.score from tb1 as a inner join tb as b on a.id = b.id
left join 返回坐表，右表中有则返，无则null
select a.id, a.name, b.score from tb1 as a left join tb2 as b on a.id = b.id
id name score
1 猴 80
1 猴 90
2 猴 null
char and varchar
char 固定字符串长 最多能放255个字符，存取快，空间利用率不高
varchar 长度可变字符串，最大65532，存取慢，空间利用率高
####mysql中int(10)和char(10)以及varchar(10)的区别
CHAR的长度是固定的，而VARCHAR的长度是可以变化的。
比如，存储字符串“mysql＂，对于CHAR(10)，表示你存储的字符串将占10个字节（包括5个空字符）。
而同样的VARCHAR (10)则只占用5个字节的长度，10只是其最大值限制，当你存储的字符小于10时，按实际长度存储
DDL 和DML DQL
DDL 数据定义 对对象 (表试图索引聚簇等)进行管理，drop creat alter truncate 隐形提交，不支持回滚
DML 数据操作 增删改查 insert delete update
DQL 数据查询 select where
truncate drop delete
truncate 清空表中内容 DDL方法，隐形提交不支持回滚
drop 删除表
delete 删除表中数据，行
like
模糊查询like后紧跟% 可能造成索引失效，覆盖索引可解决
走不走索引要联系B+结构，索引按照顺序查找，没顺序后自然失效，比如最左匹配，因为单列索引和组合索引的区别是在b+树种节点含一个或多个索引值，对于组合索引，最左列有序，后面的都是相对有序
优化查询
慢的原因
硬件，内存满，网络不好，磁盘满
数太多，分库分表
索引失效
查询语句需要优化
where 子句避免判断，操作，判空
避免select*
部分like %等导致索引失效
应该使用explain查看原因
重点看type
type 访问类型
const
eqref 主键唯一
ref join或者组合
range 范围
index 索引扫描
all 全表扫描
key 索引类型
key_len
rosw 扫描行数
extra 中有using index表示覆盖索引
缓存机制
Mysql的缓存机制：将sql文本和查询结果进行缓存，遇到相同sql直接从缓存取结果，不用再运行sql；但表结构发生改变，缓存失效，即insert delete update truncate altertable drop table
使用内存池而非操作系统进行内存管理，对查询语句进行hash计算，把哈希值和查询结果放在Query Cache；使用select语句时，计算其哈希值，和Query Cache里的哈希值进行比较，相同直接取结果
优化
explain关键字
五个原则：
1.减少数据访问
2.返回更少数据
3.减少交互次数
4.减少CPU开销
5.利用更多资源
最大化利用索引
避免全表扫描
减少无效数据查询
分库分表
当数据量太大，查找效率降低，对数据分库分表
实操
/*
create database school;#创建数据库
show databases;#显示数据库
use school;#进入数据库
show tables;#显示数据库表
#创建数据库表
create table student(
sid VARCHAR(20) ,
sname VARCHAR(20),
ssex char(4),
birthday datetime
);
#数据库表的描述和详细内容
describe student;
select * from student;
#删除
drop table student;
#在表里面添加内容
insert into student VALUES(‘101’,‘张三’,‘男’,‘2001-03-20 08:12:52’);
insert into student VALUES(‘102’,‘李四’,‘男’,‘2002-03-20 08:12:52’);
insert into student VALUES(‘103’,‘王五’,‘男’,‘2003-03-20 08:12:52’);
insert into student VALUES(‘104’,‘老六’,‘男’,‘2004-03-20 08:12:52’);
insert into student VALUES(‘106’,‘张三’,‘男’,‘2001-03-20 08:12:52’);
select * from student;
delete from student where sname =‘张三’;
update student set sname = ‘小许’ where sname = ‘老六’;
2.增删改查
2.1增加
insert into student VALUES
2.2删除
delete from student where sname ='张三';
2.3改 update set
update student set sname = '小许' where sname = '老六';
2.4查
select * from student;
alter table student add primary key(birthday);
alter table student drop primary key;
alter table student add unique (sname);
alter table student drop index sname;
3.主键约束（唯一、不可空，一张表里只能有一个主键约束）
基本操作 sid VARCHAR(20) primary key,
联合约束 primary key(sid,name),和起来不重复就ok
自增约束 sid VARCHAR(20) primary key auto_increment,
=添加约束 alter table student add primary key(sid),
删除alter table student drop primary key，
修改alter table student modify sid int primary key,
3.1唯一约束（可多，可空），避免某字段重复，添加方式同主键
删除alter table student drop index name;
3.2非空约束 后面加 not null；
3.3默认约束 字段未传值，用默认
age int detault 10;
3.4外键约束
两个表：子，父
class_id int,
foreign key(class_id) references classes(id)
*/
/*
create table classes(
id int primary key,
name varchar(20)
);
create table students(
sid int PRIMARY key,
sname varchar(20),
class_id int,
foreign key(class_id) references classes(id)
);
drop table students;
drop table classes;
select *from classes;
select *from students;
describe classes;
describe students;
insert into classes values(1,‘西电2019’);
update classes set name = ‘西电2019’ where name = ‘西电’;
insert into classes values(2,‘西电2020’);
insert into classes values(3,‘西电2021’);
insert students values(101,‘小阳’,1);
insert students values(102,‘小宁’,2);
insert students values(103,‘小悦’,3);
/*4.三大范式
4.1列不可再分
4.2一的基础上，每列完全依赖主键，否则拆表；
4.3除主键列，其他列不能有传递依赖，否则造成数据冗余
事务
最小的不可分割的工作单元，保证业务的完整性,多条sql语句要么同时成，要么同时败；例如银行转钱，转出和收入是一个事务
怎么控制事务？
特点：
A 原子性，事务是最小单位，不可再分割
C 一致性， 同一事务中的sql语句，保持同时成功或失败
I隔离性，两个事务具有隔离性
D持久性，事务一旦结束，不可以返回
事务开启：
1.默认提交
2.begin
3.start transaction
事务手动提交
commit
事务手动回滚
rollback
隔离性
read uncommitted 造成脏读 一个事务读到另一个事务没有提交的数据 脏读
read committed 造成不可重复读 读到一个事务提交的数据，读同一个表前后不一致 不可重复读
repeatable read 造成幻读 两个事务操作一张表，事务A提交的数据不能被事务B读到， 幻读
serializeble 造成性能差 当一个事务在操作时，其他事务不可以操作，进入排队状态(串行化)，直到该事务提交commit
1自动提交，手动提交，事务回滚，（不能撤销）持久性；银行转账，手动提交，成功commit，否则回滚
默认自动提交，回滚rollback 不能撤销；除非更改为0 set autocommit = 0; 但是手动提交，即插入后加一个 commit;也是不能回滚的；体现了事务的持久性
2.在默认开启事务时，可以手动开启事务
begin 或者 start transaction （小开关，set autocommit 总开关）
*/
/* 查询操作 */
#数据准备
show databases;
use school;
show tables;
drop table students2;
#成绩表
create table students2(
student_id bigint primary key,
name varchar(20),
age bigint,
classid bigint
);
#课程表
create table course(
course_id bigint primary key,
course_name varchar(20),
id bigint,
foreign key(id) references teacher(id)
);
drop table score;
#分数表
create table score(
score bigint ,
id bigint,
cid bigint,
foreign key(id) references students2(student_id),
foreign key(cid) references course(course_id),
primary key(id,cid)
);
insert into score VALUES(91,102,333);
insert into score VALUES(99,101,111);
insert into score VALUES(98,102,222);
insert into score VALUES(97,103,333);
insert into score VALUES(88,101,222);
insert into score VALUES(66,102,111);
insert into score VALUES(55,103,111);
#教师表
create table teacher(
id bigint primary key,
name varchar(20),
part varchar(20)
);
describe students2;
select *from students2;
select *from teacher;
select *from course;
select *from score;
insert students2 values(109,‘小9’,16,20);
insert students2 values(108,‘小8’,16,20);
insert students2 values(109,‘小6’,16,12);
insert students2 values(105,‘小5’,16,21);
insert students2 values(101,‘小阳’,19,7);
insert students2 values(102,‘小宁’,20,8);
insert students2 values(103,‘小悦’,21,9);
insert into teacher VALUES(1,‘张三’,‘数学部’);
insert into teacher VALUES(2,‘李四’,‘物理部’);
insert into teacher VALUES(3,‘王五’,‘太空部’);
insert into course VALUES(111,‘数学’,1);
insert into course VALUES(222,‘语文’,2);
insert into course VALUES(333,‘物理’,3);
select name from students2;
select distinct name from teacher;
*表示所有
select *from score where score between 80 and 100;
select *from score where score in(99,98);
select *from teacher score where id = 1;
#默认升序，descend ascend
select *from students2 order by age desc;
select *from students2 order by age;
select from students2 order by age desc, student_id asc;
#统计
select count()from students2 where age = 16;
#查平均成绩/分组 group by
select avg(score) from score where cid = 111;
select cid,avg(score) from score group by cid;
select cid ,avg(score)from score group by cid having count(cid)=2 ; ##having 在查询后使用
select * from students2 group by age;
#模糊查询 like
#范围查找 between 查询分数大于70小于90的student_id 列
select id from score where score >70 and score<90;
select * from score where score between 70 and 90;
#多表查询 笛卡尔积怎么回事
select student_id,name from students2;
select id,cid,score from score;
select name,cid,score from students2,score where students2.student_id = score.id;
#查询班级学生平均成绩 子查询 in
select student_id from students2 where classid = 9;
select * from score where id in (select student_id from students2 where classid = 9);
#某门课大于某同学成绩的所有成绩
select score from score where cid = 222 and id = 101;
select * from score where score > (select score from score where cid = 222 and id = 101);
#查询张三老师学生的成绩
select * from teacher where name = ‘张三’;
select course_id from course where teacher_id = (select id from teacher where name = ‘张三’);
select * from score where cid = (select course_id from course where teacher_id = (select id from teacher where name = ‘张三’));
#查询选修课多于2人的老师姓名
select count() from score group by cid;
select cid from score group by cid having count() > 2; ##怎么把大于2的信息提取出来
##提取教师表里符合的课程id；返回给name
#从course里提取课程id，返回给课程id; 多层嵌套容易蒙
select name from teacher where id in (select teacher_id from course where course_id in (select cid from score group by cid having count(*) > 2) );
select from course where cid in(select cid from score group by cid having count() > 2);
select *from teacher;
select *from course;
#查询8.9班学生
select *from students2 where classid in (8,9);
#查询物理系教师所有课程的成绩表 一个用等于，多个用in
select score from score where cid in (select course_id from course where teacher_id in (select id from teacher where part = ‘物理部’));
#求并 union 任一any 且 all
#起别名 as name as myname
/*#四种连接查询
内连接 join 表 on
左连接 left join 左表完全显示，右边有则显，否则NULL
右连接 right join
完全连接 full jion 左连接操作 union 右连接操作
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
