mysql索引框架_MySQL的框架和索引

桃扇骨 2022-10-24 11:56 407阅读 0赞

表与表之间的关系

表与表之间的关系，说的就是表与表之间数据的关系。

l 一对一关系

在实际工作中，一对一在开发中应用不多，因为一对一完全可以创建成一张表

l 一对多关系

有外键的就是多的一方。

![fbdfee269f150dc53eaf7edbf53d7fa7.png][]

l 注意事项：

一对多关系和一对一关系的创建很类似，唯一区别就是外键不唯一。

l 多对多关系(需要中间表实现)

![0be34905e690e60b797b0576d9331ba9.png][]

同一个商品对应多个订单，一个订单对应多个商品

l 注意事项：

需要中间表去完成多对多关系的创建

多对多关系其实就是两个一对多关系的组合

外键

如果公共关键字在一个关系中是

多表关联查询

JOIN 按照功能大致分为如下三类：

l CROSS JOIN(交叉连接)

l INNER JOIN(内连接或等值连接)。

OUTER JOIN(外连接)

1.1 交叉连接

交叉连接的关键字：CROSS JOIN

交叉连接也叫笛卡尔积连接

笛卡尔积是指在数学中，两个集合X和Y的笛卡尓积(Cartesian product)，又称直积，表示为X\*Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

交叉连接的表现：行数相乘、列数相加

l 隐式交叉连接

SELECT \* FROM A, B

l 显式交叉连接

SELECT \* FROM A CROSS JOIN B

1.1 内连接

内连接的关键字：INNER JOIN

内连接也叫等值连接，内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

l 隐式内连接

SELECT \* FROM A,B WHERE A.id = B.id

l 显式内连接

SELECT \* FROM A INNER JOIN B ON A.id = B.id

外连接

外联接可以是左向外联接、右向外联接或完整外部联接。也就是说外连接又分为：左外连接、右外连接、全外连接

外连接需要有主表或者保留表的概念。

在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：

l 左外连接：LEFT JOIN 或者 LEFT OUTER JOIN

SELECT \* FROM A LEFT JOIN B ON A.id = B.id

l 右外连接:：RIGHT JOIN 或者 RIGHT OUTER JOIN

SELECT \* FROM A RIGHT JOIN B ON A.id = B.id

l 全外连接(MySQL不支持)：FULL  JOIN 或 FULL OUTER JOIN

SELECT \* FROM A FULL JOIN B ON A.id = B.id

外连接总结：

l 通过业务需求，分析主从表

l 如果使用LEFT JOIN，则主表在它左边

l 如果使用RIGHT JOIN，则主表在它右边

l 查询结果以主表为主，从表记录匹配不到，则补null

分页查询

MySQL的分页关键字是：LIMIT

LIMIT关键字不是SQL92标准提出的关键字，它是MySQL独有的语法。

通过Limit关键字，MySQL实现了物理分页。

分页分为逻辑分页和物理分页

逻辑分页：将数据库中的数据查询到内存之后再进行分页。

物理分页：通过LIMIT关键字，直接在数据库中进行分页，最终返回的数据，只是分页后的数据。

l 格式：

SELECT \* FROM table LIMIT \[offset,\] rows

offset：编译量

rows：每页多少行记录。

子查询

l 定义

子查询允许把一个查询嵌套在另一个查询当中。

子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。

l 位置

select 中、from 后、where 中.

MySQL架构

逻辑架构图

![061054e816f01a79a740fbed6954d21b.png][]

![4c423c9963c101a01b775a277c58bb7c.png][]

执行流程图

![7842cd5422591a46fa728f521ea1a4cb.png][]

![fffa225483d4424dda2d1338c58a159a.png][]

存储引擎介绍

\* 多存储引擎是mysql有别于其他数据库的一大特性;

\*存储引擎是针对表的

\* MySQL 5.5之后，默认的存储引擎由MyISAM变为InnoDB。

\* 查看存储引擎：show engines;

Innodb

Myisam

存储文件

.frm 表定义文件

.ibd 数据文件

.frm 表定义文件

.myd 数据文件

.myi 索引文件

锁

表锁、行锁

表锁

事务

ACID

不支持

CRDU

读、写

读多

count

扫表

专门存储的地方

索引结构

B+ Tree

日志文件

MySQL通过日志记录了数据库操作信息和错误信息。常用的日志文件包括错误日志、二进制日志、查询日志、慢查询日志和 InnoDB 引擎在线 Redo 日志、中继日志等。

MySQL索引

1.1 介绍

\*  使用索引的主要目的是为了优化查询速度

\* 索引是一种特殊的文件或者叫数据结构(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

1.1 索引的分类

l 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

\* MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换 \*  MEMORY/HEAP存储引擎：支持HASH和BTREE索引

l 索引的分类

\* 单列索引：

\* 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

\* 唯一索引：索引列中的值必须是唯一的，但是允许为空值，

\* 主键索引：是一种特殊的唯一索引，不允许有空值。

\*组合索引

\* 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

\* 全文索引

\* 全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

\* 空间索引：不做介绍，一般使用不到。

创建索引

1 CREATE INDEX index\_name ON table(column(length))2 ALTER TABLE table\_name ADD INDEX index\_name ON (column(length))3

4 CREATE UNIQUEINDEX index\_name ON table(column(length))5 CREATE FULLTEXTINDEX index\_name ON table(column(length))6

7 ALTER TABLE article ADD INDEX index\_titme\_time (title(50),time(10))

删除索引

DROP INDEX index\_name ON table

[fbdfee269f150dc53eaf7edbf53d7fa7.png]: /images/20221024/b86c64000887437092a759896b8526db.png
[0be34905e690e60b797b0576d9331ba9.png]: /images/20221024/f24bd86ec9f44df29cac8267abdcba9c.png
[061054e816f01a79a740fbed6954d21b.png]: /images/20221024/8b9195973f3e45f68a34e3ecbc13cc83.png
[4c423c9963c101a01b775a277c58bb7c.png]: /images/20221024/c74b0bcba0394d288cce3851b546fac6.png
[7842cd5422591a46fa728f521ea1a4cb.png]: /images/20221024/0060d7b6089a4d51ae4aa497732f348e.png
[fffa225483d4424dda2d1338c58a159a.png]: /images/20221024/02a7a8e65a4142dfaf9486863ba33551.png