mysql学习
mysql的事务隔离级别
- 读未提交 可以读到其他事务未提交的数据
- 读已提交 可以读到其他事务已提交的数据
- 可重复读 在一个事务里,对同一个select语句,执行的结果都保持一致
- 串行化 读写穿行化,保证数据准确性,吞吐量低
mysql innodb存储引擎默认使用 可重复读
事务并发执行引起的问题
- 脏写 事务A可以修改到事务B未提交的数据
- 脏读 事务A可以读到事务B未提交的修改的数据
- 不可重复读 事务A可以读取到其他事务对数据修改的最新值,每次读取到的值不一致
- 幻读 事务A按照相同的条件查询,查询多次,查询到之前未出现的数据
索引
- 聚簇索引 主键使用的索引就是聚簇索引 聚簇索引建立的索引是带有所有列数据的索引
- 非聚簇索引 二级索引,联合索引,唯一索引使用的就是非聚簇索引 非聚簇索引建立的索引是只带有索引字段数据和主键值的索引
SQL优化
sql优化的过程实际上就是更好的运用索引,B+树索引适用于一下几种情况
- 全值匹配
- 匹配左边的列
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
- 用于排序
- 用于分组
根据上面的情况,我们在写sql时需要注意一下几点。
- 只为用于搜索、排序或分组的列创建索引
- 为列的基数大的列创建索引
- 索引列的类型尽量小
- 可以只对字符串值的前缀建立索引
- 只有索引列在比较表达式中单独出现才可以适用索引
- 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况,建议让主键拥有AUTO_INCREMENT属性。
- 定位并删除表中的重复和冗余索引
- 尽量使用覆盖索引进行查询,避免回表带来的性能损耗。
- 特殊情况可以强制使用索引查询
MVCC
mvcc多版本并发控制本质是建立一条数据的多个版本,组成一条数据的版本链。
每一条数据在存储时,会加上两个隐藏的列
- tx_id 事务id,用来记录当前数据变更的事务id
- roll_pointer 每次事务对数据做变更时,会记录一条undo日志,roll_pointer相当于一个指针,用来指向变更之前数据的undo日志,当数据发生多次变更时,就会生成一个版本链。
*
ReadView
对于使用READ COMMITTED
和REPEATABLE READ
隔离级别的事务来说,都必须保证读到已经提交了的事务修改过的记录,也就是说假如另一个事务已经修改了记录但是尚未提交,是不能直接读取最新版本的记录的,核心问题就是:需要判断一下版本链中的哪个版本是当前事务可见的。为此提出了一个ReadView的概念,这个ReadView中主要包含4个比较重要的内容:
-
m_ids:表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
-
min_trx_id:表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id,也就是m_ids中的最小值。
-
max_trx_id:表示生成ReadView时系统中应该分配给下一个事务的id值。
-
creator_trx_id:表示生成该ReadView的事务的事务id。
有了这个ReadView,这样在访问某条记录时,只需要按照下边的步骤判断记录的某个版本是否可见:
-
如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同,意味着当前事务在访问它自己修改过的记录,所以该版本可以被当前事务访问。
-
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值,表明生成该版本的事务在当前事务生成ReadView前已经提交,所以该版本可以被当前事务访问。
-
如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值,表明生成该版本的事务在当前事务生成ReadView后才开启,所以该版本不可以被当前事务访问。
-
如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间,那就需要判断一下trx_id属性值是不是在m_ids列表中,如果在,说明创建ReadView时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建ReadView时生成该版本的事务已经被提交,该版本可以被访问。
如果某个版本的数据对当前事务不可见的话,那就顺着版本链找到下一个版本的数据,继续按照上边的步骤判断可见性,依此类推,直到版本链中的最后一个版本。如果最后一个版本也不可见的话,那么就意味着该条记录对该事务完全不可见,查询结果就不包含该记录。
MVCC在可重复读和读已提交 实现上最大的区别
ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改
我们说过普通的SELECT语句在READ COMMITTED
和REPEATABLE READ
隔离级别下会使用到MVCC读取记录。在READ COMMITTED
隔离级别下,一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView
,ReadView
的存在本身就保证了事务不可以读取到未提交的事务所做的更改,也就是避免了脏读现象;
REPEATABLE READ
隔离级别下,一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView
,之后的SELECT操作都复用这个ReadView,这样也就避免了不可重复读和幻读的问题。
读已提交在每次select都会生成一个readview,可重复读在第一次select时会生成一个readview,所以后续的select公用一个readview,这样每次读到的数据都是一致的。