8.DataBase.md · 阿提说说/Java-interview-question

# 8.数据库

> 《高性能Mysql(第三版)》

### 数据库三大范式、反模式

1. 强调属性的原子性约束，要求属性具有原子性，不可再分解
2. 强调记录的唯一性约束，表必须有一个主键，并且没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分
3. 强调属性冗余性的约束，即非主键列必须直接依赖于主键

反模式：如果完全按照三大范式来设计表结构，会导致业务涉及表增多，查询数据需要多表联合查询，导致sql复杂，性能变差，不利于维护，也不利于分库分表，比如会在表中冗余存储城市id对应的城市名称

http://blog.720ui.com/2017/mysql_core_07_anti-pattern/

### Mysql 架构图

### InnoDB存储结构

逻辑存储单元分为表空间(TableSpace) -> 段(segment) -> 区(extent) -> 页(page)

Mysql 8.0 InnoDB架构图

- 表空间：所有数据都存在表空间中，表空间分系统表空间和独立表空间。

系统表空间

在安装数据库的时候默认会初始化一个以ibdata1命名的系统表空间，存储所有数据的信息以及回滚段信息，ibdata1默认的大小是10MB,在高并发情况下，会有性能影响，建议初始大小调整为1GB。

`相关教程参考：https://blog.csdn.net/demonson/article/details/79863166`

独立表空间

设置参数innodb_file_per_table = 1 ，目前MySQL默认都是独立表空间，每个表都有自己的表空间文件，存储对应表的B+数数据、索引和插入缓冲等信息，其余信息还是存储在共享表空间中

撤销表空间

包含撤销日志，初始化的时候会创建两个默认的撤销表空间

通用表空间

可以存储多个表的数据，相比独立表空间更节约元数据的内存开销

临时表空间

分会话临时表空间和全局临时表空间。会话临时表空间，在第一个请求中，会话临时表空间从临时表空间池分配给会话，最多两个临时表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表，当会话断开时，临时表空间将被释放进入临时表空间池中；全局临时表空间，用于存储用户创建的临时表的更改数据，用于回滚，在正常关闭或初始化中止时被删除，并在每次启动服务器时重新创建

- 段

表空间由段组成，一个表通常有数据段、回滚段、索引段等，每个段由N个区和32个零散的页组成

- 区

由连续的页组成，每个区大小固定1MB

- 页

一个区由64个连续页组成，页默认大小16KB

### 存储引擎的 InnoDB与MyISAM区别，优缺点，使用场景

ACID:

原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）

| 存储引擎 | InnoDB                      | MyISAM                                            |
| -------- | --------------------------- | ------------------------------------------------- |
| 存储文件 | .frm表定义文件 .ibd数据文件 | .frm表定义文件<br> .myd数据文件<br> .myi 索引文件 |
| 锁       | 表锁，行锁                  | 表锁                                              |
| 事务     | ACID                        | 不支持                                            |
| CRUD     | 读写                        | 读多                                              |
| count    | 扫表                        | 专门存储的地方                                    |
| 索引结构 | B+Tree                      | B+Tree                                            |

### 建立索引的原则

1. 最左匹配原则，直到遇到范围查询(>, <, between, like)就停止，比如a = 1 and b = 2 and c >3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，abd的顺序可以任意调整
 2. = 和 in可以乱序，比如a = 1 and b =2 and c = 3建立(a, b, c) 索引可以任意顺序，mysql查询优化器会帮你优化
 3. 尽量选择区分度高的索引，区分度公式count(distinct col)/count(*) ，表示字段不重复的比例，比例越大我们的扫描记录越少,比例一般是需要join的字段要求是0.1以上，即平均1条扫描10条记录
 4. 索引不能参与计算，比如from_unixtime(create_time) = '2014-05-29' 就不能使用到索引，因为b+tree中存的都是数据表中的字段值，但进行检索时，需要把素有元素都应用到函数才能比较，成本大，应该改成create_time = unix_timestamp('2014-05-29')
 5. 尽量扩展索引，不要新建索引，比如表中已经有a索引，现在要加(a,b)索引，只需要修改原来的索引即可

### 索引失效情况总结

1. 遵守最左匹配原则，中间断索引，使用范围查询
2. 在索引列上做计算
3. 索引字段使用 != 或者 <>
4. 索引字段使用 is null 或者 is not null
5. 使用通配符 %开头
6. 索引字段是字符串，查询条件没有使用字符串
7. 索引字段使用or

https://blog.csdn.net/wuseyukui/article/details/72312574

### B+Tree 索引 和 哈希索引 限制

B+Tree索引：

分两类，聚集索引和 普通索引

聚集索引，在创建表的时候，会创建一个主键，这个主键就是聚集索引，在索引叶子节点中存放了数据信息。InnoDB会给没有创建主键的表选择第一个不包含null值的唯一索引作为主键，如果唯一索引也没有，就会为该表创建一个6字节的rowid作为主键

普通索引，索引叶子节点并不包含所有行的数据，只保留键值，通过键来查找行数据

- 全值匹配，和索引中的所有列进行匹配
- 匹配最左前缀
- 匹配列前缀，可以只匹配某一列的值开头部分
- 匹配范围值，如果匹配的列不是主键，只能使用第一个索引来匹配范围，否则不走索引，如果匹配列是主键，可以不按照索引顺序来，走的是主键索引
- 精确匹配某一个列并范围匹配另外一列

哈希索引：

- 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中行的速度很快
- 哈希索引数据并不是按照索引值顺序存储的，所以也无法用于排序
- 哈希索引不支持部分索引列匹配查找，因为哈希索引始终使用索引列的全部内容来计算哈希值
- 只支持等值比较查询，包括 =、 in()、<=>，不支持范围查询
- 数据访问速度快，当哈希冲突时，必须遍历链表中的所有行指针，直到查询到符合条件的行
- 哈希冲突多的话，一些索引维护操作的代代价很高

### 事务隔离级别，设置事务方法

1. read uncommitted(未提交读) ： 可以看到未提交的数据,脏读

2. read committed (提交读)：只能读取已提交的数据，但多次读取的数据结果可能不一致，导致幻读
3. repeatable read(可重复读)：默认级别，可以重复读，解决了脏读问题，但会有幻读
4. serializable(可串行化)：最高隔离级别，强制事务串行执行，避免幻读问题

查询当前会话级别：select @@tx_isolation;

查看系统当前隔离级别：select @@global.tx_isolation;

设置当前会话隔离级别：set session transaction isolatin level repeatable read;

设置系统当前隔离级别：set global transaction isolation level repeatable read;

### 什么是MVCC, MySQL的MVCC原理

MVCC即多版本并发控制，它能在很多情况下避免加锁操作，降低开销，不同的存储引擎实现方式不同，有乐观并发控制和悲观并发控制

MySQL的InnoDB引擎，通过在每行记录后面保存两个隐藏的列来实现，一个列保存了行的创建时间，一个保存了行的过期时间（或删除时间）。实际存储的是系统版本号，每开始一个新的事务，系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。该MVCC只使用在repeatable read 和 read committed下

保存这两个额外的系统版本号，使大多数读操作都不用加锁，并且也能保证只会读到符合标准的行。缺点是需要额外的存储空间和维护工作。

### Mysql死锁

死锁是两个或者多个事务在同一资源上互相占用，并请求锁定对方资源，从而导致互相等待的现象。

死锁示例：

```sql
#事务1
start transaction;
update stockprice set close = 45 where stock_id = 4 and date = '2019-1-1';
update stockprice set close = 20 where stock_id = 3 and date = '2019-1-3'

#事务2
start transaction;
update stockprice set high = 36 where sockt_id = 3 and date = '2019-1-3';
update stockprice set hight = 60 where stock_id = 4 and date = '2019-1-1';
```

两个事务分别执行两个更新语句，都执行第一个语句，锁定了该行数据，但该行数据将做为对方事务执行下条语句的条件，所以当事务继续执行第二条语句的时候，因为需要的条件所在行已被另外一个事务锁定，这是死锁现象

避免死锁的方法：

- 约定以相同的顺序访问表
- 大事务分小事务
- 一个事务中，一次锁定资源
- 锁升级，采用表锁

###  Msyql 执行SQL 过程

![img](https://mmbiz.qpic.cn/mmbiz_png/UtWdDgynLdbAUKxPhOt5w3a5p8wcZ9a3TBdpJ75T5aUIjZVUyV1WTSJKBBaibVMicVKkjBOX0uLuqXgYTTkYZVicw/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1)

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3. 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端

### 如何优化sql翻页

1. 只让用户一页页翻，不能跳页
2. 确定每页的边界值，通过where条件查询来优化
3. 使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原有表获得需要的行

```sql
select name,sex,rating from mysql_test a inner join (
	select id from mysql_test where sex='F' order by rating limit 20000,100
) as x USING(id)
```

### 如何优化SQL语句

1. 先看表的数据类型是否设计的合理，遵守选取数据类型越简单越小的原则
2. 表中的碎片是否整理，[MySQL表的碎片整理和空间回收](https://www.cnblogs.com/kerrycode/p/10943122.html)
3. 表的统计信息是否收集，只有统计信息准确，执行计划才可以帮助我们优化SQL
4. 查看执行计划，检查索引的使用情况，没有用到索引，创建索引
5. 创建索引需要判断这个字段是否适合创建索引，遵守[建立索引的原则](#建立索引的原则)
6. 创建索引后，通过explain分析，前后性能变化

### 如何分析explain执行计划

先查看type列，如果出现all关键词，就代表sql执行全表扫描

再看key列，如果null代表没有使用索引

再看rows列，如果越大，代表需要扫描的行数越多，相应耗时就长

最后看 extra列，是否有影响性能的 Using filesort 或者 Using temporary

explain 各个字段含义：https://blog.csdn.net/weixin_34062469/article/details/94498678

### slect * from a left join b on 条件 和 select * from a left join b where 条件一样么，为什么

不一样，返回的结果不一样。

select * from a left join b on 条件 会返回 a 中没有匹配的数据

select * from a left join b where 条件 只返回where中匹配的数据

https://www.cnblogs.com/caowenhao/p/8003846.html

阿提说说/Java-interview-question

简介

发行版

贡献者

近期动态

阿提说说/Java-interview-question .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

阿提说说/Java-interview-question