ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL数据库-索引和视图 [打印本页]

---

作者: 卖不甜枣    时间: 2024-10-14 18:05
标题: MySQL数据库-索引和视图
一、视图

1.什么是视图

• MySQL中的视图（view）是一种虚拟表，其内容由查询定义，视图本身并不包罗数据。
  
• 视图看起来和真实的表完全雷同，但其中的数据来自定义视图时用到的基本表，并且在打开视图时动态生成，类似对常用的复杂多表连接查询的结果截图拍照，之后需要用到时只需看一下视图即可无需重新查询，以节流资源
  
• 视图是一种数据库对象，其内没有存储任何数据，它只是对表的一个查询
  

2.为什么需要视图

例如常常要对student和score表进行连接查询，每次都要做表的连接，写同样的一串语句，同时由于结果 数据比力敏感，对外要求不可见。对这样的题目就可以通过视图来解决。
3.视图的作用和优点

（1）作用：

• 控制安全
  
• 生存查询数据
  

（2）优点：

• 简化操作：通过视图可以利用户将注意力会合在他所关心的数据上，利用视图的用户完全不需要关心后面对应的表的布局、关联条件和筛选条件。
  
• 进步数据的安全性：在设计数据库时可以针对差异的用户定义差异的视图，利用视图的用户只能访问他们被允许查询的结果集。
  
• 数据独立：视图的布局定义好之后，如果增加新的关系或对原有的关系增加新的字段对用户访问的数据都不会造成影响。
  

4.创建视图

（1）语法

1. create [or replace] [algorithm = {undefined | merge | temptable}]
2.     view view_name [(column_list)]
3.     as select_statement
4.     [with [cascaded | local] check option]
6. # 说明：
7.     1、or replace：如果要创建的视图名称已存在，则替换已有视图。
8.     2、algorithm：可选参数，表示视图选择的算法，默认算法是 undefined
9.         (1)undefined：未定义指定算法
10.         (2)merge:更新视图表数据的同时会更新真实表的数据
11.         (3)temptable:只能查询不能更新
12.     3、view_name：新建的视图名称。
13.     4、column_list：可选，表示视图的字段列表。若省略，则使用 select 语句中的字段列表。
14.     5、as select_statement：创建视图的 select 语句。
15.     6、with check option：表示更新视图时要保证该视图的 where 子句为真。
16.         比如定义视图：create view v1 as select * from salary > 5000;
17.         如果要更新视图，则必须保证 salary 字段的值在 5000 以上，否则报错。
18.         (1)cascaded:必须满足所有针对该视图的条件才可以更新
19.         (2)local:只需满足本视图的条件就可以更新

复制代码

（2）示例

• 准备数据：
  

1. mysql> select * from student;
2. +------+--------+------+------+---------+------------+
3. | sno  | sname  | ssex | sage | monitor | birth      |
4. +------+--------+------+------+---------+------------+
5. | s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 |
6. | s002 | 吴鹏   | 男   |   19 | s010    | 2009-10-11 |
7. | s003 | 李锐   | 男   |   19 | s003    | 2008-02-13 |
8. | s004 | 赵丁雯 | 女   |   21 | s003    | 2008-06-24 |
9. | s005 | 陈晓晓 | 女   |   18 | s005    | 2009-07-26 |
10. | s006 | 孙德胜 | 男   |   22 | s005    | 2009-05-25 |
11. | s007 | 刘琦玉 | 男   |   20 | s005    | 2009-06-21 |
12. | s008 | 李波   | 男   |   20 | s005    | 2009-10-21 |
13. | s009 | 李晨   | 男   |   19 | s010    | 1998-03-30 |
14. | s010 | 王子涵 | 女   |   23 | s010    | 2007-05-01 |
15. | s011 | 孙德胜 | 女   |   24 | s010    | 2008-05-25 |
16. +------+--------+------+------+---------+------------+
17. 11 rows in set (0.00 sec)
19. mysql> select * from sc;
20. +------+------+-------+
21. | sno  | cno  | score |
22. +------+------+-------+
23. | s001 | c001 | 85.65 |
24. | s001 | c002 | 77.45 |
25. | s001 | c003 | 60.00 |
26. | s002 | c002 | 72.35 |
27. | s003 | c001 | 74.12 |
28. | s003 | c002 | 85.05 |
29. | s004 | c001 | 45.50 |
30. | s005 | c001 | 99.00 |
31. +------+------+-------+
32. 8 rows in set (0.00 sec)

复制代码

• 创建单表的视图
  

1. mysql> create view v_student as select sno,sname,ssex,year(now())-year(birth) as age from student;
2. Query OK, 0 rows affected (0.01 sec)
4. mysql> select * from v_student;
5. +------+--------+------+------+
6. | sno  | sname  | ssex | age  |
7. +------+--------+------+------+
8. | s001 | 张玲丽 | 女   |   15 |
9. | s002 | 吴鹏   | 男   |   15 |
10. | s003 | 李锐   | 男   |   16 |
11. | s004 | 赵丁雯 | 女   |   16 |
12. | s005 | 陈晓晓 | 女   |   15 |
13. | s006 | 孙德胜 | 男   |   15 |
14. | s007 | 刘琦玉 | 男   |   15 |
15. | s008 | 李波   | 男   |   15 |
16. | s009 | 李晨   | 男   |   26 |
17. | s010 | 王子涵 | 女   |   17 |
18. | s011 | 孙德胜 | 女   |   16 |
19. +------+--------+------+------+
20. 11 rows in set (0.00 sec)

复制代码

• 创建多表连接的视图
  

1. mysql> create view v_score as select student.*,score from student
2. join sc on student.sno=sc.sno;
3. Query OK, 0 rows affected (0.01 sec)
5. mysql> select * from v_score;
6. +------+--------+------+------+---------+------------+-------+
7. | sno  | sname  | ssex | sage | monitor | birth      | score |
8. +------+--------+------+------+---------+------------+-------+
9. | s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 | 60.00 |
10. | s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 | 77.45 |
11. | s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 | 85.65 |
12. | s002 | 吴鹏   | 男   |   19 | s010    | 2009-10-11 | 72.35 |
13. | s003 | 李锐   | 男   |   19 | s003    | 2008-02-13 | 85.05 |
14. | s003 | 李锐   | 男   |   19 | s003    | 2008-02-13 | 74.12 |
15. | s004 | 赵丁雯 | 女   |   21 | s003    | 2008-06-24 | 45.50 |
16. | s005 | 陈晓晓 | 女   |   18 | s005    | 2009-07-26 | 99.00 |
17. +------+--------+------+------+---------+------------+-------+
18. 8 rows in set (0.00 sec)

复制代码

• 创建视图，字段起别名
  

1. mysql> create or replace view v_avg(sex,avg_score) as select ssex
2. ,round(avg(score),2) from student inner join sc on student.sno=sc
3. .sno group by ssex;
4. Query OK, 0 rows affected (0.01 sec)
6. mysql> select * from v_avg;
7. +------+-----------+
8. | sex  | avg_score |
9. +------+-----------+
10. | 女   |     73.52 |
11. | 男   |     77.17 |
12. +------+-----------+
13. 2 rows in set (0.00 sec)

复制代码

5.更新视图

更新视图中的数据，现实上是更新创建视图时用到的基本表中的数据
（1）以下视图不可更新：

• 包罗以下关键字的 SQL 语句：聚合函数、distinct、group by 、having、union 或 uinon all
  
• select 中包罗子查询
  
• from 一个不可更新的试图
  
• where 子句的子查询引用了 from 子句中的表
  

（2）示例

• 创建视图，限定更新
  

1. mysql> create or replace view v_age as select sno,sname,ssex,sage
2. from student where sage>20 with check option;    # 增加限制更新参数
3. Query OK, 0 rows affected (0.01 sec)
5. mysql> select * from v_age;
6. +------+--------+------+------+
7. | sno  | sname  | ssex | sage |
8. +------+--------+------+------+
9. | s004 | 赵丁雯 | 女   |   21 |
10. | s006 | 孙德胜 | 男   |   22 |
11. | s010 | 王子涵 | 女   |   23 |
12. | s011 | 孙德胜 | 女   |   24 |
13. +------+--------+------+------+
14. 4 rows in set (0.00 sec)
16. mysql> update v_age set sage=24 where sno='s011';    # 符合条件更新视图
17. Query OK, 0 rows affected (0.00 sec)
18. Rows matched: 1  Changed: 0  Warnings: 0
20. mysql> select * from student;    # 查看视图的基本表，数据已变更
21. +------+--------+------+------+---------+------------+
22. | sno  | sname  | ssex | sage | monitor | birth      |
23. +------+--------+------+------+---------+------------+
24. | s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 |
25. | s002 | 吴鹏   | 男   |   19 | s010    | 2009-10-11 |
26. | s003 | 李锐   | 男   |   19 | s003    | 2008-02-13 |
27. | s004 | 赵丁雯 | 女   |   21 | s003    | 2008-06-24 |
28. | s005 | 陈晓晓 | 女   |   18 | s005    | 2009-07-26 |
29. | s006 | 孙德胜 | 男   |   22 | s005    | 2009-05-25 |
30. | s007 | 刘琦玉 | 男   |   20 | s005    | 2009-06-21 |
31. | s008 | 李波   | 男   |   20 | s005    | 2009-10-21 |
32. | s009 | 李晨   | 男   |   19 | s010    | 1998-03-30 |
33. | s010 | 王子涵 | 女   |   23 | s010    | 2007-05-01 |
34. | s011 | 孙德胜 | 女   |   24 | s010    | 2008-05-25 |
35. +------+--------+------+------+---------+------------+
36. 11 rows in set (0.00 sec)
38. mysql> update v_age set sage=18 where sno='s011';    # 不符合条件更新视图，报错！
39. ERROR 1369 (HY000): CHECK OPTION failed 'mydb9_stusys.v_age'

复制代码

• 视图中聚合函数不可更新
  

1. mysql> select * from v_student;
2. +------+--------+------+------+
3. | sno  | sname  | ssex | age  |
4. +------+--------+------+------+
5. | s001 | 张玲丽 | 女   |   15 |
6. | s002 | 吴鹏   | 男   |   15 |
7. | s003 | 李锐   | 男   |   16 |
8. | s004 | 赵丁雯 | 女   |   16 |
9. | s005 | 陈晓晓 | 女   |   15 |
10. | s006 | 孙德胜 | 男   |   15 |
11. | s007 | 刘琦玉 | 男   |   15 |
12. | s008 | 李波   | 男   |   15 |
13. | s009 | 李晨   | 男   |   26 |
14. | s010 | 王子涵 | 女   |   17 |
15. | s011 | 孙德胜 | 女   |   16 |
16. +------+--------+------+------+
17. 11 rows in set (0.00 sec)
19. mysql> update v_student set age=30 where sno='s001';    # 报错！
20. ERROR 1348 (HY000): Column 'age' is not updatable

复制代码

• 对分组和having字段不可更新
  

1. mysql> select * from v_avg;
2. +------+-----------+
3. | sex  | avg_score |
4. +------+-----------+
5. | 女   |     73.52 |
6. | 男   |     77.17 |
7. +------+-----------+
8. 2 rows in set (0.00 sec)
10. mysql> update v_avg set avg_score=80 where sex='女';    # 报错!
11. ERROR 1288 (HY000): The target table v_avg of the UPDATE is not updatable

复制代码

6.视图利用规则

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

7.修改视图

（1）通过create or replace view 下令修改视图

1. mysql> desc v_student;
2. +-------+---------+------+-----+---------+-------+
3. | Field | Type    | Null | Key | Default | Extra |
4. +-------+---------+------+-----+---------+-------+
5. | sno   | char(4) | YES  |     | NULL    |       |
6. | sname | char(4) | YES  |     | NULL    |       |
7. | ssex  | char(2) | YES  |     | NULL    |       |
8. | age   | int     | YES  |     | NULL    |       |
9. +-------+---------+------+-----+---------+-------+
10. 4 rows in set (0.00 sec)
12. mysql> create or replace view v_student as select sno,sname,ssex,
13. sage from student;    # 将age直接读取
14. Query OK, 0 rows affected (0.01 sec)
16. mysql> desc v_student;
17. +-------+---------+------+-----+---------+-------+
18. | Field | Type    | Null | Key | Default | Extra |
19. +-------+---------+------+-----+---------+-------+
20. | sno   | char(4) | YES  |     | NULL    |       |
21. | sname | char(4) | YES  |     | NULL    |       |
22. | ssex  | char(2) | YES  |     | NULL    |       |
23. | sage  | int     | YES  |     | NULL    |       |
24. +-------+---------+------+-----+---------+-------+
25. 4 rows in set (0.00 sec)

复制代码

（2）通过alter view 下令修改视图

1. mysql> alter view v_student as select sno,sname,ssex,sage from st
2. udent where ssex='女';
3. Query OK, 0 rows affected (0.01 sec)
5. mysql> select * from v_student;
6. +------+--------+------+------+
7. | sno  | sname  | ssex | sage |
8. +------+--------+------+------+
9. | s001 | 张玲丽 | 女   |   20 |
10. | s004 | 赵丁雯 | 女   |   21 |
11. | s005 | 陈晓晓 | 女   |   18 |
12. | s010 | 王子涵 | 女   |   23 |
13. | s011 | 孙德胜 | 女   |   24 |
14. +------+--------+------+------+
15. 5 rows in set (0.00 sec)

复制代码

8.删除视图

1. drop view [if exists] view_name;

复制代码

二、索引

1.什么是索引

索引是一种特别的文件，用来快速查询数据库表中的特定纪录，是进步数据库性能的紧张方式，通俗的说，数据库索引比如是一本书前面的目录，能加速数据库的查询速度。
2.索引特点

• 索引是存放在模式（schema）中的一个数据库对象
  
• 索引在数据库中用来加速对表的查询
  
• 通过利用快速路径访问方法快速定位数据,减少了磁盘的I/O
  
• 索引与表独立存放，但不能独立存在，必须属于某个表
  
• 索引由数据库自动维护，表被删除时，该表上的索引自动被删除
  

3.索引分类

（1）按照算法分类：

• Hash索引
  

• B+Tree索引
  

（2）按照功能分类：

常用索引：

• 单列索引：一个索引只包罗单个列，但一个表中可以有多个单列索引
  
• 平凡索引INDEX：加速查找，最常用的索引，允许重复
  
• 主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
  
• 唯一索引UNIQUE：加速查找+约束（不能重复）
  
• 全文索引（FULLTEXT）：仅可用于 MyISAM 表，建立于char字段
  

4.索引优缺点

优点：

• 索引可以进步检索数据的速度，这也是创建索引的最主要的原因
  
• 对于有依赖关系的子表和父表之间的联合查询时，可以进步查询速度
  
• 利用分组和排序子句进行数据查询时，同样可以明显节流查询中分组和排序的时间
  

缺点：

• 创建和维护索引需要淹灭时间，淹灭时间的数量随着数据量的增加而增加
  
• 每一个索引要占一定的磁盘存储空间
  
• 增加、删除和修改数据时，要动态的维护索引，会降低数据的维护速度
  

5.索引的设计原则

为了使索引的利用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。
设计原则：

• 选择惟一性索引
  
• 为常常需要排序、分组和联合操作的字段建立索引
  
• 为常作为查询条件的字段建立索引
  
• 限定索引的数目
  
• 尽量利用数据量少的索引
  
• 尽量利用字段前缀来索引，即限定索引长度，索引长度：对表中特定字段的前N个字符创建索引。通常用于减少索引的大小，并且可以进步查询性能
  
• 删除不再利用大概很少利用的索引
  

6.创建索引

（1）创建表的时候创建索引

1. create table 表名 (
2.             字段名1 数据类型 [完整性约束条件…],
3.             字段名2 数据类型 [完整性约束条件…],
4.             [unique | fulltext | spatial] index | key
5.             [索引名] (字段名[(长度)] [asc | desc])
6. );

复制代码

（2）在已经存在的表上创建索引

1. create [unique | fulltext | spatial] index 索引名
2.             ON 表名 ( 字段名[(长度)] [asc | desc] );

复制代码

（3）利用alter table 语句来创建索引

1. alter table 表名 add [unique | fulltext | spatial] index
2.                     索引名 (字段名[(长度)] [asc | desc]);

复制代码

（4）示例1：建表时创建索引

1. # 创建表的同时创建普通索引
2. mysql> create table index1_tb( id int, name varchar(20), sex boolean, index(id));
4. # 创建表的同时创建唯一索引
5. mysql> create table index2_tb( id int unique, name varchar(20), unique index
6. index2(id asc) );
8. # 创建单列索引 (即普通的单列索引)
9. mysql> create table index3_tb( id int, subject varchar(30), index
10. index3(subject(10)) );
12. # 创建多列索引 (即普通的多列索引)
13. # 注意：使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。
14. mysql> create table index4_tb( id int, name varchar(20), sex char(4), index
15. index4(name,sex) );

复制代码

（5）示例2：键表后添加索引

1. # 在创建完表后为其添加索引
2. mysql> create unique index un_index on index1_tb(name);
4. mysql> alter table index3_tb add primary key(id);

复制代码

7.查看索引

（1）格式

1. # 查询索引
2. show create table 表名 \G
4. # 查询某张表中索引情况
5. show index from table_name;
7. # 使用计划查询SQL使用索引情况
8. explain select * from 表名 where id=1 \G
10. # 使用系统表查看所有索引
11. select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’;
13. # 使用系统表查看单张表的所有索引
14. select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and
15. a.table_name like '%表名%’;

复制代码

（2）示例

1. mysql> show create table index1_tb \G
3. mysql> show index from index3_tb;
5. mysql> explain select * from index1_tb where id=1 \G
6. # 注意possible_keys和key 这两个属性，possible_keys：MySQL在搜索数据记录时可以选用的各个索引，
7. key：实际选用的索引

复制代码

8.删除索引

语法：

1. drop index 索引名 on 表名

复制代码

注意：一些不再利用的索引会降低表的更新速度，影响数据库的性能，对于这样的索引，应该将其删除
9.索引原理

（1）概述
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的情势存储的磁盘上，这样的话，索引查找过程中就要产生磁盘I/O斲丧，相对于内存存取，I/O存取的斲丧要高几个数量级，以是评价一个数据布局作为索引的优劣最紧张的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的布局组织要尽量减少查找过程中磁盘I/O的存取次数。

（2）索引hash算法

• 优点：通过字段的值计算的hash值，定位数据非常快
  
• 缺点：不能进行范围查找，由于散列表中的值是无序的，无法进行大小的比力
  

（3）索引二叉树算法

• 特性：分为左子树、右子树和根节点，左子树比根节点值要小，右子树比根节点值要大
  
• 缺点：有可能产生不平衡 类似于链表的布局 
  

（4）索引平衡二叉树算法
优点：

• 它的左子树和右子树都是平衡二叉树
  
• 左子树比中间小，右子树比中间值大
  
• 左子树和右子树的深度之差的绝对值不超过1
  

缺点：

• 插入操作需要旋转
  
• 支持范围查询，但回旋查询效率较低，比如要查找大于8的，会回旋到父节点7、10。
  
• 如果存放几百条数据的情况下，树高度越高，查询效率会越慢
  

（5）索引BTREE树算法
目前大部分数据库系统及文件系统都接纳B-Tree或其变种B+Tree作为索引布局，Btree布局可以有效的解决之前的相干算法遇到的题目。

注意：InnoDB引擎利用B+Tree，InnoDB的叶节点的data域存放的是数据，相比MyISAM效率要高一些， 但是比力占硬盘内存大小。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4