【MySQL】MySql常见面试题总结

目录

一、什么是sql注入

二、sql语句的执行流程

三、内连接和外连接的区别

四、Union和Union All 有什么区别

五、MySql如何取差集

六、DELETE和TRUNCATE有什么区别

七、count（*）和count（1）的区别

八、MyISAM和InnoDB的区别

九、行级锁和表级锁

十、InnoDB存储引擎的三种行级锁

十一、谈谈对事务的理解

十二、谈谈数据库事务的实现原理

十三、什么是MVCC

十四、数据库并发事务会带来哪些问题

十五、事务的隔离级别

十六、谈谈对索引的理解

十七、索引的优缺点

十八、谈谈对B+Tree的理解

十九、谈谈MySql中的索引

二十、为什么选择B+Tree

二十一、BTree和B+Tree的主要区别

二十二、索引优化

二十三、MySql索引在什么情况下会失效

二十四、SQL优化

---

一、什么是sql注入

sql注入是指攻击者通过对用户输入的字符串内容加入sql语句，与程序中sql进行拼接，形成恶意查询，非法命令等攻击手段。一般使用 ？占位符来避免sql注入。

---

二、sql语句的执行流程

通过MySql连接器，建立Tcp连接，进行用户密码的验证和权限的校验。

通过授权认证后，对sql语句进行语义分析，判断是否语法正确。

对sql语句进行预处理，sql预编译。

执行查询优化器来获取最优执行路径。

通过索引或者全表查询。

---

三、内连接和外连接的区别

内连接是指只获取两表都符合条件的数据。

而外连接除了符合条件的数据，还有左表数据、右表数据、两个连接表中所有数据，外连接分为

        左外连接是指左表所有数据保留，右表保留符合条件的数据，通过left join。

        右外连接是指右表所有数据保留，左表保留符合条件的数据，通过right join。

        全外连接是指保留所有行，通过full join。

---

四、Union和Union All 有什么区别

Union是指对两个结果集不包含重复项 Union ALL包含重复项

---

五、MySql如何取差集

当对比字段存在索引时，使用NOT EXISTS 效率高

当没有索引时，使用LEFT JOIN 或者RIGHT JOIN效率高

---

六、DELETE和TRUNCATE有什么区别

首先delete语句可以接条件，truncate不行。

delete是逐条删除的，每条数据都会写入日志，而truncate是按数据页删的，只记录页释放。

delete删除后会保留空数据页，truncate因为是按页删除的所以不会保留数据页。

如果该表存在id自增的话，delete后会继续上次的数自增，而truncate后从1开始。

---

七、count（*）和count（1）的区别

count（1）就是把查询结果替换成1，count（*）是查询所有字段。

count（1）的效率要比count（*）高。

---

八、MyISAM和InnoDB的区别

MyISAM是mysql5.5之前默认的存储引擎而InnoDB是5.5之后默认的。

MyISAM是不支持事务的，而InnoDB支持。

MyISAM只支持表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁。

MyISAM不支持外键，InnoDB支持。

---

九、行级锁和表级锁

表级锁是对整张表加锁，实现简单，消耗资源少，加锁快，不会出现死锁。但触发锁冲突的概率也大，并发度低。MyISAM和InnoDB存储引擎都支持表级锁。

行级锁是对当前操作的行进行加锁，他的并发度高，加锁慢，消耗资源多，可能发生死锁。

---

十、InnoDB存储引擎的三种行级锁

记录锁是指在单个行上加锁。

间隙锁是指锁定一个范围，不包含记录本身。

临键锁是他两相加，锁定一个范围，包含记录本身。

---

十一、谈谈对事务的理解

事务是数据库的一种特性，包括ACID四个特点：

原子性：代表事务是数据库的最小执行单元。用于确保一个执行过程中所有步骤全部成功或失败。

一致性：指在进行修改操作时，要求数据在修改前和修改后状态保持一致。

隔离性：是指在一个事务执行时不能被其他事务干扰。

持久性：是指一个事务一旦被提交，数据的改变时永久的。

---

十二、谈谈数据库事务的实现原理

InnoDB是通过重做日志来保证事务的持久性。

通过回滚日志来保证事务的原子性。

通过锁机制、MVCC等手段保证事务的隔离性。

保证了事务的持久性、原子性、隔离性后一致性才得以保障。

---

十三、什么是MVCC

MVCC是行级锁的一个变种，它能够减少加锁操作，因此开销更低，大多数MVCC都实现了非阻塞的读操作，写操作也只锁定必要行。

MVCC是一种用来解决读写冲突的并发控制，为事务分配单项增长的时间戳，为每个修改保存一个版本，每个版本对应一个时间戳，每个事务都有对应一个版本快照。快照版本按照时间戳来决定先后顺序。

读操作，只需要去读该事务开始前的最新数据快照。

---

十四、数据库并发事务会带来哪些问题

脏读：是指一个事务访问并修改这个数据时另一个事务访问了这个数据，因为修改的事务还没提交。就会导致读取的结果可能不正确。从而导致脏读。

不可重复读：是指在一个事务中多次读取同一个数据，而在其中两次读取数据之间，另一个事务对该数据进行了修改，从而导致两次读取数据不同。

丢失数据：是指两个事务同时修改一个数据导致其中一个修改的结果就被丢失。

幻读：是指在一个事务读取几行数据后，另一个事务增加了一些数据，而当第一个事务再次读取数据时，发现多了一些原本不存在的数据。

---

十五、事务的隔离级别

读未提交：最低隔离级别，允许读取尚未提交的数据。可能会导致脏读、幻读、不可重复读。

读已提交：Oracle默认隔离级别，只能读取已经提交的数据，可以避免脏读，但可能会造成幻读和不可重复读。

可重复读：MySql默认隔离级别，指一个事务中读一个数据的多次读取结果是一致的，除非这个事务本身修改过这个数据，可以避免脏读和不可重复读，可能发生幻读。

串行化：最高隔离级别，将所有事务挨个执行，可以避免脏读、幻读、不可重复读。

---

十六、谈谈对索引的理解

索引是一种用于快速查询或检索数据的数据库存储结构，保存了数据库指定字段的数据位置。

常见的索引有B+Tree索引和Hash索引

索引主要用提高数据库查询效率，如果使用索引可以通过索引快速查找表中对应记录。

 索引按照逻辑区别大致分为主键索引、唯一索引、普通索引、全文索引。按照字段分别单例索引和组合索引。

---

十七、索引的优缺点

索引的优点肯定是加快查找速率，减少数据库需要扫描的数据行。通过唯一索引保证数据库每行的唯一性。

缺点则是创建索引和维护索引会消耗时间和资源，在进行增删改的同时，也会对索引进行修改，导致sql的执行效率也会降低。索引还会占用多余存储空间。

---

十八、谈谈对B+Tree的理解

B+Tree的话是对BTree的一个变种，他是MySql默认的索引类型。他的所有节点都是按照递增顺序排列，按照左小右大原则，所有数据都是存放在叶子节点上，每一个叶子节点上都有一个指向下一个叶子节点的指针，形成有序链表。

在查询数据时，首先先在根节点上进行二分查找，找到对应的叶子节点，在进行二分查找，找到key所对应的数据。

区间查找时，由于叶子节点形成了有序链表，所以可以通过指针遍历相邻叶子结点，提高查询效率。

---

十九、谈谈MySql中的索引

除了上面的B+Tree索引还有一个常用索引Hash索引

他是基于hash表实现的一种索引结构，通过hash算法来计算字段哈希值，来找到对应数据。所以他的时间复杂度为O(1)。

他无法用于排序、分组、范围查找。只支持精确查找，

在InnoDB存储引擎中有一个特殊功能叫自适应哈希索引，当某个索引值使用频繁时，他会在B+Tree索引之上在建一个hash索引，以此来获得hash索引的优点。

---

二十、为什么选择B+Tree

全表扫描能力强。因为子节点存在有序链表，可以直接通过链表遍历。

支持排序分组。

查询时更加稳定，因为数据只保存在叶子节点上。

读写能力强。他的根节点和枝节点不保存数据，所以根枝同样大小的情况下，保存的关键字要比BTree要多。所以读一次磁盘加载的关键字也比BTree多。

---

二十一、BTree和B+Tree的主要区别

首先BTree他的数据是存放在所有节点中，而B+Tree只在叶子结点中。

BTree在查找时可能在任何节点停止，而B+Tree只会在叶子节点中，更加稳定。

B+Tree的叶子节点形成一条有序链表，能够提高查询速率。

B+Tree在区间查找更胜一筹。

---

二十二、索引优化

可以通过对选择索引的字段来进行优化：

        尽量不选择数据大多为NULL的字段，如果必须要用，建议使用0、1、true、false来代替。

        可以考虑被where条件查询的字段建立索引。

        频繁需要排序的字段建立索引。

        频繁用于连接的字段建立索引。

频繁需要更新的字段且又不常查询建议不要索引。因为每次更新数据都需要同步更新索引，需要消耗资源。

尽量多使用联合索引而不是单例索引。因为一个联合索引可以代替多个单例索引，能够减少空间，维护的时间。

注意避免冗余索引，比如一个联合索引已经包括一个单例索引，但还是建了这个单例索引。

在字符串类型的字段建议使用前缀索引而不是普通索引，因为前缀索引占用空间更小。

避免where子句中对所有字段使用函数，容易导致索引失效。

---

二十三、MySql索引在什么情况下会失效

模糊查询时使用%开头会导致索引失效。

组合索引中包含不包含最左边字段索引会失效。

组合索引中范围搜索后的字段索引会失效。

数据类型不匹配导致索引失效。

使用!=、<、>、not in 运算会失效。

字段内容为null，导致索引失效。

or前后的条件有一个不包含索引会失效。

添加索引的字段上使用函数或者计算，会导致索引失效。

---

二十四、SQL优化

禁止使用select *必须使用select 字段进行查询，在SQL中明确查询字段名称。

考虑在where和order by的字段建立索引。

使用 explain语句，观察sql执行计划。

防止索引失效，第二十三条所有。

---

注：本篇文章都是我自己的理解，可能用词和语句不够严谨，如有错误请评论指正，谢谢！（持续更新中......）