https://note.youdao.com/ynoteshare/index.html?id=53cd90b2a1d930e1f5c7558a3f595696&type=notebook&_time=1655133645036
实际上我们的理解的mysql就是存储数据的,我们可以通过可视化工具方便的对mysql进行数据的操作,但是mysql也是一个服务,对于开发mysql的人员来说以及我们后续开发服务时可以学习到的设计思想是分层,以及扩展性,mysql的扩展性主要体现在多种存储引擎的任意使用(可插拔的方式)
查询时小表驱动大表,因为每次表之间的关联操作实际上是IO操作,因此减少IO处理。
mysql server的分层:连接层、服务层(分析器、优化器)、存储引擎(事务实现是在该层)、文件存储
其中服务层的Optimizer会对sql做一些优化
如果没有命中查询缓存,就要开始真正执行语句了。MySQL 需要知道你要做什么,因此需要对SQL语句做解析。
SQL语句的分析分 为词法分析与语法分析。
存储引擎的设置实际上是基于表的,不同的表可设置不同的存储引擎
mysql的存储引擎都是采用B树的结构,并且使用的都是B+树(B树的改进),其特点就是只有在叶子节点存储数据
那么mysql为什么会选择采用B+树存储?而不使用二叉树、红黑树、hash表
相同点: 每个节点都会使用磁盘页的方式,可以存储多个数据
不同点:
其中存储引擎有很多种,比较常用的是MyISAM、INNODB(5.5之后默认的)
myISAM
INNODB
两个存储引擎B+树存储数据的区别:
解释:存储的磁盘地址,主要依赖于数据的存储位置,要是存储位置变了,磁盘地址就需要改变,但是存储的是主键值时,不管数据的实际存储位置如何改变,都不会影响索引的调整。
MyISAM 的B+树只存储磁盘地址,这样不管查询的内容是否是索引内容,都需要再根据指针查询真正的数据,而INNODB要是在查询的字段或者排序的字段是索引列时,只需要在索引文件中查询就可以得到数据,只有查询索引列以外的字段,才需要根据主键索引去数据文件中找。
什么是回表
在非聚簇索引下通过主键到聚簇索引中查询数据的过程
为什么INNODB表建议都要创建一个主键索引?
因为INNDB底层存储索引的结构就是使用B+树,因为在构建B+树的时候终归是要找到一个基点,要是不创建主键索引的话,mysql会自己找到一列不重复的作为唯一索引处理,要是所有列都没有完全不重复的数据,那么mysql就会自动的创建一列,自增
为什么建议使用整型自增?
在构建B+树时,总会有数据的比较,因为B+树是要构建成有序的,字符串的比较还要转换为ASCII码,显然整数的比较要比字符串快;自增的主要是为了防止页分裂
InnoDB将数据划分为若千个页,InnoDB中页的大小默认为16KB。
以页作为磁盘和内存之间交互的基本单位
纠正一点:上图中的大小,单位是字节,不是kb
InnoDB存储引擎是以页为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页面(包括读页面、写页面、创建新页面等操作)。而磁盘I/O需要消耗的时间很多,而在内存中进行操作,效率则会高很多,为了能让数据表或者索引中的数据随时被我们所用,DBMS会申请占用内存来作为数据缓冲池,在真正访问页面之前,需要把在磁盘上的页缓存到内存中的Buffer Pool 之后才可以访问。
这样做的好处是可以让磁盘活动最小化,从而减少与磁盘直接进行I/O 的时间。要知道,这种策略对提升SQL语句的查询性能来说至关重要。如果操作的数据在缓冲池里,那么访问的成本就会降低很多。
实际上,当我们对数据库中的记录进行修改的时候,首先会修改缓冲池中页里面的记录信息,然后会以一定的频率刷新到磁盘上。注意并不是每次发生更新操作,都会立刻进行磁盘回写。缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上,这样做的好处就是提升了数据库的整体性能。这里所说的刷盘是将redo log buffer中的内容写到redo log file文件中(磁盘),注意,这里也只是操作的redo log文件,并不是真正数据库对应的表文件(redo log正式WAL的很好体现,后续再讲WAL)。
当缓冲池不够用时,需要释放掉一些不常用的页,此时就可以强行采用checkpoint的方式,将不常用的脏页回写到磁盘上,然后再从缓冲池中将这些页释放掉。这里脏页(dirty page)指的是缓冲池中被修改过的页,与磁盘上的数据页不一致。
Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间,当缓存池的大小>1G时,建议将实例调大,默认为1,因为数据量变大或者并发访问量高时,要是只有一个实例,会影响访问效率的,但是也不是越多越好,适当即可
聚簇索引、非聚簇索引、唯一索引、联合索引、单值索引、覆盖索引、前缀索引
测试要是在单值索引和联合索引一起使用时,优先使用联合索引
正常的sql select * from a where XX前添加explain关键字就可以对该条sql进行性能分,执行结果如下字段:
explain 关键字-mysql的解释执行,可以用此来分析sql的效率,其结果为:
id、select_type、table 、type 、possiable_keys、key、key_len、ref、rows、extra
id参数主要是用来判断一条多表查询sql,执行的先后顺序
id 相同时,表的执行顺序是从上到下
id不同时,id值越大的,越先执行,优先级越大
select_type 指明select的类型:simple、primary、subquery、derived(衍生 from结构的子查询)、union、union result
type 指明索引的级别:级别从大到小
system>const>eq_ref>ref>range>index>all(全表扫描),还会有Index_merge(同一张表中对多个索引做的优化处理)
在分析sql时,至少要达到ref、range
ref :使用索引并查询出多条数据,eq_ref:使用索引只查询出一条数据,一般都是唯一索引
between > < in like‘a%’等级别是range
index虽然也是全表扫描,但是其走的是索引,all走的是硬盘
extra 其他比较重要的属性
Using filesort(不好):一般在使用了order by /group by 时,会出现该属性,该属性表明sql在执行的时候进行内部排序了,一般情况就是在使用复合索引的时候,最好是按照顺序和个数同时使用,比如复合索引共有3个字段,where 1= order by 3(执行时有Using filesort),但是where 1= order 2,3(无Using filesort)
说明:order可以结合where 条件共同使用复合索引,group by和order by 基本是一样的,group by在分组时首先做了排序,然后又进行的分组
Using temporary(不好):一般在使用group by 的时候,要是没有完全按照复合索引的顺序,不但会引来Using filesort,也会引来Using temporary,都是比较影响性能的。
Using index(好事):使用到覆盖索引的就会有该参数,什么是覆盖索引,就是select d的字段是复合索引(或者是复合索引的部分,顺序可不一致,索引type=index),但是要求是复合索引的字段数量一定要>=select的字段数量才叫覆盖索引,因此这也是select 时候尽量不要使用*的其中一个原因;另外使用到索引下推的时候也会有此标识,索引下推一般出现在联合索引使用时。
特殊说明下:在对于多表关联查询时,比如使用的join语句,那么关联条件需要建索引的时候,在从表建索引会生效,否则索引不生效。
union 不光有合并的作用,同时还有去重的作用
执行了explain的语句,一般情况下通过type,key,extra等字段就可以看sql是否走索引,同时key_len可以判断索引使用字段的个数,一般情况下,使用的查询条件越多,key_len的值越大
索引失效的原因:
1)like:查询条件中有like的,要是like的索引生效,如:like ‘ab%’,则type类型是range,但是和>不同的是,>也是range ,但是>后面的查询条件不走索引了,但是like ‘ab%’ 自身和like后面的都会走索引的。要是使用like ‘%ab’,那个like自身的条件也不走索引了,并且后面的也不走了,sql最终是否走索引需要看like 之前的条件是否使索引。
2)where 条件后的查询条件顺序可以不和索引顺序完全一致,mysql会自动将顺序排好,mysql在执行的时候会有优化器将sql进行优化,同时不会引来Using filesort。
3)一条sql在执行的时候,可能会使用到多个索引,在对于同一张表的多个索引时,会出现索引合并的情况,不同表中是不会合并的,要是出现索引合并的情况type属性写的是Index_merge,key也会列举出所有使用到的索引。
4)in 是可以正常走索引的,type = range,但是not in不走索引了,所以在使用not in时可以使用not exists替换
5)关于使用in还是exists的区别:
“外层查询表小于子查询表,则用exists,外层查询表大于子查询表,则用in,如果外层和子查询表差不多,则爱用哪个用哪个。”
6)使用is null 或者是is not null 会导致索引失效,所以我们在创建表结构的时候尽量要定义为非null,并且要设置默认值,同时对于库表中可为null的字段,在存储的时候也会额外占用空间,使用bit位进行存储。
7)使用or关键字的话,就不会走到索引了,type = index,虽然也是全表扫描,但是比all好一些。
8)索引的重要性:不光是为了查询的效率,因为命中了索引的sql查询效率肯定是快的,另一方面,合理的索引以及正确的使用索引可以避免行锁升级为表锁。
1)索引是排序好的快速查询的数据结构,不光是应用于查询,还用于排序
2)在更新操作的时候有索引会影响效率,因为mysql不光要维护数据,还要维护索引,要是做更新操作,索引文件就会重新生成
介绍
Index Condition Pushdown(ICP)是MySQL 5.6中新特性,是一种在存储引擎层使用索引过滤数据的优化方式。索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理。索引下推的目的是为了减少回表操作
join分为inner join、left join、right join、full join,在进行join时首先遵从2个原则,一个是用小结果集的表作为驱动表,另外一个就是尽量在被驱动表关联字段上添加索引。
join连接多个表有不同的方式,但本质都是各个表之间数据的循环匹配。MySQL5.5 版本之前,MySQL只支持一种表间关联方式,就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大,则join关联的执行时间会非常长。在MySQL5.5以后的版本中,MySQL通过引入BNLJ算法来优化嵌套执行。
mysql的优化器在进行inner join时,首先会优化使用哪张表作为驱动表,哪张作为被驱动表,原则:小表作为驱动表
前面的表作为驱动表
嵌套循环主要做的操作就是用驱动表,匹配被驱动表
要求:为了减少内表数据的匹配次数,在被驱动表中的关联条件上建立索引
说明:一致性是事务的最终目标,而隔离性是事务要达到目标的手段,原子性是对一个事务的简单说明
AICD主要是通过InnoDb存储引擎的不同日志保证的,同时mysql的binlog日志也有一定的作用
A(原子性):undo log,要是事务需要回滚,执行undo log的数据即可,这里当前事务执行前一版数据
C(一致性):最终目标,由其他三个特性决定
I(隔离性):MVCC+锁决定
D(持久性):内存+redo log日志(commit标志)+binlog
mysql默认使用的是rr,oracle默认是rc
更正下,幻读是两次读取结果集的行数发生了改变,多了或者少了
幻读的解决办法:https://www.cnblogs.com/xuwc/p/13873293.html
sql读取分为当前读和快照读,而mvcc是在快照读的情况下解决了幻读,但是在当前读的情况下需要通过间隙锁+行锁解决幻读
1)a事务两次select,b事务insert,由于readview的原因可以使得两次select结果相同。
2)再就可以利用当前读也可以做到,select update select ,另外一个事务insert,要是update的操作可以产生间隙锁,并且insert的恰好是间隙锁间数据,会导致a事务提交前,insert被阻塞
出现幻读的情况:
1、select (insert) update(由于是当前读,获取的是最新数据,并且在修改后将那条数据的事务ID修改成当前事务) select
当前读:
select * for update
select * lock in share mode
insert delete update
对于范围条件筛选的,可以通过间隙锁方法阻止insert等操作
对于单条件的查询要是唯一索引查询,那么使用的行锁,要是非唯一索引则是使用的间隙锁。
mvcc是多版本并发控制,主要是将版本链+readview(是一组活跃事务的id),mysql在事务开启的时候,认为就会产生readview,并且在当前事务没有结束时始终使用的都是同一个readview。而快照读依赖的就是这个readview,当我们要去访问一条数据时,首先会判断该条数据记录的最大的事务ID,然后拿该值和readview中的事务ID进行比较,要是在readview的左边说明是当前事务开始前已经完成的事务,可以对该条数据进行访问。要是在readview中或者是在这之后说明在当前事务开始之后,有别的事务对该条数据进行过修改,所以就要访问这条数据上一个版本的信息。至于多个版本的生成,实际上个人理解应该是该条数据的主键是不变的,不然无法定位到这条数据。
redo log叫做重做日志,是保证事务持久性的重要机制。当mysql服务器意外崩溃或者宕机后,保证已经提交的事务,确定持久化到磁盘中的一种措施。redo log file是一个存储在磁盘中的文件。默认是在mysql服务的var/lib/mysql目录下。在主从的结构中,主服务器的恢复主要通过redo log完成的。
redo log file有两个组成部分,一个是redo log buffer ,其是在内存中的一块空间。另一个才是磁盘中的redo log file。
redo log buffer的大小默认是16M,最大4096M,最小1M。一个redo log buffer又分为很多个redo log block 一个block的大小为512字节。
可以通过设置 innodb_log_buffer_size设置大小
0:当commit的时候,什么都不做,由存储引擎后台的线程定时执行。定时任务是每1s执行一次,注意:这里由于刷盘的动作由定时任务处理了,所以刷到磁盘中的内容有可能是未提交事务。后面还得由undolog回滚。
(缺点:有可能定时处理时没有commit,所以有可能将未提交的事务也刷盘了)
1:commit时,将redo log buffer中的数据同步到pach cache(操作系统层面),刷盘到redo log文件(磁盘)
2:commit时,将redo log buffer中的数据同步到pach cache(操作系统层面),由系统决定啥时候刷盘。相对来说,也是比较保险的,因为mysql宕机的话还是可以持久的,只有操作系统宕机了,才会丢数据。
1也是最耗时的,其次是2,最快的是0。但是1是能确保持久化的,其他两个都不能确保持久化
硬盘上存储的 redo log 日志文件不只一个,而是以一个日志文件组的形式出现的,每个的redo日志文件大小都是一样的。比如可以配置为一组4个文件,每个文件的大小是 1GB,整个 redo log 日志文件组可以记录4G的内容(文件的大小和个数均可通过配置调整)
它采用的是环形数组形式,从头开始写,写到末尾又回到头循环写,如下图所示(类似log4j)
在个日志文件组中还有两个重要的属性,分别是 write pos、checkpoint
每次刷盘 redo log 记录到日志文件组中,write pos 位置就会后移更新。
每次 MySQL 加载日志文件组恢复数据时,会清空加载过的 redo log 记录,并把 checkpoint 后移更新。
write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录
如果 write pos 追上 checkpoint ,表示日志文件组满了,这时候不能再写入新的 redo log 记录,MySQL 得停下来,清空一些记录,把 checkpoint 推进一下。
binlog是mysql的服务层维护的日志,记录形式顾名思义就是二进制,记录的内容是DDL和DML等数据库更新事件的语句,event事件。查询的sql不会被记录,原因:1、通用查询日志中记录的是最全的日志,包括查询了。2、binlog日志的作用是进行数据的同步和恢复,查询语句没有意义。建议binlog日志不要和其他日志放在一起,避免全军覆没
每当服务器重启的时候,都会新生成一个binlog文件
要是查看时间戳使用mysqlbinlog
要是查看position使用events
purger master log to ‘文件名’
purger master log befor ‘时间戳’
reset master
write和fsync的时机,可以由参数sync_binlog控制,默认是0,其工作流程类似于redolog.
0:表示每次提交事务都只write,由系统自行判断什么时候执行fsync
1:表示每次提交事务都会执行fsync
2:可以设置为N(N>1), 表示每次提交事务都write,但累积N个事务后才fsync
这里说的主要是针对主从结构中,主机和从机恢复时数据不一致问题,也就是redolog和binlog不一致问题。
mysql采用两阶段提交的方式,预防服务恢复时数据不一致的问题
这里就相当于预分单中或者是派单开始时,都要设置一个初始状态,表明该环节已经开始,至于执行结果,要再次设置值。也就是只要是记录redolog日志了,就算做是准备阶段,只有最终完成binlog的写入,才算是最终的提交,要设置commit标识
对于当前事务来说,读锁和写锁都是从权限上有限定的。
1、两种锁对锁表的事务有不同的权限,读锁只能读锁定表,写锁可以读写锁定表
2、不管是读锁还是写锁对于当前在使用锁的事务,都不可以对其他表做任何操作
从别的事务的操作来看,只对上锁的表会出现阻塞的情况,其他没有上锁的表是可以正常访问的。读锁是共享锁,可以读不可写,写锁是排他锁,读写都不可以
总结:读锁会阻塞写,写锁会阻塞读写,myISAM在进行查询操作前都会加上读锁,在进行更新操作时都会记上写锁
show open tables;–查看哪些表被锁,0-未锁,1-锁
MyISAM支持表锁,INNODB支持行锁,一般也都是给更新语句加行锁,要是给select语句加行锁的话,需要手动的添加for update,注意:对于行锁使用不当的情况下,会升级为表锁
一个区间中数据库中不存在的数据
例:主键数据有1 3 4,更新时条件为>1 and ❤️,此时虽然没有2,但是也会产生间隙锁,要是insert 2时就会有阻塞了。或者select * from table where id = 2 for update;此时会产生间隙锁,insert 2时也会阻塞。
事务中,更新和删除不存在的数据时,会生成间隙锁,那么在进行insert操作时,可能出现死锁情况
原因:
1、更新、删除数据库不存在的数据时,会产生间隙锁
2、间隙锁可以重复加
3、间隙锁阻塞insert,但是不会阻塞update
4、上述操作,session1执行update/delete,那么在事务提交前就会阻塞session2的insert,同时session2执行update/delete,阻塞session1的insert,这样就互相等待了
对于mysql来说,最主要是配置文件,windows下是my.ini,linux下是my.cnf
要求:1、主从数据库版本一致
2、必须是同一网段,最起码能ping通
3、关闭防火墙
1、修改主库配置文件
【必填】server-id = 1 (一般情况下主的都设置为1)
【必填】log-bin = 设置生成的binlog日志文件的名称
2、重启服务
3、master、slave都关闭防火墙
4、master上建立账号授权给slave,直接用mysql的命令行执行
这个是5.5版本之前的可以用一条指令就可以,要是8.0版本还需要加个指令。
5、show master status; – 查看主机的状态,重要的两个参数就是file和position,这里是作为同步的位置,需告知slave
1、修改从库配置文件
server-id = 2
#[可选]启用中继日志
relay-log = mysql-relay (该值尽量不要修改)
2、关闭防火墙
3、slave 连接master做同步
3、start slave;
4、show slave status:
show-IO,show-sql 必须同时为YES,否则会导致同步失败
接下来正常在master上做操作即可,自动会同步到slave
数据备份 : mysqldump –user=root –password=root密码 –lock-all-tables 数据库名 > 备份文件.sql
数据恢复:mysql -u root –password=root密码 数据库名 < 备份文件.sql
当str为null时,不能成功拼接,因此拼接时需要使用IFNULL()判断下
str为数据库中字段名称,substr就是要判断的字符串,若str字段中不包含substr的值则返回0,若包含则返回1,但需要注意当str(数据库字段)为null时,返回null
由于子查询会将子查询的查询结果生成一张临时表,并且要是对于大结果集查询来说,性能都会受到影响,因此建议使用join连接替换子查询
table-strategy: 表分片策略
分片策略
这一点我们直接在sql中使用分片字段进行查询即可,就可以使用上分片字段,找到对应的库和对应的表,要是没有查询字段的话,那么查询的就是全部的库和所有的表了。
创建一个中间表,里面只存储order_id和user_id,要是数据量也比较大的情况下,可以也对这张表进行分库分表,然后以order_id作为分片字段,找到对应的user_id就可以再进行查找了。其实就是找个地方存储一下他们的对应关系,匹配上就行。
将分片键的信息保存在想要查询的列中,这样通过查询的列就能直接知道所在的分片信息,这种方法叫做基因法。或者在我们的分片字段中保存上我们要查询的内容,比如我们的分片字段是orderId,但是我们要查询的字段是userId,所以我们就把userId通过一定的规则放在orderId中就行。
这样我们在使用orderId还是使用userId都可以通过分片策略找到对应的表
引入的pom包和分库分表一样,这里只说明配置。
有话要说...