mysql事务索引
文章目录
- mysql事务索引
- 事务：
- 并发事务出现的问题
- 解决办法：事务四大隔离级别
- 索引：
- innoDB索引：
- 联合索引和覆盖索引
- 前缀索引
- 索引相关问题：
- 索引失效情况：
- 索引设计原则：
- mysql索引为什么选择使用B+树而不是其他的数据结构?
事务：
四大特性（ACID）：
- 原子性
概念：一个事务中的所有操作要么全部成功提交，要么全部失败回滚
实现：通过uodo log回滚日志
每条数据的增删改操作都伴随一条undo log的生成,并且回滚日志先于数据持久化到磁盘上
怎么回滚？根据回滚日志做逆向操作
比如在回滚日志里有新增记录，则生成相应的删除语句。有删除记录就生成相应的新增语句 - 一致性
概念：事务把数据库从一个一致状态变换到另一个一致状态
实现：通过其他三个特性来保证实现 - 隔离性
概念：一个事务的执行不会被其他事务所影响
实现：通过mysql四种隔离级别 - 持久性
概念：事务提交成功后，对数据库数据的改变是永久的
实现：通过redo log重做日志
分为重做日志缓存和重做日志文件，因为mysql表数据是存储到磁盘上的，因此每次存取都要进行磁盘IO，而直接进行磁盘IO又是很耗性能的，所以为了提高性能会先将数据添加到缓冲池(Buffer Pool)，再使用后台线程将数据保存到磁盘中。
并发事务出现的问题
- 脏读
概念：事务A读取到事务B还未提交的数据 - 不可重复读
概念：事务A前后两次读取到的数据不一样
原理：事务A第一次读取之后，事务B进行了更新操作并提交，事务A再次读就发现数据变了 - 幻读
概念：事务A查询数据发现没有，进行插入操作的时候又提示该数据已存在，就好像出现了幻觉
原理：事务A查询之后，事务B进行插入操作并提交，由于幻读已经解决了不可重复读，所以事务 A再次读发现数据
解决办法：事务四大隔离级别
- 读未提交
顾名思义，读取未提交的数据，不可解决任何问题 - 读已提交
顾名思义，读取已提交的数据，可以解决脏读问题 - 可重复读
顾名思义，可以重复读取，在这个事务持续期间禁止其他事务对这个字段的更新操作，可以解决脏读和不可重复读问题 - 可串行
确保事务可以读取到相同行，在这个事务持续期间禁止其他事务的增删改操作，可以解决所有并发事务问题，但是性能很低
索引：
索引是什么？
帮助mysql高效获取数据的数据结构（B+树）。
相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引是用来提高检索效率的。
索引优劣：
优：
- 提高数据检索效率，降低数据库的IO成本
- 可以通过索引进行排序
劣：
- 索引要保存到磁盘，占用磁盘空间
- 索引可以提高查询效率，但是会降低更新表的效率。进行增删改操作时，mysql不仅要保存数据，还要保存或者更新对应索引。
索引类型：
主键索引、普通索引、唯一索引、全文索引、前缀索引、联合索引
innoDB索引：
聚簇索引（主键索引、一级索引）和辅助索引（非聚簇索引、二级索引）
除了主键索引其他都是辅助索引.
聚簇索引：叶子节点保存每一行的行数据
辅助索引：叶子节点保存对应主键值（查询时可能需要进行回表查询）
联合索引和覆盖索引
联合索引：对两个或多个列建立的索引.
在建立索引时按最左边的列进行排序，左边列相同时再对下一列进行排序，依次类推
所以在组合索引中只有最左边的列是绝对有序的，而其他列无序。
最左前缀法则：
联合索引索引了多列，要遵守最左前缀法则。
最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳过某一列，该列后面的字段索引将失效。如果跳过了最左列，索引将失效变为全表扫描
ps：where条件后面索引中的字段顺序随便写，只要存在即可。但是order by后面的字段要按顺序写，否则会失效。
覆盖索引：
使用辅助索引查找时，只查辅助索引字段值及对应主键值，不需要回表，直接返回数据，所以要避免使用select * 否则很容易出现回表查询，性能降低。
联合索引和单列索引：尽量使用联合索引，避免使用单列索引；联合索引使用得当，可以覆盖索引，避免回表。创建联合索引时考虑顺序，因为最左前缀法则。
前缀索引
字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。
此时可以只将字符串的一部分前缀建立索引，大大节约索引空间，从而提高索引效率
索引相关问题：
索引失效情况：
1.不要在索引列上进行运算操作，索引将失效：where substring(phone,10,2)=‘15’
2.字符串不加引号，索引将失效：
3.模糊查询：头部模糊，索引失效，尾部模糊索引不失效
4.or前后只要存在非索引的列，索引将失效
5.联合索引中，出现范围查询(>,<)，范围查询右侧的索引将失效（在不影响的情况下可以改为>=,<=）
6.数据分布影响：如果mysql评估使用索引比全表更慢，则不使用索引。走不走索引取决于表中数据的分布的，而不是一个固定的。如果返回的值是少部分则走索引，如果是大部分则直接全表扫描.
索引设计原则：
1.对数据量较大且查询频繁的表建立索引；
2.针对where查询条件以及orderby、groupby后面的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引
4.建立前缀索引
5.建立联合索引
6.控制索引数量，索引越多，维护索引的代价越高，也会影响增删改的效率
mysql索引为什么选择使用B+树而不是其他的数据结构?
为什么不用二叉树(AVL,红黑树)?
平衡二叉树跟红黑树在大数据量等场景下，树仍然太高.
红黑树:通过舍弃严格的平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在大数据量的场景下，树仍然太高，IO次数太多。
为什么用B+树而不用B树（B+树相对于B树有什么优点）
1.B+树的数据都在叶子节点上，查询效率更稳定
2.B树B+树的每一个节点的逻辑存储结构都是页，一页的大小都是固定的16k，而对于B树，无论是叶子节点还是非叶子节点，都会保存数据，这样就会导致一页中存储的键值减少，跟着指针也就减少，要保存同样大小的数据，就只能增加树的高度，导致性能下降。
3.B+树叶子节点采用双向链表，便于范围查询和排序,相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可,而B-Tree需要获取所有节点，相比之下B+Tree效率更高。
为什么不用hash索引
hash索引只支持等值匹配(精准匹配)，不支持范围匹配和排序操作
