MySQL的框架和索引
表与表之间的关系
表与表之间的关系,说的就是表与表之间数据的关系。
l 一对一关系
在实际工作中,一对一在开发中应用不多,因为一对一完全可以创建成一张表
l 一对多关系
有外键的就是多的一方。
l 注意事项:
一对多关系和一对一关系的创建很类似,唯一区别就是外键不唯一。
l 多对多关系(需要中间表实现)
同一个商品对应多个订单,一个订单对应多个商品
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架构
逻辑架构图
执行流程图
存储引擎介绍
* 多存储引擎是mysql有别于其他数据库的一大特性;
* 存储引擎是针对表的
* MySQL 5.5之后,默认的存储引擎由MyISAM变为InnoDB。
* 查看存储引擎:show engines;
Innodb |
Myisam |
|
存储文件 |
.frm 表定义文件 .ibd 数据文件 |
.frm 表定义文件 .myd 数据文件 .myi 索引文件 |
锁 |
表锁、行锁 |
表锁 |
事务 |
ACID |
不支持 |
CRDU |
读、写 |
读多 |
count |
扫表 |
专门存储的地方 |
索引结构 |
B+ Tree |
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 UNIQUE INDEX 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