这篇博客，就写一下一些理论知识吧~
MySQL 索引 与 事务
索引
作用、指令、原理、不适用场景
作用：类似于一本书的目录，提高查询效率
指令：show index from tbname; 或 show index from tbname\G;
原理：给一张表创建一个索引，相当于又创建了一个索引表。因此索引并不是适用于所有的字段，不是越多越好。 因为索引多了，会影响插入和删除的效率。
不适用场景：
- 不经常作为条件用于查询依据的字段不适合
- 字段修改频率较低的字段不适合
- 索引会额外占据磁盘空间
索引是如何提高查询效率的
索引是如何提高查询效率的：数据结构。
索引采用不同的数据结构组织可以提高检索效率（尽快找到索引项，通过索引项找到实际数据的存储位置，然后取出数据）。
B树 与 B+树
B树：是一个多叉树，每一层的个数不再是2的整数次幂，比2的整数次幂要多。
B+树：也是一个多叉树。
差别：
-
B树的数据与索引存储在一起；
-
B+树数据与索引分离，一次性可以从磁盘中读取出更多索引信息，更利于索引检索；
-
B+树数据顺序存储，所存节点中会包含指向下一节点的指针，在连续查询或范围查询时可以连续IO取出数据！效率较高
-
B树节点，既有数据，又有Page指针，而B+，只有叶子节点有数据，其他目录页，只有键值和Page指针； B+叶子节点，全部相连，而B没有。
为何选择B+：
节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
叶子节点相连，更便于进行范围查找，降低树的高度来提高查询效率。
注：innodb使用的是B+树作为索引类型
聚簇索引 / 非聚簇索引
聚簇索引 / 非聚簇索引：都采用B+树。
聚簇索引：以主键作为主索引，数据节点在磁盘中顺序进行存储，其他的索引作为辅助索引，其中辅助索引保存的是主键索引得字段值。索引与数据存储顺序一致，一张表中只能有一个聚簇索引。
非聚簇索引：主键索引与普通索引区别不大，都是最终索引项中存储数据在磁盘中的存放位置，数据节点在磁盘中并非顺序存储。索引与数据存储顺序不一定一致，一张表中可以有多个非聚簇索引。
使用场景：
-
聚簇索引：索引与数据都是顺序存储的，当在中间插入时很难受，即中间插入/删除需要调整索引的存储结构；
聚簇索引的数据都是顺序存储的，所以连续/范围查询的时候效率较高；
一张表中只能有一个聚簇索引，通常聚簇索引是针对主键进行创建的。 -
非聚簇索引：索引与数据存储顺序不一定一致，其中间插入数据，只需要将数据存储到磁盘新的位置，中间调整索引信息即可；
因为索引与数据存储顺序不一致，导致在范围查询时，与单个查询效率没有差别都需要一个个去找出来；
一张表中可以有多个非聚簇索引。
数据库操作中的索引类型
索引类型
主键索引 PRI：一个字段被设置为主键，则默认就会为主键字段创建主键索引；
唯一键索引 UNI：一个字段被设置了唯一约束，则默认创建唯一键索引；
外键索引 MUL：一个字段被设为外键，也会默认创建外键索引；
普通索引：并非默认创建的索引。
索引特点
-
主键索引的特点：
一个表中，最多有一个主键索引，当然可以使符合主键；
主键索引的效率高（主键不可重复）；
创建主键索引的列，它的值不能为null，且不能重复；
主键索引的列基本上是int。 -
唯一索引的特点：
一个表中，可以有多个唯一索引；
查询效率高；
如果在某一列建立唯一索引，必须保证这列不能有重复数据；
如果一个唯一索引上指定not null，等价于主键索引。 -
普通索引的特点：
一个表中可以有多个普通索引，普通索引在实际开发中用的比较多；
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。 -
全文索引的创建
当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是 MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。
索引创建原则
- 比较频繁作为查询条件的字段应该创建索引；
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
- 更新非常频繁的字段不适合作创建索引；
- 不会出现在where子句中的字段不该创建索引。
相关指令
查看索引
show index from tbname; 或 show index from tbname\G;
创建索引
对于非主键、非唯一约束、非外键的字段，可以创建普通索引
create index 索引名 on 表名(字段名);
如
create index idx_classes_name on classes(name);
删除索引
drop index 索引名 on 表名;
如
drop index idx_classes_name on classes;
（注意：innodb索引类型默认是聚簇索引，中间数据的插入与删除会涉及索引与数据的位置调整，因此大多数情况都是使用自增主键为聚簇索引，这样的话数据与索引总是在最后添加，而不涉及中间插入的调整。）
事务
概念、特性
概念：一个或多个sql的组合
特性：
-
原子性：一个事务要么一次完成要么一个都不做。
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。 -
一致性：在事务前与事务后，数据完整性都要符合预设规则，依赖原子性。
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。 -
持久性：事务之后，数据的修改是永久的（持久化存储）。
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。 -
隔离性：允许多个事务并发执行，不会因为交叉执行导致数据不一致。
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化（ Serializable ）。
异常情况
并发情况下，若无隔离性保护，则有可能出现的情况：
-
脏写：事务A对数据的修改，在事务提交之前被其他事务覆盖。
如果两个事务并发执行，修改同一条数据，两个事务都还没提交，第一个事务正常执行修改，但是第二个事务却想要回滚，此时由于undo log日志中存放的是一开始的数据记录，那么第二个事务回滚就会把这条数据回滚为最初的数据值，导致第一个事务的修改无效。这就是脏写了。
但由于写的时候一般是会加锁的，所以基本不讨论它。 -
脏读：一个事务中读取到的数据是其他事务中未提交的数据。
一个事务在执行中，读到另一个执行中事务的更新(或其他操作)但是未commit（提交）的数据。 -
不可重复读：在一个事务的不同阶段所读取的数据不一致。
针对：update -
幻读：在一个事务内的不同时间段读取到的数据条数不一致。
针对：insert / delete
解决方案
解决方案：从事务的隔离性下手， 四种隔离级别：
-
读未提交：有可能出现脏读、不可重复读、幻读，不加锁；
-
读提交：可解决脏读问题，但有可能会有不可重复读、幻读的问题，不加锁；
-
可重复读：可解决脏读、不可重复读，同时结合 MVCC多版本并发控制 可解决幻读问题，不加锁
MySql的默认级别是可重复读级别 ！！！！ -
串行化：对数据表进行加锁操作，可解决脏读、不可重复读、幻读等问题，但其不可并发处理，效率低下！
多版本并发控制
MVCC：多版本并发控制
就是给每个事务分配一个事务id 。
事务中对数据进行操作时，都临时拷贝出一份数据进行操作，
在本次事务对数据再次进行访问时访问的都是这个指定事务 id 的临时拷贝，其他事务访问时是未修改的原数据，
事务提交时将事务操作持久化存储。
以上为本篇博客内容，大多是理论知识，有些枯燥~
侵权删~
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
