MySQL索引、事务常见的一些问题总结

1、索引是什么？如何分类？

索引是一种快速查找数据的数据结构，在innodb中以.index文件的方式存储在磁盘上；MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。
常见的几种索引是：B树索引、全文索引和hash索引；
1）B树索引是最常用的索引，索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的；
2）Hash索引基于哈希表实现，只要精准匹配索引所有列时生效；对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），适用于精准匹配单向查询；
3）全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。适用于搜索引擎进行一个全文查找；

innodb引擎支持B+树索引，这也是主流索引设计，此外它还有自适应的hash索引设计；
如果按照MySQL使用分类，也可以分为 主键索引、唯一索引、普通索引和联合索引；

2、索引的设计原则？

1、对于数据量比较大的数据表建立索引；
2、索引列的要求，区分度高、不经常更改、最好还应该是非空字段；
3、索引列如果数据量较大采用前缀索引，即只采用部分值；
4、避免过度使用索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长

3、B+树；

B+树是一种特殊的多路平衡查找树；一个B+树的结构一般来说如下图所示，非叶子节点主要存储每一个页面的地址，每个索引所在叶子上；叶子节点按顺序存储数据，比如下图的5，10，12，15…，而且这些叶子之间有一个类似于双向链表的结构；

B+树的查找过程：通过上面的非叶子节点二分查找思想查到叶子节点位置，再做一个叶子上的顺序查询即可查到数据所在位置；

B+树如何插入数据：比较值后进行直接叶子节点的直接追加或者分裂和旋转后在添加；
插入数据必保证整个数据有序，所以需要有一个思想中间值拆分思想和页面容量的概念，如同b树的插入，在b+树的叶子里插入新数据，会先考虑当前数据页是否已满，比如上述的结构中，我想插入一个13，会先查找到12，然后尝试插入12这一页，如果这一页容量可以容下它，就直接插入，如果容量不够，则会尝试旋转或者拆分两种情况。

二叉平衡查找树，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值的平衡二叉树；在这中结构中查找比顺序查找来的快；但是插入新的值需要左右旋保持平衡；且数据量存储越大劣势越大；显然不太适合索引。而B+树相对于B树空间利用率更高，可减少I/O次数，磁盘读写代价更低，B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。且查询效率稳定；

4、索引的存储设计

• 索引以物理文件的方式存储在磁盘上，MyIsam引擎是索引文件.MDI和数据文件.mdb文件,Innodb引擎是索引和数据存在一起的.ibd文件，当数据库查找数据时如果是MyIsam则会先查找索引文件，在从索引文件中去查找数据文件，而innodb引擎会直接通过.idb文件去找到数据并返回。

• 之所以将数据存在一起可以充分利用空间并提高索引存储数量；对于同一个B+树的节点来说，通过

磁盘文件存储是按页存储的，CPU与内存的最小交换单位，即每次IO的次数以页为单位；如果将b+树的节点设计成一个页的整数倍或者一页则可以充分利用IO资源；

Innodb引擎下的各种索引存储方式，默认为B+树

• 主键索引，innodb的主键索引将数据和索引存在一起，通过比较索引大小来进行叶子节点数据的查找，当查找到数据页时进行遍历比对；
• 辅助索引，非主键索引即辅助索引不会进行数据存储，只会存储主键索引，然后通过主键索引进行查找；到主键索引树检索数据的过程称为回表查询；
• 联合索引，联合索引将多个索引作为索引节点存储，即需要比较三个索引才能进一步的查找叶子节点；因为比较索引的方式是从左往右的，所以索引匹配需要满足最左匹配原则；
  最左匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配；

覆盖索引，辅助索引需要回表查询，但如果常用的数据只有几列，可以把这几列纳入组合索引，这样叶子节点的数据就是结果，不需要回表查询；

5、为什么需要使用整形自增作为主键？

为什么UUID不行，首先存储容量上，由于UUID比整形大导致的索引变少，高度变大。因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，导致数据碎片化；
为什么整数行，空间小，方便排序；
为什么需要自增，因为插入数据到索引需要进行比较，从左到右递增，只需要插入到页面后面，而不需要进行一个节点的分裂；

顺序索引的一个缺点：高并发情况下的竞争问题，因为都是在同一个叶子节点下追加；

6、什么时候索引会失效

通过使用explain可以看到sql语句使用索引的情况，有些时候sql语句不会使用索引：

• 对于顺序查找比使用索引快的情况不会会使用索引；如一些无法覆盖的辅助索引来说，MySQL的优化器会采用聚集索引的顺序读来替换掉随机读的辅助索引；举一个常见的例子是：有一场订单表包括自增主键、订单id（辅助索引）和订单信息，如果走非主键索引的订单id会多走一次回表查询，当查询的数据量大一点就会选择采用主键索引来进行查找；如where 订单id>10000这种；范围查找；
• sql语句中的or前后只有部分带有索引时，为了避免不必要的消耗会放弃使用索引，除非or前后都有索引；
• 模糊查询中like 中%开头的语句，因为无法匹配后面的字段，所以索引会失效；
• 索引列的数据格式如果是隐式转换后的，索引也会失效；
• 索引列的数据不能是表达式，否则也会失效；

索引的一些使用上的问题

• 首先索引无法识别表达式，即想要使用索引必须将它作为一个单列；即where num +2 = 0；
• 如果想要索引的字符列比较大，可以通过实现hash索引，或者前缀索引；
• 索引的设计原则应尽量符合，最起码符合排序和可区分行；
• 重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。冗余索引，即建立了组合索引后再为组合里面列电镀建立的索引应避免，除非处于性能考虑；

7、事务是什么，事务的ACID特性

事务是一个SQL执行单元，在事务中的操作不可分割，只能全部都执行或全都不执行；
ACID特性指原子性、一致性、隔离性、持久性；
以银行取钱为例：
原子性：事务不可分割，它的执行是一个原子性操作；即用户取钱的操作不能只执行一半，必须不做或者做完；
一致性：事务操作前后，数据的操作总量是一致的；不能发生改变；即取钱前后账户的存取总数正确，不能这边取了钱，那边没改变；
隔离性：多个事务之间的执行是分割的；事务之间不能相互干扰；且该事务提交前对其他事务都不可见，即多个用户来取钱，不能影响到其他人；
持久性：每次事务提交后都是永久保存的，不会在后面失效；即使发生宕机等故障，数据库也能将数据恢复。

8、事务的分类

• 扁平事务：最普通的事务模型，一次性执行所有，或者一次性回滚所有；
• 带保存点的扁平事务：每一小部分事务执行完毕可以加一个保存点，发生错误时可以可以回滚到任意保存点；
• 链式事务：带有保存点的扁平事务变种，在回滚时只能回滚到上一个保存点；
• 嵌套事务（Nested Transaction）是一个层次结构框架。由一个顶层事务（top-level transaction）控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction），其控制每一个局部的变换。任何子事物都在顶层事务提交后才真正的提交。任意一个事务的回滚会引起它的所有子事务一同回滚；innodb没有默认支持嵌套事务；
• 分布式事务（Distributed Transactions）通常是一个在分布式环境下运行的扁平事务，即一个事务的操作可能涉及多个数据库和用户；

9、事务的实现

事物的实现通常指其四个特性如何实现，对于Innodb引擎，原子性、持久性通过数据库的redolog和undo log来实现，隔离性常通过MySQL中的锁和MVCC实现；其中一致性的实现由其他三个特性来保证；

• 持久性的实现：因为MySQL操纵数据是从bufferpool中获取的，如果没有及时刷新缓存文件到磁盘上，则无法保证持久性，因此引入了redo log来进行磁盘的刷新，通过redo log记录表的操作记录，在从redo log写磁盘文件上；从而保证持久性；

redo log即重做日志，它记录了数据库的所有执行的操作即数据库的行为；由redolog buffer重做日志缓冲和redo log file重做日志文件两部分组成；当事务提交时，必须将该事务的所有日志写入到重做日志文件进行持久化操作，事务提交后持久化结束；因为数据读取每次需要从buffer pool中获取数据，所以为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次缓存刷新操作；即将buffer pool中的缓存文件数据刷新到磁盘；可以通过设置innodb_flush_log_at_trx_commit改变刷新操作提高效率；此外重做日志是物理日志，因此其恢复速度较之二进制日志恢复快得多。例如对于INSERT操作，其记录的是每个页上的变化。redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

原子性的本质是操作不可拆分，通过一次成功全部执行，中途失败全部回滚这样的机制就可以保证一个操作的原子性，即这个操作不会只做一半，回滚靠的主要是undo log日志，它也记录了数据库的更改操作，当事务需要进行回滚时，InnoDB引擎就会调用undo log日志进行SQL语句的撤销，实现数据的回滚。并且对于undolog的操作也会记录到redo log 中；

当事务对数据库进行修改时，InnoDB也会生成对应的undo log；undo log日志undo log属于逻辑日志，它记录的是sql执行相关的信息。当InnoDB存储引擎回滚时，它实际上做的是与先前相反的工作。对于每个INSERT，InnoDB存储引擎会完成一个DELETE；对于每个DELETE，InnoDB存储引擎会执行一个INSERT；对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。

隔离性指多个事务之间不能相互影响，但要实现这个特性比较困难，因为需要保证效率，这就和多线程的问题类似，因此事务根据影响的大小划分了不同的级别如下：

事务隔离级别

事务的隔离级别有读未提交、读已提交、可重复读、和可串行化；

首先隔离级别是为了解决并发状态下的事务问题，常见的问题如下：

1. 更新丢失：两个事务同时处理数据，后提交的事务会覆盖先前事务的更改结果，
2. 脏读：事务A读到了事务B已经修改还没有提交的数据，如果此时B回滚，则A读到的数据无效；
3. 不可重复读：同一事务前后两次重复读取的同一个数据不相同；即事务A读到了事务B提交的数据；
4. 幻读：同一事务读取的数据表发生了新增行；事务A读到了事务B提交的新增操作；

各个事务隔离级别解决的问题

隔离级别越低，事务请求的锁越少或保持锁的时间就越短。
MySQL 默认采用的 REPEATABLE_READ隔离级别，
通过查看tx_isolation属性即可查看表隔离级别；显式地开启一个事务需使用命令BEGIN、START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，rollback回滚；
事务隔离级别的实现
MVCC
MVCC(Multi Version Concurrency Control的简称)，代表多版本并发控制。与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的优势：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

InnoDB中每一行记录都有两个隐藏列：DATA_TRX_ID、DATA_ROLL_PTR（如果没有主键，则还会多一个隐藏的主键列）。即事务id，undo log指针和主键row_id;查到这些数据可以从undo log中匹配事务的操作；

读已提交：每次执行select前都会重新建立一个新的版本号记录，因此如果事务A第一次select之后，事务B对数据进行了修改并提交，那么事务A第二次select时会重新建立新的记录版本号，因此事务B的修改对事务A是可见的。因此读已提交隔离级别可以避免脏读，但是无法避免不可重复读和幻读。
可重复读：在事务开始后第一次执行select前创建版本号及晒，直到事务提交都不会再创建。这样就完全隔离了两个事务之间修改记录，而查询数据时因为存在版本号，可以查到的数据还是未修改前的；实现可重复读；

MySQL默认事务的自动提交，并且也可以设置自动回滚，但最好在程序端进行事务的手动提交，即关闭自动提交，实现手动的事务权限控制；将START TRANSACTION、COMMIT、ROLLBACK操作有程序员来进行控制；

各个隔离级别案例即问题展示：
默认有这样一张表：

事务A和事务B同时开启：
1、事务A查看表一次；

2、事务B并发启动开始并修改一次表记录

3、事务A再次查看表：没有出现脏读现象；

4、事务B再次修改数据并提交；

5、此时事务A查看：
可重复读隔离级别的表现：事务B提交了修改结果，可重复读的隔离级别没有出现问题；

读已提交隔离级别的表现：出现了同一事务中两次读取不一致的问题；

7、重新开启事务c删除一条记录；并提交；

8、在可重复读的隔离级别上在事务A中尝试修改被删除的那一行数据,发现失败了：即出现了幻读现象；