索引 、表关联

---

索引 index

定义

1. 索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

2. 一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

3. 创建索引是可见的过程，我们是可以清晰看得到的，使用索引的过程是不可见的，数据库查询时自动使用的。

分类

1. 单值索引：一个索引只包含一个列，一个表可以有多个列
2. 唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
3. 复合索引：一个索引同时包括多列

索引的SQL操作

查看索引，主键会自动创建索引

show index from emp;

创建索引

#create index 索引名字 on 表名(字段名); #创建索引
 
CREATE INDEX job_index ON emp(job); #创建索引

创建唯一索引

ALTER TABLE emp ADD UNIQUE(ename);#创建唯一索引--索引列的值必须唯一

创建复合索引

alter table emp add index fuhe_index(ename,sal,deptno);

创建复合唯一索引

alter table emp add unique fuhe_index(sal,deptno);

删除索引

alter table emp drop index fuhe_index;

---

索引扫描类型

type：

• ALL 全表扫描，没有优化，最慢的方式
• index 索引全扫描，其次慢的方式
• range 索引范围扫描，常用语<，<=，>=，between等操作
• ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
• eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
• const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
• null MySQL不访问任何表或索引，直接返回结果

最左特性

最左特性指的是在索引时要有最左边的索引值，否则容易失效。
explain //用来观察SQL的执行计划，主要用来看有没有使用索引

explain//用来观察SQL的执行计划，主要用来看有没有使用索引
 
select * from dept where loc='二区' #使用了loc索引
 
explain
 
select * from dept where dname='研发部'#使用了dname索引
 
explain
 
select * from dept where dname='研发部' and loc='二区' #使用了dname索引

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。

为何索引快？

明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

• 排序，tree结构，类似二分查找
• 索引表小

小结

优点:

• 索引是数据库优化
• 表的主键会默认自动创建索引
• 每个字段都可以被索引
• 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
• 索引事先对数据进行了排序，大大提高了查询效率

缺点:

• 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
• 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
• 虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
• 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

表关联 association

概念

表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

分类：

表的关系分为四种：

• 一对一 one to one QQ和QQ邮箱，员工和员工编号
• 一对多 one to many 最常见，部门和员工，用户和订单
• 多对一 many to one 一对多反过来，员工和部门，订单和用户
• 多对多 many to many 老师和学生，老师和课程

多表联查

多表联查：一张表已经无法满足业务需求，需要联合两个和两个以上的表的查询

笛卡尔积 Cartesian product

笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

#方式1：笛卡尔积
SELECT * FROM dept,emp;#查出了所有数据
SELECT * FROM dept,emp
# where 两张表的关联关系
WHERE dept.`deptno`=emp.`deptno`#两张表的关联关系
AND dept.dname='JAVA工程师';#表名 . 字段名

三种连接 join

• 内连接 inner join
• 左（外）连接 left join
• 右（外）连接 right join

内连接 inner join

SELECT * FROM dept d
INNER JOIN emp e 		#两个表关联
ON d.`deptno`=e.`deptno`	#描述字段
WHERE d.`dname`='Python开发';	#具体过滤条件

左（外）连接 left join

SELECT * FROM dept d
LEFT JOIN emp e 	#左边的所有和右边满足的
ON d.`deptno`=e.`deptno`;

右（外）连接 right join

SELECT * FROM dept d
RIGHT JOIN emp e #右边的所有和左边满足的
ON d.`deptno`=e.`deptno`;

inner join、left join、right join的区别？

• INNER JOIN两边都对应有记录的才展示，其他去掉
• LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
• RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充
• 注意：在工作中，常用的是：小表 left join 大表，小表驱动大表

“内连接、左外连接、右外连接、完全连接、交叉连接”的理解

子查询 subquery

概念

子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。

单行子查询 =

返回结果为一个

#方式3：子查询：把上次的查询结果作为这次查询的条件
#1.根据部门名称查部门编号dept
#2.把查到的编号作为条件，查员工的信息
SELECT * FROM emp WHERE deptno=(
SELECT deptno FROM dept WHERE dname='JAVA工程师');

多行子查询 in

in子查询

SELECT * FROM emp WHERE deptno 
IN (SELECT deptno FROM dept WHERE dname='Python开发')