1.什么是索引?
索引是单独的、存储在磁盘上的数据结构,他包含着对数据表里所有记录引用的指针。可以快速找出某个或者多列的值。当没有索引的时候执行select * from table num = 1000;这时候你查找到这条数据必须遍历表,知道找到这条数据。但是如果有索引的话,他通过索引可以直接定位到这条数据。比如你用hash来存储索引,数据库只要计算hash值,取到这条数据的引用就可以拿到这条数据。
2.索引的存储类型
1.HASH
2.BTREE
不同的数据库引擎支持不同的索引,比如MyISAM/InnoDB只支持BTREE,MEMORY/HEAP支持HASH和BTREE。
3.索引的优点和缺点
优点:加快查询速度
可以通过创建唯一索引,保证数据库表中数据的唯一性;
在使用分组和排序字句可以显著减少查询分组排序的时间;
缺点:创建索引需要时间;
创建索引会消耗磁盘空间
对表中数据经行增加、删除、修改的时候,索引需要动态维护,降低维护速度;
4.索引分类
1).普通索引和唯一索引
普通索引是MySql中的基本索引类型,允许在定义索引的列中插入重复值和空值.
唯一索引,索引列的值唯一,允许有空值,如果是组合索引,则组合列值必须唯一。主键索引是特殊的唯一索引,不允许有空值。
2).单列索引和组合索引
单列索引就是索引只包含单列,一张表可以有多个单列索引。组合索引是在多个字段上创建的索引;
3).全文索引
全文索引只有MyISAM引擎支持,类型为FULLTEXT,在定义索引的列上支持全文查找,允许插入重复值和空值,全文索引可以在CHAR、VARCHAR、TEXT数类型的列上创建
4).空间索引
MYISAM支持,其他不可以,空间索引是对空间数据类型的字段建立的索引,空间索引的数据类型GEOMETRY,POINT,LINESTRING和POLYGON.。
5.创建索引的原则
1).索引的个数要适当,如果索引过多,会占用大量的磁盘空间,同时影响inset,delete,update,因为在进行这些操作的时候同时要对索引进行调整更新。
2).经量对经常跟新的表经行过多索引,索引的列也要京可能少,经常查询的表应该增加索引,
3).数据较少的表不要使用索引
4).对SQL语句中条件表达式多的建立索引,值少的不要建立索引。
5).当唯一性是数据的特征的时候建立唯一索引
6).对频繁排序分组的列上建立索引(group by 或者order by)
6.创建索引
创建索引的语法
[UNIQUE|FULLTEXT|SPATIAL] [INDEX] [index_name]{column_name[length]}[ASC|DESC]
UNIQUF,FULLTEXT,SPATIAL为可选参数,表示唯一索引,全文索引和空间索引index_name为索引名称,column_name表 示创建索引的列名,最后是排序方式。
1).创建普通索引,在创建表的时候创建普通索引,使用show create table person,查看生成表的SQL语句
2).创建唯一索引
语句UNIQUE INDEX UniqIdx(column_name);
3).创建单列索引
语句INDEX SingleIdx(column_name(length))
4).创建组合索引
语句INDEX MultiIdx(column_name,column_name)
5).创建全文索引(mysql现在存储引擎是Innodb,所以不支持全文索引,记得切换为MyISAM否则会报错)
语句FULLTEXT INDEX FullTxtIdx(column_name);
6).创建空间索引 空间索引只能在空间类型
语句:SPATIAL INDEX spatIdx (column_name);
上面都是在创建表的时候建立索引,如果在创建好的表上创建索引;
SQL语句:ALTER TABLE table_name ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX] [index_name]{column_name[length]}[ASC|DESC]
或者
CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX] [index_name]{column_name[length]} ON table_name[ASC|DESC]
7.删除索引
ALTER TABLE table_name DROP INDEX index_name;
或者Drop index index_name on table_name;
如果不知道index_name可以使用show create table table_name 查看index_name;