mysql，oracle数据库优化之索引

数据库引擎

常用的存储引擎的选择有MYISAM、InnoDB、MEMORY，不同的存储引擎支持的功能不一样，MySQL5.5之后默认的是InnoDB。绝大部分场景都是使用InnoDB引擎。

MYISAM 不支持事务, 不支持外键，其优势是访问速度快，对事务完整性没有要求或者以select、insert为主的应用程序可以选择这个引擎，支持全文索引，表锁，注意：MYISAM 在删除数据时好像类似于逻辑删除，需要定时物理删除，清理碎片：optimize table 名称；
InnoDB 支持事务，不支持全文索引，标锁，支持外键
MEMORY：查询速度极快，数据在内存中不持久化，数据库重启数据就消失，类似于缓存的作用memcache
表引擎取决于实际应用场景；日志及报表类表建议用myisam，只读的表；与交易，审核，金额相关的表建议用innodb引擎。

建议：不要混合使用存储引擎，实际场景中会有MyISAM和InnoDB混合使用的情况，但是这样有问题，比如一个事务同时操作了myisam引擎的表和innodb引擎的表，而myisam是不支持事务的，就会造成myisam表没有回滚。现在开发中绝大部分都是使用InnoDB，也不经常见到myisam，至少我工作中没见到过。

数据库索引

索引类型

主键索引(primary key)：添加了主键就有了主键索引，可以在创建表的时候指定主键，也可以在创建成功之后再增加
唯一索引(unique)：添加了唯一约束就有了唯一索引,唯一索引可以有多个null
普通索引(normal)：一般是先建表，后面再创建索引，普通索引使用的最多
全文索引(fulltext)：主要针对文本段落等，全文索引只能应用MyISAM引擎

主键索引

主键：

　　某一个属性组能唯一标识一条记录

　　如：学生表（学号，姓名，班级，性别等等），学号时唯一标识的，可以作为主键

特点：

　　最常见的索引类型

　　确保数据记录的唯一性

　　确定特定数据记录在数据库中的位置

实例：

CREATE TABLE `表名`（、

　　`GradeID` INT(11)  AUTO_INCREMENT PRIMARY KEY,

　　#或 PRIMARY KEY(`GradeID`)

）

唯一索引

作用：

      避免同一个表中某数据列中的值重复

与主键索引的区别

　　主键索引只能有一个

　　唯一索引可有多个

实例：

CREATE TABLE `Grade`（、

　　`GradeID` INT(11)  AUTO_INCREMENT PRIMARY KEY,

　　`GradeName` VARCHAR(32) NOT NULL UNIQUE

　　#或 UNIQUE KEY ` GradeID`(`GradeID`)
　　）
　　-或者， 创建唯一索引
create unique index 索引名 on 表名(列名)

普通索引

作用：

快速定位特定数据

注意：

index 和 key 关键字都可以设置常规索引

应加在查询条件的字段

不易添加太多常规索引，影响数据的插入，删除和修改操作

实例：

##创建表时添加

CREATE TABLE `result`{

　　//省略一些代码

　　INDEX / KEY `ind` (`studentNo`,`subjectNo`)

}

##创建后追加

ALTER TABLE `result` ADD  INDEX `ind` (`studentNo`,`subjectNo`);

全文索引

全文索引只能用于MyISAM引擎，通常如果用到全文索引一般通过Elasticsearch、
Solr、Lucene等技术来实现。

位图索引（oracle数据库才有）

如果在性别列上建立了位图索引，对于性别这个列，针对每行的rowid（rowid可以理解为每行的物理位置），位图索引形成两个向量，男向量为10100…，向量的每一位表示该行是否是男，如果是则位1，否为0，同理，女向量位01011，（可以理解为给每行数据的性别列中为产生两个向量分别为男向量和女向量：男向量中 值为男：用1表示，值不是男用0表示，同理女向量中 值为女：用1表示，值不是女：用0表示）

如果在婚姻状况列上建立了位图索引 对于婚姻状况这一列，位图索引生成三个向量，已婚为11000…，未婚为00100…，离婚为00010…。

oracle 位图索引检索数据的过程：
当我们使用查询语句“select * from table where Gender=‘男’ and Marital=“未婚”;”的时候 首先取出男向量10100…，然后取出未婚向量00100…，将两个向量做and操作，这时生成新向量00100…，可以发现rowid=3的and之后的结果为1，表示该表的rowid=3的这行数据就是我们需要查询的结果（如下“and的结果”为1的就是需要查询的结果），然后根据rowid找到需要的数据

什么情况下应该使用位图索引？
位图索引适合只有几个固定值的列，如性别、婚姻状况、行政区等等，而身份证号这种类型不适合用位图索引，如果用户查询的列的相异基数非常的小， 要为这些相异基数值比较小的列建索引，就需要建立位图索引。
那么何谓相异基数非常的小？可以认为行集中不同项的个数除以行数应该是一个很小的数（接近0），例如，某个列（性别）可能取值为M、F、null.如果一个表中有20000条数据，那么3/20000=0.00015，那么这就算是个相异基数很小的情况，类似的，如果有100000个不同的值，与10000000条结果相比，比值是0.01，同样也很小，也可以认为是相异基数很小的情况，都可以建立位图索引；
弊端：
虽然位图索引代表的行数多了，意味着读得快，但同样在修改同一个位图索引对应的某一条数据时，就会锁住大量的数据，这样影响的并发的数量所以实际运用中我们可以考虑读写分离

在哪些列上适合添加索引

频繁作为查询条件的列或者连接条件的列适合创建索引，即Where中的列或者是连接子句指定的列
唯一性太差的字段不适合创建索引，如性别
更新非常频繁的字段不适合创建索引
不作为where条件的字段不要创建索引
选用NOT NULL的列
尽量使用字段长度小的列作为索引
使用数据类型简单的列（int 型，固定长度）

索引顺序

ASC | DESC 选项 除非显式指定降序 (DESC)，否则列以升序 (ASC) 排序。
不管索引是升序排列还是降序排列，在执行升序或降序 ORDER BY 操作时都会使用索引。
但是如果通过混合的升序和降序属性来执行 ORDER BY，
则仅当索引是用同样的升序和降序属性创建的时才使用索引。

-- 可以显式指定索引字段的顺序，默认为升续
CREATE INDEX idx_username ON tbl_user(username ASC);

-- 对于较长的字符内容可以指定前N个字节创建索引，没必要为整个值都创建索引
CREATE INDEX idx_username ON tbl_user(contnet(20) ASC);

-- 复合索引：基于多个字段共同创建索引(区分度最大的字段放在前面，经常会被使用到的列在前面)
CREATE INDEX idx_username_email ON tbl_user(username, email);

-- 删除索引
DROP INDEX idx_username ON tbl_user;

-- 查看某个表的索引，两种方式效果一样
SHOW INDEX FROM tbl_user;
SHOW KEYS FROM tbl_user;

查询索引

show index from 表名;
show keys from 表名;

删除索引

alter table xxx drop index 索引名；

alter table xxx drop primary key;
以下操作可以不做，
- 删除重复和冗余的索引(第三方工具需要额外安装)
pt-duplicate-key-checker h=127.0.0.1

-- 更新索引统计信息及减少索引碎片
ANALYZE TABLE <table_name>

-- 清理碎片(注意会锁表)
OPTIMIZE table <table_name>

索引失效的原因

1．隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
错误的例子：select * from test where tu_mdn=13333333333;
正确的例子：select * from test where tu_mdn=‘13333333333’;

2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

3. 使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
错误的例子：select * from test where round(id)=10; 说明，此时id的索引已经不起作用了

4. 以下使用会使索引失效，应避免使用；
a. 使用 <> 、not in 、not exist、!=
b. like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。
d. 字符型字段为数字时在where条件里不添加引号.
e. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

5. 不要将空的变量值直接与比较运算符（符号）比较。
如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。

6. 不要在 SQL 代码中使用双引号。
因为字符常量使用单引号。如果没有必要限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。

索引使用原则：

在根据执行计划对SQL进行分析之后，逐步的去优化每个子查询的索引及成本使用情况，执行计划我的理解就是用来对索引进行逐步的优化的一个过程，让每个查询都尽量使用索引，在索引过程中，索引会经常性失效。
口诀

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE符号写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不能丢，SQL高级也不难
分组之前必排序，一定要上索引啊

建索引注意事项

1、单值索引，尽量选择过滤性更好的字段，例如：性别字段，过滤度为50%，识别率很差，不建议建索引
2、组合索引，索引字段的顺序可以按照识别度进行排序，识别度越高，放在越靠前
3、组合索引，尽量包含where语句中的更多字段
4、尽可能的根据分析执行计划、统计信息，去调整query的写法达到合适索引的目的
索引容易失效的几个注意点

1、不在索引列上做任何的操作（计算、函数、类型转换），会导致索引失效而转向全表扫描
2、组合索引中，如果中间某个字段使用了范围条件，则右边的列索引失效
3、尽量使用覆盖索引（索引列和查询列一致），减少使用select *
4、mysql在使用不等于（！= 或者<>）的时候，无法使用索引列会导致全表扫描
5、is null ，is not null 也无法使用索引
6、like通配符必须放在索引列的右边，否则索引失效，编程全表扫描
7、字符串不加单引号索引失效
8、少用or，用它连接索引会失效

什么还有本地索引和全局索引？

首先表分区后才有本地索引和全局索引说法，其次本地索引和全局索引概念是oracle数据库概念，本质上讲mysql只实现了本地索引， mysql每个分区表的索引时其实也是在每个分区中创建索引，每个分区维护各自的索引（其实也就是另一种local index）也就是mysql分区索引就是我们常说的那几种索引没什么全局索引之说，那么我们来说下oracle的本地索引和全局索引

本地(local index)索引

其中本地索引又可以分为有前缀(prefix)的索引和无前缀(nonprefix)的索引。而全局索引目前只支持有前缀的索引。位图索引必须是本地索引。下面就介绍本地索引以及全局索引各自的特点来说明区别；

一、本地索引特点：

 

1.    本地索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，本地索引的分区机制和表的分区机制一样。
2.    如果本地索引的索引列以分区键开头，则称为前缀局部索引。
3.    如果本地索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。
4.    前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
5.    本地索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用本地索引去给表做唯一性约束，则约束中必须要包括分区键列。
6.    本地分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，本地分区索引具有更高的可用性。
7.    位图索引只能为本地分区索引。
8.    本地索引多应用于数据仓库环境中。
例子：
partition by RANGE (id)//分区建
create index i_id on test(id) local; 因为id是分区键，所以这样就创建了一个有前缀的本地索引。
create index i_data on test(data) local 因为data不是分区键，所以这样就创建了一个无前缀的本地索引。

总结：
本地索引:创建了一个分区表后，如果需要在表上面创建索引，并且索引的分区机制和表的分区机制一样，那么这样的索引就叫做本地分区索引。本地索引是由ORACLE自动管理的，它分为有前缀的本地索引和无前缀的本地索引。什么叫有前缀的本地索引？有前缀的本地索引就是包含了分区键，并且将其作为引导列的索引。什么叫无前缀的本地索引？无前缀的本地索引就是没有将分区键的前导列作为索引的前导列的索引

全局索引(global index)

1.全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。

2.全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。

3.全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。

4.全局索引多应用于oltp系统中。

5.全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。

6.oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

7.表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。
例子：
SQL> create index orders_global_2_idx
  2  on orders(part_no)
  3   global partition by range (order_no)
  4    (partition IND1 values less than (555555),
  5     partition IND2 values less than (MAXVALUE)
  6    )
  7  ;
 global partition by range (order_no)
                                    *
ERROR at line 3://Oracle不支持非前缀的全局分区索引，如果需要建立非前缀分区索引，索引必须建成本地索引。
ORA-14038: GLOBAL partitioned index must be prefixed

总结
与本地分区索引不同的是，全局分区索引的分区机制与表的分区机制不一样。全局分区索引全局分区索引只能是B树索引，到目前为止(10gR2)，oracle只支持有前缀的全局索引。另外oracle不会自动的维护全局分区索引，当我们在对表的分区做修改之后，如果执行修改的语句不加上update global indexes的话，那么索引将不可用