mysql-数据库-索引详解.doc

索引的概念 索引是一种特殊的数据库结构，可以快速查询数据库表中特定记录。索引是提高数据库性能的重要方式。表的索引是表中数据的目录。索引建立在表上，不能单独存在。 索引的作用 表的索引是表中数据的目录。如果使用索引，则扫描索引文件，根据索引项，找到元祖的地址，然后再根据地址找到数据。由于索引文件很小，而且索引有顺序，可以极大地提高查询的效率。 索引存放内容 索引列的值（有序） 物理行地址 08001 address1 08002 address2 08003 address3 08004 address4 ... ... 索引的分类 聚集索引与非聚集索引 聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引确定表中数据的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（联合索引）（不过mysql的innodb只支持主键聚集索引，不支持联合聚集索引），就像电话簿按姓氏和名字进行组织一样。 非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表可以包含多个非聚集索引。 InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），innodb会按照如下规则进行处理： 1、如果一个主键被定义了，那么这个主键就是作为聚集索引 2、如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引 3、如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。 Mysql索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引。 普通索引 创建普通索引时，不附加任何限制条件。可以创建在任何数据类型上，其值是否唯一和非空由字段自身的完整性约束条件决定。建立索引后，查询时可以通过索引进行查询。 惟一性索引 使用unique参数可以设置索引为惟一性索引。在创建惟一性索引时，限制该索引的值必须是唯一的。通过惟一性索引可以更快速的确定某条记录。主键就是一种特殊惟一性索引。 全文索引 使用fulltext参数可以设置索引为全文索引。全文索引只能创建在char、varchar、text类型字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。 单列索引 在表中单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引也可以是惟一性索引、全文索引，只要保证索引只对应一个字段即可。 多列索引 在表的多个字段创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。只有查询条件使用这些字段中第一个字段时，索引才会被使用。 空间索引 使用spatial参数可以设置索引为空间索引，空间索引只创建在空间数据类型上，可以提高系统获取空间数据的效率。空间数据类型包括geometry、point、linestring、polygon等。目前只有myisam存储引擎支持空间索引。这类索引很少使用。 索引设计原则 惟一性索引的值是惟一的，可以更快速通过索引确定某条记录。 为经常需要排序、分组、联合操作的字段建立索引 为常作为查询条件的字段建立索引 限制索引的数目 索引数目不是越多越好，每个索引都占用磁盘空间，索引越多，需要磁盘空间越多。修改表时，对索引的重构和更新很麻烦。越多的索引，更新表浪费更多的时间。 尽量使用数据量少的索引 索引值很长，查询速度会受到影响。 尽量使用前缀来索引 如果索引字段值很长，最好使用值前缀来索引。 ?删除不再使用或很少使用的索引 表中数据被大量更新或者数据使用方式改变后，原有一些索引不再被使用。数据库管理员应当定期找到这些索引，删除，减少索引对更新操作的影响。 示例： CREATE TABLE employee( id INT primary key, ename VARCHAR(50), sex char(2), age INT , PASSWORD VARCHAR(50) ); #创建索引 CREATE INDEX 索引名 ON mytable(列名(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。 CREATE INDEX index_ename ON employee(ename(20)); #注意添加索引的长度必须小于建表时给字段定义的长度，也可以不给长度， 如：CREATE INDEX index_ename ON employee(ename); #删除索引 DROP INDEX index_ename ON employee; #创建唯一索引 索引列的值必须唯一，但允许有