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