@Catyee 2021-04-29T19:59:27.000000Z 字数 9111 阅读 658

oracle索引和约束的迁移

工作

一、索引的迁移

1.1 oracle中的索引

oracle中的索引从实现上可以分为B-Tree索引和非B-Tree索引，B-Tree索引是oracle默认的索引方式，一般创建的索引都是B-Tree索引，所以也被成为"普通索引(normal index)"，非B-tree索引指的是位图索引(bitmap索引)、基于函数的索引(function-based index)、域索引(application domain indexes)

1.1.1 普通索引：

-- 最普通的索引
CREATE INDEX index_1 ON index_test_table (col2);
-- 复合索引
CREATE INDEX index_2 ON index_test_table (col3, col4);
-- 唯一索引
CREATE UNIQUE INDEX index_3 ON index_test_table (col5);
-- 指定排序方式
CREATE INDEX index_4 ON index_test_table (col6 ASC, col7 DESC);
-- 索引压缩
CREATE INDEX index_5 ON index_test_table (col8, col9) COMPRESS ADVANCED; -- oracle 12c才支持
CREATE INDEX index_6 ON index_test_table (col10, col11) COMPRESS 1;
-- 以上可以组合：
CREATE UNIQUE INDEX index_7 ON index_test_table (col12, col13 ASC, col14 DESC) COMPRESS 1;
// 反转索引
create index PK_REV_TEST02 on TEST02(EMPNO) REVERSE;
-- 主键索引：主键索引其实是唯一索引+非空约束

二级索引(multivalue index):
二级索引只作用于JSON类型的数据。用"."语法指定需要进行索引的数据。

CREATE MULTIVALUE INDEX mvi_1 ON mytable t (t.jcol.credit_score.numberOnly());

1.1.2 基于函数的索引

CREATE INDEX index_8 ON index_test_table (12 * col15 * col16, col15, col16);
CREATE INDEX index_9 ON index_test_table (UPPER(col17));
CREATE INDEX index_10 ON index_test_table (CASE col18 WHEN 'A' THEN 'A' END);
CREATE UNIQUE INDEX index_11 ON index_test_table
   (CASE WHEN col19 =2 THEN col20 ELSE NULL END,
    CASE WHEN col19= 2 THEN col19 ELSE NULL END);

1.1.2 分区索引

oracle中的分区索引是给分区表用的，分区表只有oracle商业版才能使用，测试版和教育版无法使用。
分区索引分为局部分区索引和全局分区索引。
局部分区索引即局部索引于分区一一对应，每个分区都有一个分区索引，每个分区索引都只索引它对应的分区。
局部分区索引又分为局部前缀索引(Local prefixed index)和局部非前缀索引(
Local nonprefixed index)

局部前缀索引：以分区键作为索引定义的第一列
局部非前缀索引：分区键没有作为索引定义的第一列

全局分区索引是针对所有分区的，一个全局分区索引可能指向所有表分区。全局前缀索引（prefixed global index），索引分区键必须作为索引定义的第一列。

-- 局部分区索引
创建分区表
CREATE TABLE hash_sales
   ( prod_id        NUMBER(6)
   , cust_id        NUMBER
   , time_id        DATE
   , channel_id     CHAR(1)
   , promo_id       NUMBER(6)
   , quantity_sold  NUMBER(3)
   , amount_sold    NUMBER(10,2)
   )
PARTITION BY HASH (prod_id)
PARTITIONS 2; 
创建分区索引：
CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;
创建分区表：
CREATE TABLE hash_products 
    ( product_id          NUMBER(6)   PRIMARY KEY
    , product_name        VARCHAR2(50) 
    , product_description VARCHAR2(2000) 
    , category_id         NUMBER(2) 
    , weight_class        NUMBER(1) 
    , warranty_period     INTERVAL YEAR TO MONTH 
    , supplier_id         NUMBER(6) 
    , product_status      VARCHAR2(20) 
    , list_price          NUMBER(8,2) 
    , min_price           NUMBER(8,2) 
    , catalog_url         VARCHAR2(50) 
    , CONSTRAINT          product_status_lov_demo 
                          CHECK (product_status in ('orderable' 
                                                  ,'planned' 
                                                  ,'under development' 
                                                  ,'obsolete') 
 ) ) 
 PARTITION BY HASH (product_id) 
 PARTITIONS 4 
 STORE IN (tbs_01, tbs_02, tbs_03, tbs_04); 
CREATE INDEX prod_idx ON hash_products(category_id) LOCAL
   STORE IN (tbs_01, tbs_02);
-- 全局分区索引
范围分区
CREATE INDEX index_par1 ON index_test_table (col2)
   GLOBAL PARTITION BY RANGE (col2)
      (PARTITION p1 VALUES LESS THAN (1000),
       PARTITION p2 VALUES LESS THAN (2000),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));
hash分区
CREATE INDEX index_4 ON index_test_table (col2)
  GLOBAL PARTITION BY HASH (col2)
    PARTITIONS 4;

1.1.3 位图索引

位图索引只在商业版中可用，测试版和教育版是不能用的。

位图索引用一个索引键条目存储指向多行的指针。B-Tree索引则是一个索引键对应表中的一行数据。

比如说一张表有一列存储性别，取值可以是'男'、'女'，如果创建普通索引，也就是B-Tree索引，依然要扫描近一半的数据，但如果在这一列上建立了位图索引，针对每行的rowid，位图索引形成两个向量，男向量为10100...，向量的每一位表示该行是否是男，如果是则位1，否为0，同理，女向量位01011...，可以理解为给每行数据的性别列中为产生两个向量分别为男向量和女向量：男向量中值为男：用1表示，值不是男用0表示，同理女向量中值为女：用1表示，值不是女：用0表示。当我们使用查询语句“select * from table where Gender='男'的时候,首先取出男向量10100...，然后取出未婚向量00100...，将两个向量做与操作，这时生成新向量00100...，可以发现rowid=3的与操作之后的结果为1，表示该表的rowid=3的这行数据就是我们需要查询的结果。

位图索引适合只有几个固定值的列，如性别、婚姻状况、行政区等等，而身份证号这种类型不适合用位图索引。

位图索引在读密集的环境中能很好地工作，但是对于写密集的环境则极不适合，原因在于，一个位图索引键条目（可以理解为前面的男、女、未婚、已婚等）指向多行。如果一个会话修改了有索引的列的数据，那么大多数情况下，这个索引条目指向的所有行都会被锁定。这样就极大的影响了并发性，因为每个更新都有可能锁定数百行。

所以位图索引不适合用于OLTP的场景，一般都是用oracle建数仓的时候才会用到。

创建位图索引：

-- 普通位图索引
CREATE BITMAP INDEX bm_index_1 on par_table (gender); 
-- 位图和基于函数的索引
CREATE BITMAP INDEX typeid_i ON books (SYS_TYPEID(author));
-- bitmap join indexs
CREATE BITMAP INDEX employees_bm_idx 
ON     employees (jobs.job_title) 
FROM   employees, jobs
WHERE  employees.job_id = jobs.job_id;

1.1.4 应用域索引(application domain index)

应用域索引是特定于应用程序的自定义索引。也就是用户自定义的索引结构。
Oracle数据库本身实现的全文索引，就是应用程序域索引的一个很好的例子。

create table test_tab(dsc clob);
-- 全文索引
create index test_idx on test_tab(dsc) indextype is ctxsys.context;

应用域索引一般是第三方解决方案供应商使用，因为他们需要使用自定义的索引来访问各类非传统的数据，比如文档，空间数据，图像和视频剪辑，Oracle提供的数据类型可能不太适用。

也就是说应用域索引可能经常和自定义的数据类型组合使用。

1.2 mysql中的索引

1、普通索引

CREATE INDEX part_of_name ON customer (name(10)); 对于字符串类型，可以指定索引作用的长度

2、基于函数的索引
基于函数的索引，只在mysql8中才进行支持

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1)))); 
CREATE INDEX idx1 ON t1 ((col1 + col2)); 
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1); 
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

3、fulltext索引

 CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)

4、空间索引

CREATE TABLE `gim` (
  `path` varchar(512) NOT NULL,
  `box` geometry NOT NULL,
  PRIMARY KEY (`path`),
  SPATIAL KEY `box` (`box`)
) ;

5、二级索引
mysql支持二级索引，二级索引用于对JSON数组进行索引。

Create Table: CREATE TABLE `customers` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `custinfo` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zips` ((cast(json_extract(`custinfo`,_latin1'$.zip') as unsigned array)))
)

目前能够迁移的索引类型：
普通索引
基于函数的索引

1.3 oracle中索引信息的获取

索引信息的获取有两种途径：jdbc和系统表
如果通过jdbc：

ResultSet getIndexInfo(String catalog,String schema,String table,Boolean unique,Boolean approximate)
从resultset中可以获取的信息：
TABLE_CAT：表的目录名，可以为null
TABLE_SCHEMA：表的模式名，可以为null
TABLE_NAME：表名
NON_UNIQUE：如果为真则说明索引值不唯一，为假则说明索引值必须唯一。
INDEX_QUALIFIER：表示索引目录，可以为null
INDEX_NAME：不为空则代表索引名。
TYPE：指出索引类型
ORDINAL_POSITION：指出索引中列的序号，当TYPE是tableIndexStatistic时为0。
COLUMN_NAME：指出索引的列名，当TYPE是tableIndexStatistic时为null。
ASC_OR_DESC：为a或d。当为a的时候，说明索引是按照升序排列的；当为d的时候，说明索引是按照降序排列的；当为null时，说明不支持按一定顺序排序，或者TYPE的值是tableIndexStatistic。
CARDINALITY：指出在索引中值是唯一的列的个数，当TYPE是tableIndexStatistic时，这个值是表中的行数。
PAGES：当前索引所使用的页数，当TYPE是tableIndexStatic时，这个值表明该表使用的页数。
FILTER_CONDITION：过滤条件，可以为null

可以看到通过jdbc能获取到的信息有限，第二种就是通过查询系统表来获取，但是要通过多个sql进行组合

SELECT INDEX_name,INDEX_type,table_owner,table_name,uniqueness FROM user_indexes  WHERE table_name='INDEX_TEST_TABLE'; 
查询结果：
INDEX_11    FUNCTION-BASED NORMAL   ORA1    INDEX_TEST_TABLE    UNIQUE
INDEX_10    FUNCTION-BASED NORMAL   ORA1    INDEX_TEST_TABLE    NONUNIQUE
INDEX_9 FUNCTION-BASED NORMAL   ORA1    INDEX_TEST_TABLE    NONUNIQUE
INDEX_8 FUNCTION-BASED NORMAL   ORA1    INDEX_TEST_TABLE    NONUNIQUE
INDEX_7 FUNCTION-BASED NORMAL   ORA1    INDEX_TEST_TABLE    UNIQUE
INDEX_6 NORMAL  ORA1    INDEX_TEST_TABLE    NONUNIQUE
INDEX_4 FUNCTION-BASED NORMAL   ORA1    INDEX_TEST_TABLE    NONUNIQUE
INDEX_3 NORMAL  ORA1    INDEX_TEST_TABLE    UNIQUE
INDEX_2 NORMAL  ORA1    INDEX_TEST_TABLE    NONUNIQUE
INDEX_1 NORMAL  ORA1    INDEX_TEST_TABLE    NONUNIQUE
SYS_C007886 NORMAL  ORA1    INDEX_TEST_TABLE    UNIQUE

第一个sql能够知道索引名称、索引的类型、索引的唯一性、索引作用的表。还不知道的信息是索引作用的列

SELECT * FROM user_ind_columns WHERE table_name='INDEX_TEST_TABLE' AND index_name='INDEX_2'; 
查询结果：
INDEX_2 INDEX_TEST_TABLE    COL3    1   20  20  ASC
INDEX_2 INDEX_TEST_TABLE    COL4    2   20  20  ASC
SELECT * FROM user_ind_columns WHERE table_name='INDEX_TEST_TABLE' AND index_name='INDEX_11'; 
查询结果：
INDEX_11    INDEX_TEST_TABLE    SYS_NC00026$	1	22	0	ASC
INDEX_11	INDEX_TEST_TABLE	SYS_NC00027$    2   22  0   ASC

可以获取列名，列处于索引中的位置，升序还是降序
但是有些是虚拟列，虚拟列中实际存的是函数相关信息，进一步查询：

SELECT * FROM all_tab_cols WHERE table_name='INDEX_TEST_TABLE' AND column_name='SYS_NC00026$';
查询结果：
CASE "COL19" WHEN 2 THEN "COL20" ELSE NULL END

看到了函数的定义，但是是字符串的类型。

如果要进行函数的迁移，同样要解析字符串的语法。

1.4 索引对象的建模

在Java中用一个对象进行建模：

public class TableIndex {
    private String indexName; // 索引名称
    private IndexType indexType; // 索引类型
    private Boolean uniqueness; // 是否唯一
    private List<ColExpression> colExpressions; // 作用的列
}
public class ColExpression {
    private String colExpression; // 表达式（列或函数字符串）
    private Integer effectLength; // 列作用的长度
    private OrderType orderType; // 排序方式，降序还是升序
    private Integer seq; // 列在索引中的序号
}

通过上面的对象恢复成mysql中能执行的索引:

-- 普通索引
CREATE INDEX <index_name> on <catalog.table> (<colExpression> <orderType>, <colExpression> <orderType>...);
-- 基于函数的索引：
CREATE INDEX <index_name> on <catalog.table> ((<colExpression>, <colExpression>...));

二、约束的迁移

约束只有几种：
主键约束、唯一性约束、非空约束、检查约束、外键约束
由于主键在获取表结构的时候已经获得，不用特意处理。
唯一性约束在迁移索引的时候已经获得，不用特意处理。
非空约束在获取列的时候已经获得，不用特意处理。
要处理的只有检查约束和外键约束。

2.1 oracle中的约束

CREATE TABLE foreign_table(col1 int PRIMARY KEY, col2 int);
CREATE TABLE constraint_table
    (col1 int PRIMARY key, 
    col2 int UNIQUE, 
    col3 varchar(5) NOT NULL, 
    col4 char(1) constraint ck_constraint check(col4 in('M','W')), 
    col5 int CONSTRAINT fk_constraint REFERENCES foreign_table(col1) ON DELETE SET NULL)

一个更复杂外键约束的例子：

CREATE TABLE departments
    (department_id int, 
    manager_id int, 
    department_name varchar(20), 
    PRIMARY KEY(department_id, manager_id));
CREATE TABLE dept_20 
   (employee_id     NUMBER(4), 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    department_id   int,
    manager_id      int, 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2)
    ); 
ALTER TABLE dept_20 
    ADD CONSTRAINT fk_empid_hiredate
        FOREIGN KEY (department_id, manager_id)
        REFERENCES departments(department_id, manager_id)
        EXCEPTIONS INTO wrong_emp;
CREATE TABLE wrong_emp 
   (row_id rowid,
   owner varchar2(30),
   table_name varchar2(30),
   constraint_name varchar2(30)
   );

2.2 mysql中的约束

mysql中没有检查约束
下面是一个外键约束的例子：

create table tab1 (id int primary key);
create table tab2 (
    id int primary key,
    col1 int,
    foreign key (col1) references tab1(id));

2.3 oracle中约束信息的获取

只能通过系统表的方式获取：

SELECT * FROM user_constraints WHERE TABLE_NAME='DEPT_20';
SELECT * FROM user_cons_columns WHERE TABLE_NAME='DEPT_20' ;
SELECT * FROM user_cons_columns WHERE constraint_name = 'SYS_C007863';

2.4 约束对象的建模：

public class CheckConstraint {
    private String constraintName;
    private ConstraintType constType;
    private String colName;
    private String expression;
}
public class ForiginConstraint {
    private String constraintName;
    private ConstraintType constType;
    private String colName;
}
public class ForiginPart {
    private String referTable;
    private List<String> cols;
    private List<String> referCols;
}

mysql关闭外键约束检查：
SET FOREIGN_KEY_CHECKS=0;
外键约束还原：

ALTER TABLE <table> ADD CONSTRAINT <fk_name> FOREIGN KEY(<cols...>) REFERENCES <refer table>(<referr cols...>)

mysql打开外键约束检查：
SET FOREIGN_KEY_CHECKS=1;