索引、视图和序列

1、索引

  • 索引：类似于书的目录，提高查询效率

创建索引

create index index_name on table_name(col_name[,col_name,...]);

index：索引关键字
index_name：索引名称
table_name：表名
col_name：索引列名
  • 索引它并不是在所有情况下都会优化查询，它是在大量数据查询时，每次查询的数据量在表的总数据量30%以下时，会提高效率
  • 索引会提高查询效率，但时会降低添加和修改的执行效率
  • 索引一般是给增删改操作比较少的表上建

索引分类

  • 单列索引：给某个列建一个索引，叫单行索引
  • 联合索引：给多个列建一个索引，叫联合索引，如果联合索引，在查询是，使用了第一个索引列作为条件，那么它会使用索引提高查询效率，如果使用了非第一个索引列作为条件，它不会使用索引

注意：在使用索引列作为查询条件时，不要在索引列上进行数学运算

索引在逻辑上和物理上都于相关的表和数据无关，创建和删除索引，都不会影响基本的表，都不会影响SQL语句的语法

原理

如果我们没有索引，搜索 name=‘scott’ ，需要检索所有的记录，给 name 字段加上索引，Oracle 就会对全表进行一次搜索，将每条记录的 name 值进行升序排序，然后构建索引条目，存储在索引段中，搜索 name=‘scott’ ，直接找索引排序后的结果

create index index_emp_sal on emp(sal);
-- 这样写条件不会使用索引
select * from emp where sal*12>10000;
-- 这样它就会使用索引
select * from emp where sal>10000/12;

删除索引

drop index index_name;

drop index index_emp_sal;

2、视图

  • 视图以经过定制的方式显示来自一个或多个表的数据
  • 视图可以视为“虚拟表”或存储的查询
  • 创建视图所依据的表称为基表

视图的优点

  • 提供了另外一种级别的表安全性
  • 隐藏了数据的复杂性
  • 简化的用户的SQL命令
  • 隔离基表结构的改变
  • 通过重命名列，从另一个角度提供数据

创建视图

create [or replace] [force] view view_name [(alias[,alias,...])] as select_statement
[with check option]
[with read only]

-- 授予scott用户创建视图的权限
grant create view to scott;
  • view：视图关键字
  • or replace：如果视图已经存在，则替换旧视图
  • force：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用
  • view_name：视图名称，符合标识符命名规范
  • alias：别名，别名的数量要和后面的查询语句的列相同
  • with check option：子句可以保证让你只能在视图的==条件之内（where后的擦好像条件）==对视图进行DML
  • with read only：表示是一个只读视图
create view view_dept_emp as select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

注意：创建视图中可以使用单行函数、分组函数和表达式，但必须为使用的函数或者表达式的字段指定名字

视图上的DML语句的限制：

  • 只能修改一个底层的基表（也就是键保留表）
  • 如果修改违反了基表的约束条件，则无法更新视图
  • 如果视图包含连接操作符、DISTINCT关键字、集合操作符、聚合函数或group by 子句，则无法更新视图
  • 如果视图包含伪劣或表达式，则无法更新视图
  • 复杂视图中，可以更新键保留表的列（单表和视图都可以作为主键的列所在的表），不可以更新非键保留表的列

3、序列

  • 序列：是Oracle中自动生成一个序列的对象，一般用来生成表的主键的值

创建序列

create sequence sequence_name
	start with startValue
	increment by inrValue
	minvalue minValue
	maxvalue maxValue
	cache|nocache
	cycle|no cycle
  • sequenct：序列关键字
  • sequenct_name：序列名称
  • start with startValue：指定序列的初始值，startValue是一个自然数
  • increment by inrValue：指定序列的值每次增加多少，inrValue是一个自然数
  • minvalue：指定序列最小值，minValue是一个自然数
  • maxvalue：指定序列最大值，maxValue是一个自然数
  • cache|no cache：指定序列缓存，默认缓存20个。oracle会预先在内存中放置一组指定大小的序列，当使用完这些序列后再生成下一组，这样会存取得快些，但当数据库关闭等情况时，下一次再生成序列时可能会使序列间断，不是一串连续的号，当不是特别需要连续的序列时最好指定
  • cycle|no cycle：指定序列是否循环生成
-- 创建序列
create sequence my_seq start with 1 increment by 1
-- 取值
select my_seq.nextval from dual;
select my_seq.currval from dual;

-- 使用案例
insert into tablename values(my_seq.nextval,value1,value2,...)
  • nextval：取序列的值，并且序列的值增加
  • currval：取序列的当前值，序列值不会变

修改序列

  • 不能更改序列的start with 参数
alter sequenct my_seq maxvalue 5000 cycle;

删除序列

drop sequence my_seq;

4、主键生成策略

  1. 数字自增长序列方式
    Oracle 中使用 sequence，mysql 使用自增长选项即可实现
  • 优点：简单、性能不错、分页排序都容易
  • 缺点：不同数据库生成方式不统一，进行数据库迁移时，要特殊处理。多台机器组成主从集群时，要特殊处理，设置好起始和步长，包括分库分表
  1. UUID universally unique identifier
    生成时会调用本机的网卡地址，芯片id码，纳秒级的时间还有其他数字混合生成，大部分生成在各个平台或者编程语言自带。
  • 优点：不冲突，使用简单，数据合并时候，或者分库分表以及微服务架构都不会冲突
  • 缺点：空间使用比较大，传输数据量大，无法排序，字符串类型，无异议
  1. 自研的生成策略
    Twitter的snowflake算法
  • 优点：不依赖与数据库，灵活方便，id也是唯一且自增的数字
  • 缺点：数据库内部不方便使用，要用到一些编程语言辅助生成