当前位置:网站首页>mysql 总结

mysql 总结

2022-08-09 06:29:00 史上最强的弟子

mysql 

引擎:
innodb  聚簇索引 (叶子节点存了所有的行数据)
myisam   非聚簇索引(叶子节点存行id)

事务的隔离级别
    ACID 
    原子性:事务作为最小执行单位,事务要么全部被执行,要么全部不执行。
    一致性:事务的执行使得数据库从一种正确状态转换成另外一种正确状态。
    隔离性:事务与事务之间互不影响。
    持久性:事务正确提交之后,其结果将永远保存在数据库之中,即使在事务提交之后有了其他故障,事务的处理结果也会得到保存。
事务的隔离级别:
    read uncommit:  读到未提交的事务数据。 (脏读,不可重复读,幻读)
    read commit  :  读到提交后事务的数据。  (不可重复读,幻读)
    repeat read  :  多次读取的数据都是一样的。 (幻读)mysql 默认
    searial      :  串行化,一个事物一个事物的执行。

事务的实现原理是 undolog 和redolog 一起来决定的。
1.数据库表行数据隐藏字段 DB_TRX_ID (当前事务ID),DB_ROLL_PTR(回滚指针,对应的是undolog),DB_ROW_ID(6byte,隐含的自增ID(隐藏主键),如果数据表没有主键,InnoDB会自动以DB_ROW_ID产生一个聚簇索引)
2.undolog 保证了事务提交之前数据操作的反向日志,比如说:insert 操作,undolog 会保存delete 相反的语句,这样就可以实现事务的回滚。
3.redolog 物理日志,记录的是数据页的物理修改,用来恢复提交后的物理数据页。解决了mysql 宕机,可以恢复数据。
4.binlog 数据修改的操作日志,对slave 提供热备份功能。

索引B+tree 结构,DataPage

dataPage 默认大小为16k 

DataPage 里面包含了多个data (B+tree 的叶子节点)
这里要注意为什么要求id 最好自增,自增的话不会破坏老的datapage ,(3,5) 插入4 就会变成 (3,4)(5)这样就会对datapage 进行修改,还有移动数据的操作还会对索引树进行维护。


MVCC 多版本数据控制
多数据版本控制,实现原理undolog 来控制不同的数据版本,从而对于读和写互补影响。

索引分类:
1、主键索引
2、Hash
3、组合
4、唯一索引
5、全文索引


查询索引注意:


1.索引下推: mysql 5.6 之后 ,执行计划中Using index condition.表示使用索引下推 。 例如: (A,B) 为索引  C 是普通条件,查询ABC 没有索引下推会通过执行引擎查询AB 数据后在内存里面在对于C 进行筛选,开启索引下推C条件传给了引擎,直接返回结果。

2.索引覆盖: 索引覆盖的意思是查询的数据在索引中能够获取到值,不用在做回表操作

3.最左匹配原则: 组合索引,(A,B,C)where A= and B= and C= 命中,注意   where  B= and C= and where A= and C= 无法命中

4.回表:  根据索引查询主键ID ,再通过主键ID 查询主键b+tree 这样可以取到全数据的data


索引失效
1.in() 过多的数据,优化器会认为数据量过大,还不如走全表。
2.or ,like '%%'和'%'',函数等都会导致索引。
3.索引的长度,索引优先选取长度短的。
4.条件不一致,"1" 使用  1 查询 。
5.!= 不走索引

执行计划
explain select * from 


id  select_type table partition type prossiblekey key key_len ref rows filtered extra


1.select_type:
SIMPLE 简单查询
PRIMARY 主键最外层查询
SUBQUERY 映射为子查询
DERIVED 被驱动只查询
UNION 联合
UNION RESULT 使用联合结果

2.table: 表名

3.type 类型
ALL 全表
index  全索引查询
RANGE 范围索引查询
INDEX_MERGE 合并索引
CONST 常量


4.possible_keys : 可能使用的索引

5.key : 真实使用的索引

6.key_len : MySQL中使用索引字节长度

7.rows : mysql 预估为了找到所需的行而要读取的行数

8.filtered : 过滤多少条数据

9.extra:
Using index:    此值表示mysql将使用覆盖索引,以避免访问表
Using where: mysql 将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。
Using temporary: mysql 对查询结果排序时会使用临时表
Using filesort: mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行


锁:
行锁,锁的是b+tree 叶子节点。
表锁,锁的是整个表。
排他锁,修改操作,insert delete update   共享锁指的就是对于多个不同的事务,对同一个资源共享同一个锁,只能一个修改 lock in share mode;(update,insert,delete)语句会自动加排它锁。
共享锁,查询操作 共享锁指的就是对于多个不同的事务,对同一个资源共享同一个锁,只能查询 for update。
乐观锁,update set A=1 where A=3 。
悲观锁,for update。
间隙锁,间隙锁是封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。

索引结构:
b+tree 和 b tree 区别
b tree   
    每个节点存值。
b+tree
    叶子节点存值。分支节点存的是范围值。所有的叶子节点组成了一个双向的链表结构,      并且多个叶子节点存放在一个DataPage ,这样可以减少磁盘的I/O 读写。减少了b+tree 的层级,从而缩减了查询的平均时间。

主键索引:
1.设置主键索引,没设置主键则会采用唯一索引,如果也没有唯一索引,那么会自动以DB_ROW_ID 产生一个6bit 的聚簇索引。

组合索引和唯一索引:
叶子节点存的数据是主键ID.

原网站

版权声明
本文为[史上最强的弟子]所创,转载请带上原文链接,感谢
https://blog.csdn.net/sai739295732/article/details/114030642