这篇博客详细探讨了MySQL面试中常见的问题，包括数据库索引的原理、inner join和left join的区别、数据库事务的理解、数据库的三大范式、以及SQL优化方法等。文章深入浅出地解释了索引的优缺点、SQL执行顺序和语义顺序，帮助读者理解数据库设计和优化的关键点。
1、数据库索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。
- 优点：
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 - 缺点：
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 - 添加索引原则
在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
定义为text、image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
2、inner join和left join
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录。inner join(等值连接) 只返回两个表中联结字段相等的行
3、数据库事务
事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。事务是DBMS中最基础的单位，事务不可分割。事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID。
1）原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，[删删删]因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
2）一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
3）隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。
不同的隔离级别：
Read Uncommitted（读取未提交[添加中文释义]内容）：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
Read Committed（读取提交内容）：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题。
Repeated Read（可重复读）：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读。
Serialization（可串行化）：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。
4）持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
4、数据库的三大范式
- 第一范式：当关系模式R的所有属性都不能再分解为更基本的数据单位时，称R是满足第一范式，即属性不可分
- 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式
- 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，即非主属性不传递依赖于键码
5、数据库连接泄露的含义
数据库连接泄露指的是如果在某次使用或者某段程序中没有正确地关闭Connection、Statement和ResultSet资源，那么每次执行都会留下一些没有关闭的连接，这些连接失去了引用而不能得到重新使用，因此就造成了数据库连接的泄漏。数据库连接的资源是宝贵而且是有限的，如果在某段使用频率很高的代码中出现这种泄漏，那么数据库连接资源将被耗尽，影响系统的正常运转。
6、数据库中的聚类查询
聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。当索引值唯一时，使用聚集索引查找特定的行也很有效率。
7、数据库事务、主键与外键的区别？
- 数据库的事务：事务即用户定义的一个数据库操作序列，这些操作要么全做要全不做，是一个不可分割的工作单位，它具有四个特性，ACID，原子性，一致性，隔离性，持续性。
- 主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。
- 外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。
8、count(*) 和 count(1)和count(列名)区别
执行效率上：
-
执行效果上：
- count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略为NULL的值。
- count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略为NULL的值。
- count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
- 列名为主键，count(列名)会比count(1)快
- 列名不为主键，count(1)会比count(列名)快
- 如果表多个列并且没有主键，则 count(1 的执行效率优于 count（*）
- 如果有主键，则 select count（主键）的执行效
9、请问什么是脏读？
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，但是还没有来得及提交到数据库中，这时，另一个事务也访问这个数据，然后使用了这个数据
10、请问什么是幻读？
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的数据行。
11、mysql的四种隔离状态
Mysql主要包含四种隔离状态：
12、介绍一下mysql的MVCC机制
MVCC是一种多版本并发控制机制，是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。MVCC是通过保存数据在某个时间点的快照来实现该机制，其在每行记录后面保存两个隐藏的列，分别保存这个行的创建版本号和删除版本号，然后Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。
13、请问SQL优化方法有哪些
- 通过建立索引对查询进行优化
- 对查询进行优化，应尽量避免全表扫描
- 调整数据结构的设计，对于经常访问的数据库表建立索引
- 调整SQL语句， ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。
- 调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。
- 调整硬盘Ｉ／Ｏ，DBA可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间Ｉ／Ｏ负载均衡。
14、请你说一下MySQL引擎和区别
1、MySQL引擎
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。
MySQL存储引擎主要有： MyIsam、InnoDB、Memory、Blackhole、CSV、Performance_Schema、Archive、Federated、Mrg_Myisam。但是最常用的是InnoDB和Mylsam。
2、InnoDB
InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。
Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。 经常更新的表，适合处理多重并发的更新请求。支持事务。可以从灾难中恢复（通过bin-log日志等）。外键约束。只有他支持外键。支持自动增加列属性auto_increment。
索引结构：
InnoDB也是B+Treee索引结构。Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。
3、Mylsam
MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT或UPDATE数据时即写操作需要锁定整个表，效率便会低一些。MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。 不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。 不支持外键的表设计。查询速度很快，如果数据库insert和update的操作比较多的话比较适用。整天对表进行加锁的场景。MyISAM极度强调快速读取操作。当然， MyIsam存在缺点： 就是不能在表损坏后主动恢复数据。
索引结构：
MyISAM索引结构：MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。
4、InnoDB和Mylsam的区别：
1）事务：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持已经外部键等高级数据库功能。
2）性能：MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。
3）行数保存：InnoDB 中不保存表的具体行数，也就是说，执行select count()
fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。
4）索引存储：对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持。
MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
5）服务器数据备份：InnoDB必须导出SQL来备份，LOAD TABLE FROM
MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
6）锁的支持：MyISAM只支持表锁。InnoDB支持表锁、行锁
行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
15、mysql删除语句
1.drop语句。可以用来删除数据库和表。
用drop语句来删除数据库：drop database db;
用drop语句来删除表：drop table tb;
2.delete语句。用来删除表中的字段。
delete from tb where id=1;
如果delete语句中没有加入where就会把表中的所有记录全部删除：
3.用truncate来删除表中的所有字段：
truncate table tb;
16、数据库mysql中CHAR和VCHAR的区别
1、char(n)类型
char类型是定长的类型，即当定义的是char(10)，输入的是"abc"这三个字符时，它们占的空间一样是10个字节，包括7个空字节。当输入的字符长度超过指定的数时，char会截取超出的字符。而且，当存储char值时，MySQL是自动删除输入字符串末尾的空格。 char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要。
2、varchar(n)类型
varchar(n)类型用于存储可变长的，长度为n个字节的可变长度且非Unicode的字符数据。n必须是介于1和8000之间的数值，存储大小为输入数据的字节的实际长度+1/2. 比如varchar(10), 然后输入abc三个字符，那么实际存储大小为3个字节。除此之外，varchar还需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节（是定义的最长长度，不是实际长度），则使用1个字节表示长度，否则使用2个字节来表示。取数据的时候，不需要去掉多余的空格。
17.sql执行顺序和语义顺序
SQL语句执行顺序
结合上图，整理出如下伪SQL查询语句。
从这个顺序中我们可以发现，所有的查询语句都是从FROM开始执行的。在实际执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。 接下来，我们详细的介绍下每个步骤的具体执行过程。
1 FROM执行笛卡尔积
FROM 才是 SQL 语句执行的第一步，并非 SELECT 。对FROM子句中的前两个表执行笛卡尔积(交叉联接），生成虚拟表VT1，获取不同数据源的数据集。 FROM子句执行顺序为从后往前、从右到左，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，即最后的表为驱动表，当FROM 子句中包含多个表的情况下，我们需要选择数据最少的表作为基础表。
2 ON 应用ON过滤器
对虚拟表VT1应用ON筛选器，ON 中的逻辑表达式将应用到虚拟表VT1中的各个行，筛选出满足ON 逻辑表达式的行，生成虚拟表VT2。
3 JOIN 添加外部行
如果指定了OUTER JOIN保留表中未找到匹配的行将作为外部行添加到虚拟表VT2，生成虚拟表VT3。保留表如下：LEFT OUTER JOIN把左表记为保留表RIGHT OUTER JOIN把右表记为保留表FULL OUTER JOIN把左右表都作为保留表
在虚拟表VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表VT3。 如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1~3，直到处理完所有的表为止。
4 WHERE 应用WEHRE过滤器
对虚拟表VT3应用WHERE筛选器。根据指定的条件对数据进行筛选，并把满足的数据插入虚拟表VT4。由于数据还没有分组，因此现在还不能在WHERE过滤器中使用聚合函数对分组统计的过滤。同时，由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的。
5 GROUP BY 分组
按GROUP BY子句中的列/列表将虚拟表VT4中的行唯一的值组合成为一组，生成虚拟表VT5。如果应用了GROUP BY，那么后面的所有步骤都只能得到的虚拟表VT5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。 同时，从这一步开始，后面的语句中都可以使用SELECT中的别名。
6AGG_FUNC计算聚合函数
计算 max 等聚合函数。SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。常用的 Aggregate 函数包涵以下几种：AVG：返回平均值COUNT：返回行数FIRST：返回第一个记录的值LAST：返回最后一个记录的值MAX： 返回最大值MIN：返回最小值SUM： 返回总和
7 WITH 应用ROLLUP或CUBE
对虚拟表VT5应用ROLLUP或CUBE选项，生成虚拟表VT6。 CUBE 和 ROLLUP 区别如下：CUBE 生成的结果数据集显示了所选列中值的所有组合的聚合。ROLLUP 生成的结果数据集显示了所选列中值的某一层次结构的聚合。
8 HAVING 应用HAVING过滤器
对虚拟表VT6应用HAVING筛选器。根据指定的条件对数据进行筛选，并把满足的数据插入虚拟表VT7。 HAVING语句在SQL中的主要作用与WHERE语句作用是相同的，但是HAVING是过滤聚合值，在 SQL 中增加 HAVING 子句原因就是，WHERE 关键字无法与聚合函数一起使用，HAVING子句主要和GROUP BY子句配合使用。
9 SELECT 选出指定列
将虚拟表VT7中的在SELECT中出现的列筛选出来，并对字段进行处理，计算SELECT子句中的表达式，产生虚拟表VT8。
10 DISTINCT 行去重
将重复的行从虚拟表VT8中移除，产生虚拟表VT9。DISTINCT用来删除重复行，只保留唯一的。
11 ORDER BY 排列
将虚拟表VT9中的行按ORDER BY 子句中的列/列表排序，生成游标VC10，注意不是虚拟表。因此使用 ORDER BY 子句查询不能应用于表达式。同时，ORDER BY子句的执行顺序为从左到右排序，是非常消耗资源的。
12 LIMIT/OFFSET 指定返回行
从VC10的开始处选择指定数量行，生成虚拟表VT11，并返回调用者。实例 接下来，我们看一个实例，以下SQL查询语句是否存在问题？
首先，我们先看下如上SQL的执行顺序，如下：首先执行 FROM 子句, 从学生成绩表中组装数据源的数据。执行 WHERE 子句, 筛选学生成绩表中所有学生的数学成绩不为 NULL 的数据 。执行 GROUP BY 子句, 把学生成绩表按 "班级" 字段进行分组。计算 avg 聚合函数, 按找每个班级分组求出数学平均成绩。执行 HAVING 子句, 筛选出班级数学平均成绩大于 75 分的。执行SELECT语句，返回数据，但别着急，还需要执行后面几个步骤。执行 ORDER BY 子句, 把最后的结果按 "数学平均成绩" 进行排序。执行LIMIT ，限制仅返回3条数据。结合ORDER BY 子句，即返回所有班级中数学平均成绩的前三的班级及其数学平均成绩。 思考一下，如果我们将上面语句改成，如下会怎样？
我们发现，若将avg(数学成绩) > 75放到WHERE子句中，此时GROUP BY语句还未执行，因此此时聚合值avg(数学成绩)还是未知的，因此会报错。
下面是来自于程序员周周的详细版本
https://blog.youkuaiyun.com/adminpd/article/details/122910606
扫一扫
241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
