首页  思维导图  详情

mysql

2021-12-13 19:10:31   10  举报





AI智能生成

mysql知识点

知识脑图

mysql原理

作者其他创作

大纲/内容

如果普通索引查询所需要的列不在该索引里，则需要先从普通索引定位到主键，再去主键索引拿到数据

回表查询

在普通索引树上就能获取到查询的字段，不需要做回表操作，速度更快；explain的输出结果Extra字段为Using index时，能够触发索引覆盖

索引覆盖

mysql5.6之后引入了索引下推，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。以like为例子，5.6之前是在联合索引里匹配到like的结果集，然后逐个回表，在主键索引上找出对应记录，然后再对比其他条件5.6以后则是在索引遍历的过程中，过滤掉不匹配的字段，减少回表次数

索引下沉

要走索引需要满足最左前缀原则group by底层是先走一次order by

group byordder by

一次性去除所有满足条件的字段数据，然后去sort buffr中（加载到内存中）进行排序，得到结果

单路排序

拿满足条件的字段id和排序字段去sort buffer排序，得到结果后根据id回表拿到结果

双路排序（回表排序）

Using filesort文件排序字段总长度max_length_for_sort_data小于1024（默认）字节，走单路排序、大于走双路排序

前缀索引

记忆点

ACID:原子性、一致性、隔离性、持久性

多个事务基于最初选定的值同时更新同一行，最后提交的覆盖另一个事务

脏写

事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求

脏读

事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

不可重复读

事务A读取到了事务B提交的新增数据，不符合隔离性

幻读

并发问题

读未提交

读已提交

可重复读解决了脏读和不可重复读（mysql默认，多数都用这个）

整个数据库变成单线程的串行化处理方式，效率低

串行化

隔离级别

乐观锁

悲观锁

按性能分

（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响

读锁

（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

写锁

读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

按对数据库操作类型分（读锁写锁都属于悲观锁）

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

表锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

行锁

按操作粒度分

范围查询会触发，范围内所有间隙加上锁

间隙锁是在可重复读隔离级别下才会生效。

间隙锁

无索引行锁会升级为表锁锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁合理设计索引，尽量缩小锁的范围尽可能减少检索条件范围，避免间隙锁尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行尽可能低级别事务隔离

优化建议

锁

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

undo日志

读已提交是每次查询都基于当前数据库生成一份最新的readView；可重复读是当前事务第一次查询触发获取readView

readView

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

mvcc机制（读已提交和可重复复用到了）

相当于一个数据缓存，所有ddl操作不会直接写到磁盘，会存到bufferpool，定时磁盘io。顺序io比乱序io效率高非常多；（保证了高并发）如果机器挂了，会从redo日志里恢复数据到buffpool，保证一致性

buffer pool

事务

客户端

管理连接和权限校验

连接器

解析sql语句

词法分析器

执行计划生成，语法选择

优化器

调用引擎接口获取查询结果

执行器

因为只要对表有ddl操作就会清空缓存重新收集，所以一般用在静态表上，比如配置表、数据字典

查询缓存

记录每条修改的sql优点：不需记录每行的数据变化，减少日志量缺点：主从复制不一致

statement（默认）

记录每行数据没修改的情况优点：可以看见修改细节缺点：数据量大

row

statement和row的结合在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种

mixed

bin-log日志（三种工作模式）

server层

InnoDb

MyISAM

Memory

读写磁盘，数据结构化存储的实现

引擎层

内部组件

二叉树

红黑树

hash表

1、节点中存有索引和数据，全部数据是分布在这个树上。2、每次搜索过程等价于做一次全集的二分查找，可能在非叶子结点结束。

B tree

1、中间节点只存了索引，没有存数据，叶子节点中包含了全部的数据。2、每次搜索过程无论成功与否，都是一条从根节点到叶子节点的路径。

B+ tree

数据结构

聚簇索引

索引类型

先开发完项目主体功能，根据涉及到的所有查询情况，合理的设计索引

比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段（尽量用到索引覆盖），还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则

索引尽量不要建立在小基数列上（例如性别），数据大的表效率不如全表查询

索引尽量建在数据类型较小的字段上，例如tinyint；如果必须要在大字段上简历索引，可以考虑去建立前缀索引（前缀索引无法排序）

涉及索引时where和order by冲突，优先考虑where

索引设计原则

索引

不在索引列做任何操作（计算、函数、类型装换），不然会导致索引失效

联合索引多个查询条件要遵循最左前缀原则，从左开始不能跳过索引中的列，保证其有序性

尽量使用覆盖索引（查询的列在索引中）

mysql在使用不等于（！=或者<>），not in ，not exists，isNull、isNotNull 、like的前面放通配符这些的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>=，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

字符串不加单引号会索引失效

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引（in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描）

范围查询：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。例如有些情况可能是由于单次数据量查询过大导致优化器最终选择不走索引优化方法：可以将大的范围拆分成多个小范围

尽量用小表做驱动表

尽量单表查询，连接查询最好不要大于三个表，并用主键链接；逻辑处理劲量交给java、es、大数据等去做，术业有专攻

NLJ算法

把驱动表（t2，100行）所有数据放入join buffer中，把t1表每一行数据都取出来跟join buffer中的数据比较，返回满足条件的数据；等于扫描了100*10000=100w次join buffer空间不够则会分段方，比如第一次先比较100行中的60行，第二次再放入剩下的40行比较。等于多扫了一次t1表

BNL算法

MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

连接查询（难以优化）

如果是连续的主键排序再分页可以用where id>10000 limit 10;

分页查询

select * from a where a.id in (select id from b);-->类似于for循环表Bselect * from A where exists (select 1 from B where B.id = A.id)-->类似于for循环表A尽量降低循环数

in和exists

count（*）效率其实最高（mysql专门做了优化）

myisam引擎不带where的总行数会被记录在磁盘上可以直接查询

show table status；可以查到一个大概的估值。

redis维护，不过很难满足一致性

增加一个计数表，和增删刚到一个事务，保证一致性