MySQL面试题

MySQL面试题

1、数据库存储引擎有哪些？(高频)

MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

2、InnoDB与MyISAM的区别？(高频)

1、InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2、InnoDB支持外键，而MyISAM不支持。

3、InnoDB是支持表锁和行级锁，MyISAM只支持表锁

3、什么是索引？(高频)

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

4、如何创建索引？(高频)

为了提升上述查询效率，可以对name字段创建索引。创建索引有两种方式：

1、方式一：在创建表的时候创建索引

-- 语法结构
CREATE TABLE  表名( 属性名 数据类型[完整性约束条件], 
    属性名 数据类型[完整性约束条件], 
    ...... 
    属性名 数据类型  
    [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX | KEY 
    [ 别名]  ( 属性名1  [(长度)]  [ ASC | DESC] ) 
);

2、方式二：使用create index语句进行索引创建

5、常见的索引约束有哪些？(高频)

1、UNIQUE：唯一索引

​ 表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为UNIQUE。

2、FULLTEXT: 全文索引

​ 表示全文搜索，在检索长文本的时候，效果最好，短文本建议使用普通索引,但是在检索的时候数据量比较大的时候，现将数据放入一个没有全局索引的表中，然后在用Create Index创建的Full Text索引，要比先为一张表建立Full Text然后在写入数据要快的很多。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

3、SPATIAL: 空间索引

​ 空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。如果没有指定索引约束，此时创建的索引就是普通索引。而一般情况下只需要创建普通索引。

4、普通索引：如果没有指定索引约束，此时创建的索引就是普通索引。而一般情况下只需要创建普通索引。

6、常见的索引类型有哪些？(高频)

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。

MySQL目前提供了以下4种索引：

• BTREE索引： 最常见的索引类型，大部分索引都支持 B 树索引。

• HASH索引：只有Memory引擎支持 ， 使用场景简单 。

• R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。

• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

各种存储引擎对索引的支持：

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。

7、什么情况下设置了索引失效情况？(高频)

① 违背了最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

② 范围查询: 范围查询右边的列，不能使用索引 。

③ 列运算：不要在索引列上进行运算操作， 索引将失效。

④ 字符串：字符串不加单引号，造成索引失效。

由于，在查询时没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

⑤ 模糊查询：以%开头的like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

8、如何优化SQL？(高频)

① SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如： select id from table_name where numin(1,2,3) 对于连续的数值，能用between 就不要用in了。

② SELECT语句务必指明字段名称

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

③ 如果排序字段没有用到索引，就尽量少排序

④ 如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

or查询：

(1) or两边放联合索引，不触发索引(如果两边是单列索引另算)

(2) or两边是单列索引，查询走索引

(3) or两边只要有一个不是索引就不启用索引查询

单例索引演示：

复合索引演示：

(4) or两边一个是联合索引的最左索引一个是单例索引才生效，否则失效

示例：

-- 创建单列索引
 create index idx_nickname on tb_seller(nickname) ;

使用索引：

索引失效：

⑤ 不建议使用%前缀模糊查询：例如LIKE “%name”或者LIKE “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE“name%”。

9、什么是事务？(高频)

概述：由多个操作组成的一个逻辑单元，组成这个逻辑单元的多个操作要么都成功，要么都失败。

举例：转账

10、ACID是什么？可以详细说一下吗？(高频)

A=Atomicity原子性：就是上面说的,要么全部成功,要么全部失败，不可能只执行一部分操作。

C=Consistency一致性：系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态。

I=Isolation隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况。

D=Durability持久性：一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果。

11、并发事务带来哪些问题？(高频)

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致

以下的问题。

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

丢失修改（Lost to modify）：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-

1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

不可重复读（Unrepeatableread）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的

两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

12、怎么解决这些问题呢？MySQL的默认隔离级别是？(高频)

解决方案：对事务进行隔离

MySQL的四种隔离级别如下:

未提交读(READ UNCOMMITTED)：这个隔离级别下,其他事务可以看到本事务没有提交的部分修改。因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚)。这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

已提交读(READ COMMITTED)：其他事务只能读取到本事务已经提交的部分。这个隔离级别有不可重复读的问题，在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改。

REPEATABLE READ(可重复读)：可重复读隔离级别解决了上面不可重复读的问题(看名字也知道)，但是不能完全解决幻读。MySql默认的事务隔离级别就是：

REPEATABLE READ

SERIALIZABLE(可串行化)：这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用。

13、MySQL中有哪几种锁？

从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。

2） 行锁：操作时，会锁定当前操作行。

3） 页面锁：会锁定一部分的数据

从对数据操作的类型分：

1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

各存储引擎对锁的支持情况：

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

MySQL这2种锁的特性可大致归纳如下 ：

锁类型	特点
表级锁	偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁	偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁	开销和加锁时间界于表锁和行锁之间； 会出现死锁； 锁定粒度界于表锁和行锁之间， 并发度一般。