MySQL索引

前言

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

创建索引 

        1.普通索引

        当我们的users表中有username字段用户数量较为庞大,并且我们需要查询出某个用户名则需要创建一个索引以优化查询速度.

CREATE INDEX idx_username ON users (username);

        检查查询过程是否使用了索引

EXPLAIN SELECT * FROM users WHERE username = 'example';

        1.1分析执行过程

                

                使用EXPLAIN语句分析查询计划并验证索引的使用情况：

                          检查查询计划中的"possible_keys"字段和"key"字段：

                                如果"possible_keys"字段中列出了索引，且"key"字段显示了使用的索引，则索引被成功使用。

                                如果"possible_keys"字段为空，或者"key"字段显示为NULL，则索引失效。

        1.2 使用LIKE

                在SQL中为字段添加了索引,因为索引是全文查询,所以在使用LIKE时通配符%应在后面,否则索引失效.

                例如:       

        在上述演示中我们不难发现当LIKE中的通配符在前使用了,索引失效了. 

        1.3原因及思考

•  在全查询的情况下，索引的选择性和索引字段的长度可能对索引的使用产生影响。
  • 索引选择性较好且索引字段长度足够长时，索引可以有效地过滤出匹配的结果，提高查询性能。
  • 索引选择性较差或索引字段长度过短时，索引可能无法有效过滤数据，导致索引失效。
  • 当你创建一个索引时，MySQL会按照索引列的值进行排序并存储索引项。对于精确匹配查询，MySQL可以直接定位到索引项并快速返回结果。但是，对于模糊查询，MySQL需要扫描整个索引来查找匹配的行，这可能会导致性能下降

                        

        2.删除索引

                       我们可以使用 DROP INDEX 语句来删除索引。

                        DROP INDEX 的语法:

DROP INDEX idx_user_name on users;-- DROP INDEX固定关键字,idx_user_name索引名

        3.唯一索引

                在 MySQL 中，你可以使用 CREATE UNIQUE INDEX 语句来创建唯一索引。

                唯一索引确保索引中的值是唯一的，不允许有重复值。

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

                

• CREATE UNIQUE INDEX: 用于创建唯一索引的关键字组合。
• index_name: 指定要创建的唯一索引的名称。索引名称在表中必须是唯一的。
• table_name: 指定要在哪个表上创建唯一索引。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下是一个创建唯一索引的实例： 假设我们有一个名为 employees的 表，包含 id 和 email 列，现在我们想在email列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

CREATE UNIQUE INDEX idx_email ON employees (email);