- InnoDB和MyISAM区别
- Mysql索引相关面试题
一、数据库事务简介
要讲解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设置为默认的引擎如下。
-
查看Mysql存储引擎情况： mysql>show engines InnoDB | YES，说明此Mysql数据库服务器支持InnoDB引擎。
-
设置InnoDB为默认引擎：在配置文件my.ini中的 [mysqld] 下面加入default-storage-engine=INNODB
-
重启Mysql服务器
-
登录Mysql数据库，mysql>show engines 如果出现 InnoDB |DEFAULT，则表示设置InnoDB为默认引擎成功。
1.什么是索引
- 官方定义：一种帮助mysql 提高查询效率的数据结构
- 索引数据结构
- 索引的优点
- 大大加快数据查询速度
- 索引的缺点
- 维护索引需要耗费数据库资源
- 索引需要占用磁盘空间
- 对表的数据进行增删改的时候，因为要维护索引，速度会受到影响
2.索引分类
-
a.主键索引
设定为主键后数据库会自动建立索引,innodb为聚簇索引
-
b.单值索引
即一个索引只包含单个列,一个表可以有多个单列索引
-
c.唯一索引
索引的值必须唯一，但允许有空值
-
d.复合索引
即一个索引包含多个列
-
e.Full Text 全文索引(my5.7版本之前 只能用MYISAM引擎)
全文索引类型为FULL TEXT,在定义索引的列上支持值的全文查找，允许这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引
3.索引的基本操作
-
主键索引 自动创建
– 主键索引是在建表时自动创建
-
单列索引(普通索引 单值索引)
-- 建表时创建 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 表名
-
唯一索引
-- 建表时创建 create table t_user(id varchar(20) primary key,name varchar(20),unique(name)) -- 建表后创建 create unique index name_inedx on t_user(name)
-
复合索引
-- 建表时创建 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;
上面数据明明没有按顺序插入,查询时却是有顺序
-
原因是：MySQL底层为主键自动创建索引,一定创建索引会进行排序；也就是MySQL底层真正存储是这样的；
-
为什么要排序呢?
因为排序之后在查询就相对比较快了，如查询 id=3的我只需要按照顺序找到3就行啦
(如果没有排序大海捞针,全靠运气!)
为了进一步提高效率MySQL索引又进行了优化
当上面的那条链足够大足够长的时候，就会导致查询的时间过长，时间复杂度过高，效率极低，针对此，MySQL基于页的形式进行管理索引，如查询id=4的直接先比较页，先去页目录中找，再去数据目录中找；
B+树数据结构
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gvH1G2GX-1634042219885)(C:UsersChef LiuAppDataRoamingTyporatypora-user-imagesimage-20211012202944364.png)]
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构，这里不多讲解，只提一句，如果按照我们上面的建表结构，三层B+树大概能够存储近10亿条数据；
聚簇索引和非聚簇索引
聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
注意:在InnoDB中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似Oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可
常见问题
使用聚簇索引的优势
问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？
- 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快
- 辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小
聚簇索引需要注意什么
- 当使用主键为聚簇索引时，主键最好不要使用uuid（雪花id），因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源
- 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量
为什么主键通常建议使用自增id
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高
什么情况下无法利用索引
- 查询语句中使用LIKE关键字：在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用；
- 查询语句中使用多列索引：多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用；
- 查询语句中使用OR关键字：查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引；
