索引
什么是索引？
索引是存储引擎用于提高数据库表的访问速度的一种数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
索引一般存储在磁盘的文件中，它是占用物理空间的。
索引的优缺点？
优点：
- 加快数据查找的速度
- 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
- 加快表与表之间的连接
缺点：
- 建立索引需要占用物理空间
- 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长
索引的作用？
数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。
什么情况下需要建索引？
- 经常用于查询的字段；即对经常出现在 WHERE 子句中的列创建索引。
- 经常用于连接的字段建立索引，可以加快连接的速度
- 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度；例如对 ORDER BY、GROUP BY 或者 DISTINCT 操作中出现的列建立索引。
什么情况下不推荐建索引？
where条件中用不到的字段不适合建立索引- 表记录较少。比如只有几百条数据，没必要加索引。
- 需要经常增删改。对于频繁进行插入、更新和删除操作的表，索引会导致额外的维护开销，因为每次数据变更时都需要更新索引，这会影响性能。需要评估是否适合加索引，
- 参与列计算的列不适合建索引
- 区分度不高的字段不适合建立索引，如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。
什么是最左匹配原则？
MYSQL索引(的最左前缀匹配原则指的是在使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。
底层原理：因为联合索引在B+树中的排列方式道循“从左到右”的顺序，例如联合索引(a，b，c) 会按照(a，b，c)的顺字在B+ 树中进行排序。MYSQL在查找时会优先使用 a 作为匹配依据，然后依次使用 b 和 c。因此，组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引。
如果 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无法使用索引。因为遇到了范围查询。
以下查询条件符合最左匹配原则
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连接，也会导致索引失效
索引数量是否越多越好?为什么?
索引并不是越多越好。因为索引不论从时间还是空间上都是有一定成本的
从时间上：每次对表中的数据进行增删改的时候，索引也必须被更新,，这会增加写入操作的开销，例如出除了一个 name为seven 的记录，不仅主键索引上需要修改，如果name字段有索引，那么 name 索引都需要修改，所以索引越多需要修改的地方也就越多，时间开销就大了，并目 B+ 树可能会有页分裂、合并等操作，时间开销就会更大。还有一点需要注意，mysql 有个查询优化器，它需要分析当前的查询，选择最优的计划、这过程就需要考虑出选择哪个索引的查询或本低，如果索引过多，那么会导致优化器耗费更多的时间在选择上，甚至可能因为索引选的不准确而选择了次优的索引。
从空间上：每建立一个二级索引，都需要新建一个 B+ 树，默认每个数据页都是16KB，如果教据量很大，索引又很多，占用的空间可不小
浙公网安备 33010602011771号