MySQL数据库从入门到精通01

常见面试题

1、什么是事务，以及事务的四大特性？

答：事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。

　　事务的四大特性： 

　　　　原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么 全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。

　　　　一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。

　　　　隔离性: 一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。

　　　　持久性:一旦事务提交，则其所做的修改会永久保存到数据库。

2、事务的隔离级别有哪些，MySQL默认是哪个？

答：SQL 标准定义了四个隔离级别：
　　READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

　　READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

　　REPEATABLE-READ(可重复读)《默认》： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

　　SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

3、内连接与左外连接的区别是什么？

答：使用内连接时，不符合连接条件的数据，（不管是左表中的还是右表中的）都不会被组织到结果集中。

　　使用左外连接时，对于不符合连接条件的数据，左表中的内容依然会被组织到结果集中，结果集中该条数据对应的右表部分为 null。

4、常用的存储引擎？InnoDB与MyISAM的区别？

定义

　　InnoDB：MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。它被设计成为大量的短期事务，短期事务大部分情况下是正常提交的，很少被回滚。InnoDB的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

　　MyISAM：在MySQL 5.1 及之前的版本，MyISAM是默认引擎。MyISAM提供的大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。

事务

　　InnoDB：支持

　　MyISAM：不支持

锁

　　InnoDB：支持行锁、表锁。行锁是实现在索引上的，如果没有索引，就没法使用行锁，将退化为表锁。

　　MyISAM：支持表锁。

主键

　　InnoDB：必须有，没有指定会默认生成一个隐藏列作为主键

　　MyISAM：可以没有

索引

　　InnoDB：聚集索引，使用 B+ 树作为索引结构，数据文件和索引绑在一起，必须要有主键。主键索引一次查询；辅助索引两次查询，先查询主键，再查询数据；

　　MyISAM：非聚集索引，使用 B+ 树作为索引结构，索引和数据文件是分离的。主键索引和辅助索引是独立的。

外键

　　InnoDB：支持

　　MyISAM：不支持

AUTO_INCREMENT

　　InnoDB：必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

　　MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

数据库文件

　　InnoDB：frm是表定义文件，ibd是数据文件。支持两种存储方式：

• 共享表空间存储：所有表的数据文件和索引都保存在一个表空间里，一个表空间可以有多个文件，通过 innodb_data_file_path 和 innodb_data_home_dir 参数设置共享表空间的位置和名字，一般共享表空间的名字叫 ibdata1-n。
• 多表空间存储：每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开关，以.ibd为扩展名

　　MyISAM：frm是表定义文件，myd是数据文件，myi是索引文件。支持三种存储格式：静态表（默认，注意数据末尾不能有空格，会被去掉。）、动态表、压缩表。

表的行数

　　InnoDB：没有保存。select count(*) from table;会扫描全表。

　　MyISAM：保存。select count(*) from table;会直接取出该值。

注：但加了 where 条件后，两者处理方式一样，都是扫描全表。

全文索引

　　InnoDB：5.7及以后版本支持。

　　MyISAM：支持。

5、MySQL默认InnoDB引擎的索引是什么数据结构？

答：B+Tree

6、如何查看MySQL的执行计划？

答：MySQL 使用 explain + sql 语句查看 执行计划。

7、索引失效的情况有哪些？

答：1.有or必全有索引;

　　2.复合索引未用左列字段;
　　3.like以%开头;
　　4.需要类型转换;
　　5.where中索引列有运算;
　　6.where中索引列使用了函数;
　　7.如果mysql觉得全表扫描更快时（数据少）;

8、什么是回表查询？

答：如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

9、什么是MVCC？

答：MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存。

 

　　MVCC 使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。

10、MySQL主从复制的原理是什么？

答：

①当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中。

②salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程。

③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。

④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。

⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。

11、主从复制之后的读写分离如何实现？

答：就是写一个主库，但是主库挂多个从库，然后从多个从库来读，单单只是写主库，然后主库会自动把数据给同步到从库上去。

12、数据库的分库分表如何实现？

答：1、增加一个中间层，中间层实现 MySQL 客户端协议，可以做到应用程序无感知地与中间层交互。由于是基于协议层的代理，可以做到支持多语言，但需要多启动一个进程、SQL 的解析也耗费大量性能、由于协议绑定仅支持单个种类的数据库库。

　　2、在代码层面增加一个路由程序，控制对数据库与表的读写。路由程序写在项目里，与编程语言绑定、连接数高、但相对轻量（比如 Java 仅需要引入 SharingShpere 组件中 Sharding-JDBC 的 jar 即可）、支持任意数据库。