Mysql 索引

最新推荐文章于 2025-03-21 18:18:40 发布

小李飞刀8

最新推荐文章于 2025-03-21 18:18:40 发布

阅读量3.7k

点赞数 1

分类专栏： mysql 文章标签： mysql 索引

本文链接：https://blog.csdn.net/brushli/article/details/39677387

版权

mysql 专栏收录该内容

16 篇文章

订阅专栏

本文详细介绍了Mysql索引的两大分类，包括B+Tree、R-Tree和Hash索引的结构特点，以及按使用方式分类的普通索引、主键索引、外键索引、唯一索引、全文索引、前缀索引和组合索引。此外，还讨论了索引的限制，如LIKE查询、类型转换、列运算、OR条件、表连接、子查询和排序对索引的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引通常是提高查询效率的最好方式，使用索引经常可以使查询效率提高几十倍、几百倍，甚至更多。

本人将Mysql索引归为两大类，按索引组织结构分类和按索引使用方式分类。

1、按索引组织结构分类

1.1 B+Tree索引

以下内容引用自：http://blog.csdn.net/tonyxf121/article/details/8393545

1.1.1 B-Tree

B-Tree介绍

B-Tree是一种多路搜索树（并不是二叉的）：

(1) 定义任意非叶子结点最多只有M个儿子,且M>2

(2) 根结点的儿子数为[2, M]

(3) 除根结点以外的非叶子结点的儿子数为[M/2, M]

(4) 每个结点存放至少M/2-1（取上整）和至多M-1个关键字,（至少2个关键字）

(5) 非叶子结点的关键字个数=指向儿子的指针个数-1

(6) 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]

(7) 非叶子结点的指针：P[1], P[2], …, P[M], 其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树

(8) 所有叶子结点位于同一层

如：（M=3）

B-树的特性：

(1) 关键字集合分布在整颗树中

(2) 任何一个关键字出现且只出现在一个结点中

(3) 搜索有可能在非叶子结点结束

(4) 其搜索性能等价于在关键字全集内做一次二分查找

(5) 自动层次控制

树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；

重复，直到所对应的儿子指针为空，或已经是叶子结点；

1.1.2 B+Tree

B+Tree介绍

B+树是B-树的变体，也是一种多路搜索树：

(1) 其定义基本与B-树同，除了：

(2) 非叶子结点的子树指针与关键字个数相同；

(3) 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；

(5) 为所有叶子结点增加一个链指针；

(6) 所有关键字都在叶子结点出现；

如：（M=3）

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找.
B+的特性：

(1) 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；

(2) 不可能在非叶子结点命中；

(3) 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

(4) 更适合文件索引系统；

mysql中普遍使用B+Tree做索引，但在实现上又根据聚簇索引和非聚簇索引而不同。

(1) 聚簇索引

所谓聚簇索引，就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

(2) 非聚簇索

非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中，如下图：

1.2 R-Tree索引

R-Tree树的介绍来自百度百科：http://baike.baidu.com/view/5086047.htm?fr=aladdin

R树是B树向多维空间发展的另一种形式，它将对象空间按范围划分，每个结点都对应一个区域和一个磁盘页，

非叶结点的磁盘页中存储其所有子结点的区域范围，非叶结点的所有子结点的区域都落在它的区域范围之内；

叶结点的磁盘页中存储其区域范围之内的所有空间对象的外接矩形。R树是一种动态索引结构。

符号说明：M：结点中单元的最大数目，m(1<= m <= M/2)为非根结点中单元个数的下限。

一个R树满足如下性质:

(1) 每一个叶子结点中包含的单元的个数介于m和M之间，除非他同样是根结点

(2) 每一个叶子结点中的单元(I, tuple-identifier),I为包含所有子结点的最小包含矩形(MBR)

(3) 每一个非叶子结点的子结点数介于m和M之间，除非他是根结点

(4) 每一个非叶子结点单元(I, child -pointer)I是包含子结点的MBR.Pointer是指向子结点的指针。通过该指针可以访问到叶子结点。

(5) 根结点至少有两个子结点，除非他同时是叶子结点

(6) 所有的叶子结点都处在树的同一层上。

1.3 Hash索引

键值生成哈希值，然后映射到某一存储单元，这样查找的时间复杂度是O(1)，哈希索引的查询速度非常快，最多一次IO，比B-TREE快多了，

B-TREE执行IO的平均次数是树的高度；但哈希索引也有一些局限，只能判断=或！=，或者IN操作，不能进行<，>操作，也不能对order by进行优化。

哈希索引是Memory存储引擎默认的索引，当然Memory也支持B-TREE索引。

2、按索引使用方式分类

2.1 普通索引（index）

下面在表test 的字段name是建立普通索引name_index

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)

2.2 主键索引（primary key）

在字段id上建立主键索引，主键不能为空，也不能重复。

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
)

2.3 外键索引（foreign index）

为表test创建外键student_id的时候，Mysql就会自动生成外键索引，如果是表之间的连接，最好使用外键，这样就可以利用索引，加快查询速度。

CREATE TABLE `test`(  
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `student_id` BIGINT,
  PRIMARY KEY (`id`),
  CONSTRAINT `foreign_student_id` FOREIGN KEY (`student_id`) REFERENCES `test`.`student`(`student_id`)
)

2.4 唯一索引（unique index）

唯一索引确保索引列的值只能是唯一的，可以为空。

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique_index` (`name`)
)

2.5 全文索引（fulltext index）

为了解决like ‘%test%’这类查询低效的问题，mysql引入全文索引，但对中文的支持不是很好。

Mysql5.5及以下版本只在MyISAM中实现全文索引，而且只支持char、varchar、text这种数据类型。

Mysql5.6及之后的版本也能在InnoDB使用全文索引。

如果是JAVA应用，不使用Mysql的全文索引，也可以使用Lucene+Solr来解决。

2.6 前缀索引（prefix index）

选择某列的前N个字符生成索引，使得索引的长度更短，索引文件更小。在对blob或者text数据类型的列建索引时，通常会建立前缀索引。

InnoDB的前缀索引的最大长度是767字节。

CREATE TABLE test(
       id BIGINT PRIMARY KEY ,
       blob_col BLOB, 
       INDEX(blob_col(10))
)

2.7 组合索引

前面提到的索引大多是单列索引，在有些情况下组合索引（多列索引的查询效率更高。

下面对列name,age,birth使用组合索引：

CREATE TABLE `test ` (
  `id` bigint(20) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_age_birth_index` (`name`,`age`,`birth`)
)

3. 索引的限制

3.1 like限制

在like 查询条件下，通配符不能在最左边，否则索引失效。

比如下面的name字段上的索引是有效的

where name like ‘name%’  

where name like ‘name%me’

下面name字段上的索引是失效的

where name like ‘%name’

3.2 类型转换限制

列name是varchar，并且带有索引

以下name字段上的索引有效

SELECT * FROM student WHERE name = ‘11’;

以下name字段上的索引失效: 右边的值是整数11，需要进行类型转换，将整数型的11转换成字符型的'11'，导致name字段上的索引失效

SELECT * FROM student WHERE name_index = 11;

3.3 列运算

以下列name的索引有效

SELECT * FROM student WHERE name = 'li1324312';

以下列name的索引失效

SELECT * FROM student WHERE CONCAT(name,'44') = 'li1324312';

3.4 查询条件OR

如果索引列与非索引列同时作为查询条件，中间隔个or，则索引失效.

以下查询语句，其中name字段无索引，name_index字段有索引，中间是or，这样导致name_index上的索引失效。

SELECT * FROM student WHERE name_index = 'li47382' OR name = 'li';

EXPLAIN SELECT * FROM student WHERE name_index = 'li47382' OR name = 'li';

从下面的执行计划中可以看出，进行全表扫描，并没有使用字段name_index上的索引。

如果OR两边的查询列都有索引,则两个索引都有效,会做索引合并.

SELECT * FROM student WHERE name_index = 'li47382' OR sex_index = 'li';

EXPLAIN SELECT * FROM student WHERE name_index = 'li47382' OR sex_index = 'li';

如果有多个OR查询条件，则所有OR两边的查询列都要有索引，所有的索引才会有效；只要有一个OR旁边的列无索引，则所有的索引都会失效。

3.5 组合索引

在name、sex、age这三个列上建了个组合索引，组合索引对前缀列有效，对非前缀列无效，比如对于查询条件

where name=’li’

或

where name=’li’ and sex=’male’

或

where name=’li’ and sex=’male’ and age=11

是有效的，但对

where sex=’male’

或

where sex=’male’ and age = 11

或

where age=11

是无效的。

注意,，如果组合索引的列只是打乱了顺序，比如

where sex=’male’ and age = 11 and name=’li’

由于MYSQL查询优化器会做一些SQL优化，最终name排在前面，所以这个条件仍然会使用索引.

3.6 表连接

表与表之间的连接最发使用外键连接，或者使用有索引的字段连接，这样查询的效率快很多。

下面两个表student，user_student，他们通过外键student_id关联。

CREATE TABLE `student` (
  `student_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
) ENGINE=InnoDB

CREATE TABLE `user_student` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `student_id` bigint(20) DEFAULT NULL,
  `student_id2` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `fk_student_id` (`student_id`),
  CONSTRAINT `fk_student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB

利用外键student_id关联

EXPLAIN SELECT s.* FROM student s, user_student us WHERE s.`student_id` = us.`student_id`;

结果是：

如果使用字段student_id2，即非外键也没加索引。

EXPLAIN SELECT s.* FROM student s, user_student us WHERE s.`student_id` = us.`student_id2`;

结果会进行全表扫描。

3.7 子查询

看下面两条SQL语句的查询效率

SELECT * FROM student WHERE student_id IN(
 SELECT student_id FROM student WHERE NAME_index = 'li233'
);

SELECT s1.* FROM student s1, student s2 WHERE s1.student_id = s2.student_id AND s2.NAME_index = 'li233';

返回了 26 行

执行耗时   : 6.099 sec
传送时间   : 0 sec
总耗时      : 6 sec
--------------------------------------------------

返回了 26 行

执行耗时   : 0.016 sec
传送时间   : 0 sec
总耗时      : 0.016 sec

上面两条SQL语句的查询时间,可以查询时间相差巨大,后面实行表连接的查询速度比前面SQL语句快380倍!

前者会创建临时表来存放子查询中的数据，因而会比较慢。

查看两条SQL的执行计划

EXPLAIN SELECT * FROM student WHERE student_id IN(
  SELECT student_id FROM student WHERE NAME_index = 'li23'
);

可以看出索引失效，执行全表扫描

EXPLAIN SELECT s1.* FROM student s1, student s2 WHERE s1.student_id = s2.student_id AND s2.NAME_index = 'li23';

可以看出，使用了索引。

3.8 索引排序

表的结构如下：

CREATE TABLE `student` (
  `student_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `sex` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth_day` date DEFAULT NULL,
  `name_index` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `sex_index` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `age_index` int(11) DEFAULT NULL,
  `name2` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `sex2` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `age2` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `name_index` (`name_index`(10)),
  KEY `sex_index` (`sex_index`),
  KEY `age_index` (`age_index`),
  KEY `name2_sex2_age2` (`name2`,`sex2`,`age2`)
)

3.8.1 排序使用索引的情况

不能使用单列索引，一定要使用组合索引，比如上面的组合索引name2_sex_age2，并且查询条件中要有索引的前缀，并且排序条件一致，并且排序的索引列要一致，以下的排序可以使用索引。

EXPLAIN SELECT * FROM student  WHERE name2 = 'li' ORDER BY sex2;

EXPLAIN SELECT * FROM student  WHERE name2 = 'li' and name = 'li' ORDER BY sex2;

EXPLAIN SELECT * FROM student  WHERE name2 = 'li' ORDER BY sex2, age2;

EXPLAIN SELECT * FROM student  WHERE name2 = 'li' ORDER BY sex2 DESC, age2 DESC;

3.8.2 排序不能使用索引的情况

（1）使用单列索引

SELECT * FROM student  WHERE name_index = 'li' ORDER BY name_index;

（2）使用组合索引，但索引前缀没作为查询条件

SELECT * FROM student ORDER BY name2;

（3）使用组合索引及其索引前缀，但同时也使用了其他索引

SELECT * FROM student WHERE name2='li' AND name_index='li' ORDER BY sex2;

（4）使用组合索引及其前缀作为查询条件，但排序列与查询列不是连续的

SELECT * FROM student WHERE name2='li' ORDER BY age2;

（5）使用使用组合索引及其前缀作为查询条件，但排序列的顺序不连续

SELECT * FROM student WHERE name2='li' ORDER BY age2, sex2;

（6）使用使用组合索引及其前缀作为查询条件，但排序顺序不一致，要全为DESC，或全为ASC

SELECT * FROM student WHERE name2='li' ORDER BY sex2 ASC, age2 DESC;

（7）使用使用组合索引及其前缀作为查询条件，但排序中同时使用其他非本组合索引的列

SELECT * FROM student WHERE name2='li' ORDER BY sex2, name;

可以在查询条件前面加上命令explain，如果出现using filesort，则说明排序没有使用索引;否则排序有使用索引。

3.9 查询条件优先级

如果在查询条件中同时含有id>XXX和create_time>XXX，那么MYSQL引擎会优先使用id索引，即使id索引扫描到所有数据！