MySQL
2022-03-14 23:41:42 41 举报AI智能生成
总结笔记
作者其他创作
大纲/内容
MySQL架构
Server层
连接器、查询缓存、分析器、优化器、执行器等
存储引擎层
InnoDB、MySALM、MEMORY、MERGE.....
一条SQL查询语句的执行过程
连接器
1、你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管 理连接。
建立连接之后会对密码和权限进行校验，才能连接完成，客户端如果长时间不发送命令到Server端，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值 是 8 小时
建立连接之后会对密码和权限进行校验，才能连接完成，客户端如果长时间不发送命令到Server端，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值 是 8 小时
查询缓存
2、建立连接之后，就需要执行select语句了
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找 到 key，那么这个 value 就会被直接返回给客户端
如果不存在缓存，就继续执行直到完成后把结果存入缓存（MySQL8.0后取消该功能 缓存极易失效）
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找 到 key，那么这个 value 就会被直接返回给客户端
如果不存在缓存，就继续执行直到完成后把结果存入缓存（MySQL8.0后取消该功能 缓存极易失效）
分析器
3、没有命中查询缓存，就要开始真正执行语句，因此需要知道你做什么，需要对 SQL 语句做解析。 分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是 什么，代表什么
优化器
4、经过分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理决定选择使用哪一个方案
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接 顺序
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接 顺序
执行器
5、执行器会先判断你有没有执行查询的权限，然后执行器就会根据表的引擎定义，去使用这个引擎提供的接口
（1）、调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
（2）、 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
（3）、 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端
（1）、调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
（2）、 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
（3）、 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端
在Mysql中有一个慢查询日志，有一个读取行的记录字段rows_examined ，这个就是读取每行时累加的数据
一条SQL更新语句的执行过程
1、根据ID的索引取得这一行所在的整页数据，加载到Buffer Pool中
2、然后把旧数据写到undo日志中，方便之后回滚
3、更新Buffer Pool缓存里面的值
4、把日志写进redo重做日志，记录什么做了修改
5、准备提交事务，redo日志写入磁盘（commit)
6、准备提交事务，写binlogbinlog记录的是更新的具体逻辑。所有存储引擎都有些binlog日志的操作
7、写一个commit标记到redo日志，为了redo与binlog数据一致
8、随机把Buffer Pool的数据写入磁盘以page为单位写入
流程示意图片
示意图
存储引擎
InnoDB
Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统
每个InnoDB表在磁盘上存储了两个文件
.frm后缀名文件存储的是表结构，.ibd存储的是索引和数据
.frm后缀名文件存储的是表结构，.ibd存储的是索引和数据
MyISAM
不支持事务也不支持外键，对事物的完整性没有要求或者以select、insert为主的应用基本都可以使用这个引擎。
特点：访问速度快
每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是
.frm（存储表定义）
.MYD（MYData，存储数据）
.MYI （MYIndex，存储索引）
.frm（存储表定义）
.MYD（MYData，存储数据）
.MYI （MYIndex，存储索引）
MEMORY
将所有数据保存在内存 中，在需要快速定位记录和其他类似数据的环境 下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内 存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。 MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果
MERGE
用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象 引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同 的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB 环境十分适合
索引
索引概念
索引是帮助Mysql高效获取数据的数据结构
索引数据结构
二叉树
二叉树插入的数据如果大于本身，会放在父节点的右下角，小的会放在父节点的左下角
会存在一种特殊情况，每次插入都比本身数据大，那么就形成了链表一样，查询效率O(n)
示意图
红黑树
又叫平衡二叉树。当一边子节点比另一边高太多的时候，会自动旋转平衡。当数据量比较大的时候比如1000万，红黑树存储的高度就可能达到几十。如果数据量越大树的高度就会越高。每查一个节点要进行一次磁盘IO交互。树的高的越高查找效率越低
示意图
Hash表
对索引key进行一次计算就可以找到数据存储的位置
很多时候比B+树还要快
但不支持范围扫描
示意图
B tree
树的高的越高查找效率越低，那么将树高缩小，比如限制在5层，把一层存放更多元素。把一个节点的数据在磁盘同一个区域全部查出来放到内存，只做一次IO查找，就可以查到很多索引信息。B树又叫平衡多叉树
B树的特点就是每层节点数目非常多，层数很少,遍历效率低
示意图
B+tree
B+树节点只存储 key 的副本，真实的 key 和 data 域都在叶子节点存储，数据全部存储在叶子节，并且每一个节点之间用指针串联起来，形成链表，方便遍历，可以跨区间访问（范围查找方便）
一棵B+Tree可以存放多少行数据
InnoDB页大小为16kb，假如存放主键ID为bigint类型的数据8字节，加上InnoDB中的指针大小6字节，这样一共14字节
一页能存放1600/14约等于1170个数据，可以算出一棵高度为2 的B+树，能存放 1170 * 16 = 18720 条这样的数据记录
高度为3的b+Tree，可以存放1170*1170*16=21902400数据，每查找一页就是一次IO，只需要3次磁盘IO，效率极高
一页能存放1600/14约等于1170个数据，可以算出一棵高度为2 的B+树，能存放 1170 * 16 = 18720 条这样的数据记录
高度为3的b+Tree，可以存放1170*1170*16=21902400数据，每查找一页就是一次IO，只需要3次磁盘IO，效率极高
B+Tree的高度是由非叶子节点能放多少个索引决定的
示意图
聚集索引和非聚集索引
聚集索引（聚类索引、簇集索引））
索引和数据存储在一起的叫聚集索引，如InnoDB
物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序，可以理解为在文件中也是按b+Tree的顺序存放的
聚集索引中叶节点包含了完整的数据记录
一个表只能有一个
非聚集索引（非聚类索引、非簇集索引）
索引和数据分开存储的叫非聚集索引，如MyISAM
物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序
一个表可以有多个
联合索引
联合索引就是指，由两个或以上的字段共同构成一个索引
最左前缀原则它会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推
最左前缀原则它会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推
为什么要使用联合索引
减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知
联合索引无效情况
没有按最左前缀匹配来，而是查询的中间的索引开始，就好像只能先根据姓，再根据名，或者直接根据姓名才能查询到你这个人，直接查询名是找不到你这个人的
不符合最左前缀原则，范围查询
索引使用总结
索引分析
Explain
使用Explain关键字可以对SQl语句进行分析
Explain的列
id（select标识）
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。 id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
select_type（查询的类型）
SIMPLE
简单查询。查询不包含子查询和union
PRIMARY
复杂查询中最外层的 select
UNION
在 union 中的第二个和随后的 select
SUBQUERY
包含在 select 中的子查询（不在 from 子句中）
DERIVED
包含在 from 子句中的子查询
table
输出结果集的表
type
NULL
直接返回结果，不访问任何表索引
system
system查询结果只有一条的数据,const类型的特例
const
const根据主键或者唯一索引进行查询，表示一次就找到了
eq_ref
主键或者唯一索引，使用多表关联查询查询出来的数据只有一条
ref
根据非唯一性的索引查询，返回的记录有多条，比如给某个字段添加索引
range
范围查询 between <> in等操作，前提是用索引，要自己设定索引字段
index
扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接 对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这 种通常比ALL快一些
ALL
遍历所有数据文件，扫描的聚集索引有整张表的所有数据，效率最低
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
一般来说，得保证查询达到range级别，最好达到ref
possible_keys
显示查询可能使用哪些索引来查找
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引 对此查询帮助不大，选择了全表查询
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提 高查询性能，然后用 explain 查看效果
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提 高查询性能，然后用 explain 查看效果
key
显示mysql实际采用哪个索引来优化对该表的访问
key_len
mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
计算规则
char(n)和varchar(n)，n均代表字符数，而不是字节数，如果是utf-8，一个数字 或字母占1个字节，一个汉字占3个字节
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度
tinyint
1字节
smallint
2字节
int
4字节
bigint
8字节
date
3字节
timestamp
4字节
datetime
8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL，索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索 引
ref
索引关联查询的字段，常见的有：const（常量），字段名（例：film.id）
rows
是mysql估计要读取并检测的行数，注意这个不是结果集里的行数
Extra
Using index
覆盖索引：从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数
使用覆盖索引，mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中 获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个 查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
select film_id from film_actor where film_id = 1;select后面的字段就是索引，直接通过辅助索引就能拿到值
Using where
使用 where 语句来处理结果，并且查询的列未被索引覆盖
Using index condition
查询的列不完全被索引覆盖，where条件中是一个索引的范围
using temporary
mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索 引来优化
Using filesort
将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。
Select tables optimized away
使用某些聚合函数（比如 max、min）来访问存在索引的某个字段
锁机制
行锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景
乐观锁
底层CAS+自旋
悲观锁
比如synchronized阻塞
读锁(共享锁)
会阻塞写，不会阻塞读
写锁(排它锁)
写锁会把读和写都阻塞
并发事务处理带来的问题
更新丢失(Lost Update)或脏写
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存 在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新
脏读（Dirty Reads）
一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B 事务回滚，A读取的数据无效，不符合一致性要求
不可重读（Non-Repeatable Reads）
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改 变、或某些记录已经被删除了！这种现象就叫做“不可重复读”
一句话：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
一句话：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。 一句话：事务A读取到了事务B提交的新增数据，不符合隔离性
隔离级别
读未提交
别人改数据的事务尚未提交，我在我的事务中也能读到
读已提交
别人改数据的事务提交之后，我在我的事务中才能读到
底层MVCC
可重复读（默认）
别人改数据的事务已经提交，我在我的事务中也不去读，我读到的还是我自己刚开始看到的数据
底层MVCC
可串行化
客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围都会被加锁，如果客户端B在该范围内插入数据都会被阻塞，所以就避免了幻读
我的事务尚未提交，别人就别想改数据，改不了，得等我改完了才能继续改
（并发性极低）
我的事务尚未提交，别人就别想改数据，改不了，得等我改完了才能继续改
（并发性极低）
MVCC多版本并发控制机制
Mysql在可重复读隔离级别下较高的隔离性就是由MVCC机制来保证的，MVCC底层实际上是由undo日志版本链与read view机制实现的，使得不同的事务会根据数据版本链对比规则读取 同一条数据在版本链上的不同版本数据
undo版本链比对机制
是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚 日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链
示意图
一致性视图read-view
在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，这个视图由执行查询时所有未提交事 务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应 版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果
版本链比对规则：
1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；
2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；
3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自 己的事务是可见的)；
b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见
1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；
2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；
3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自 己的事务是可见的)；
b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见
日志模块
重做日志（redo log）
确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据
回滚日志（undo log）
保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
二进制日志（binlog）
用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步，可以简单认为记录的就是sql语句
普通查询日志 general query log
记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来
慢查询日志
慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句，默认不开启的
中继日志(relay log)
relay log是复制过程中产生的日志，很多方面都跟binary log差不多，区别是: relay log是从库服务器I/O线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件，然后从库的SQL线程会读取relay-log日志的内容并应用到从库服务器上
常见问题
为什么建议InnoDB表必须建立主键，并且推荐使用整型主键
InnoDB .ibd后缀的文件，里面是一个B+树，如果表里面有主键就会利用主键作为整个B+树的索引
如果没有指定主键，mysql就会遍历所有列，找一个里面值都不相同的列作为主键
如果找不到，就会自己创建一个隐藏列，唯一的ID内置6字节长的ROWID作为主键
如果没有指定主键，mysql就会遍历所有列，找一个里面值都不相同的列作为主键
如果找不到，就会自己创建一个隐藏列，唯一的ID内置6字节长的ROWID作为主键
主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页，如果不用自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面
为什么非主键索引结构叶子结点存储的是主键值
主键索引和非主键索引的区别是：非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引
主键查询的方式，则只需要搜索 ID 这棵 B+树
非主键的查询方式，则先搜索非主键索引树，得到主键的ID,再通过主键ID搜索一次才能找到完整数据，这个过程也被称为回表
主键查询的方式，则只需要搜索 ID 这棵 B+树
非主键的查询方式，则先搜索非主键索引树，得到主键的ID,再通过主键ID搜索一次才能找到完整数据，这个过程也被称为回表
保障数据一致性
如果非主键索引的叶子节点也存储一份数据，如果通过非主键索引插入数据，那么要向主键索引对应的行数据进行同步，那么会带来数据一致性问题
当数据需要更新的时候，非主键索引不需要修改，只需要修改主键索引，一个表只能有一个主键索引，其他的都是非主键索引，这样只需要修改主键索引就可以了，不需要重新构建非主键索引
节省存储空间
主键索引和非主键索引维护各自的B+树结构，当插入的数据的时候，由于数据只有一份，通过非主键索引获取到主键值，然后再去主键索引的B+树数据结构中找到对应的行数据，节省了内存空间
通俗来理解
就是订单表里面有个用户ID信息，关联用户表信息，即便订单其他数据修改也不影响用户信息，倘若把用户信息存放在订单表，还需要维护订单表的用户数据
为什么最左前缀原则一定要按顺序查询才会走索引？
底层是B+树，根据索引字段从左到右排好序的。假如不按顺序跳过了第一列，那么相对于整个b+Tree来说是无序的，参照姓名例子
为什么Mysql不能直接更新磁盘上的数据而且设置Buffer Pool缓存机制来执行SQL了？
它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能 保证各种异常情况下的数据一致性
更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。 正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求
磁盘随机读写的性能是非常差的，不能抗住高并发
数据库三大范式是什么
第一范式：每个列都不可以再拆分
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分
主键的其他列，是只能根据主键id才能获取的，也是主键约束
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键
也就是同一个表不要存储相同字段要用外键去约束
MyISAM索引与InnoDB索引的区别
InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
B树和B+树的区别
在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。
什么是死锁？怎么解决？
常见的解决死锁的方法
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
如果业务处理不好可以用分布式事务锁或者使用乐观锁
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
如果业务处理不好可以用分布式事务锁或者使用乐观锁
什么是脏读？幻读？不可重复读？
什么是事务的隔离级别？MySQL的默认隔离级别是什么？
InnoDB存储引擎的锁的算法有三种
Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身
什么是存储过程？有哪些优缺点？
存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
优点
1）存储过程是预编译过的，执行效率高。
2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3）安全性高，执行存储过程需要有一定权限的用户。
4）存储过程可以重复使用，减少数据库开发人员的工作量。
1）存储过程是预编译过的，执行效率高。
2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3）安全性高，执行存储过程需要有一定权限的用户。
4）存储过程可以重复使用，减少数据库开发人员的工作量。
缺点
1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。
1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。
CHAR 和VARCHAR 的区别
1、CHAR 和 VARCHAR 类型在存储和检索方面有所不同
2、CHAR 列长度固定为创建表时声明的长度， 长度值范围是 1 到 255 当 CHAR 值被存储时， 它们被用空格填充到特定长度， 检索 CHAR 值时需删除尾随空格
2、CHAR 列长度固定为创建表时声明的长度， 长度值范围是 1 到 255 当 CHAR 值被存储时， 它们被用空格填充到特定长度， 检索 CHAR 值时需删除尾随空格
五种关联查询？
交叉连接（CROSS JOIN）
没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义
内连接（INNER JOIN）
同时符合某种条件的数据记录的集合
外连接（LEFT JOIN/RIGHT JOIN）
左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN
联合查询（UNION与UNION ALL）
就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
如果使用UNION ALL，不会合并重复的记录行
全连接（FULL JOIN）
MySQL不支持
mysql中 in 和 exists 区别
exists是用循环遍历本表，然后一条条对比
in是把里面的查询出来作为b结果，再一条条和其他表对比
大表连小表，用in，小连大用exist
SQL优化
本质就是查看是否走索引
Explain的type列、extra列
优化Order by、Group By、where、以及排查不走索引、使索引失效的原因
超大分页怎么处理？
MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写
先快速定位需要获取的id段，然后再关联
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
先快速定位需要获取的id段，然后再关联
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
MySQL中count(字段) ，count(主键 id) ，count(1)和count(*)的区别
count(可空字段)
扫描全表，读到server层，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加
count(非空字段)与count(主键 id)
扫描全表，读到server层，判断字段不可空，按行累加。
count(1)
扫描全表，但不取值，server层收到的每一行都是1，判断不可能是null，按值累加
count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作
count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作
count(*)
MySQL 执行count(*)在优化器做了专门优化。因为count(*)返回的行一定不是空。扫描全表，但是不取值，按行累加
count(可空字段) < count(非空字段) = count(主键 id) < count(1) ≈ count(*)
undo log和redo log了解过吗？它们的作⽤分别是什么？
undo log和redo log是mysql中InnoDB存储引擎的基本组成：
（1）undo log保存了事务执⾏前数据的值，以便于事务回滚时能回到事务执⾏前的数据版本，多次 更新会有undo log的版本链；
（2）redo log在物理层⾯上记录了事务操作的⼀系列信息，保证就算遇到mysql宕机等因素还没来 得及将数据刷到磁盘⾥，通过redo log也能恢复事务提交的数据。
（1）undo log保存了事务执⾏前数据的值，以便于事务回滚时能回到事务执⾏前的数据版本，多次 更新会有undo log的版本链；
（2）redo log在物理层⾯上记录了事务操作的⼀系列信息，保证就算遇到mysql宕机等因素还没来 得及将数据刷到磁盘⾥，通过redo log也能恢复事务提交的数据。
redo log怎样保证事务不丢失的？
当⼀个事务提交成功后，虽然缓冲池中的数据不⼀定来得及⻢上落地到磁盘中，但是redo log记录 的事务信息持久化到磁盘中了、且含有commit标记，此时如果mysql宕机导致缓冲池中的、已经被事务 更新过的内存数据丢失了，此时在mysql重启时，将磁盘中的redo log中将事务变更信息给加载到缓冲 池中，保证事务信息不会丢失。或者redo log刷盘了，binlog写成功了，在重启时会⾃动给上commit标 记，在重放数据。
.更新操作为什么不直接更新磁盘反⽽设计这样⼀个复杂的InnoDB存储引擎来完成？
直接更新磁盘是随机IO写，存在磁盘地址寻址操作，性能⾮常低，承载不了⾼并发场景； ⽽转换为InnoDB中，内存⾼速读写、redo log和undo log顺序写磁盘性能相对于随机IO写性能会 ⾼的多，⽽这种性能上的提⾼⾜以抵消这种架构上带来的复杂，可在⼀定QPS内承载⾼并发场景
事务是先提交还是先刷盘？
事务先提交后刷盘； 1.Redo log刷盘成功->2.Binlog刷盘->3.BinLog名称和⽂件路径信息、commit标志写到Redo log 中，事务两阶段提交的⽅式来保证
0 条评论
下一页
