mysql管理索引_mysql 索引管理

功能

索引的功能就是加速查找 mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

mysql常用的索引

普通索引INDEX：加速查找

唯一索引：

-主键索引PRIMARY KEY：加速查找+约束(不为空、不能重复)

-唯一索引UNIQUE:加速查找+约束(不能重复)

联合索引：

-PRIMARY KEY(id,name):联合主键索引

-UNIQUE(id,name):联合唯一索引

-INDEX(id,name):联合普通索引

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表

有下列字段：

会员编号 INT

会员姓名 VARCHAR(10)

会员身份证号码 VARCHAR(18)

会员电话 VARCHAR(10)

会员住址 VARCHAR(50)

会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY

会员姓名 如果要建索引的话，那么就是普通的 INDEX

会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE (唯一的，不允许重复)

#除此之外还有全文索引，即FULLTEXT

会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。

用于搜索很长一篇文章的时候，效果最好。

用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

但其实对于全文搜索，我们并不会使用MySQL自带的该索引，

而是会选择第三方软件如Sphinx，专门来做全文搜索。

#其他的如空间索引SPATIAL，了解即可，几乎不用

各个索引的应用场景

索引的两大类型

#我们可以在创建上述索引的时候，为其指定索引类型，分两类

hash类型的索引：查询单条快，范围查询慢

btree类型的索引：b+树，层数越多，数据量指数级增长(我们就用它，因为innodb默认支持它)

#不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

创建删除索引的语法

#方法一：创建表时

CREATE TABLE 表名 (

字段名1 数据类型 [完整性约束条件…],

字段名2 数据类型 [完整性约束条件…],

[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY

[索引名] (字段名[(长度)] [ASC |DESC])

);

#方法二：CREATE在已存在的表上创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名

ON 表名 (字段名[(长度)] [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX

索引名 (字段名[(长度)] [ASC |DESC]) ;

#删除索引：DROP INDEX 索引名 ON 表名字;

复制代码

#创建索引

alter table test add index index_name(name);

#创建索引

create index index_name on test(name);

#查看索引

desc table;

#查看索引

show index from table;

#删除索引

alter table test drop key index_name;

#添加主键索引(略)

#添加唯一性索引

alter table student add unique key uni_xxx(xxx);

#查看表中数据行数

select count(*) from city;

#查看去重数据行数

select count(distinct name) from city;

# 前缀索引

根据字段的前N个字符建立索引

alter table test add index idx_name(name(10));

联合索引

多个字段建立一个索引

例：

where a.女生 and b.身高 and c.体重 and d.身材好

index(a,b,c)

特点：前缀生效特性

a,ab,ac,abc,abcd 可以走索引或部分走索引

b bc bcd cd c d ba ... 不走索引

原则：把最常用来做为条件查询的列放在最前面

#创建people表

create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f'));

#创建联合索引

alter table people add index idx_gam(gender,age,money);