MySQL索引 & 视图

MySQL 索引

什么是索引

• 在数据库表中，对字段建立索引可以大大提高查询速度
• 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字

常见索引分类

索引名称	说明
主键索引 (primary key)	主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的每一 条记录
唯一索引 (unique)	唯一索引指的是 索引列的所有值都只能出现一次，必须唯一
普通索引 (index)	最常见的索引，作用就是 加快对数据的访问速度

• MySql将一个表的索引都保存在同一个索引文件中，如果对中数据进行增删改操作，MySql都会自动地更新索引

主键索引 (PRIMARY KEY)

• 特点：主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的某一条记录

  • 一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL

  -- 语法格式
  -- 方式一：创建表的时候直接添加主键索引 (最常用)
  CREATE TABLE 表名(
      -- 添加主键 (主键是唯一性索引,不能为null,不能重复,)
      字段名 类型 PRIMARY KEY,
  );
  
  -- 方式二：修改表结构 添加主键索引
  ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )
  

  -- 创建 demo01表
  CREATE TABLE demo01(
      did INT,
      dname VARCHAR(20),
      hobby VARCHAR(30)
  );
  
  -- 为demo1 表添加主键索引
  ALTER TABLE demo01 ADD PRIMARY KEY (did);
  

唯一索引(UNIQUE)

• 特点：索引列的所有值都只能出现一次， 必须唯一

  • 事实上，在许多场合，人们创建唯一索引的目的往往不是为了 提高访问速度，而只是为了避免数据出现重复

  -- 语法格式
  -- 方式一：创建表的时候直接添加主键索引
  CREATE TABLE 表名(
      列名 类型(长度),
      -- 添加唯一索引
      UNIQUE [索引名称] (列名)
  );
  
  -- 方式二：使用create语句创建: 在已有的表上创建索引
  create unique index 索引名 on 表名(列名(长度))
  
  -- 方式三：修改表结构添加索引
  ALTER TABLE 表名 ADD UNIQUE ( 列名 )
  

  -- 为 hobby字段添加唯一索引
  create unique index ind_hobby on demo01(hobby)
  

普通索引 (INDEX)

• 特点：普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度

  • 因此，应该只 为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建索引

  -- 语法格式
  -- 使用create index 语句创建: 在已有的表上创建索引
  create index 索引名 on 表名(列名[长度])
  
  -- 修改表结构添加索引
  ALTER TABLE 表名 ADD INDEX 索引名 (列名)
  

  --  给 dname字段添加索引
  alter table demo01 add index dname_indx(dname);
  

删除索引

• 由于索引会占用一定的磁盘空间，因此，为了避免影响数据库的性能，应该及时删除不再使用的索引

  -- 语法格式
  ALTER TABLE table_name DROP INDEX index_name;
  

索引的优缺点总结

• 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引

索引的优点

• 大大的提高查询速度
• 可以显著的减少查询中分组和排序的时间
• 通过创建唯一索引保证数据的唯一性

索引的缺点

• 创建索引和维护索引需要时间，而且数据量越大时间越长
  • 所以要在表中数据量不大的时候就加索引，否则后期可能加不上了
• 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度
• 索引文件需要占据磁盘空间

MySQL 视图

什么是视图

• 视图是由查询结果形成的一张虚拟的表

视图的作用

• 权限控制
  • 比如，某几个列可以运行用户查询，其他列不允许，可以开通视图查询特定的列，起到权限控制的作用
• 简化复杂的多表查询
  • 视图本身就是一条查询SQL，我们可以将一次复杂的查询构建成一张视图， 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
  • 视图主要就是为了简化多表的查询

视图的使用

创建视图

-- 1.先编写查询语句
-- 查询所有商品 和 商品的对应分类信息
SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;

-- 2.基于上面的查询语句,创建一张视图
CREATE VIEW products_category_view
AS SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;

-- 3.查询视图 ,当做一张只读的表操作就可以
SELECT * FROM products_category_view;

通过视图进行查询

-- 查询各个分类下的商品平均价格

-- 通过 多表查询
SELECT
    cname AS '分类名称',
    AVG(p.`price`) AS '平均价格'
FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`
GROUP BY c.`cname`;


-- 通过视图查询 可以省略连表的操作
SELECT
    cname AS '分类名称',
    AVG(price) AS '平均价格'
FROM products_category_view GROUP BY cname;

-- 查询鞋服分类下最贵的商品的全部信息
-- 通过连表查询
-- 1.先求出鞋服分类下的最高商品价格
SELECT
    MAX(price) AS maxPrice
FROM
products p LEFT JOIN category c ON p.`category_id` = c.`cid`
WHERE c.`cname` = '鞋服'

-- 2.将上面的查询 作为条件使用
SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`
WHERE c.`cname` = '鞋服' AND p.`price` =
(SELECT
    MAX(price) AS maxPrice
FROM
products p LEFT JOIN category c ON p.`category_id` = c.`cid`
WHERE c.`cname` = '鞋服');


-- 通过视图查询
SELECT * FROM products_category_view pcv
WHERE pcv.`cname` = '鞋服' AND pcv.`price` = 
(SELECT 
     MAX(price) 
 FROM products_category_view WHERE cname = '鞋服');

视图与表的区别

• 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
• 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果，不允许更新）
• 删除视图，表不受影响，而删除表，视图不再起作用