Mysql索引详细整理（Innodb）

索引类型

普通索引：又称二级索引或辅助索引，就是在非主键，非唯一的字段上加索引。

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个。(当有主键时，innodb就用主键来组织数据，当没有主键时，就用一个唯一的字段)

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

其实创建什么索引，就要看你经常查询的的状况，例如你经常是几个列一起查，那么用复合索引，单独列进行查询就用单值索引

如果要利用复合索引，必须要符合最左匹配原则。
如果复合索引是name，age，bir顺序
那就只能利用name/name,age/name,age，bir查才能够利用索引
Mysql执行引擎为了更好的利用索引，在查询过程中会动态的调整索引顺序，所以三者调换顺序也是可以的。但还要满足最左前缀匹配原则

页目录管理索引

形成B+树

一般2-3层，顶层页常驻内存（减少IO）

聚簇索引和非聚簇索引

主键索引一定是聚簇索引
辅助索引也叫二级索引，都是非聚簇索引
（聚簇索引，聚集索引其实都一样）

整个过程从辅助索引树到聚簇索引树的过程叫做“回表”。

为什么不直接存储物理位置呢？ 因为数据的增删改会改变数据存储的具体的物理位置，但是与数据对应的主键却是不变的，所以借助主键索引再进行二次查找

使用聚簇索引的优势

• 问题:每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗?聚簇索引的优势在哪?
• 1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
• 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化﹔或者是我们需要查找的数据，在上一次Io读写的缓存中没有，需要发生一次新的Io操作时，可以避免对特辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

聚簇索引需要注意什么?

。当使用主键为聚蔟索引时，主键最好不要使用uuid ，因j为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
。建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影冲最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间,也会影响到Io操作读取到的数据量。
#11．为什么主键通常建议使用自增id
12 34
1 2 3 4
聚簇索引的微j据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少,效率也高。

聚簇索引的缺点

• 聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
• 插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
• 聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。
• 聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。
  ————————————————
  版权声明：本文为CSDN博主「那些年的代码」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
  原文链接：https://blog.csdn.net/weixin_44018338/article/details/105166521

思考：聚簇索引感觉就是将索引和数据存放在了一起，那么一个表中可以存在多少个聚簇索引呢？聚簇索引多了不就造成数据重复，会占用大量空间吗？如果要存储数据，那每个表都需要至少有一个聚簇索引吗？

聚簇索引的创建

每个InnoDB表都需要一个聚簇索引。该聚簇索引可以帮助表优化增删改查操作。

如果你为表定义了一个主键，MySQL将使用主键作为聚簇索引。

如果你不为表指定一个主键，MySQL讲索第一个组成列都not null的唯一索引作为聚簇索引。

如果InnoBD表没有主键且没有适合的唯一索引（没有构成该唯一索引的所有列都NOT NULL），MySQL将自动创建一个隐藏的名字为“GEN_CLUST_INDEX ”的聚簇索引。

因此每个InnoDB表都有且仅有一个聚簇索引。
————————————————
版权声明：本文为CSDN博主「明明如月学长」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/w605283073/article/details/95255618

什么情况下无法利用索引呢?

• 1．查询语句中使用LIKE关键字
  在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为"%”，索引不会被使用。如果"%"不是在第一个位置，索引就会被使用。
• 2.查询语句中使用多列索引
  多列索引是在表的多个字段上创建一个索引**，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。**
• 3.查询语句中使用OR关键字
  查询语句只有OR关键字时，如果on前后的两个条件的列都是索引，那么查询中将使用索引**。如果oR前后有一个条件的列不是索引，那么查询中将不使用索引。**

稠密索引和稀疏索引

稠密索引
**在稠密索引中，文件中的每个搜索码值都对应一个索引值。也就是说，稠密索引为数据记录文件的每一条记录都设一个键-指针对。**如下图所示，索引项包括索引值以及指向该搜索码的第一条数据记录的指针，即我们所说的键-指针对。

稀疏索引
在稀疏索引中，只为搜索码的某些值建立索引项。也就是说，稀疏索引为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存储单元连续。如下图所示。

优缺点：

• 稠密索引比稀疏索引更快的定位一条记录。
• 稀疏索引所占空间小，并且插入和删除时所需维护的开销也小。
  ————————————————
  版权声明：本文为CSDN博主「Jeaforea」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
  原文链接：https://blog.csdn.net/Jeaforea/article/details/61420445

Hash索引和B+tree索引的区别

1、在查询速度上，如果是等值查询，那么Hash索引明显有绝对优势，因为只需要经过一次 Hash 算法即可找到相应的键值，复杂度为O(1)；当然了，这个前提是键值都是唯一的。如果键值不是唯一(或存在Hash冲突)，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据，这时候复杂度会变成O(n)，降低了Hash索引的查找效率。所以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等（当然B+tree索引也不适合这种离散型低的字段上）；
> 2、Hash 索引是无序的，如果是范围查询检索，这时候 Hash 索引就无法起到作用，即使原先是有序的键值，经过 Hash 算法后，也会变成不连续的了。因此

①、Hash 索引只支持等值比较查询、无法索成范围查询检索，B+tree索引的叶子节点形成有序链表，便于范围查询。
②、Hash 索引无法做 like ‘xxx%’ 这样的部分模糊查询，因为需要对 完整 key 做 Hash 计算，定位bucket。而 B+tree 索引具有最左前缀匹配，可以进行部分模糊查询。
③、Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。B+tree
索引的叶子节点形成有序链表，可用于排序。

3、Hash 索引不支持多列联合索引，对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用；
4、因为存在哈希碰撞问题，在有大量重复键值情况下，哈希索引的效率极低。B+tree 所有查询都要找到叶子节点，性能稳定；

————————————————
版权声明：本文为CSDN博主「老王(Rean.Wang)」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_43841693/article/details/107301253

覆盖索引

简单地讲就是在二级索引部分就已经可以获得选择的所有列，那就不必要再去进行聚簇索引的搜索，可以减少IO操作。例子就像筛选主键id，那么筛选条件可以直接使用辅助索引，那么辅助索引的叶子节点直接保存的的就是主键值，那就不需要再去进行聚簇索引的操作。

**InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。**使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作

• 非聚集索引上直接可以拿到所需数据，不需要再回表查，比如 select id from table where name = xxx;(id为主键、name为索引列)
• 在统计操作中也会使用覆盖索引。比如(a,b)联合索引，select * from table where b = xxx语句按最左前缀原则是不会走索引的，但如果是统计语句select count(*) from table where b = xxx;就会使用覆盖索引。
  ————————————————
  版权声明：本文为CSDN博主「果子爸聊技术」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
  原文链接：https://blog.csdn.net/pzjtian/article/details/107327060

冗余索引和重复索引

冗余索引：存在索引（A，B），则索引A是冗余索引，一模一样的（A，B）则是重复索引。

索引(条件）下推

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
  
• 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
  
  ————————————————
  版权声明：本文为CSDN博主「古柏树下」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
  原文链接：https://blog.csdn.net/sinat_29774479/article/details/103470244

还是来看这句sql：select * from t_yuangong where name=? and age=?;
首先我们要知道(name,age)组合索引是在同一个B+树上。
组织形式是一个二元组，如下图：比如我找(2,3),那么我肯定要先看name列，再看age列。

没有索引下推时，先从存储引擎中拉取数据(根据name筛选的数据)，然后mysql服务器根据拉去的数据再通过age去筛选。
有索引下推时，会直接根据name，age来获取数据，不需要再用mysql服务器进行数据筛选。
索引下推是将数据筛选从内存中转移到了磁盘上！！大大减少了IO量

————————————————
版权声明：本文为CSDN博主「ygpGoogle」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/ygp12345/article/details/109254107

应用 WHERE的服务器层和存储引擎

一般MySQL能够使用如下三种方式应用 WHERE条件，从好到坏依次为:

• 在索引中使用 WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖扫描（在Extra列中出现了Using index〉来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在 MySQL服务器层完成的，但无须再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where)。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。