9-视图、索引

1、视图

• 语法：

# 创建视图
create view 视图名 as select 语句 [with check option];
注： 添加with check option表示如果插入的值不满足筛选条件时，报错。
# 修改视图
alter view 视图名 as select 语句;

• 插入或修改视图数据，可以相应地对基本表进行操作，反过来也成立。但并不是所有情况都成立，比如创建的视图是在原表基础上有删选条件。

• 视图创建须知：

  • 创建的视图中使用聚合函数，不能进行修改

  • 创建试图时，不能使用视图或用户变量

  • 在存储过程中不能修改视图

  • 在临时表中不能创建视图

  • 可以在视图中继续创建视图

  • 如果使用多表连接，除非视图和多表所有列数之和相同，否则不能修改视图

2、索引

• 特点：

  • 大大提高查询速度，但降低了写入速度。故当存在大量插入数据的场景，可先禁用索引，等数据插入完成再次启用。

  • 索引也是存储数据，会占用磁盘空间，有时索引会大于数据表。

  • 多表查询时，使用索引可以避免笛卡尔积，提高查询效率，但至少有一张扫描要进行全文扫描。

  • 创建多列索引，若where不使用第一列，则索引不生效

  • 如果使用模糊查询(%)，where语句中%在最左侧

  • 使用and作为连接符，and两边都要有索引；使用or作为连接符，不使用索引

    注：如果表内容经常变化，要不定期执行{analyze table 表名}，可优化表。

• 类型:

  • 普通索引（index）：数据无约束

  • 唯一性索引（unique index）：唯一

  • 主键索引（primary index）：唯一非空。当创建了主键，自动就是主键索引。

  • 全文索引（fulltext index）: 用于检索文本信息。

  • 多列索引：创建一个索引，包括多列。先按一个字段排序，如果排不出来，再按第二个字段排序，依次类推。多列索引性能高于多个单列索引。

  • 覆盖索引：一个索引包含或覆盖所有查询字段的值

• 语法：

# 单独创建方式添加
create index 索引名 on 表名(列名)； //多列用逗号分隔
# 修改方式添加
alter table 表名 add index|keys 索引名(列名);
# 建表时添加
create table 表名(
列1 数据类型1,
列2 数据类型2,
index 索引名(列名)     //创建属性索引时，关键字index被相应替换
);
# 查看索引
show index from 表名\G
# 删除索引
alter table 表名 drop index 索引名；
# 控制索引
alter table 表名 enable|disable keys;   // 启用|禁用索引

• 索引工作方式

1、先把索引列的数据拿到索引

2、在索引里对数据列进行排序，排序默认算法为平衡二叉树(BTREE)。

3、根据索引里的数据指针指向表中对应的数据列

4、插入数据时，BTREE会进行调整，以保证平衡二叉树算法。

• explain: 优化器，可以分析select语句。

示例:
mysql> explain select * from mm1 where id=8\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mm1
   partitions: NULL
         type: ref
possible_keys: fbi
          key: fbi
      key_len: 2
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

# 注：explain执行select并非实际执行

示例说明：
- type：               扫描类型。all为全表扫描。
- possible_keys:       预测使用的索引
- key:                 实际执行使用的索引
- rows:                本次查询涉及数据行数
- Extra:               使用定位条件.若无索引为using where.