MySQL 视图、索引、外键关联策略

系统管理员 2023-02-14 00:29 41阅读 0赞

### 目录 ###

*   *   *  视图
         *   *  视图常用操作
             *  创建视图的完整语法
             *  不可更新的视图
         *  索引
         *   *  索引的优缺点
             *  常见的几种索引
             *  常用的索引操作
             *  常见的索引方式
             *  mysql对使用了索引的sql语句的优化
             *  联合索引的最左匹配原则
             *  索引的最佳实践
             *   *  哪些字段适合创建索引
                 *  创建索引的注意点
                 *  sql语句中合理运用索引
         *  主键
         *  外键关联策略

### 视图 ###

视图是一张虚表，将查询结果集保存起来，作为视图使用。实际存在的表叫作基本表。

视图的作用

*  提高安全性。grant授权用户只能操作视图，通过视图来操作基本表，可以保护基本表中的数据
 *  提高查询性能。视图只是基本表的一部分，查视图比查全表快。尤其是多表查询的时候，查视图一张表比连接多张表查询要快很多

#### 视图常用操作 ####

-- 创建视图，as指定结果集，假设cs系的id是1
    create view view_dep_cs as (select * from tb_student where dep_id=1);  -- 可以把这个视图作为cs系学生信息表来使用
    
    
    -- 修改视图定义，用新结果集覆盖原来的结果集。如果视图不存在，会自动创建
    create or replace view view_dep_cs as (select id,name from tb_student where dep_id=1);  
    
    
    -- 从视图查询数据
    select * from view_dep_cs where name='chy';  --查询cs系名字是chy的学生
    
    
    -- 删除视图
    drop view view_dep_cs;

#### 创建视图的完整语法 ####

create [algorithm=merge] view view_dep_cs as (select * from tb_student where dep_id=1) [with check option] ;

algorithm指定视图执行机制，有3个可选的值

1、merge 合并

不创建临时表，执行时会先用视图定义替换视图名，每次都是操作基本表，并不会提高查询性能，但可以增改删查。

2、temptable 临时表

把结果集保存为临时表，每次操作的都是临时表，可以提高查询性能，但只读，不能增删改。

3、undefined 未定义  
不指定algorithm就是undefined，使用数据库设置的默认值，mysql默认使用merge。

如果使用merge，还可以设置一个可选参数：with check option 检查条件。

创建视图时设置了条件where dep\_id=1，即视图中的记录都是dep\_id=1的。

如果指定了with check option，往视图中插入、更新记录时，都要满足记录的dep\_id=1这个条件，否则不执行操作；如果不指定with check option，则不要求dep\_id=1。

使用merge时往往要设置with check option。

#### 不可更新的视图 ####

即使使用merge，也不一定可以进行增删改，as指定视图数据来源，如果视图来源中使用了一下任一项，则创建的视图只读（只能查询）、不能增删改

*  聚合函数
 *  group by子句
 *  having子句
 *  distinct关键字
 *  union运算符
 *  from来源于多个表或者来源于不可更新的视图

一句话：不是直接来源于一个基本表的视图，则该视图只读、不能更新视图数据。

### 索引 ###

不使用索引时，要操作某些记录，需要遍历整张表来找到匹配的记录，时间开销大。

索引相当于数据表的目录，根据目录可直接定位到章节，根据索引可直接定位到数据表的记录，无需遍历整张表。

#### 索引的优缺点 ####

*  极大提高了检索速度，尤其是记录数很多的时候（优）
 *  索引也是一张表，要占硬盘空间，有额外的空间开销（缺）
 *  对基本表进行增改删时会同步到索引（维护索引），有额外的时间、资源开销（缺）

相较于优点，索引的缺点微不足道。

#### 常见的几种索引 ####

*  单值索引：索引中只包含数据表的一列（一个字段）
 *  唯一索引：索引中列的值唯一，一般是数据表的主键列
 *  联合索引：也叫作符合索引，索引中包含数据表的多个列
 *  全文索引：只能对MyISAM引擎的表使用，且索引中的列要是char、varchar、text等文本类型
 *  覆盖索引：如果索引中已经包含了查询sql要用到的所有字段，则会直接在索引中进行查询，不再回表。如果不是覆盖索引，则先查索引定位记录在表中的位置，再回表查询。

#### 常用的索引操作 ####

create index index_ts on tb_student(id,name);  -- on指定使用哪张表的哪些字段来创建索引。经常使用学号、姓名定位学生（记录），所以使用这2列创建索引
     
    show index from tb_student;  -- 查看某个表上所有的索引
     
    drop index index_ts on tb_student;  -- 删除索引

索引、视图都可以在数据库管理工具（比如Navicat）中直接操作。索引是在 “设计表” 中操作的。

#### 常见的索引方式 ####

1、b+树

eg. 以id字段创建索引，假设有7条记录，id 1~7  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoeV8xODg4MzcwMTE2MQ_size_16_color_FFFFFF_t_70]  
查找id=7的记录的地址：4 -> 6 -> 7

2、hash  通过hash值直接定位记录位置

eg. 使用id字段建立索引，查找id=7的记录：计算7的hash值 -> 根据hash值直接确定记录在表中的位置。

b+树要一级一级地找，hash直接定位，效率远高于b+树。但一般都是使用b+树，因为大多数存储引擎都支持b+树，hash只有memory存储引擎支持。

#### mysql对使用了索引的sql语句的优化 ####

mysql会对使用了索引的sql语句进行优化，主要优化点如下

*  自动调整同级别and条件的顺序，把使用了索引的字段放前面，尽量走索引。不要过度依赖于mysql自身的优化，尽量把走索引的条件写在前面。
 *  走索引的字段使用in限定范围时，in()中的值可以乱序。
 *  如果使用in()的字段走的是唯一索引，且()中只有一个值时，会自动优化为=等值判断。

--eg. username、tel都加了唯一索引, age没加索引
    
    
    --mysql会自动调整同级别and条件的顺序、尽量用到索引、选择合适的索引，用and连接时条件时条件可以乱序
    where age>18 and username='xxx'
     
    
    --使用了or后，同级别的条件不会走索引，慎用or
    
    --三个字段都不会走索引
    where username='xxx' and tel='xxx' or age>18
    --三个字段都不会走索引，or左边的()作为整体看待不会走索引，()里面的各字段自然不会走索引
    where (username='xxx' and tel='xxx') or age>18
    --or所在级别的条件tel、age不会走索引，但username会走索引
    where username='xxx' and (tel='xxx' or age>18)
    
    
    --模糊查询尽量使用前缀匹配，只有前缀匹配的模糊查询才会走索引
    
    --username会走索引
    where username like 'x%'
    --以下2种的username都不会走索引
    where username like '%x%'
    where username like '%xx'

#### 联合索引的最左匹配原则 ####

联合索引的最左匹配原则：使用联合索引时，会从联合索引中最左边的字段向右匹配，直到遇到没有使用到联合索引中的字段，或者遇到<、>、between之类的范围限定。

--eg. username、tel、email三个字段建立联合索引
    
    --只会用到联合索引的username字段，没有涉及tel到tel就断了，不会用到联合索引中的email字段
    where username='xxx' and email='xxx'
    
    --只会用到联合索引的username字段，遇到范围限定like直接断了
    where username='xxx' and tel like '%888%' and email='xxx'

#### 索引的最佳实践 ####

使用好索引包括2个方面：设计、建立合理的索引， sql语句中合理运用索引。

##### 哪些字段适合创建索引 #####

*  主键
 *  外键
 *  频繁作为条件的字段。eg. 经常要用where name=’ '，那就给name字段创建索引
 *  group by分组使用的字段
 *  order by排序使用的字段
 *  统计（聚合函数）使用的字段

添加主键时，会自动给主键列创建唯一索引（Unique）；添加外键时，会自动给外键列创建普通索引（Normal）。

创建索引后，从表中查找匹配的记录时数据库会自动使用合适的索引。

##### 创建索引的注意点 #####

1、记录少的表不必建立索引，一般以1千行记录为界限。

建立索引有额外的空间开销，增改删时维护索引也有额外的时间开销，记录数少时全表扫描的性能相对较高，没有必要创建索引。

2、频繁增改删的字段，维护索引开销大，不适合用于创建索引。

3、区分度低的字段建立索引没有多大意义，尽量选择区分度高（列值重复少）的字段建立索引；列值较长的字段不适合作为索引，就算要作为索引，也尽量只取列值的前缀作为索引。

4、联合索引会覆盖其中包含的单列索引，存在联合索引时再建立其中的单列索引没有意义。

5、联合索引遵循最左匹配原则，设计联合索引时，尽量将最左字段设置为值区分度高、使用频率高的字段。

6、一张表建立的索引不宜太多，一般不超过5个索引。

一张表建立太多的索引，空间占用多，数据库会花费更多的时间在索引的选择上，且增删改需要同时维护几个索引，对性能的影响较大。

7、及时删除废弃的、没有必要的索引，避免维护索引带来的不必要开销。

##### sql语句中合理运用索引 #####

字段加了索引后，sql语句使用索引时需要注意以下几点

1、数据库字段为字符串类型时，sql语句中该字段的值加不加引号都行，但加了引号才会走索引，不加引号则不会走索引。

--值没有加引号，username加了索引也不会走索引
    select ... from user where username=xxx;
    
    --username会走索引
    select ... from user where username='xxx';

2、where子句中，比较运算符左边的列参与了数学运算，则该列不会走索引；参与了函数调用的列不管是放在比较运算符的哪边，都不会走索引。

--比较运算符>=左边的age参与了数学运算，不会走索引
    select ... from user where age-18>=0
    
    --可以将数学运算移到右边 (以下2个sql的age都会走索引)
    select ... from user where age>=0+18
    --或者把要走索引的列移到比较运算符的右边
    select ... from user where 0+18<=age

3、使用联合索引时，考虑到最左匹配原则，尽量把条件中联合索引中的字段放在范围限定的前面，且字段顺序尽量与联合索引中的字段顺序保持一致，保证尽可能多的使用联合索引中的字段。

### 主键 ###

mysql创建表时，如果没有指定主键，且存在 整型+not null+自增 的字段，则该字段必须设置为主键才合法。

mysql允许创建表时不指定主键，未指定主键时mysql会自动把 整型+not null 的字段作为主键，如果不存在这样的字段，InnoDB会自动生成一个隐式主键，这个隐式主键我们看不到。

尽量手动给每个表设置主键，保证主键可控。

### 外键关联策略 ###

eg. tb\_order通过外键user\_id关联tb\_user的主键id，当update、delete tb\_user的id时，如何处理与之对应的tb\_order中的记录？  
![在这里插入图片描述][20200530110020175.png]  
设计外键时，mysql提供了4种外键关联策略

1、RESTRICT  限制（默认）

如果有外键关联了tb\_user的id，则tb\_user不能删除被关联的记录、不能更新关联记录id字段的值（会报错）。

如果要删除记录、更新id字段的值，需要先切断关联关系，比如先删除tb\_order中与之关联的记录、或者把相关记录外键字段的值置为null。

2、CASCADE  级联（最常用）

删除tb\_user中的记录时，会自动删除tb\_order中与之关联的记录；修改tb\_user中id字段的值时，会自动修改tb\_order中与之关联的记录的外键字段的值（同步变化）。

3、NO ACTION  什么都不做

删除、更新tb\_user中的user\_id字段的值时，tb\_order中与之关联的记录不作任何处理。此种策略需要存储引擎支持，如果存储引擎不支持，会自动换为RESTRICT。

4、SET NULL  置为NULL

删除tb\_user的记录，或者更新id字段的值，会自动将tb\_order中与之关联的记录的外键字段的值置为NULL。

这种方式有一个要求：设计tb\_order时，外键字段不能用NOT NULL约束。

一般使用CASCADE，未设置外键关联策略时默认为RESTRICT（为了数据安全）。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoeV8xODg4MzcwMTE2MQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200530100348537.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoeV8xODg4MzcwMTE2MQ==,size_16,color_FFFFFF,t_70
[20200530110020175.png]: https://img-blog.csdnimg.cn/20200530110020175.png