MySQL数据库索引
分类:普通索引、唯一索引、主键索引、组合索引
普通索引
特点:1)最基本的MySQL数据库索引
2)它没有任何限制
语句:1)CREATE INDEX indexName ON mytable(username(length));
2)ALTER mytable ADD INDEX [indexName] ON (username(length));
3)DROP INDEX [indexName] ON mytable;
唯一索引
特点:1)MySQL数据库索引列的值必须唯一
2)允许有空值
语句:1)CREATE UNIQUE INDEX indexName ON mytable(username(length));
2)ALTER mytable ADD UNIQUE [indexName] ON (username(length));
主键索引
特点:1)它是一种特殊的唯一索引
2)不允许有空值
语句:1)CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID));
组合索引
语句:1)CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
聚集索引
含义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引
适用:InnoDB
特点:1)如果一个主键被定义了,那么这个主键就是作为聚集索引
2)如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引
3)如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增
4)在数据表创建上INNODB聚集索引存储的是一个文件,后缀为.frm
5)InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录
6)采用B+树的结构
适用:InnoDB 优点:查询效率相当快。只要找到第一个索引记录的值,其余的连续性的记录也一定是连续存放的
缺点:修改起来比较版,因为它需要保持表中记录和索引的顺序需要一致,在插入新记录的时候就会对数据也重新做一次排序
非聚集索引
含义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引
适用:MyISAM
特点:1)在数据表创建上Myisam存储为三个文件.frm、.MYD、.MYI
2)MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址
3)叶子层并不喝世纪数据叶相互重叠,而是采用叶子层包含一个指向表中的记录指针
4)采用B+树的结构
缺点:索引的层次比较多,但是不会造成数据的重排