mysql索引

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结构。索引可以提高查询的速度。

通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

索引可以提高数据库的性能。

索引建立在一个或者几个字段上。建立了索引后，表中的数据就按照索引的一定规则排序。这样可以提高查询速度。

主键是表中数据的唯一标识，建立主键时，系统会自动建立一个唯一性索引。

对已经建立索引的表中插入数据，插入一条数据就要对该记录按照索引排序。因此，导入大量数据的时候会很慢。一般先删除索引，再插入，之后恢复索引。

不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。

索引有两种存储类型，包括B型树索引(BTREE)和哈希索引(HASH)两种。InnoDB和MyISAM支持BTREE，MEMORY两者都支持，默认为HASH。

优点：提高检索数据的速度，对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度，使用分组排序子句进行查询，节省时间。

缺点：创建和维护索引需要耗费时间，随着数据量的增加而增加；需要占物理空间；增加删除修改数据时，要动态的维护索引，造成数据维护速度降低。

创建索引

       创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。

索引的分类

1、普通索引

       不附加任何限制条件，可以创建在任何数据类型中，值是否唯一和非空由字段本身的完整性约束条件决定。查询时可以通过索引进行查询。

       KEY|INDEX   `city_ids` (`city_ids`), //前面是索引名，括号中的为要建立索引的字段

2、唯一性索引

      唯一性索引，限制该索引值必须是唯一的，主键就是一种特殊的唯一性索引。可以快速确定某条记录。如果出现重复，会降低查询效率

      UNIQUE KEY `unique_sn` (`order_sn`),

3、全文索引

       只能用在CHAR、VCHAR或TEXT字段上。只有MyISAM存储引擎支持全文检索。默认下，全文索引的搜索执行方式不区分大小写。但是索引列使用二进制排序后，可以执行区分大小写的全文索引。

       FULLTEXT KEY `deal_cate_match` (`deal_cate_match`),

4、单列索引

     只要保证该索引值对应一个字段即可。

5、多列索引

     在表的多个字段上创建一个索引。可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中的第一个字段时，索引才会被使用。

     FULLTEXT KEY `all_match` (`name_match`,`deal_cate_match`,`tag_match`,`type_match`)

6、空间索引

     SPATIAL，只能建立在控件数据类型上，如GEOMETRY、PONT等，只有MyISAM存储引擎支持空间检索。

设计原则

1、为经常需要ORDER BY、GROUP BY、UNIOM等操作的字段建立索引

2、为常作查询条件的字段建立索引

3、限制索引数目

4、尽量使用数据量少的索引

5、尽量使用前缀进行索引，只检索字段的前面的若干字符，可以提高检索速度 KEY `index4` (`subject`(10))

在已经存在的表上创建索引

CREATE  INDEX  indexname ON  tabname(colname);

ALTER  TABLE  tabname   ADD  INDEX  indexname(name);

删除索引

DROP INDEX indexname ON tabname;