赞
踩
connect:不同语言的代码程序和mysql的交互(SQL交互)
1、连接池(connection pool): 管理、缓冲用户的连接,线程处理等需要缓存的需求
2、管理服务和工具组件(Management Service & Utilities) :系统管理和控制工具,例如备份恢复、Mysql复制、集群等
3、sql接口: 接受用户的SQL命令,并且返回用户需要查询的结果
4、查询解析器: SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)
5、查询优化器 :SQL语句在查询之前会使用查询优化器对查询进行优化;检查索引基数、搜索行数、确定是否走索引;确定映射字段、确定临时表、是否需要进行索引自动优化(FORCE INDEX强制使用索引
)
select id,name from user where age = 40;
# a、这个select 查询先根据where 语句进行选取,而不是先将表全部查询出来以后再进行age过滤
# b、这个select查询先根据id和name进行属性投影,而不是将属性全部取出以后再进行过滤
# c、将这两个查询条件联接起来生成最终查询结果
# 强制使用索引 inx_a,不允许自动优化
select * from table_name force index (idx_a) where a = 100;
6、缓存池组件: 如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据
7、插入式存储引擎(Pluggable Storage Enginess): 存储引擎说白了就是如何管理操作数据(存储数据、如何更新、查询数据等)的一种方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型);
8.文件系统及文件和日志: 数据和日志在硬盘的存储
索引:为了加快查询速度,而使用的一种加快查询速度的方法;对于不同的索引类别有不同数据结构;
在mysql中如果没有自定义默认使用的是BTree索引,对于BTree索引,复合索引的key的顺序十分重要,因为其符合最左前缀匹配原则;
索引包括:主键索引和辅助索引;
下面将围绕上图进行解析:
对于InnoDb而言:
索引可以分为:聚簇索引(主键索引)和非聚簇索引(辅助索引);
对于MYISAM而言:
主键索引和辅助索引的组织方式并无区别;
下面俩图分别时InnoDB的聚簇索引组织形式、叶节点数据;
上图可以看出聚簇索引数据(叶子节点)是以页(16k)的形式为单位保存的,每个页的第一个key为该页的最小值;非叶子节点也是以页的形式读出,这样就避免浪费IO资源(我们知道IO也是以页的形式读出,如果是MYISAM的索引结构,那么每个页其实有用的数据可能只有一个(其存储不是连续组织起来的)非常浪费IO资源)
上图可以看出叶子节点除了保存数据(即记录)还有主键列(或者聚簇的键)事务ID和回滚指针(其实它们也是记录的一部分,mysql的InnoDB会给我们创建的表加上上述隐式字段用于MVCC);
定义:聚簇索引也叫簇类索引,是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。聚簇索引的索引页面指针指向数据页面。每张表只能建一个聚簇索引,并且建聚簇索引需要至少相当该表120%的附加空间,以存放该表的副本和索引中间页。
特点:
下图位InnoDB的聚簇索引的优缺点:(还有一条是使用辅助索引查找时可能需要回表。另外缺点中第二条也是InnoDB推荐使用递增主键的重要原因)
定义:非聚簇索引不重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。
特点:
1.哈希索引只保存哈希值和行指针,不保存数据;
2.哈希索引数据并不是按照哈希值存储的所以页无法排序
3.哈希索引如果是组合索引(例如a、b、c同时作为索引的主键那么哈希值也是根据这三个同时算出),则无法使用于最左原则及部分索引;
4.哈希索引无法进行范围查询,范围查询会进行全表扫描
5.对于频繁哈希冲突:InnoDB会自动的创建BTree索引,而如果没有创建Btree索引缓解冲突那么查询时会遍历复合条件的哈希索引的每一行;
MySQL的BTree索引使用的是B+Tree的优化
1.平衡二叉树数据结构 (1)非叶子节点最多拥有两个子节点; (2)非叶子节值大于左边子节点、小于右边子节点; (3)树的左右两边的层级数相差不会大于1; (4)没有值相等重复的节点; 2.B树数据结构(多路平衡查找树,就是为了降低平衡二叉树的高度,加快查找效率) 阶:m (1)根结点至少有两个子女; (2)每个节点最多有m-1个关键字; (3)非根节点至少有m/2个关键字; (4)每个节点中的关键字都按照从小到大的顺序排列,每个关键字的左子树中的所有关键字都小于它,而右子树中的所有关键字都大于它; (5)所有叶子节点都位于同一层 3.B+树数据结构 和B+Tree的区别: (1)要保存的数据只存储在叶子节点 (2)B+Tree的叶子节点保存的数据间相互循环连接
(1)唯一索引:索引列的值必须唯一,但可以为null;
(2)单值索引:一个索引只包含一个列,一个表中可以有多个单列索引
(3)复合索引:多个列值按顺序组合成索引key
(4)覆盖索引:当一个key索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引
1.索引创建
Create INDEX indexName IndexType ON TableName(ColumName...)
2.索引删除
DROP INDEX indexName ON tabelName
3.ALTER语句
4.索引查看
SHOW INDEX FROM TableName
回表:回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低
常用存储引擎:InnoDB、MyISAM、MEMORY、MERGE、NDB
其他储存引擎:CSV、BLACKHOLE
特点 | InnoDB | NDB | MyISAM | MEMORY | MERGE |
---|---|---|---|---|---|
储存限制 | 有 | 有 | 有 | 有 | 无 |
事务安全 | 支持 | ||||
锁机制 | 行锁(高并发) | 行锁 | 表锁 | 表锁 | 表锁 |
B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
Hash索引 | 支持 | ||||
全文索引 | 支持 | 支持 | |||
集群索引 | 支持 | ||||
数据索引 | 支持 | 支持 | 支持 | ||
索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||||
空间使用 | 高 | 低 | 低 | N/A | 低 |
内存使用 | 高 | 高 | 低 | 中等 | 高 |
批量插入的速度 | 低 | 高 | 高 | 高 | 高 |
支持外键 | 支持 |
MySQL使用最多的俩种存储引擎是MyISAM和InnoDB,其中MySQL5.5之前默认使用MyISAM,5.5之后默认使用InnoDB;
特点:
使用场景:
非事物型应用( MyISAM不支持事务)、数据只读(或绝大多数情况只读);空间类应用(如存储GPS数据,支持空间函数,可应用空间函数对数据进行计算);
对表进行检查修复:
方式一:
检查:check table myIsam;
修复:repair table myIsam;
方式二:使用工具修复(如果使用命令行工具对myisam表进行修复的话,则需要咱Mysql服务停止,在mysql服务运行的同时,如果使用命令行工具对表进行修复,可能使表造成更加大的损坏)
myisamchk --help
https://zhuanlan.zhihu.com/p/158978012
后面的事务、锁和MVCC均基于InnoDB
要了解InooDB上面每个字段都很重要:
主要作用:
各线程:
InnoDB的存储引擎内存池:这是一个十分复杂的结构:
缓存池(buffer pool):缓冲池就是一块内存区域,主要缓冲数据页和索引页,可以通过innodb_buffer_pool_size设置缓冲池的大小,缓冲池通过LRU算法进行管理(最频繁使用的页在LRU列表前端,最少使用的页在末端);
读取数据时:首先判断数据所在的页是否在缓冲池中,如果不在则才读取磁盘对应的数据,当缓冲池数据满时使用LRU算法进行替换。需要强调的是从磁盘读取的页不是放在LRU链表的最前面,而是放到midpoint位置(默认为5/8处,之所以这么做是因为要保留mysql的热点数据,避免读取大量非热点数据时将热点数据刷掉);
页修改时:对页的修改操作,首先修改在缓冲池中的页,再以一定的频率(Checkpoint机制)刷新到磁盘。
重做日志缓冲:
重做日志先放到这个缓冲区,然后按一定频率刷新到重做日志文件。
参数:innodb_log_buffer_size
刷新的规则:
额外的内存池
内存堆,对InnoDB内部使用的数据结构对象进行管理
检查点机制(CheckPoint)
1.上文提到脏页数据刷新回磁盘使用的是检查点技术;首先只有增、删、改会产生脏页;而数据库使用的是write Ahead log策略,也就是说脏页数据不会立刻刷新到磁盘中;
2.当数据库出现问题是,为了恢复数据库的一致性,我们会使用undo log进行重做和回滚,为了避免使用整个日志进行undo和redo所以使用了检查点的技术;
3.当缓冲池不够用时,根据LRU算法会溢出最近最少使用的页,若此页为脏页则也要利用CheckPoint刷新数据;
4.当重做日志出现不可用时,*因为当前事务数据库系统对重做日志的设计都是循环使用的,并不是让其无限增大的,重做日志可以被重用的部分是指这些重做日志已经不再需要,当数据库发生宕机时,数据库恢复操作不需要这部分的重做日志,因此这部分就可以被覆盖重用。*如果重做日志还需要使用,那么必须强制Checkpoint,将缓冲池中的页至少刷新到当前重做日志的位置。
脏页:当进程修改了高速缓存里的数据时,该页就被内核标记为脏页,内核将会在合适的时间把脏页的数据写到磁盘中去,以保持高速缓存中的数据和磁盘中的数据是一致的
CheckPoint主要有俩种类型:
1.Sharp Checkpoint
2.Fuzzy Checkpoint
1.Master Thread Checkpoint
Master Thread每个1秒或10秒按一定比例将缓存池的脏页列表刷新会磁盘
2.FLUSH LRU LIST Checkpoint
Page Cleaner线程发现LRU列表中可用页数量少于innodb_lru_scan_depth(1024)【这个检查被放在了一个单独的Page Cleaner线程中进行,并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量,该值默认为1024】,就将LRU列表尾端移除,如果这些页中有脏页,就需要Checkpoint;
3.Async/Sync Flush Checkpoint
重做日志文件空间不可以用时,将一部分脏页刷新到磁盘。
4.Dirty Page too much Checkpoint:
脏页数量太多(超过比例innodb_max_dirty_pages_pct,默认75),执行Checkpoint。
文件:磁盘上的数据文件叫表空间文件;表空间包括系统表空间、undo log 表空间、甚至可以让create出来的每张table都有本身单独的表空间
1.InnoDB默认采用的事务隔离级别是可重复读
2.InnoDB采用MVCC来应对高并发的读写、写读问题
3.InnoDB使用间隙锁防止幻读
4.如上文InnoDB使用的是聚簇索引(主键索引)加二级索引(非聚簇索引)
5.以页的形式管理和组织数据
https://blog.csdn.net/weixin_33766168/article/details/92676952
1、共享表空间和独占表空间介绍 共享表空间以及独占表空间都是针对数据的存储方式而言的。 共享表空间: 每一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1 初始化为10M。 独占表空间: 每一个表都将会生成以独立的文件方式来进行存储,每一个表都有一个.frm表描述文件,还有一个.ibd文件(这个文件包括了单独一个表的数据内容以及索引内容)。 2、共享表空间和独占表空间的区别 共享表空间: 优点: 1)可以放表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上)。 所以其大小限制不再是文件大小的限制,而是其自身的限制。从Innodb的官方文档中可以看到,其表空间的最大限制为64TB,也就是说,Innodb的单表限制基本上也在64TB左右了,当然这个大小是包括这个表的所有索引等其他相关数据。 2)表数据和表描述放在一起方便管理。 缺点: 1)所有的数据和索引存放到一个文件中,将有一个很常大的文件,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日志系统这类应用最不适合用共享表空间。 独立表空间(在配置文件(my.cnf)中设置innodb_file_per_table=1): 优点: 1)每个表都有自已独立的表空间。 2)每个表的数据和索引都会存在自已的表空间中。 3)可以实现单表在不同的数据库中移动。 4)空间可以回收。 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理(表空不能自已回收),处理方式如下: Drop table操作自动回收表空间 如果对于统计分析或是日志表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩 5)使用独占表空间的效率以及性能会更高一点。 缺点: 1)单表增加过大,如超过100个G: 当使用独享表空间来存放Innodb的表的时候,每个表的数据以一个单独的文件来存放,这个时候的单表限制,又变成文件系统的大小限制了。 3、共享表空间以及独占表空间之间的转化 修改独占空表空间配置,以下几个参数必须在一起加入 innodb_data_home_dir = "/usr/local/MySQL/var/" 数据库文件所存放的目录 innodb_log_group_home_dir = "/usr/local/mysql/var" 日志存放目录 innodb_data_file_path=ibdata1:10M:autoextend 设置配置一个可扩展大小的尺寸为10MB的单独文件(共享数据文件),名为ibdata1。没有给出文件的位置,所以默认的是在MySQL的数据目录内(如 /db/mysql/ibdata1)。 innodb_file_per_table=1 是否使用共享以及独占表空间(1 为使用独占表空间,0 为使用共享表空间) mysql>show variables like "innodb_file_per_table" on=1=独立表空间 off=0=共享表空间 innodb_file_per_table 通过这个参数来实现的转化,如果为OFF说明所使用的是共享表空间【默认情况下,所使用的表空间为共享表空间】 innodb_file_per_table值来进行修改即可,但是对于之前使用过的共享表空间则不会影响,除非手动的去进行修改
通过上述描述redo log大致数据结构为:
除此之外考虑到操作系统也有缓冲的概念:传统的UNIX实现的内核中都设置有缓冲区或者页面高速缓存,大多数磁盘IO都是通过缓冲写的。当你想将数据write进文件时,内核通常会将该数据复制到其中一个缓冲区中,如果该缓冲没被写满的话,内核就不会把它放入到输出队列中。因此需要用到fsync系统调用,来刷新某一文件的缓冲到文件中保证当InnoDB从自身redo log buffer提交write后,系统不会等待系统的write buffer满后再写,而是直接进入write队列开始写入文件;
首先上图展示了Page5是mysql的InnoDB保存回滚段数组位置;回滚段的组织形式;**所有回滚段均有1024个槽位;**下面的图将展示这部分细节;
在了解完重要的特征后,需要说明的是InnoDB为了在undo log实现MVCC功能在我们创建的表中加上了隐式字段
首先不考虑redo log记录undo log、页分裂、事务回滚、事务id自增和只读事件等;下面是简要的update过程
delete的过程类似,不过只是这里修改的数据是delete_flag字段;同样该行数据不会被删除知道清理线程满足条件清理该数据前该数据一直存在;
看到这里或许你已经知道其怎么实现原子性的了:首先通过undo log保证事务要么全部执行要么均不执行,而undo log记录的是逻辑日志,我们知道InnoDB使用的是行级锁结构,如果使用物理日志那么undo log造成的页分裂后又回滚就会影响其他事务执行(可能)后的结果;redo log之所以可以使用物理日志(redo log使用物理日志是为了恢复的时候快速),redo log本身就不是按事务来写的所有操作均会被并发记录到redo log中,同时页又会有逻辑版本号LSN,不存在这样的问题;
crash-safe
能力的1.原子性:Atomicity,数据库事务是不可分割的最小工作单位
2.一致性:Consistency,事务将数据库从一种一致性状态转变为下一种一致性的状态
3.隔离性:Isolation,每个读写事务的对象对其他事务的操作对象在提交前不可见
4.持久性:Durability,事务一旦提交,其结果是永久性的
1.脏读(读写锁\多版本并发控制)和丢失修改(写锁):事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据;丢失修改就是事务A修改后,事务B也进行了修改(导致事务A的修改丢失)
2.不可重复读:事务 A 多次读取同一数据,期间事务 B对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
3.幻读:事务A修改了某一表的全部行,让其 x=1,然后读取的时候发现读取到了存在 x=2。
由上图可以看出mysql的InnoDB的四种隔离级别分别是:
在了解了InnoDB的隔离级别后重点关注mysql如何实现各个隔离级别或说InnoDB如何实现ACID;
https://www.cnblogs.com/f-ck-need-u/p/9010872.html#auto_id_8
LSN称为日志的逻辑序列号(log sequence number),在innodb存储引擎中,lsn占用8个字节。LSN的值会随着日志的写入而逐渐增大,而且每次修改的记录的重做日志字节数就是LSN增大的数,重做日志总量就是当前LSN-开始的LSN序号。由上述信息可以得出:LSN和下面三个数据相关:
1.数据页的版本信息。
2.写入的日志总量,通过LSN开始号码和结束号码可以计算出写入的日志量。
3.可知道检查点的位置。
上图是关于LSN和数据记录页、redo log、check point的关系;上图为简化的情况,不考虑事件由redo log buffer提交到 redo log file和错误情况及data buffer因另外俩种原因提交到data file;下面是上图各个字段的解释;
innodb从执行修改语句开始:
(1).首先修改内存中的数据页,并在数据页中记录LSN,暂且称之为data_in_buffer_lsn;
(2).并且在修改数据页的同时(几乎是同时)向redo log in buffer中写入redo log,并记录下对应的LSN,暂且称之为redo_log_in_buffer_lsn;
(3).写完buffer中的日志后,当触发了日志刷盘的几种规则时,会向redo log file on disk刷入重做日志,并在该文件中记下对应的LSN,暂且称之为redo_log_on_disk_lsn;
(4).数据页不可能永远只停留在内存中,在某些情况下,会触发checkpoint来将内存中的脏页(数据脏页和日志脏页)刷到磁盘,所以会在本次checkpoint脏页刷盘结束时,在redo log中记录checkpoint的LSN位置,暂且称之为checkpoint_lsn。
(5).要记录checkpoint所在位置很快,只需简单的设置一个标志即可,但是刷数据页并不一定很快,例如这一次checkpoint要刷入的数据页非常多。也就是说要刷入所有的数据页需要一定的时间来完成,中途刷入的每个数据页都会记下当前页所在的LSN,暂且称之为data_page_on_disk_lsn
https://blog.csdn.net/qq_39459385/article/details/84644005
MVCC和锁机制
首先回顾一下数据库理论课学的读、写锁和意向锁;
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
共享锁(读锁,s锁) | 允许事务读锁定的数据,多个事务可同时加上该锁 |
---|---|
排他锁(读锁,x锁) | 一个事务加上该锁后只允许该事务在锁定部分执行读写操作 |
意向排他锁(IX锁) | 事务想要获得一张表中某几行的共享锁,这里主要涉及多粒度封锁协议(下同) |
意向共享锁(IS锁) | 事务想要获得一张表中某几行的排他锁 |
多粒度封锁协议:存在一个多粒度树,该粒度从上至下粒度的精细程度递增,例如粒度树为:数据库–>表–>前后多行–>跳跃本数据的前后多行—>本数据行加下一行–>本数据行
意向锁就是对一个节点加意向锁,则该节点的下层节点正在加锁
乐观锁:假定并发的事物在处理时不会彼此互相影响,只在提交时检查有没有其它事物修改了该数据,冲突回滚重新执行;
悲观锁:并发的事物在处理时都会引起并发冲突,每次操作数据的时候都会上锁,实行先取锁再执行的策略;
乐观锁常用的实现方式就是:数据版本Version方式和CAS机制方式;
数据版本就是给字段添加一个属性version作为版本,version是一个递增的版本字段,每次获取需要更新的数据时把version字段页一并获取,将version+1再将字段写回(更新),更新时比较版本号是否大于数据库表中version的版本号,大于则写回否则重做;
CAS机制比较复杂:
MYISAM使用的是表级锁结构;
MYISAM的加锁和InnoDB有很多的不同;在MYISAM中加锁时:
首先回顾两段锁协议:是指所有的事务必须分两个阶段对数据项加锁和解锁,在申请锁阶段不释放锁,释放锁阶段不申请锁;(两段锁协议可以保证调度是可串行化的调度,可串行化的调度就是多个事务的并发执行是正确的,当且仅当其结果与按某一次序串行地执行它们时的结果相同,我们称这种调度策略为可串行化的调度;保证可串行化调度可以让事务并发执行);
InnoDB使用的是MVCC加两段锁协议实现并发控制;
首先InnoDB支持多粒度锁(行锁和表锁)对于表锁和MYISAM类似支持读锁和写锁,还存在一类特殊的锁自增锁;重点介绍行锁;
自增锁是一种特殊的表级别锁(table-level lock),专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况,如果一个事务正在往表中插入记录,所有其他事务的插入必须等待,以便第一个事务插入的行,是连续的主键值。
MySQL的InnoDB行锁是加在索引的叶子节点上,也就是说只有通过索引查询才会使用行锁否则使用的是表锁,也就是说全表扫描都使用的是表锁;除此之外,MySQL的InnoDB默认使用的是快照读(RR模式),所以一般的SELECT语句不会加锁;只有当前读才会加锁;
有了以上基础,下面介绍类型;行锁主要包括:读、写锁(S、X)、意向锁(IS、IX)、间隙锁(Gap Locks、Next-key Locks、Insert Intention Locks)、记录锁(Record Locks);
加锁规则
读、写锁:
默认情况下SELECT语句使用的是MVCC快照读,并不会加锁;
可以通过在SELECT语句尾部加上lock in share mode表示加上读锁(共享锁);
可以通过在SELECT语句后尾部上for update表示加上写锁(间隙锁);【主要解决当前读和快照读的幻读问题】
insert、update、delete会自动加上写锁;
意向锁:加锁规则和上文介绍一样;
间隙锁和记录锁
间隙锁主要是为了解决当前读的幻读问题(MVCC已经解决了快照读的幻读问题);
间隙锁和记录锁:
Gap Locks(间隙锁):对索引项之间的间隙加锁,加锁之后索引间隙范围内不允许插入数据;
Insert Intention Locks(插入意向锁):
Insert Intention Locks意为插入意向锁,插入意向锁是Innodb gap锁的一种类型,这种锁表示要以这样一种方式插入:如果多个事务插入到相同的索引间隙中,如果它们不在间隙中的相同位置插入,则无需等待其他事务。比如说有索引记录4和7,有两个事务想要分别插入5,6,在获取插入行上的独占锁之前,每个锁都使用插入意图锁锁定4和7之间的间隙,但是不要互相阻塞,因为行是不冲突的,意向锁的涉及是为了插入的正确和高效。
Next-key Locks(临键锁):临键锁是间隙锁和记录锁的组合,临键锁遵循左开右闭原则(主要是为了包含等值记录)InnoDB默认加的行锁;
Record Locks(记录锁):很显然该锁只锁定一行指定索引对应的数据行;
加锁规则:(参考了博客https://blog.csdn.net/qq_40174198/article/details/111835482)
唯一索引等值查询:当索引项存在时,next-key lock 退化为 record lock;当索引项不存在时,默认 next-key lock,访问到不满足条件的第一个值后next-key lock退化成gap lock;
唯一索引范围查询:默认 next-key lock,(特殊’<=’ 范围查询直到访问不满足条件的第一个值为止);
非唯一索引等值查询:默认next-key lock ,索引项存在/不存在都是访问到不满足条件的第一个值后next-key lock退化成gap lock;
非唯一索引范围查询:默认 next-key lock,向右访问到不满足条件的第一个值为止;
(这里参考博客不太确定,后面补)
略;
SELECT
语句,我们在update、insert、delete等进行连接操作让 table1.a = table2.a、条件判断等等都会需要读取数据up_limit_id
还要小,显然当前事务可以读取该行的数据;low_limit_id
大或者相等,显然当前事务不能访问该版本的数据,通过回滚指针找undo log
(中的update undo log
)的“历史版本”,在undo log
中由头直尾,知道找到合适的;update
操作);trx_id
属性值是不是在trx_ids
列表中,如果在,说明创建ReadView
时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建ReadView
时生成该版本的事务已经被提交,该版本可以被访问。Read View
直接决定了当前事务和其他事务的隔离程度,
SELECT
均生成一个Read View,显然会出现不可重复读;SELECT
不存在幻读问题的,(select
查询后,确定当前事务能够select
的数据是确定的(即当前读视图能够读取的数据)SELECT
检查没有 x=1;
SELECT
结果会左右作为后续写操作的依据时,需要加上 for update
id:查询id select_type:查询类型 simple:查询语句不包含union或子查询的查询; primary:对于包含union、unionall或子查询的大查询来说,最左边的那个查询类型就是primary; union:对于包含union、unionall或子查询的大查询来说,除了最左边的那个查询外,其他查询都是union,和primary是一组; union result:当mysql选择使用临时表来完成union查询的去重工作时,针对该临时表的查询类型 TYPE type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL table:表名;每条记录对应某个单表的访问方法,多个表关联会输入多条记录;非关联查询查询id相同,驱动表在前面; possible_keys:可能用到的索引 key:实际使用的索引 filtered:经过搜索条件过滤后,剩余记录条数的百分比 rows:预估需要读取的记录数
连接
连接主要分为笛卡尔积与内连接、全连接及左(外)连接和右(外)连接、自然连接;
笛卡尔积:(笛卡尔积的结果是表的行数相乘,会出现无效结果),如果加上WHERE table1.xxx = table2.xxx就变成全连接
--笛卡尔积
SELECT XXX FROM table1, table2
SELECT XXX FROM table1 INNER JOIN table2
--内连接
SELECT XXX FROM table1, table2 WHERE table1.XX = table2.XX
自然连接:mysql自动会将所有列名相等的列进行等值连接并且自动合并;
SELECT XXX FROM table1 NATURAL JOIN table2
全连接及左连接和右连接:全连接就是即左连接又右连接,左\右外连接和左\右连接效果一样;左连接和右连接主要如果某一数据在另一边不存在(NULL)时,仍然记录;
SELECT XXX FROM table1 LEFT JOIN table2
SELECT XXX FROM table1 RIGHT JOIN table2
SELECT XXX FROM table1 FULL JOIN table2
当前查询
FROM—>ON—>JOIN—>WHERE—>GROUP BY---->WITH—>HAVING—>SELECT—>select list(筛选)—>ORDER BY —>LIMIT
FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
ON: 对虚表VT1进行ON筛选,只有那些符合的行才会被记录在虚表VT2中。
JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3, rug from子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为 止。
WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5.
CUBE | ROLLUP: 对表VT5进行cube或者rollup操作,产生表VT6.
CUBE:生成的结果集显示了所选列中值的所有组合的聚合。【mysql不支持CUBE】
ROLLUP:生成的结果集显示了所选列中值的某一层次结构的聚合。
SELECT *,COUNT(*) AS `num`
FROM test a
GROUP BY a.b,a.a,a.c
with ROLLUP;
//ROLLUP简单来说就是对分组后再次进行分组操作
HAVING: 对虚拟表VT6应用having过滤,只有符合的记录才会被 插入到虚拟表VT7中。
SELECT: 执行select操作,选择指定的列,插入到虚拟表VT8中。
DISTINCT: 对VT8中的记录进行去重。产生虚拟表VT9.
ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作,产生虚拟表VT10.
LIMIT:取出指定行的记录,产生虚拟表VT11, 并将结果返回。
%、_
开头时索引失效、但是以xxx%、_
索引有效垂直拆分
水平拆分
重点学习怎么实现分库分表(水平拆分)
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>xxxx</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>xxxx</version>
</dependency>
server.port=8070 #配置数据库逻辑名 spring.shardingsphere.datasource.names=master,slave #逻辑名master的数据库配置 spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://ip:port/database?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false spring.shardingsphere.datasource.master.username=root spring.shardingsphere.datasource.master.password=123456 #逻辑名slave的数据库配置 spring.shardingsphere.datasource.slave.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave.jdbc-url=jdbc:mysql://ip:port/database/zy_business1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false spring.shardingsphere.datasource.slave0.username=root spring.shardingsphere.datasource.slave0.password=123456 #shardingsphere主从数据库逻辑名 spring.shardingsphere.masterslave.name=ms #主库 spring.shardingsphere.masterslave.master-data-source-name=master #从库,可以有多个 spring.shardingsphere.masterslave.slave-data-source-names=slave #开始sql监控(方便debug- spring.shardingsphere.props.sql.show=true # mybatis 配置 mybatis.mapper-locations=classpath:mapping/*.xml mybatis.type-aliases-package=com.zypcy.sharding.business.entity
配置完成即可(无论是代码方式还是properties(或者yam方式),其都是低侵入的,所以和直接使用JDBC没有区别,真正进行路由都是由中间件完成;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。