数据库技术 - MySQL

前言

共29题。

内连接和外连接的区别？

内连接和外连接都是数据库进行多表联查时使用的连接方式。

1. 内连接指的是使用左表中的每一条数据分别去连接右表中的每一条数据，仅仅显示出匹配成功的那部分；

2. 外连接又分为左外连接和右外连接：

   • 左外连接: 首先要显示出左表的全部，然后使用连接条件匹配右表，能匹配中的就显示，匹配不中的显示为null；
   • 右外连接: 首先要显示出右表的全部，然后使用连接条件匹配左表，能匹配中的就显示，匹配不中的显示为null。

union与union all的区别？

二者都是MySQL中用于合并多条select语句结果的关键字，将前后两条select语句的结果组合到一个结果集合中。区别在于UNION ALL会返回所有结果，UNION会去掉重复的记录。

char和varchar的区别？

• char是定长的，数据整齐，用不到的会用隐藏空格填充，浪费空间，查找效率高，可以查身份证号、手机号；
• varchar是不定长的，节省空间，查找效率低。

事务的四大特性？

事务的四大特性指的是：原子性、一致性、隔离性、持久性。

1. 原子性：事务是最小的执行单位，不允许分割，同一个事务中的所有命令要么全部执行，要么全部不执行；

2. 一致性：事务执行前后，数据的状态要保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

3. 隔离性：并发访问数据库时，一个事务不被其他事务所干扰，各并发事务是独立执行的；

4. 持久性：一个事务一旦提交，对数据库的改变应该是永久的，即使系统发生故障也不能丢失。

并发事务带来的问题？

1. 脏读：一个事务读取到了另外一个事务没有提交的数据；

2. 不可重复读：一个事务读取到了另外一个事务修改的数据；

3. 幻读（虚读）：一个事务读取到了另外一个事务新增的数据。

事务隔离级别？

事务隔离级别是用来解决并发事务问题的方案，有以下四种：

1. 读未提交：允许读取并发事务尚未提交的数据，可能会导致脏读、幻读或不可重复读；

2. 读已提交：允许读取并发事务已提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生；

3. 可重复读：同一个事务中，对同一字段的多次读取结果都是一致的（即使数据已经在另一个事务中发生改变）除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生；

4. 可串行化：所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，该级别可以防止脏读、不可重复读以及幻读。

这些事务隔离级别效率依次降低，安全性依次升高，如果不单独设置，MySQL默认的隔离级别是可重复读，而PostgreSQL默认的隔离级别是读已提交。

数据库三大范式？

数据库三大范式是指导设计数据库的原则。

• 第一范式：表中的每一列不能再进行拆分；
• 第二范式：一张表只做一件事；
• 第三范式：数据不能存在传递关系，通过其它字段推出来的字段没必要再存储。但实践中都做反第三范式：通过添加冗余字段，来减少多表联查或计算。

索引的分类？

1. 主键索引：默认是聚簇索引，只有一个（Primary）；

2. 唯一索引：不允许重复（Unique）；

3. 普通索引：给经常用来查询的某个字段设置索引；

4. 组合索引：给经常一起用来查询的某几个字段设置一个索引（同时经常用到）。

索引的创建原则？

在建立索引的时候应该遵循下面这些原则：

1. 主键字段、外键字段应该添加索引；

2. 经常作为查询、排序或分组条件的字段需要建立索引；

3. 经常使用多个条件查询时建议使用组合索引代替多个单列索引。

除此之外，下面这些情况，不应该建立索引：

1. 数据量小的表不建议添加索引；

2. 不要在区分度低的字段建立索引，比如性别字段；

3. 数据变化比较大的表不建议创建索引（例如：表经常添加、修改）。

什么情况下索引会失效？

1. 模糊查询：like "%ehdbfd"数据前加%会使索引失效；

2. 联表查询：连接条件中存在没有索引的会导致索引失效；

3. 数据操作：计算、聚合函数、自动/手动类型转换会导致索引失效；

4. 组合索引：使用组合索引时，没有遵循最左匹配原则（按照最左优先的方式进行索引的匹配）会导致索引失效。

如何知道索引是否失效？

通过执行计划：explain，主要观察type的结果，如果出现all代表没走索引，我们在优化的时候尽量优化到range级别以上。

查询语句的执行流程？

一条查询语句到达MySQL数据库之后：

1. 连接器会负责建立连接、检查权限等操作；

2. 连接成功之后，会查询缓存，如果缓存中有结果会直接返回；如果缓存中没有结果，会将sql交给分析器处理；

3. 分析器检查sql的词法、语法，如果没有问题，再将sql交给优化器处理；

4. 优化器会决定用哪个索引，决定表的连接顺序等，然后将优化之后的sql交给执行器；

5. 执行器根据存储引擎类型，调用存储引擎接口；

6. 存储引擎负责最后数据的读写。

请描述一下sql语句的执行顺序？

如果是一个select语句，它的执行顺序是这样的：

1、查询条件

1. FROM子句：确定数据来源，包括JOIN的表。
2. ON：执行JOIN条件。
3. JOIN：如果有JOIN则根据JOIN类型（内连接、外连接）连接表。
4. WHERE子句：过滤记录。

2、分组条件

5. GROUP BY子句：根据指定的列分组记录。
6. HAVING子句：过滤分组。

3、结果集过滤

7. SELECT子句：选取特定的列。
8. DISTINCT子句：去除重复数据。
9. ORDER BY子句：最后对结果进行排序。
10. LIMIT / OFFSET子句（或者是TOP，或者是FETCH）：最后的结果截取。

索引的数据结构是什么？

在MySQL中索引使用的数据结构是B+Tree，区间查询效率更高更稳定。特点是矮胖：层级较少，每层数据较多，数据存储在叶子节点上，非叶子节点只存储索引。

索引的数据结构B+树是如何存储数据的？

B+树在存储数据时，分为聚簇索引（主键索引）和二级索引。

• 聚簇索引会产生一颗B+树，非叶子节点存储的是聚簇索引，叶子节点包含了完整的数据记录
• 二级索引也会产生一颗B+树，非叶子节点存储二级索引，叶子节点存储的是主键值

实际面试问题

Q：现在有一个场景，有一个表，字段A是主键，B建立了普通索引，C没有索引。现有两个查询语句，语句1是：select A from table where B=多少，语句2是：select A,C from table where B=多少。这两个查询语句的效率是一样的吗？

A：两个查询语句的效率是不一样的，语句1效率高于语句2。在执行语句1时，select A先去查找B所在的二级索引B+树，这棵树的叶子结点有主键A的值；而在执行语句2时，C字段的值不在二级索引B+树上，此时就得去聚簇索引B+树寻找。因此，语句1只查找了一颗B+树，而语句2查找了两颗B+树，所以语句1效率高于语句2。

数据库中的锁有哪些？

数据库中的锁有悲观锁和乐观锁：

1. 悲观锁：认为并发事务之间可能会经常发生冲突。为了防止其他事务对其进行修改，会强制使用锁来保证数据安全，它会阻塞其他事务的访问；

2. 乐观锁：认为多个事务在并发执行时不会经常发生冲突。所以一般就是先执行了再说，最后再看是提交还是回滚。

MySQL的日志类型？

常见的日志有：

1. binlog归档日志：用于做数据同步，记录主数据库的增删改查操作，一般用于主从复制；

2. redolog重做日志：存储未提交的数据，用于确保事务的持久性；

3. undo log回滚日志 ：放可以回滚的日志。

MySQL主从复制的流程？

其实就是主节点向从节点同步数据，主要是依靠MySQL的binLog实现的，大体流程分为三步：

1. 主库事务提交时，会把数据变更记录在BinLog日志中；

2. 读取主库的Binlog日志 ，写入到从库的RelayLog中继日志中；

3. 重做RelayLog中继日志中的事件，将改变反映成它自己的数据。

谈谈你对sql的优化的经验？（高频必问） ****

总流程

sql优化大体分为三步：

1. 查找问题sql，开启mysql的慢查询日志，它会将执行时间较长的sql记录下来；

2. 找到sql之后，分析出现问题的原因，主要有字段类型选择错误、sql语句效率低、索引失效等等；

3. 根据问题不同，再去定具体的解决方案。

有以下几个常见问题：

存储引擎

确定选择的引擎是否合适：

• myisam：查询为主，不支持事务和外键

• Innodb：支持事务和外键，或者包括很多的更新和删除的数据表

表设计

表设计是否合理：

• 单表不要有太多字段，建议在20以内，超过20个分到另一张表中

• 合理的加入冗余字段可以提高查询速度

数据类型

确定字段的数据类型是否合适：

1）数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型

2）设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低，varchar的长度只分配真正需要的空间

3）尽量使用TIMESTAMP而非DATETIME

4）尽量设计所有字段都得有默认值，尽量避免null

sql书写规范

确定sql的书写是否有问题：

1. select语句：SELECT语句务必指明字段名称，避免直接使用select *

2. in语句：SQL语句中IN包含的值不应过多

3. 多表联查连接方式：可以用内连接，就尽量不要使用外连接

4. 子查询：使用连接查询来代替子查询

5. 临时表：使用联合(UNION)来代替手动创建的临时表

索引

表数据比较多的时候是否添加了合适的索引，表的主键、外键必须有索引，经常用于查询、排序、分组的字段，应当建立索引，加上索引之后，还应该使用执行计划Explain来确认索引是否生效

服务器问题

如果上面的几项都没有问题，那可能就是因为服务器性能或者数据量过大导致的查询慢，此时可以考虑读写分离，也就是我们搭建一个MySQL的主从集群，让1个主节点负责写入数据，多个从节点负责查询数据，分摊查询压力

视图是一个“虚表”，视图的构造基于什么？

基于基本表或视图，作用：隐藏表的一些列，封装复杂的SQL。

索引和主键有什么区别？

主键是索引的一种，是唯一索引的特定类型，每张表只能有一个主键索引，可通过PRIMARY KEY约束创建。

Mysql匹配多个索引的时候是如何选择索引的？

SQL优化器【CBO（Cost-based Optimizer，基于成本的优化器）】决定了具体某一索引的选择，也就是常说的执行计划。而优化器的选择是基于成本（cost），哪个索引的成本更低，优先使用哪个索引。

优化器认为一条SQL需要创建基于磁盘的临时表，这时的成本是最大的，索引键值的比较、记录之间的比较，其实开销是非常低的，但如果要比较的记录数非常多，则成本会变得非常大。

Cost = Server Cost + Engine Cost = CPU Cost + IO Cost

1. CPU Cost：表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在Server层完成；
2. IO Cost：表示引擎层IO的开销，MySQL 8.0可以通过区分一张表的数据是否在内存中，分别计算读取内存IO开销以及读取磁盘IO的开销。

MySQL如何防止sql注入？

SQL注入指的是黑客使用某种方式欺骗服务器执行恶意的sql命令。

解决SQL注入最好的方式就是绑定变量使用预编译语句，例如MyBatis中的预编译处理方式#，因为变量用?表示，外力无法改变SQL语句的结构。

一张表最多可以建多少索引？

一般建议单张表索引不超过5个索引。

• 第一索引也会占用存储空间；
• 第二如果索引字段和表字段一样多那和全表扫描也没啥区别了还建索引干啥；
• 第三索引可能会提高查询效率但是同样的可能会降低增删改的效率。

MySQL的覆盖索引是什么？

覆盖索引是指一个查询语句在执行时，所需的数据可以完全通过索引来获取，而无需访问实际的数据行。覆盖索引是数据库优化中的一种重要技术。

覆盖索引的适用场景：

1. 查询列较少：当查询的列较少，且这些列都包含在索引中时，可以使用覆盖索引来提高查询性能。
2. 高并发环境：在高并发环境下，减少对数据表的访问可以减少锁的竞争，从而提高并发性能。覆盖索引通过直接从索引中获取所需数据，减少了对数据表的访问次数，因此适用于高并发环境。

MySQL做过分库分表吗？

分库思路

1. 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
2. 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

分表思路

1. 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
2. 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

MySQL分库分表可能遇到的问题？

• 事务问题：使用分布式事务；
• 跨节点join、order by、group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并；
• ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制，最简单可以使用UUID。

MySQL分库分表的三种应用场景？

1. 只分库不分表：当数据库的读写访问量过高，还有可能会出现数据库连接不够用的情况。这个时候我们就需要考虑分库，通过增加数据库实例的方式来获得更多的数据库连接，从而提升系统的并发性能。
2. 只分表不分库：当单表存储的数据量非常大的情况下，并且并发量也不高，数据库的连接也还够用。但是数据写入和查询的性能出现了瓶颈，这个时候就需要考虑分表了。将数据拆分到多张表中来减少单表存储的数据量，从而提升读写的效率。
3. 既分库又分表：结合前面的两种情况，如果同时满足前面的两个条件，也就是数据连接也不够用，并且单表的数据量也很大，从而导致数据库读写速度变慢的情况，这个时候就要考虑既分库又分表。