MySQL的索引分段_MySQL索引详解

⼀ 、初识索引

为什么要有索引?

⼀般的应⽤系统，读写比例在10:1左右，⽽且插入操作和⼀般的更新操作很少出现性能问题，在⽣产环境中， 我们遇到最多的，也是最容易出问题的，还是⼀些复杂的查询操作，因此对查询语句的优化显然是重中之重。 说起加速查询，就不得不提到索引了。

什么是索引？

索引在MySQL中也叫是⼀种“键”，是存储引擎⽤于快速找到记录的⼀种数据结构。索引对于良好的性能 非常关 键，尤其是当表中的数据量越来越⼤时，索引对于性能的影响愈发重要。 索引优化应该是对查询性能优化最有

效的⼿段了。索引能够轻易将查询性能提⾼好⼏个数量级。 索引相当于字典的⾳序表，如果要查某个字，如果 不使⽤⾳序表，则需要从⼏百⻚中逐⻚去查。

⼆、索引的原理

索引的⽬的在于提⾼查询效率，与我们查阅图书所⽤的⽬录是⼀个道理：先定位到章，然后定位到该章下的⼀个⼩节，然后找到⻚数。相似的例⼦还有：查字典，查火⻋⻋次，⻜机航班等本质都是：通过不断地

缩⼩想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是⽤同⼀种查找⽅式来锁定数据。数据库也是⼀样，但显然要复杂的多，因为

不仅⾯临着等值查询，还有范围查询(>、

呢？最简单的如果1000条数据，1到100分成第⼀段，101到200分成第⼆段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，⼀下⼦去除了90%的⽆效数据。但如果是1千万的记录呢，

分成⼏段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这⾥我们忽略了⼀个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。⽽数据库实现比较复杂，

⼀⽅⾯数据是保存在磁盘上的，另外⼀⽅⾯为了提⾼性能，每次⼜可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本⼤概是访问内存的⼗万倍左右，所以简单的搜索树难以满⾜复杂的应⽤场景。

三、索引管理

⼀ 功能

#1. 索引的功能就是加速查找

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

⼆ MySQL常⽤的索引

普通索引

INDEX：加速查找

唯⼀索引：

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

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

联合索引：

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

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

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

三 索引的两⼤类型hash与btree

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

这个系统有⼀个会员表

有下列字段：

会员编号 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 表名字;