数据库MySql

MySql

主键和外键

• 主键(主码) ：主键用于唯一标识一个元组，不能有重复，不允许为空。一个表只能有一个主键。
• 外键(外码) ：外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键。

三大范式

• 第一：属性不可再分
• 第二：所有非主属性都依赖于主键属性
• 第三：所有非主属性都与主键直接相关 不能间接相关

drop、delete、turncate

• drop table + 表名 ： 删除表
• delete from + 表名 + where x = a ： 删除表的列
• turncate table + 表名 ： 清空表中数据

MyISAM（之前）和 InnoDB（当前MySql默认）

• 行级锁：MyISAM只有表级锁 MySql支持行级锁
• 全表行数：MyISAM需要全表扫描， MySql有行数变量
• 事务：MyISAM不提供事务支持 MySql支持事务 可以提交和回滚
• 外键：MyISAM不支持 MySql支持
• MVCC:MyISAM不支持 MySql支持
• 数据库异常崩溃后的安全恢复:MyISAM不支持 MySql支持(redo日志)

事务

 事务是逻辑上的一组操作，要么都执行，要么都不执行。

实现原理

 MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性；
 使用 undo log(回滚日志) 来保证事务的原子性。
 MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。
 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

ACID特性

1. 原子性：事务不可分割；
2. 一致性：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
3. 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性：一个事务被提交之后。它对数据库中数据的改变是持久的。

并发事务带来的问题

1. 脏读：一个事务修改数据未提交，这时另外一个事务也访问了这个数据，并且使用了这个数据，另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
2. 不可重复读：在一个事务内多次读同一数据，多次读取的间隙其他事务使用并修改了数据，导致多次读取的数据不一致。
3. 幻读：在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

 幻读强调读取的数据增多或者减少了，不可重复读强调多次读取的数据不一致。

• 事务隔离级别
  1. 读未提交：允许读取未提交的数据变更，会导致脏读、不可重复读、幻读；
  2. 读已提交：允许读取并发事务已提交的事务，会导致不可重复读、幻读；
  3. 可重复读（默认）：同一字段多次的读取结果一致，除非是事务本身自己修改，会导致幻读；
  4. 可串行化：所有事务依次执行，完全服从ACID。
• 如何解决幻读
  1.MVCC 解决快照读条件下的幻读 + 间隙锁 解决当前读（for update 读最新数据）的幻读
  2.串行化

索引

 索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
 优点 ：
- 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
 缺点 ：
- 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
- 索引需要使用物理文件存储，也会耗费一定空间。

Hash表

 哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。
 哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的值相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。
- 为什么MySQL 没有使用其作为索引的数据结构呢？
 Hash 冲突问题 ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

B树 & B+

 B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。（查找：左小右大 平衡：左右子树高度差不大于1）
1. B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
2. B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
3. B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节 点到叶子节点的过程，叶子节点的顺序检索很明显。
为什么不使用二叉平衡搜索树：因为二叉树的层数太大了，每个节点存储的数据量又太小，每次I/o读取的数据是以页单位的，因此小数据读取一页会造成很大的I/O浪费.
同样情况下，B+的非叶子节点存储更多的索引，因此B+更矮更胖，可以有效减少I/o次数

为什么使用B+树

• B树
   B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
   由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
• hash表：不适合范围查询和顺序检索
• 二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
• 红黑树： 树的高度随着数据量增加而增加，IO代价高。

聚集索引和非聚集索引

聚集索引：记录的排列顺序和与索引的排列顺序一致（字典按拼音查）
 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。
- 非聚集索引：记录的排列顺序和与索引的排列顺序不一致（字典按偏旁查）
 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的
 跟聚集索引一样，非聚集索引也依赖于有序的数据
 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。(不是必须（覆盖索引）：一个索引包含（覆盖）所有需要查询字段的值 select score from student where score > 90)

主键索引 & 辅助（二级）索引

• 数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
• 二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据（data）是主键。包括唯一、普通、前缀、全文索引。
• 索引适用情况（频繁更新的字段不适用索引）
  • 不为Null的字段：尽量不为Null，否则数据库较难优化
  • 被频繁查询的字段：目的所在
  • 作为条件查询的字段：更快定位数据
  • 频繁排序的字段：排序后的字段可以加快查询时间
• 最左匹配 (a,b,c)索引 走a,ab,abc;遇到><and 会中断（>前面的还有效），但是顺序调换不影响查询结果时会优化顺序；遇到or和运算，索引会失效
• 创建索引
  ALTER TABLE + 表名 + ADD PRIMARY KEY（col）//主键
  ALTER TABLE + 表名 + ADD UNIQUE（col）//唯一
  ALTER TABLE + 表名 + ADD INDEX index_name（col）//普通
  ALTER TABLE + 表名 + ADD FULLTEXT ( col)//全文
  ALTER TABLE + 表名 + ADD INDEX index_name ( column1, column2, column3 )//多列

慢查询

• 慢查询
  1. 偶尔很慢：数据库在刷盘、等待锁
  2. 一直很慢：没创建索引或者创建了索引却没用到（一直全表扫描）

EXPLAIN SELECT * FROM employees.titles WHERE emp_no=‘10001’ AND title=‘Senior Engineer’ AND from_date=‘1986-06-26’;

• type：type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。如const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的覆盖索引) 。
• key：此字段是 MySQL 在当前查询时所真正使用到的索引。
• rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数. 这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。

索引失效

1. 使用!= 或者 <> 导致索引失效
2. 类型不一致导致的索引失效
3. 函数导致的索引失效
   如：SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;
   如果使用函数在索引列，这是不走索引的。
4. 运算符导致的索引失效
   SELECT * FROM user WHERE age - 1 = 20;
   如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。
5. OR引起的索引失效
   SELECT * FROM user WHERE name = ‘张三’ OR height = ‘175’;
   OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。
6. 模糊搜索导致的索引失效
   SELECT * FROM user WHERE name LIKE ‘%冰’;
   当%放在匹配字段前是不走索引的，放在后面才会走索引。
7. NOT IN、NOT EXISTS导致索引失效

大表优化

当MySQL单表记录数过⼤时，数据库的CRUD性能会明显下降

• 读写分离
  将MySQl的数据源拷贝到其他服务器上，将数据分为主库和从库分别负责读和写（bin log日志保证一致性），优点（主库宕机从库替代，不影响业务）缺点（极端情况可能导致数据不一致）
• 分库分表
  • 垂直分区-根据业务信息拆分，多列表拆成多张表 使得数据列变小，减少I/o次数，简化表结构；却会产生主键冗余，使事务更复杂。
  • 水平分区-把单一表按照某种规则拆分到多个数据库（例如hash(id)、时间），对行的拆分，避免表单过大,却会产生很多分区键和联表复杂度。

锁

• 乐观锁-认为每次访问数据时不会被别人修改,但是更新时会判断这个数据是否发生了改变（CAS版本号）适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
• 悲观锁-假设每次拿数据时都会被别人修改，所以拿数据时都要上锁。（Synchronized）多写的场景下用悲观锁就比较合适。
• 全局锁：对整个数据库实例加锁，全库逻辑备份时使用，其他的DDl操作都会被阻塞。
• 共享锁：又称读锁，是读取数据创建的锁，其他用户可以并发读，但是不能修改没否则会死锁。
• 排他锁：也叫写锁，获取排他锁可以进行读写，其他事务不能访问这个对象。
  • 记录锁（行锁）SELECT * FROM test WHERE id=1 FOR UPDATE; 其他事务可读，不可操作。
    它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。当有明确指定的主键时，是行级锁。否则是表级别。仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
  • 间隙锁（gap lock）:在可重复读的隔离级别下，通过普通索引进行查询，自动创建间隙锁。锁住要查询的数据附近的间隙，在间隙插入数据时会失败。
  • 临键锁（next-key lock）:临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。用next-key lock解决当前读下的幻读问题，如果是走索引，他会锁住索引本身的行锁；如果是范围，就会成为一个行锁+间隙锁，导致范围内的无法插入；如果是无索引的，直接全表加上了间隙锁，无法插入，阻塞。

日志

• redo log（重做日志）
   redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
   比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

  • MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。
    
    

• 刷盘时机：
   1.默认 提交事务时刷盘
   2.间隔1 秒的轮询机制，会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后刷盘
   3.当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘

• 存储形式
  日志文件组，采用环形数组的方式从头开始写，写到末尾又回到头循环写。
  日志文件组有两个属性相当于时针和分针，记录完成一个指针向后移动，恢复数据时另一个指针向前移动，如果指针快要接近，意味着需要清楚一些记录。

• binlog（归档日志）
   redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。只要发生了表数据更新，都会产生 binlog 日志。mysql依靠binlog来同步数据，保证数据一致性。

• undo log（回滚日志）
   我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。依靠binlog来同步数据，保证数据一致性。

• MCVV（多版本并发控制）
   多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的。
• 隐式字段：最近修改(修改/插入)事务ID；回滚指针，指向这条记录的上一个版本
• undo日志
  
• Read View(读视图)：事务进行快照读操作的时候生产的读视图（用来做可见性判断的）
  有三个属性：生成时刻系统正活跃的事务ID，列表中事务id最小值，目前已出现过的事务ID的最大值+1
  进行数据查询时，会
  1. 当前数据的事务id和最小id比较，若当前事务更小，则数据是可见的；否则继续比较
  2. 当前事务id和最大值比较，若当前事务更大，则说明该记录在Read View生成后才出现的，对当前事务不可见；否则继续比较
     3.当前事务id是否子啊活跃列表，如果在，则说明ReadView生成时该事务还在活跃（还未提交），对当前事务不可见；如果不在，说明这条事务已经提交，数据是可见的。
• 可重复度如何在读已提交的基础上解决不可重复读
  可重复读在一个事务中读出的数据都是一致的，这是因为可重复读的ReadView生成时机和读已提交不同，它会在事务第一次进行数据查询时生成（记住快照读！快照！），而读已提交每次读数据都会创建新的ReadView，因此读已提交在同一事务中会读出别的事务提交了的数据。
• 可串行化是如何实现的
  串行级别下的快照读会退化成当前读（当读取的是记录的最新版本，会对读取的记录进行加锁，保证其他并发事务不能修改当前记录，而快照读可能读到的并不一定是数据的最新版本，可能是之前的历史版本）。

命令和连表查询

//建表
CREATE TABLE `grade` ( 
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID', 
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', 
	 PRIMARY KEY (`gradeid`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8 
//插入
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
//删除 turncate清空表数据 结构、索引不变
DELETE FROM grade WHERE gradeid = 5；
TRUNCATE [TABLE] table_name;

连接查询如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
- 内连接 inner join 查询两个表中的结果集中的交集 (查交集)
- 外连接 outer join
左外连接 left join (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充) （查左边全部 右边没有的Null补）
右外连接 right join (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)（查右边全部 左边没有的Null补）

• 排序 Order BY + ASC / DESC 最大最小MAX()、MIN()
• 分页 LIMIT 0,10 (左闭右开原则)
• 计数 COUNT
• 求和 SUM
• 平均 AVG
• 去重 DISTINCT