MySQL面试系列-03

1. MySQL 中事务回滚机制原理？

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。

要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚。

2. 什么是锁？

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

3. 什么是视图？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得获取数据更容易，相比多表查询。

4. 视图有哪些优点？

1）视图能够简化用户的操作；

2）视图使用户能以多种角度看待同一数据；

3）视图为数据库提供了一定程度的逻辑独立性；

4）视图能够对机密数据提供安全保护。

5. 什么是存储过程？如何调用？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

可以用一个命令对象来调用存储过程。

6. 什么是游标？

游标是对查询出来的结果集作为一个单元来有效的处理。

游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

7. 说一说 SQL 语句优化有哪些方法？

1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

2）用EXISTS替代IN、用NOTEXISTS替代NOTIN。

3）避免在索引列上使用计算。

4）避免在索引列上使用ISNULL和ISNOTNULL。

5）对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引。

6）应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。

7）应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

8. MySQL 中单表记录数过大时如何优化？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施：

1、限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；

2、读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；

3、缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

4、垂直分区

根据数据库里面数据表的相关性进行拆分。 例如用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

垂直拆分的优点：可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

5、水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。

9. 什么是脏读、不可重复读和幻读？

脏读：意味着一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。即这个事务读取的数据是不正确的

不可重复读：在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。即这个事物在读的过程中被修改了

幻读：当一个事务对整个table进行修改之后，第二个事物向表中插入了一行数据，此时第一个事物发现了新插入的没有修改的数据行，好像发生了幻觉一样。也就是本身应该是修改5行数据，但是现在有6行数据发生修改。

10. MySQL 有哪几种索引类型，各自特点？

常见的MySQL索引结构有B-树索引，B+树索引，Hash索引和全文索引。

B-Tree索引

1）因为存储引擎不用进行全表扫描来获取数据，直接从索引的根节点开始搜索，从而能加快访问数据的速度。

2）B-Tree对索引是顺序组织存储的，很适合查找范围数据。

3）适用于全键值、键值范围或者键前缀查找（根据最左前缀查找）。

4）限制：对于联合索引来说，如果不是从最左列开始查找，则无法使用索引；不能跳过索引中的列。

B+Tree索引

1）是B-Tree索引的变种，现在主流的存储引擎都不用单纯的B-Tree，而是其变种B+Tree或者T-Tree等等。

2）和B-Tree最主要的区别就是B+Tree的内节点不存储data，只存储key，叶子节点不存储指针。

Hash索引

1）基于Hash表实现，只有Memory存储引擎显式支持哈希索引。

2）适合等值查询，如=、in)、<=>，不支持范围查询。

3）因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序。

4）Hash索引在查询等值时非常快。

5）因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找。

6）如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

7）程序员可以在B+Tree索引的基础上创建自适应Hash索引。

全文索引

1）MyISAM和InnoDB都支持全文索引。

2）有三种模式：自然语言模式，布尔模式和查询扩展模式。

R-Tree索引

MyISAM支持R-Tree索引，这个和全文索引基本不问。

11. 什么是聚簇索引？

聚集索引也称Clustered Index。是指关系表记录的物理顺序与索引的逻辑顺序相同。由于一张表只能按照一种物理顺序存放，一张表最多也只能存在一个聚集索引。与非聚集索引相比，聚集索引有着更快的检索速度。

MySQL中只有INNODB表支持聚集索引，INNODB表数据本身就是聚集索引，也就是常说IOT，索引组织表。非叶子节点按照主键顺序存放，叶子节点存放主键以及对应的行记录。所以对INNODB表进行全表顺序扫描会非常快。

12. 什么是非聚簇索引？

非聚集索引也称为Secondary Index。指的是非叶子节点按照索引的键值顺序存放，叶子节点存放索引键值以及对应的主键键值。

MySQL里除了INNODB表主键外，其他的都是二级索引。MYISAM、memory等引擎的表索引都是非聚集索引。简单点说，就是索引与行数据分开存储。一张表可以有多个二级索引。

13. SQL 语句中使用 like 如何避免索引失效？

使用like模糊查询全部字段的的情况

1）当两边使用%时，结果是全表扫描，索引失效。

2）当左边使用%时，结果是全表扫描，索引失效。

3）当右边使用%时，结果是range，索引生效。

上述三种情况当使用like模糊查询所有字段，不包含在索引列中的字段时，只有%放在右边索引才会生效。

注意尽量避免SELECT *查询，而是一一罗列出所需要查询的字段，因为可能搞不好SELECT *查询多出一个字段，就导致全表扫描。

覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

只扫描索引而无需回表的优点：

1）索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。

2）因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。

3）一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。

4）innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引

14. MySQL 中分库分表可能会遇到什么问题？

事务问题：需要用分布式事务。

跨节点Join的问题：解决这问题可以分两次查询实现。

跨节点的count、order by、group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。

数据迁移，容量规划，扩容等问题。

ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID。

跨分片的排序分页问题：后台加大对pagesize处理。

15. 在高并发情况下，如何做到安全的修改同一行数据？

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案。

使用悲观锁

悲观锁思想是当前线程要进来修改数据时，别的线程都得拒之门外，比如可以使用如下SQL语句。

select * from jingxuan where id=736 for update

以上这条sql语句会锁定了User表中所有符合检索条件（id=736）的记录。本次事务提交之前，别的线程都无法修改这些记录。

使用乐观锁

乐观锁思想是有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。

实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

16. MySQL 中自增主键可能会遇到什么问题？

1、使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。解决方案的话，简单点的话可以考虑使用UUID。

2、自增主键会产生表锁，从而引发问题。

3、自增主键可能用完问题。

17. MySQL 中存储日期格式时，如何考虑时区转换问题？

1、datetime类型适合用来记录数据的原始的创建时间，修改记录中其他字段的值，datetime字段的值不会改变，除非手动修改它。

2、timestamp类型适合用来记录数据的最后修改时间，只要修改了记录中其他字段的值，timestamp字段的值都会被自动更新。

18. SQL 语句执行过久，如何优化？从哪些方面入手？

1、查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等。

2、优化索引结构，看是否可以适当添加索引。

3、数量大的表，可以考虑进行分离/分表（如交易流水表）。

4、数据库主从分离，读写分离。

5、explain分析sql语句，查看执行计划，优化sql。

6、查看mysql执行日志，分析是否有其他方面的问题。