[大数据] Mysql面试题

原子性：在事务中进行的修改，要么全部执行，要么全不执行。如果在事务完成之前系统出现故障，SQLServer会撤销在事务中的修改。
一致性：为了事务在查询和修改时数据不发生冲突。
隔离性：隔离性是一种用于控制数据访问的机制，能够确保事务只能访问处于期望的一致性级别下的数据。SQLServer使用锁对各个事务之间正在修改和查询的数据进行隔离。
持久性：在将数据修改写入到磁盘之前，总是先把这些修改写入到事务日志中。这样子，即使数据还没有写入到磁盘中，也可以认为事务是持久化的。这是如果系统重新启动，SQL Server也会检查数据库日志，进行恢复处理。

隔离级别：读未提交、读已提交、可重复读、串行化

2. 事务并发产生的问题和隔离级别！

事务并发产生的问题:

脏读：一个事务读取另一个事务的未提交数据！真错误！read UNCOMMITTED；
不可重复读：一个事务读取；另一个事务的提交的修改数据！read committed;
虚读（）幻读：一个事务读取了另一个数的提交的插入数据！repeatable read

? ? 2.隔离级别：读未提交、读已提交、可重复读、串行化

隔离级别选择:
数据库隔离级别越高！数据越安全，性能越低！
数据库隔离级别越低！数据越不安全，性能越高
建议：设置为第二个隔离级别 read committed;

3. Spring中事务的传播特性有哪些？

propagation_requierd：如果当前没有事务，就新建一个事务，如果已存在一个事务中，加入到这个事务中，这是最常见的选择。
propagation_supports：支持当前事务，如果没有当前事务，就以非事务方法执行。
propagation_mandatory：使用当前事务，如果没有当前事务，就抛出异常。
propagation_required_new：新建事务，如果当前存在事务，把当前事务挂起。
propagation_not_supported：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
propagation_never：以非事务方式执行操作，如果当前事务存在则抛出异常。
propagation_nested：如果有事务在运行，当前的方法就应该在这个事务的嵌套事务内运? 行，否则就启动一个新的事务，并在它自己的事务内运行。

Spring 默认的事务传播行为是 PROPAGATION_REQUIRED

4. MySQL的内部有哪几部分组成？

连接器

MYSQL拥有非常多的客户端比如：navicat，jdbc，SQLyog等客户端，这些客户端都需要向
MYSQL通信都必须要建立连接，这个建立连接的工作就是由连接器完成的

查询缓存(5.8版本开始取消了这个)

连接建立之后，你就可以执行select语句了这个时候首先会去查询下缓存，缓存的存储形式类似于
key-value，key保存的是sql语句value 保存的是结果集，如果查询不在缓存中就会执行sql语句执
行完成后把执行结果放入缓存中，如果是能在缓存中查询到那么直接返回

词法分析器

解析sql语句中的词法, 语法

优化器

对解析后生成的执行计划进行自动优化, 提升查询效率

执行器

执行sql语句分析后生成的执行计划, 执行后返回结果

5. MySQL如何实现乐观锁和悲观锁

①乐观锁实现：版本号控制及时间戳控制。? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 版本号控制：表中加一个 version 字段；当读取数据时，连同这个 version 字段一起读出；数据每更新一次就将此值加一；当提交更新时，判断数据库表中对应记录的当前版本号是否与之前取出来的版本号一致，如果一致则可以直接更新，如果不一致则表示是过期数据需要重试或者做其它操作。? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?时间戳控制：其原理和版本号控制差不多，也是在表中添加一个 timestamp 的时间戳字段，然后提交更新时判断数据库中对应记录的当前时间戳是否与之前取出来的时间戳一致，一致就更新，不一致就重试。
②悲观锁实现：必须关闭mysql数据库的自动提交属性，开启事务，再进行数据库操作，最后提交事务。

6. MySQL常用的存储引擎及区别

InnoDB默认的存储引擎, 不需要任何配置, 默认使用的就是它, 支持事务, 支持主外键连接, 速度一般
MyISAM不支持事务, 速度快, 以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Memory存储的数据都放在内存中, 服务器断电, 数据丢失, 速度最快, 现基本被redis取代.

7. 说一说对MySQL索引的理解

什么是数据库索引？? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 数据库索引，是数据库管理系统中一个排序的数据结构，索引实现通常使用B树及变种的B+树。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
索引的作用？? ? ? ? 协助快速查询、更新数据库表中数据。
副作用：? ? ? ? ? ? ?增加了数据库的存储空间插入和修改数据时要花费较多的时间（因为索引也会随之变动）

8. MySQL中什么字段适合添加索引

表的主键、外键必须有索引；
经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
索引应该建在选择性高的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

9. MySQL中常见的索引类型

普通索引：是最基本的索引，它没有任何限制
唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引
组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

10. MySQL中索引失效的场景！

WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引，类似地，如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=…），MYSQL将无法使用索引
在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用
如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE 'abc%',MYSQL将使用索引；如果条件是LIKE '%abc'，MYSQL将不使用索引。

11. 说一说Hash和B+树结构索引区别？

hash索引 : 等值查询效率高，不能排序，不能进行范围查询；Hash索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
B+树索引 : 数据有序，支持范围查询, 查询效率没有hash索引高.

12. B+比B树索引的区别？

B树，每个节点都储存key和dta，所有节点组成这棵树，并且叶子节点指针为null，叶子节点不包含任何关键字的信息。
B+树，所有叶子节点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子节点本身依关键字的大小自小尔达的顺序链接，所有的非终端节点可以看成是索引部分，节点中仅含有其子树根节点中最大的（或最小的）关键字。

13. 聚集（集中）索引和非聚集（稀疏）索引的区别

【聚集索引】：也称 Clustered Index。是指关系表记录的物理顺序与索引的逻辑顺序相同。由于一张表只能按照一种物理顺序存放，一张表最多也只能存在一个聚集索引。与非聚集索引相比，聚集索引有着更快的检索速度。? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? MySQL 里只有 INNODB 表支持聚集索引，INNODB 表数据本身就是聚集索引，也就是常说 IOT，索引组织表。非叶子节点按照主键顺序存放，叶子节点存放主键以及对应的行记录。所以对 INNODB 表进行全表顺序扫描会非常快。
【非聚集索引】：也叫 Secondary Index。指的是非叶子节点按照索引的键值顺序存放，叶子节点存放索引键值以及对应的主键键值。MySQL 里除了 INNODB 表主键外，其他的都是二级索引。MYISAM，memory 等引擎的表索引都是非聚集索引。简单点说，就是索引与行数据分开存储。一张表可以有多个二级索引。

14. 什么是索引倒排！

倒排索引源于实际应用中需要根据属性的值来查找记录。这种索引表中的每一项都包括一个属性值和具有该属性值的各记录的地址。由于不是由记录来确定属性值，而是由属性值来确定记录的位置，因而称为倒排索引(inverted index)。带有倒排索引的文件我们称为倒排索引文件，简称倒排文件(invertedfile)。

15. 如何快速的复制一张表！

将表结构和数据导出成sql脚本文件, 可以根据要求修改脚本中的表名字防止重名, 然后再导入到mysql中
在库中创建新表后, 使用insert into 表名(字段名) select 字段名 from 表名

16. SQL语句优化的方案？

第一, 开启慢查询日志,让系统运行一段时间, 打开慢查询日志找到具体需要优化的sql语句
第二, 使用explain执行计划分析sql语句中问题出现的哪里
第三, 根据sql语句编写规范, 进行优化, 例如: 不要使用like模糊查询, 因为索引会失效; 尽量避免使用select*, 因为会返回无用字段; 条件中不要使用or关键字, 因为会全表扫描等.

17. 组合索引的最左原则？

MySQL 的联合索引会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。联合索引当遇到范围查询(>、<、between、like)就会停止匹配，建立索引时匹配度高的字段在前，匹配度低的字段在后
举例 : a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

18. 什么是行锁, 表锁, 页锁?

行锁 : 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。
表锁 : 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率高? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 表锁的语法是 lock tables … read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。? ? ? ? ? ? ? ? ? ? ? ? 特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般