(索引大PK )ORACLE VS MYSQL INDEX SKILL ROUND ONE

最近为了更能贴近优化ORACLE 生产数据库中的某些查询，开始研究ORACLE 的索引 和 其他数据库 有何不同，究竟每个数据库中的索引的共同点和不同点，又是什么，怎么能通过多种方式来模拟或者学习某种数据库的索引的优点。

要一次能讲清楚多种数据库的索引的特点和他们的“特异功能”，这绝对是一个件不好完成的任务，所以本期先来看看同是一家公司的两个数据库产品之间的差异。 ORACLE  PK  MYSQL  INDEX 

———————————————————————————

ORACLE 数据库以索引种类多，以及各种别的数据库没有的数据库创建方式特立独行，这和ORACLE 本身数据库设计的初衷有关，经过最近对 ORACLE 数据库原理的学习，以及和其他数据库原理的比较，ORACLE 数据库面向的，的确是 大数据量，高并发的数据存取的模式，他的数据的存取方式，也就决定了索引在某些方面和其他数据库的想法的不同。

1 基于堆表的数据存储方式，提到堆表，SQL SERVER 和 MYSQL 一般是鄙视的，尤其MYSQL数据库对于堆表有天然的过敏性。ORACLE 的数据已堆表作为存储的方式，基于数据非热点的插入和读取，让数据更分散的存储，这是一种基于高并发的思维方式。

ORACLE 主要的索引有

B + TREE

Bitmap

函数索引

分区索引和全局索引

反向索引

HASH 索引

——————————————————————————————————

看一个简单的ORACLE 的查询语句

SELECT I.PRODUCT_ID,

       D.LANGUAGE_ID,

       CASE

         WHEN D.LANGUAGE_ID IS NOT NULL THEN

          D.TRANSLATED_NAME

         ELSE

          TRANSLATE(I.PRODUCT_NAME USING NCHAR_CS)

       END AS PRODUCT_NAME,

       I.CATEGORY_ID,

       CASE

         WHEN D.LANGUAGE_ID IS NOT NULL THEN

          D.TRANSLATED_DESCRIPTION

         ELSE

          TRANSLATE(I.PRODUCT_DESCRIPTION USING NCHAR_CS)

       END AS PRODUCT_DESCRIPTION,

       I.WEIGHT_CLASS,

       I.WARRANTY_PERIOD,

       I.SUPPLIER_ID,

       I.PRODUCT_STATUS,

       I.LIST_PRICE,

       I.MIN_PRICE,

       I.CATALOG_URL

  FROM OE.PRODUCT_INFORMATION I, OE.PRODUCT_DESCRIPTIONS D

 WHERE D.PRODUCT_ID(+) = I.PRODUCT_ID

   AND D.LANGUAGE_ID(+) = SYS_CONTEXT('USERENV', 'LANG')

可能看到这里，没有ORACLE知识的 DBA 已经晕头了，那个（+）什么鬼。

第一个加是左连接，第二个是相当于 

select * from OE.PRODUCT_DESCRIPTIONS D where D.LANGUAGE_ID = SYS_CONTEXT('USERENV', 'LANG')  加载到 两个表的左连接之前进行单表的过滤。  （画外音，我实在不大理解这样的写法有什么好处）

转换成大部分DBA 能看的懂的模式

SELECT I.PRODUCT_ID,

       D.LANGUAGE_ID,

       CASE

         WHEN D.LANGUAGE_ID IS NOT NULL THEN

          D.TRANSLATED_NAME

         ELSE

          TRANSLATE(I.PRODUCT_NAME USING NCHAR_CS)

       END AS PRODUCT_NAME,

       I.CATEGORY_ID,

       CASE

         WHEN D.LANGUAGE_ID IS NOT NULL THEN

          D.TRANSLATED_DESCRIPTION

         ELSE

          TRANSLATE(I.PRODUCT_DESCRIPTION USING NCHAR_CS)

       END AS PRODUCT_DESCRIPTION,

       I.WEIGHT_CLASS,

       I.WARRANTY_PERIOD,

       I.SUPPLIER_ID,

       I.PRODUCT_STATUS,

       I.LIST_PRICE,

       I.MIN_PRICE,

       I.CATALOG_URL

  FROM OE.PRODUCT_INFORMATION I, (SELECT * FROM OE.PRODUCT_DESCRIPTIONS WHERE  LANGUAGE_ID = SYS_CONTEXT('USERENV', 'LANG')) D

 WHERE D.PRODUCT_ID(+) = I.PRODUCT_ID

CREATE INDEX idx_OPE_LANGUAGE ON OE.PRODUCT_DESCRIPTIONS (LANGUAGE_ID) ONLINE;

在添加完一条索引后，原来的FULL SCAN 变为  index range scan ，当然使用不使用索引，这也要看ORACLE 查询分析器的选择。

另外ORACLE 中比较特殊的函数索引，函数索引可以将通过各种函数计算的值与函数计算的列进行比较，加快数据的读取减少I/O的操作。

SELECT *

  FROM OE.PRODUCT_INFORMATION

 WHERE SUBSTR(PRODUCT_DESCRIPTION, 1, 1) = 'P';

如果在其他的数据库里面，这样的情况一般是不好解决，可能使用LIKE 或全表扫描，而ORACLE 可以使用它独门的秘籍，函数索引来解决这个问题。在建立完函数索引后再看执行计划，是不是已经很顺眼了。

另外ORACLE 还有位图索引以及反向索引，这些都是针对特定场景，例如大数据量，但值种类很少的情况，以及频繁访问，散开热点的方式，总体来说，ORACLE的索引一直是向着大数据量的方向考虑的。（分区索引暂时不谈，case when）

——————————————————————————————————

MYSQL 作为甲骨文公司的继子，最近发展的不错，虽然开源人士的口诛笔伐以及PG近似疯狂的抢夺市场，MYSQL 还是在顽强的活着，并且没有丝毫的畏惧，与ORACLE 大而壮相比，MYSQL 则是走着小而美的路，随着PERCONA  SERVER 8 RC 的推出，未来的MYSQL 前景还是比较光明的，废话不说。

MYSQL 的索引的类型，和ORACLE 比，类型是比较少的，但少不一定不好用，不一定没有技巧.

MYSQL 一般有以下索引类型

B + TREE

聚簇索引

HASH （此HASH 非彼HASH）

前缀索引（不属于类型但比较特殊）

MYSQL 向来是反正统，那我们就先来点开胃菜，倒着说，刚才说ORACLE的函数索引的功能强大，但MYSQL 的确是没有函数索引，但如果我们有下面的一个需求。

挑拣出，下面表中 name 第一个字符是 L的，如果是ORACLE 分分钟使用函数索引解决问题，并且还能弄歪三观，直接在计算符号的左边进行函数计算，在没有特殊函数索引的加持下MYSQL 没辙吗，NO ，NO  ， NO ，我也有我的妙招。用LIKE 来查，众多 ORACLE DBA 马上 一个大大的白眼

我们先看看在建立有特殊索引的情况下，我们的表有多大，并且在对比建立了普通索引后的表和索引有多大。并且这两个索引都能完成同样的功能，但占用磁盘的空间，和速度则是不一样。

（1 特殊前缀索引）

（2 普通索引）

那我们如果执行查询，到底会走哪个索引，自然是走小的索引，将MYSQL 小而美的原子贯彻到底了。 MYSQL 有一种独特的索引叫 前缀索引，也就是建立的索引可能是这个字段的部分字符，这样的索引方式对比较长的字段，但只需要对比前面的部分字符最有利，当然还有别的用法和场景，这里就不细说了。这也是其他数据库目前还没有听说有的一个功能。（POSTGRESQL对着ORACLE 和MYSQL 一起笑，你们有的我都有，我还有你们没有的，等着）

且让POSTGRESQL 自己笑会儿去吧。

MYSQL 某些DBA 也会自豪的说MYSQL 也有HASH 索引，但他和ORACLE的HASH 索引不是一个意思。下图是官方文档，也就是说MYSQL常用的数据库引擎并不支持HASH 索引的存储，当然我们可以灵活的用DBA的心灵手巧，建立MYSQL 所谓的HASH 索引，（上次已经说过，这里不再重复，没看到的，自己找上上期的就会明白怎么在MYSQL上建立一个类似HASH索引的东西），加快查询。

同时MYSQL 也将灵活的索引页的合并和分离的选择赋予用户，这是ORACLE  SQL SERVER  不可能让用户来选择的，例如下面的语句，对T1表的索引合并，在更新和删除每行索引后，进行判断占用比，是合并还是分离，让用户有更多的自主权，性能的好坏在一个有经验的和无经验的 MYSQL DBA 身上会显现无疑，这和ORACLE 这类大型数据库数据库性能的好坏越来越和DBA无关截然相反，至少目前一个好的MYSQLDBA 和一个没有经验的 MYSQLDBA 能让你的数据库相差50% 以上的性能还是有可能的。

ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

另外 MYSQL的索引建立也和ORACLE的不同，要和你的查询语句写法匹配，也就是哪个字段在索引在那个位置也是有讲究的，错一个位置可能就让你的索引不在被MYSQL 使用。所以相对ORACLE DBA ，MYSQL DBA 还是比较费心力和经验值的。

——————————————————————————————————

索引作为 DBA 控制数据库查询和插入，更新性能的最后一道关卡，用好他则给数据库查询报以飞一般的速度，用不好，给你的数据库备份，数据插入，以及数据维护，带来的麻烦也让人头痛，所以能用逻辑解决的，不要用索引解决，终究这不是能体现一个DBA 智慧的所在。