MySQL

如果自己设置索引,可能造成页分裂
因为会出现插队现象:比如一个节点中是1,2,5;这时候如果生成一个4的索引,就会把5寄出去,造成页分裂

B+Tree只在叶子节点存储数据,而BTree的非叶子节点也要存储数据

B+Tree的单个节点的数据量更小,在相同的磁盘I/O次数下,就能查询更多的节点(查询相同节点时,需要的磁盘I/O数更少)

B+Tree叶子节点采用的是双链表连接,适合MySQL中常见的基于范围的顺序查找,而B树无法做到这一点

对于有N个叶子节点的B+Tree,其搜索复杂度为O(logdN),其中d表示节点允许的最大子节点个数为d,在实际的应用中,d值是很大的,这就保证了,即使数据量很大,B+Tree的高度依然维持在一个较小的范围,也就是说一次数据查询操作只需要较少次数的磁盘I/O操作就能查询到目标数据,而二叉树的每个父节点的儿子节点个数是2个,意味着其搜素复杂度为O(logN),这已经比B+Tree高出了不少,因此二叉树检索到目标数据所经历的磁盘I/O次数要更多;(二叉树的叶子节点只有两个,这样会导致树更高,磁盘I/O数更多)

Hash在做等值查询的时候很快,搜索复杂度为O(1),但是Hash不适合做范围查询

查数据时,要查的值就是索引值,不用再根据主键回表

SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2024-01-01';
在没有索引下推的情况下，数据库可能首先扫描 customer_id = 123 的记录，再对这些记录逐一应用 order_date > '2024-01-01' 的过滤条件。但如果启用了索引下推，数据库会直接在扫描过程中就应用 order_date > '2024-01-01'，只检索满足这两个条件的记录，避免了不必要的数据扫描。

在mysql8.0.13以后,优化器引入了跳跃扫描技术,允许对复合索引的不同部分分别进行范围扫描,举例来说,如果复合索引是由f1和f2组成,而查询条件只设计f2>40,那么mysql会获取f1的不同值,比如f1=1和f1=2;然后根据每个f1的值,结合f2>40的条件,分别做范围扫描.将所有范围扫描的结构合并
索引跳跃ISS其实就是 MySQL 8.0 推出的适合联合索引左边列唯一值较少的情况的一种优化策略,如果左边列唯一值太多,就不会发生索引跳跃

索引(a,b,c)中,b和c是全局无序,局部相对有序的

联合索引的最左匹配原则,在遇到范围查询(如>,<)的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询字段后面的字段无法用到联合索引,注意,对于>=,<=,BETWEEN,like前缀匹配的范围查询,并不会停止匹配

查不到

查得到

主键索引

非主键索引发生索引覆盖 

不可读不可写

可读不可写

对一张表进行CRUD操作的时候,加的是MDL读锁

对一张表做结构变更操作的时候i,加的是MDL写锁

innode_autoinc_lock_mode = 0

innode_autoinc_lock_mode = 2

innode_autoinc_lock_mode = 1

S(Share)

X(排它)

子主题

不同事务获取相同范围间隙锁时互斥

查到

没查到

大于等于的范围查询

小于或小于等于的范围查询

大于的范围查询

查询记录不存在

查询记录存在

对二级索引的加锁不会退化

会走全表扫描,每一条记录都会加上临建锁,相当于锁住全表,这时如果其他事务对该表进行增 删 改都会阻塞

报错,并且给重复的索引加上S型的记录锁

报错,给重复的二级索引记录加S型的next-key锁

STATEMENT(默认)

ROW

MIXED

在完成主从复制后,你就可以在写数据时只写主库,在读数据时只读从库,这样即使写请求会锁表或者锁记录,也不会影响读请求的执行

MySQL主库提交事务的线程要等待所有从库的复制成功响应,才能返回客户端结果,这种方式在实际项目中,基本上没法用,原因:一是性能很差,因为要复制到所有节点才会返回响应,二是可用性也很差,主库和所有从库任何一个数据库出问题,都会影响业务

MySQL主库提交事务的线程不会等待binlog同步到各从库,就返回客户端结果,这种模式一旦主库宕机,数据就会丢失

事务线程不用等待所有的从库复制成功响应,只要一部分复制成功响应回来就行,比如一主二从的集群,只要数据成功复制到任意一个从库上,主库的事务线程就可以返回给客户端,这种半同步复制的方式,兼顾了异步复制和同步复制的优点,即使出现主库宕机,至少还有一个从库有最新的数据,不存在数据丢失的风险

sync_binlog=0(默认)

sync_binlog=1

sync_binlog=N

将redolog对应的事务状态设置为prepare,然后将redolog刷新到硬盘

将binlog刷新到磁盘,接着调用引擎的提交事务接口,将redolog状态设置为commit,此时该状态并不需要持久化到磁盘,只需要write到文件系统的page cache就够了,因为只要binlog写到磁盘成功,就算redolog的状态还是prepare也没有关系,一样会被认为事务已经执行成功

如果binlog中没有当前内部XA事务的XID,说明redolog完成刷盘,但是binlog还没有刷盘,则回滚事务,对应时刻A崩溃恢复的情况

如果binlog中由当前内部XA事务的XID,说明redolog和binlog都已经完成了刷盘,则提交事务,对应时刻B崩溃恢复的情况

通过MVCC解决
MVCC:多版本(一行数据的版本链)并发(场景)控制(读取的结果)
MVCC是一种数据库技术，通过保存数据的历史版本(版本链)，使得事务在不阻塞的情况下读取一致的数据快照，同时允许其他事务并发修改数据，从而提高数据库的并发性能和隔离性

不可重复读指的是数据的值

幻读值的是数据的量

每次对某条聚簇索引记录进行改动时,都会把旧版本记录到undo日志中,然后这个隐藏列是个指针,指向每一个旧版本记录,于是就可以通过它找到修改前的记录

当一个事务对某条聚簇索引记录进行改动时,就会把该事务的事务id记录在trx_id隐藏列里

如果记录的trx_id在m_ids列表中,表示生成该版本记录的活跃事务依然活跃着(还没提交事务),所以该版本的记录对当前事务不可见

如果记录的trx_id不在m_ids列表中,表示生成该版本记录的活跃事务已经被提交,所以该版本的记录对当前事务可见

可以通过next-key(临建锁)解决d

幻读了怎么办?
幻读只是一个现象,我们可以通过业务代码逻辑去解决

A
lock.lock(12331213)
1.通过wx登录,select for update name where id = wx_id()
2.根据手机号插入用户数据 insert name where phonenumber  = 12331213(插入)
释放锁

B
1.通过zfb登陆 select for update name where id = zfb_id)
2.根据手机号插入用户数据 insert name where phonenumber  = 12331213(在A释放锁之前,无法插入,只有A插入成功后释放锁,才可以插入,但此时已经不需要插入了,因为上一条通过支付宝id查到的数据已经有电话号码)

select * from device_weight_record where limit 100000, 20;
Select * from device_weight_record where id > 100000 LIMIT 20;

按需查询字段,减少网络IO消耗

避免使用select * 减少MySQL优化器负担

查询的字段尽量保证索引覆盖

使用nosql缓存数据缓解MySQL数据库压力

0

1

2

表中只有一行数据,MySQL会扫描这一行

查询条件是唯一索引(主键)

SELECT * FROM orders o JOIN customers c ON o.customer_id = c_id
(id是主键)  使用唯一索引进行连接查询

使用非唯一索引(普通索引)查找,查询条件可能返回多行

索引范围扫描

使用索引全表扫描

没有使用索引全表扫描

查询只需通过索引获取数据

查询需要创建临时表来存储中间结果
select count(*),city from users group by city order by city
(由于order by 和 group by,且city没有合适的索引,MySQL分组后需要使用临时表对数据进行排序)

MySQL需要额外的排序操作来返回结果
select id,name from users order by name(如果name列没有索引,MySQL需要额外排序操作)

select count(*), city from users group by city(有索引city,MySQL使用索引来进行分组)

问题

如果脏页还没来得及刷入磁盘,mysql宕机了,不就丢失数据了吗
这个不用担心,InnoDB的更新操作采用的是write ahead log策略,即先写日志,再写入磁盘,通过redolog日志让mysql拥有了崩溃恢复能力

触发脏页刷磁盘的情况