mysql架构是什么？ 
执行流程：
客户端请求 ---> 连接器（验证用户身份，给予权限） ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作） ---> 优化器（主要对执行的sql优化选择最优的执行方案方法，选择索引） ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
储存引擎：不同的数据在磁盘的不同组织形式。
什么是查询缓存？有什么利弊？
查询缓存在mysql的server层，就是每次mysql执行查询操作之后会以k-v的形式放到查询缓存里面，下次再查询如果key一样，就直接返回结果
利弊：
只要对缓存表有修改操作会失效整张表的所有缓存，对于经常更新的表来说，此操作会带来额外的开销，
key难以命中，如果大小写不一致或者有空格的sql都不会命中缓存的key因为hash值不一样，所以该功能实际上是利大于弊的，mysql 8.0之后对该功能停用，之前的版本可以手动开启或者关闭查询缓存功能。
什么是buffer pool？
组要知道的背景知识:
1.innodb存储引擎会把数据写到磁盘上，以表为单位生成表名.frm和.idb文件存储在相应的mysql存储目录下。
2.innodb是以页（大小为16K）为单位，从磁盘读到内存，一页包含多个记录，每页至少两条记录，记录有更新也是以页为单位刷到磁盘中，没一页都是按照索引大小顺序排序的。
任何池化技术都是为了提高速度，buffer pool也不例外。
如果没有buffer pool将会是什么样子？？
查询一条sql：select * from school where id = 400;
首先需要找到id=400这条记录所在的页，把这页读取到内存，然后从这页的所有记录里面找到id=400那条记录返回。
之后又查询id=401；又要按照上面的流程走一边。每次搜索都要进行磁盘io，而io的速度相对于CPU，内存速度远远不及，为了避免每次搜索都进行磁盘io，所以innodb引入了buffer pool来充当缓存。
查询现在buffer pool里面查找id = 400这条记录所在的页，如果有直接返回，如果没有则从磁盘读取，放入buffer pool返回记录。
buffer pool的功能就是缓存页，减少查询磁盘io，提高读写效率。 默认是128M（可以通过innodb_buffer_pool_size设置）
buffer pool是如何提高读写效率？
什么是脏页？存在的意义是什么?
buffer pool的淘汰策略是什么？
buffer pool和查询缓存的区别是什么？
查询缓存是server层的，缓存的是sql查询的结果
buffer pool是位于引擎层的，缓存的是数据项所在的数据页
https://juejin.cn/post/6844904035858776071mylsam和innodb的区别是啥？
可以用过SHOW ENGINES;查看数据库支持的索引
可以看到默认使用的是innodb
transactions：是否支持事务
XA：是否支持分布式事务
savepoints：保存点，回滚能力
- mylsam不支持事务（没有回滚能力），innodb支持事务。
- mylsam使用的是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键和辅助索引是独立的，innodb是聚集索引，文件存放在主键索引的叶子节点上面，因此innodb必须要有主键索引。
- mylsam不支持外键，所以innodb有包含外键的表会转换失败。
- mylsam不支持行锁，即使操作一条记录也要锁住整张表，不适合高并发操作
- innodb磁盘里面是两个文件（.frm，.idb），mylsam是三个文件
什么是索引？，说说你对索引的理解？
目的肯定是为了提高数据的查询效率，比如一本书的目录就是内容的索引，数据库也一样先根据索引找到地址。如果没有索引要找一条数据，要一条一条遍历，如果有索引就像目录一样快速查到到数据所在的页。
官方定义是，索引是帮忙mysql高效获取数据的数据结构，所以索引的本质是数据结构。
索引本身也很大，一般以文件的形式存储到磁盘上。
索引主要分为B+tree索引,Hash索引，全文索引，R-tree索引，平常说索引没有特别明指的话，就是B+tree,其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用的都是B+树索引。
索引是在存储引擎层面实现的，不是server层面，并不是所有的存储引擎都支持所有的索引
优势
提高查询效率，减少磁盘io次数
降低排序成本，降低cpu消耗
劣势
索引也需要占用内存
降低表更新的速度，除了插叙操作，还要额外维护索引信息
一张表有id自增主键，当insert17条之后，删除了15，16，17，把mysql重启，在insertid是18还是15？
mylsam是18会记录到文件
innodb15存在内存里面
索引的分类？
数据结构角度
B+tree
Hash
Full-Text
R-tree
物理存储角度
聚集索引
非聚集索引，也叫辅助索引（都是B+tree结构）
逻辑角度
主键索引：是特殊的唯一索引，不允许有空值，innodb主键自带索引（innodb如果表没有主键，就是唯一键，如果没有唯一键就会自动生成一个rowid对用户不可见，主键是用int还是varchar？用int会存储更多得key，主键上建立的索引是聚簇索引，innodb至少有一个聚簇索引 MYSQL INNODB主键使用varchar和int的区别_weixin_33895516的博客-优快云博客）
普通索引单列索引：只包含单个列，一个表有多个单列索引
多列索引（复合索引、联合索引）：指多个字段上创建索引，只有在查询条件中使用创建索引时的第一个字段，索引才会被使用，遵循最左前缀原则。
唯一索引或者非唯一索引。
数据库索引的原理，为什么要用B+tree，为什么不用二叉树？
简单介绍一下常见的查找树，想详细了解就去搜一下详细原理
二叉树
左小又大，如果插入持续增长的数字，二叉树就退化成了链表，时间复杂度就变成了O（n）
平衡二叉树
平衡二叉搜索树，又被称为AVL树，且具有以下性质：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树
红黑树
和红黑树相比，AVL树是严格的平衡二叉树，平衡条件必须满足（所有节点的左右子树高度差不超过1）。通过对任何一条从根到叶子的路径上各个节点着色的方式的限制，红黑树确保没有一条路径会比其它路径长出两倍，因此，红黑树是一种弱平衡二叉树二叉查找树、平衡二叉树、红黑树、B-/B+树性能对比_奔跑的小河-优快云博客_平衡二叉树的查找效率前言：BST、AVL、RBT、B-tree都是动态结构，查找时间基本都在O(longN)数量级上。下面做出详细对比。1. 二叉查找树 (Binary Search Tree)概念二叉查找树又称二叉搜索树，二叉排序树，特点如下: 1. 左子树上所有结点值均小于根结点 2. 右子树上所有结点值均大于根结点 3. 结点的左右子树本身又是一颗二叉查找树 4. 二叉查找树中序遍...
https://blog.youkuaiyun.com/z702143700/article/details/49079107?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164084699216780366555408%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=164084699216780366555408&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-3-49079107.pc_search_all_es&utm_term=%E5%B9%B3%E8%A1%A1%E4%BA%8C%E5%8F%89%E6%A0%91%E5%92%8C%E7%BA%A2%E9%BB%91%E6%A0%91&spm=1018.2226.3001.4187
二叉查找树与平衡二叉树_金发只是水一下的博客-优快云博客_二叉平衡树二叉查找树 二叉查找树，也称二叉搜索树，或二叉排序树。其定义也比较简单，要么是一颗空树，要么就是具有如下性质的二叉树：（1）若任意节点的左子树不空，则左子树上所有结点的值均小于它的根结点的值；（2） 若任意节点的右子树不空，则右子树上所有结点的值均大于它的根结点的值；（3） 任意节点的左、右子树也分别为二叉查找树；（4） 没有键值相等的节点。 如上图所示，是不同形态...
B+tree （树的层数低，磁盘IO次数少，三层B+tree只需要三次磁盘IO）
首先插入几条测试数据，乱序插入数据会按照id自动升序排列， 因为主键自带索引。
数据存储的内部结构类似一个链表的形式，通过指针关联不同的数据
P：指向下一个指针。
这种结构数据量大的时候查询还是特别慢，因为要遍历，mysql 如何解决？
磁盘IO与预读
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
mysql有个页的概念，相当于把数据分页，把一部分数据存入一个page中，先查页在查数据。
一个页的大小为16384B,也就是16kb，innodb一次读取16Kb（可以设置4、8、16，通过innodb_page_size来设置），取决于单个数据大小， 相当于给数据建立了上层目录，先在大目录在找数据。
假设一页存了100条数据，我要找205条，直接去第三页查找。
mysql给page也提供了快速查询的page目录（目录页）
把每个page中的第一条数据，id加指针，存入了page目录中，查询数据的时候先找到它的page，再进入page中查找数据。一个目录页中，也可以存储16kb数据，如果是海量数据page目录也会有很多。
为了提高查询效率，在最顶层，给目录页在加一层目录。
第一层是根节点，第二层是子节点，第三层是叶子节点，所有的非叶子节点只储存键值信息，所有叶子节点之间都有一个链指针
这种结构就是B+tree。
三层的数据量会存储2200万数据， 一般的数据量两层就够了。
【MySQL】面试题之：在InnoDB中一个3层B+树最多大概可以存放多少行数数据？？_CAFE-BABE的博客-优快云博客_3层b+树能存储多少数据
B+tree和B-tree的区别？
B+Tree相对于B-Tree有几点不同：
- 非叶子节点只存储键值信息；
- 所有叶子节点之间都有一个链指针；
- 数据记录都存放在叶子节点中
什么是聚簇索引和非聚簇索引？
聚簇索引：取决于数据和索引是否是放在一起的，叶子节点存储的是完整数据记录，innodb主键就是聚簇索引（innodb至少有一个聚簇索引）
非聚簇索引：索引文件与数据文件分离，叶子结点保存的是主键或者是数据地址，mylsam只支持非聚簇索引（需要知道回表、覆盖索引的概念）
聚簇索引和非聚簇索引有什么区别？什么情况用聚集索引？ - 掘金
B-tree/b+tree 原理以及聚簇索引和非聚簇索引_Sweet Baby，甜宝-优快云博客_b+tree 索引
主键索引和辅助索引？
主键索引存放的是id和完整的数据行。
辅助索引存放的索引数据列和主键的值，如果想通过辅助索引查询非索引列，需要先通过辅助索引定位到行，在通过主键去主索引查询，这个操作叫回表。
为什么推荐使用整型自增主键而不推荐使用uuid？
- 因为每页大小为 16K，uuid比整型消耗更多的存储空间。
- 在B+tree查找时需要跟经过的节点值比较大小，整型比较比字符串比较要快
- 自增整型在磁盘储存是连续的，读取也是连续的，uuid是随机的，读取也是分散的，不适合执行where id > 5 && id < 20这样的查询
- 在插入或者删除的时候整体自增主键会在叶子节点的末尾建立新的节点，不会破坏左侧子树的结构。uuid为了自身的特性，有可能会重构消耗更多的时间。
为什么非主键索引机构叶子节点储存的是主键的值？
保证数据的一致性，节省空间，就比如说订单表里面存储的是用户的id作为关联的外键，而不需要存储完整的用户信息，用户表修改后也不需要去调整订单表的信息，同时也节省了存储空间。
为什么使用B+tree而不是B-tree？
- 用B+tree而不用B-tree考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+tree是只有叶子节点才存储数据，所以查找相同数据量下，B树的高度更高，IO更频繁。
- 数据库索引是存储在磁盘上，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个次磁盘页（对应索引树的节点）
- mysql底层对B+tree进行进一步优化：在叶子节点中是双向链表，且在链表的头节点和为节点页是循环指向的。
为什么不适用Hash索引？
- 因为底层是Hash表，所以多个数据在存储关系上是完全没有顺序的关系的，所以对于区间查询是无法直接通过所以查询的，需要全表扫描。
- 所以hash索引只适用于等值查询的场景，B+tree是一种多路平衡查找树，所以他的节点是天然有序的（左子节点小于父节点，父节点小于右节点），所以B+tree范围查询不需要全表扫描。
- hash索引不支持多列联合索引的最左匹配原则，如果有大量重复键值情况下，hash索引效率会很低，因为存在hash碰撞的问题。
哪些情况需要创建索引？
- 主键自动建立唯一索引。（聚簇索引）
- 频繁作为查询条件的字段
- 查询中与其他表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题，高并发下倾向创建组合索引
- 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
- 查询中统计或分组字段
哪些情况不要创建索引？
- 表记录太少
- 经常增删改的表
- 数据重复分布均匀的表字段，如果某列重复字段过多，建立索引没有意义，识别度比较低
- 频繁更新的字段不适合创建索引（会增加io负担）
- where条件里面用不到的不合适创建索引
什么是回表，覆盖索引，索引下推？
想通过辅助索引查询非索引列，需要先通过辅助索引定位到行，在通过主键去主索引查询，这个操作叫回表。
怎么避免回表？
覆盖索引（查询列要被所建的索引覆盖。）创建的联合索引包含全部要返回的列。
判断标准：使用explain，显示为using index
索引下推？
mysql5.6之后的优化，目的是减少回表的次数
count（*）count（1）count（列名）区别？
- 列名为主键，count(列名)会比count(1)快
- 列名不为主键，count(1)会比count(列名)快
- 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
- 如果有主键，则 select count（主键）的执行效率是最优的
- 如果表只有一个字段，则 select count(*) 最优。
union和union all的区别？
- union会去重，并且会按照字段的顺序排序
- union all 就是合并两个结果，不去重，不排序
索引测试
未创建索引之前，通过执行计划可以看到是全表扫描
添加主键索引
ALTER TABLE `User` ADD PRIMARY KEY (id);
走索引之后rows为1
drop和delete有什么区别？
delete是数据库操作语言，需要事务提交才生效，只删除数据，不删除表结构。（delete from 表名 where 条件字）
drop是数据库定义语言，删除表的结构，约束，触发器，索引，表占用的空间全部释放，立即生效，不能回滚（drop table 表名）
创建数据库表
创建数据库
create database test_sy;
使用数据库
use test_sy;
创建表
CREATE TABLE IF NOT EXISTS `user`(
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`age` int NOT NULL,
`adds` DATE,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Mysql事务
事务的四大基本要素有哪些？
- 原子性（A）：整个事务中所有操作，要么全部执行，要么全部不执行，不能停在中间的某个环境，如果发生错误，会被回滚到事务开始前的状态。
- 一致性（C）：事务开始前结束后数据库的完整性没有被破坏，比如：A向B转账100，A的账户要减100，B的账户要加100。
- 隔离性（I）：一个事务的执行不能被其他事务干扰，并发执行各个事务间不能互相干扰。
- 持久性（D）：事务完成之后，事务的所有操作会被持久化到数据库中，不会被回滚。
并发事务带来的几个问题？
直接写SQL给你演示什么是脏读 幻读 可重复读，思路清晰_哔哩哔哩_bilibili
- 更新丢失（Lost Update)： 事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题
- 脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据（测试需要先设置隔离级别）
- 不可重复读（Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
- 幻读（Phantom Reads)：当某个事务在读取某个范围的记录的时候，另外一个事务又在该范围插入了新的记录，当前事务再次读取这个范围的记录，会产生幻行（Phantom Data）
幻读和不可重复读区别？
- 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
- 幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）
Mysql隔离级别有哪些？
彻底搞懂 MySQL 事务的隔离级别-阿里云开发者社区 (aliyun.com)
查询当前隔离级别
show global variables like '%isolation%';
设置隔离级别为提交读
set global transaction_isolation ='read-committed';
到底可重复读隔离级别下，解决了幻读问题没有？
了解过MVCC的同学，肯定知道或听说过当前读，和快照读。（不知道的同学，可以查找相关资料了解下，当然后续我也会有文章专门介绍MVCC)。首先要知道的是MVCC 就InnoDB 秒级建立数据快照的能力。 快照读就是读取数据的时候会根据一定规则读取事务可见版本的数据。 而当前读就是读取最新版本的数据。什么情况下使用的是快照读：（快照读，不会加锁）
一般的 select * from .... where ... 语句都是快照读
什么情况下使用的是当前读：（当前读，会在搜索的时候加锁）
select * from .... where ... for update select * from .... where ... lock in share mode update .... set .. where ... delete from. . where ..
如果事务中都使用快照读，那么就不会产生幻读现象，但是快照读和当前读混用就会产生幻读。
面试官一上来就问Mysql：幻读到底是什么？ - 知乎 (zhihu.com)
关于幻读的几个问题？附优秀博主的答疑
面试官：MySQL是怎么解决幻读问题的？ - 知乎 (zhihu.com)
MySQL中怎么解决幻读问题 - 开发技术 - 亿速云 (yisu.com)关于幻读，可重复读的真实用例是什么？ - 知乎 (zhihu.com)
1.混淆了ANSI SQL隔离级别和MySQL（默认指InnoDB引擎，下同）的隔离级别。
关于ANSI SQL隔离级别，请见《高性能MySQL》第三版 page 9
首先，ANSI SQL隔离级别标准里可重复读级别是存在幻读问题；但是InnoDB的可重复读级别通过MVCC机制解决了幻读问题！（见《高性能MySQL》第三版 page 8）所以InnoDB的可重复读是不存在幻读问题的！有小伙伴提到当前读会存在幻读，后边我会详细说明。
2.到底什么是幻读？？
幻读：当某个事务在读取某个范围的记录的时候，另外一个事务又在该范围插入了新的记录，当前事务再次读取这个范围的记录，会产生幻行（Phantom Data）。（见《高性能MySQL》第三版 page 8）
因此，InnoDB在可重复读级别下，事务A启动的时候通过MVCC建立了一个稳定的视图，在事务A过程中，无论数据怎么变更，它读到的数据都是不变的！！所以不存在幻读可能。
2. 高赞答案
关于幻读的例子是错误的。
首先，InnoDB在RR下不可能复现幻读问题，根本就不存在幻读。bigcat所举的例子其实是一个由于触发了当前读而导致数据冲突的问题。
何为当前读？就是说insert、update等语句执行之前，会先select，再执行insert、update。简单说，就是先读一次，再执行更新语句。而且这个读，是读最新的数据！！！
举个例子：（以下为可重复读隔离级别）
先初始化建表预置数据:
create table `test` ( `a` int not null primary key);
insert into `test` values(1);事务A启动并查询计数字段（值为1）：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)事务B启动，递增字段（值为2），并且提交：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set a = a + 1 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.00 sec)这时候事务A，无论你怎么查，都只能查到计数值为1。因为InnoDB在可重复读下通过MVCC提供的视图确保了不可能发生幻读！！
mysql> select * from test;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)但是！如果事务A执行insert、update语句，会触发一次当前读，当前读获得的最新计数值是2！
所以当你在事务A尝试执行以下insert一个计数行2，当前最新读就会发现主键冲突：
mysql> insert into test values(2);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'而当你在事务A尝试执行update语句，进行递增，就会在当前最新读的基础上递增，结果为3：
mysql> update test set a = a + 1 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from test;
+---+
| a |
+---+
| 1 |
| 3 |
+---+
2 rows in set (0.00 sec)等等！为什么变成两条数据了？？ 3可以理解，但是1从哪里来的？？？前边不是提到了MVCC吗，就是这玩意儿保证了1的存在。
当事务A commit 之后，就只剩下3了：
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> select *from test;
+---+
| a |
+---+
| 3 |
+---+
1 row in set (0.00 sec)综上，这个例子不能被归类为幻读，只不过是当前最新读带来的问题。
总结：
在RR隔离级别下，如果一个事务从头到尾就只有快照读，那么MVCC解决了幻读的问题。如果一个事务从头到尾只有当前读，那么MVCC通过间隙锁和临建锁也解决了幻读问题。但是如果一个事务既有快照读也有当前读，那么MVCC就解决不了幻读问题。
扫一扫
3893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
