MySQL入门很简单-索引

1、索引是一种特殊的数据结构，可用来快速查询数据库表中的特定记录。是提高性能的重要方式，mysql中所有的数据类型都可以被索引。包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。是创建在表上的，对数据库表中一列或多列的值进行排序的一种结构。通过索引，查询数据时可不必读完记录中的所有信息，只是查询索引列。

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

     两种索引存储类型，包括B型树索引和哈希索引。

     InnoDB和MyISAM存储引擎支持BTREE索引。MEMORY存储引擎支持HASH索引和BTREE索引，默认为哈希索引。

3、索引的优势是可以提高检索数据的速度；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，也可以提高查询中分组和排序的时间；

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

     索引可以提高查询的速度，但会影响插入记录的速度。向有索引的表中插入记录时，数据库系统会按索引进行排序，在插入大量记录的情况下速度影响更明显。因此在插入大量数据时需要先删除表中的索引，然后插入数据，插入完成后再创建索引。

4、索引的分类

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

     2.唯一性索引，使用UNIQUE参数设置，创建时限制该索引的值必须为唯一的。主键是一种特殊的唯一性索引。

     3.全文索引，使用FULLTEXT参数设置，只能创建在char/varchar/text类型的字段是。查询数据量较大的字符串类型字段时，可使用全文索引。mysql从3.23.23版本开始支持全文索引，但只有myisam存储引擎支持全文索引。默认全文索引的搜索执行方式不区分大小写，但索引的列使用二进制排序后，可执行区分大小写的全文索引。

     4.单列索引，在表中的单个字段上创建索引。只根据该字段进行索引，单列索引可以是普通索引也可以是唯一性索引，还可以是全文索引，只要保证该索引只对应一个字段即可。

     5.多列索引，在表的多个字段创建索引，该索引指向创建时对应的多个字段，可通过这几个字段进行查询。但只有在查询条件中使用了这些字段的第一个字段时，索引才会被使用。

     6.空间索引，使用SPATIAL参数设置，只能建立在空间数据上。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。只有ＭｙISAM支持空间检索，且索引字段不能为空。

５、索引的设计原则

     选择唯一性索引；

     为经常需要排序、分组和联合操作的字段建立索引；如经常需要order by, group by, distinct, union等操作。

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

     限制索引的数目；索引的数目对修改表和更新表的效率都变得麻烦。

     尽量使用数据量少的索引。

     尽量使用前缀来索引，只检索字段的前面的若干个字段。

     删除不再使用或者很少使用的索引。

6、创建表的时候创建索引

     create table 表名 (属性名 数据类型 [完整性约束条件]，

                                   ...

                                  属性名 数据类型

                                  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [别名] (属性名1 [(长度)] [ASC|DESC] ) );

     unique,fulltext,spatial为可选参数； index和key指定索引两者取一；别名可选；属性名1是表中属性名定义好的字段，长度可选，指定索引长度，字符串类型的可用；ASC和DESC可选，表升序和降序。

     create table index2(id int unique, name varchar(20), unique index index2_id(id asc));  //id不为unique时也可以建立唯一性索引，但达不到提高查询速度的目的

     create table index3(id int, info varchar(20), fulltext index index3_info(info) ) ENGINE=MyISAM;  //全文索引

     create table index4(id int, subject varchar(30), index index4_st(subject(10)  ));  //单列索引

     create table index5(id int, name varchar(20), sex char(4), index index5_ns(name, sex) );  //多列索引

     EXPLAIN select * from index5 where name='aaa' \G   //使用EXPLAIN语句查看索引的使用情况。extra的显示区显示是否正在使用索引

     create table index6(id int, space geometry not null, spatial index index6_sp(space)engine=myisam;  //space类型非空

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

     create [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 on 表名 (属性名 [(长度)] [ASC|DESC] );

     create index index7_id on example0(id);

     create unique index index8_id on index8(couse_id);

     create fulltext index index9_info on index9(info);  //info字段必须为char varchar text等类型

     create index index10_addr on index10(address(4));  //单列索引

     create index index11_na on index11(name, address);  //创建多列索引

8、用alter table语句创建索引

     alter table 表名 add [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 (属性名[(长度)] [ASC|DESC]);

9、删除索引

     一些不再使用的索引会降低表的更新速度，影响数据库性能，应该将其删掉。

     drop index 索引名 on 表名;

     

10、MySQL中索引、主键和唯一性的区别是：

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

     主键是表中数据的唯一标识，不同的记录的主键值不同，在建立主键时，系统自动建立一个唯一性索引。

     唯一性也是建立在表中的一个或几个字段上，目的是为了对于不同的记录，具有唯一性的字段的值不同。

11、对已经建立了索引的表中插入数据，插入一条数据就要对该记录按索引进行排序，因此导入大量数据的时候速度会很慢。解决的方法是在无任何索引的情况下插入数据然后建立索引。