数据库开发|通过栗子来学习MySQL高级知识点（学习，复习，面试都可）数据仓库|java|python|后端

前言：
首先很感谢各位小伙伴对我上一篇文章的支持，给我的创作带来了很大的动力。
其次最近要考试MySQL高级这个课程了，这块内容也是面试中重要的考点，所以正好借此机会看了之前看过的网课笔记和网上的资料，记录一下面试中常问的问题和这个课程里重要的知识点，以帮助自己和各位小伙伴儿应对期末考试，巩固学习，以后面试用，在这里先感谢大佬们的支持。
如果文章中有什么不对的地方或者需要改进的地方，还请大佬们不吝赐教。

文章图片

文章目录

MySQL索引
- 什么是索引
- 索引都有哪些数据结构
- 索引的原理
- 索引底层是怎么实现的
- - Hash索引
  - 二叉树索引
  - 平衡二叉树
  - B树（多路平衡查找树）
  - B+树
- B+树优于B树原因
- 什么是覆盖索引
- 什么是聚簇索引
- 联合索引
- 最左匹配原则
- 创建索引的原则
- 创建索引的优缺点
MySQL存储引擎
- 分类
- InnoDB 和 MylSAM存储引擎区别
MySQL事务
- 什么是事务
- 事务操作
- 事务的特性
- 事务的隔离级别
MySQL 锁机制
- MySQL锁的特性
- 如何加锁
- 死锁

MySQL索引

文章图片

什么是索引索引是一种数据结构，可以帮助我们快速查找数据
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
索引都有哪些数据结构索引可能有三种数据结构哈希表、有序数组和N叉树。MySQL索引使用的是B+树（InnoDB存储引擎）
索引的原理索引的原理
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

文章图片

索引底层是怎么实现的 Hash索引
哈希索引是采用一定的哈希算法，将键值换算成新的哈希值，映射到对应的槽位上，然后存储在hash表中。
如果两个或多个键值，映射到一个相同的槽位上，他们就会产生Hash冲突，也叫Hash碰撞，可以通过链表来解决。

文章图片

Hash索引特点

优点：
查询效率高，在没有产生hash冲突的情况下，通常只需要一次检索就可以了，效率通常要高于B+树索引。
缺点：
1.Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，<,…），即无法进行大小比较
2.无法利用索引完成排序操作（Hash索引是无序排列的）
存储引擎支持
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能（利用方法将b+树索引转化为hash索引），hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

二叉树索引
二叉树索引分为左子树，右子树，根节点，左子树要比根节点小，右子树要比根节点大

文章图片

二叉树缺点：顺序插入时，会形成一个链表（如右图），查询性能大大降低。很多数据量的情况下，层次较深，检索速度慢。

平衡二叉树
特点：
它的左子树和右子树都是平衡二叉树
左子树比中间小，右子树比中间值大
左子树和右子树的深度之差的绝对值不超过1

文章图片

缺点

缺点：
a、插入操作需要旋转
b、支持范围查询，但回旋查询效率较低，比如要查找大于8的，会回旋到父节点7、10。
c、如果存放几百条数据的情况下，树高度越高，查询效率会越慢

B树（多路平衡查找树）

文章图片

B树：叶子节点和非叶子节点都存储数据，数据结构为有序数组+平衡多叉树，也叫b-树。
m为树的叉数
每个节点最多有m-1个关键字(可以存有的键值对)。
根节点最少可以只有1个关键字。
非根节点至少有m/2个关键字。
每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。每个节点都存有索引和数据，也就是对应的key和value。

文章图片

【数据库开发|通过栗子来学习MySQL高级知识点（学习，复习，面试都可）】b树优点：二叉平衡树的基础上，使加载一次节点，可以加载更多路径数据，同时把查询范围缩减到更小
树的每一个节点都包含key和value。
所以，经常访问的元素可能离根节点更近，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。

缺点：业务数据的大小可能远远超过了索引数据的大小，每次为了查找对比计算，需要把数据加载到内存以及 CPU 高速缓存中时，都要把索引数据和无关的业务数据全部查出来。本来一次就可以把所有索引数据加载进来，现在却要多次才能加载完。如果所对比的节点不是所查的数据，那么这些加载进内存的业务数据就毫无用处，全部抛弃。

文章图片

B+树
以m阶B+树为例
一个m阶的B+树具有如下几个特征：
有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

B+树只有叶子节点存储数据(B+数中有两个头指针：一个指向根节点，另一个指向关键字最小的叶节点)，叶子节点包含了这棵树的所有数据，所有的叶子结点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用

文章图片

B+树优于B树原因 b+树的中间节点不保存数据，可以容纳更多的节点元素
所有的叶子结点使用链表相连，有助于区间查找和遍历
B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间
B树的话，就需要进行每一层的递归遍历
相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好

文章图片

什么是覆盖索引索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。b+索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

1.覆盖索引是一种数据查询方式，不是索引类型
2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引
3.查询的字段被使用到的索引树全部覆盖到

举个栗子：

文章图片

在上图中，id为主键索引，name为唯一索引
假如你执行命令

select id,name from eclass where id=1;

此时由于id和name字段都在索引树中，所以这就是覆盖索引查询

文章图片

什么是聚簇索引聚簇索引是物理索引，数据表就是按顺序存储的，物理上是连续的。
一般情况下主键会默认创建聚簇索引
将索引与数据放在一起，当你找到索引后，也就找到对应的数据了。每张表只能建立一个聚簇索引，但是该索引可以包含多个列（一般使用的是主键等不经常更新的列）
非聚簇索引：数据储存于索引分开，叶节点指向了对应的数据行。辅助索引访问数据时需要二次查找。辅助索引存储的不是行的物理位置，而是主键的值。而通过辅助索引首先找到的就是主键的值，再通过主键的值找到数据行对应的数据页，最后才能找到对应行。

文章图片

联合索引我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。使用联合索引，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。。

文章图片

最左匹配原则顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停?匹配。
mysql创建复合索引的规则是?先对复合索引最左边的字段的数据进?排序，在此基础上，再对后?的字段进?排序，这样第?个字段是绝对有序的，后?的字段就是?序的了，?般情况下第?个字段进?条件判断是?不到索引的，可能出现type是index类型的，这就是mysql 最左前缀的原因。
举个栗子：假设创建了顺序为（a，b，c）的索引
1.当查询条件为a=1 and b=1 或b=1 and a=1（查询优化器会调换a与b的位置），这时候都可以走索引。
当查询条件为a=1 and b>1 and c=1 时，由于是范围查找，a和b走索引，c不走索引

文章图片

创建索引的原则创建索引，肯定是有利于我们的查询效率的，如果无效地创建索引，只会浪费我们的内存和执行程序的效率，因此创建索引是有原则的

首先应考虑对where 和 order by 涉及到的列上建立索引
对一个存在大量更新操作的表，所建索引的数目一般不超过3个，最多不超过5个，索引虽说提高了访问速度，但太多索引会影响数据的更新操作,并且索引本身会占用存储空间。
建立唯一索引。唯一索引能够更快速地帮助我们进行数据定位；
为经常需要进行查询操作的字段建立索引；
更新频繁的列不宜设置索引，索引列不能参与计算
数据量小的表不要使用索引
重复数据多的字段不宜设置索引，如性别男和女。
在建立索引的时候，要考虑索引的最左匹配原则（在使用SQL语句时，如果where部分的条件不符合最左匹配原则，可能导致索引失效，或者不能完全发挥建立的索引的功效）；
如果建立的单个索引查询数据很多，查询得到的数据的区分度不大，则考虑建立合适的联合索引；
尽量考虑字段值长度较短的字段建立索引，如果字段值太长，会降低索引的效率

创建索引的优缺点优点

大大加快数据的查询速度
使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
创建唯一索引，能够保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面，可以加速表和表之间的连接
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

创建索引和维护索引需要消耗时间并且随着数据量的增加，时间也会增加
索引需要占据磁盘空间
对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

文章图片

MySQL存储引擎数据库存储引擎是数据库底层软件组织，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
用户可以根据不同的需求为数据表选择不同的存储引擎
可以使用 SHOW ENGINES命令可以查看Mysql的所有执行引擎我们可以到默认的执行引擎是innoDB支持事务，行级锁定和外键。
MySQL默认的存储引擎是InnoDB。
分类

MylSAM：Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务
InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎
Memory:所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失。
Archive：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差
Federated:将不同的MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.csv文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。
BlackHole：黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog做复制的中继
ERFORMANCE_SCHEMA存储引擎该引擎主要用于收集数据库服务器性能参数。
Mrg_Myisam Merge存储引擎，是一组Mylsam的组合，也就是说，他将Mylsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是Mylsam引擎的表中，但是可以直接进行查询、删除更新等操作。

文章图片

InnoDB 和 MylSAM存储引擎区别

InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
InnoDB支持外键，MyISAM不支持
从MySQL5.5.5以后，InnoDB是默认引擎
InnoDB不支持FULLTEXT类型的索引
InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
InnoDB支持行锁（某些情况下还是锁整表）

图片表示

文章图片

MySQL事务

文章图片

什么是事务在MySQL中的事务Transaction是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。事务用来管理DDL、DML、DCL操作，比如insert，update，delete语句，默认是自动提交的。
事务操作开启事务：Start Transaction
任何一条DML语句(insert、update、delete)执行，标志事务的开启命令：BEGIN或 START TRANSACTION
提交事务：Commit Transaction
成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步命令：COMMIT
回滚事务：Rollback Transaction
失败的结束，将所有的DML语句操作历史记录全部清空
命令：ROLLBACK

set autocommit =0 禁止自动提交事务
set autocommit =1 开启自动提交事务

文章图片

事务的特性

原子性（Atomicity）：事务是一个不可分割的整体，事务开始后的所有操作，要么全部完
成，要么全部不做
一致性（Consistency）：系统从一个正确的状态，迁移到另一个正确的状态
隔离性（Isolation）：每个事务的对象对其他事务的操作对象互相分离，事务提交前对其
他事务不可见
持久性（Durability）：事务一旦提交，则其结果是永久性的

事务的隔离级别读未提交(Read uncommitted)
一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证，会造成脏读。
读已提交(Read committed)
一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。
可重复读(Repeatable read) I
就是在开始读取数据(事务开启)时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。
串行(Serializable)
是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
Mysql的默认隔离级别是Repeatable read （可重复读）。

脏读：强调的是第二个事务读到的数据不够新
栗子：假设老板今天给你发这个月的工资，本来要给你发2w元，结果手一抖发了20w元，这时老板还没有提交事务，而你查询到你的账户里多了20w元，老板总觉得不对劲，总感觉少了些什么，最后发现后回滚事务，给你发了2w，然后这时你打开账户发现只有2w块钱。
不可重复读：同一事务，两次读取到的数据不一样
栗子：前提同第一个，老板给你发了2w元工资，你看到账户里多了2w块钱，此时老板觉得你这个月做的还不错，给你加了1w块钱奖励金并提交了事务，而此时你再次查询时发现是3w块钱，这就造成了在一个事务中读取到的事务不一致。
幻读：重点在于新增或删除，同样的条件，第一次和第二次读出来的记录数不一样
栗子：和你相同薪资的人有10人，此时读数据读到的事务为10人，此时突然增加了一条工资和你们一样的人，提交事务后记录为11人，因此产生了幻读。

文章图片

MySQL 锁机制锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。
在数据库中，除传统的计算资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
从对数据操作的粒度分：
1.表锁：操作时，会锁定整个表。
2.行锁：操作时，会锁定当前操作行。
从对数据操作的类型分：
1.读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2.写锁(排它锁)：当前操作没有完成之前，它会阻断其他写锁和读锁。
其中，InnoDB表锁和行锁都支持，而MyISAM只支持表锁
MySQL锁的特性表级锁偏向MyISAM存储引擎，开销小，加锁快不会出现死锁：锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁偏向InnoDB存储引擎，开销大，加锁慢；会出现死；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理(OLTP)系统。
如何加锁 MyISAM表锁
MyISAM存储引擎只支持表锁
如何加表锁
MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE 命令给MyISAM表显式加锁。
加读锁：

lock table table_name read;

加写锁：

lock table table_name write;

文章图片

InnoDB行锁：
行锁特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。
行锁模式：
InnoDB 实现了以下两种类型的行锁。
共享锁(S) 又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁（X）又称为写锁，简称x锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X)；
对于普通SELECT语句，InnoDB不会加任何锁；
共享锁（S）：SELECT ★ FROM table name WHERE ... LOCK IN SHARE MODE
排他锁（X）：SELECT ★ FROM table name WHERE ... FOR UPDATE