目录
1.内外连接;
2. 索引;
1.内外连接:
1.1内连接:
语法: select 字段 from 表名 inner join 表名 on 字段限制;
1.2 外连接:
分为左右外连接;
(1)左外连接:
语法: select * from 表名 left join 表名 on 字段限制.
🌰查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来:
(2) 右外连接:
语法: select * from 表名 right join 表名 on 连接条件.
🌰对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来:
列出部门名称和这些部门的员工信息，同时列出没有员工的部门:
2. 索引:
2.1 目的:
目的: 提高海量查找数据的效率. 一般查找数据的效率是远远低于索引查找的效率的.
语法: select table 表名 add index(索引名);
2.2 认识磁盘:
(1) 扇区: (这里用一下博主一剑何风情的图片!!!)
扇区就是磁盘分的一块存储区域.(每个扇区存储大小是512字节)
结论: 磁盘扇区的大小和存储大小由关系, 那么就将操作系统和硬件之间进行交互了吗? 当然不是的因为磁盘的效率太低了, 读取的基本单位是数据块(4k). 所以系统读取磁盘是以数据块为单位, 4k单位.
2.3 随机访问和连续访问:
随机访问: 本次io本次扇区和上次扇区的地址不连续, 导致需要磁盘的磁头移动幅度变大;
连续访问: 本次io本次扇区和上次扇区的地址连续, 导致需要磁盘的磁头移动幅度变小;
2.4 MySQL与磁盘交互的基本单位:
MySQL进行io的基本单位是16kb; 16384/1024=16;
磁盘设备的基本单位是512字节, MySQL和磁盘进行数据交互的基本单位是16kb, 这个基本单位是page;
总结: a. MySQL数据文件里面都是以page存储的;
b.MySQL里面涉及CURD(增删查改), 就需要计算,那么也就需要找到数据; 也要进行cpu计算, 首先要将数据存放到内存当中.
2.5 索引的了解:
一般表的创建默认存储引擎就是Innodb.
插入数据之后为什么数据都是有序的?
⭐为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗?
加载如果查找id=5需要查找5次, 下次还要查找还是这样, 但是如果是page将查找数据存储在page中然后加载到MySQL的buffer pool里面之后查找就不需要加载,减少io次数.采用局部性原理方案进行交互.
page: 因为mysql里面有多个page, 那么就需要将它们管理起来.
数据结构: 双向链表.
⭐为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？
排序的目的就是优化提高查找效率, 页内部存放数据的模块实际上就是链表结构, 链表特点增删快, 查询慢, 那么就需要索引时候都是有效查找, 速度就会加快.
多个page结构: 这样看结构查找效率也是很低.
页目录: 就是提高查找效率, 一种以空间换时间的方法.
单页page和多页page都可以使用到页目录;
目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址
是不是和B+树的结构很像, 就是!!!
总结: (1) page分为目录页和数据页, 目录页只放下一page的最小键值.
(2) 查找的时候只要加载目录到内存中进行筛选即可.
⭐InnoDB 在建立索引结构来管理数据的时候，其他数据结构为何不行？
链表是线性结构查找效率低, 二叉搜索树, 可能会退化为单只二叉树效率低;
AVL和哈希表, 二叉树结构, 相比于B+树后者更好;
哈希: 有一些存储引擎使用到, 但是Inodb和MISAM都是B+树.
为啥是B+树不是B树? B+树最后子节点是链表形式连接.
2.7 聚簇索引 VS 非聚簇索引:
MyISAM和inoDB的区别:
MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。 相较于InnoDB索引, InnoDB 是将索引和数据放在一起的。
非聚簇索引: MyISAM, 用户数据与索引数据分离;
聚簇索引: 用户数据与索引数据在一起;
辅助索引: 用户按照其他信息建立索引;
主键索引: 按照主键信息进行索引;
回表查询: 检索辅助索引获得主键，然后用主键 到主索引中检索获得记录;
2.8 索引操作:
2.8.1 创建主键索引:
(1) 在创建表的时候，直接在字段名后指定
primary key create table user1(id int primary key, name varchar(30));
(2) 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));
(3) 创建表以后再添加主键
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);
特点: 主键索引不能为空, 而且最多只有一个, 效率高, 一般类型是int;
2.8.2 唯一键索引:
(1) 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);
(2) 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));
(3) 创建完后修改:
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);
特点: 可以有多个唯一键索引, 但是不能重复数据, 唯一键指定not null就是主键索引.
2.8.3 普通键索引:
(1) 在表的定义最后，指定某列为索引:
create table user8(id int primary key, name varchar(20), email varchar(30), index(name));
(2) 创建完表以后指定某列为普通索引:
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name);
(3) 创建一个索引名为 idx_name 的索引:
create table user10(id int primary key, name varchar(20), email varchar(30));
create index idx_name on user10(name);
特点: 一张表可以有多个普通索引, 而且数据可以重复.
2.8.4 全文索引:
场景: 进行大量的文字查询, 搜索引擎必须是MyISAM;
其中工具: explain, 查看是否是否索引;
使用: select * from 表名
where match (字段) against ('查询数据');
2.8.5 查询索引:
(1) show keys from 表名;
(2) show index from 表名;
(3) desc 表名;
2.8.6 删除索引:
(1) 删除主键索引: alter table 表名 drop primary key;
(2) 删除其他索引: alter table 表名 drop index 索引名;
(3) drop index 索引名 on 表名;
2.8.7 索引创建原则:
(1) 条件繁琐字段适合作为索引;
(2) 唯一性好或者不频繁查询的可以作为索引;
(3) 出现在where子句的字段可以作为索引;
