首页  思维导图  详情

MySQL 45讲

2022-10-06 10:10:29   0  举报





仅支持查看

AI智能生成

为你推荐

查看更多



mysql

Mysql

MySQL

相关MySQL 45讲相关知识点

MYSQL知识点学习笔记

作者其他创作

大纲/内容

MySQL45讲

MyISAM引擎：把一个表的总行数存在了磁盘上（前提是没有where条件）

InnoDB引擎：在执行count(*)的时候，需要把数据一行一行的从引擎里读出来，然后累积计数

总结：在count没有where条件下，MyISAM的效率比InnoDB高

a. 在MySQL的引擎当中

MVCC机制下，返回的行数也不能立即确定

在扫描时通过扫描主键索引的二叉树来获取多少个值

b. 为何InnoDB没有把行数存储起来

MyISAM表count(*)很快，但是不支持事务

show table status返回很快，但是表数据量大之后，会有误差

InnoDB表直接count(*)会遍历全表，结果准确，但是会导致性能问题

对比MyISAM、show table status指令，count(*)在InnoDB优势

InnoDB引擎遍历整张表，把每一行的主键id值取出来，返回给sever层。server层拿到后，判断不可能为空的，就按行累加

count(主键id)

如果这个“字段”是定义not null，一行行的从记录读取这个字段，判断不能为null，按行累加

如果这个“字段”是定义允许null，那么执行的时候，判断有可能是null，还要把值取出来再判断一下，不是null才累加

count(1)

如果这个“字段”是定义not null，一行行的从记录读取这个字段，判断不能为null按行累加

如果这个“字段”是定义允许null，执行的时候判断有可能是null，还要把值取出来再判断一下，不是null才累加

count(字段)

并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加

count(*)

效率排序：count(*) 优于或等于 count(1) 优于 count(主键id) 优于 count(字段)

建议尽量使用count(*)

总结

C. count(*)、count(主键id)、count(字段)、count(1)之间的区别

14.count(*)为何慢的原因

具体例子解析如下：在A写入redo log 处于prepare阶段之后、写binlog之前，发生了崩溃（crash）。此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚

1. binlog 与 redo log两段提交的解析

a. 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交

如果是，则提交事务

否则，回滚事务

b. 如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整

如何判断一下情景下redo log binlog 是如何处理？

statement格式【理解为创建表】的binlog，最后会有COMMIT

row格式【理解为insert/update/delete语句】的binlog，最后会有一个XID event

MySQL -- binlog格式：Row和Statement

一个完整的binlog是有格式会有

这两个log中有一个共同字段 XID 来串联，假如崩溃恢复时，会顺序扫描redo log

如果碰到既有prepare、又有commit的redo log，就直接提交

如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务

redo log 和 binlog是怎么关联起来的

只要binlog已经写完/commit了，redo log没同步commit时候宕机，恢复之后均会commit操作

这样做保证一致性

处于prepare阶段的redo log加上完整binlog，重启就能恢复，MySQL为什么要这么设计

redo log并没有记录数据页的完整数据，所以他没有能力自己去更新磁盘数据页

正常运行的实例，最终数据落盘，就是把内存的脏页写盘，这个过程跟redo log毫无关系

在崩溃场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读入内存，然后让redo log更新内存内容。更新完成后，内存页变成脏页，就回到了上面情况的状态

正常运行中的实例，数据写入后的最终落盘从redo log更新过来的还是从buffer pool更新过来

15. 日志和索引相关问题

使用explain分析该SQL如图， Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer

Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer

MySQL为排序开辟的内存（sort_buffer）的大小。排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序

补充：mysql.ini配置属性sort_buffer_size

具体分析：从图中可以看到，满足city='杭州’条件的行，是从ID_X到ID_(X+N)的这些记录。具体执行流程如下：1. 初始化sort_buffer，确定放入name、city、age这三个字段；2. 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；4. 从索引city取下一个记录的主键id；5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；6. 对sort_buffer中的数据按照字段name做快速排序；7. 按照排序结果取前1000行返回给客户端。

前提：如果MySQL认为排序的单行长度太大会怎么做？

修改 SET max_length_for_sort_data = 16max_length_for_sort_data是MySQL中专门控制用于排序的行数据的长度的一个参数

city、name、age 这三个字段的定义总长度是36，现在最大只能为16而已，所以超过这个值，M有SQL需要下面做法

rowid排序

如果内存够买就多利用内存，尽量减少磁盘访问

既然不论是order by的sselect语句，无论是全表还是rowId，还不如索引覆盖，一次性在二分查找时候一起列出来

个人总结

总结: 全字段排序 VS rowid排序

Extra字段分析

16.“orderby\"是怎样工作的

1. 创建一个临时表。这个临时表使用的是memory引擎，表里有两个字段，第一个字段是double类型，为了后面描述方便，记为字段R，第二个字段是varchar(64)类型，记为字段W。并且，这个表没有建索引。2. 从words表中，按主键顺序取出所有的word值。对于每一个word值，调用rand()函数生成一个大于0小于1的随机小数，并把这个随机小数和word分别存入临时表的R和W字段中，到此，扫描行数是10000。3. 现在临时表有10000行数据了，接下来你要在这个没有索引的内存临时表上，按照字段R排序。4. 初始化 sort_buffer。sort_buffer中有两个字段，一个是double类型，另一个是整型。5. 从内存临时表中一行一行地取出R值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加10000，变成了20000。6. 在sort_buffer中根据R的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。7. 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出word值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了20003。

具体分析步骤

InnoDB引擎下答案：一个长度为6字节的rowid来作为主键rowid ：每个引擎用来唯一标识数据行的信息1 对于有主键的InnoDB表来说，这个rowid就是主键ID2 对于没有主键的InnoDB表来说，这个rowid就是由系统生成的3 MEMORY引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个rowid其实就是数组的下标order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

MySQL的表是用什么方法来定位“一行数据”

在执行 select word from words order by rand() limit 3 分析 :1. Extra字段显示Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作2. order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法

tmp_table_size限制了内存临时表的大小，默认16m。如果临时表大小超过，就会转成磁盘临时表磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的

优先队列算法的触发条件：1. 所需的有序行数 * 每一行的大小 < sort_buffer_size 的大小2. 执行计划的OPTIMIZER_TRACE结果中，filesort_priority_queue_optimization这个部分的chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的number_of_tmp_files是0。

优先队列排序算法MySQL 5.6引入

鉴于rand() 函数会导致临时表排序，在海量数据下SQL的执行效率极低，所以采用函数的方法来生成随机的结果

随机排序方法

磁盘临时表磁盘临时表使用引擎默认为InnoDB。

17. 讲如何正确地显示随机消息

当SQL写成：mysql> select count(*) from tradelog where month(t_modified)=7时由于对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能所以该SQL用不到索引，变成全表扫描

比如select * from tradelog where id + 1 = 10000这个SQL语句时用不到索引，优化器会屏蔽所用定位，所以请改为select * from tradelog where id = 10000 -1

尽量不要对带有索引的条件字段来使用函数，而是把SQL语句改成基于字段本身范围的查询：

案例一：条件字段函数操作

执行select * from tradelog where tradeid=110717; 语句发现1. tradeid的字段类型是varchar(32) ，需要做类型转换2. tradeid字段有索引，但是这条语句扫描了全表

这里就有两个问题：1. 数据类型转换的规则是什么？2. 为什么有数据类型转换，就需要走全索引扫描？MySQL设置为：字符串和数字做比较的话，是将字符串转换成数字

执行select * from tradelog where id=\"83126\";语句发现1. 当字段不进行类型转换，只有参数进行类型转换时，仍然会走索引的树搜索方法

select * from tradelog where id=\"83126\"相当于select * from tradelog where id = CAST(\"83126\" AS signed int);

案例二：隐式类型转换

MySQL中存在字符编码的概念，在同一个数据库的不同表可能会使用不同的字符编码集。当不同字符编码的表进行连接时，往往会导致连接字段的索引失效。比如例子当中：trade_detail表 tradeid是字符集是utf8mb4 tradelog表 tradeid是utf8

MySQL中字符编码转换规则：在MySQL做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。比如：字符集utf8mb4是utf8的超集，当这两个类型的字符串在做比较的时候，MySQL内部的操作是：先把utf8字符串转成utf8mb4字符集，再做比较。执行这个SQL: select * from trade_detail where tradeid=$L2.tradeid.value ;在MySQL变更为：select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;第二个表中的连接字段被隐式的执行了类型转换函数，导致索引失效，进行了全表扫描

1. 统一字符集，这样就没有字符集转换的问题了比如： alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

解决方法

案例三：隐式字符编码转换

18. 为什么SQL逻辑相同，性能差异却巨大

另外一种查询被堵住的情况，在表t上，执行下面的SQL语句：select * from information_schema.processlist where id=1; 出现上面情况* 出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句

1. MySQL里对表做flush操作的用法，一般有以下两个： flush tables t with read lock; // 定表t的话，代表的是只关闭表t flush tables with read lock; // 表示关闭MySQL里所有打开的表2. 结论：在session A中，我故意每行都调用一次sleep(1)，这样这个语句默认要执行10万秒，在这期间表t一直是被session A“打开”着。然后，session B的flush tables t命令再要去关闭表t，就需要等session A的查询结束。这样，session C要再次查询的话，就会被flush 命令堵住了。

总结：多个事务id访问同一个table，刚好事务A调用过程中，事务B在flush，导致事务C被flush给卡住

情况2：等Flush

查询是占着这个写锁，入到事务A在update某个id值，刚好事务B在select同一个id值，且使用下列的语句：select * from t where id=xxx lock in share mode; 直接因为有写锁保护，不能读取最新值

查看占用写锁的方法[MySQL 5.7版本]，命令如下：select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\\G

情况3：等行锁

第一类：查询长时间不返回

使用慢查询指定1. set long_query_time=02. 查看慢查询日志分析结果

第一种情况：没有索引表数据过万条，查询条件没有任何索引情况

第二种情况：可重复读隔离级别下，视图过长，需要回滚多次例：sessionA 启动事务；sessionB更新了100万次；sessionA查询；此时sessionA需要回滚100万次的回滚才能拿到值。

第二类：查询慢

19. 讲为什么我只查一行的语句，也执行这么慢

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

重点说明：* 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现* 幻读专指“新插入的行”【update语句就没问题】

1.什么是幻读？

1. 首先是语义上的破坏【在MySQL环境下可重复度级别下，也是有幻读出现，例子破坏了一致性问题】

2. 数据一致性的问题【上面1例子截图反馈出每次SeesinA在没commit的多次select中数据条数不一出现】

3. 即使把所有记录都加上锁，还是阻止不了新插入的记录这里使用 select * from xxx where d=5 for update；加行锁但是还是依然觉得不到问题，下面InnoDB介绍如何解决

2.幻读有什么问题？

幻读产生的原因：行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。解决幻读问题：InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)

间隙锁的做法：执行select * from t where d=5 for update时不止给数据库已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新纪录

间隙锁和行锁合称为next-key lock

间隙锁和next-key lock的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”，比如死锁出现

间隙锁是在可重复读隔离级别下才会生效的，所以把隔离级别设置为读提交的话，就没有间隙锁了。但同时，要解决可能出现的数据和日志不一致的问题，需要把binlog格式设置为row。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响并发度的

行锁跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作

间隙锁之间都不存在冲突关系

间隙锁与间隙锁之间关系间隙锁与读写锁之间关系

3. 如何解决幻读

20 讲幻读是什么，幻读有什么问题

默认可重复读隔离级别

两个“原则”、两个“优化”、一个“bug”：1. 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间2. 原则2：查找过程中访问到的对象才会加锁3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

当sessionB执行insert时候，原来sessionA的间隙锁为（5，10）之间，所以sessionC可以插入，B在Acommit前都会BLock

案例一：等值查询间隙锁

案例二：非唯一索引等值锁

案例三：主键索引范围锁

假如非唯一索引，所以间隙锁的区间为c=10的两边，（5，10】、（10，15】，所以sessionB和session C会block

案例四：非唯一索引范围锁

案例五：唯一索引范围锁bug

当执行delete语句时候，如图

案例六：非唯一索引上存在\"等值\"的例子

案例八：一个死锁的例子

21 讲为什么我只改一行的语句，锁这么多

短连接导致业务高峰期出现的连接数突然暴涨的情况

可以通过max_connections控制MySQL实例，但同时存在的连接数的上限问题

通过 show processlist显示相关的线程，通过kill connection + id的命令去kill了sleep指令

注意点：可能会误杀事务通过查information_schema库的innodb_trx表判断是否事务中

1. 先处理掉那些占着连接但是不工作的线程

产生情况：有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段

解决方案：使用–skip-grant-tables参数启动。整个MySQL会跳过所有的权限验证

2. 减少连接过程的消耗

短连接风暴

1. 索引没有设计好

2. SQL语句没写好

3. MySQL选错了索引

慢查询性能问题

QPS突增问题

22 讲MySQL有哪些“饮鸩止渴”提高性能的方法

30 用动态观点看加锁（20、21 整合分析内容）

执行语句 select * from t1 straight_join t2 on (t1.a=t2.a)

通过explain查看结果如上后，这个语句的执行流程：1. 从表t1中读入一行数据 R；2. 从数据行R中，取出a字段到表t2里去查找；3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；4. 重复执行步骤1到3，直到表t1的末尾循环结束

这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，再去表t2中查找满足条件的记录这个过程就跟嵌套查询类似，可以称之为“Index Nested-Loop Join”，简称NLJ 具体分析：先遍历表1，根据表1中取出的值去表2查询满足条件的记录。类似嵌套查询，并且可以用上被驱动表的索引使用join语句，性能比强行拆成多个单表执行SQL语句的性能更好使用join语句，需要让小表做驱动表

Index Nested-Loop Join

执行语句： select * from t1 straight_join t2 on (t1.a=t2.b)

特点：1. 被驱动表没有用上索引2. 直接全量扫描被驱动表

Simple Nested-Loop Join

1. 被驱动表没有索引2. 把表1数据放入内存join_buffer，扫描表2，跟join_buffer作对比3. 扫描函数m+n，判断次数同上一个方法m*n，但是是内存操作，速度快很多，性能更好4. 如果内存放不下表1的所有数据，就分段放

Block Nested-Loop Join

可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的

否则扫描次数特别多，尽量不要用

能不能使用join语句？

34 讲到底可不可以使用join

35 讲join语句怎么优化

MySQL架构图

Server层：连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数

存储引擎层：负责数据的存储和提取（比如支持InnoDB、MyISAM、Memory等多个存储引擎）

MySQL 可以分为Server层和存储引擎层两部分

1、负责跟客户端建立连接、获取权限、维持和管理连接

2、执行mysql -h$ip -P$port -u$user -p 指令

3、客户端长时间无操作会被连接器自动断开。由参数wait_timeout控制。默认8小时。中间没有执行的话就处于空闲状态

4、通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

a. 先到【连接器】

MySQL拿到【select * from T where ID=10】先到这里查询缓存

补充1 ：将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存

补充2：对于确定要使用查询缓存的语句，可以用SQL_CACHE显式指定：select SQL_CACHE * from T where ID=10；

补充3：MySQL 8.0版本直接将查询缓存的整块功能删掉了（重点）

b. 【查询缓存】

分析器先会做语法分析

c.【分析器】开始进入执行SQL语句

1. 决定使用哪个索引

2. 决定各个表的连接顺序

d. 【优化器】通过分析器结果执行语句

首先判断用户执行权限，然后根据引擎不同调用不同接口获取数据

慢查询日志中rows_examined字段，表示这个语句扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的

e. 【执行器】

一条SQL语句select * from T where ID=10；执行过程分析

1. 开篇介绍MySQL

1. InooDB引擎特有的日志【存储引擎层】

2. MySQL写操作时候使用WAL(WAL的全称是Write-Ahead Logging)技术关键点就是先写日志，再写磁盘.这是在redo log层面

3. crash-safe : 有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失

4. innodb_flush_log_at_trx_commit这个参数设置成 1 表示每次事务的redo log都直接持久化到磁盘。这个参数建议设置成1，这样可以保证MySQL异常重启之后数据不丢失。

补充： redo log buffer是一块内存，用来先存redo日志的。在执行commit语句的时候，真正把日志写入redo log文件

日志模块 redo log

1. Server层日志

a. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用

b. redo log是物理日志，记录的是“在某个数据页上做了什么修改” binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”

c. redo log是循环写的，空间固定会用完 binlog是可以追加写入的“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

2. redo log和binlog区别

a. 执行器先找引擎去ID=2的一行。ID是主键，引擎直接用树搜索。如果在内存中则直接返回给执行器，否则需要从磁盘读入内存，然后返回

b. 执行器拿到引擎给的行数据，加1，得到新一行数据，调用引擎接口，写入新数据

c. 引擎更新数据到内存，同时记录到redo log，此时redo log处于prepare状态，然后告知执行器执行完成了，随时可以提交事务

d. 执行器生成binlog，写入磁盘

e. 执行器调用引擎的提交事务接口，引擎把redo log改成提交（commit）状态，更新完成

3. InnoDB引擎执行update操作内部流程SQL: update T set c=c+1 where ID=2

a. redo log写入被拆成了两个步骤：prepare和commit，这就是“两阶段提交”

b. redo log和binlog都可以用于表示事物的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致

4. 两阶段提交目的：为了让两份日志之间的逻辑一致

重要的日志模块：binlog

2.日志系统：SQL更新语句是如何执行重点分析：redo log 与 bin log

【A -- atomicity】原子性

【C -- consistency】一致性

【I -- isolation】隔离性

【D -- durability】持久性

ACID

目的：为了解决脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题

读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。个人理解--别人改数据的事务尚未提交，我在我的事务中也能读到

读提交（read committed）：一个事务提交之后，他做的变更才会被其他事务看到个人理解--别人改数据的事务已经提交，我在我的事务中才能读到。

可重复读（repeatable read)：一个事务执行过程中看到的数据，总是跟这个事务在启动时候看到的数据是一致的。在可重复读隔离级别下，为提交变更对其他事务也是不可见的。个人理解--别人改数据的事务已经提交，我在我的事务中也不去读

串行化（serializable）：对同一行记录，读写都会加锁。当出现锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。个人理解--我的事务尚未提交，别人就别想改数据

4个隔离级别

每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）

查询记录的时候，不同的事务ID会根据MVCC找到相对应符合MySQL规定的事务ID对应的快照值

记录的日志删除，根据系统判断没有回滚日志时候便会删除

尽量不使用长事务：1.会存在很老的事务视图。占用大量存储空间 2.占用锁资源

隔离级别的实现

显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback

set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到主动执行commit 或 rollback 语句，或者断开连接

建议set autocommit=1，通过显式语句启动事务

事务的几种启动方式

隔离级别

3. 事务隔离

为了提高查询的效率

索引的目的

是一种以键-值【K-V】存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value

这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎

哈希表

在等值查询和范围查询场景中的性能就都非常优秀

查找复杂度O(log(N))

有序数组索引只适用于静态存储引擎，在需要更新数据的时候比较麻烦

有序数组

更新复杂度O(log(N))

N叉树：多个儿子之间大小保证从左到右递增

实际大多数的数据库采用是N叉树因为索引不止存在内存中，还要写到磁盘上

为了让一个查询尽量少的度期盼，必须让查询访问尽量少的数据库，所以使用N叉树

二叉搜索树

常见的索引模型

InnoDB使用了B+树索引模型，所以数据都是存储在B+树中

在InnoDB中，表都是根据主键顺序以索引的形式存放的

MySQL InnoDB索引模型

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

主键索引

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

非主键索引

如果语句是select * from T where ID=500，是主键查询方式只需要搜索ID这棵B+树

如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表

优化：于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询

主键索引和非主键索引查询的不同？

MySQL InnoDB索引分类

条件查询中使用到非主键索引的值，都需要再一次通过结果召回对应条件的主键索引，这个过程统称回表

何为回表？

4.深入浅出索引 - 上

例子

定义：上面例子中由于搜索的条件不是主键索引，故需要根据非主键索引召回主键索引的过程就叫回表

何为回表

操作覆盖索引：在查询中，索引k已经“覆盖了”，比如索引k记录了主键ID，这称为覆盖索引

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

例子：市民信息表中建立身份证号和名字联合索引当根据身份证号查询名字是，可以减少回表次数

优化回表操作 -- 覆盖索引

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上

如果第一个字段是范围查询需要单独建一个索引

在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边

什么是最左前缀原则

最左前缀原则

在MySQL5.6版本之前，使用非主键索引进行查询的时候，存储引擎通过索引查询数据，然后将结果返回给MySQL server层，在server层判断是否符合条件

在MySQL5.6版本及之后，使用索引下推的特性：在索引的列做为判断条件时，MySQL server将这一部分判断条件传递给存储引擎，然后存储引擎会筛选出符合MySQL server传递条件的索引项，即在存储引擎层根据索引条件过滤掉不符合条件的索引项，然后回表查询得到结果，将结果返回给MySQL server。【总结：存储引擎做了过滤器，把判断的索引条件在这里进行了赛选，结果返回给MySQL Server】

在5.6版本之前的执行处理，查询的索引只要不是主键索引，每一条均需要会回表，这里需要2次回表，假如符合要求更多就要回表更多

在5.6版本及以后的执行处理：对索引中的字段先做判断过滤不符合的字段，得出结果回表查询对应id，只做一次回表

索引下推

5. 深入浅出索引 - 下

根据加锁的范围MySQL里面的锁：可以分成全局锁、表级锁和行锁三类

定义：全局锁就是对整个数据库实例加锁

使用案例：做全库逻辑备份

命令： Flush tables with read lock (FTWRL)后果：当前事务id只读，其他线程或者写入操作均阻塞

set global readonly=true 让全数据库变成只读

执行方式

全局锁

表级锁有两种：一种是表锁，一种是元数据锁

执行方式： lock tables … read/write 锁住这个表用unlock tables主动释放锁

表锁

执行方式：访问一个表的时候会被自动加上

作用：保证读写的正确性

MDL（metadata lock)

表级锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。

情景：事务A内有两个update的操作【换而言之有2个行锁在这个事务内】，事务B同样有同一个id去修改该值分析结果：事务B需要等待事务A释放资源【等待事务A要commit释放】，事务B才能执行begin的update语句。这个等待的过程会阻塞总结：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态解决死锁策略：1. 等待释放，通过innodb_lock_wait_timeout来设置等待时间 2. 发起死锁检测，发现死锁后主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。通过innodb_deadlock_detect设置为on，表示开启这个逻辑

行锁引申-- 两阶段提交

行锁

6-7. 全局锁和表锁

前提：一个table名叫T，事务A、B、C分别对T表同一个id进行读写操作a. 在3节讲到：在可重复读隔离级别，事务 T 启动会创建一个视图 read-view，之后事务执行期间有其他事务修改了数据，事务 T 看到的仍然跟在启动时看到的一样b. 在7节讲到：事务要更新一行，如果刚好有另外一个事务拥有这一行的行锁，会被锁住，其他事务进入等待状态。a与b之前不是矛盾吗？今天就是讲这里面的区别，主要是通过MVCC机制处理快照读，而更新采用的是行锁+间隙锁来处理数据一致性的问题

分析table T中A\\B\\C事务如何操作: 上面执行的SQL，无论是A\\B\\C均为访问快照读，所以这个事务的快照，就是“静态”操作顺序为：start A、start B、start C、C undate k=k+1、C commit、B undate k=k+1、B Select、A Select、A commit、B commit。

定义：MVCC是指多版本并发控制。 MVCC是在并发访问数据库时，通过对数据进行多版本控制，避免因写锁而导致读操作的堵塞，从而很好的优化并发堵塞问题

undo log实现了数据库快照功能，通过事务id和undo log我们可以找到历史版本的数据

a.通过undo log来保存多版本的数据

InnoDB为每个事务构造了一个数组，来记录这些活跃的事务id集合而成，根据事务id的最小值为低水位，最高值为高水位这和而成的read-veiw叫做一致性事务，也称快照

对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：1.如果落在蓝色部分，表示这个版本是已提交的事务生成的，这个数据是可见的；2.如果落在黄色部分，表示这个版本是由将来可能启动的事务生成的，是肯定不可见的3.如果落在绿色部分，那就包括两种情况： a. 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见 b. 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见

b.通过一致性视图来保存当前活跃的事务列表，将两者结合和制定一定的规则来判断当前可读数据

rr机制下，所有事务id公用同一个视图【可以理解为同一个readview】，所以当执行start transaction with consistent snapshot指令的下一行sql才开始事务

rc【read commit】机制下，每一个语句执行前都会重新算出一个新的一致性视图，也就是每次事务id过来，都是一个新的readview，所以start transaction with consistent snapshot指令报废，直接start transaction就是开启了事务

rr机制与rc机制的readview区别

当执行insert、update、delete的sql语句是先读后写的，而这个读，只能读当前的值，称为“当前读”

mvcc机制这个更多是对select语句这块的处理理解，其他当前读的操作要看具体分析了

当前读与快照读的不同

MVCC机制实现原理【MySQL 】

MySQL InnoDB下的MVCC机制

MVCC机制

8 讲事务到底是隔离的还是不隔离的

前提：在4节【深入浅出索引 - 上】知道MySQL InnoDB中有主键索引【也称：唯一索引】、非主键索引【普通索引】

查询性能上的区别：在执行select id from T where k=5的SQL语句，唯一索引与普通索引区别 * font color=\"#bbdefb\

InnoDB的数据是按数据页为单位来读写的，在InnoDB中，每个数据页的大小默认是16KB

查询过程

当MySQL需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%

对于多写少读的情景：比如账单类、日志类的系统，此时change buffer的使用效果最好。

一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大

对于一个业务的更新模式是写入之后马上会做查询，更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

使用场景

change buffer

唯一索引：找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束

普通索引：找到3和5之间的位置，插入这个值，语句执行结束

第一种情况：这个记录要更新的目标页在内存中【change buffer内判断】

唯一索引：需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束

普通索引：将更新记录在change buffer，语句执行就结束了

第二种情况：这个记录要更新的目标页不在内存中【在数据页内】