1 为什么使用索引
数据库对象索引其实和书的目录类似主要是提高从表中检索的速度。由于数据存储在数据库表中,所以索引是创建在数据库表对象上的,由表中的一个字段或者多个字段生成的键组成,这些键存储在数据结构中(B-树或哈希表),通过mysql可以快速有效的查询与键值相关联的字段。根据索引存储的类型可以将索引分为B型树索引(BTREE)和哈希索引(HASH).
注:InooDB和MyISAM存储引擎支持BTREE索引类型,MEMORY存储引擎支持HASH类型索引,默认为前者索引。
数据库索引的出现,除了可以提高数据库管理系统的查询速度,而且还可以保证字段的唯一性,从而实现数据库表的完整性。mysql支持6中索引:普通索引,唯一索引,全文索引 ,单列索引,多列索引,和空间索引。
以下情况适合创建索引:
* 经常被查询的字段,即在where字句中出现的字段。
* 在分组的字段,即在 GROUP BY 字句中出现的字段。
* 存在依赖关系的字表和父表之间的联合查询,即主键和外键。
* 设置唯一完整性约束的字段。
以下情况不适合创建索引:
* 在查询中很少被使用的字段。
* 拥有许多重复值的字段。
2 创建和查看索引(三种方式)
2.1 创建和查看普通索引(第一种)
语法形式:
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
属性名 数据类型,
........
INDEX(KEY) 【索引名】(属性1 【长度】 【ASC|DESC】)
);
INDEX或者KEY参数用来指定字段的索引,“索引名”参数用来指定所创建索引名,“属性1”参数指定索引所关联的字符的名称,“长度”参数指定索引的长度,“ASC或DESC”参数指定升序或降序。
例:
创建t_dept表。
CREATE TABLE t_dept(
deptno INT,
dname VARCHAR(20),
loc VARCHAR(40),
INDEX index_deptno(deptno)
);
校验索引是否创建成功
SHOW CREATE TABLE t_dept \G
2.2 在已经存在的表中创建普通索引(第二种)
语法形式:CRETAE INDEX 索引名 ON 表名 (属性名 【长度】 【ASC|DESC】)
例:创建索引
CREATE INDEX index_deptno ON t_dept (deptno);
校验索引是否创建成功
SHOW CREATE TABLE t_dept \G
2.3 通过sql语句ALTER TABLE创建普通索引(第三种)
语法形式:ALTER TABLE table_name ADD INDEX|KEY 索引名 (属性 【长度】 ASC|DESC);
例:
执行sql语句:
ALTER TABLE t_dept ADD INDEX index_deptno(deptno);
校验结果:
SHOW CREATE TABLE t_dept \G
3创建和查看唯一索引
所谓唯一索引,就是在创建索引时,限制索引的值必须唯一。通过该类型的索引可以更快速的查询某条记录,可以分为自动索引和手动索引。
3.1 创建表时创建唯一索引
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
属性名 数据类型,
........
UNIQUE INDEX(KEY) 【索引名】(属性1 【长度】 【ASC|DESC】)
);
例;
创建表
CREATE TABLE t_dept(
deptno INT UNIQUE,
dname VARCHAR(20),
loc VARCHAR(40),
UNIQUE INDEX index_deptno(deptno)
);
校验
SHOW CREATE TABLE t_dept \G
3.2在已经存在的表上创建唯一索引
语法形式:CREATE UNIQUE INDEX 索引名 ON 表名 (属性名 【长度】 【ASC|DESC】)
例:
CREATE UNIQUE INDEX index_deptno ON t_dept (deptno);
校验
SHOW CREATE TABLE t_dept \G
3.3 通过sql语句创建唯一索引
语法形式:ALTER TABLE table_name ADD UNIQUE INDEX|key 索引名 (属性 【长度】 ASC|DESC);
例:
ALTER TABLE t_dept ADD UNIQUE INDEX index_deptno(deptno);
校验
SHOW CREATE TABLE t_dept \G
4创建和查看全文索引
全文索引主要关联在数据类型为CHAR VARCHAR TEXT 字段上,以便能够快速地查询数据量较大的字符串类型的字段。
MYSQL从3.23.23版本开始支持全文索引。只能在存储引擎为MyISAM的数据库表创建全文索引,默认情况下全文索引的搜索方式不区别大小写,如果为二进制数据类型,则区分。
4.1创建表时创建全文索引
语法形式:
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
属性名 数据类型,
........
FULLTEXT INDEX(KEY) 【索引名】(属性1 【长度】 【ASC|DESC】)
);
4.2 在已经存在的表中创建全文索引
语法形式:CREATE FULLTEXT INDEX 索引名 ON 表名 (属性名 【长度】 【ASC|DESC】)
4.3通过sql语句创建全文索引
语法形式:ALTER TABLE table_name ADD FULLTEXT INDEX|key 索引名 (属性 【长度】 ASC|DESC);
5创建和查看多列索引
5.1创建表时创建多列索引
语法形式:
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
属性名 数据类型,
........
INDEX(KEY) 【索引名】(属性1 【长度】 【ASC|DESC】
.......
属性名n 【长度】 【ASC|DESC】)
);
语法形式:CREATE INDEX 索引名 ON 表名 (属性名 【长度】 【ASC|DESC】
.......
属性名n 【长度】 【ASC|DESC】
)5.3
通过sql语句创建多列索引
语法形式:ALTER TABLE table_name ADD INDEX|key 索引名 (属性名 【长度】 ASC|DESC
.......
属性名n 【长度】 【ASC|DESC】
);6删除索引
DROP INDEX index_name ON table_name;