MySQL/Oracle索引的创建与使用、超详细

索引的作用以及索引的优缺点

索引可以大大提高数据库的检索速度，虽然大大增加了查询速度，但是同时也会降低更新表的速度，如对表进行insert、update、delete。

因为更新表时，不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

MYSQL和Oracle的索引的区别

MYSQL默认是B+树索引

Oracle默认是B树索引

索引的类型

单列索引：即一个索引只包含单个列(字段)，一个表可以有多个单列索引，但这并不是联合索引。

联合索引：即一个索引包含多个列。

创建索引

CREATE INDEX indexName ON TABLENAME(username(length));

创建表的时候直接指定

CREATE TABLE TABLENAME(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
); 

删除索引的语法

DROP INDEX [indexName] ON TABLENAME;

唯一索引

唯一索引的值必须唯一，但允许有空值。如果他是组合索引，则列值组合必须唯一。

它有以下几种创建方式:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

注意：可以选择指定索引的长度

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表时直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);  

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list); 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list); 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list); 添加普通索引，索引值可出现多次。
• **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);**该语句指定了索引为 FULLTEXT ，用于全文索引。

注意：设置主键还需要设置索引吗？

不需要，主键具备索引的功能了，当你创建或者设置主键的时候，数据库会自动添加一个与主键对应的唯一索引。

哪些情况建立索引合适？

要加快查询速度，优化查询效率，主要原则是尽量避免全表扫描，应该考虑在where 及 order by涉及的列上建立索引

案例：使用索引对limit分页优化

SELECT id FROM A LIMIT 1000,10      很快
SELECT id FROM A LIMIT 90000，10     很慢   因为检索的数据位置数比较大

优化方法:

select id from A order by id limit 90000,10;   很快，大概0.04秒就ok了，因为id主键做索引当然快

注意：另外一些注意事项

索引的命名规则: 表名_字段名

1、需要加索引的字段，必须要在where条件中
2、数据量小的表不需要加索引
3、如果where条件中是or关系，加所以不起作用
4、符合最左原则