MySQL索引管理 – Egon林海峰

一功能

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

二 MySQL常用的索引分类

我们可以在创建上述索引的时候，为其指定索引类型，主要有以下几类

#===========B+树索引（innodb存储引擎默认）

聚集索引：即主键索引，PRIMARY KEY
    用途：
        1、加速查找
        2、约束（不为空、不能重复）

唯一索引：UNIQUE
    用途：
        1、加速查找
        2、约束（不能重复） 

普通索引INDEX：
    用途：
        1、加速查找

联合索引： 
    PRIMARY KEY(id,name):联合主键索引 
    UNIQUE(id,name):联合唯一索引 
    INDEX(id,name):联合普通索引

#===========HASH索引(查询单条快，范围查询慢)
将数据打散再去查询
Inodb和Myisam都不支持，设置完还是Btree
memery存储引擎支持


#===========FULLTEXT：全文索引 （只可以用在MyISAM引擎）
通过关键字的匹配来进行查询，类似于like的模糊匹配
like + %在文本比较少时是合适的
但是对于大量的文本数据检索会非常的慢
全文索引在大量的数据面前能比like快得多，但是准确度很低
百度在搜索文章的时候使用的就是全文索引，但更有可能是ES

#===========RTREE：R树索引
RTREE在mysql很少使用，仅支持geometry数据类型
geometry数据类型一般填写经纬度那样的数据
支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
RTREE范围查找很强，但Btree也不弱.

#不同的存储引擎支持的索引类型也不一样
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 等索引；

各个索引的应用场景：举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

#除此之外还有全文索引，即FULLTEXT
会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

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

三创建/删除索引的语法

#方法一：创建表时
    　　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 country drop index 索引名字;

#查看索引
方法一：
mysql> desc t1;
+-----+
| Key |
+-----+
| PRI | 主键索引
| MUL | 普通索引
| UNI | 唯一键索引
+-----+

方法二：
mysql> show index from t1;

创建索引示范

#方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name) #index没有key
);


#方式二
create index ix_age on t1(age);

#方式三
alter table t1 add index ix_sex(sex);

#查看
mysql> show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  UNIQUE KEY `uni_id` (`id`),
  KEY `ix_name` (`name`),
  KEY `ix_age` (`age`),
  KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

创建索引示范

# 1）主键索引(聚集索引)
#创建主键索引
mysql> alter table student add primary key pri_id(id);
mysql> create table student(id int not null, primary key(id)); 
mysql> create table student(id int not null primary key auto_increment comment '学号');

#提示：
database可以写 schema
index可以写 key


# 2）唯一键索引
#创建唯一建索引
mysql> alter table country add unique key uni_name(name);
mysql> create table student(id int unique key comment '学号');
mysql> create unique key index index_name on table_name(id);


# 3）普通索引(辅助索引)
#普通索引的创建
mysql> alter table student add index idx_gender(gender);
CREATE INDEX index_name ON table_name (column_list);


# 4）创建前缀索引
按照该列数据的前n个字母创建索引
mysql> alter table student add index idx_name(name(4));


# 5）全文索引
#针对content做了全文索引：
CREATE TABLE t1 (
id int NOT NULL AUTO_INCREMENT,
title char(255) NOT NULL,
content text,
PRIMARY KEY (id),
FULLTEXT (content));

查找时：
select * from table where match(content) against('想查询的字符串');

推荐文章