浅谈索引，MySql索引，索引策略，索引类型，索引维护

最新推荐文章于 2026-03-04 03:59:05 发布

原创最新推荐文章于 2026-03-04 03:59:05 发布 · 置顶 · 466 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Mysql

本文深入解析MySQL中的索引原理，包括B-Tree、哈希、全文索引等类型，探讨索引策略如独立列、前缀、多列、聚簇及覆盖索引，以及索引优化和维护方法。

索引在MySql中也叫做键(key)，是储存引擎用于快速查找记录的一种数据结构。
索引是在存储引擎层实现的，所以不同储存引擎的索引工作方式不一样。
索引减少了服务器需要扫描的数据量

1. 索引类型

1.1 B-Tree索引

B-Tree索引简介：
      它是使用B-Tree数据结构来存储数据的。除开特殊需求，一般使用B-Tree索引。
      不同的引擎以不同的方式使用B-Tree索引，性能也各有不同，MyISAM搜索引擎中，使用前缀压缩技术使索引更小，InnoDB按照原数据格式进行存储。
      B-Tree通常意味着所有的值都是按顺序存储的，并且每个子页到根的距离相同。B-Tree索引能加快访问数据的速度，因为存储引擎不需要再全表扫描，而是直接从索引的根节点开始搜索。
      B-Tree索引树图
B-Tree索引使用范围：
      全值匹配：where name = ‘xxx’
      范围值匹配：where age<20 and age>10
      列前缀匹配：where name like ‘xx%’ 非列前缀模糊匹配是不支持的，例 like '%xx%'不支持索引
      精确匹配一列并范围匹配另外一列：where age = 10 and name like ‘xx%’ (联合索引)
      如果order by的条件满足上面几中查询类型，则索引也可以满足排序需求
1.2 哈希索引

哈希索引简介：
      哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
      只有Memory引擎显式支持哈希索引。InnoDB不显式的支持哈希索引(不能人为创建哈希索引列)，InnoDB会根据表使用情况自动生成哈希索引(隐式支持)。

InnoDB引擎的哈希索引：自适应哈希索引 —— InnoDB注意到某些索引值被使用得非常频繁时，会在基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速查找，这个行为用户无法控制

哈希索引使用范围：
      哈希索引值支持等值比较查询 =，in()，<=>。因为哈希索引根据哈希码匹对。
      哈希索引不按照索引顺序存储，也无法用于排序。
1.3 全文索引

      FULLTEXT 全文索引是一种特殊的索引，他查找的是文本中的关键词，而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情。
      在相同的列上同时创建全文索引，和基于值的B-Tree索引不会又冲突，可以同时存在。全文索引使用MATCH(column) AGAINST(parm)操作，不是普通的where操作。
      若是数据量大的表，需要模糊匹配，可以考虑使用全文索引

SELECT * FROM ms_fans WHERE nick_name LIKE '秋天%'; -- 123  0.003

SELECT * FROM ms_fans WHERE nick_name LIKE '%秋天%'; -- 201 2.907

SELECT * FROM ms_fans WHERE MATCH(nick_name)AGAINST('秋天 打烊 王井控');

1.4 其他索引
UNIQUE:唯一索引，毋庸置疑当前索引列的值不能重复。
APATIAL:空间索引
NORMAL:一般索引

2. 索引策略

2.1 独立的列（如何使用索引）

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。在查询时如果查询中的列不是独立的，则Mysql无法调启索引。
不能调启索引示例：

 SELECT * FROM TABLE WHERE COLUMN + 1 = 5;

 SELECT * FROM TABLE WHERE FROM_UNIXTIME(COLUMN,'%Y-%m-%d') = '2020-01-01'

2.2 前缀索引

      前缀索引是指去字段列值的前N位创建索引，N值为多少视情况定。
      对于很长的TEXT,BLOB,VARCHAR类型列，必须使用前缀索引，因为Mysql不允许这些列完整长度建立索引。
      前缀索引能使索引更小，更快。但是无法使用前缀索引做order by 和group by，也无法使用前缀索引做覆盖扫描
      创建前缀索引：ALTER TABLE table_name ADD KEY(COLUMN(N));

2.3 多列索引（联合索引）

多字段列合建一个索引即多列索引。在一个B-Tree多列索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。
多列索引(A,B,C)在创建后，等于索引(A,B,C) + 索引(A)，因此在创建多列索引后，多列索引最左列（第一列），无需再重复创建索引。

如何选择索引列的顺序（经验法则）：
1、将选择性最高的列放到索引最前列。
2、将匹配精确度最高的列放到索引最前列。
经验法则在大多数情况下固然有效，但是应用到场景还是需要具体分析，特别是排序，分组和范围条件等因素对查询造成的影响。

2.4 聚簇索引

聚簇索引并不是一种单独的索引，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引，和数据行。即数据行和相邻的键值紧凑的存储在一起。因为无法同时吧数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
Mysql引擎目前不支持选择任一索引作为聚簇索引，InnoDB通过主键聚集数据，没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB则会隐式的创建一个主键作为聚簇索引。
InnoDB引擎默认主键为聚簇索引，无主键则选择非空索引代替，无此索引则会隐式创建主键。Mysql引擎目前不支持人工选择索引作为聚簇索引，这与InnoDB是否支持哈希索引类似。都是被动的，用户无法主动选择
2.5 覆盖索引

一个索引包含所有需要查询的字段的值，我们称之为覆盖索引。即查询时只需要扫描索引而不需要回表（InnoDB B-Tree索引按原数据存储）。
2.6 冗余，重复，未使用索引（不盲目创建索引）

       Mysql的唯一限制，和主键限制都是通过索引实现的，因此在指定主键后，无需对主键列进行索引创建，对主键列创建的索引是重复的索引。除非是有不同查询需求。
       如果创建了索引(A,B)，再创建索引(A)就是冗余索引。因为这是前一个索引的前缀索引。所以(A,B)也可以当做(A)使用。
      增加新索引将会导致INSERT,UPDATE,DELETE等操作速度变慢，特别是新增索引后导致达到了内存瓶颈的时候。
      未使用，冗余，重复的索引可以删除。有需要再根据需要添加。

3. 索引优化

索引设计时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引会降低其他另一些查询效率，那么应该考虑优化原来的查询。应当同时优化查询和索引找到最佳平衡。
避免多个范围条件查询，在多列索引中，尽量将范围查询的索引列放在末尾。因为Mysql无法使用范围列后面的其他索引列了，但是对于多个等值查询没有这个限制。

4.维护索引和表

       行碎片：数据行被存储为多个地方的多个片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降
       行间碎片：逻辑上顺序的也，或者行在磁盘上不是顺序存储的。行间碎片对全表扫描，和聚簇索引扫描有很大影响，因为这些操作都是能从磁盘上顺序的存储数据中获益。
      剩余空间碎片：数据页中有大量的空余空间，这会导致服务器读取大量不需要的数据，造成浪费。

-- 查看表，表的索引的错误
CHECK TABLE table_name;
-- 修复损坏的表
REPAIR TABLE table_name;
-- 更改表的存储引擎
ALTER TABLE table_name ENGINE = INNODB;
-- 重新生成对索引的统计信息
ANALYZE TABLE table_name;
-- 查看库表的索引基数
SHOW INDEX FROM table_name
-- 清除空间碎片
OPTIMIZE TABLE table_name;

如果一个查询无法从所有可能的索引中获益，则应该看看是否可以创建一个更适合的索引来提升性能，如果不行，也可以看看是否可以重写该查询，将其转化为一个能够高效利用现有索引或者新创建索引的查询。

标签

#mysql