数据库入门4
文章目录
索引
索引是一种排好序的快速查找的数据结构,它帮助数据库高效的进行数据的检索。在数据之外,数据库还维护着满足特定查找算法的数据结构(额外的存储空间),这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高效的查找算法。这种数据结构叫做索引。
一般来说索引本身也很大,不可能全部存储在内存中,往往以索引文件的形式存放在磁盘中。没目前大多数索引采用B+树构建。
好处:给加完索引的列提高查询效率
坏处:索引本质上就是一张表,一张表的体积太大,会占内存
主键本身就具有索引
索引的分类
- 单值索引: 一个索引值包括一个列
- 唯一索引: 索引的值必须唯一,但允许有空值,主键会自动创建唯一索引
- 复合索引:一个索引同时包括多列
创建索引
创建唯一索引
CREATE INDEX 索引名 ON 表名(字段名);
经常被查询的字段建议加索引
索引的简单操作
修改表结构 添加普通索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名);
创建唯一索引
ALTER TABLE 表名 ADD UNIQUE(字段名);
创建复合索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名,字段名);
创建复合唯一索引
复合索引可能会产生索引失效的情况
使用索引生效失效情况讨论
- 比如 A(1,2,3) 只有A1,A1,2, A1,2,3 会产生效果
- 当一个字段有很多索引时,优先使用复合索引
- A1,2,3查询的时候随便用什么顺序都不影响索引的效果,mysql会自动调换顺序
- 使用模糊查询like,索引失效
- 使用or索引失效
ALTER TABLE 表名 ADD UNIQUE 索引名(字段名,字段名);
一个字段可以重复加索引
删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
查看索引,主键会自动创建索引
show index from 表名;
使用索引
按照索引列查询更快
查看索引是否正确使用
用关键字explain来跟踪
索引的扫描类型
- ALL 全表扫描,没有优化,最慢的方式
- index 索引全扫描,其次慢的方式
- range 索引范围扫描,常用语< , <= , >= , between
- ref 使用非唯一索引扫描或者唯一索引的前缀扫描,返回单条记录,常出现在关联查询中
- eq_ref类似ref,区别在于使用的是唯一索引,使用索引的关联查询
- const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或者唯一索引查询,system是const的特殊情况
- null MySQL不访问任何表或索引,直接返回结果
视图view
视图的本质就是一个查询,它执行完会有缓存,下次查询就直接使用。但因为事先缓存,无法做优化,大型项目中禁止使用
--创建视图, 本质上就是缓存一个查询结果
CREATE VIEW 视图名 AS SQL语句;
CREATE VIEW stu_v AS
SELECT * FROM stu WHERE ssex=0
REPLACE VIEW stu_v AS
SELECT * FROM stu WHERE ssex=0
--查询表结构
SELECT * FROM stu WHERE ssex =0;
--直接查询视图,高效,但是无法优化
SELECT * FROM stu_v;
好处:提高查询效率
坏处:占用了内存,无法进行SQL优化,当更新了数据时视图也需要时间更新
试图直接当一张表来使用
多表联查
联合查询多张表中的数据
实际开发中尽量少用多表联查,其根本原因就在这里,查询过程中,先在内存中构建一个大大的结果集,然后再进行数据的过滤。那这个构建过程,和所使用的内存资源,包括过滤时的判断,都是既耗费资源,又浪费时间。
- 笛卡尔积(无实际的业务意义)
- 表连接
- 子查询
JOIN
- 内连接inner join
两边都对应有记录的才战术 - 左连接 left join
左表中的数据都出现,右边没有的数据用NULL填充 - 右连接 right join
右中的数据都出现,左边没有的数据用NULL填充
子查询
把上一次的查询结果作为条件继续使用