索引用来快速地寻找那些具有特定值
![](/icons/11633de.gif)
记录
![](/icons/11633dou.gif)
所有MySQL索引都以B-树
![](/icons/11633de.gif)
形式保存
![](/icons/11633dou2.gif)
如果没有索引
![](/icons/11633dou.gif)
执行查询时MySQL必须从第
![](/icons/11633yi.gif)
个记录开始扫描整个表
![](/icons/11633de.gif)
所有记录
![](/icons/11633dou.gif)
直至找到符合要求
![](/icons/11633de.gif)
记录
![](/icons/11633dou2.gif)
表里面
![](/icons/11633de.gif)
记录数量越多
![](/icons/11633dou.gif)
这个操作
![](/icons/11633de.gif)
代价就越高
![](/icons/11633dou2.gif)
如果作为搜索条件
![](/icons/11633de.gif)
列上已经创建了索引
![](/icons/11633dou.gif)
MySQL无需扫描任何记录即可迅速得到目标记录所在
![](/icons/11633de.gif)
位置
![](/icons/11633dou2.gif)
如果表有1000个记录
![](/icons/11633dou.gif)
通过索引查找记录至少要比顺序扫描记录快100倍
假设我们创建了
![](/icons/11633yi.gif)
个名为people
![](/icons/11633de.gif)
表:
CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );
然后
![](/icons/11633dou.gif)
我们完全随机把1000个区别name值插入到people表
![](/icons/11633dou2.gif)
下图显示了people表所在数据文件
![](/icons/11633de.gif)
![](/icons/11633yi.gif)
小部分:
可以看到
![](/icons/11633dou.gif)
在数据文件中name列没有任何明确
![](/icons/11633de.gif)
次序
![](/icons/11633dou2.gif)
如果我们创建了name列
![](/icons/11633de.gif)
索引
![](/icons/11633dou.gif)
MySQL将在索引中排序name列:
对于索引中
![](/icons/11633de.gif)
每
![](/icons/11633yi.gif)
项
![](/icons/11633dou.gif)
MySQL在内部为它保存
![](/icons/11633yi.gif)
个数据文件中实际记录所在位置
![](/icons/11633de.gif)
“指针”
![](/icons/11633dou2.gif)
因此
![](/icons/11633dou.gif)
如果我们要查找name等于“Mike”记录
![](/icons/11633de.gif)
peopleid(SQL命令为“SELECT peopleid FROM people WHERE name='Mike';”)
![](/icons/11633dou.gif)
MySQL能够在name
![](/icons/11633de.gif)
索引中查找“Mike”值
![](/icons/11633dou.gif)
然后直接转到数据文件中相应
![](/icons/11633de.gif)
行
![](/icons/11633dou.gif)
准确地返回该行
![](/icons/11633de.gif)
peopleid(999)
![](/icons/11633dou2.gif)
在这个过程中
![](/icons/11633dou.gif)
MySQL只需处理
![](/icons/11633yi.gif)
个行就可以返回结果
![](/icons/11633dou2.gif)
如果没有“name”列
![](/icons/11633de.gif)
索引
![](/icons/11633dou.gif)
MySQL要扫描数据文件中
![](/icons/11633de.gif)
所有记录
![](/icons/11633dou.gif)
即1000个记录!显然
![](/icons/11633dou.gif)
需要MySQL处理
![](/icons/11633de.gif)
记录数量越少
![](/icons/11633dou.gif)
则它完成任务
![](/icons/11633de.gif)
速度就越快
索引
![](/icons/11633de.gif)
类型
MySQL提供多种索引类型供选择:
普通索引 这是最基本
![](/icons/11633de.gif)
索引类型
![](/icons/11633dou.gif)
而且它没有唯
![](/icons/11633yi.gif)
性的类
![](/icons/11633de.gif)
限制
![](/icons/11633dou2.gif)
普通索引可以通过以下几种方式创建:
创建索引
![](/icons/11633dou.gif)
例如CREATE INDEX <索引
![](/icons/11633de.gif)
名字> _disibledevent=>
首先
![](/icons/11633dou.gif)
我们可以考虑在单个列上创建索引
![](/icons/11633dou.gif)
比如firstname、lastname或者age列
![](/icons/11633dou2.gif)
如果我们创建firstname列
![](/icons/11633de.gif)
索引(ALTER TABLE people ADD INDEX firstname (firstname);)
![](/icons/11633dou.gif)
MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'
![](/icons/11633de.gif)
记录
![](/icons/11633dou.gif)
然后再在这个“中间结果集”上进行其他条件
![](/icons/11633de.gif)
搜索:它首先排除那些lastname不等于“Sullivan”
![](/icons/11633de.gif)
记录
![](/icons/11633dou.gif)
然后排除那些age不等于17
![](/icons/11633de.gif)
记录
![](/icons/11633dou2.gif)
当记录满足所有搜索条件的后
![](/icons/11633dou.gif)
MySQL就返回最终
![](/icons/11633de.gif)
搜索结果
由于建立了firstname列
![](/icons/11633de.gif)
索引
![](/icons/11633dou.gif)
和执行表
![](/icons/11633de.gif)
完全扫描相比
![](/icons/11633dou.gif)
MySQL
![](/icons/11633de.gif)
效率提高了很多
![](/icons/11633dou.gif)
但我们要求MySQL扫描
![](/icons/11633de.gif)
记录数量仍旧远远超过了实际所需要
![](/icons/11633de.gif)
![](/icons/11633dou2.gif)
虽然我们可以删除firstname列上
![](/icons/11633de.gif)
索引
![](/icons/11633dou.gif)
再创建lastname或者age列
![](/icons/11633de.gif)
索引
![](/icons/11633dou.gif)
但总地看来
![](/icons/11633dou.gif)
不论在哪个列上创建索引搜索效率仍旧相似
为了提高搜索效率
![](/icons/11633dou.gif)
我们需要考虑运用多列索引
![](/icons/11633dou2.gif)
如果为firstname、lastname和age这 3个列创建
![](/icons/11633yi.gif)
个多列索引
![](/icons/11633dou.gif)
MySQL只需
![](/icons/11633yi.gif)
次检索就能够找出正确
![](/icons/11633de.gif)
结果!下面是创建这个多列索引
![](/icons/11633de.gif)
SQL命令:
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);
由于索引文件以B-树格式保存
![](/icons/11633dou.gif)
MySQL能够立即转到合适
![](/icons/11633de.gif)
firstname
![](/icons/11633dou.gif)
然后再转到合适
![](/icons/11633de.gif)
lastname
![](/icons/11633dou.gif)
最后转到合适
![](/icons/11633de.gif)
age
![](/icons/11633dou2.gif)
在没有扫描数据文件任何
![](/icons/11633yi.gif)
个记录
![](/icons/11633de.gif)
情况下
![](/icons/11633dou.gif)
MySQL就正确地找出了搜索
![](/icons/11633de.gif)
目标记录!
那么
![](/icons/11633dou.gif)
如果在firstname、lastname、age这 3个列上分别创建单列索引
![](/icons/11633dou.gif)
效果是否和创建
![](/icons/11633yi.gif)
个firstname、lastname、age
![](/icons/11633de.gif)
多列索引
![](/icons/11633yi.gif)
样呢?答案是否定
![](/icons/11633de.gif)
![](/icons/11633dou.gif)
两者完全区别
![](/icons/11633dou2.gif)
当我们执行查询
![](/icons/11633de.gif)
时候
![](/icons/11633dou.gif)
MySQL只能使用
![](/icons/11633yi.gif)
个索引
![](/icons/11633dou2.gif)
如果你有 3个单列
![](/icons/11633de.gif)
索引
![](/icons/11633dou.gif)
MySQL会试图选择
![](/icons/11633yi.gif)
个限制最严格
![](/icons/11633de.gif)
索引
![](/icons/11633dou2.gif)
但是
![](/icons/11633dou.gif)
即使是限制最严格
![](/icons/11633de.gif)
单列索引
![](/icons/11633dou.gif)
它
![](/icons/11633de.gif)
限制能力也肯定远远低于firstname、lastname、age这 3个列上
![](/icons/11633de.gif)
多列索引
最左前缀
多列索引还有另外
![](/icons/11633yi.gif)
个优点
![](/icons/11633dou.gif)
它通过称为最左前缀(Leftmost Prefixing)
![](/icons/11633de.gif)
概念体现出来
![](/icons/11633dou2.gif)
继续考虑前面
![](/icons/11633de.gif)
例子
![](/icons/11633dou.gif)
现在我们有
![](/icons/11633yi.gif)
个firstname、lastname、age列上
![](/icons/11633de.gif)
多列索引
![](/icons/11633dou.gif)
我们称这个索引为fname_lname_age
![](/icons/11633dou2.gif)
当搜索条件是以下各种列
![](/icons/11633de.gif)
组合时
![](/icons/11633dou.gif)
MySQL将使用fname_lname_age索引:
firstname
![](/icons/11633dou.gif)
lastname
![](/icons/11633dou.gif)
age
firstname
![](/icons/11633dou.gif)
lastname
firstname
从另
![](/icons/11633yi.gif)
方面理解
![](/icons/11633dou.gif)
它相当于我们创建了(firstname
![](/icons/11633dou.gif)
lastname
![](/icons/11633dou.gif)
age)、(firstname
![](/icons/11633dou.gif)
lastname)以及(firstname)这些列组合上
![](/icons/11633de.gif)
索引
![](/icons/11633dou2.gif)
下面这些查询都能够使用这个fname_lname_age索引:
SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND
age='17'; SELECT peopleid FROM people WHERE firstname='Mike' AND
lastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The
following queries cannot use the index at all: SELECT peopleid FROM people WHERE
lastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECT peopleid
FROM people WHERE lastname='Sullivan' AND age='17';
选择索引列 在性能优化过程中
![](/icons/11633dou.gif)
选择在哪些列上创建索引是最重要
![](/icons/11633de.gif)
步骤的
![](/icons/11633yi.gif)
![](/icons/11633dou2.gif)
可以考虑使用索引
![](/icons/11633de.gif)
主要有两种类型
![](/icons/11633de.gif)
列:在WHERE子句中出现
![](/icons/11633de.gif)
列
![](/icons/11633dou.gif)
在join子句中出现
![](/icons/11633de.gif)
列
![](/icons/11633dou2.gif)
请看下面这个查询:
SELECT age ## 不使用索引 FROM people WHERE firstname='Mike' ## 考虑使用索引 AND
lastname='Sullivan' ## 考虑使用索引
这个查询和前面
![](/icons/11633de.gif)
查询略有区别
![](/icons/11633dou.gif)
但仍属于简单查询
![](/icons/11633dou2.gif)
由于age是在SELECT部分被引用
![](/icons/11633dou.gif)
MySQL不会用它来限制列选择操作
![](/icons/11633dou2.gif)
因此
![](/icons/11633dou.gif)
对于这个查询来说
![](/icons/11633dou.gif)
创建age列
![](/icons/11633de.gif)
索引没有什么必要
![](/icons/11633dou2.gif)
下面是
![](/icons/11633yi.gif)
个更复杂
![](/icons/11633de.gif)
例子:
SELECT people.age, ##不使用索引 town.name ##不使用索引 FROM people LEFT JOIN town _disibledevent=> lastname='Sullivan' ##考虑使用索引
和前面
![](/icons/11633de.gif)
例子
![](/icons/11633yi.gif)
样
![](/icons/11633dou.gif)
由于firstname和lastname出现在WHERE子句中
![](/icons/11633dou.gif)
因此这两个列仍旧有创建索引
![](/icons/11633de.gif)
必要
![](/icons/11633dou2.gif)
除此的外
![](/icons/11633dou.gif)
由于town表
![](/icons/11633de.gif)
townid列出现在join子句中
![](/icons/11633dou.gif)
因此我们需要考虑创建该列
![](/icons/11633de.gif)
索引
那么
![](/icons/11633dou.gif)
我们是否可以简单地认为应该索引WHERE子句和join子句中出现
![](/icons/11633de.gif)
每
![](/icons/11633yi.gif)
个列呢?差不多如此
![](/icons/11633dou.gif)
但并不完全
![](/icons/11633dou2.gif)
我们还必须考虑到对列进行比较
![](/icons/11633de.gif)
操作符类型
![](/icons/11633dou2.gif)
MySQL只有对以下操作符才使用索引:<
![](/icons/11633dou.gif)
<=
![](/icons/11633dou.gif)
=
![](/icons/11633dou.gif)
>
![](/icons/11633dou.gif)
>=
![](/icons/11633dou.gif)
BETWEEN
![](/icons/11633dou.gif)
IN
![](/icons/11633dou.gif)
以及某些时候
![](/icons/11633de.gif)
LIKE
![](/icons/11633dou2.gif)
可以在LIKE操作中使用索引
![](/icons/11633de.gif)
情形是指另
![](/icons/11633yi.gif)
个操作数不是以通配符(%或者_)开头
![](/icons/11633de.gif)
情形
![](/icons/11633dou2.gif)
例如
![](/icons/11633dou.gif)
“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”这个查询将使用索引
![](/icons/11633dou.gif)
但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引
分析索引效率 现在我们已经知道了
![](/icons/11633yi.gif)
些如何选择索引列
![](/icons/11633de.gif)
知识
![](/icons/11633dou.gif)
但还无法判断哪
![](/icons/11633yi.gif)
个最有效
![](/icons/11633dou2.gif)
M