Java面试 Day3 - InnoDB和MyISAM区别和数据库索引

InnoDB和MyISAM区别

一、数据库事务简介

要讲解InnoDB，MyISAM 首先一定要知道数据库事务！

==数据库事务( transaction)==是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

事务特性（ACID）

A: Atomicity, 原子性：事务是最小的操作序列单元，一个事务中包含的所有操作在一次执行后要么全部操作成功，要么全部操作失败，也就是说如果事务执行过程中出错，那么就会回滚到事务开始前的状态

C: Consistency, 一致性：指事务的执行不能破坏数据库数据的完整性和一致性，例如A向B转账，如果事务中只给B的账户增加了余额而A的余额不变，那么就破坏了数据的一致性

I: Isolation, 隔离性：不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间，不应互相干扰

D: Duration, 持久性：事务一旦提交，对数据的修改便被永久保留

例：打个很简单的比方，我辰兮购买CSDN会员花费300元

整体执行算是一个事务，首先我的余额减少300，然后CSDN账户余额增加300
如果我支付成功这里我的账户余额就会少，但是如果这时候突然出现异常情况，这时候CSDN账户没有增加300，那么这个业务就算是失败的，事务的原子性和一致性就提到要不全部执行，要不就不执行，如果执行一半事务一致性也会破坏

例：常见比如你要删除一个用户的信息，肯定是删除用户表信息，用户详情表信息

删除相关信息就要删除的彻彻底底，你执行一个事务就要将这个事务执行完整，业务逻辑也要符合实际场景,这才合情合理

总结：事务是由一系列对数据的访问与更新操作组成的程序执行逻辑单元

二、InnoDB 和 MyISAM

面试中也经常考到InnoDB和MyISAM区别？

1、InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

2、 InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

3、InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4、 InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5、InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

注意：目前 MySQL 将默认存储引擎 InnoDB，支持事务，支持行锁支持外键

根据实际场景选择不同的数据库引擎：

1.是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；

2.如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3.系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

4.MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。

根据自己实际的场景选择不同的数据库即可，如果你不知道用什么存储引擎，那就用InnoDB吧，毕竟是mysql目前默认引擎。

三、MYSQL引擎简介

上面的InnoDB和MyISAM都是MYSQL引擎，我们是否真正了解过引擎概念呢？

简介一下MYSQL引擎相关知识：

MYSQL存储数据时，有不同的存储方式，这些方式都使用了不同的底层实现（如：存储机制，索引技巧，索引技巧，锁定水平），底层实现的差异带来的功能也就不同。在不同的场景下使用合适的存储方式就能让你的数据读写速度更快或者获得额外的功能。

MYSQL主要有什么存储引擎？

主要的有： MyIsam , InnoDB, Mrg_Myisam, Memory, Blackhole, CSV, Performance_Schema, Archive, Federated

数据库查询语句中可用通过 show engines 查看你的MYSQL的存储引擎

拓展：【数据库】Mysql更改默认引擎为Innodb的步骤方法

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。

基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。

我为什么要改默认引擎，其实原因很简单，我使用的Mysql默认引擎是MyISAM，而MyISAM并不支持事务处理，所以我需要更换它的引擎。

更改 Mysql 默认引擎为 InnoDB步骤如下：

Mysql默认是关闭InnoDB存储引擎的使用的，将InnoDB设置为默认的引擎如下。

1. 查看Mysql存储引擎情况： mysql>show engines InnoDB | YES，说明此Mysql数据库服务器支持InnoDB引擎。

2. 设置InnoDB为默认引擎：在配置文件my.ini中的 [mysqld] 下面加入default-storage-engine=INNODB

3. 重启Mysql服务器

4. 登录Mysql数据库，mysql>show engines 如果出现 InnoDB |DEFAULT，则表示设置InnoDB为默认引擎成功。

Mysql索引相关面试题

1.什么是索引

• 官方定义：一种帮助mysql 提高查询效率的数据结构
• 索引数据结构
• 索引的优点
  1. 大大加快数据查询速度
• 索引的缺点
  1. 维护索引需要耗费数据库资源
  2. 索引需要占用磁盘空间
  3. 对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

2.索引分类

• a.主键索引

  设定为主键后数据库会自动建立索引,innodb为聚簇索引

• b.单值索引

  即一个索引只包含单个列,一个表可以有多个单列索引

• c.唯一索引

  索引的值必须唯一，但允许有空值

• d.复合索引

  即一个索引包含多个列

• e.Full Text 全文索引(my5.7版本之前 只能用MYISAM引擎)

  全文索引类型为FULL TEXT,在定义索引的列上支持值的全文查找，允许这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

3.索引的基本操作

1. 主键索引 自动创建

   – 主键索引是在建表时自动创建

2. 单列索引(普通索引 单值索引)

   -- 建表时创建
   create table t_user(id varchar(20) primary key,name varchar(20),key(name))
   -- 建表后创建
   create index name_index on t_user(name)
   -- 删除索引
   dorp index 索引名 on 表名
   

3. 唯一索引

   -- 建表时创建
   create table t_user(id varchar(20) primary key,name varchar(20),unique(name))
   
   -- 建表后创建
   create unique index name_inedx on t_user(name)
   

4. 复合索引

   -- 建表时创建
   create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age))
   
   -- 建表后创建
   create index nameage_index on t_user(name,age)
   

   # 经典面试题
   - name age bir 
   #1.最左前缀原则
   #2.mysq1引擎在查询为了更了更好利用索引在查询过程中会动态调整查询字段顺序以便利用索引
   

4.索引的底层原理

-- 建立一个新表
drop table if exists t_emp;
create table t_emp(id int primary key,name varchar(20),age int);

-- 随机插入一些数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
show index from t_emp;
select * from t_emp;

上面数据明明没有按顺序插入,查询时却是有顺序

1. 原因是：MySQL底层为主键自动创建索引,一定创建索引会进行排序；也就是MySQL底层真正存储是这样的；

2. 为什么要排序呢?

   因为排序之后在查询就相对比较快了，如查询 id=3的我只需要按照顺序找到3就行啦

   (如果没有排序大海捞针,全靠运气!)

为了进一步提高效率MySQL索引又进行了优化

当上面的那条链足够大足够长的时候，就会导致查询的时间过长，时间复杂度过高，效率极低，针对此，MySQL基于页的形式进行管理索引，如查询id=4的直接先比较页，先去页目录中找，再去数据目录中找；

B+树数据结构

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构，这里不多讲解，只提一句，如果按照我们上面的建表结构，三层B+树大概能够存储近10亿条数据；

聚簇索引和非聚簇索引

聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

注意:在InnoDB中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似Oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可

常见问题

聚簇索引和非聚簇索引区别：

• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
• 聚集索引:物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
• 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

使用聚簇索引的优势

问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快
2. 辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小

聚簇索引需要注意什么

1. 当使用主键为聚簇索引时，主键最好不要使用uuid（雪花id），因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源
2. 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量

为什么主键通常建议使用自增id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高

什么情况下无法利用索引

1. 查询语句中使用LIKE关键字：在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用；
2. 查询语句中使用多列索引：多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用；
3. 查询语句中使用OR关键字：查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引；

B树和B+树的区别

B树
每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

B+树

只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。

所以，B+树在B树的基础上做了优化，它与B树的差异在于：

（1）有 k 个子节点的节点必然有 k 个key；
（2）非叶子节点仅具有索引作用，跟记录有关的信息均存放在叶子节点中。
（3）树的所有叶子节点构成一个有序链表，可以按照key排序的次序遍历全部记录。

怎么判断mysql中sql语句索引是否生效

explain显示了MySQL如何使用索引来处理select语句以及连接表。他可以帮助选择更好的索引和写出更优化的查询语句

explain显示了很多列，各个关键字的含义如下：

table：顾名思义，显示这一行的数据是关于哪张表的；

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：const、eq_reg、ref、range、indexhe和ALL；

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句；

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在Select语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MySQL忽略索引；

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好；

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数；

rows：MySQL认为必须检查的用来返回请求数据的行数；

Extra：关于MySQL如何解析查询的额外信息。

索引隔离级别

四种隔离级别：
SERIALIZABLE：最严格的级别，事务串行执行，资源消耗最大；
REPEATABLE READ：保证了一个事务不会修改已经由另一个事务读取但未提交（回滚）的数据。避免了“脏读取”和“不可重复读取”的情况，但是带来了更多的性能损失。
READ COMMITTED：大多数主流数据库的默认事务等级，保证了一个事务不会读到另一个并行事务已修改但未提交的数据，避免了“脏读取”。该级别适用于大多数系统。
READ UNCOMMITED：保证了读取过程中不会读取到非法数据。

上面的解释其实每个定义都有一些拗口，其中涉及到几个术语：脏读、不可重复读、幻读。这里解释一下：

• 串行：一般是不会使用，会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题
• 可重复读：这是mysql的默认级别，就是每次读取结果都一样，但是有可能造成幻读
• 读已提交：两次读取结果不一致，叫做不可重复读。
  不可重复读解决了脏读的问题，他只会读取已经提交的事务
• 读未提交：可能会读取到其他事务未提交的数据，也叫作脏读

脏读：某一个事务已经更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个执行Rollback操作，则后一个事务所读取的数据就会是不正确的。
不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据
幻读：在一个事务中的两次查询结果数据笔数不一致。这可能是两次查询过程中间插入了一个事务新增了几条数据