mysql常见面试准备（不涉及底层）

索引的基本原理

索引用来快速的寻找那些具有特定值的记录。如果没有索引，一般来说查询时遍历整张表
索引的原理：就是把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序
2. 把排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出地址链，从而拿到具体数据

mysql聚簇索引和非聚簇索引的区别

都是B+树的数据结构
通过对数据进行排序提高查询速度，一个节点可以存放多个元素，叶子节点有指针

• 聚簇索引：将数据存储和索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序和索引的顺序是一致的，既：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的
• 非聚簇索引：叶子上节点不存储数据、存储的数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本树的目录，比如我们要找的第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看章节。

优势：

1. 查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率更高
2. 聚簇索引对于范围查询的效率更高，因为其数据是按照大小排序的
3. 聚簇索引适合用在排序的场合

劣势：

1. 维护索引很昂贵，特别是插入新行或者主键被更新导致要分页（page split）的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPYIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片使用独享表空间可以弱化碎片
2. 表因为使用UUID作为主键，使数据存储稀疏，这就会出现聚簇索引有可能比全表扫描更慢，所以建议使用int的auto_increment作为主键
3. 如果主键比较大的话，那辅助索引会变得更大，因为辅助索引的叶子存储的是主键值：过长的主键值，会导致非叶子节点占用更多的物理空间

InnoDB中一定有主键，主键一定是聚簇索引，不手动设置，则会使用unique唯一索引，没有unique索引，则会使用数据库内部的一个行的隐藏id来当做主键索引。在聚簇索引之上创建的索引叫做辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引、辅助索引叶子节点存储的不再是行的物理位置，而是主键值

MYISAM使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两颗B+树看上去没有什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助索引B+树存储了辅助健。表数据存储在独立的地方，这两颗B+树的叶子节点都使用了一个地址指向了真正的表数据，对于表数据来说这两个健没有任何的差别。由于索引树是独立的，通过辅助健检索无需访问逐渐的索引树。

如果涉及到大量的数据排序、全表扫描、count之类的操作的话，还是MyISAM优势些，因为索引所占空间小，这些操作是需要在内存中完成的

mysql索引的数据结构，各自优劣

索引的数据结构和具体存储引擎实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，InnoDB存储引擎默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能块，其余大部分场景选择B+树

B+树
B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度不超过1，而且同层级的节点有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅度波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

哈希索引

1. 如果是等值查询，那么哈希索引有绝对的优势；前提是键值都是唯一的，如果不唯一，那么就需要找到该健的位置，然后再根据链表往后扫描，查找到相应的数据。
2. 如果是范围查询哈希索引不占优势，哈希索引没办法完成排序，原先有序的键值，经过哈希算法后，有可能变成不连续的了
3. 哈希索引也不支持多列联合索引的最左匹配原则

索引的设计原则

查询更快、占用空间小
常用的索引有：普通索引、全文索引、联合索引、唯一索引

mysql锁的类型有哪些

基于锁的属性分类：共享锁、排他锁
基于锁的粒度分类：行级锁、表级锁、记录锁、间隙锁、临健锁
基于锁的状态分类：意向共享锁、意向排他锁

mysql执行怎么看

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回结果集的行数
EXPLAIN SELECT * from emp;

1. id：是一个有顺序的编号，是查询的顺序号，有几个select就显示几行。id的顺序是按select出现的顺序增长的。id列的值越大执行的优先级别越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。
2. select_type 表示查询中每个select子句的类型
3. table：表示该语句查询的表
4. type：优化sql的重要字段，也是判断sql性能和优化程度重要指标。

const: 通过索引一次命中，匹配一行数据
system：表中只有一行数据，相当于系统表
eq_ref：唯一性索引扫描，对于每个索引健，表中只有一条记录与之匹配
ref：非唯一性索引扫描，返回匹配某个值所有
range：只检索给定范围的行，使用一个索引来选择行，一般用between <> 可能有多个索引
index: 只遍历索引树
ALL：表示扫描全表，这个类型的查询是性能最差的查询之一。
执行效率：ALL<index<range<ref<eq_ref<const<system。最好是避免ALL和index
prossible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能实际上不一定会用到
key：此字段是mysql在当前查询时真正使用的索引

事务的基本特性和隔离级别

基本特性

1. 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

隔离级别

1. READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED(读取未提交)	√	√	√
READ-COMMITTED (读取已提交)	×	√	√
REPEATABLE-READ (可重复读)	×	×	√
SERIALIZABLE (可串行化)	×	×	×

并发事务带来哪些问题

1. 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
2. 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
3. 不可重复读（Unrepeatable read）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
4. 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读

MySQL 的默认隔离级别是什么?

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

事务的实现原理

我们这里以 MySQL 的 InnoDB 引擎为例来简单说一下。
MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。
保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

mysql中MyISAM和InnoDB的区别

MyISAM：

1. 不支持事务，但是每次查询都是原子的
2. 只支持表级锁，即每次操作是对整个表加锁
3. 不支持外键，
4. 不支持数据库在异常崩溃后的安全恢复
5. 不支持MVCC

InnoDB：

1. 支持事务，具有提交和回滚的能力
2. 支持表级锁和行级锁，默认情况下是行级锁
3. 支持外键
4. 支持数据库在异常崩溃后的安全恢复
5. 支持MVCC

锁机制与 InnoDB 锁算法

MyISAM 和 InnoDB 存储引擎使用的锁：

1. MyISAM 采用表级锁(table-level locking)。 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁
2. 表级锁和行级锁对比：
   • 表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
   • 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
3. InnoDB 存储引擎的锁的算法有三种：
   • Record lock：记录锁，单个行记录上的锁 Gap lock：
   • 间隙锁，锁定一个范围，不包括记录本身 Next-key lock：
   • record+gap 临键锁，锁定一个范围，包含记录本身 # 查询缓存

MVCC解决的问题是什么

MVCC是一个多版本并发控制，即多个不同版本实现并发控制的技术，其基本思想是为每次事务生成一个新版本的数据，在读数据时选择不同版本的数据即可实现对事物结果的完整性读取

MVCC是用来解决读写冲突的无锁并发控制

1. 在并发读写数据时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
2. 解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题