首页  思维导图  详情

mysql相关内容学习总结整理

2022-08-26 09:45:53   0  举报





AI智能生成

为你推荐

查看更多



MySQL

MySQL

mysql知识整理脑图

mysql

知识脑图

数据分析

作者其他创作

大纲/内容

子主题

架构图

将查询处理和其它的系统任务以及数据的存储提取相分离

插件式的存储引擎架构

连接层

服务层

引擎层

存储层

组成结构

1.客户端访问

2.连接器

mysql高版本后取消缓存功能8之后取消的

3.查询缓存

对sql语法进行分析

4.分析器

对执行的sql选择最优的执行方案

5.优化器

6.执行器

7.去引擎层获取数据并进行返回

流程图

mysql的查询流程

架构

概念

查看存储引擎

设置存储引擎

查看数据文件存储位置

操作

支持行锁

支持事务

支持外键

事务方面的支持

.frm文件

.ibd文件

.ibdata文件

.ibd/.ibdata文件

文件存储结构

InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。

数据页

插入缓冲（insert buffer)

二次写(double write)

自适应哈希索引(ahi)

预读(read ahead)

特性

InnoDB(mysql5.5之后默认存储引擎)

只支持表锁

不支持事务

不支持外键

事务方面支持

.MYD文件

.MYI文件

MyISAM

支持Hash索引

Memory

NDB

1.InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

2.InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

3.InnoDB 是聚簇索引，MyISAM 是非聚簇索引。

4.InnoDB 不保存表的具体行数

5.InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。

主外键

事务

锁

缓存

表空间大小

关注点

细节对比

1.InnoDB和MyISAM的区别

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

3.哪个存储引擎执行 select count(*) 更快，为什么?

相关面试题

存储引擎类型

存储引擎

BIT

BOOL

TINY INT

SMALL INT

MEDIUM INT

INT

BIG INT

整型

FLOAT

DOUBLE

DECIMAL

浮点型

类型图解

数值类型

CHAR

VARCHAR

char

TINY TEXT

TEXT

MEDIUM TEXT

LONGTEXT

text

TINY BLOB

BLOB

MEDIUM BLOB

LONG BLOB

Blob

字符串类型

Date

DateTime

TimeStamp

Time

Year

日期类型

BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection

其他数据类型

1.char是固定长度，varchar长度可变：

2.char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符

3.存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间

char(n)，varchar(n)中的n都代表字符的个数

超过char，varchar最大长度n的限制后，字符串会被截断。

相同点

char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。

char在存储时会截断尾部的空格，而varchar不会。

char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。

不同点

CHAR 和 VARCHAR 的区别？

字符串类型是：SET、BLOB、ENUM、CHAR、CHAR、TEXT、VARCHAR

列的字符串类型可以是什么？

BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB

span style=\

BLOB 保存二进制数据，TEXT 保存字符数据。

BLOB和TEXT有什么区别？

面试题

数据类型

索引的目的

索引存储位置

索引的概念

创建索引

添加主键索引

添加唯一索引

添加普通索引

添加全文索引

修改表结构(添加索引)

删除索引

查看索引

索引操作

B+树索引

Hash索引

Full-Text全文索引

R-Tree索引

数据结构角度

聚集索引

非聚集索引

物理存储角度

主键索引

普通索引或者单列索引

多列索引（复合索引、联合索引）

唯一索引或者非唯一索引

空间索引

逻辑角度

索引分类

提高数据检索效率，降低数据库IO成本

降低数据排序的成本，降低CPU的消耗

好处

索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

劣势

索引的优劣

B-Tree是为磁盘等外存储设备设计的一种平衡查找树

InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的大小：show variables like 'innodb_page_size';

使用场景

除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。

若根节点不是叶子节点，则至少有2个孩子

所有叶子节点都在同一层，且不包含其它关键字信息

关键字的个数n满足：ceil(m/2)-1 <= n <= m-1

一棵m阶的B-Tree有如下特性：

B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值

示意图

B-Tree

非叶子节点只存储键值信息；

所有叶子节点之间都有一个链指针；

数据记录都存放在叶子节点中

B+Tree相对于B-Tree的不同

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。

B+Tree

B+Tree和B-Tree的区别

一种是对于主键的范围查找和分页查找

一种是从根节点开始，进行随机查找。

可以对B+Tree进行两种查找运算：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

B+Tree结构

MyISAM引擎的索引文件和数据文件是分离的

MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址

索引文件与数据文件分离，这样的索引称为非聚簇索引

MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

在MyISAM中，索引（含叶子节点）存放在单独的.myi文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）

通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。

主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

MyISAM主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录

InnoDB的数据文件本身就是主键索引文件，这样的索引被称为聚簇索引

一个表只能有一个聚簇索引

在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。Innodb的数据文件可以按照表来切分（只需要开启innodb_file_per_table)，切分后存放在xxx.ibd中，默认不切分，存放在xxx.ibdata中

辅助（非主键）索引

InnoDB主键索引与辅助索引的结构

Hash算法

检索算法

MySQL目前有Memory引擎和NDB引擎支持Hash索引

全文索引的作用

实现方式

full-text全文索引

R-Tree空间索引

mysq索引结构

1.主键自动建立唯一索引

2.频繁作为查询条件的字段

3.查询中与其他表关联的字段，外键关系建立索引

4.单键/组合索引的选择问题，高并发下倾向创建组合索引

5.查询中排序的字段，排序字段通过索引访问大幅提高排序速度

6.查询中统计或分组字段

需要建立索引的情况

1.表记录太少

2.经常增删改的表

3.数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）

4.频繁更新的字段不适合创建索引（会加重IO负担）

5.where条件里用不到的字段不创建索引

不需要建立索引的情况

索引的建立选择

判断标准

覆盖索引

说说你对 MySQL 索引的理解？

mysql数据库索引的原理，为什么要用 B+树，为什么不用二叉树？

聚集索引与非聚集索引的区别？

InnoDB引擎中的索引策略，了解过吗？

创建索引的方式有哪些？

聚簇索引/非聚簇索引，mysql索引底层实现，为什么不用B-tree，为什么不用hash，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？

MySQL 索引底层实现

叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？

使用索引查询一定能提高查询的性能吗？为什么?

那为什么mysql推荐使用整型自增主键而不是选择UUID？

为什么非主键索引结构叶子节点存储的是主键值？

索引

查看InnoDB数据页大小

查看当前事务隔离级别

检查死锁

查看开启状态

开启慢查询日志

慢查询日志

创建数据库

查看数据库创建语句

查看所有数据库

使用某个库

数据库操作

命令行登录

数据库登录

mysql命令

1.count(*) 和 count(1)和count(列名)区别

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

2.MySQL中 in和 exists 的区别

UNION和UNION ALL都是将两个结果集合并为一个，两个要联合的SQL语句字段个数必须一样，而且字段类型要“相容”（一致）；

3.UNION和UNION ALL的区别?

4.sql的执行顺序

5.mysql 的内连接、左连接、右连接有什么区别？什么是内连接、外连接、交叉连接、笛卡尔积呢？

sql

A原子性

C一致性

I隔离性

D持久性

ACID

更新丢失（Lost Update)

脏读(Dirty Reads)

不可重复读（Non-Repeatable Reads)

幻读（Phantom Reads)

不可重复读的重点是修改

幻读的重点在于新增或者删除

幻读和不可重复读的区别：

问题

“更新丢失”

“脏读” 、 “不可重复读”和“幻读”

处理方案

并发事务处理带来的问题和相关处理方案

READ-UNCOMMITTED(读未提交)

READ-COMMITTED(读已提交)

REPEATABLE-READ(可重复读)(MySQL的InnoDB引擎默认隔离级别)

SERIALIZABLE(可串行化)

事务隔离级别

原理

乐观（optimistic）并发控制

悲观（pressimistic）并发控制

底层

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

REPEATABLE READ（可重读）隔离级别下MVCC

MySQL的InnoDB中MVCC实现方式

MVCC机制

事务日志的产生原因

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。

事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据

事务日志可以帮助提高事务效率

MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。

事务的实现就是如何实现ACID特性。

事务的实现

实现持久化和原子性

参考链接

在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。

redolog写入到磁盘涉及三种方式

缓存池

日志缓存

系统缓存

磁盘

redolog写入涉及到的几个位置

redo log日志的写入方式

1效率问题

2安全性问题

使用日志持久化而不是直接数据持久化的原因

重做日志redo

实现一致性

undo log 主要为事务的回滚服务。记录为逻辑日志

回滚日志undo

事务日志分类

事务日志

分布式事务的实现

应用程序（AP）

资源管理器（RM）

事务管理器（TM）

模型中分三块

MySQL 的分布式事务模型

第一阶段所有的事务节点开始准备，告诉事务管理器ready

第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

分布式事务采用两段式提交（two-phase commit）的方式

MySQL对分布式事务的支持

1.事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

2.什么是幻读，脏读，不可重复读呢？

3.MySQL事务的四大特性以及实现原理

4.MVCC熟悉吗，它的底层原理？

5.事务是如何通过日志来实现的，说得越深入越好。

错误日志

查询日志

二进制日志

中继日志

6.你知道MySQL 有多少种日志吗？

7.分布式事务相关问题，可能还会问到 2PC、3PC

读锁（共享锁）

写锁（排他锁）

从对数据操作的类型分类

表级锁

行级锁

页面锁

从对数据操作的粒度分类

锁的分类

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

两种表锁

锁实现

MyISAM 表锁

排他锁（X）

共享锁（S）

行锁

意向共享锁（IS）

意向排他锁（IX）

意向锁

索引失效会导致行锁变表锁

记录锁(Record Locks)

间隙锁（Gap Locks）

临键锁(Next-key Locks)

锁模式(InnoDB有三种行锁的算法)

明确指定主键，并且有此笔资料，row lock

明确指定主键，若查无此笔资料，无lock

无主键，table lock

主键不明确，table lock

select for update锁的不同状态

InnoDB锁

各个存储引擎对于锁的支持

乐观锁

悲观锁

加锁机制

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环

当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁

锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

死锁的产生

死锁的检测

死锁恢复

外部锁的死锁检测

死锁影响性能

MyISAM避免死锁

在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁

在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。

如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句

改变事务隔离级别

InnoDB避免死锁：

不同存储引擎对死锁的处理

死锁

1.数据库的乐观锁和悲观锁？

2.MySQL 中有哪几种锁，列举一下？

3.MySQL中InnoDB引擎的行锁是怎么实现的？

4.MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁

5.select for update有什么含义，会锁表还是锁行还是其他

6.MySQL 遇到过死锁问题吗，你是如何解决的？

锁机制

1.业务需求对MySQL的影响(合适合度)

7.其他一些访问频繁但变更较少的数据

11.不适合放进MySQL的数据

12.需要放进缓存的数据

13.Schema设计对系统的性能影响

14.尽量减少对数据库访问的请求

15.尽量减少无用数据的查询请求

业务影响

8.二进制多媒体数据

9.流水队列数据

10.超大文本数据

IO影响

4.活跃用户的基本信息数据

5.活跃用户的个性化定制信息数据

6.准实时的统计信息数据

附加操作影响

2.存储定位对MySQL的影响

3.系统各种配置及规则数据

16.硬件环境对系统性能的影响

硬件影响

影响mysql的性能因素

1.客户端向 MySQL 请求一条 Query

2.命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimizer

3.MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值

4.对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等

5.然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划

6.如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

执行过程

MySQL Query Optimizer

CPU

服务器硬件的性能瓶颈查看命令

MySQL常见瓶颈

性能分析

1.查询语句写的烂

2.索引失效（单值、复合）

3.关联查询太多join（设计缺陷或不得已的需求）

4.服务器调优及各个参数设置（缓冲、线程数等）

性能下降SQL慢执行时间长等待时间长原因分析

永久配置

临时配置

手动开启慢查询日志

得到返回记录集最多的10个SQL

得到访问次数最多的10个SQL

得到按照时间排序的前10条里面含有左连接的查询语句

也可以和管道配合使用

pt-query-digest

日志分析工具

1.慢查询日志

1.表的读取顺序

2.数据读取操作的操作类型

3.哪些索引可以使用

4.哪些索引被实际使用

5.表之间的引用

6.每张表有多少行被优化器查询

作用

使用方式

SIMPLE

PRIMARY

SUBQUERY

DERIVED

UNION

UNION RESULT

查询类型

select_type

table

system

const

eq_ref

ref

range

index

ALL

使用索引情况

type

possible_keys

key

key_len

rows

using filesort:

Using temporary

using index

using where

using join buffer

impossible where

select tables optimized away

distinct

Extra

显示内容的意义

示例图

第一行（执行顺序4）

第二行（执行顺序2）

第三行（执行顺序3）

第四行（执行顺序1）

第五行（执行顺序5）

示例分析

分析示例

2.EXPLAIN 分析查询

字段结果意义

查看是否开启

开启功能

show profiles;

3.profiling分析

show status ——显示状态信息（扩展show status like ‘XXX’）

show variables ——显示系统变量（扩展show variables like ‘XXX’）

show innodb status ——显示InnoDB存储引擎的状态

show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

mysqladmin variables -u username -p password——显示系统变量

mysqladmin extended-status -u username -p password——显示状态信息

4.show命令查询系统状态及系统变量

MySQL常见性能分析手段

1.全值匹配

3.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select

7.like \"xxxx%\" 是可以用到索引的，like \"%xxxx\" 则不行(like \"%xxx%\" 同理)。like以通配符开头('%abc...')索引失效会变成全表扫描的操作，

8.字符串不加单引号索引失效

9.少用or，用它来连接时会索引失效

10.<，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

索引优化

对于单键索引，尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

少用Hint强制索引

索引使用建议

1.永远小标驱动大表（小的数据集驱动大的数据集）

1.order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序

2.MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；

ORDER BY语句使用索引最左前列

使用where子句与ORDER BY子句条件列组合满足索引最左前列

3.ORDER BY 满足两种情况，会使用Index方式排序

4.尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀

单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序

5.如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序

增大sort_buffer_size参数的设置

增大max_lencth_for_sort_data参数的设置

6.优化策略

2.order by关键字优化

1.group by实质是先排序后进行分组，遵照索引建的最佳左前缀

2.当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置

3.where高于having，能写在where限定的条件就不要去having限定了

3.GROUP BY关键字优化

1.更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型

2.简单就好：简单的数据类型通常需要更少的CPU周期

3.尽量避免NULL：通常情况下最好指定列为NOT NULL

3.数据类型优化

查询优化

性能优化

1.日常工作中你是怎么优化SQL的？

2.SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？

3.如何写sql能够有效的使用到复合索引？

4.一条sql执行过长的时间，你如何优化，从哪些方面入手？

5.什么是最左前缀原则？什么是最左匹配原则？

6.查询中哪些情况不会使用索引？

1.逻辑数据分割

2.提高单一的写和读应用速度

3.提高分区范围读查询的速度

4.分割数据能够有多个不同的物理文件路径

5.高效的保存历史数据

查看是否支持表分区