面试速记之MySQL-阿南达文事网

面试速记之MySQL

数据类型
- 1.整数
- 2.小数
- 3.日期
- 4.文本、二进制
- 5.字符
sql优化
- 1.sql语句优化
- 2.用索引
- 3.表优化
- 如何优化慢sql
sql语句
- sql四种连接
- - 1.内连接
  - 2.左(外)连接
  - 3.右(外)连接
  - 4.全连接
- 筛选的方法
- - limit
- sql分类
- 删除相关
- 其他
三大范式
mysql逻辑架构
- 查数据过程
存储引擎
- 默认
- 对比
- - 适用场景
索引
- 简介
- 优点
- 缺点
- 分类
- - 主键索引
  - 唯一索引
  - 单列索引
  - 复合索引
  - 全文索引
  - 按是否数据 + 索引？
  - - a.聚簇索引
    - b.非聚簇索引
  - 按是否回表？
  - - a.覆盖索引
    - b.回表操作
  - mysql索引底层
  - - 优点
    - 哈希索引
    - 其他
- 索引优化
- - 索引失效
- 索引的原理
锁
- 按操作数据类型
- - 读锁
  - 写锁
- 按粒度
- - 表锁（偏读）
  - - 加读锁
    - 加写锁
    - 查看表
    - 解锁
    - 查看锁信息
    - 如何锁定某一行
    - 案例
  - 行锁（偏写）
  - - 案例
  - 页锁
- 按数据是否共享
- - 共享锁
  - 排他锁
- 按主观臆断
- - 乐观锁
  - 悲观锁
- 其他锁
- - 间隙锁
  - - 危害
- MySQL锁优化
- 数据库并发场景
- - 读-读
  - 读-写
  - 写-写
- 数据库死锁
- - 举一个数据库死锁的场景
事务
- 四大特性（ACID）
- - 分类
  - - 原子性
    - 一致性
    - 隔离性
    - 持久性
  - 如何保证
  - - 保证原子性
    - 保证一致性
    - 保证隔离性
    - 保证持久性
- 事务存在的问题
- - 脏读
  - 不可重复读
  - 幻读
- 事务隔离级别
- - 读未提交
  - 读已提交
  - 可重复读
  - - MVCC
    - - 当前读
      - 快照读
      - 优点
      - 缺点
  - 序列化
一些问题
JDBC
分库分表
- 数据切分
- 垂直分库
- 垂直分表
- 水平切分
MySQL日志
- redo log（重做日志）
- - 执行过程
- undo log（回滚日志）
- bin log（归档日志）
- - 日志形式
- 区别

数据类型

1.整数

tinyInt 1byte
smallInt 2byte
mediumInt 3byte
int 4byte

2.小数

float 4byte
double 8byte

3.日期

year YYYY 1901-2155
date YYYY-MM-DD
time

4.文本、二进制

tinyblob、blob、smallblob、mediumblob、longblob
tinytext、text、mediumtext、longtext

5.字符

char 定长 0-255
varchar 变长 0-65535

sql优化

1.sql语句优化

不 select *

union 或 union all 代替 or（union可去重，union all不可）

用关联查询(join)代替子查询

2.用索引

数量：每张表<=5

为查询最频繁的字段建

更改频繁的、不能显著区分数据的字段不适合建

3.表优化

字段尽量不为null

单表数据<=300w

字段类型应为符合要求的最小数据类型

禁建预留字段（暂时用不上空在那的）

如何优化慢sql

查原因，明确偶尔慢还是一直慢

偶尔慢考虑
1.数据库在刷新脏页
2.有表锁或行锁，且当前事务未获得锁

补充：mysql脏页是什么

一直慢可以开慢查询，分析慢查询日志
1.未用索引 -> 全表扫描 -> 查询慢
解决：加索引
2.用了索引 -> 索引失效 -> 全表扫描 -> 查询慢
解决：分析失效原因并修复

sql语句

删除
delete from table where

建索引
create index index_name on table(column)

sql四种连接

1.内连接

2.左(外)连接

3.右(外)连接

4.全连接

mysql不支持，可用它法实现

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

筛选的方法

where

group by having

order by

limit

下面两种写法都表示取2、3、4三条数据

select * from article LIMIT 1,3
select * from article LIMIT 3 OFFSET 1

limit 要跳过的数量 , 取的数据的条数
limit 取的数据的条数
limit 取的数据的条数 offset 要跳过的数量

sql分类

DQL：数据查询语言，查
DML：数据操纵语言，增、删、改
DDL：数据定义语言，创建
DCL：数据控制语言，授权、回滚、提交

删除相关

delete
DML，删除行，记录于redo和undo日志，可回滚

truncate
DDL，清空表数据，保留结构，不可回滚

drop
DDL，清除表的数据和结构，不可回滚

其他

起别名
字段 + as + 别名
字段 + 空格 + 别名

去重
distinct

+号只能做运算，不能拼接字符串
拼接用concat(string1,string2)

字符串用单引号，‘abc’

不等：!= 和 <>

查询员工名中第三个字符为e,第五个字符为a的员工名和工资

select * from employees where last_name like '__e_a%';

查询员工名中第二个字符为_的员工名
这里可以采用转义的方法实现
方式一：采用java中的\进行转义
方式二：采用MySQL中的escape “转义符”的方式

select last_name from employees where last_name like '_$_%' escape '$' ;

between and
左右均为闭区间
左小右大，颠倒后无语法错误，无结果

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

order by
可跟表达式和别名

asc 升序（默认）
desc 降序

查询员工信息，要求先按工资升序，再按员工的编号升序

select * from employees order by salary,hiredate;

三大范式

第一：每列不可再分
第二：属性完全依赖于主键
第三：属性不依赖于其他非主属性

mysql逻辑架构

1.连接层

2.服务层
a.系统管理和服务工具
b.SQL接口：接收 + 返回
c.解析器：验证 + 解析
d.查询优化器：优化，提高查询效率
e.缓存和缓冲：缓存读，缓冲写

3.引擎层：管理引擎，存取操作

4.存储层：与引擎层交互

查数据过程

1.检查权限
2.有权限，查缓存
3.缓存无，解析器检查语句合法性
4.若合法，优化器优化语句，检查权限
5.有权限，调存储引擎，返回数据

更新数据过程
1.走一遍查数据过程
2.调执行引擎，把新结果写入内存，更新重写日志，通知执行器
3.执行器更新归档日志并提交

存储引擎

默认

5.5- myisam
5.5+ innodb

对比

引擎	外键	事务	锁支持	缓存	索引	唯一索引
myisam	无	无	表锁	索引	非聚簇	可以有
innodb	有	有	表锁+行锁	索引+数据	聚簇	必须有

myisam 相较 innodb 读速度快，占用资源少

适用场景

引擎	事务	并发	数据更新
myisam	无	低	不频繁
innodb	有	高	频繁

innodb 若未定义主键 -> 生成隐藏主键(6byte的列)

索引

简介

帮数据库快速拿数据的数据结构

实现有B树和B+树

相当于目录

优点

提高查询效率，减少io

降低排序成本

缺点

占用空间

数据变更，索引也需调整，增加耗时，消耗资源

索引优化

避免索引失效

避免冗余索引和重复索引

<=5个/表

联合索引字段顺序按查询频率由高到低

索引失效

explain + sql
关注key和key_len，就能知道索引是否部分/完全失效

假设有联合索引（name，age，pos）

age>11 使 pos索引失效：

不等于会索引失效：

like的情况：

最左前缀匹配原则：

索引的原理

排序 -> 生成倒排表 -> 倒排表拼上数据地址

查询时，先拿倒排表，再取地址链，最后拿数据

锁

按操作数据类型

读锁

对于同一份数据，多个读操作可同时进行

写锁

若当前写操作未完成，会阻断其他读和写操作

按粒度

表锁（偏读）

偏向MyISAM

锁整个表

开销大

加锁慢

不会死锁

加读锁

lock table 表名1 read,表名2 read......

加写锁

lock table 表名1 read,表名2 write......

查看表

可以查看哪个表被加锁了

show open tables;

会返回以下字段：

Database：所在数据库。

Table：表名

In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

mysql> show open tables where in_use >=1;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| MyDB     | test  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

解锁

unlock tablses.

查看锁信息

show status like 'table%';

table_locks_waited：被阻塞的线程的数量，数量越多，系统越阻塞

如何锁定某一行

1.设置起点：begin

2.行后面加上for update
如：

selsect * from table A where a=8 for update;

此时该行被锁定直到提交之后，在此期间，如果其他线程来修改此行的数据，则会被阻塞

3.提交：commit

案例

注：session代表线程

行锁（偏写）

偏向Innodb

锁某一行

开销小

加锁快

存在死锁

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

案例

注：session代表线程

页锁

开销和速度介于表锁和行锁

存在死锁

按数据是否共享

共享锁

同读锁

排他锁

同写锁

按主观臆断

79、什么是悲观锁

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

乐观锁

别人不会去写自己所读的数据

别人不会去读自己正在修改的数据

可通过时间戳控制并发出现的问题（类似版本号）

可在数据库表中单独加一列时间戳，如“TimeStamp”

每次读带上该字段，遇写加1，提交之前，跟数据库的该字段比较一次，如果待写入时间戳比数据库的大，说明无人改动，否则不允许保存

这种处理方法虽然不使用数据库系统提供的锁机制，但是这种方法会大大提高数据库处理的并发量

悲观锁

自己读数据库的时候，别人可能刚好在写自己刚读的数据

自己在修改某条数据的时候，别人可能刚好在读该条数据

所以操作前要加锁

加排它锁(写锁) 和共享锁(读锁)

其他锁

间隙锁

范围查询，并请求共享或排他锁时

Innodb会为条件范围内的值（即使不存在）加锁

对于条件范围内不存在的记录，称为间隙（GAP）

危害

间隙锁将导致其他线程无法插入锁定范围内的任何数据

阻塞了…

MySQL锁优化

尽可能让所有检索通过索引完成，避免行锁 -> 表锁

尽量精确检索条件，避免间隙锁

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

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

锁定某行后，尽量不要调其他表或行，应尽快完成该行操作并解锁

数据库并发场景

读-读

无任何问题

读-写

可能会脏读、幻读、不可重复读

写-写

可能会写覆盖（更新丢失）

数据库死锁

死锁相关看并发那篇，差七不差八

传送门：面试速记之Java并发

举一个数据库死锁的场景

用户A在访问行1，对行1加了锁，在访问过程中，又访问行2

而用户B在访问行2，对行2加了锁，然后中间又访问行1

这样用户A和用户B都在等待对方释放资源，于是产生了死锁现象

事务

一个不可分割的数据库操作序列

一组sql

并发控制的基本单元

四大特性（ACID）

如何保证

保证原子性

innodb有undo log（回滚日志）

当事务执行失败或调rollback时，通过undo log记录信息将数据库还原至之前状态

保证一致性

通过其他三大特性来保证

保证隔离性

锁 + MVCC机制

MVCC即多版本并发控制

一个行记录有多个版本的快照数据

快照数据存于undo log

保证持久性

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

innodb有redo log

数据修改时，除了在内存操作，redo log 也会记录此次操作

事务提交，将redo log日志写入硬盘

数据库宕机重启，先根据redo log恢复到宕机前状态，再根据undo log 和 bin log内容决定回滚还是提交

redo log为什么比数据页刷盘（刷新硬盘）效率高？
1.redo log只记录修改了啥，占用小
2.redo log采用末尾追加，属于顺序IO，比随机IO快

事务存在的问题

脏读

事务A读到事务B修改后但未提交的数据

一旦事务B回滚，那么事务A读到的数据便成了脏数据

不可重复读

事务A读某个数据，此时事务B刚好修改了那个数据，事务A再次读那个数据便会出现前后读取数据不一致的情况

幻读

事务A读取数据，此时事务B插入了新的数据，事务A再次读取发现前后读取到的数据不一致，结果中存在新的记录

事务隔离级别

设置隔离级别

set transaction isolation level xxx

读未提交

存在脏读、不可重复读、幻读

读已提交

Oracle默认隔离级别

存在不可重复读、幻读

读锁在语句执行完释放

可重复读

MySQL默认隔离级别

读锁在事务提交后释放

使用了MVCC

MVCC

多版本并发控制机制

不通过锁解决读-写冲突

为事务分配单向增长的时间戳

为每个修改提供一个版本

时间戳和版本关联

读操作将读事务开始前的数据库快照

当前读

读取的是记录的最新版本

要加读锁

快照读

不加锁的非阻塞读

读到的不一定是最新版本，也可能是历史版本

优点

可做到读不阻塞写，写不阻塞读，提高了数据库的并发读写性能

解决了脏读、不可重复读问题

说避免了幻读是不准确的，因为在某些情况下仍会出现
详见：【MySQL】面试题之：MVCC能否解决幻读？

缺点

无法解决丢失更新

序列化

表锁在事务提交后释放

一些问题

Q：对 a,b,c 字段建立联合索引，则

select * from table where c=4 and b=6 and a=3;

能不能用到索引？

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

Q：sql中的in会走索引吗？
A：in的范围/条件较少时，会走索引；in的范围/条件过大时，会导致索引失效。

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

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

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

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

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

如果删除的是中间的两条数据，则无论哪种引擎，重启不重启都是18，因为自增主键的最大ID都是最大ID。

Q：如何保证数据库与缓存中的数据一致？
A：写操作：先删除缓存，再更新数据库；

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

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

JDBC

传送门：使用JDBC连接数据库步骤

分库分表

关系型数据库出现性能瓶颈

数据量达1000w以上

数据切分

将数据分散存储在多个数据库中

使得单一数据库数据量变小

垂直分库

基于表

将关联度低的不同表存在不同数据库中

垂直分表

基于列

某表字段较多，可扩展出一张新表

大表拆小表

MySQL底层通过数据页存储数据，记录占用空间过大会导致跨页

垂直分表能解决跨页问题

水平切分

当无法进行垂直切分时，考虑水平切分

根据表内数据的内在逻辑关系，将同一表下按不同条件分散到多个表或数据库中，每个表只包含部分数据

MySQL日志

redo log（重做日志）

确保事务的持久性

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

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

执行过程

1.一条语句更新

2.Innodb引擎把更新的记录写到 redo log

3.更新内存

4.空闲时或按设好的更新策略将redo log中内容更新到磁盘中

undo log（回滚日志）

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

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

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

bin log（归档日志）

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

属于MySQL Server层面

以二进制形式记录执行语句的原始逻辑，在不同存储引擎间可达一致性要求

日志形式

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

statement：记录每一条修改的数据的SQL语句

mided：两种方式的结合

区别

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

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