Mysql索引

1.什么是索引？

        索引是数据库中用于优化查询性能的一种数据结构。它类似于书籍的目录，通过建立索引，可以快速定位到数据表中的特定记录，而不需要扫描整个表。

2.为什么需要索引？

• 提高查询效率：索引可以显著减少数据库查询时需要扫描的数据量。例如，如果你有一个包含百万条记录的表，并且经常根据某个字段进行查询，那么在该字段上建立索引可以快速定位到符合条件的记录，而不是逐行扫描整个表。

• 优化排序和分组：索引可以帮助数据库更高效地进行排序和分组操作。如果查询中包含ORDER BY或GROUP BY子句，索引可以减少排序和分组的计算开销。

• 实现唯一性约束：唯一索引可以确保某个字段或字段组合的值在表中是唯一的，从而防止数据重复。

3.如何使用索引？

3.1 索引的分类

现在按照不同规则的分类太多了，这里仅以用途作为划分。

主键索引：基于主键（Primary Key）建立的索引；

通常在创建表的时候⼀起创建，⼀张表最多只有⼀个主 键索引，索引列的值不允许有空值。

CREATE TABLE table_name (
PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引：保证索引列的值在表中是唯一的；

唯⼀索引建⽴在 UNIQUE 字段上的索引，⼀张表可以有多个唯⼀索引，索引列的值必须唯⼀，但 是允许有空值。

CREATE TABLE table_name (
UNIQUE KEY(index_column_1,index_column_2,...)
);

普通索引：没有唯一性限制，可以有重复的值；

普通索引就是建⽴在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

CREATE TABLE table_name (
INDEX(index_column_1,index_column_2,...)
);

联合索引：基于多个列建立的索引；

CREATE INDEX idx_name_email ON users (name, email);

前缀索引：只对字符串类型的字段的前几个字符建立索引，而不是对整个字符串建立索引；

CREATE TABLE table_name(
column_list,
INDEX(column_name(length))
);

全文索引：用于全文搜索，可以快速查找文本内容；

CREATE FULLTEXT INDEX idx_content ON articles (content);

3.2 索引的使用时机

什么时候适⽤索引？

字段有唯⼀性限制的，⽐如商品编码；

经常⽤于 WHERE 查询条件的字段，这样能够提⾼整个表的查询速度，如果查询条件不是⼀个 字段，可以建⽴联合索引。

经常⽤于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做⼀次排序了，因 为我们都已经知道了建⽴索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

WHERE 条件， GROUP BY ， ORDER BY ⾥⽤不到的字段，索引的价值是快速定位，如果起不到 定位的字段通常是不需要创建索引的，因为索引是会占⽤物理空间的。

字段中存在⼤量重复数据，不需要创建索引，⽐如性别字段，只有男⼥，如果数据库表中，男 ⼥的记录分布均匀，那么⽆论搜索哪个值都可能得到⼀半的数据。在这些情况下，还不如不要 索引，因为 MySQL 还有⼀个查询优化器，查询优化器发现某个值出现在表的数据⾏中的百分⽐ 很⾼的时候，它⼀般会忽略索引，进⾏全表扫描。

表数据太少的时候，不需要创建索引；

经常更新的字段不⽤创建索引，⽐如不要对电商项⽬的⽤⼾余额建⽴索引，因为索引字段频繁 修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性 能的。

3.3 sql优化

explain select * from sys_user where user_id = 1

id：表示查询中每个 SELECT 的标识符；

select_type：表示查询的类型；

table：表示查询的表；

partitions：表示查询涉及的分区；

type 表⽰数据扫描类型，我们需要重点看这个；

possible_keys 字段表⽰可能⽤到的索引；

key 字段表⽰实际⽤的索引，如果这⼀项为 ，说明没有使⽤索引；

key_len 表⽰索引的⻓度；

ref：表示索引列的值与哪个列或常量进行比较；

rows 表⽰扫描的数据⾏数；

filtered：表示通过条件过滤的行数的百分比；

Extra：提供额外的查询信息；

4. 索引失效

1.当我们使⽤左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种⽅式都会 造成索引失效；

2.当我们在查询条件中对索引列使⽤函数，就会导致索引失效。

3.当我们在查询条件中对索引列进⾏表达式计算，也是⽆法⾛索引的。

4.隐式类型转换是通过 CAST 函数实现的，等同于对索引列使⽤了函数，所以就会导致索引失 效。

5.联合索引要能正确使⽤需要遵循最左匹配原则，也就是按照最左优先的⽅式进⾏索引的匹配， 否则就会导致索引失效。

6.在 WHERE ⼦句中，如果在 OR 前的条件列是索引列，⽽在 OR 后的条件列不是索引列，那么索 引会失效。