MySql索引研究（InnoDB,Myisam）

pigfu

已于 2025-06-18 11:42:14 修改

阅读量222

点赞数

CC 4.0 BY-SA版权

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

于 2019-04-30 10:09:30 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_38597669/article/details/89678730

mysql 专栏收录该内容

19 篇文章

订阅专栏

本文围绕MySQL索引展开，介绍了索引概念、分类，指出索引能提高查询速度但会增加存储和维护成本。还提及MySQL存储引擎对索引的支持，阐述索引设计原则，如考量多方面因素、遵循建立原则等。最后分析了索引失效的多种情况，如索引不存null值、前导模糊查询等。

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

索引概念

索引是对数据库表中一列或多列的值按某种方法进行排序的一种结构，就像是书本的目录一样，帮助你快速找到所需要的资源。索引最大的作用就是提高数据库查询的速度,不管是表的简单查询，还是多表连接，分组，排序。另外，也具有约束的作用（如唯一索引不允许有重复值）。但是在提升查询速度时也要付出一定的代价：一是增加了数据库的存储空间，二是在插入和修改数据时要花费额外时间维护索引(因为索引也要随数据的变动而变动)。
索引是一个单独的、存储在磁盘上的数据库接口（文件），它们包含着对数据表里索引记录的引用/指针。

索引分类
索引从不同角度看，分为不同的类型，一般常用的聚簇索引，非聚簇索引，主键索引，唯一索引，辅助（普通）索引，复合索引等。
见下表；
在这里插入图片描述 FIXME:聚族索引是稠密索引，非聚族索引是稀疏索引，（允许字段值为空，不存在等）
补充:前缀索引：就是对某一字段前若干位为key建立索引。

ALTER TABLE tablename ADD INDEX  indexname (fieldname(10))//假设字段类型为varchar(30);

注意组合索引与前缀索引是对建立索引技巧的一种称呼,并非索引的类型。

mysql存储引擎对索引的支持
在mysql官网可以看到下表，详情。
在这里插入图片描述
索引的设计
在一个表上建立索引时要从索引的优点，缺点，实现方式，建立索引目的等多方面考量，要充分考虑，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引或不充分、不正确的索引对性能都毫无益处，正如文章开始所言，在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
另外在一个查询语句中，一般只选择一个最优索引，即使有多个可用的索引，也不会都用上。这是因为与只使用一个索引的速度比起来，去分析两个或多个索引文件更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。
通过资料和个人测试，总结了部分建立索引的原则：

小表是否要加索引？这个需要分情况酌情处理，如果小表仅仅是简单查询自身数据，可以不加索引，当和大表做关联查询，就要小心了，若小表处于关联查询的核心路径，对小表的访问次数可能会放大很多倍，若依旧每次扫描全表，就很可怕了，此时需要在关联字段上加索引的。
对经常出现在 where,on,group by,order by 子句中的字段使用索引。
在不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在用户表的”性别”字段上只有”男”与”女”两个不同值，因此就无须建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
为较长的字符串使用前缀索引。但前缀索引有一定的弊端，mysql中无法用前缀索引做group by,order by,也无法做覆盖扫描，因此要慎重。

根据查询条件建立合适的组合索引,使用时注意其遵循的‘最左前缀原则’，对组合索引中的字段顺序做好安排，即（a,b,c）=>(a,b,b),(a,b),(a)。一般遵循下面的原则，否则考虑用单列索引。

A、正确选择复合索引中的主列字段（最左字段），一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？
单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，需要考虑二者是否重复；

索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
MySQL主要提供2种方式的索引：B-Tree索引，Hash索引；B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(Log2 N)，相当于二分查找。哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

PS:对于联合索引，建立时字段顺序最好符合ESR规则。

索引失效 参考文章
1.索引不存储null值
更准确的说，单列索引不存储null值，复合索引不存储全为null的值。索引不能存储Null，所以对这列采用is null条件时，因为索引上根本没Null值，不能利用到索引，只能全表扫描。
为什么索引列不能存Null值？

将索引列值进行建树，其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。

这样的话，null值实际上是不能参与进建索引的过程。也就是说，null值不会像其他取值一样出现在索引树的叶子节点上。

2.不适合键值较少的列（重复数据较多的列）
假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。
再加上访问索引块，一共要访问大于200个的数据块。
如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块
少一些，肯定就不会利用索引。

3.前导模糊查询不能利用索引(like ‘%XX’或者like ‘%XX%’)
假如有这样一列code的值为’AAA’,‘AAB’,‘BAA’,‘BAB’ ,如果where code like '%AB’条件，由于前面是
模糊的，所以不能利用索引的顺序，必须一个个去找，看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like 'A % '，就可以查找CODE中A开头的CODE的位置，当碰到B开头的数据时，就可以停止查找了，因为后面的数据一定不满足要求，这样就可以利用索引了，但是尽量减少like为好。

4.如果条件中有or，not in ,!=等，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

5.对于多列索引，不符合最左前缀原则就不会使用索引

7.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

8.如果mysql估计使用全表扫描要比使用索引快,则不使用索引，如2中所说。

9.不要在列上进行运算,即是用sql自带函数；例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。