1、MySQL中myisam与innodb的区别

MyISAM：

不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作对整个表加锁；
存储表的总行数；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDb：

支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
不存储总行数；
一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

两者的适用场景：

因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。

如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。

2、索引

普通索引： 没有任何限制条件的索引，该索引可以在任何数据类型中创建。

唯一索引： 使用UNIQUE参数可以设置唯一索引。创建该索引时，索引列的值必须唯一，但允许有空值。通过唯一索引，用户可以快速地定位某条记录，主键索引是一种特殊的唯一索引。

全文索引： 仅可用于 MyISAM 表，针对较大的数据，生成全文索引耗时耗空间。

空间索引： 只能建立在空间数据类型上。这样可以提高系统获取空间数据类型的效率。仅可用于 MyISAM 表，索引的字段不能为空值。使用SPATIAL参数可以设置索引为空间索引。

单列索引： 只对应一个字段的索引。

多列索引： 在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询，想使用该索引，用户必须使用这些字段中的一个字段。

聚集索引，将无序的数据转为有序的B+树，索引主要有B+树和hash两种类型，hash会出现哈希冲突，适合等值查询，不适合范围查询,容易发生冲突。另外还有数组适合做静态存储引擎的索引。对于B+树来说，叶子结点存储的是真实的数据，其他节点都是为了叶子结点数据查找做准备。且是按照值得到小放在叶子结点上，各叶子节点之间通过双向链表进行连接，索引对应的是页 -- B+树索引找到的只是被查找的数据行所在的页，然后数据库通过把页读入内存，再在页中进行查找，找到对应的数据。叶子结点存放的是一整行的数据。每一张表只有一个聚集索引
辅助索引：辅助索引存放的则是对应的主键，其中辅助索引会导致的问题是回表查询
索引的操作： create创建| show 查看| alter 删除

select * from user where age = 28  //先查找辅助索引age,在查找对应的主键索引id
可以创建联合索引，进行覆盖查询
create index age_name on use('age',name);

最左前缀原则查询联合索引的最左边的字段原则则是经常使用的放在最左边，加快查询的速度

缺点：顺序依赖比如 a,b,c 其中a,b则依赖于a,b,c | 如果是b,c则不需要

分析：通过explain语句进行分析
一般什么情况下加索引，什么情况下不加索引？

写操作比较频繁的操作慎重加索引
不要为输出的列添加索引，而是在选择的条件上

select ip_address from t_user_action_log
where name='LiSi'
group by action
order by create_time;

其中在name、action、create_time条件上加，而不是在ip_address

索引下推对于联合索引来说，如果查询中某个列的范围查询，则右边的锁对应的列都无法使用索引进行查询，因此退出索引下推。当使用联合索引的时候，如果左侧索引存在范围查询，则一边使用索引进行查询，一边where过滤

3、事务（对应的InNoDB引擎才有事务）

事务的四个特性：ACID 原子性，持久性、一致性、隔离性

并发访问的时候，将会导致以下的问题，脏读，不可重复读（数据的修改）、幻读（数据的增加或减少）

基于以上的问题，提出了四种的隔离级别：未提交读，提交读、可重复读、可串行化。对应的事务的隔离级别越高，并发一致性的问题就越少，但是数据库付出的代价就越高

四种隔离级别的具体的实现：MVCC（版本一致性），其中MVCC包括undolog和readview

Undolog 版本链，记录事务如何回到之前视图记录的状态

readview 视图对于某一个事务来说，能看见哪些undolog

3.1 隔离级别的具体实现（MVCC），主要是已提交读和可重复读

对于已提交读的情况下，视图是在每个SQL语句下进行创建的
在可重复读的情况下，视图是在事务启动的时候就创建，即一个事务在启动的执行过程中数据是一致的

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。

1.已经执行完那么多操作，如何回到视图记录的状态？
使用undolog版本连，InnoDB根据undolog得到数据的历史版本，同一条记录在系统中存在多个版本，就是数据库中的多版本并发控制
2. 每条记录如何和undolog进行关联呢？
有两个字段，最新更新事务的ID，之前生成的undolog的指针，将undolog进行串联起来 3.一个记录被一堆事务进行修改，一个记录就会有很多的undolog，怎么保证事务能看见哪些undolog？
ReadView机制就是用来判断当前事务能够看到哪些版本

通过undolog版本链，和readview视图机制，可以保证一个事务不会读到并发执行的另一个事务的更新，但可以读到事务自己修改的数据或事务之前的数据

事务的来讲主要是针对不可重复读的隔离级别，在事务一开始的时候生成视图来控制能读到的版本连。这时的MVCC包括readview和undolog来实现提交读和可重复读的隔离级别

对应的幻读的实现，可利用可串行化实现（更高的隔离级别），每次操作都进行加锁
对于低的隔离级别，不可重复读，使用MVCC版本控制进行实现，分为快照读和当前读。对于快照读，则使用MVCC就可以。对于当前读，使用MVCC加邻键锁

四、锁机制

INNODB随时都可以进行加锁，但不是随时进行解锁，采用两阶段所协议，只有在commit和rollback时才可以进行解锁

加锁：意向锁表锁行锁（基于索引的，因为索引就是一行行的数据）

INNODB存储引擎行锁的三种算法分为一个索引和多个索引

Record Lock：记录锁封锁该行的唯一索引记录加for update
next-key Lock：邻键锁
Gap lock:间隙锁基于非唯一索引，是一段范围内索引进行加锁 bettwen 1 and 10 for update

五、日志主要是redo log 和 bin log日志还有undolog日志进行实现MVCC，回滚

binlog InnoDB存储引擎持有，记录DB执行的写入性操作，以二进制的形式保存在磁盘中

写入的方式：追加  类似于redis中的AOF

//待补

补：问题慢查询的优化

分析语句：是否加载了额外的数据
explain获得索引的使用情况，使语句可能命中更多的索引
是否数据量过大，是否分库分表

java面试10-MySQL