MySQL视图和索引

1、视图

        视图(view)是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。通过视图可以展现基表的部分数据。视图数据来自自定义视图的查询中使用的表。用来创建视图的表叫做基表base table

        创建视图：

                create view 视图名 as (select 语句)。select语句可以为单表也可以多表，还可以是空表

        视图的作用：

                简单：无需考虑结构、关联条件，筛选条件。是一个筛选好的结果集。提高开发效率。

                安全：只能访问视图允许查询的结果集，保障数据安全性。

        视图的DML操作：

                删除：delete view v1 as（select语句）

                新增操作：insert into view(元素1，元素2...) values(值1，值2....)

                部分视图不可以做DML操作，如下：

                1、select子集中包含distinct。

                2、select子集中包含分组函数。

                3、select语句中包含group by。

                4、select语句中包含union、union all等集合运算符

                5、where子句中包含相关子查询

                6、如果视图有计算列则不能更新。

                7、如果基表中有某个列包含非空约束未出现在视图的定义中，则不能做insert操作。

        视图约束：

                对于可执行DML的视图，定义时可以带上with check option约束。

    v1插入的数据只能是男：create view v1 as select * from stu where sex='男' with check option

        删除视图：

                drop view 视图名                drop view if exists 视图名

---

2、索引

        索引是一种特殊的文件，包含对数据表里所有记录的引用指针。能加快数据库的查询速度。

        创建索引共有两种方法：

                1、创建索引：create index index_stu_name on stu(name);

                2、修改表结构：alter table stu add index index_stu_name(name);

        删除索引：

                drop index index_stu_name on user

        2.1、索引的分类

        普通索引：

                create index 索引名字 on 表名（列名）

        普通索引—最左前缀：

                create index 索引名字 on 表名（列名（长度））

        主键索引：不允许空值，唯一

                alter table 表名 add primary key（列名）

        唯一索引：允许空，唯一

                create unique index 索引名字 on 表名（列名）

                alter table 表名 add unique 索引名（列名）

        多列索引：

                create unique index 索引名字 on 表名（列名1，列名2）

                alter table 表名 add unique 索引名（列名1，列名2）

        2.2、索引的优点和缺点

        优点：

                1、可以通过建立唯一索引或主键索引，保证数据库中的每一行数据的唯一性。

                2、建立索引可以大大提高检索的速度，减少表的索引行数。

                3、在表的连接条件加索引，可以加速表与表的直接相连。

                4、在分组和排序子句中进行数据检索，可以减少查询时间中分组和排序所消耗的时间。

        缺点：

                1、创建索引和维护索引会耗费时间，随着数据量的增加而增加。

                2、索引文件会占用物理内存，除了数据表，每个索引还会额外占用一定物理空间。

                3、对表进行增删改的时候，索引也要动态维护，导致降低维护速度。

        使用索引时需要注意：

                1、在经常需要搜索的列建立索引会加快检索速度。

                2、主键列可以确保行的唯一性。

                3、在表与表的连接条件加索引，可以加快检索速度。

                4、在经常排序、去重和分组的列加索引，可以加快排序时间。单独排序无法使用。

                5、索引选择的数据类型越小，处理速度越快。

                6、在保证索引唯一的前提下，使用最左前缀(段索引)可以加快检索速度。

                7、数据基数越大，效果越明显。

                8、在where或like等筛选条件位置加索引，可以加快检索速度

                9、like '%value%'会导致索引失效。

                10、复合索引不能为null，否则导致索引失效。可以用0或空字符串替代。

                11、在列上运算，使用函数，类型转换会导致索引失效。

                12、not in 导致索引失效。

                13、字符串不加单引号(隐式转换)，导致索引失效。

                14、出现or运算，需要每个字段都带索引，否则索引失效。

        不能建立索引的情况：

                1、查询中很少使用到的列不适合建立索引，否则会降低性能，增大空间需求。

                2、选择区分度高的列，如果在性别列加索引会导致效率提升不明显。

                3、当类型为text、image、bit时不应该增加索引。

                4、当增删改的操作远远大于查询时，不适合创建索引。