php主动补空技巧_MySQL 面试题超强总结

文章目录 [+]

非关系型数据库和关系型数据库差异，上风比较

非关系型数据库（觉得翻译不是很准确）称为 NoSQL，也便是 Not Only SQL，不仅仅是 SQL。
非关系型数据库不须要写一些繁芜的 SQL 语句，其内部存储办法因此 key-value 的形式存在可以把它想象成电话本的形式，每个人名（key）对应电话（value）。
常见的非关系型数据库紧张有 Hbase、Redis、MongoDB 等。
非关系型数据库不须要经由 SQL 的重重解析，以是性能很高；非关系型数据库的可扩展性比较强，数据之间没有耦合性，遇见须要新加字段的需求，就直接增加一个 key-value 键值对即可。

php主动补空技巧_MySQL 面试题超强总结

关系型数据库以表格的形式存在，以行和列的形式存取数据，关系型数据库这一系列的行和列被称为表，无数张表组成了数据库，常见的关系型数据库有 Oracle、DB2、Microsoft SQL Server、MySQL等。
关系型数据库能够支持繁芜的 SQL 查询，能够表示出数据之间、表之间的关联关系；关系型数据库也支持事务，便于提交或者回滚。

（图片来自网络侵删）

它们之间的劣势都是基于对方的上风来知足的。

MySQL 事务四大特性

一说到 MySQL 事务，你肯定能想起来四大特性：原子性、同等性、隔离性、持久性，下面再对这事务的四大特性做一个描述

原子性(Atomicity): 原子性指的便是 MySQL 中的包含事务的操作要么全部成功、要么全部失落败回滚，因此事务的操作如果成功就必须要全部运用到数据库，如果操作失落败则不能对数据库有任何影响。

这里涉及到一个观点，什么是 MySQL 中的事务？

事务是一组操作，组成这组操作的各个单元，要不全都成功要不全都失落败，这个特性便是事务。

在 MySQL 中，事务是在引擎层实现的，只有利用 innodb 引擎的数据库或表才支持事务。

同等性(Consistency)：同等性指的是一个事务在实行前后其状态同等。
比如 A 和 B 加起来的钱一共是 1000 元，那么不管 A 和 B 之间如何转账，转多少次，事务结束后两个用户的钱加起来还得是 1000，这便是事务的同等性。
持久性(Durability): 持久性指的是一旦事务提交，那么发生的改变便是永久性的，纵然数据库碰着分外情形比如故障的时候也不会产生滋扰。
隔离性(Isolation)：隔离性须要重点说一下，当多个事务同时进行时，就有可能涌现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read) 的情形，为理解决这些并发问题，提出了隔离性的观点。

脏读：事务 A 读取了事务 B 更新后的数据，但是事务 B 没有提交，然后事务 B 实行回滚操作，那么事务 A 读到的数据便是脏数据

不可重复读：事务 A 进行多次读取操作，事务 B 在事务 A 多次读取的过程中实行更新操作并提交，提交后事务 A 读到的数据不一致。

幻读：事务 A 将数据库中所有学生的成绩由 A -> B，此时势务 B 手动插入了一条成绩为 A 的记录，在事务 A 变动完毕后，创造还有一条记录没有修正，那么这种情形就叫做涌现了幻读。

SQL的隔离级别有四种，它们分别是读未提交(read uncommitted)、读已提交(read committed)、可重复读(repetable read) 和串行化(serializable)。
下面分别来阐明一下。

读未提交：读未提交指的是一个事务在提交之前，它所做的修正就能够被其他事务所看到。

读已提交：读已提交指的是一个事务在提交之后，它所做的变更才能够让其他事务看到。

可重复读：可重复读指的是一个事务在实行的过程中，看到的数据是和启动时看到的数据是同等的。
未提交的变更对其他事务不可见。

串行化：顾名思义是对付同一行记录，写会加写锁，读会加读锁。
当涌现读写锁冲突的时候，后访问的事务必须等前一个事务实行完成，才能连续实行。

这四个隔离级别可以办理脏读、不可重复读、幻象读这三类问题。
总结如下

个中隔离级别由低到高是：读未提交 < 读已提交 < 可重复读 < 串行化

隔离级别越高，越能够担保数据的完全性和同等性，但是对并发的性能影响越大。
大多数数据库的默认级别是读已提交(Read committed)，比如 Sql Server、Oracle ，但是 MySQL 的默认隔离级别是可重复读(repeatable-read)。

MySQL 常见存储引擎的差异

MySQL 常见的存储引擎，可以利用

SHOW ENGINES

命令，来列出所有的存储引擎

可以看到，InnoDB 是 MySQL 默认支持的存储引擎，支持事务、行级锁定和外键。

MyISAM 存储引擎的特点

在 5.1 版本之前，MyISAM 是 MySQL 的默认存储引擎，MyISAM 并发性比较差，利用的场景比较少，紧张特点是

不支持事务操作，ACID 的特性也就不存在了，这一设计是为了性能和效率考虑的。
不支持外键操作，如果强行增加外键，MySQL 不会报错，只不过外键不起浸染。
MyISAM 默认的锁粒度是表级锁，以是并发性能比较差，加锁比较快，锁冲突比较少，不太随意马虎发生去世锁的情形。
MyISAM 会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是 .frm(存储表定义)、.MYD(MYData,存储数据)、MYI(MyIndex,存储索引)。
这里须要特殊把稳的是 MyISAM 只缓存索引文件，并不缓存数据文件。
MyISAM 支持的索引类型有全局索引(Full-Text)、B-Tree 索引、R-Tree 索引Full-Text 索引：它的涌现是为理解决针对文本的模糊查询效率较低的问题。
B-Tree 索引：所有的索引节点都按照平衡树的数据构造来存储，所有的索引数据节点都在叶节点R-Tree索引：它的存储办法和 B-Tree 索引有一些差异，紧张设计用于存储空间和多维数据的字段做索引,目前的 MySQL 版本仅支持 geometry 类型的字段作索引，相对付 BTREE，RTREE 的上风在于范围查找。
数据库所在主机如果宕机，MyISAM 的数据文件随意马虎破坏，而且难以规复。
增编削查性能方面：SELECT 性能较高，适用于查询较多的情形InnoDB 存储引擎的特点

自从 MySQL 5.1 之后，默认的存储引擎变成了 InnoDB 存储引擎，相对付 MyISAM，InnoDB 存储引擎有了较大的改变，它的紧张特点是

支持事务操作，具有事务 ACID 隔离特性，默认的隔离级别是可重复读(repetable-read)、通过MVCC（并发版本掌握）来实现的。
能够办理脏读和不可重复读的问题。
InnoDB 支持外键操作。
InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生去世锁的情形。
和 MyISAM 一样的是，InnoDB 存储引擎也有 .frm文件存储表构造定义，但是不同的是，InnoDB 的表数据与索引数据是存储在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。
InnoDB 有安全的日志文件，这个日志文件用于规复因数据库崩溃或其他情形导致的数据丢失问题，担保数据的同等性。
InnoDB 和 MyISAM 支持的索引类型相同，但详细实现由于文件构造的不同有很大差异。
增编削查性能方面，果实行大量的增编削操作，推举利用 InnoDB 存储引擎，它在删除操作时是对行删除，不会重修表。
MyISAM 和 InnoDB 存储引擎的比拟锁粒度方面：由于锁粒度不同，InnoDB 比 MyISAM 支持更高的并发；InnoDB 的锁粒度为行锁、MyISAM 的锁粒度为表锁、行锁须要对每一行进行加锁，以是锁的开销更大，但是能办理脏读和不可重复读的问题，相对来说也更随意马虎发生去世锁可规复性上：由于 InnoDB 是有事务日志的，以是在产生由于数据库崩溃等条件后，可以根据日志文件进行规复。
而 MyISAM 则没有事务日志。
查询性能上：MyISAM 要优于 InnoDB，由于 InnoDB 在查询过程中，是须要掩护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而 MyISAM 可以直接定位到数据所在的内存地址，可以直接找到数据。
表构造文件上： MyISAM 的表构造文件包括：.frm(表构造定义),.MYI(索引),.MYD(数据)；而 InnoDB 的表数据文件为:.ibd和.frm(表构造定义)；MySQL 根本架构

这道题该当从 MySQL 架构来理解，我们可以把 MySQL 拆解成几个零件，如下图所示

大致上来说，MySQL 可以分为 Server层和存储引擎层。

Server 层包括连接器、查询缓存、剖析器、优化器、实行器，包括大多数 MySQL 中的核心功能，所有跨存储引擎的功能也在这一层实现，包括存储过程、触发器、视图等。

存储引擎层包括 MySQL 常见的存储引擎，包括 MyISAM、InnoDB 和 Memory 等，最常用的是 InnoDB，也是现在 MySQL 的默认存储引擎。
存储引擎也可以在创建表的时候手动指定，比如下面

CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

然后我们就可以磋商 MySQL 的实行过程了

连接器

首先须要在 MySQL 客户端上岸才能利用，以是须要一个连接器来连接用户和 MySQL 数据库，我们一样平常是利用

mysql -u 用户名 -p 密码

来进行 MySQL 上岸，和做事端建立连接。
在完成 TCP 握手后，连接器会根据你输入的用户名和密码验证你的登录身份。
如果用户名或者密码缺点，MySQL 就会提示 Access denied for user，来结束实行。
如果登录成功后，MySQL 会根据权限表中的记录来剖断你的权限。

查询缓存

连接完成后，你就可以实行 SQL 语句了，这行逻辑就会来到第二步：查询缓存。

MySQL 在得到一个实行要求后，会首先去查询缓存中查找，是否实行过这条 SQL 语句，之前实行过的语句以及结果会以 key-value 对的形式，被直接放在内存中。
key 是查询语句，value 是查询的结果。
如果通过 key 能够查找到这条 SQL 语句，就直接返回 SQL 的实行结果。

如果语句不在查询缓存中，就会连续后面的实行阶段。
实行完成后，实行结果就会被放入查询缓存中。
可以看到，如果查询命中缓存，MySQL 不须要实行后面的繁芜操作，就可以直接返回结果，效率会很高。

但是查询缓存不建议利用

为什么呢？由于只要在 MySQL 中对某一张表实行了更新操作，那么所有的查询缓存就会失落效，对付更新频繁的数据库来说，查询缓存的命中率很低。

剖析器

如果没有命中查询，就开始实行真正的 SQL 语句。

首先，MySQL 会根据你写的 SQL 语句进行解析，剖析器会先做词法剖析，你写的 SQL 便是由多个字符串和空格组成的一条 SQL 语句，MySQL 须要识别出里面的字符串是什么，代表什么。
然后进行语法剖析，根据词法剖析的结果，语法剖析器会根据语法规则，判断你输入的这个 SQL 语句是否知足 MySQL 语法。
如果 SQL 语句禁绝确，就会提示 You have an error in your SQL syntax优化器

经由剖析器的词法剖析和语法剖析后，你这条 SQL 就合法了，MySQL 就知道你要做什么了。
但是在实行前，还须要进行优化器的处理，优化器会判断你利用了哪种索引，利用了何种连接，优化器的浸染便是确定效率最高的实行方案。

实行器

MySQL 通过剖析器知道了你的 SQL 语句是否合法，你想要做什么操作，通过优化器知道了该怎么做效率最高，然后就进入了实行阶段，开始实行这条 SQL 语句

在实行阶段，MySQL 首先会判断你有没有实行这条语句的权限，没有权限的话，就会返回没有权限的缺点。
如果有权限，就打开表连续实行。
打开表的时候，实行器就会根据表的引擎定义，去利用这个引擎供应的接口。
对付有索引的表，实行的逻辑也差不多。

至此，MySQL 对付一条语句的实行过程也就完成了。

SQL 的实行顺序

我们在编写一个查询语句的时候

SELECT DISTINCT < select_list >FROM < left_table > < join_type >JOIN < right_table > ON < join_condition >WHERE < where_condition >GROUP BY < group_by_list >HAVING < having_condition >ORDER BY < order_by_condition >LIMIT < limit_number >

它的实行顺序你知道吗？这道题就给你一个回答。

FROM 连接

首先，对 SELECT 语句实行查询时，对FROM 关键字两边的表实行连接，会形成笛卡尔积，这时候会产生一个虚表VT1(virtual table)

首先先来阐明一下什么是笛卡尔积

现在我们有两个凑集 A = {0,1} , B = {2,3,4}

那么，凑集 A B 得到的结果便是

A B = {(0,2)、(1,2)、(0,3)、(1,3)、(0,4)、(1,4)};

B A = {(2,0)、{2,1}、{3,0}、{3,1}、{4,0}、(4,1)};

上面 A B 和 B A 的结果就可以称为两个凑集相乘的笛卡尔积

我们可以得出结论，A 凑集和 B 凑集相乘，包含了凑集 A 中的元素和凑集 B 中元素之和，也便是 A 元素的个数 B 元素的个数

再来阐明一下什么是虚表

在 MySQL 中，有三种类型的表

一种是永久表，永久表便是创建往后用来长期保存数据的表

一种是临时表，临时表也有两类，一种是和永久表一样，只保存临时数据，但是能够长久存在的；还有一种是临时创建的，SQL 语句实行完造诣会删除。

一种是虚表，虚表实在便是视图，数据可能会来自多张表的实行结果。

ON 过滤

然后对 FROM 连接的结果进行 ON 筛选，创建 VT2，把符合记录的条件存在 VT2 中。

JOIN 连接

第三步，如果是 OUTER JOIN(left join、right join) ，那么这一步就将添加外部行，如果是 left join 就把 ON 过滤条件的左表添加进来，如果是 right join ，就把右表添加进来，从而天生新的虚拟表 VT3。

WHERE 过滤

第四步，是实行 WHERE 过滤器，对上一步生产的虚拟表引用 WHERE 筛选，天生虚拟表 VT4。

WHERE 和 ON 的差异

如果有外部列，ON 针对过滤的是关联表，主表(保留表)会返回所有的列;如果没有添加外部列，两者的效果是一样的;

运用

对主表的过滤该当利用 WHERE;对付关联表，先条件查询后连接则用 ON，先连接后条件查询则用 WHERE;GROUP BY

根据 group by 字句中的列，会对 VT4 中的记录进行分组操作，产生虚拟机表 VT5。
果运用了group by，那么后面的所有步骤都只能得到的 VT5 的列或者是聚合函数（count、sum、avg等）。

HAVING

紧随着 GROUP BY 字句后面的是 HAVING，利用 HAVING 过滤，会把符合条件的放在 VT6

SELECT

第七步才会实行 SELECT 语句，将 VT6 中的结果按照 SELECT 进行刷选，天生 VT7

DISTINCT

在第八步中，会对 TV7 天生的记录进行去重操作，天生 VT8。
事实上如果运用了 group by 子句那么 distinct 是多余的，缘故原由同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么以是的记录都将是不相同的。

ORDER BY

运用 order by 子句。
按照 order_by_condition 排序 VT8，此时返回的一个游标，而不是虚拟表。
sql 是基于凑集的理论的，凑集不会预先对他的行排序，它只是成员的逻辑凑集，成员的顺序是无关紧要的。

SQL 语句实行的过程如下

什么是临时表，何时删除临时表

什么是临时表？MySQL 在实行 SQL 语句的过程中，常日会临时创建一些存储中间结果集的表，临时表只对当前连接可见，在连接关闭时，临时表会被删除并开释所有表空间。

临时表分为两种：一种是内存临时表，一种是磁盘临时表，什么差异呢？内存临时表利用的是 MEMORY 存储引擎，而临时表采取的是 MyISAM 存储引擎。

MEMORY 存储引擎：memory 是 MySQL 中一类分外的存储引擎，它利用存储在内容中的内容来创建表，而且数据全部放在内存中。
每个基于 MEMORY 存储引擎的表示实对应一个磁盘文件。
该文件的文件名与表名相同，类型为 frm 类型。
而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高全体表的效率。
MEMORY 用到的很少，由于它是把数据存到内存中，如果内存涌现非常就会影响数据。
如果重启或者关机，所有数据都会消逝。
因此，基于 MEMORY 的表的生命周期很短，一样平常是一次性的。

MySQL 会不才面这几种情形产生临时表

利用 UNION 查询：UNION 有两种，一种是UNION ，一种是 UNION ALL ，它们都用于联合查询；差异是利用 UNION 会去掉两个表中的重复数据，相称于对结果集做了一下去重(distinct)。
利用 UNION ALL，则不会排重，返回所有的行。
利用 UNION 查询会产生临时表。
利用 TEMPTABLE 算法或者是 UNION 查询中的视图。
TEMPTABLE 算法是一种创建临时表的算法，它是将结果放置到临时表中，意味这要 MySQL 要先创建好一个临时表，然后将结果放到临时表中去，然后再利用这个临时表进行相应的查询。
ORDER BY 和 GROUP BY 的子句不一样时也会产生临时表。
DISTINCT 查询并且加上 ORDER BY 时；SQL中用到 SQL_SMALL_RESULT 选项时；如果查询结果比较小的时候，可以加上 SQL_SMALL_RESULT 来优化，产生临时表FROM 中的子查询；EXPLAIN 查看实行操持结果的 Extra 列中，如果利用 Using Temporary 就表示会用到临时表。
MySQL 常见索引类型

索引是存储在一张表中特定列上的数据构造，索引是在列上创建的。
并且，索引是一种数据构造。

在 MySQL 中，紧张有下面这几种索引

全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引，它的涌现是为理解决针对文本的模糊查询效率较低的问题。
哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据构造，很适宜作为索引。
HASH 索引具有一次定位的好处，不须要像树那样逐个节点查找，但是这种查找适宜运用于查找单个键的情形，对付范围查找，HASH 索引的性能就会很低。
B-Tree 索引：B 便是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的便是 B+ Tree，它被 MySQL 广泛利用。
R-Tree 索引：R-Tree 在 MySQL 很少利用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对付 B-Tree 来说，R-Tree 的上风在于范围查找。
varchar 和 char 的差异和利用场景

MySQL 中没有 nvarchar 数据类型，以是直接比较的是 varchar 和 char 的差异

char ：表示的是定长的字符串，当你输入小于指定的数目，比如你指定的数目是 char(6)，当你输入小于 6 个字符的时候，char 会在你末了一个字符后面补空值。
当你输入超过指定许可最大长度后，MySQL 会报错

varchar： varchar 指的是长度为 n 个字节的可变长度，并且是非Unicode的字符数据。
n 的值是介于 1 - 8000 之间的数值。
存储大小为实际大小。

Unicode 是一种字符编码方案，它为每种措辞中的每个字符都设定了统一唯一的二进制编码，以实现跨措辞、跨平台进行文本转换、处理的哀求

利用 char 存储定长的数据非常方便、char 检索效率高，无论你存储的数据是否到了 10 个字节，都要去占用 10 字节的空间

利用 varchar 可以存储变长的数据，但存储效率没有 char 高。

什么是内连接、外连接、交叉连接、笛卡尔积

连接的办法紧张有三种：外连接、内链接、交叉连接

外连接(OUTER JOIN)：外连接分为三种，分别是左外连接(LEFT OUTER JOIN 或 LEFT JOIN) 、右外连接(RIGHT OUTER JOIN 或 RIGHT JOIN) 、全外连接(FULL OUTER JOIN 或 FULL JOIN)左外连接：又称为左连接，这种连接办法会显示左表不符合条件的数据行，右边不符合条件的数据行直接显示 NULL

右外连接：也被称为右连接，他与左连接相对，这种连接办法会显示右表不符合条件的数据行，左表不符合条件的数据行直接显示 NULL

MySQL 暂不支持全外连接

内连接(INNER JOIN)：结合两个表中相同的字段，返回关联字段符合的记录。

笛卡尔积(Cartesian product)：我在上面提到了笛卡尔积，为了方便，下面再列出来一下。

现在我们有两个凑集 A = {0,1} , B = {2,3,4}

那么，凑集 A B 得到的结果便是

A B = {(0,2)、(1,2)、(0,3)、(1,3)、(0,4)、(1,4)};

B A = {(2,0)、{2,1}、{3,0}、{3,1}、{4,0}、(4,1)};

上面 A B 和 B A 的结果就可以称为两个凑集相乘的笛卡尔积

我们可以得出结论，A 凑集和 B 凑集相乘，包含了凑集 A 中的元素和凑集 B 中元素之和，也便是 A 元素的个数 B 元素的个数

交叉连接的原文是Cross join ，便是笛卡尔积在 SQL 中的实现，SQL中利用关键字CROSS JOIN来表示交叉连接，在交叉连接中，随便增加一个表的字段，都会对结果造成很大的影响。
SELECT FROM t_Class a CROSS JOIN t_Student b WHERE a.classid=b.classid 或者不用 CROSS JOIN，直接用 FROM 也能表示交叉连接的效果SELECT FROM t_Class a ,t_Student b WHERE a.classid=b.classid 如果表中字段比较多，不适宜用交叉连接，交叉连接的效率比较差。
全连接：全连接也便是 full join，MySQL 中不支持全连接，但是可以利用其他连接查询来仿照全连接，可以利用 UNION和 UNION ALL 进行仿照。
例如(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB ) 或 (select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB )；利用 UNION 和 UNION ALL 的把稳事变通过 union 连接的 SQL 分别单独取出的列数必须相同利用 union 时，多个相等的行将会被合并，由于合并比较耗时，一样平常不直策应用 union 进行合并，而是常日采取 union all 进行合并谈谈 SQL 优化的履历查询语句无论是利用哪种判断条件即是、小于、大于， WHERE 左侧的条件查询字段不要利用函数或者表达式利用 EXPLAIN 命令优化你的 SELECT 查询，对付繁芜、效率低的 sql 语句，我们常日是利用 explain sql 来剖析这条 sql 语句，这样方便我们剖析，进行优化。
当你的 SELECT 查询语句只须要利用一条记录时，要利用 LIMIT 1不要直策应用 SELECT ，而该当利用详细须要查询的表字段，由于利用 EXPLAIN 进行剖析时，SELECT 利用的是全表扫描，也便是 type = all。
为每一张表设置一个 ID 属性避免在 WHERE 字句中对字段进行 NULL 判断避免在 WHERE 中利用 != 或 <> 操作符利用 BETWEEN AND 替代 IN为搜索字段创建索引选择精确的存储引擎，InnoDB 、MyISAM 、MEMORY 等利用 LIKE %abc% 不会走索引，而利用 LIKE abc% 会走索引对付列举类型的字段(即有固定罗列值的字段)，建议利用ENUM而不是VARCHAR，如性别、星期、类型、种别等拆分大的 DELETE 或 INSERT 语句选择得当的字段类型，选择标准是尽可能小、尽可能定长、尽可能利用整数。
字段设计尽可能利用 NOT NULL进行水平切割或者垂直分割

水平分割：通过建立构造相同的几张表分别存储数据

垂直分割：将常常一起利用的字段放在一个单独的表中，分割后的表记录之间是逐一对应关系。

文章参考：

https://www.cnblogs.com/sharpest/p/10390035.html

https://blog.csdn.net/yl2isoft/article/details/17205413

https://www.cnblogs.com/jinianjun/archive/2011/11/08/2240525.html

https://www.cnblogs.com/huihuixi/p/12155165.html

https://www.php.cn/faq/418056.html

https://blog.csdn.net/w516162189/article/details/78914035

https://baike.baidu.com/item/聚拢索引/11041381?fr=aladdin

https://blog.csdn.net/riemann_/article/details/90324846

https://blog.csdn.net/qq_39101581/article/details/82461076