当前位置 博文首页 > cainno的博客:MySql史上最全总结
?
写在之前:不建议那种上来就是各种面试题罗列,然后背书式的去记忆,对技术的提升帮助很小,对正经面试也没什么帮助,有点东西的面试官深挖下就懵逼了。
个人建议把面试题看作是费曼学习法中的回顾、简化的环节,准备面试的时候,跟着题目先自己讲给自己听,看看自己会满意吗,不满意就继续学习这个点,如此反复,好的offer离你不远的,奥利给
和其它数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
?
画出 MySQL 架构图,这种变态问题都能问的出来
MySQL 的查询流程具体是?or 一条SQL语句在MySQL中如何执行的?
客户端请求 —> 连接器(验证用户身份,给予权限) —> 查询缓存(存在缓存则直接返回,不存在则执行后续操作) —> 分析器(对SQL进行词法分析和语法分析操作) —> 优化器(主要对执行的sql优化选择最优的执行方案方法) —> 执行器(执行时会先看用户是否有执行权限,有才去使用这个引擎提供的接口) —> 去引擎层获取数据返回(如果开启查询缓存则会缓存查询结果)
?
说说MySQL有哪些存储引擎?都有哪些区别?
存储引擎是MySQL的组件,用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。
使用哪一种引擎可以灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求,使用合适的存储引擎,将会提高整个数据库的性能 。
MySQL服务器使用可插拔的存储引擎体系结构,可以从运行中的 MySQL 服务器加载或卸载存储引擎 。
-- 查看支持的存储引擎SHOW ENGINES-- 查看默认存储引擎SHOW VARIABLES LIKE 'storage_engine'--查看具体某一个表所使用的存储引擎,这个默认存储引擎被修改了!show create table tablename--准确查看某个数据库中的某一表所使用的存储引擎show table status like 'tablename'show table status from database where name="tablename"
-- 建表时指定存储引擎。默认的就是INNODB,不需要设置CREATE TABLE t1 (i INT) ENGINE = INNODB;CREATE TABLE t2 (i INT) ENGINE = CSV;CREATE TABLE t3 (i INT) ENGINE = MEMORY;-- 修改存储引擎ALTER TABLE t ENGINE = InnoDB;-- 修改默认存储引擎,也可以在配置文件my.cnf中修改默认引擎SET default_storage_engine=NDBCLUSTER;
默认情况下,每当 CREATE TABLE
或 ALTER TABLE
不能使用默认存储引擎时,都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为,可以启用 NO_ENGINE_SUBSTITUTION SQL
模式。如果所需的引擎不可用,则此设置将产生错误而不是警告,并且不会创建或更改表
常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。
InnoDB 现在是 MySQL 默认的存储引擎,支持事务、行级锁定和外键
在 MySQL中建立任何一张数据表,在其数据目录对应的数据库目录下都有对应表的 .frm
文件,.frm
文件是用来保存每个数据表的元数据(meta)信息,包括表结构的定义等,与数据库存储引擎无关,也就是任何存储引擎的数据表都必须有.frm
文件,命名方式为 数据表名.frm,如user.frm。
查看MySQL 数据保存在哪里:show variables like 'data%'
MyISAM 物理文件结构为:
.frm
文件:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等.MYD
(MYData
) 文件:MyISAM 存储引擎专用,用于存储MyISAM 表的数据.MYI
(MYIndex
)文件:MyISAM 存储引擎专用,用于存储MyISAM 表的索引相关信息InnoDB 物理文件结构为:
.frm
文件:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
.ibd
文件或 .ibdata
文件:这两种文件都是存放 InnoDB 数据的文件,之所以有两种文件形式存放 InnoDB 的数据,是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据,还是用独享表空间存放存储数据。
独享表空间存储方式使用.ibd
文件,并且每个表一个.ibd
文件 共享表空间存储方式使用.ibdata
文件,所有表共同使用一个.ibdata
文件(或多个,可自己配置)
?
ps:正经公司,这些都有专业运维去做,数据备份、恢复啥的,让我一个 Javaer 搞这的话,加钱不?
select count(*) from table
时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;对比项 | MyISAM | InnoDB |
---|---|---|
主外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 |
表空间 | 小 | 大 |
关注点 | 性能 | 事务 |
默认安装 | 是 | 是 |
?
一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ?
如果表的类型是MyISAM,那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中,重启MySQL自增主键的最大ID也不会丢失;
如果表的类型是InnoDB,那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中,所以重启数据库或对表进行OPTION操作,都会导致最大ID丢失。
?
哪个存储引擎执行 select count(*) 更快,为什么?
MyISAM更快,因为MyISAM内部维护了一个计数器,可以直接调取。
InnoDB 中 count(*) 语句是在执行的时候,全表扫描统计总数量,所以当数据越来越大时,语句就越来越耗时了,为什么 InnoDB 引擎不像 MyISAM 引擎一样,将总行数存储到磁盘上?这跟 InnoDB 的事务特性有关,由于多版本并发控制(MVCC)的原因,InnoDB 表“应该返回多少行”也是不确定的。
主要包括以下五大类:
?
CHAR 和 VARCHAR 的区别?
char是固定长度,varchar长度可变:
char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
存储时,前者不管实际存储数据的长度,直接按 char 规定的长度分配存储空间;而后者会根据实际存储的数据分配最终的存储空间
相同点:
不同点:
char是适合存储很短的、一般固定长度的字符串。例如,char非常适合存储密码的MD5值,因为这是一个定长的值。对于非常短的列,char比varchar在存储空间上也更有效率。
?
列的字符串类型可以是什么?
字符串类型是:SET、BLOB、ENUM、CHAR、CHAR、TEXT、VARCHAR
?
BLOB和TEXT有什么区别?
BLOB是一个二进制对象,可以容纳可变数量的数据。有四种类型的BLOB:TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB
TEXT是一个不区分大小写的BLOB。四种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
BLOB 保存二进制数据,TEXT 保存字符数据。
?
说说你对 MySQL 索引的理解?
数据库索引的原理,为什么要用 B+树,为什么不用二叉树?
聚集索引与非聚集索引的区别?
InnoDB引擎中的索引策略,了解过吗?
创建索引的方式有哪些?
聚簇索引/非聚簇索引,mysql索引底层实现,为什么不用B-tree,为什么不用hash,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方?
创建:
创建索引:CREATE [UNIQUE] INDEX indexName ON mytable(username(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
修改表结构(添加索引):ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)
删除:DROP INDEX [indexName] ON mytable;
查看:SHOW INDEX FROM table_name\G
--可以通过添加 \G 来格式化输出信息。
使用ALERT命令
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list
这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。ALTER TABLE tbl_name ADD INDEX index_name (column_list)
添加普通索引,索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
该语句指定了索引为 FULLTEXT ,用于全文索引。聚集索引(clustered index)
非聚集索引(non-clustered index),也叫辅助索引(secondary index)
聚集索引和非聚集索引都是B+树结构
?
为什么MySQL 索引中用B+tree,不用B-tree 或者其他树,为什么不用 Hash 索引
聚簇索引/非聚簇索引,MySQL 索引底层实现,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方?
使用索引查询一定能提高查询的性能吗?为什么?
首先要明白索引(index)是在存储引擎(storage engine)层面实现的,而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型,它们的实现和行为也可能有所差别。
MyISAM 和 InnoDB 存储引擎,都使用 B+Tree的数据结构,它相对与 B-Tree结构,所有的数据都存放在叶子节点上,且把叶子节点通过指针连接到一起,形成了一条数据链表,以加快相邻数据的检索效率。
先了解下 B-Tree 和 B+Tree 的区别
B-Tree是为磁盘等外存储设备设计的一种平衡查找树。
系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
InnoDB 存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB,可通过参数 innodb_page_size
将页的大小设置为 4K、8K、16K,在 MySQL 中可通过如下命令查看页的大小:show variables like 'innodb_page_size';
而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。
B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B-Tree,首先定义一条记录为一个二元组[key, data] ,key为记录的键值,对应表中的主键值,data 为一行记录中除主键外的数据。对于不同的记录,key值互不相同。
一棵m阶的B-Tree有如下特性:
B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个 3 阶的 B-Tree:
索引
每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例,关键字为17和35,P1指针指向的子树的数据范围为小于17,P2指针指向的子树的数据范围为17~35,P3指针指向的子树的数据范围为大于35。
模拟查找关键字29的过程:
分析上面过程,发现需要3次磁盘I/O操作,和3次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。
B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB 存储引擎就是用 B+Tree 实现其索引结构。
从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。
B+Tree相对于B-Tree有几点不同:
将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:
通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。
可能上面例子中只有22条数据记录,看不出B+Tree的优点,下面做一个推算:
InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为103)。也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿 条记录。
实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
B+Tree性质
MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域,存放的并不是实际的数据记录,而是数据记录的地址。索引文件与数据文件分离,这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大,只是主键索引不能有重复的关键字。
在MyISAM中,索引(含叶子节点)存放在单独的.myi文件中,叶子节点存放的是数据的物理地址偏移量(通过偏移量访问就是随机访问,速度很快)。
主索引是指主键索引,键值不可能重复;辅助索引则是普通索引,键值可能重复。
通过索引查找数据的流程:先从索引文件中查找到索引节点,从中拿到数据的文件指针,再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。
InnoDB引擎索引结构的叶子节点的数据域,存放的就是实际的数据记录(对于主索引,此处会存放表中所有的数据记录;对于辅助索引此处会引用主键,检索的时候通过主键到主键索引中找到对应数据行),或者说,InnoDB的数据文件本身就是主键索引文件,这样的索引被称为“聚簇索引”,一个表只能有一个聚簇索引。
我们知道InnoDB索引是聚集索引,它的索引和数据是存入同一个.idb文件中的,因此它的索引结构是在同一个树节点中同时存放索引和数据,如下图中最底层的叶子节点有三行数据,对应于数据表中的id、stu_id、name数据项。
在Innodb中,索引分叶子节点和非叶子节点,非叶子节点就像新华字典的目录,单独存放在索引段中,叶子节点则是顺序排列的,在数据段中。Innodb的数据文件可以按照表来切分(只需要开启innodb_file_per_table)
,切分后存放在xxx.ibd
中,默认不切分,存放在xxx.ibdata
中。
这次我们以示例中学生表中的name列建立辅助索引,它的索引结构跟主键索引的结构有很大差别,在最底层的叶子结点有两行数据,第一行的字符串是辅助索引,按照ASCII码进行排序,第二行的整数是主键的值。
这就意味着,对name列进行条件搜索,需要两个步骤:
① 在辅助索引上检索name,到达其叶子节点获取对应的主键;
② 使用主键在主索引上再进行对应的检索操作
这也就是所谓的“回表查询”
InnoDB 索引结构需要注意的点
正如我们上面介绍 InnoDB 存储结构,索引与数据是共同存储的,不管是主键索引还是辅助索引,在查找时都是通过先查找到索引节点才能拿到相对应的数据,如果我们在设计表结构时没有显式指定索引列的话,MySQL 会从表中选择数据不重复的列建立索引,如果没有符合的列,则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,并且这个字段长度为6个字节,类型为整型。
?
那为什么推荐使用整型自增主键而不是选择UUID?
?
为什么非主键索引结构叶子节点存储的是主键值?
保证数据一致性和节省存储空间,可以这么理解:商城系统订单表会存储一个用户ID作为关联外键,而不推荐存储完整的用户信息,因为当我们用户表中的信息(真实名称、手机号、收货地址···)修改后,不需要再次维护订单表的用户数据,同时也节省了存储空间。
主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储。
检索算法:在检索查询时,就再次对待查关键字再次执行相同的Hash算法,得到Hash值,到对应Hash表对应位置取出数据即可,如果发生Hash碰撞,则需要在取值时进行筛选。目前使用Hash索引的数据库并不多,主要有Memory等。
MySQL目前有Memory引擎和NDB引擎支持Hash索引。
空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型
?
为什么Mysql索引要用B+树不是B树?
用B+树不用B树考虑的是IO对性能的影响,B树的每个节点都存储数据,而B+树只有叶子节点才存储数据,所以查找相同数据量的情况下,B树的高度更高,IO更频繁。数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。其中在MySQL底层对B+树进行进一步优化:在叶子节点中是双向链表,且在链表的头结点和尾节点也是循环指向的。