数据库入门4

数据库入门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填充

子查询

把上一次的查询结果作为条件继续使用