索引设计准则:
- 1.索引并非越多越好,一个表中如有大量的索引,不仅占用磁盘空间, 而且会影响INSERT DELETE UPDATE等语句的性能,因为当表中的数据更改的同时, 索引也会进行调整和更新.
- 2.避免对经常更新的表进行过多的索引,并且索引中的列尽可能少, 而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段
- 3.数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可以比遍历索引的时间还要短, 索引可能不会产生优化效果
- 4.在条件表达式中经常用到不同值较多的列上建立索引,在不同值少的列上不要建立索引. 比如学生表的"性别" 字段上只有"男"与"女"两个不同值,因此无须建立索引.如果建立索引不但不会提高 查询效率,反而会严重降低更新速度.
- 5.当唯一性是某种数据本身的特征时,指定叭一索引,使用唯一索引需能确保定义的列的数据完整性, 以提高查询速度.
- 6在频繁进行排序或分组(即进行group by 或 order by操作)的列上建立索引,如果街排序的列有多个 可以在这些列上建立组合索引.
创建表的时候创建索引
CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY] [index_name] (col_name[length]) [ASC|DESC] UNIQUE FULLTEXT 和SPATIAL为可选参数,分别表示唯一索引 全文索引 和空间索引
- INDEX与 KEY为同义词,两者作用相同,用来指定创建索引
- index_name 指定索引的名称
- col_name 为需要创建索引的字段列
- length 可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
- ASC DESC 指定升序或者降序的索引值存储
1.创建普通索引
最基本的索引类型, 没有唯一性之类的限制,其作用只是加快对数据库的访问速度. 在book表中的year_publication字段上建立普通索引,SQL语句如下:
CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL, authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NOT NULL, comment VARCHAR(255) NULL,
year_publication VARCHAR(255) NOT NULL, INDEX(year_publication)
) 使用 SHOW CREATE TABLE table_name \G 查看表结构.
2.创建唯一索引
例:创建一个表t1,在表中的id字段关键字创建唯一索引. CREATE TABLE t1
( id INT NOT NULL,
ame char(30) NOT NUL,
UNUE INDEX uniqidx(id)
) 3.创建单列索引
CREATE TABLE t2
( id INT NOT NULL, name CHAR(50) NULL, INDEX SingleIdx(name(20))
) 4.创建组合索引
组合索引是在多个字段上创建一个索引
例:创建表t3,在表中的id name 和 age字段上建立组合索引
CREATE TABLE t3
( id INT NOT NULL, name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255), INDEX MultiIdx(id,name,age(100))
) 组合索引可起几个索引的作用,但是使用时并不是随便查询哪个字段都可以使用索引,而是遵从 "最左前缀",利用索引中最左边的列集来匹配行,这样的列集称为最左前缀. 例如这里由id,name,age 3个字段构成的索引,索引行中按 id/name/age的顺序存放,索引可以搜索下面的字段组合: (id,name,age) (id,name) 或者 id ,如果列不构成索引最左面的前缀,MySQL不能使用局部 索引,如(age)或者(name,age) 组合则不能使用索引查询.
5.创建全文索引
FULLTEXT 全文索引 可以用于全文搜索, 只有MyISAM存储引擎支持 FULLTEXT索引,并且 只为char varchar 和 text列, 索引总是对整个列进行,不支持局部(前缀) 索引.
例: 创建表 t4, 在表中的info字段上建立全文索引,SQL语句如下
CREAE TABLE t4
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTextIdx(info)
)ENGINE=MyISAM; 因为MySQL5.5中默认存储引擎为InnoDB, 在这里创建表时需要修改表的存储引擎为MyISAM.
6.创建空间索引
空间索引必须在MyISAM类型的表中创建,且空间类型字段必须为非空.
例:创建表t5, 在空间类型为GEOMETRY的字段上创建空间索引,SQL语句如下
CREATE TABLE t5
( g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
)ENGINE=MyISAM 注意:空间类型字段值的非空约束,并且表的存储引擎为MyISAM
在已经存在的表上创建索引
1.使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD[UNIQE|FULLTEXT|SPATIAL] [INDEX|KEY]
[index_name](col_name[length])[ASC|DESC] 2.使用CREATE INDEX创建索引
CREATE [UNIQUE|FULLTEXT|SPTIAL] INDEX index_nameON table_name(col_name[length],...) [ASC|DESC] 删除索引
1.使用ALTER TABLE删除索引 ,基本语法如下
ALTER TABLE table_name DROP INDEX index_name 查看是否删除
SHOW CREATE table table_name \G;
SHOW INDEX FROM table_name \G; 2.使用DROP INDEX语句删除索引
DROP INDEX index_name ON table_name; SHOW CREATE table table_name \G;SHOW INDEX FROM table_name \G; explain select * from table_name where conditions \G ; ![mysql 索引[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)