MySQL数据库学习笔记(二)

mysql索引 数据库对象索引优点类似于书的目录.主要是为了提高从表中检索数据的速度. 根据索引存储的类型分为:B-树索引或者哈希索引 InnoDB 和MyISAM存储引擎支持BTREE类型索引;MEMORY存储硬气支持HASH类型索引.默认为前者索引.

索引虽然可以跳高查询速度，同时能够保证字段的唯一性，但是创建索引则会占据许多的磁盘空间. 所以索引的创建有利也有弊. MySQL支持6中索引: 普通索引 唯一索引 全文索引 单列索引 多列索引 空间索引 以下情况适合创建索引: (1)经常被查询的字段，即在where子句中出现的字段 (2)在分组的字段,即在group by 子句中出现的字段 (3)存在依赖关系的子表和父表之间的联合查询，即主键或外键字段. (4)设置唯一约束性约束的字段 以下情况不适合创建索引 (1)在查询中喝少被使用的字段 (2)拥有许多重复值的字段.

####创建表的时候创建索引 create table table_name( 属性名 属性类型, 属性名 属性类型, …

属性名 属性类型,

index | key [索引名](属性名 [(长度)] [asc|desc])

); index或者key 参数用来指定字段为索引, “索引名” 用来指定创建索引没那个, “属性名”用来指定索引所关联的字段的名称, “长度”用来指定索引的长度 asc|desc 升序|降序 [] 表示可选项。

####创建表的时候指定索引. CREATE TABLE dept ( deptno int(11) NOT NULL, dname varchar(32) DEFAULT NULL, PRIMARY KEY (deptno), KEY deptno_index (deptno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

####已经存在的表上创建普通索引 create index 索引名 on 表名 (属性名 [(长度)] [asc|desc]);

####通过alter table 创建普通索引 alter table table_name add index|key 索引名 (属性名 [(长度)] [asc|desc]);

####创建和查询唯一索引 唯一索引: 创建索引的时候，限制索引的值必须是唯一.(比如primary key 特点唯一不重复不为null)

根据创建索引方式进行分类:自动索引和手动索引 (1)自动索引: 在数据库表里面设置完整约束的时候，改变会被系统自动创建索引; 比如设置表中某个字段设置主键或唯一完整性约束时 系统会自动创建关联该字段的唯一索引

(2)手动创建索引:手动在表上创建索引.

####创建表的时候创建唯一索引 create table table_name( 属性名 属性类型, 属性名 属性类型, …

属性名 属性类型,

unique index | key [索引名](属性名 [(长度)] [asc|desc])

); ####执行计划 explain select * from t_dept where deptno = 10 \G

####在已经存在的表上创建唯一索引 unique create index 索引名 on 表名 (属性名 [(长度)] [asc|desc]);

####通过alter table 方式添加唯一索引 alter table table_name add unique index|key [索引名](属性名 [(长度)] [asc|desc]);

####使用show create table table_name查看我们创建的索引。

3.创建全文索引 全文索引关联的数据类型为char varchar 和text的字段上，从而快速查询数据量大的字符串类型的字段 只能在MyISAM的存储引擎上创建全文索引。 默认情况下全文搜索中心方式不区分大小写，如果关联的字段为而二进制，则区分大小写的搜索方式执行

####创建表的时候创建全文索引 ####适用fulltext index 来指定。 create table table_name( 属性名 属性类型, … 属性名 属性类型, fulltext index |key [索引名](属性名 [(长度)] [asc|desc]) );

CREATE TABLE t_dept ( deptno int(11) NOT NULL AUTO_INCREMENT, dname varchar(32) DEFAULT NULL, loc varchar(50) DEFAULT NULL, PRIMARY KEY (deptno), FULLTEXT KEY loc_index (loc) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> explain select * from t_dept where loc = ‘CQ’ \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_dept type: system possible_keys: loc_index key: NULL key_len: NULL ref: NULL rows: 1 Extra: 1 row in set (0.00 sec)

possible和key都为loc_index才表示索引已经存在，并且开始启用。

####已经存在的表上创建全文索引 create fulltext index 索引名 on 表名 (属性名[(长度)] [asc|desc])

####适用SQL语句alter table方式添加索引 alter table table_name add fulltext index|key [索引名](属性名 [(长度)] [asc|desc]);

4.多列索引创建和查看 ####创建表的时候创建多列索引 create table table_name( 属性名 属性类型, 属性名 属性类型, …

属性名 属性类型,

index | key [索引名](属性名 [(长度)] [asc|desc],属性名 [(长度)] [asc|desc])

);

####已经存在的表中创建多列索引 create unique index 索引名 on 表名 (属性名 [(长度)] [asc|desc],属性名 [(长度)] [asc|desc]);

####使用SQL的alter table创建多列索引 alter table table_name add index|key 索引名(属性名 [(长度)] [asc|desc],属性名 [(长度)] [asc|desc]);

####索引类型: primary key 主键索引 fulltext 全文索引 unique 唯一索引.

####删除索引(6大索引都可以删除) alter table table_name drop index 索引名. 或者 drop index index_name(索引名) on table_name;

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/100797.html原文链接：https://javaforall.cn