面试题- Mysql汇总

数据库索引创建时的思考

什么时候需要建立索引
什么时候不应该创建索引

什么时候需要建立索引:

作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
在经常使用在 where 子句中的列上面创建索引，加快条件的判断速度。

什么时候不应该创建索引:

在查询中很少使用或者作为参考的列不应该创建索引。
对于那些只有很少数据值的列也不应该增加索引（比如性别，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度）。
对于那些定义为text，image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引，因为修改性能和检索性能是矛盾的。

引申：给定 sql，索引应该如何建立

SELECT * FROM table WHERE a > 1 and b = 2; 

主要考察联合索引、最左匹配几个知识点。 1）最左原则指的就是如果 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。 2）Mysql 有优化器会自动调整a,b的顺序与索引顺序一致，比如对(a,b)字段建立联合索引，查询条件是 b=xx and a=xx，优化器会自动调整查询顺序以匹配索引

对 (b,a) 建立索引。如果建立的是 (a,b) 索引，那么只有 a 字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。如果对 (b,a) 建立索引那么两个字段都能用上，优化器会帮调整 where 后 a,b 的顺序，让用上索引。

引申：索引有哪些优化手段

索引覆盖：
- 可以减少回表次数，如果所需信息在索引上存在，就不需要回表了。
- 追问：什么是回表？以非主键索引搜索，会先拿到主键ID，然后再通过主键ID查询主键索引，取到行内容。这个过程称为回表
最左前缀原则：
- 无论是a索引，还是联合索引(a, b)，都可以在应用最左前缀原则
- a索引和联合索引(a, b)，都可以用来查询a。所以有(a, b)，一般就不用a了
索引下推：
- MySQL 5.6之前，无索引下推，联合索引利用率低，只能使用最左边的索引信息，其余字段无用
- MySQL 5.6之后，有索引下推，如果查询条件字段，包含在联合索引的非最左字段们中，也可以进行判断，从而减少回表的次数
建立索引的字段应该非空，在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替NULL；

了解更多……

SQL 中 INNER、LEFT、RIGHT JOIN 的区别

JOIN 用于按照 ON 条件联接两个表，主要有四种：

A INNER JOIN B ON……：内联操作，将符合 ON 条件的 A 表和 B 表结果均搜索出来，然后合并为一个结果集。仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。我理解的是只要记录不符合ON条件，就不会显示在结果集内。
A LEFT JOIN B ON……：左联操作，左联顾名思义是，将符合 ON 条件的 B 表结果搜索出来，然后左联到 A 表上，然后将合并后的 A 表输出。如果左表的某记录在右表中没有匹配记录，则在相关联的结果集中右表的所有选择列表列均为空值。理解为即使不符合ON条件，左表中的记录也全部显示出来，且结果集中该类记录的右表字段为空值。
A RIGHT JOIN B ON……：右联操作，右联顾名思义是，将符合 ON 条件的 A 表结果搜索出来，然后右联到 B 表上，然后将合并后的 B 表输出。
A FULL JOIN B ON……：完整外部联接返回左表和右表中的所有行。就是LEFT JOIN和RIGHT JOIN和合并，左右两表的数据都全部显示。

乐观锁与悲观锁的区别

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。[1] 悲观锁假定其他用户企图访问或者改变你正在访问、更改的对象的概率是很高的，因此在悲观锁的环境中，在你开始改变此对象之前就将该对象锁住，并且直到你提交了所作的更改之后才释放锁。悲观的缺陷是不论是页锁还是行锁，加锁的时间可能会很长，这样可能会长时间的限制其他用户的访问，也就是说悲观锁的并发访问性不好。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。[1] 乐观锁不能解决脏读的问题。乐观锁则认为其他用户企图改变你正在更改的对象的概率是很小的，因此乐观锁直到你准备提交所作的更改时才将对象锁住，当你读取以及改变该对象时并不加锁。可见乐观锁加锁的时间要比悲观锁短，乐观锁可以用较大的锁粒度获得较好的并发访问性能。但是如果第二个用户恰好在第一个用户提交更改之前读取了该对象，那么当他完成了自己的更改进行提交时，数据库就会发现该对象已经变化了，这样，第二个用户不得不重新读取该对象并作出更改。这说明在乐观锁环境中，会增加并发用户读取对象的次数。

从数据库厂商的角度看，使用乐观的页锁是比较好的，尤其在影响很多行的批量操作中可以放比较少的锁，从而降低对资源的需求提高数据库的性能。再考虑聚集索引。在数据库中记录是按照聚集索引的物理顺序存放的。如果使用页锁，当两个用户同时访问更改位于同一数据页上的相邻两行时，其中一个用户必须等待另一个用户释放锁，这会明显地降低系统的性能。interbase和大多数关系数据库一样，采用的是乐观锁，而且读锁是共享的，写锁是排他的。可以在一个读锁上再放置读锁，但不能再放置写锁；你不能在写锁上再放置任何锁。锁是目前解决多用户并发访问的有效手段。

了解更多

MySQL 分库分表环境下全局 ID 生成方案

在大型互联网应用中，随着用户数的增加，为了提高应用的性能，我们经常需要对数据库进行分库分表操作。在单表时代，可以完全依赖于数据库的自增ID来唯一标识一个用户或数据对象。但是当对数据库进行了分库分表后，就不能依赖于每个表的自增ID来全局唯一标识这些数据了。因此，需要提供一个全局唯一的ID号生成策略来支持分库分表的环境。

1、数据库自增ID——来自 Flicker 的解决方案:

因为 MySQL 本身支持 auto_increment 操作，很自然地，会想到借助这个特性来实现这个功能。Flicker 在解决全局 ID 生成方案里就采用了 MySQL 自增长 ID 的机制（auto_increment + replace into + MyISAM）

先创建单独的数据库(eg:ticket)，然后创建一个表：

CREATE TABLE Tickets64 (
      id bigint(20) unsigned NOT NULL auto_increment,
      stub char(1) NOT NULL default '',
      PRIMARY KEY (id),
      UNIQUE KEY stub (stub)
  ) ENGINE=MyISAM

REPLACE INTO 用于实时覆盖写入数据。写入数据时，会先根据主键判断待写入的数据是否已经存在于表中，并根据判断结果选择不同的方式写入数据：

如果待写入数据已经存在，则先删除该行数据，然后插入新的数据。

如果待写入数据不存在，则直接插入新数据。

当插入记录后，执行 SELECT * from Tickets64，查询结果就是这样的：

id	stub
72157623227190423	a

在我们的应用端需要做下面这两个操作，在一个事务会话里提交：

REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();

这样我们就能拿到不断增长且不重复的 ID 了。

从高可用角度考虑，接下来就要解决单点故障问题：Flicker 启用了两台数据库服务器来生成 ID，通过区分 auto_increment 的起始值和步长来生成奇偶数的 ID。

最后，在客户端只需要通过轮询方式取 ID 就可以了。

优点：

充分借助数据库的自增 ID 机制，提供高可靠性，生成的 ID 有序。缺点：
占用两个独立的 MySQL 实例，有些浪费资源，成本较高。

2、独立的应用程序——来自 Twitter 的解决方案:

Twitter 在把存储系统从 MySQL 迁移到 Cassandra 的过程中由于 Cassandra 没有顺序 ID 生成机制，于是自己开发了一套全局唯一 ID 生成服务：Snowflake。GitHub地址：https://github.com/twitter/snowflake。根据 twitter 的业务需求，snowflake 系统生成 64 位的 ID。由 3 部分组成，最高位是符号位，始终为 0：

41 位的时间序列（精确到毫秒，41 位的长度可以使用 69 年）
10 位的机器标识（10 位的长度最多支持部署 1024 个节点）
12 位的计数顺序号（12 位的计数顺序号支持每个节点每毫秒产生4096个 ID 序号）

优点：

高性能，低延迟；
独立的应用；
按时间有序。

缺点：

需要独立的开发和部署。

使用 B 树和 B+ 树的比较

InnoDB 的索引使用的是 B+ 树实现，B+ 树对比 B 树的好处：

IO 次数少：B+ 树的中间结点只存放索引，数据都存在叶结点中，因此中间结点可以存更多的数据，让索引树更加矮胖；
范围查询效率更高：B 树需要中序遍历整个树，只 B+ 树需要遍历叶结点中的链表；查询效率更加稳定：每次查询都需要从根结点到叶结点，路径长度相同，所以每次查询的效率都差不多；

使用 B+ 树索引和哈希索引的比较

哈希索引能以 O(1) 时间进行查找，但是只支持精确查找，无法用于部分查找和范围查找，无法用于排序与分组；B+树索引支持大于小于等于查找，范围查找。哈希索引遇到大量哈希值相等的情况后查找效率会降低。哈希索引不支持数据的排序。

谈谈你对聚集索引的理解

聚集索引：该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

非聚集索引：数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

非聚集索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。对于非聚集索引，可以为在表非聚集索引中查找数据时常用的每个列创建一个非聚集索引。有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。

问题引申

第一：聚集索引的约束是唯一性，是否要求字段也是唯一的呢？ 分析：如果认为是的朋友，可能是受系统默认设置的影响，一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。结论：聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。

第二：为什么聚集索引可以创建在任何一列上，如果此表没有主键约束，即有可能存在重复行数据呢？ 粗一看，这还真是和聚集索引的约束相背，但实际情况真可以创建聚集索引。分析其原因是：如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。

第三：是不是聚集索引就一定要比非聚集索引性能优呢？ 如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引，此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。

第四：在数据库中通过什么描述聚集索引与非聚集索引的？ 索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。

第五：在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？ 有了上面第四点的认识，我们分析这个问题就有把握了，在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况:聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行，这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。

MySQL 隔离级别有哪些

读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。
读提交：一个事务提交之后，它做的变更才会被其他事务看到。
可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。（数据校对）
串行化：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行;

MySQL 默认隔离级别是可重复读，Oracle 默认隔离级别是读提交。

了解更多……

什么是 MVCC？

多版本并发控制（Multi-Version Concurrency Control, MVCC），MVCC 在每行记录后面都保存有两个隐藏的列，用来存储创建版本号和删除版本号。

创建版本号：创建一个数据行时的事务版本号（事务版本号：事务开始时的系统版本号；系统版本号：每开始一个新的事务，系统版本号就会自动递增）；
删除版本号：删除操作时的事务版本号；

各种操作：

插入操作时，记录创建版本号；
删除操作时，记录删除版本号；
更新操作时，先记录删除版本号，再新增一行记录创建版本号；
查询操作时，要符合以下条件才能被查询出来：
- 删除版本号未定义或大于当前事务版本号（删除操作是在当前事务启动之后做的）；
- 创建版本号小于或等于当前事务版本号（创建操作是事务完成或者在事务启动之前完成）通过版本号减少了锁的争用，提高了系统性能；

可以实现提交读和可重复读两种隔离级别，未提交读无需使用MVCC

了解更多……

MYSQL 存储引擎（4种）

MyISAM 它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表。每个MyISAM 在磁盘上存储成 3 个文件，其中文件名和表名都相同，但是扩展名分别为：.frm(存储表定义) ,YD(MYData，存储数据) , MYI(MYIndex，存储索引),
InnoDB, InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
memory使用存在内存中的内容来创建表。每个MEMORY表实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常快，因为它到数据是放在内存中的，并且默认使用HASH索引，但是一旦服务器关闭，表中的数据就会丢失，但表还会继续存在。
merge存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，MERGE表中并没有数据，对MERGE类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的MyISAM表进行操作。

事务的概念和特性？

概念：事务（Transaction）是一个操作序列，不可分割的工作单位，以BEGIN TRANSACTION开始，以ROLLBACK/COMMIT结束特性（ACID）：原子性（Atomicity）：逻辑上是不可分割的操作单元，事务的所有操作要么全部提交成功，要么全部失败回滚（用回滚日志实现，反向执行日志中的操作）；一致性（Consistency）：事务的执行必须使数据库保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的；隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其它事务是不可见的（并发执行的事务之间不能相互影响）；持久性（Durability）：一旦事务提交成功，对数据的修改是永久性的

了解更多……

快照读与当前读

使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销：

select * from table ...;

当前读读取的是最新的数据，需要加锁。以下第一个语句需要加 S 锁，其它都需要加 X 锁：

select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update;
delete;

了解更多……

什么是两段锁协议？

事务必须严格分为两个阶段对数据进行加锁和解锁的操作，第一阶段加锁，第二阶段解锁。也就是说一个事务中一旦释放了锁，就不能再申请新锁了。

了解更多……

MySQL是如何保证主备一致

使用binlog（三种格式，statement、row、mixed）

数据库索引创建时的思考

引申：给定 sql，索引应该如何建立

引申：索引有哪些优化手段

SQL 中 INNER、LEFT、RIGHT JOIN 的区别

乐观锁与悲观锁的区别

MySQL 分库分表环境下全局 ID 生成方案

使用 B 树和 B+ 树的比较

使用 B+ 树索引和哈希索引的比较

谈谈你对聚集索引的理解

问题引申

MySQL 隔离级别有哪些

什么是 MVCC？

MYSQL 存储引擎（4种）

事务的概念和特性？

快照读与当前读

什么是两段锁协议？

MySQL是如何保证主备一致

CATALOG

FEATURED TAGS

FRIENDS