(二)Mysql篇---Mysql索引

⭐简单说两句⭐

✨ 一直努力让自己发光发亮的银氨呀~ 💖 超级爱分享，分享各种有趣干货！ 👩‍💻 记录学习，也是记录生活，我们一起进步~ 🌈 感谢关注，关注了你就是我的超级粉丝啦！ 🔒 以下内容仅对你可见~

作者：银氨溶液

优快云个人主页：银氨溶液

🎉欢迎关注🔎点赞👍收藏⭐️留言📝

本篇文章带各位小伙伴们一起来学学mysql索引和日志相关知识点，那废话不多说，我们直接开始吧~

索引

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

我们加了索引之后也是这样的原理，传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，MySQL需要将表的数据从头到尾遍历一遍。在我们添加完索引之后，MySQL一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，使用能够大幅地查询的效率的折半查找的方式，找到相应的键从而获取数据。

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

索引类型也是有很多种的，分别如下：主键索引、唯一索引、普通索引、复合索引、全文索引。

• 主键索引：这表明你的索引只能是你某张表中的主键，且设置索引后，索引的值不能为 null，且必须唯一。
• 唯一索引：包含在主键索引下面的，区别就在于索引列的值必须是唯一的，但是可以允许为空值。

create table d(
  id int primary key auto_increment , 
  name varchar(32) unique
    )

• 普通索引：普通索引普通的就在于可以选择表中任意一个字段作为索引字段，没有任何限制。

create index 索引名 on table_name(column1);
alter table table_name add index 索引名(column1);

• 复合索引：见文知意，需要多个字段同时设置索引，在使用的时候需要遵循最左匹配原则。

比如我创建了复合索引（a,b,c）,在使用的时候条件语句必须要带上a字段，否则索引会失效，例如：where b='ksdksk' and c='sjkksk'  或者 where b='sdsd' 或者 where c='dfsd'，以上sql条件语句都会导致索引失效。

想想为什么会失效呢？

这里如果你还不了解mysql底层存储结构的，可以先去看看这篇文章：MySQL底层为什么选择用B+树作为索引

        现在就当你已经了解过了底层的存储结构，所以你也知道了如果我创建一个复合索引，那么索引的存储是依次进行存储的，且在构建B+树的时候创建索引也只会创建一条，即（a,b,c），相当于是一个整体,索引先按照a排序，a相同的时候再按照b排序，b相同的时候再按照c排序；那我们在读取数据的时候自然也是先读取a的值，a相同的话再去读取b的值，b相同的话再去读取c的值，因为mysql的索引是层层读取的，从根节点到叶子结点逐层读取，如果我缺了最左边的索引a，那么无论怎么读取，都无法读取正确。

• 全文索引：全文索引主要针对文本文件，比如文章，标题。在MySQL5.6之前，只有MyISAM存储引擎支持全文索引，MySQL5.6之后InnoDB存储引擎也支持全文索引。

虽然索引能够更高效的帮我们进行数据处理，但是索引没有创建好也会直接影响整体性能，也有可能导致慢sql发生。

如何创建一个好的索引

• 首先，我们得知道的是索引不是越多越好，东西在精不在多，放在这上面也同样适用滴~

• 主要是因为索引也是存储在磁盘当中的，如果索引创建过多，我的数据在频繁的进行增删改操作的时候，索引也需要同时进行调整，这样就极大提高了磁盘的I/O操作，导致性能下降；
• 其二索引创建好以后也是需要维护滴，也需要占用大量的空间来存储，尤其是数据表中数据量大的时候，索引的占用更是显而易见的；
• 其三，在数据删除的时候，可能会导致索引文件出现碎片，影响磁盘的读写性能。

• 索引的创建需要有较大的区分度，如果我有一行属性列，这行属性列有较多的重复字段，那如果我把这个属性列设置为索引，那么在查询的时候同样会查询到很多数据，而且sql执行的时候会走一次优化器，如果当前索引区分度不高，可能就直接走全文索引了~
• 避免对频繁进行增删改操作的属性上建立索引，还是上面第一条原因，频繁对数据进行修改的同时，索引为了维护数据，索引也会进行大量操作，导致性能下降，不好维护，也有可能导致索引文件出现碎片，影响磁盘读写性能。
• 如果是构建的复合索引，把区分度高的索引放在最前面------遵守最左匹配原则。
• 联合索引可以通过索引覆盖（如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。）而避免回表查询，可以大大提升效率，对于频繁的查询，可以考虑将select后面的字段和where后面的条件放在一起创建联合索引。
• 当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

索引失效

当然，如果我们创建好了索引，没有正确上的使用也是会导致索引失效的，一般失效的场景有以下：
在 MySQL 中，索引是用来加速查询的，但在某些场景下，索引可能会失效，导致查询性能降低，甚至需要进行全表扫描。以下是一些常见的索引失效的场景：

 1. 使用 `OR` 连接条件时
   - 当查询条件中使用 `OR` 连接多个条件时，索引可能会失效，特别是当 `OR` 连接的列没有联合索引时。MySQL 无法使用单个索引来优化 `OR` 的查询。
  例子：
     SELECT * FROM users WHERE name = 'John' OR age = 25;
     如果 `name` 和 `age` 列有单独的索引，MySQL 可能无法有效利用这些索引，尤其是当 `OR` 两侧的条件分别位于不同的列时。

 解决方法：使用联合索引，或者使用 `UNION` 来避免使用 `OR` 连接。

 SELECT * FROM users WHERE name = 'John'
   UNION
   SELECT * FROM users WHERE age = 25;

 2. 使用 `LIKE` 进行模糊匹配时（尤其是以通配符开头）
   - 如果在 `LIKE` 查询中使用通配符 `'%xxx'` 来匹配字符串的开头，索引将无法被有效利用，因为 MySQL 无法确定匹配的起始位置。
   例子：
     SELECT * FROM users WHERE name LIKE '%John'
     由于 `%` 在前面，MySQL 需要对所有数据进行匹配，而不是通过索引来加速查询。

   解决方法：尽量避免使用 `%` 在前的模糊查询。若必须使用，可以考虑全文索引（Full-Text Index）。

 3. 使用 `NULL` 值进行查询时
   - 当查询条件中涉及 `NULL` 值时，MySQL 无法利用索引进行优化，特别是对于 `IS NULL` 或 `IS NOT NULL` 的查询。
例子：
     SELECT * FROM users WHERE email IS NULL;
     虽然字段 `email` 上有索引，但 MySQL 不能通过索引来优化此类查询，因为索引结构不包含 `NULL` 的信息。

   解决方法：如果字段上有很多 `NULL` 值，考虑重新设计表结构或通过不同的查询条件来处理。

 4. 函数操作或表达式中使用列
   - 在查询中对列进行函数操作或表达式计算时，索引会失效。例如，在 `WHERE` 子句中使用 `CONCAT()`、`DATE()`、`UPPER()` 等函数时，索引将无法生效。
 例子：
          SELECT * FROM users WHERE YEAR(birthdate) = 1990;
     `YEAR(birthdate)` 会导致索引失效，因为 `birthdate` 列上的索引只适用于直接匹配该列的查询。

 5. 范围查询与其他条件混用
   - 如果查询条件中包含范围查询（如 `BETWEEN`、`<`、`>`）和等值查询（如 `=`），通常情况下索引只能对范围查询生效，而等值查询的条件将失效。
   - 例子：
     SELECT * FROM users WHERE age = 30 AND created_at > '2023-01-01';
     如果 `age` 和 `created_at` 分别有索引，MySQL 可能只能使用 `created_at` 的索引，而不能同时使用两个索引。

6. 使用 `DISTINCT` 或 `GROUP BY` 时
   - 当查询使用 `DISTINCT` 或 `GROUP BY` 时，MySQL 可能无法充分利用索引，尤其是当查询的字段没有合适的索引时。这是因为 MySQL 可能需要扫描大量的数据来实现去重或分组操作。
   例子：
     SELECT DISTINCT name FROM users;
     如果 `name` 列上没有合适的索引，`DISTINCT` 操作可能导致全表扫描。

   解决方法：在常用的查询列上创建合适的索引，尤其是对于 `GROUP BY` 和 `DISTINCT` 操作相关的列。

 7. `JOIN` 操作时没有适当的索引
   - 当使用 `JOIN` 操作连接多个表时，如果连接字段没有索引，MySQL 可能会选择全表扫描而不是利用索引加速查询。
    例子：
     SELECT * FROM orders JOIN users ON orders.user_id = users.id;
     如果 `orders.user_id` 或 `users.id` 上没有索引，查询会变得非常慢。

 总结

索引虽然能加速查询，但在以下几种情况下会失效：

1. 使用 `OR` 连接多个条件。
2. 使用 `LIKE` 查询时，尤其是以 `%` 开头。
3. 查询涉及 `NULL` 值。
4. 对索引列使用函数或表达式。
5. 范围查询与等值查询混合。
6. 使用 `DISTINCT` 或 `GROUP BY` 时。
7. `JOIN` 操作时缺少合适的索引。
8. 排序操作 (`ORDER BY`) 时缺少索引。
9. 使用 `NOT IN` 或 `<>` 等操作时。

今天就先聊到这儿，如果有补充的后面这篇文章还会继续更新，也欢迎各位评论区留言哦~

【都看到这了，点赞加关注，收藏不迷路呀~】😚😚

✨ 一直努力让自己发光发亮的银氨啊~ 💖 爱生活，更爱分享，分享各种学习干货！🌈 感谢关注，关注了你就是我的超级粉丝啦！ 🔒 以下内容仅对你可见~

作者：银氨溶液

优快云个人主页：银氨溶液

🎉欢迎关注🔎点赞👍收藏⭐️留言📝