mysql实战45讲笔记

作者: 涂改 | 来源:发表于2019-03-30 16:14 被阅读0次

(04篇)
1、为什么使用 N+树？

2、索引是在存储引擎中实现，而存储引擎是可替换的，所以索引的实现方式有很多。

主键索引中保存的值是整行的数据，而非主键索引保存的是主键的值。(见下图)

image
具体的查询时：

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
索引的维护：由于B+树是有顺序的，所以当插入一个中间大小的索引时，需要先移动后面的数据，空出一个位置在插入(如果当前数据页已经满了，就要重新申请新的数据页，再把数据移动过去，这就是页分裂)。
所以尽量用自增主键，就不会出现页分裂的现象了。

(05)

覆盖索引：可以减少树的搜索次数(减少回表)(因为在索引中就已经存在了需要的值)

如何使用覆盖索引：建立联合索引
最左前缀原则：只要满足最左前缀，就可以利用索引来加速检索，这个前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

例如： like '张%' 这就满足最左前缀原则，可以用到索引; like '%张%' 这就不满足了(但是会用索引快速定位记录,不算用了索引)
联合索引：
写法 KEY `name_age` (`name`,`age`)
生成索引结构如图.....

image

如果索引为(a,b)，索引中会先根据a排序，在根据b排序(b只有在a相同的时候排序有规律)。
例子：(1,2), (2,3), (2,4), (3,1), (4,2)
所以当只有基于b的查询条件时,无法使用(a,b)的联合索引,因为b不是规律的
索引下推：

在MySQL5.6引入了索引下推优化。

可以在索引便利过程中，对索引中包含的字段先做判断，直接过滤掉不满足的条件的记录，减少回表次数。

例子：建立联合索引(name,age) 使用 like '张%’and age >10 检索，MySQL5.6版本之前，会先根据索引找到符合张%的列,再对匹配的数据进行回表查询。5.6版本后，根据索引找出符合的数据后,会根据联合索引中存储的age的值在筛选一遍，排除不符合的lie(age <= 10)再进行回表查询，减少回表率，提升检索速度。如下图.....
image

1、全局锁：对整个数据库实例加锁

使用场景：全库逻辑备份。(推荐使用–single-transaction方法)

2、表级锁：分为表锁和MDL(metadata lock)

表锁：一般只有在没有行级锁的时候才是用
MDL：不需要显示调用,在访问一个表的时候会自动加上。作用：保证读写的正确性
MySQL5.6中加入MDL，当对一个表增删改查时,加MDL读锁; 当对表做结构变更操作时,加MDL写锁。

读锁不互斥:可以多线程对表CURD
读写锁、写锁之间互斥:用来保证变更表结构的安全性

1、两阶段锁协议：在InnoDB事务中,行锁是需要的时候才加上的,要等到事务结束才释放

2、死锁和死锁检测：当两个事务互相等待就进入死锁

解决方案：

1、进入等待直到超时，参数：innodb_lock_wait_timeout。
2、发起死锁检测，发现死锁后，主动回滚死锁链中的某一个事务，参数：innodb_deadlock_detect设置为on(默认为on开启) 死锁检测会消耗大量的CPU资源，所以遇到热点行跟新的时候，最好控制并发度。

3、innodb行级锁是通过索引记录实现的，如果更新的列没有建索引就会锁住整个表。

1、一致性读视图(consistent read view): InnoDB在实现MVCC时会用到 ,用于支持RC(读提交)和RR(可重复读)隔离级别的实现,它没有物理结构，作用是事务执行期间用来定义"我能看到什么数据"。
2、InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图。
在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

3、当前读:(current read)：更新数据都是先读后写的，而这个读，只能读当前的值(数据库中最新的值)。

select 语句如果加锁，也是当前读。

例子：如图.....
image

2、change buffer：

当跟新一个数据时，如果该数据页在内存中就直接更新内存(同时写redo log日志)。如果数据页没在内存中,会将更新操作缓存在change buffer中，在下次查询访问时才将数据读入内存，然后执行change buffer中与这个页相关的操作。
3、InnoDB数据的读写：

会先以数据页为单位，将一页数据页都读到内存中，然后在内存中读取。每个数据页默认大小(16KB)
4、InnoDB数据更新：和change buffer原理类似。
数据更新例子：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

1、Page 1 在内存中，直接更新内存；
2、Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3、将上述两个动作记入 redo log 中。
5、redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

网友评论

本文标题：mysql实战45讲笔记

本文链接：https://www.haomeiwen.com/subject/dgcsbqtx.html

mysql实战45讲笔记