数据库-CSDN博客

原文：https://github.com/jujunchen/Java-interview-question/blob/master/8.%20%E6%95%B0%E6%8D%AE%E5%BA%93.md#%E5%BB%BA%E7%AB%8B%E7%B4%A2%E5%BC%95%E7%9A%84%E5%8E%9F%E5%88%99

数据库三大范式、反模式

强调属性的原子性约束，要求属性具有原子性，不可再分解
强调记录的唯一性约束，表必须有一个主键，并且没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分
强调属性冗余性的约束，即非主键列必须直接依赖于主键
反模式：如果完全按照三大范式来设计表结构，会导致业务涉及表增多，查询数据需要多表联合查询，导致sql复杂，性能变差，不利于维护，也不利于分库分表，比如会在表中的冗余存储城市id对应的城市名称http://blog.720ui.com/2017/mysql_core_07_anti-pattern/

mysql架构层：

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

系统表空间：在安装数据库的时候会初始化一个以ibdata1命名的系统表空间，存储所有数据的信息以及回滚端信息，ibdata1默认的大小是10M，在搞并发的情况下，会有性能的影响，建议初始大小调整为1GB。相关教程参考：https://blog.csdn.net/demonson/article/details/79863166
独立表空间：设置参数innodb_file_table=1,目前mysql默认多是独立表空间，每个表都有自己的表空间文件，存储对应表的B+数据、索引和插入缓冲等信息，其余信息还是存储在共享表空间中
撤销表空间：包含撤销日志，初始化的时候会创建两个默认的撤销表空间
通用表空间：可以存储多个表的数据，相比独立表空间更节约元数据的内存开销
临时表空间：分会话临时表空间和全局临时表空间。会话临时表空间，在第一个请求中，会话临时表空间，在第一个请求中，会话临时表空间从临时表空间池分配给会话，最多两个临时表空间，一个用于用户创建的临时表，另外一个优化器创建内部临时表，当会话断开时，临时表空间将被释放进入临时表空间池中；全局临时表空间，用于存储用户创建的临时表的更改数据，用于回滚，在正常关闭或初始化中止时被删除，并在每次启动服务器时重新创建

段：表空间由段组成，一个表通常由数据段、回滚段、索引段等，每个段由N个区和32个零散的也组成
区：由连续的也组成，每个区大小固定时1MB
页：一个区由64个连续页组成，页默认大小是16KB

存储引擎的In'no'DB与MylSAM区别，优缺点，使用场景

ACID:

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

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

建立索引的原则：

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

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

B+Tree索引：分两类

聚集索引：在创建表的时候，会创建一个主键，这个主键就是聚集索引，在索引叶子节点中存放了数据信息。InnoDB会给没有创建主键的表选择第一个不包含null值的唯一索引作为主键，如果唯一索引也没有，就会为该表创建一个6字节的rowid作为主键
普通索引：索引叶子节点并不包含所有行的数据，只保留键值，通过键来查找行数据
1. 全值匹配，和索引中的所有列进行匹配
2. 匹配最左前缀
3. 匹配列前缀，只可以匹配某一列的值开头部分
4. 匹配范围值，如果匹配的列不是主键，只能使用第一个索引来匹配范围，否则不走索引，如果匹配列是主键，可以不按照索引顺序来，走的是主键索引
5. 精确匹配某一个列并范围匹配两外一个列

哈希索引：

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

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

read uncommited(未提交读)：可以看到为提交的数据脏读
read comminted(提交读)：只能读取以提交的数据，但多次读取的数据结果可能不一致，导致幻读
repeatable read(可重复读)：默认级别，可以重复读，解决了脏读问题，但会有幻读
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死锁

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

避免死锁的方法：

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

mysql执行过程：

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

如何优化sql翻页：

只让用户一页页翻，不能跳页
确定每页的边境值，通过where条件查询来优化

使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原有表获得需要的行

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语句：

首先看表的类型设计是否合理，遵守选取数据类型越简单越小的原则
表中的碎片是否整理
表的统计信息是否收集，只有统计信息准确，执行计划才能帮助我们优化sql
查看执行计划，检查索引的使用情况，没有用到索引，创建索引
创建索引需要判断这个字段是否适合创建索引，遵守索引创建原则
常见索引后通过explain分析性能变化

如何分析explain执行计划：

先查看type列，如果出现all关键词，就代表sql扫描全表
再看key列，如果是null代表没有使用索引
再看rows列，如果越大代表扫描的函数越多，相应耗时就长
最后再看extra列，是否有影响性能的Using filesort 或者 Using temporary
explain各字段的含义：https://blog.csdn.net/weixin_34062469/article/details/94498678

select * 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://blog.csdn.net/mlh532354163/article/details/104719925