面试总结-MySQL常考题

其他博客：https://blog.csdn.net/ThinkWon/article/details/104778621?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param
Redis
https://blog.csdn.net/Butterfly_resting/article/details/89668661?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522160031195619195188344075%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=160031195619195188344075&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_v2~rank_v25-2-89668661.first_rank_v2_rank_v25&utm_term=rides%E9%9D%A2%E8%AF%95%E9%A2%98&spm=1018.2118.3001.4187

事务四大特性

1.原子性： 不可分割的操作，事务要么成功，要么失败。
2.一致性： 事务在执行前后，数据是一致的。
3.隔离性： 事务操作之间是彼此独立互不影响，通常使用锁来实现。（如果事务被影响了，就会撤回到之前的状态）
4.持久性： 事务一旦提交，结果就是永久的，即便系统发生故障，也能恢复。

---

事务隔离级别

1.读未提交 其他事务只要修改了数据，即使未提交，本事务也能够读取到修改后的数据。
2.读已提交 只能读取到已经提交的数据。（Oracale默认隔离级别）
3.可重复读 一个事务内，多次读取同一个数据，在这个事务还没有结束时，其他事务不能访问该数据（包括读和写），这样就可以在同一个事务两次读取到的数据是一样的。（MyIASM默认隔离级别）
4.可串行化 提供严格的事务隔离，要求事务序列化执行，只能一个接着一个执行，不能并发执行。隔离级别最高，代价也最高，性能最低，一般很少使用。

---

并发操作的不一致性

1.脏读 一个事务读取到了另一未提交的数据。（如果那个事务撤回了，读取的数据就是无效的）
2.不可重复读 是在同一个事务内，两次读取到的数据是不同的结果。（事务T1会读取两次数据，在第一次读取某一条数据后，事务T2修改了该数据并提交了事务，T1此时再次读取该数据，两次读取便得到了不同的结果。）
3.幻读 事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据。这是因为在两次查询过程中有另外一个事务插入数据造成的。

---

数据库的三范式

第一范式： 每列都是不可再分的最小数据单元。
第二范式： 在满足第一范式 的条件下，非主键列完全依赖于主键，而不能依赖主键的一部分。
第三范式： 在满足第二范式的条件下，非主键列只能依赖于主键，不能出现传递依赖

---

索引相关

数据库索引，是数据库管理系统中的一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树。B树，索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据，相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

索引并不是越多越好，原因如下：
1。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行插入，更新和删除。因为更新表时，不仅要保存数据，还要保存一下索引文件.
2。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

B树特点：
（1）每个节点可以存多个值（右边值大于左边），节点可以有多个孩子；所有叶子节点位于同一层。多路平衡查找树
（2）每个节点既可保存索引，又可以保存数据
（3）搜索时相当于二分查找
优点：
B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

B+树特点：
（1）非叶子节点不存数据，只存索引，叶子节点存数据；
（2）非叶子节点是从小到大的顺序排列，右边值大于等于左边值，叶子节点也按照大小排列；
（3）每个叶子节点都有相邻叶子节点的指针，安装自小到大的顺序链接；
（4）父节点存有右孩子的第一个元素的索引
优点：
（1）b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素；
（2）b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；
（3）对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

hash索引
哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。对于hash相同的，采用链表的方式解决冲突。类似于hashmap。因为索引的结构是十分紧凑的，所以hash索引的查询很快。
区别
哈希索引适合等值查询，但是无法进行范围查询
哈希索引没办法利用索引完成排序
哈希索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

索引类型
普通索引、唯一索引、主键索引、组合索引、全文索引
普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
全文索引：如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较.
1.普通索引
普通索引唯一的任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件或排序条件中的数据列创建索引
2.唯一索引
在某个数据列只包含彼此不同的值，这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是唯一索引可以保证数据记录的唯一性，MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。
3.主键索引
主键索引的数据列不允许重复和唯一索引类似，但是不允许为NULL，一个表只能有一个主键。
4.外键索引
如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
5.复合索引
联合索引是在多个列上建立索引，联合在数据库操作期间所需的开销更小。只有最左边的一列单独使用才会触发索引，其他的列单个使用无法触发索引。复合索引中，从最左边开始，相连的两个或多个会触发索引（相连和不相连的性能不同），如果没有最左边的列，后面的无论是否相连都不会触发索引。

定义主键和外键主要是为了维护关系数据库的完整性，总结一下：
1.主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。
身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。

2.外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。
比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。
聚集索引与非聚集索引
聚集索引和非聚集索引。其中聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。

---

锁

MyISAM和InnoDB存储引擎使用的锁：

MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

行级锁，表级锁和页级锁对比

行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

---

MySQL数据类型

数值型（INT BIGINT），浮点型(FLOAT DOUBLE)，日期/时间型(DATE TIME YEAR)，字符型(CHAR VARCHAR TEXT)

---

MyISAM与InnoDB 的区别

mysql-5.1版本之前默认引擎是MyISAM，之后是innoDB
区别：
1、 InnoDB支持事务，MyISAM不支持
2、 InnoDB支持外键，而MyISAM不支持。
3、 InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4、 Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；5.7以后的InnoDB支持全文索引了
5、InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

如何选择：
1、 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2、如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3、系统奔溃后，MyISAM恢复起来更困难，能否接受；

---

MySQL优化方法

https://www.cnblogs.com/liliuguang/p/11015964.html
1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

2、使用连接（JOIN）来代替子查询(Sub-Queries)
使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。
连接（JOIN）…之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

4、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
那该对哪些字段建立索引呢？
一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

5、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

6、事务
7、锁定表