MYSQL

最新推荐文章于 2024-07-11 16:26:04 发布

嘿嘿要努力站起来

最新推荐文章于 2024-07-11 16:26:04 发布

阅读量185

点赞数

文章标签： mysql

原文链接：https://blog.csdn.net/pfnie/article/details/88775755

版权

文章目录

1.数据库左连接右连接

1、左连接的定义：是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

下边以A表和B表为例子，A、B之间的左连接条件为：ano=bno;查询语句为：SELECT * FROM A LEFT JOIN B ON ano=bno;

eg1：A表中的只有1条记录，B表中2条记录，B表的2条记录bno都等于ano,
在这里插入图片描述
查询结果：将A表的记录都查询出来，B表中bno等于ano的都查询出来了且左侧为ano对应的信息。
eg2:A表中存在的ano，B表中不存在对应的bno;
结果：A表的记录全部查询出来而且如果B没有bno=ano的记录时右侧显示为空，B表中只有bno=ano的记录查询出来了。

2、右连接的定义，是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。
eg:以上边的数据为例子，进行右连接测试如下：
在这里插入图片描述结果：a表只显示和b表id相等的2行数据，b表的记录全部显示出来

3、内链接：使用比较运算符根据每个表共有的列的值匹配两个表中的行；
eg:继续以之前的数据为例子：
在这里插入图片描述
结果：只显示a.aid=b.bid的2行记录

2.Mysql数据库事务的四大特性（ACID）

事务：把一组密不可分的操作系列集合在一起，这些操作要么全部执行，要么全部不执行。

1.原子性：事务是内定义的操作是一个整体，是不可分割的。

2.一致性：同一个事务，多次读取数据库中的同一个数据，读取的内容应该是一致的，不变的。

3.隔离性：不同事务之间相互独立，互不干扰。

4.持久性：事务提交后，事务内的操作对数据库的修改被永久保存在数据库文件中。

3.事物、事物方法及事务的四种隔离级别

MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

事务的四种隔离级别
1 读未提交 read uncommitted 一个事务可以读到别的事务尚未提交的数据，出现的问题是读到的数据为脏数据，称为脏读。

2 读已提交 read committed 一个事务读不到别的事务还没提交的数据，可以读到已经提交的事务。出现的问题是不可重复复，即同一个事务每次读取的数据会不一致。

3 可重复读 repeatable read 事务开启后，同一个事务每次读到的数据是一致的，就算别的事务提交了新的数据也是读取不到的，这就造成了读取的数据有可能跟数据库的数据不相同，这种方式叫做幻读。

4 串行化 seriaziable 一个事务的执行，需要等待上一个事务执行完成。正在执行的事务会把当前表锁住（表级别的锁），别的事务无法访问当前的表。出现的问题，数据库的执行效率低。

4.索引

1）什么是索引？

Mysql索引作用：快速检索需要数据，也就减少的用户等待的页面响应时间

Mysql索引本质：一种高效获取数据的数据结构

索引是对数据库表中的一列或者多列的值进行排序的一种数据结构，如果把数据库中的表比作一本书，索引就是这本书的目录，通过目录可以快速查找到书中指定内容的位置

索引也是一张表，该表中存储着索引的值和这个值的数据所在行的物理地址，使用索引后可以不用扫描全表来定位某行的数据，而是通过索引表来找到该行数据对应的物理地址

2）索引的优缺点

优点：
建立索引的列可以保证行的唯一性，生成唯一的rowId
索引可以有效缩短数据的检索时间，减少I/O次数
索引可以加快表与表之间的连接
为用来排序和分组的字段建立索引可以加快分组和排序
缺点：
创建索引和维护索引需要时间成本，这个成本随着数据量的增大而加大
创建索引和维护索引需要空间成本，每一条索引都需要占据数据库的物理存储空间，数据量越大，占用空间也越大
会降低表的增删改的效率，因为每次增删改，索引需要进行动态维护

3）Mysql 索引底层数据结构类型

3.1、二叉查找树
优点：二叉树是一种比顺序结构更加高效地查找目标元素的结构，它可以从第一个父节点开始跟目标元素值比较，如果相等则返回当前节点，如果目标元素值小于当前节点，则移动到左侧子节点进行比较，大于的情况则移动到右侧子节点进行比较，反复进行操作最终移动到目标元素节点位置。
在这里插入图片描述缺点：在大部分情况下，我们设计索引时都会在表中提供一个自增整形字段作为建立索引的列，在这种场景下使用二叉树的结构会导致我们的索引总是添加到右侧，在查找记录时跟没加索引的情况是一样的，二叉查找树会出现单边增长，深度不可控的情况。如下图所示：
在这里插入图片描述 3.2、红黑树
优点：红黑树也叫平衡二叉树，它不仅继承了二叉查找树的有点，而且解决了上面二叉查找树遇到的自增整形索引的问题，从下面的动态图中可以看出红黑树会走动对结构进行调整，始终保证左子节点 < 父节点 < 右子节点的规则。
在这里插入图片描述缺点：在数据量大的时候，深度也很大。从图中可以看出每个父节点只能存在两个子节点，如果我们有很多数据，那么树的深度依然会很大，可能就会超过十几二十层以上，对我们的磁盘寻址不利，依然会花费很多时间查找。

3.3、hash
优点：对数据进行Hash运算，主流的Hash算法有MD5、SHA256等等，然后将哈希结果作为文件指针可以从索引文件中获得数据的文件指针，再到数据文件中获取到数据，按照这样的设计，我们在查找where Col2 = 22的记录时只需要对22做哈希运算得到该索引所对应那行数据的文件指针，从而在MySQL的数据文件中定位到目标记录，查询效率非常高。
在这里插入图片描述缺点：无法解决范围查询（Range）的场景，比如 select count(id) from sus_user where id >10；因此Hash这种索引结构只能针对字段名=目标值的场景使用。不适合模糊查询（like）的场景。

3.4、B-Tree
既然红黑树存在缺点，那么我们可以在红黑树的基础上构思一种新的储存结构。解决的思路也很简单，既然觉得树的深度太深，就只需要适当地增加每个树节点能存储的数据个数即可，但是数据个数也必须要设定一个合理的阈值，不然一个节点数据个数过多会产生多余的消耗。按照这样的思路，我们先来了解下关于B-Tree的一些知识点：
度(Degree)-节点的数据存储个数，每个树节点中数据个数大于 15/16Degree（未验证）时会自动分裂，调整结构。
叶节点具有相同的深度，左子树跟右子树的深度一致，叶节点的指针为空，节点中的数据key从左到右递增排列。
用中采用的是另一种BTree的变种B+Tree（B+树）。当然有人可能会这样想，那我们为什么不把数据全部都存在一个节点，这样深度不就是1了吗？当然不行了！内存与硬盘的交互是有大小限制的，一页数据4k左右，所以不能把所有数据都放在一个节点来获取，一般来说节点会尽量预存4K容量。看到这里，我们知道（4K=节点；节点=小节点小节点的容量）一个节点是4K，而节点内有几个小节点，那么也就是说，只要我们每个小节点的data容量越小，那么可以存的小节点也就可以更多。

4.5、B+Tree
为什么要对B-Tree继续做优化？
要解答这个疑问需要先了解B-Tree每个节点结构（上面已经说明）和MySQL数据库它是如何读取索引数据的，索引和表数据在不使用的时候是存储在文件中的，也就是磁盘，当我们执行查询操作时会DBMS（数据库管理系统）首先会先从内存中查找，如果找到直接使用，如果找不到则从磁盘文件中读取；操作系统储存数据的最小单位是页（page），一页假设是4K大小（由操作系统决定），对内存和磁盘读取数据是按一页的整数倍读取的。
在这里插入图片描述这里我们假设数据库一次IO操作就读取1页4K的数据，再假设图中圈起来的元素就是一个大节点，内含多个小节点的索引和数据。假设这样的一个大节点是10MB，那么我们要从磁盘中读取完整个大节点需要进行 10M / 4K = 2500次IO操作，这样就可以看出如果大节点数据总量越大，需要执行的IO操作越多，花费的时间也越长，因此为了提高性能，数据库会建议我们一个大节点只存储一页4K大小的数据，这里的数据包含了索引和表记录，另外我们还能计算出树的度Degree应该设置成多大才合理：

Degree = 内存页大小（4K） / 单个索引值字节大小；

进一步分析，索引值的大小相对于整条记录的大小是很小的，如果我们需要查找的数据刚好是在最后，那么前面遍历过的节点中存储的记录数据是不是对我们来说是没用的，它会占用比索引大得多的空间，导致我们一个大节点里能遍历的索引数量大大减少，需要向下继续遍历的几率就更大，花费更多时间查找，那么有没有办法可以优化呢？看下一个个问题。

相对于B-Tree，B+Tree做了哪些优化？
新的B+tree结构没有在所有的节点里存储记录数据，而是只在最下层的叶子节点存储，上层的所有非叶子节点只存放索引信息，这样的结构可以让单个节点存放下更多索引值，增大度Degree的值，提高命中目标记录的几率。这种结构会在上层非叶子节点存储一部分冗余数据，但是这样的缺点都是可以容忍的，因为冗余的都是索引数据，不会对内存造成大的负担。
在这里插入图片描述 B+Tree通过把data不放在非叶子节点来增加度（小节点），一般会一百个以上使得深度是3~5，从而减少查询次数。并且，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找，而不再从上面节点往下一个个找。

结论：B+Tree 既减少查询次数又提供了很好的范围查询。

5.Mysql的索引有哪些

从逻辑角度

1)普通索引：

普通索引是最基本的索引，它没有任何限制，允许在定义索引的列中插入重复值和空值

直接创建：

create index index_name on table_name (column(length));

修改表结构添加索引：

alter table table_name add index index_name on(column(length));

创建表的时候创建索引：

create table table_name(
.....
....
....
index index_name(column(length))
)

删除索引：

drop index index_name on table;

2)唯一索引：
索引列的值必须唯一，允许有空值，如果是组合索引，列值的组合必须唯一
直接创建：

create unique index index_name on table_name (column(length));

修改表结构添加索引：

alter table table_name add unique index index_name on(column(length));

创建表的时候创建索引：

create table table_name(
.....
....
....
unique index index_name(column(length))
)

3)主键索引：

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的时候指定主键，主键默认就是主键索引

create table table_name(
........
......
.....
primary key(column)
)

4)组合索引：
多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用

alter table table_name add index index_name(column,column,column..);

5)全文索引：
索引类型为FULLTEXT，允许有重复值和空值，可以在char、varchar、text类型的列上创建，Mysql中MyISAM和InnoDB存储引擎都支持。主要用来查找文本中的关键字，而不是直接与索引中的值比较，它更像是一个搜索引擎，全文索引需要配合match against操作使用，而不是一般的where语句加like
6)空间索引：
空间索引是对空间数据类型的字段建立的索引，Mysql中的空间索引类型有4种，GEOMETRY、POINT、LINESTRING、POLYGON，创建空间索引的列，必须将其声明为not null，Mysql中只有MyISAM存储引擎支持创建空间索引
从物理存储角度

聚集索引和非聚集索引

聚集索引与辅助索引相同的是:不管是聚集索引还是辅助索引,其内部都是B+树的形式,即高度是平衡的,叶子结点存放着所有的数据

聚集索引与辅助索引不同的是:叶子结点存放的是否是一整行的信息

聚簇索引

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引
将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
介绍：
在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。
表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护这个顺序；
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
聚集索引与非聚集索引的区别
聚集索引
1.纪录的索引顺序与无力顺序相同，因此更适合between and和order by操作
2.叶子结点直接对应数据从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索
非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容多个索引会影响insert和update的速度

为什么使用聚簇索引？优点？

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

索引的设计原则

为了提升数据检索速度，降低查询延时和IO消耗，在建表或者编写SQL语句时，应该首先设计及检查索引和数据分布情况，下面是常见的索引设计或使用原则：
尽量选择惟一性索引；
为经常需要排序、分组和联合操作的字段建立索引；
为常作为查询条件的字段建立索引；
限制索引的数目，索引越多，更新和插入的效率越低；
尽量使用数据量少的索引；
组合索引中的尽量把能过滤掉更多数据的字段放在前面；
尽量使用前缀来索引，针对长文本，TEXT等类型；
删除不再使用或者很少使用的索引；
数据是动态变化中的，索引的使用也需要根据数据的变化而变化。