Mysql 的索引数量是否越多越好? 为什么?

什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。

索引一般存储在磁盘的文件中，它是占用物理空间的。

索引的优缺点？

优点：

• 加快数据查找的速度

• 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度

• 加快表与表之间的连接

缺点：

• 建立索引需要占用物理空间

• 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为 B+树的高度一般在 2-4 层，最多只需要读取 2-4 次磁盘，查询速度大大提升。

什么情况下需要建索引？

1. 经常用于查询的字段；即对经常出现在 WHERE 子句中的列创建索引。

2. 经常用于连接的字段建立索引，可以加快连接的速度

3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度；例如对 ORDER BY、GROUP BY 或者 DISTINCT 操作中出现的列建立索引。

什么情况下不推荐建索引？

1. where条件中用不到的字段不适合建立索引

2. 表记录较少。比如只有几百条数据，没必要加索引。

3. 需要经常增删改。对于频繁进行插入、更新和删除操作的表，索引会导致额外的维护开销，因为每次数据变更时都需要更新索引，这会影响性能。需要评估是否适合加索引，

4. 参与列计算的列不适合建索引

5. 区分度不高的字段不适合建立索引，如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。

索引的数据结构

索引的数据结构主要有 B+树和哈希表，对应的索引分别为 B+树索引和哈希索引。InnoDB 引擎的索引类型有 B+树索引和哈希索引，默认的索引类型为 B+树索引。

B+树索引

B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 key_i 和 key_i+1，则该指针指向节点的所有 key 大于等于 key_i 且小于等于 key_i+1。

进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于 B+树数据结构来实现。

mysql> show index from blog\G;*************************** 1. row ***************************        Table: blog   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: blog_id    Collation: A  Cardinality: 4     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:      Visible: YES   Expression: NULL

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的 key 值，将指向数据行的指针作为哈希表的 value 值。这样查找一个数据的时间复杂度就是 O(1)，一般多用于精确查找。

索引有什么分类？

从数据结构角度来看，MySQL 索引可以分为以下几类:

• B+树索引：通过树形结构存储数据，适用于范围查询(如 BETWEEN)和精确査询(如 =)，支持有序数据的快速查找、排序和聚合操作。是 MySQL 默认的索引类型，常用于 InnoDB 和 MyISAM 引擎

• 哈希索引：基于哈希表的结构，适用于等值查询(如 = )，查询速度非常快，但不支持范围査询(如>、<=)。哈希索引不存储数据的顺序，常用于 Memory 引擎。

• 倒排索引(Full-Text)：用于全文搜索，将全文分词，通过存储词与文档的映射，支持模糊匹都和关键字搜索，特别话合用于大文本字段，如 TXT 类型的列，用于查找包合特定词语的记录。

• R- 树索引：专为多维空间数据(如地理坐标)设计，适用于空间查询(例，计算地理位置的最近距离、区域查询等)。常用于存储和查询地理信息系统(G1S)中的空间数据。

从常见的基于 InnoDB B+树索引角度来看，可以分为：（后文中的聚集索引也就是聚簇索引）

• 聚簇索引：InnoDB 中主键索引就是聚族索引。它基于主键排序存储。之所以叫聚簇索引是因为素引的叶子节点存储完整数据行数据,

• 非聚谈索引：指的是 InnoDB 中非主键索引的索引，之所以称之为非聚族是因为这个索引叶子节点仅保存索引字段和主键的值，如果要查询完整的数据行中的数据，需要再从聚族索引助主键索引中通过主键查询，一个表可以有多个非聚族索引。

从索引性质的角度来看，可以分为:

• 普通索引(二级索引、辅助索引)：普通索引是最基本的索引，它没有任何限制，值可以为空。

• 主键索引：名为 primary 的唯一非空索引，不允许有空值。

• 联合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一索引字段可以为 null 且可以存在多个 null 值，而主键索引字段不可以为 null。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。

• 全文索引：只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

• 前缀索引：前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

• 空间索引：用于空间数据(如地图上的经纬度坐标等)查询。通常使用 R-树结构，适合多维数据的查询，如区域查询和最近距离查询，主要用于 MySAM 和 InnoDB 存储引警中的地理信息数据。

为什么不用 hash 表

这与 hash 表的原理有关。

使用 hash 表的目的是为了尽可能的散列，因此在使用 hash 表的时候要选择 hash 算法，避免 hash 碰撞和 hash 冲突。

这就导致了 hash 表存储的数据是无序的，当需要进行范围查询的时候，只能挨个进行遍历对比，效率极低

所以：

1. 不用 hash 表的原因在于不适合范围查询

2. 容易导致全表扫描，因为可能存在不同的 key 经过 hash 运算后值相同

3. 索引列上的值相同的话，易造成 hash 冲突，效率低下

为什么不用二叉树

如果将一个乱序的数据放入二叉树中，效率会高，但是如果数据是有顺序的，比如 1、2、3、4、5，则二叉树将会编程一个链表的样式，失去了二叉树的优势

总结：有可能退化成链表，读取数据需要频繁 IO

为什么不用红黑树（或二叉平衡树）

红黑树也是一种二叉平衡树，红黑树可以有效解决掉顺序数据一次放入二叉树而导致的形成链表的结果，但是红黑树一个节点只能存储一个数据，就导致如果是大量的数据，红黑树的高度就不可控，如果一个红黑树是 20 的高度，要查询的数据在叶子结点，则表示需要需磁盘 20 次 IO，效率还是不高

总结：相比于二叉树能避免退化成链表，但 IO 次数还是会很高

为什么不用 B 树而用 B+树

B 树比红黑树的优势是，B 树是一个节点上存储多个数据，比如磁盘的一页数据，这样的横向扩展，相同的数据量就可以比红黑树减少更多的高度，从而减少了磁盘的 IO 次数

B 树：data 为数据的磁盘地址，还可能是整条列的数据

1. 数据是分散在每个结点上的，所有结点元素不重复，

2. 结点元素从左到右递增

3. 叶子结点具有相同的深度，叶子结点的指针为空

B+树：data 为数据的磁盘地址，还可能是整条列的数据

1. 叶子结点包含了所有的索引字段，以及数据的磁盘地址，而非叶子结点不再存储 data 数据，作用只是便于查找的冗余索引

2. 非叶子结点是从子结点选取一页的开头来作为自己的值，指针为子结点那页的地址

3. 每一个结点里的值都是排好序的

4. 叶子结点之间是用「双向链表」进行连接，这样方便了范围查找，比如 where col > 10，这是 mysql 对 B+的变种，也是对比 B 树的一个优势

5. 由于 data 可能会很大，非叶子结点在不存储 data 后，非叶子可以存储的元素则会变多，还可以降低树的高度，提高了查询的效率，这是与 B 树对比，B+树的一个优势

总结：B+树相比于 B 树，非叶子节点不再存储 data 数据，非叶子可以存储的元素则会变多，一个非叶子节点就可以存储更多的索引数据，更进一步降低树的高度，提高了查询的效率。相比于 hash 表，B+树利用叶子节点之间的指针可以进行范围查询

为什么用 B+树作为索引结构？

• 高效的查找性能：B+树是一种自平衡树，每个叶子节点到根节点的路径长度相同，B+树在插入和删除节点时会进行分裂和合并操作，以保持树的平衡，但它又会有一定的冗余节点，使得删除的时候树结构的变化小，更高效。查找、插入、删除等操作的时间复杂度为 O(log n)，能够保证在大数据量情况下也能有较快的响应时间。

• 树的高度增长不会过快，使得查询磁盘的 I/0 次数减少：B+树不像红黑树，数据越多树的高度增长就越快，它是多叉树，非叶子节点仅保存主键或索引值和页面指针，使得每一页能容纳更多的记录，因此内存中就能存放更多案引，容易命中缓存，使得查询磁盘的 IO 次数减少。

• 范围查询能力强：B+ 树特别适合范围查询。因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描链表即可遍历后续的数据，非常高效。

什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。

对(a,b,c,d)建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么 a、b 和 c 三个字段能用到索引，而 d 无法使用索引。因为遇到了范围查询。

如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当 a 相等时，会根据 b 进行排序）。直接执行b = 2这种查询条件无法使用索引。

当 a 的值确定的时候，b 是有序的。例如a = 1时，b 值为 1，2 是有序的状态。当a = 2时候，b 的值为 1，4 也是有序状态。 当执行a = 1 and b = 2时 a 和 b 字段能用到索引。而执行a > 1 and b = 2时，a 字段能用到索引，b 字段用不到索引。因为 a 的值此时是一个范围，不是固定的，在这个范围内 b 值不是有序的，因此 b 字段无法使用索引。

什么是聚集索引？什么是聚集索引？

InnoDB 使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为 6 个字节，它的值会随着数据的插入自增。

非聚集索引：索引叶子节点存储的是数据行的主键和对应的索引列，需通过主键才能访问完整的数据行。一个表可以有多个非聚集索引(也称之为非主键索引、辅助索引、二级索引)，适用于快速查找特定列的数据。

什么是回表？

"回表"是指在使用二级索引(非聚簇索引)作为条件进行查询时，由于一级索引中只存储了索引字段的值和对应的主键值，无法得到真正想要的数据。

如果要查询数据行中的其它数据，需要根据主键去聚簇索引查找实际的数据行，这个过程被称为回表

什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以 MySQL 使用 b+树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的 extra 列会显示为using index。

比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。

explain select blog_id from user_like where user_id = 13;

explain结果的Extra列为Using index，查询的列被索引覆盖，并且 where 筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。

explain select user_id from user_like where blog_id = 1;

explain结果的Extra列为Using where; Using index， 查询的列被索引覆盖，where 筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

MySQL 索引的最左前缀匹配原则是什么?

MYSOL 索引(的最左前缀匹配原则指的是在使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。

底层原理：因为联合索引在 B+树中的排列方式道循“从左到右”的顺序，例如联合索引(a，b，c) 会按照(a，b，c)的顺字在 B+ 树中进行排序。MYSQL 在查找时会优先使用 a 作为匹配依据，然后依次使用 b 和 c。因此，组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引。

以下查询条件符合最左匹配原则

where a=1;where a=1, b=2;where a=1, b=2, c=3;

以下条件不符合最左匹配原则

where b=2;where c=3;where b=2, c=3;

索引的设计原则？

选择合适的列：

• 经常用于查询的字段；即对经常出现在 WHERE 子句中的列创建索引。

• 经常用于连接的字段建立索引，可以加快连接的速度

• 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度；例如对 ORDER BY、GROUP BY 或者 DISTINCT 操作中出现的列建立索引。

优化多列索引：

• 复合索引：对多个列组合查询的情况，考虑使用复合索引而非单个列索引，以减少表扫描次数。

• 利用最左前缀原则：确保复合索引的列顺序遵循最左前缀原则，即将使用最频繁的列放在最前面。

控制索引的数量和类型 ：

• 避免过多索引： 索引有利于查询，但每个新增索引会影响插入、更新、删除的性能。应在查询效率与维护成本之间取得平衡。

• 适用索引类型： 选择合适类型的索引，例如 B-tree 索引用于大多数情况，Full-text 索引用于全文搜索，Hash 索引用于等值查询（如内存表）。

考虑数据分布和选择性 ：

• 高选择性的列： 高基数、高选择性（具有许多不同值）的列索引往往能提高查询效率。

• 均匀分布的数据： 索引在数据分布均匀时效果最佳，避免对只有少数不同值的列（低基数）设置索引。

避免冗余和重复索引

• 合并索引：考虑合并类似用途的索引，减少冗余。

• 定期清理：定期审查和清理不再使用或效果不佳的索引。

其他注意点：

• 覆蓋索引： 当索引包含查询所需的所有列时，称为覆盖索引，能有效减少读取数据页的次数。尽量使查询使用覆盖索引。

• 索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

• 避免给"大字段"建立索引。尽量使用数据量小的字段作为索引。因为MySQL在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。

• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘 I/O 较少，查询速度更快。

• 频繁增删改的字段不要建立索引。假设某个字段频繁修改，那就意味着需要频繁的重建索引，这必然影响 MySQL 的性能

使用索引一定有效吗?如何排查索引效果?

索引不一定有效。

例如查询条件中不包含索引列、低基数列索引效果不佳，或查询条件复杂且不匹配索引的顺序。

对于一些小表，MySQL 可能选择全表扫描而非使用索引，因为全表扫描的开销可能更小。

最终是否用上索引是根据 MySQL 成本计算决定的，评估 CPU 和 IO 成本最终选择用辅助索引还是全表扫猫，有时候确实是全表扫描成本低所以没用上索引，但有时候由于一些统计数据的不准确，导致成本计算误判，而没用上索引。

排查索引效果的方法：使用 EXPLAIN 命令，通过在查询前加上 EXPLAIN，可以查看 MSQL 选择的执行计划，了解是否使用了索引、使用了哪个索引、估算的行数等信息。

主要观察 EXPLAIN 结果以下几点

• type(访问类型)：这个属性显示了查询使用的访问方法，例如 ALL、index、range 等，当查询使用案引时，这个属性通常会显示为 index 或 range，表示查询使用了案引访问。如果这个值是 ALL，则表示查询执行了全表扫描

• key(使用的索引)：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是 ALL，则表示查询没有使用索引。

• rows(扫描的行数)：这个属性显示了查询扫描的行数，需要评估下扫描量

区分度不高的字段建索引一定没用吗

区分度不高的字段建索引的问题:

• 低效的过滤效果： 如果字段的选择性很低（例如只有几个不同的值），那么通过索引过滤数据的效果可能不明显，因为每个值对应太多的行。

• 索引维护开销： 在更新或插入操作时，所有的索引需要同步更新，低选择性字段的索引可能带来不必要的开销，而收益有限。

• 增大查询成本： 尽管索引帮助快速定位数据位置，但由于返回的数据集过大，可能会导致后续操作（如排序和过滤）带来额外的系统开销。

那何时低选择性索引可能有用呢 ？

• 固定值查询： 即使选择性低，如果应用程序中有特定固定的值或者特定过滤条件经常被使用，并要求快速响应（如状态标志字段），索引仍可能提高查询速度。

• 结合其他高选择性条件： 当低选择性的字段被结合其他有高选择性的字段一起使用时，可能仍然增益明显。

• 作为覆盖索引的一部分： 如果一个多列索引（复合索引）包含一个低选择性字段，同时能使一个查询成为覆盖索引，这依然可以提升性能。

• 大数据集中的特定场合： 在非常大的数据集中，占用大量数据的字段索引可能仍有助于减少所需的扫描行数。

• 统计分析和报告： 如果索引用于统计或聚合操作中，通过索引可以更高效地进行分组操作。

以上属于特定情况，区分度不高的字段建立索引还是要依据具体应用场景和查询场景进行权衡和测试。

索引什么时候会失效？

导致索引失效的情况：

• 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引

• 以 %开头的 like 查询如%abc，无法使用索引；非 %开头的 like 查询如abc%，相当于范围查询，会使用索引

• 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

• 判断索引列是否不等于某个值时

• 对索引列进行运算

• 查询条件使用or连接，也会导致索引失效

什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的数据行。

什么是索引下推？

索引下推(Index Condition Pushdown,ICP)是一种减少回表查询,提高查询效率的技术。它允许 MVSQL 在使用索引查找数据时，将部分査询条件下推到存储引层过滤，从而减少需要从表中读取的数据行，减少了 IO(本该由 Server 层做操作，交由存储引擎层因此叫做“下推”)。

注意：索引下推是应用在联合索引上的。

在没有索引下推的情况下，当查询使用复合索引时，MySQL 可能需要访问主表来评估不能完全通过索引条件确定的行。例如，如果只有索引的部分条件在索引中能被使用，而其他条件需要读取实际行再进行筛选，传统方式可能会导致更多的全行读取。

在联合索引遍历过程中，对联合索引中包含的字段先做判断，在存储引擎层进行数据过滤，而不是在服务层过滤，直接过滤掉不满足条件的记录，利用索引现有的数据减少回表次数，就叫索引下推

假设当前 people 表有索引 INDEX(zipcode，lastname，firstname)，当执行以下查询时:

SELECT * FROM people  WHERE zipcode='95054'  AND lastname LIKE '%etrunia%'  AND address LIKE '%Main Street%';

如果没有索引下推，当前的联合索引只能用上 zipcode='95854’这个条件，引擎层用不上 lastname 这个条件过滤，只能得到所有符合 zipcode='95054'记录，传递给 server 层过滤。

有了索引下推之后，引擎层在得到符合 zipcode='95854'的数据后，可以直接通过 lastname 条件过滤数据，不符合条件的不会返回给 server 层。

索引数量是否越多越好?为什么?

索引并不是越多越好。因为索引不论从时间还是空间上都是有一定成本的

从时间上：每次对表中的数据进行增删改的时候，索引也必须被更新,，这会增加写入操作的开销，例如出除了一个 name 为 seven 的记录，不仅主键索引上需要修改，如果 name 字段有索引，那么 name 索引都需要修改，所以索引越多需要修改的地方也就越多，时间开销就大了，并目 B+ 树可能会有页分裂、合并等操作，时间开销就会更大。还有一点需要注意，mysql 有个查询优化器，它需要分析当前的查询，选择最优的计划、这过程就需要考虑出选择哪个索引的查询或本低，如果索引过多，那么会导致优化器耗费更多的时间在选择上，甚至可能因为索引选的不准确而选择了次优的索引。从空间上：每建立一个二级索引，都需要新建一个 B+ 树，默认每个数据页都是 16KB，如果教据量很大，索引又很多，占用的空间可不小

Innodb 加索引的时候会锁表吗

在 InnoDB 中，操作索引时是否锁表取决于具体的操作类型。有些情况可能会导致表锁，而有些情况则不会。

• 在线添加索引（Online DDL）：从 MySQL 5.6 开始，InnoDB 支持在线添加索引。这意味着在添加索引期间，表可以同时进行读取和写入操作，从而不会锁住整张表。在线 DDL 的特性允许通过特定配置，让表在加索引的同时保持可用状态（如 LOCK=NONE 选项）。默认情况下，即使是在线添加索引，也可能会对表的某些部分进行短暂的锁定，但不会长期锁表。需要注意的是，在不支持在线 DDL 的旧版本中，添加索引通常是涉及锁表的操作，尤其是在大量数据情况下。

• 删除索引操作通常不需要长时间锁表，但在删除过程中可能进行短暂的锁定以保证元数据更新的一致性。

• 对于某些索引修改，可能会引发锁表现象，尤其是在不支持在线 DDL 的情况下或在某些特定场景下，需要评估具体 MySQL 版本和参数配置。

文章转载自：程序员Seven

原文链接：https://www.cnblogs.com/seven97-top/p/19019031

体验地址：http://www.jnpfsoft.com/?from=001YH