MySQL事务与索引

---

前言

一、事务

定义：一组操作的集合，把这个集合看做一个整体，执行时要么操作全部成功，要么全部失败
默认的事务是一句执行语句，以分号为标识

事务的语法

#也可使用begin
start transaction;
#提交事务，结束
commit;
#回滚事务
rollback;
 

事务的四大特性

（ACID）

• 原子性:开启事务到提交事务的这一阶段中的所有操作看做一个整体，是不可分隔的最小单元，要么全部成功，要么全部失败
• 一致性：指的是事务完成后的数据总数和事务完成前的总数是一致的
• 隔离性：事务不受外部并发操作的影响（在事务未提交之前，并发操作查看数据是原数据，事务中的会话可以看到数据改变，是因为吧它放在一个日志缓存中，还没有真正的持久化）
• 持久性： 事务一旦提交后数据库的该表是永久修改的

并发下的四个隔离级别

并发下可能造成的现象：

• 脏读： 事务A对数据进行了更新，事务B读取了事务A更新后的数据，但是A由于某种原因并没有进行提交，进行了回滚，这种情况就是脏读
• 脏写 ：事务A更新了数据，事务B读到被更新的数据，然后再进行了修改,但是事务A进行了回滚，事务写的就无效了，这种情况就是脏写
• 不可重复读：事务A在进行的过程进行了两次数据的读取，但是在两次读取的过程中有B事务对数据值进行了修改，这时候两次读取的数据值就不一样这样的情况就叫可不重复读
• 幻读：事务B在读取两次的过程，A对数据集合进行了增加删除，导致读取的项少了或者多了，这种情况就是幻读，幻读侧重在读取的集合中的项的多少，不可重复读侧重在于数据值发生了改变
  解决的四种隔离级别 ：
  读未提交：解决脏写，事务进行更新后，还未提交，其他事务就能进行读取，不管事务是否会执行成功，但是不能进行写
  读已提交：解决脏读某行记录被事务写过，要等事务提交之后才能进行读取
  可重复读：解决脏读，不可重复读是指当事务A进行修改前，会复制一份（相当于视图），无论当前事务修改执行到哪儿，所有其他事务的读操作都是从这个复制表中进行查询，而其他事务要进行写操作时却是读取的数据却是事务A执行到当前位置下的数据
  串行化：解决脏读，不可重复读，幻读在串行化下，读写都会被锁定，只有事务完成后，其他事务才能对这块数据进行操作

二、索引

高效获取数据的数据结构：

有hash,B+,full-text索引
	默认为B+树索引
		当查询字段没有索引的时候会全局顺序查找对比

分类

按类型分类

• 主键索引：当我们定义主键约束的时候，会自动创建主键索引（且唯一（因为主键关联存储数据））
• 唯一索引：当字段定义唯一约束的时候，会自动创建唯一索引（可以创建多个）
• 常规索引：手动创建索引（可以创建多个）
• 全文索引：一般根据full-text这种数据结构创建索引

按数据存储方式分

• 聚集索引：将数据存储与索引放在一起，叶子节点下面保存一行的数据（有且只有一个）（数据存储的方式时基于索引的，有类似的索引组织表来对整体索引结构进行描述）
  当一个表中没有主键索引和唯一索引的时候我们的默认引擎innodb会自动给表分配一行为id的字段，但是看不见，为隐藏索引，然后进行存储

• 二级索引 ：就是索引的叶子节点没有存储数据，它存储的是对应的主键，所以二级索引是依据聚集索引进行查询

• 成为聚集索引的优先级： 主键索引>唯一索引>(隐藏索引)

InnoDB下默认的索引结构B+树

B树 B+树 二叉平衡树 对比

• 二叉平衡树：一个节点下只能有两个以下分支，当数据过多的时候会造成树的高度过高，查询也会造成不便

• B树（多路平衡二叉树）：它可以在一个节点下有多个分支，当限定一个节点有n个数据后，那它可以成为n+1一个节点的父节点，这样可以使得树的高度大大下降，但是在进行排序的时候，由于树的结构每个节点都需要遍历，才能进行排序。

• B+树:是B树的变种，它只在叶子节点层存储数据（相当于上层所有节点为底层的索引，是不存储数据的，只存储相应的索引值，当一个节点的大小被限定的时候，上层节点能够存储更多的索引值，能更加有效的降低树的高度，由于上层没有数据，在叶子节点一层，看起来是成线性排列，天然的变得有序）且B+在叶子节点层有从左到右的指针指向，能够更好的进行排序获取值。

• 而在mysql中的B+还增加了一个从右到左的指针，变成了一个双向链表，能够满足正序排列和降序排列

索引的语法

create index 索引名（主键索引fk_s字段名，一般索引idx_字段名）on 表名（字段名）（当字段名为多个的时候为联合索引）
show index from 表名
drop index 索引名 on 表名

B+树一般多高

mysql的节点默认大小为16kb 索引指指针占6个字节  主键为bigint 占8个字节 ，一行数据占1k 当为一层的时候
	索引节点可以有一千多个，两层就有一万多个，三层就千万级别了

mysql常见存储引擎

• InnoDB存储引擎 InnoDB是MySQL的默认事务型引擎，也是最重要、使⽤最⼴泛的存储引擎。 它被设计⽤来处理⼤量的短期(short-lived)事务，应该优先考虑InnoDB引擎。
  MylSAM存储引擎 在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。 但是MyISAM不⽀持事务和⾏级锁，⽽且崩溃后⽆法安全恢复。 同时MyISAM对整张表加锁，很容易因为表锁的问题导致典型的的性能问题。

• Memory 引擎 Memory表⾄少⽐MyISAM 表要快⼀个数量级，数据⽂件是存储在内存中。 Memory表的结构在重启以后还会保留，但数据会丢失。 Memroy表在很多场景可以发挥好的作⽤: ⽤于查找(lookup）或者映射(mapping）表，例如将邮编和州名映射的表。 ⽤于缓存周期性聚合数据(periodically aggregated data)的结果。 ⽤于保存数据分析中产⽣的中间数据。

• Archive引擎 Archive存储引擎只⽀持INSERT和SELECT操作，会缓存所有的写并利⽤zlib对插⼊的⾏进⾏压缩，所以 ⽐MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执⾏全表扫描。 所以Archive表适合⽇志和数据采集类应⽤。

• CSV引擎 CSV引擎可以将普通的CSV⽂件(逗号分割值的⽂件）作为MySQL 的表来处理，但这种表不⽀持索引。 因此CSV引擎可以作为⼀种数据交换的机制，⾮常有⽤。

innoDB与MyLSAM的区别

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 的重要原因之⼀；

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。