MySQL-----视图与索引

目录

视图

1.视图

2.操作

11.索引

1.定义

2.优缺点:

3.分类

4.索引的设计原则

5.索引的使用

作业

---

视图

1.视图

❓如果需要在原表中隐藏部分字段时，怎么办？ 视图 📖视图:

是一个没有存储任何数据的表，可以对其CRUD视图所有的数据来源都来自于底层表是一个数据对象删除视图时，原数据不会变化操作视图数据时，源数据会发生变化，因为视图数据来自于原表

📘视图设计原则:

视图必须有唯一命名在mysql中视图的数量没有限制创建视图必须从管理员那里获得必要的权限视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。视图不能索引，也不能关联触发器或默认值视图可以和表同时使用

2.操作

1.创建视图
mysql> create view employee_v_1
-> as
-> select id,number,name,job from employee;
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
+-------------------+
| Tables_in_dml_lls |
+-------------------+
| department |
| employee |
| employee_v_1 |
| user |
+-------------------+
4 rows in set (0.00 sec)2.视图没有索引
mysql> desc employee_v_1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type 	   | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id 	 | bigint(20)  | NO	  | 	| 0 	  | |
| number | varchar(20) | NO   | 	| NULL    | |
| name	 | varchar(50) | YES  |	    | NULL    | |
| job 	 | varchar(50) | YES  | 	| NULL    | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)3.查询视图数据
mysql> select * from employee_v_1; 
+----+-------------+-----------+-----------------+
| id | number | name | job |
+----+-------------+-----------+-----------------+
| 1 | E2018010001 | 吴所为 | 总经理 |
| 2 | E2018070003 | 韩金龙 | 总经理 |
| 3 | E2018060002 | 王黎明 | 总经理 |
| 4 | E2018020002 | 龚爱国 | 总经理 |
| 5 | E2019050001 | 马金花 | 财务总监 |
| 6 | E2019050018 | 李昌贵 | 财务专员 |
| 7 | E2019100011 | 王建国 | 网络管理员 |
| 8 | E2019110004 | 黎锦熙 | 网络管理员 |
| 9 | E2020020023 | 繁茂森 | 销售专员 |
| 10 | E2019060005 | 张善民 | 销售经理 |
| 11 | E2019060009 | 廖云龙 | 技术总监 |
| 12 | E2019120021 | 刘盛会 | 研发工程师 |
| 13 | E2019020001 | 马明全 | 高级工程师 |
| 14 | E2019120015 | 李意 | 行政专员 |
| 15 | E2019020017 | 刘六一 | 财务总监 |
| 16 | E2020020012 | 陈超 | 研发工程师 |
+----+-------------+-----------+-----------------+
16 rows in set (0.00 sec)4.更改视图数据
mysql> insert into employee_v_1 values(null,"202222222","张三","程序员");
Query OK, 1 row affected (0.00 sec)5.原表数据也会发生变化
mysql> select * from employee;
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| id | number | name | job | leader_NO | hire_date |salary | bonus | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| 1 | E2018010001 | 吴所为 | 总经理 | NULL | 2018-01-01 |3000.00 | 4000.00 | 9 | D2019060001 |
| 2 | E2018070003 | 韩金龙 | 总经理 | NULL | 2018-07-01 |2800.00 | 4000.00 | 8 | D2019090001 |
| 3 | E2018060002 | 王黎明 | 总经理 | NULL | 2018-06-01 |2800.00 | 4000.00 | 8 | D2019060002 |
| 4 | E2018020002 | 龚爱国 | 总经理 | NULL | 2018-02-01 |2800.00 | 4000.00 | 8 | D2020010001 |
| 5 | E2019050001 | 马金花 | 财务总监 | E2018010001 | 2019-01-01 |3800.00 | 500.00 | 6 | D2019060011 |
| 6 | E2019050018 | 李昌贵 | 财务专员 | E2019050001 | 2019-04-21 |2800.00 | 800.00 | 4 | D2019060011 |
| 7 | E2019100011 | 王建国 | 网络管理员 | E2018010001 | 2019-10-01 |3200.00 | NULL | 5 | D2019060013 |
| 8 | E2019110004 | 黎锦熙 | 网络管理员 | E2019100011 | 2019-11-01 |3200.00 | NULL | 5 | D2019060013 |
| 9 | E2020020023 | 繁茂森 | 销售专员 | E2019060005 | 2020-02-01 |2800.00 | 0.00 | 4 | D2019060014 |
| 10 | E2019060005 | 张善民 | 销售经理 | E2018010001 | 2019-06-01 |2800.00 | 500.00 | 6 | D2019060014 |
| 11 | E2019060009 | 廖云龙 | 技术总监 | E2018010001 | 2019-06-01 |4800.00 | 2000.00 | 7 | D2019060012 |
| 12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 |4800.00 | 500.00 | 5 | D2019060012 |
| 13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 |4800.00 | 1000.00 | 6 | D2019060012 |
| 14 | E2019120015 | 李意 | 行政专员 | E2018070003 | 2019-12-20 |2800.00 | 500.00 | 4 | D2019090001 |
| 15 | E2019020017 | 刘六一 | 财务总监 | E2018070003 | 2019-02-16 |3800.00 | 1000.00 | 6 | D2019090011 |
| 16 | E2020020012 | 陈超 | 研发工程师 | E2019060009 | 2020-02-18 |4200.00 | 500.00 | 5 | D2019060012 |
| 17 | 202222222 | 张三 | 程序员 | NULL | NULL |NULL | NULL | 0 | NULL |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
17 rows in set (0.00 sec)6.删除张三
mysql> delete from employee_v_1 where name = "张三";
Query OK, 1 row affected (0.00 sec)7.修改视图--起别名
mysql> alter view employee_v_1 (id,员工号,姓名,工作) as select id,number,name,job from employee;
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
+-------------------+
| Tables_in_dml_lls |
+-------------------+
| department |
| employee |
| employee_v_1 |
| user |
+-------------------+
4 rows in set (0.00 sec)mysql> select * from employee_v_1;
+----+-------------+-----------+-----------------+
| id | 员工号 | 姓名 | 工作 |
+----+-------------+-----------+-----------------+
| 1 | E2018010001 | 吴所为 | 总经理 |
| 2 | E2018070003 | 韩金龙 | 总经理 |
| 3 | E2018060002 | 王黎明 | 总经理 |
| 4 | E2018020002 | 龚爱国 | 总经理 |
| 5 | E2019050001 | 马金花 | 财务总监 |
| 6 | E2019050018 | 李昌贵 | 财务专员 |
| 7 | E2019100011 | 王建国 | 网络管理员 |
| 8 | E2019110004 | 黎锦熙 | 网络管理员 |
| 9 | E2020020023 | 繁茂森 | 销售专员 |
| 10 | E2019060005 | 张善民 | 销售经理 |
| 11 | E2019060009 | 廖云龙 | 技术总监 |
| 12 | E2019120021 | 刘盛会 | 研发工程师 |
| 13 | E2019020001 | 马明全 | 高级工程师 |
| 14 | E2019120015 | 李意 | 行政专员 |
| 15 | E2019020017 | 刘六一 | 财务总监 |
| 16 | E2020020012 | 陈超 | 研发工程师 |
+----+-------------+-----------+-----------------+
16 rows in set (0.00 sec)8.替换/修改视图
mysql> create or replace view employee_v_1 (id,员工号,姓名,职位,入职日期) as select id,number,name,job,hire_date from employee where name = "陈超";
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+-------------------+
| Tables_in_dml_lls |
+-------------------+
| department |
| employee |
| employee_v_1 |
| user |
+-------------------+
4 rows in set (0.00 sec)mysql> select * from employee_v_1;
+----+-------------+--------+-----------------+--------------+
| id | 员工号 | 姓名 | 职位 | 入职日期 |
+----+-------------+--------+-----------------+--------------+
| 16 | E2020020012 | 陈超 | 研发工程师 | 2020-02-18 |
+----+-------------+--------+-----------------+--------------+
1 row in set (0.00 sec)9.删除视图
mysql> drop view employee_v_1 
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+-------------------+
| Tables_in_dml_lls |
+-------------------+
| department |
| employee |
| user |
+-------------------+
3 rows in set (0.00 sec)10.删除视图，原表不发生变化
mysql> select * from employee;
+----+-------------+-----------+-----------------+-------------+------------+--
-------+---------+-------+---------------+
| id | number | name | job | leader_NO | hire_date |
salary | bonus | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+--
-------+---------+-------+---------------+
| 1 | E2018010001 | 吴所为 | 总经理 | NULL | 2018-01-01 |3000.00 | 4000.00 | 9 | D2019060001 |
| 2 | E2018070003 | 韩金龙 | 总经理 | NULL | 2018-07-01 |2800.00 | 4000.00 | 8 | D2019090001 |
| 3 | E2018060002 | 王黎明 | 总经理 | NULL | 2018-06-01 |2800.00 | 4000.00 | 8 | D2019060002 |
| 4 | E2018020002 | 龚爱国 | 总经理 | NULL | 2018-02-01 |2800.00 | 4000.00 | 8 | D2020010001 |
| 5 | E2019050001 | 马金花 | 财务总监 | E2018010001 | 2019-01-01 |3800.00 | 500.00 | 6 | D2019060011 |
| 6 | E2019050018 | 李昌贵 | 财务专员 | E2019050001 | 2019-04-21 |2800.00 | 800.00 | 4 | D2019060011 |
| 7 | E2019100011 | 王建国 | 网络管理员 | E2018010001 | 2019-10-01 |3200.00 | NULL | 5 | D2019060013 |
| 8 | E2019110004 | 黎锦熙 | 网络管理员 | E2019100011 | 2019-11-01 |3200.00 | NULL | 5 | D2019060013 |
| 9 | E2020020023 | 繁茂森 | 销售专员 | E2019060005 | 2020-02-01 |2800.00 | 0.00 | 4 | D2019060014 |
| 10 | E2019060005 | 张善民 | 销售经理 | E2018010001 | 2019-06-01 |2800.00 | 500.00 | 6 | D2019060014 |
| 11 | E2019060009 | 廖云龙 | 技术总监 | E2018010001 | 2019-06-01 |4800.00 | 2000.00 | 7 | D2019060012 |
| 12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 |4800.00 | 500.00 | 5 | D2019060012 |
| 13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 |4800.00 | 1000.00 | 6 | D2019060012 |
| 14 | E2019120015 | 李意 | 行政专员 | E2018070003 | 2019-12-20 |2800.00 | 500.00 | 4 | D2019090001 |
| 15 | E2019020017 | 刘六一 | 财务总监 | E2018070003 | 2019-02-16 |3800.00 | 1000.00 | 6 | D2019090011 |
| 16 | E2020020012 | 陈超 | 研发工程师 | E2019060009 | 2020-02-18 |4200.00 | 500.00 | 5 | D2019060012 |
+----+-------------+-----------+-----------------+-------------+------------+--
-------+---------+-------+---------------+
16 rows in set (0.00 sec)

11.索引

1.定义

什么是索引？

索引是对数据库一列或者多列的值进行排序的一种结构。索引的建立会大大提高mysql的检索速度。如果想高效的使用mysql,而且数据量大时，需要花费事件去设计索引，建立优秀的索引规则，或优化sql

语句

2.优缺点:

优点:

索引减少了服务器需要扫描的数据量，加快了检索的速度，这就是使用索引的主要原因通过创建唯一索引，可以保证数据表中每一行数据的唯一性索引可以帮助服务器避免排序和创建临时表

缺点:

创建索引和维护索引，需要成本，随着数据量的增加成本需要增加。它会占用物理空间，除了数据表占用的空间外，每一个索引都需要占用一定的物理空间，特别是聚簇索引，更会占用大量空间对数据表数据操作(删除、修改、增加)时，索引需要动态维护，降低了整个维护速度如果具备大量重复数据时，没有建立索引的必要了。对于非常小的表，没有必要！！mysql 5.5及之后的版本，默认使用innodb引擎，因此默认会添加主键索引，底层为B+Tree

3.分类

1.根据底层: 聚簇索引(主键)和非聚簇索引(其他索引)----后续面试时，一定要冲刺这部分聚簇索引按照数据存放的物理位置为顺序---多行检索快非聚簇索引对单行的检索特别快2.根据作用点: 主键索引、唯一索引、单列索引、多列索引、全文索引等等

4.索引的设计原则

1.选择惟一性索引2.为经常需要排序、分组和联合操作的字段建立索引3.为常作为查询条件的字段建立索引4.限制索引的数目5.尽量使用数据量少的索引6.尽量使用前缀来索引7.删除不再使用或者很少使用的索引

5.索引的使用

1. 查询索引
show index from 表名
show index from 表名\G --- 竖着显示详细信息2. 创建索引
create index 索引名 on 表名(字段名) -- 1.创建普通索引 -单列索引
create unique index 索引名 on 表名(字段名) -- 2.创建唯一索引
create index 索引名 on 表名(字段1,字段n) -- 3.多列索引
create fulltext index 索引名 on 表名(字段名(只能为char\varchar\text)) -- 4.全文索引
Create table index6( -- 5.空间索引
Id int,
Space geometry not null,
Spatial index index6_sp(space)
)engine=myisam;
建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包括geometry,point,linestring和polygon类型等。平时很少用到。3. 删除索引
drop index 索引名 on 表名4. 执行计划--看索引是否命中
explain 查询sql 
EXPLAIN分析结果的含义:
table：这是表的名字。
type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
possible_keys：可能可以利用的索引的名字
Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计。
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

1.查看索引
mysql> show index from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee | 0 | PRIMARY | 1 | id | A |16 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)2.查看主键索引的执行计划
mysql> explain select * from employee where id = 10; 
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employee | NULL | const | PRIMARY | PRIMARY | 8| const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)3.竖着展示详细信息
mysql> show index from employee\G;
*************************** 1. row ***************************
Table: employee
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 16
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)4.创建索引-为name字段
mysql> create index name_index on employee(name); 
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show index from employee;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment 
|+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee | 0 | PRIMARY | 1 | id | A |16 | NULL | NULL | | BTREE | | |
| employee | 1 | name_index | 1 | name | A |16 | NULL | NULL | YES | BTREE | | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)5.查询命中情况
mysql> explain select * from employee where name = "陈超";
+----+-------------+----------+------------+------+---------------+------------
+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employee | NULL | ref | name_index | name_index |203 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)6.对比非索引的字段查询情况
mysql> explain select * from employee where job = "研发工程师"; 
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+------
---+------+------+----------+-------------+
| 1 | SIMPLE | employee | NULL | ALL | NULL | NULL | NULL| NULL | 17 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+------
---+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql> show index from employee\G:
*************************** 1. row ***************************
Table: employee
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 16
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: employee
Non_unique: 1
Key_name: name_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 16
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)7.删除索引
mysql> drop index name_index on employee; 
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0