子查询 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