数据库分表创建索引数据库分区索引

转载

架构师之光 2024-03-25 20:47:23

文章标签 数据库分表创建索引数据库字段 oracle MySQL 文章分类 数据仓库大数据

表可以按range、hash、list分区，表分区后，其上的索引和普通表上的索引有所不同，oracle对于分区表上的索引分为2类，即局部索引和全局索引，下面分别对这2种索引的特点和局限性做个总结。
局部索引local index1.局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区数，一句话，局部索引的分区机制和表的分区机制一样。
2.如果局部索引的索引列以分区键开头，则称为前缀局部索引。
3.如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。
4.局部索引只能依附于分区表上。5.前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
6.局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。
7. 局部分区索引是对单个分区的，每个分区索引只指向一个表分区；全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。
8.位图索引只能为局部分区索引。9.局部索引多应用于数据仓库环境中。

全局索引global index1.全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。
2.全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。
3.全局索引可以依附于分区表；也可以依附于非分区表。
4.全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。
5.全局索引多应用于oltp系统中。6.全局分区索引只按范围或者散列分区，hash分区是10g以后才支持。
7.oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。
8.表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。

分区索引字典
DBA_PART_INDEXES 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)
Dba_ind_partitions 每个分区索引的分区级统计信息
Dba_indexes/dba_part_indexes 可以得到每个表上有哪些非分区索引
索引重建Alter index idx_name rebuild partition index_partition_name [online nologging]
需要对每个分区索引做rebuild，重建的时候可以选择online(不会锁定表),或者nologging建立索引的时候不生成日志，加快速度。
Alter index rebuild idx_name [online nologging]
对非分区索引，只能整个index重建
分区索引实例

复制代码

--1、建分区表
CREATE TABLE P_TAB(
C1 INT,
C2 VARCHAR2(16),
C3 VARCHAR2(64),
C4 INT ,
CONSTRAINT PK_PT PRIMARY KEY (C1)
)
PARTITION BY RANGE(C1)(
PARTITION P1 VALUES LESS THAN (10000000),
PARTITION P2 VALUES LESS THAN (20000000),
PARTITION P3 VALUES LESS THAN (30000000),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
);
--2、建全局分区索引
CREATE INDEX IDX_PT_C4 ON P_TAB(C4) GLOBAL PARTITION BY RANGE(C4)
(
PARTITION IP1 VALUES LESS THAN(10000),
PARTITION IP2 VALUES LESS THAN(20000),
PARTITION IP3 VALUES LESS THAN(MAXVALUE)
);
--3、建本地分区索引
CREATE INDEX IDX_PT_C2 ON P_TAB(C2) LOCAL (PARTITION P1,PARTITION P2,PARTITION P3,PARTITION P4);
--4、建全局分区索引(与分区表分区规则相同的列上)
CREATE INDEX IDX_PT_C1
ON P_TAB(C1)
GLOBAL PARTITION BY RANGE (C1)
(
PARTITION IP01 VALUES LESS THAN (10000000),
PARTITION IP02 VALUES LESS THAN (20000000),
PARTITION IP03 VALUES LESS THAN (30000000),
PARTITION IP04 VALUES LESS THAN (MAXVALUE)
);
--5、分区索引数据字典查看
SELECT * FROM USER_IND_PARTITIONS;
SELECT * FROM USER_PART_INDEXES;

7.4.5. MySQL如何使用索引

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。如果一个表有1000行，这比顺序读取至少快100 倍。注意如果你需要访问大部分行，顺序读取要快得多，因为此时我们避免磁盘搜索。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

字符串自动地压缩前缀和结尾空格。参见13.1.4节，“CREATE INDEX语法”。

总的来说，按后面的讨论使用索引。本节最后描述hash索引(用于MEMORY表)的特征。

索引用于下面的操作：

·         快速找出匹配一个WHERE子句的行。

·         删除行。如果可以在多个索引中进行选择，MySQL通常使用找到最少行的索引。

·         当执行联接时，从其它表检索行。

·         对具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化，检查是否对索引中在key_col之前发生所有关键字元素使用了WHERE key_part_# = constant。在这种情况下，MySQL为每个MIN()或MAX()表达式执行一次关键字查找，并用常数替换它。如果所有表达式替换为常量，查询立即返回。例如：
·                SELECT MIN(key_part2),MAX(key_part2)
·                    FROM tbl_name WHERE key_part1=10;

·         如果对一个可用关键字的最左面的前缀进行了排序或分组(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有关键字元素后面有DESC，关键字以倒序被读取。参见7.2.12节，“MySQL如何优化ORDER BY”。

·         在一些情况中，可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列，为了更快，可以从索引树检索出值。
·                SELECT key_part3 FROM tbl_name
·                    WHERE key_part1=1

假定你执行下面的SELECT语句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果col1和col2上存在一个多列索引，可以直接取出相应行。如果col1和col2上存在单列索引，优化器试图通过决定哪个索引将找到更少的行来找出更具限制性的索引并且使用该索引取行。

如果表有一个多列索引，优化器可以使用最左面的索引前缀来找出行。例如，如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。

如果列不构成索引最左面的前缀，MySQL不能使用局部索引。假定有下面显示的SELECT语句。

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果 (col1，col2，col3)有一个索引，只有前2个查询使用索引。第3个和第4个查询确实包括索引的列，但(col2)和(col2，col3)不是 (col1，col2，col3)的最左边的前缀。

在oracle中，组合索引只要包含col1就走索引。

也可以在表达式通过=、>、>=、<、<=或者BETWEEN操作符使用B-树索引进行列比较。如果LIKE的参数是一个不以通配符开头的常量字符串，索引也可以用于LIKE比较。例如，下面的SELECT语句使用索引：
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第1个语句中，只考虑带'Patrick' <=key_col < 'Patricl'的行。在第2个语句中，只考虑带'Pat' <=key_col < 'Pau'的行。

下面的SELECT语句不使用索引：
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一条语句中，LIKE值以一个通配符字符开始。在第二条语句中，LIKE值不是一个常数。

如果使用... LIKE '%string%'并且string超过3个字符，MySQL使用Turbo Boyer-Moore算法初始化字符串的模式然后使用该模式来更快地进行搜索。

如果col_name被索引，使用col_name IS NULL的搜索将使用索引。

任何不跨越WHERE子句中的所有AND级的索引不用于优化查询。换句话说，为了能够使用索引，必须在每个AND组中使用索引前缀。

下面的WHERE子句使用索引：
 ... WHERE index_part1=1 AND index_part2=2 AND other_column=3
     /* index = 1 OR index = 2 */
 ... WHERE index=1 OR A=10 AND index=2
     /* optimized like "index_part1='hello'" */
 ... WHERE index_part1='hello' AND index_part3=5
     /* Can use index on index1 but not on index2 or index3 */
 ... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

 下面的WHERE子句不...........

Oracle 索引的目标是避免全表扫描，提高查询效率，但有些时候却适得其反。

例如一张表中有上百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这可能是 oracle 索引失效造成的。oracle 索引有一些限制条件，如果你违反了这些索引限制条件，那么即使你已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差。下面就是总结的能使 Oracle 索引失效的七大限制条件。

1. 没有 WHERE 子句

2. 使用 IS NULL 和 IS NOT NULL

SELECT ... FROM emp WHERE comm IS NULL; comm 列的索引会失效

3. WHERE 子句中使用函数

如果没有使用基于函数的索引，那么 where 子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。例如：

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

注意：对于 MIN, MAX 函数，Oracle 仍然使用索引。

4. 使用 LIKE ‘%T’ 进行模糊查询

5. WHERE 子句中使用不等于操作

不等于操作包括：<>, !=, NOT colum >= ?, NOT colum <= ?

对于这个限制条件可以通过 OR 替代，例如： colum <> 0 ===> colum>0 OR colum<0

6. 等于和范围索引不会被合并使用

SELECT emp_id, emp_m, salary_q ... FROM emp WHERE job='manager' AND deptno>10

job 和 deptno 都是非唯一索引，这种条件下 oracle 不会合并索引，它只会使用第一个索引。

7. 比较不匹配数据类型

dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为 oracle 会自动把 where 子句转换成 to_number(dept_id)=900198，相当于使用函数，这样就限制了索引的使用。正确写法如下：

select * from dept where dept_id = '900198';

oracle复合索引介绍(多字段索引)

在大多数情况下,复合索引比单字段索引好。但是,复合索引比单字段索引的内容原理复杂,复合索引有两个重要原则需要把握: 前缀性和可选性.如果糊里糊涂的滥用复合索引,效果适得其反。

复合索引的原理和设计建议

1.复合索引的第一个建议: 前缀性(Prefixing)

先从例子说起.假设省,市,县分别用3个字段存储数据,并建立了一个复合索引.请记住: Oracle索引,包括复合索引都是排序的.例如该复合索引在数据库索引树上是这样排序的,即先按省排序,再按市排序,最后按县排序:

省  市  县
北京  北京  东城
北京  北京  西城
北京  北京  海淀
... ...
黑龙江  哈尔滨  道里区
黑龙江  哈尔滨  道外区
黑龙江  哈尔滨  香坊区
... ...
黑龙江  齐齐哈尔龙沙区
黑龙江  齐齐哈尔铁锋区
黑龙江  齐齐哈尔富拉尔基区
... ...
湖南  长沙  芙蓉区
湖南  长沙  岳路区
湖南  长沙  开福区
... ...

oracle不是智能的,它只会按图索骥,该索引结构是先按省排序的,所以只要给出省名,就能使用索引.如果没有省名,oracle就成了无头苍蝇,乱找一气,变成了全表扫描了.例如,如果你只给一个县条件,如"开福区",oracle肯定不会使用该索引了.

2.关于skip scan index

有时候复合索引第一个字段没有在语句中出现,oralce也会使用该索引.对,这叫oralce的skip scan index功能,oracle 9i才提供的.

skip scan index功能适合于什么情况呢?如果oracle发现第一个字段值很少的情况下,例如假设emp表有gender(性别)字段,并且建立了 (gender,ename,job,mgr)复合索引.因为性别只有男和女,所以为了提高索引的利用率,oracle可将这个索引拆成('男 ',ename,job,mgr),('女',ename,job,mgr)两个复合索引.这样即便没有gender条件,oracle也会分别到男索引树和女索引树进行搜索.

但是,(gender,ename,job,mgr)索引本身设计是不合理的,它违背了复合索引的第二个原理,可选性(Selectivity),见下面描述.

3.复合索引的第二个原理:可选性(Selectivity)

您可能会问:复合索引中如何排序字段顺序?这时就要用到复合索引的第二个原理:可选性(Selectivity)规则.oracle建议按字段可选性高低进行排序,即字段值多的排在前面.例如,(ename,job,mgr,gender),(县,市,省).这是因为,字段值多,可选性越强,定位的记录越少,查询效率越高.例如,全国可能只有一个"开福区",而湖南省的记录则太多了.

4.复合索引设计建议

(1).分析SQL语句中的约束条件字段.

(2).如果约束条件字段比较固定,则优先考虑创建针对多字段的普通B*树复合索引.如果同时涉及到月份,纳税人识别号,税务机关代码3个字段的条件,则可以考虑建立一个复合索引.

(3).如果单字段是主键或唯一字段,或者可选性非常高的字段,尽管约束条件比较固定,也不一定要建成复合索引,可建成单字段索引,降低复合索引开销.

(4).在复合索引设计中,需首先考虑复合索引的第一个设计原理:复合索引的前缀性.即在SQL语句中,只有将复合索引的第一个字段作为约束条件,该复合索引才会启用.

(5).在复合索引设计中,其实应考虑复合索引的可选性.即按可选性高低,进行复合索引字段的排序.例如上述索引的字段排序顺序为:纳税人识别号,税务机关代码,月份.

(6).如果条件涉及的字段不固定,组合比较灵活,则分别为月份,税务机关代码和纳税人识别号3个字段建立索引.

(7).如果是多表连接SQL语句,注意是否可以在被驱动表(drived table)的连接字段与该表的其他约束条件字段上创建复合索引.

(8).通过多种SQL分析工具,分析执行计划以量化形式评估效果.

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。