子查询 subquery
概念
子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。
单行子查询 =
--列出tony所在部门的所有人员
select deptno from emp where ename='tony';
select * from emp where deptno = (select deptno from emp where ename='tony');
多行子查询 in
in子查询
select * from emp where job in ('经理','员工');
select * from emp where job in (select distinct job from emp); 
索引 index
定义
索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。
分类
-
单值索引：一个索引只包括一个列，一个表可以有多个列
-
唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
-
复合索引：一个索引同时包括多列
创建索引
#查看索引，主键会自动创建索引
show index from dept;
#创建普通索引
#create index 索引名字 on 表名(字段名); #创建索引
create index loc_index on dept(loc); #创建索引
# 创建唯一索引
#创建唯一索引--索引列的值必须唯一
CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
CREATE UNIQUE INDEX bindex ON dept(loc)
# 创建复合索引
#如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX 索引名 ON 表名 (字段1, 字段2)
CREATE INDEX PIndex ON Persons (LastName, FirstName)
# 删除索引
alter table dept drop index fuhe_index 
索引扫描类型
type：
ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描，其次慢的方式
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
null MySQL不访问任何表或索引，直接返回结果
最左特性
explain
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不仅要保存数据,也要保存一下索引文件
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引
视图View
概念
可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。
下次还要发起相同的sql，直接查视图。现在用的少,了解即可.
使用： 1，创建视图 2，使用视图
create view 视图名 as SQL语句;
select * from 视图名;
#视图：就是一个特殊的表，缓存上次的查询结果
#好处是提高了SQL的复用率，坏处是占内存无法被优化
#1.创建视图
CREATE VIEW emp_view AS
SELECT * FROM emp WHERE ename LIKE '%a%' #模糊查询，名字里包含a的
#2.使用视图
SELECT * FROM emp_view
SQL优化
查询SQL尽量不要使用select *，而是具体字段
反例：SELECT * FROM student
正例：SELECT id,NAME FROM student
理由：
字段多时，大表能达到100多个字段甚至达200多个字段
只取需要的字段，节省资源、减少网络开销
select * 进行查询时，很可能不会用到索引，就会造成全表扫描避免在where子句中使用or来连接条件
反例：SELECT * FROM student WHERE id=1 OR salary=30000
正例：
# 分开两条sql写
SELECT * FROM student WHERE id=1
SELECT * FROM student WHERE salary=30000
理由：
使用or可能会使索引失效，从而全表扫描
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的使用varchar代替char
反例：`deptname` char(100) DEFAULT NULL COMMENT '部门名称'
正例：`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'
理由：
varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
char按声明大小存储，不足补空格
其次对于查询来说，在一个相对较小的字段内搜索，效率更高尽量使用数值替代字符串类型
主键（id）：primary key优先使用数值类型int，tinyint
性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态（state）：1-开启、2-暂停、3-停止
商品状态（state）：1-上架、2-下架、3-删除查询尽量避免返回大量数据
如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。
通常采用分页，一页习惯10/20/50/100条。
使用explain分析你SQL执行计划
SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。
EXPLAIN
SELECT * FROM student WHERE id=1
3027
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
