Mysql 索引和事务

1 索引 相当于 一本书的目录: 背景:帮助我们 快速找到想要的记录, 避免出现 顺序遍历.

(1) 使用 哈希 作为索引 (使用的不多), 无法解决模糊匹配问题. 而且 InnoDB 引擎不支持哈希表索引. 但是和B+树索引比, 哈希索引的 查找速度更快, 结构更简单.
(2) 使用 二叉搜索树 作为索引, 但这样会导致在数据量非常大的情况下, 二叉搜索树的深度过深, 在进行搜索的时候, 自根节点向下搜索需要访问的叶子结点也会非常多, 这就大大降低了查询的效率.

2 先看 B树(B-树) 每个节点可以存储多个数据, 多个数据就划分了一定的"区间",进一步的数据结合着这样的区间来摆放, 高度更低, 查找效率更高, 完全体就是B+树.

3 索引使用场景.
(1) 那些 经常用来作为 sql语句的条件 的字段上.
(2) 数据库表 查询的次数远远高于 修改和删除的次数. (索引只是避免进行遍历, 优化了查询速度, 会降低插入和删除速度, 实际工作中查询的场景非常多)
(3) 表中的 行数足够大.

4 工作原理. 底层数据结构是 B+树:

B树中非叶子节点 可能存储数据. B+树数据一定在叶子节点上, 非叶子节点 只是用来进行 辅助查找. 每一层兄弟节点之间都是 相互连通的(类似于链表), 这使得 遍历起来更加方便, 尤其是 指定区间 进行查找的时候. B+树优点:

和哈希表相比, (1) 能解决模糊查询问题,
二叉搜索树相比, (2) 高度更低, 查找效率更高.
B树相比, B树只适合随机检索, (3) 同时支持 随机检索和顺序检索.
(4) 单一节点 可以储存更多的数据.
(5) 所有的查询都需要查找到叶子结点, 使得 查询性能更稳定.

5 主键索引: 主键索引的查询效率 比其他列索引 效率更高, 主键索引最终叶子节点上存储的就是一条一条记录. 创建 主键约束 时会自动创建对应列的索引.

当一个表中 存在主键时, 数据库 天然就是通过 B+树 的结构组织所有记录的.
而 其他列上索引 的叶子节点存储的是 对应记录的主键id，通过其他列来查询记录，需要 查找两次, 第一次先在该列索引中找到对应id，然后跟据id在主键索引中查找.

6 操作索引:
创建索引: (对于 非主键、非唯一约束、非外键的字段, 可以创建 普通索引)
create index 索引名字 on 表名(字段名);
create index idx on classes(name);

查看索引:
show index from 表名;
show index from student;

删除索引:
drop index 索引名 on 表名;
drop index idx on classes;

7 聚簇索引: 聚簇索引的 叶子节点就是数据节点, 一个表只能有一个聚簇索引, 一般不设置主键为聚簇索引.

非聚簇索引: 非聚簇索引的 叶子节点仍然是索引节点, 只不过有指向对应数据块的指针.

联合索引: MySQL中可以使用 多个字段同时建立一个索引.
为什么要注意联合索引中的顺序? 在联合索引中, 如果要命中索引, 需要按照 建立索引时的字段顺序挨个使用, 否则无法命中索引.

1 事务: 指逻辑上的一组操作, 或者一组被看成整体的SQL. (比如"转账"问题就是就是用事务来解决的)

2 事务的4个特征. (ACID)
原子性: （atomicity) 其中的操作要么全部成功, 要么全部失败. (通过回滚把数据自动恢复了)
一致性: （consistency) A有5000,B有1000, 转账中 保证A和B的金额总和为6000.
隔离性: （isolation）隔离性是指多个事务 并发执行是相互没有影响.
持久性: （durability） 一旦事务成功执行了对 数据库的修改是永久的, 后续的 其它操作都不会影响它的正确性.

3 事务隔离级别:
read uncommitted 读未提交, 可能产生 脏读.
read committed 读已提交, 可能产生 不可重复读.
reaptable read 可重复读, 即读写时都加锁,可能产生幻读.(默认的隔离级别)
serializable 严格串行执行, 隔离程度最高.

4 可能出现的问题:

(1) 脏读. 比如事务B执行过程中修改了 数据X, 在未提交前, 事务A读取了X, 而事务B却回滚了, 这样事务A就形成了脏读. 即当前事务读到的数据 是别的事务想要修改但没有修改成功的数据. 可以在 写操作时加锁 (提高了隔离性,降低了并发性).

(2) 不可重复读. 事务A首先读取了一条数据, 然后执行逻辑的时候, 事务B将这条数据改变了, 然后事务A 再次读取 的时候发现数据不匹配了. 即当前事务 先进行了一次数据读取, 然后再次读取到的数据是别的事务修改成功的数据, 导致两次读取到的 数据不匹配. 可以在 读操作时加锁 (进一步提高隔离性,降低并发性).

(3) 幻读. 事务A首先得到N条数据, 然后事务B 改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据, 导致事务A再次搜索发现有 N+M条数据了, 就产生了幻读. 可以 "串行化"来解决 (串行化:并行就没有了,完全串行来执行,隔离性最高,多个事务之间就不会有任何影响.)

不可重复读和幻读比较: 两者有些相似, 但 前者针对的是 update或delete, 后者针对的是 insert.

5 其他更进一步的问题: 分布式系统中 是如何实现事务的, 如 "跨行转账"问题.