mysql实战45讲笔记

mysql笔记

一、索引

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

• 树的高度越高，需要访问的数据块就越多，所需的读取时间就越长，所以使用N+树(B+)来减少高度。
  B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问
• 问：为什么索引数据越多查询越慢？ 答：索引数据多了，树的高度就高了，磁盘读取的时候就要去别的数据块读取数据了

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

• 在InnoDB中使用B+树来实现

3、索引类型分为主键索引(聚簇索引)和非主键索引(二级索引)

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

  
  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

二、全局锁、表锁(06)

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

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

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

• 表锁：一般只有在没有行级锁的时候才是用

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

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

三、行锁(07)

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

• 优化点：如果事务中需要所多个行，要把最可能造成锁冲突、最可能影响并发度的锁放在后面执行。

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

• 解决方案：

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

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

四、事务(行级锁和事务隔离级别的原理)(08)

• 1、一致性读视图(consistent read view): InnoDB在实现MVCC时会用到 ,用于支持RC(读提交)和RR(可重复读)隔离级别的实现,它没有物理结构，作用是事务执行期间用来定义"我能看到什么数据"。

• 2、InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。

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

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

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

  例子：如图.....

  image

五、普通索引和唯一索引(09)

• 1、唯一索引：列中所有的值不重复，不能为null。
       普通索引：列中可以有重复的值。

• 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 消耗。