MySQL的索引

MySQL的索引

MySQL中的索引是一种特殊的数据结构，用于提高数据库表的查询速度。通过在列上创建索引，MySQL可以更快地定位和检索数据，尤其是对大型数据集进行查询时效果明显。以下是关于MySQL索引的一些重要信息：

1. 索引类型：

   • B-tree索引：最常见的索引类型，适用于全精确匹配和部分匹配。
   • 哈希索引：适用于等值查询，不支持范围查询，通常用于内存表。
   • 全文索引：用于全文搜索，适用于对文本内容进行搜索。

2. 索引的创建：

   • 在创建表格时可以在一个或多个列上创建索引。
   • 使用 CREATE INDEX 语句来为已存在的表格添加索引。

3. 索引的使用：

   • 当执行查询时，MySQL会尝试使用索引来加速查询，减少扫描整个表格的开销。
   • 通过 EXPLAIN 关键字可以查看MySQL执行查询时是否使用了索引。

4. 索引的优化：

   • 确保对经常查询的列创建索引，以提高查询性能。
   • 避免创建过多的索引，因为每个索引都会占用额外的存储空间，并且会增加写操作的开销。

5. 主键和唯一索引：

   • 主键在MySQL中是一种特殊的唯一索引，用于唯一标识一条记录。
   • 唯一索引也能确保列中的值是唯一的，但允许NULL值，而主键则不允许NULL值。

6. 索引的限制：

   • 索引需要占用额外的存储空间。
   • 对表格进行插入、更新、删除等操作时，索引也需要维护，可能影响写入性能。

在实际应用中，合理地使用索引可以显著提高数据库的查询性能，但需要根据具体的业务需求和数据特点来进行设计和优化。

什么是聚簇索引什么是非聚簇索引 ?

聚簇索引

在使用InnoDB存储引擎的时候, 主键索引B+树叶子节点会存储数据行记录，简单来说数据和索引在一起存储 , 这就是聚簇索引。

非聚簇索引

在使用MyISAM存储引擎的时候, B+树叶子节点只会存储数据行的指针，简单来说数据和索引不在一起 , 这就是非聚簇索引。

什么情况下索引会失效 ?

MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，编写合理化的SQL能够提高SQL的执行效率

1. 不要在列上使用函数和进行运算

2. 不要在列上使用函数，这将导致索引失效而进行全表扫描。

3. 尽量避免使用 != 或 not in或 <> 等否定操作符

4. 尽量避免使用 or 来连接条件

5. 多个单列索引并不是最佳选择，建立组合索引代替多个单列索引, 可以避免回表查询

6. 查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找

7. 索引不会包含有NULL值的列

8. 当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。

9. like 语句的索引失效问题 like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询

需要创建索引情况

1. 主键自动建立主键索引

2. 频繁作为查询条件的字段应该创建索引

3. 多表关联查询中，关联字段应该创建索引 (on 两边都要创建索引)

4. 查询中排序的字段，应该创建索引

5. 频繁查找字段 , 应该创建索引

6. 查询中统计或者分组字段，应该创建索引

不要创建索引情况

1. 表记录太少

2. 经常进⾏行行增删改操作的表

3. 频繁更新的字段

4. where条件里使用频率不高的字段