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;