MySQL贼基础面试题

MySQL数据库

主从同步

什么是 MySQL 主从同步

当 master(主)库的数据发生变化的时候，变化会实时的同步到slave(从)库。

主从同步有什么好处

1、水平扩展数据库的负载能力
2、容错，高可用。Failover/High Availability
3、数据备份

关系型数据库

MySQL（端口号：3306），Oracle，SQLserver，DB2，SQLlite

非关系数据库（NoSQL）

Redis（6379），MongDB

连接数据库

update user set password=password('123456')where user='root'; --修改密码
flush privileges; --刷新数据库
show databases; --显示所有数据库
use dbname；--打开某个数据库
show tables; --显示数据库mysql中所有的表
describe user; --显示表mysql数据库中user表的列信息
create database name; --创建数据库
use databasename; --选择数据库

exit; --退出Mysql
? --命令关键词 : 寻求帮助
-- 表示注释

数据库常用命令

creat database 'mybatis';
use 'mybatis';
creata table 'user'(
	'id' int(10)not null,
    'name'varchar (20)default null,
    'pwd'varchar(20) default null;
)engine=innodb default charset=utf8;

1. 创建数据库

   CREATE DATABASE [IF NOT EXISTS] webos;
   

2. 删除数据库

   DROP DATABASE [IF EXISTS] webos;
   

3. 查看所有的数据库

   SHOW DATABASES;
   

4. 使用数据库

   SQLUSE student;
   命令行连接数据库 mysql -uroot -p
   
   show databasses;查看所有数据库
   
   Database changed;
   
   show tables; 查看所有数据库中的表
   
   describe student ;显示数据库中所有表的单位
   
   create database  westos; 创建一个数据库
   
   DESC student 查看表的结构
   
   exit;退出连接
   

使用SQL创建数据库

create table if not exists "student"(

  		'id' int(4) not null auto_increment comment '学号',

	   'name' varchar(30) not null default '匿名' comment '姓名',

	   'birthday' datatime default null comment '出生日期',

		primary key('id')

)

操作表

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE '表名' (
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释]
)[表类型][字符集设置]

SHOW CREATE DATABASE student; -- 显示建数据库语句
SHOW CREATE TABLE student; -- 显示建表语句

修改删除表

ALTER TABLE student RENAME AS teacher; -- 修改表名
alter table 旧表名 rename as 新表名

ALTER TABLE teacher ADD sex INT(11); -- 添加字段
alter table 表名 add 字段名 字段属性

ALTER TABLE teacher MODIFY sex VARCHAR(11); -- 修改字段属性
alter table 表名 modify 字段名 字段属性
ALTER TABLE teacher CHANGE sex sex1 VARCHAR(12);-- 修改字段名和属性
alter table 表名 change 旧字段名 新字段名 字段属性[]

ALTER TABLE teacher DROP sex1; -- 删除表的字段
alter table 表名 drop 字段名

插入语句 insert

insert into 表名([字段名1,字段名2,字段3])value('值1','值2',....)

一般写插入语句，我们一定要数据和字段一一对应；

插入多个字段：

insert into 'grade'('gradename')

values('大二'),('大一')

修改 update

update 表名 set colnum_name=value,[colnum_name=value] where [条件]

update 'student' set 'name'='名字' where id =1

注意事项：

colnum_name 是数据库的列，尽量带上’ ';

条件，删选的条件，如果没有指定，则会修改所有列；

value，是一个具体的值，也可以是一个变量；

多个设置的属性之间，可以用英文逗号隔开；

删除

DROP TABLE teacher; --删除表
drop table if exists 表名

delete from ' 表名'[where 条件]

清空表：truncate ‘表名’

delete和truncate的区别：相同点：都能删除数据，不会删除表的结构

不同：truncate 重新设置自增列计数器会归零，不会影响事物；

查询数据select

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查询的记录从哪条至哪条

指定查询字段（可以取别名as）：

select 字段（as 别名） from 表（as 别名）

去重distinct

查询一下有那些同学参加了考试

select * from result（所有的信息）

select distinct ‘列名’ from result 发现重复数据，去重

输出相同的having

where 条件字句

查询考试成绩在95–100中的数据

select 'studentNo', 'studentresult' from result

where studentresult between 95 and 100

查询除了1000号学生之外的同学成绩

select 'studentNo', 'studentresult' from result

where studentNo !=1000;

模糊查询：比较运算符

like 中%（代表0到任意字符）_(代表后面有一个字符) SQL匹配，如果a匹配b，则结果为真

in 假设a在a1中，或者a2…其中的一个值中，结果为真

分页和排序

分页 limit 和排序order by

排序 升序 asc ，降序desc

分页语法limit 起始值，页面大小，例如：limit 0， 5

UPPER是大写

as是重命名

subString是取字符

contact是连接

SQL的执行顺序：

第一步：执行FROM

第二步：WHERE条件过滤

第三步：GROUP BY分组

第四步：执行SELECT投影列

第五步：HAVING条件过滤

第六步：执行ORDER BY 排序

MySQL的事务隔离级别有哪些，分别用于解决哪些问题

隔离级别有：读未提交，读已提交，重复读，序列化；

主要用于解决脏读，不可重复读，幻读

脏读：一个读取到另一个事物还未提交的数据

不可重复读：在一个事物中多次读取到另一个数据时，结果出现不一致

幻读：在一个事物中使用相同的SQL两次读写，第二次读取到了其他事物新插入的行

隔离级别	脏读	不可重复读	幻读
读已提交	有	有	有
读未提交	无	有	有
可重复度	无	无	有
串行化	无	无	无

MySQL的可重复读怎么实现的

使用MVCC实现，就是多版本并发控制；

InnoDB在每行记录后面保存数据行的创建版本号和删除版本号，每开始一个新的事物，系统版本号都会递增，事物开始时刻的版本号会作为事物的版本号，用来查询每行记录的版本号对比；

MVCC解决了幻读没有

幻读：在一个事物中使用相同的SQL两次读取，第二次读取到了其他事物新插入的行，交幻读

谈到幻读，首先我们要引入“当前读”和“快照读”的概念，聪明的你一定通过名字猜出来了：

快照读：生成一个事务快照（ReadView），之后都从这个快照获取数据。普通 select 语句就是快照读。

当前读：读取数据的最新版本。常见的 update/insert/delete、还有 select … for update、select … lock in share mode 都是当前读。

对于快照读，MVCC 因为因为从 ReadView 读取，所以必然不会看到新插入的行，所以天然就解决了幻读的问题。

而对于当前读的幻读，MVCC 是无法解决的。需要使用 Gap Lock 或 Next-Key Lock（Gap Lock + Record Lock）来解决。

什么是索引

索引（index）是帮助MySQL获取数据的数据结构，简单的理解就是类似字典里面的目录

常见的索引有哪些

常见的索引有：hash、b树、b+树；

hash：底层就是hash表，进行查找时，根据key调用hash函数或得对应的hashcode，根据hashcode找到对应的数据行地址，根据地址拿到对应的数据

B树：B树是一种多路搜索树，n路搜索树代表每个节点最多有n个子节点，每个节点存储 key + 指向下一层节点的指针+ 指向 key 数据记录的地址。

B+树：B+树是b树的变种，主要区别在于：B+树的非叶子节点只存储 key + 指向下一层节点的指针。另外，B+树的叶子节点之间通过指针来连接，构成一个有序链表，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。

为什么MySQL数据库要使用B+树存储索引

红黑树：如果在内存中，红黑树的查找效率比B树搞，但涉及到磁盘，B树就更加优秀。因为红黑树是二叉树，数据量大数的层数很高，从树的根节点向下寻找的过程的，每读一个节点，都相当于一次IO操作，因此红黑树的I/O操作会比B树多得多

hash索引：如果只是查询单个值，hash索引的效率非常高，但是有几个问题：不支持范围查询、不支持索引的排序操作。不支持联合索引的最左匹配规则

B树索引：相比于B+树，需要做局部中序遍历，可能要跨层访问，跨层访问就意味着要进行额外的I/o操作；

MySQL中索引叶子节点存放的是什么

MyISAM和InnoDB都是采用B+树作为索引结构，但是叶子节点的存储上不同；

MyISAM：主键索引和辅助索引的叶子节点都是存放key和key对应的数据行的地址，在MyISAM中主键索引和辅助索引没有区别；

InnoDB：主键索引存放的是key和key对应的数据行，辅助索引存放的是key和key对应的主键值；

什么是聚集索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式。聚簇索引将索引和数据行放到了一起，找到了索引也就找到了数据，无需进行回表操作，所以效率很高；

什么是回表查询

InnoDB中，对于主键索引，只需要走一遍主键索引的查询就能找到叶子节点拿到数据，而对于普通索引，叶子节点存储的是key+主键值，因此需要再走一遍主键索引，通过主键索引找到行记录，这就是回表查询，先定为主键，在定位行记录；

走普通的索引一定会出现回表查询吗

不一定

B+树中一个节点大小为多大

1页，也就是16k

为什么一页就够了

对于叶子节点：我们假设1行数据大小为1k（对于普通业务绝对够了），那么1页能存16条数据。

对于非叶子节点：key 使用 bigint 则为8字节，指针在 MySQL 中为6字节，一共是14字节，则16k能存放 16 * 1024 / 14 = 1170个。那么一颗高度为3的B+树能存储的数据为：1170 * 1170 * 16 = 21902400（千万级）。

所以在 InnoDB 中B+树高度一般为3层时，就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次 IO 操作即可查找到数据。千万级别对于一般的业务来说已经足够了，所以一个节点为1页，也就是16k是比较合理的。

InnoDB的四大特性

插入缓冲、二次写、自适应哈希索引、预读

说一下共享锁和排他锁

共享锁又称为读锁：就是多个事物对于同一数据可以共享一把锁，都能够访问到数据，但是只能读不能修改；

排它锁又称写锁：排他锁是不能够跟其它锁并存，如果一个事务获取了一个数据的排他锁，其他事物就不能获取该行的其他锁。但是排它锁可以对数据进行读取和修改；

说说数据库的行锁和表锁

行锁：操作是只锁某一行，不多其他行有影响，开销大，加锁慢，会出现死锁，发生锁冲突的概率低，并发度高；

表锁：及时操作数据一条记录也会锁住整个表，开销小，家锁块，不会出现死锁，发生锁的冲突概率高，并发度最低；

页锁：操作时锁住一页数据，开销和加锁速度介于行锁和表锁之间，会出现死锁；

InnoDB的行锁是怎么实现的

是通过索引上的索引项来实现，意味着只有通过索引条件检索数据，InnoDB才会使用行锁

InnoDB锁的算法有哪几种

Record lock：记录锁，单条索引记录上加锁，锁住的永远是索引，而非记录本身。

Gap lock：间隙锁，在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

Next-key lock：Record lock 和 Gap lock 的结合，即除了锁住记录本身，也锁住索引之间的间隙。

MySQL如何实现悲观锁和乐观锁

乐观锁：更新时带上版本号；

悲观锁：使用共享锁和排它锁

对比项	InnoDB	MyLSAM
事务	支持	不支持
锁类型	行锁，表锁	表锁
缓存	缓存索引和数据	只缓存索引
主键	必须要	可以没有
索引	B+树，主键是聚簇索引	B+树，非聚簇索引
hash索引	支持	不支持
外键	支持	不支持
关注点	事务	性能

存储引擎的选择

没有特殊情况下，选择InnoDB，如果表大部分都只是查询数据，可以考虑MyISAM

InnoDB支持事物，支持行级锁，支持外键

MyISAM不支持事务，支持表级锁

MyISAM存储表有两个文件，MYD数据文件和MYI索引文件；InnoDB只有一个文件；

type有哪些常见的值

按类型排序，从好到坏，常见的有：const > eq_ref > ref > range > index > ALL。

const：通过主键或唯一键查询，并且结果只有1行（也就是用等号查询）。因为仅有一行，所以优化器的其余部分可以将这一行中的列值视为常量。

eq_ref：通常出现于两表关联查询时，使用主键或者非空唯一键关联，并且查询条件不是主键或唯一键的等号查询。

ref：通过普通索引查询，并且使用的等号查询。

range：索引的范围查找（>=、<、in 等）。

index：全索引扫描。

All：全表扫描

如何做慢SQL优化

首先要搞明白慢的原因是什么：是查询条件没有命中索引？还是 load 了不需要的数据列？还是数据量太大？所以优化也是针对这三个方向来的。

首先用 explain 分析语句的执行计划，查看使用索引的情况，是不是查询没走索引，如果可以加索引解决，优先采用加索引解决。

分析语句，看看是否存在一些导致索引失效的用法，是否 load 了额外的数据，是否加载了许多结果中并不需要的列，对语句进行分析以及重写。

如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行垂直拆分或者水平拆分。

MySQL的一级缓存和二级缓存

一级缓存：
一级缓存也称本地缓存，session级别的缓存，一级缓存是默认开启的，与数据库同一次的会话期间查询到的数据会放在本地缓存中，如果有需要获取相同的数据，则直接从缓存中取，就不会再次查询数据库。在日常的开发中，经常会有相同的sql执行多次的情况，mybatis就提供了一级缓存来优化这些查询，避免多次请求数据库，重点是它的作用域为一次sqlSession会话。

二级缓存：
二级缓存是全局缓存，是一个基于namespace级别的缓存，作用域更为广泛，不局限于一个sqlSession，可以在多个sqlSession之间共享，mybatis的二级缓存默认也是开启的，但是由于它的作用域是namespace，所以还需要在mapper.xml中开启才能生效，只需要加入cache标签即可。当要访问数据库形成一个会话查询一条数据时，这个数据就会被放在当前会话的一级缓存中，如果这个时候会话关闭了，那么以及缓存中的数据会被保存到二级缓存中，新的会话查询信息就会参照二级缓存，需要注意的是不同的namespaace会被防止到自己对应的缓存中去。
小结：
简单来说就是缓存首先一进来，就会先去二级缓存中查询，如果没有就去一级缓存中查找，一级缓存中没有的话，最后进入数据库中查找。作用顺序为二级缓存>一级缓存>数据库。