mysql ace_Mysql面试总结

1.什么是索引？

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。

2.那么索引具体采用的哪种数据结构呢？

常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树。

B+ Tree的叶子节点都可以存哪些东西吗？

InnoDB的B+ Tree可能存储的是整行数据，也有可能是主键的值。

那这两者有什么区别吗？

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引(聚集索引)。

而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

下面详细介绍B+树、B-树：

那么MySQL最终为什么要采用B+树存储索引结构呢，那么看看B-树和B+树在存储结构上有什么不同？1、B-树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。

因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，B+树的叶子节点存放关键字和数据，

因此，从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，

因此查询会更快一些。

2、B-树由于每个节点都存储关键字和数据，因此离根节点进的数据，查询的就快，离根节点远的数据，查询的就慢；

B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。

3、在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

B-树： B-树的搜索，从根结点开始，对结点内的关键字(有序)序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B+树：  B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中(B-树可以在非叶子结点命中)，其性能也等价于在关键字全集做一次二分查找；

3.InnoDB使用的B+ Tree的索引模型，为什么采用B+ 树吗？这和Hash索引比较起来有什么优缺点吗

B+ Tree索引和Hash索引区别 哈希索引适合等值查询，但是不无法进行范围查询 哈希索引没办法利用索引完成排序 哈希索引不支持多列联合索引的最左匹配规则 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

4.你们在创建索引的时候都会考虑哪些因素呢？

对于查询概率比较高，经常作为where条件的字段设置索引

最左前缀匹配？

在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了(key1)、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

5.一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，

再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15 ？

(1)如果表的类型是 MyISAM，那么是 18

因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大

ID 也不会丢失

(2)如果表的类型是 InnoDB，那么是 15

InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行

OPTIMIZE 操作，都会导致最大 ID 丢失

6.请简洁描述 Mysql 中 InnoDB 支持的四种事务隔离级别名称？

四大特性：

⑴ 原子性(Atomicity)

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面

两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应

用到数据库，如果操作失败则不能对数据库有任何影响。

⑵ 一致性(Consistency)

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态

拿转账来说，假设用户 A 和用户 B 两者的钱加起来一共是 5000，那么不管 A和 B 之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是 5000，这就是事务的一致性。

⑶ 隔离性(Isolation)

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一

个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，在事务 T1 看来，

T2 要么在 T1 开始之前就已经结束，要么在 T1 结束之后才开始，这样每个事务都

感觉不到有其他事务在并发地执行。

关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

⑷ 持久性(Durability)

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性

的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

例如我们在使用 JDBC 操作数据库时，在提交事务方法后，提示用户事务操作

完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使

这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我

们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

以上介绍完事务的四大特性(简称 ACID)，现在重点来说明下事务的隔离性，

当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以

保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先

看看如果不考虑事务的隔离性，会发生的几种问题：

1，脏读

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，

这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：

用户 A 向用户 B 转账 100 元，对应 SQL 命令如下

update account set money=money+100 where name=’B’; (此时 A 通知 B)

update account set money=money - 100 where name=’A’;

当只执行第一条 SQL 时，A 通知 B 查看账户，B 发现确实钱已到账(此时即发

生了脏读)，而之后无论第二条 SQL 是否执行，只要该事务不提交，则所有操作都

将回滚，那么当 B 以后再次查看账户时就会发现钱其实并没有转。

2 ，不可重复读

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回

了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

例如事务 T1 在读取某一数据，而事务 T2 立马修改了这个数据并且提交事务给

数据库，事务 T1 再次读取该数据就得到了不同的结果，发送了不可重复读。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数

据，而不可重复读则是读取了前一事务提交的数据。

在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最

后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数

据 A 和 B 依次查询就可能不同，A 和 B 就可能打起来了……

3 ，虚读 (幻读)

幻读是事务非独立执行时发生的一种现象。例如事务 T1 对一个表中所有的行的

某个数据项做了从“1”修改为“2”的操作，这时事务 T2 又对这个表中插入了一行数

据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务 T1 的用户如

果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务 T2 中添

加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同)，所

不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体(比

如数据的个数)。

现在来看看 MySQL 数据库为我们提供的四种隔离级别：

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

③ Read committed (读已提交)：可避免脏读的发生。

④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted

级别，当然级别越高，执行效率就越低。像 Serializable 这样的级别，就是以锁表

的方式(类似于 Java 多线程中的锁)使得其他的线程只能在锁外等待，所以平时选

用何种隔离级别应该根据实际情况。在 MySQL 数据库中默认的隔离级别为Repeatable read (可重复读)。

在 MySQL 数据库中，支持上面四种隔离级别，默认的为 Repeatable read (可重复读)；而在 Oracle 数据库中，只支持 Serializable (串行化)级别和 Read

committed (读已提交)这两种级别，其中默认的为 Read committed 级别

7.你怎么看到为表格定义的所有索引？

索引是通过以下方式为表格定义的：

show index from  表名

8.、LIKE 声明中的％和_是什么意思？

％对应于 0 个或更多字符，_只是 LIKE 语句中的一个字符。

9.MyISAM 表格将在哪里存储，并且还提供其存储格式？

每个 MyISAM 表格以三种格式存储在磁盘上：

·“.frm”文件存储表定义

·数据文件具有“.MYD”(MYData)扩展名

索引文件具有“.MYI”(MYIndex)扩展名

myisam 引擎 ---- frm(定义的表结构) + myd(表中的数据记录) + myi(表中的索引)     (数据与索引分离)

不支事物、外键，优势是访问速度快

innodb 引擎  --- frm(表的创建结构存储) + idb(表中数据和索引的存储)                                       (数据+索引)

是mysql默认的存储引擎，具有事物特征，支持外键，支持自动增长列

10.可以使用多少列创建索引？

任何标准表最多可以创建 16 个索引列。

11.sql和索引做过哪些优化(定位那些sql操作效率低)？

11.1 慢查询日志

可以设置慢查询日志，当sql执行时间超过设定的时间，那么这些sql会被记录在慢查询日志中；通过查看慢查询日志，用explain分析执行计划，判定为何效率低下，没有用到索引还是索引本身创建有问题？或者用到了索引的情况下，由于表数据量大，花费时间 很长，此时可以把表划分成n个小表

11.2 查看磁盘I/0 读写的数据量

任务管理器查看-选择列，检测磁盘读写的数据量，添加了索引的消耗k级别，不添加m级别。

11.3 show status查看运行参数

12.优化总结

1,考虑在where和order by涉及的列上建立索引

2，索引失效：避免使用在where子句中使用or，like条件，会导致mysql放弃使用索引而进行全表扫描;慎用in、not in、!=(如可以用between1 and 3代替in(1,2,3))。避免在where中对字段进行函数操作.

3.索引不是越多越好，可以提高select效率，但降低了insert及update的效率，因为insert和update可能会重建索引，一个表的索引最好不要超过6个。

4.尽可能使用varchar代替char,首先字段存储空间小，节省存储空间，其次在一个相对较小的字段搜索效率显然更高。

5.多表查询时连接查询代替子查询：子查询 select * from student where id in(select id from grade where average>60)会有中间表的产生和销毁，效率低。注：连接查询时，两个表会有大表小表(行数少)之分，小表会做整表查询用不到索引，大表创建索引提高查询效率。但是如果有where条件，那么where条件过滤后的行数少的为小表。联合索引的创建规则：长期用一个非主键寻找另一个非主键，可以选用where条件的字段作为联合主键的左边。(select score from student where classid="01"),创建联合索引classid_index_score