MySQL数据库索引案例

MySQL索引可以用来快速地寻找某些具有特定值的记录,所有MySQL索引都以B-树的形式保存。例如MYSQL没有索引,执行select时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。如果表中数据有上亿条数据,查询一条数据花费的时间会非常长,索引的目的就类似电子书的目录及页码的对应关系。

如果在需搜索条件的列上创建了索引,MySQL无需扫描全表记录即可快速得到相应的记录行。如果该表有1000000条记录,通过索引查找记录至少要比全表顺序扫描快至少100倍,这就是索引在企业环境中带来的执行速度的提升。

MYSQL数据库常见索引类型包括:普通索引(normal)、唯一索引(unique)、全文索引(full text)、主键索引(primary key)、组合索引等,如下为每个索引的应用场景及区别:

普通索引:        normal,使用最广泛;

唯一索引:        unique,不允许重复的索引,允许有空值;

全文索引:        full text,只能用于MyISAM表,FULLTEXT主要用于大量的内容检索;

主键索引:        primary key又称为特殊的唯一索引,不允许有空值;

组合索引:        为提高mysql效率可建立组合索引;

MYSQL数据库表创建各个索引命令,以t1表为案例,操作如下:

主键索引 ALTER TABLE t1 ADD PRIMARY KEY ( `column` );

唯一索引 ALTER TABLE t1 ADD UNIQUE (`column`);

普通索引 ALTER TABLE t1 ADD INDEX index_name ( `column’ );

全文索引 ALTER TABLE t1 ADD FULLTEXT ( `column` );

组合索引 ALTER TABLE t1 ADD INDEX index_name ( `column1`, `column2`, `column3` );

如图11-8所示,为t1表的id字段创建主键索引,查看索引是否被创建,然后插入相同的id,提示报错:

图11-8 MYSQL主键索引案例演示

MYSQL数据库表删除各个索引命令,以t1表为案例,操作如下:

DROP INDEX index_name ON t1;

ALTER TABLE t1 DROP INDEX index_name;

ALTER TABLE t1 DROP PRIMARY KEY;

MYSQL数据库查看表索引:

show index from t1;

show keys from t1;

MYSQL数据库索引的缺点:

  • MYSQL数据库索引虽然能够提高数据库查询速度,但同时会降低更新、删除、插入表的速度,例如如对表进行INSERT、UPDATE、DELETE时,update表MySQL不仅要保存数据,还需保存更新索引;
  • 建立索引会占用磁盘空间,大表上创建了多种组合索引,索引文件的会占用大量的空间。