表索引的定义、创建和使用

什么是索引

索引的描述：

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引的作用：

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

索引的创建查询和删除

创建索引

创建一个简单的单列索引：

create index 索引名称 on 表名称(创建索引的列);
例如：

create index index_test on table_test(aid);

创建唯一索引：
create unique index 索引名称 on 表名称 (创建索引的列);
例如：

create unique index index_test on table_test(aid);

创建一个复合索引：
create index 索引名称 on 表名称(索引列1,索引列2,…)
例如：

create index index_test on table_test(aid,aname);

查看索引

查看某张表的所有索引：
select * from all_ind_columns where table_name=‘表名’
例如：

select * from all_ind_columns where table_name='table_test'

查看所以有的索引名称：
select index_name from all_indexes where table_name = ‘表名’;
例如：

select index_name from all_indexes where table_name = 'table_test';

删除索引

删除索引：
drop index 索引名称

索引的使用

查询

只要数据库认为可以使用某个已经创建的索引，索引就会自动运用。但是使用复合索引要注意“顺序”，必须与创建的顺序一致。
例如创建时是：
create index index_test on table_test(id,name);
使用时必须是：
select * from table_test where id = ‘1’ and name = ‘whd’
而不能使用：
select * from table_test where id = ‘1’
select * from table_test where name = ‘whd’
这两种情况都不会触发索引。

插入

1.插入时如果是唯一索引的话，索引值是不能相同的。
2.如果索引值是null，则可以插入多条
3.如果索引值是“”，不可以插入多条

索引失效

索引失效的情况
1.以%开头的like查询不能使用B-Tree索引
2.隐式转换时，当列类型是字符串时候，要是where查询时候没有引起来，就也不会走索引
3.复合索引情况下不满足最左原则Leftmost,也不会使用复合索引。
4.如果MySQL估计使用索引比全表扫描更慢，则不使用索引。
以or分割开的条件，如果or前的条件列中有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫面的情况下，就没有必要多一次索引扫面增加I/O访问，一次全表扫描过滤条件就足够了。