MySQL索引
文章目录
概述
索引可以理解为是一种
有序的数据结构
,它允许数据库系统快速的找到表中的特定行。类似与字典或者书籍的目录,可以快速的定位到需要的信息。
为什么要使用索引?
目的,当然是为了提供查询性能,避免扫描整个表,减少了磁盘的IO操作。
MySQL有那些索引呢?
- 单列索引:只有一个索引列,一个数据表可以有多个单列索引。
- 普通索引:允许数据重复,且允许NULL值。
- 主键索引:特殊的一种索引,不允许数据重复,且不允许NULL值。
- 唯一索引:不允许数据重复,但允许NULL值。
- 前缀索引:用于存储字符串前几位的索引,相对于普通索引空间占用更小,但有可能查询会稍慢。
- 多列索引(复合索引):多列组合成一个索引,只有遵守**
最左前缀原则
**的时候才能使该索引生效。 - 全文索引:适用于对文本进行全文搜索,可以在大量文本数据上提供高效的搜索功能。
- 空间索引:没用过😂,只知道使用
SPATIAL
创建。
操作索引
语法1
在CREATE TABLE
中创建索引
CREATE TABLE table_name(
column1 primary key, -- primary key 添加主键索引
column2,
column3,
column4
[,[FULLTEXT|UNIQUE|SPATIAL] index (column_name,...) [index_type],...]
)
index_type:
USING {BTREE | HASH}
示例
CREATE TABLE Index_Table(
id bigint primary key, -- 1.id为表的主键
code VARCHAR(50),
name VARCHAR(50),
sex VARCHAR(50),
index code_index(code), -- 2.增加一个普通索引,索引列为 code 名称为 code_index
unique index name_sex_unique(name,sex) USING BTREE -- 3.增加一个多列唯一索引,索引列为(name,sex)索引名为 name_sex_uniqu。使用B+树的索引类型
)
语法2
使用ALTER TABLE
创建索引,删除索引
-- 添加索引
ALTER TABLE table_name ADD [FULLTEXT|UNIQUE|SPATIAL] INDEX index_name (column1, column2, ...) [index_type];
-- 删除索引
ALTER TABLE table_name drop INDEX index_name;
index_type:
USING {BTREE | HASH}
示例
-- 为Index_Table添加一个全文索引 索引名FULLTEXT_SEX_INDEX,索引列为sex
alter table Index_Table ADD FULLTEXT INDEX FULLTEXT_SEX_INDEX(sex);
-- 删除Index_Table表的name_sex_unique索引
ALTER TABLE Index_Table DROP INDEX name_sex_unique;
语法3
使用CREATE INDEX
创建索引
CREATE [FULLTEXT|UNIQUE|SPATIAL] INDEX index_name ON table_name (column1[,...]) [index_type] ;
index_type:
USING {BTREE | HASH}
示例
--- 在Index_Table上创建一个唯一索引 索引名为 name_unique 索引列为 name
CREATE UNIQUE INDEX name_unique on Index_Table USING BTREE;
索引的数据结构
-
Hash:
-
- 类似hashmap中的hash表
- 根据索引的key进行hash运算,然后定位到对应数据存储的位置。
- 插入效率很低,并且不支持范围查询,因为key对应的位置是hash运算后的位置,这个跟顺序无关,所以不支持范围查询。
-
BTree(默认):
-
- B+树
- 相邻的两个叶子节点,以及头尾节点是可以互相访问的就类型与双端队列一样。
- 因为索引是从左到右递增的,很好的适配的范围查询。
-
InnoDB下索引的分类
如图:InnoDB默认索引的数据结构是一颗B+树只有叶子节点中才会存储对应的数据。非叶子节点则是索引的值。聚集索引
在InnoDB中必须要有聚集索引,且只能有一个。
索引与数据是存放在一起的:在聚集索引中,叶子节点存储的数据是当前行的地址。(可以通过这个地址快速的读取到其他列的数据)。
查询数据的过程(当一个查询需要查到除索引值之外的数据):通过聚集索引定位到数据后,可以通过当前行的地址(索引存储的数据)快速取到其他数据。
为什么MySQL推荐InnoDB表都需要有一个主键?
一个表中的聚集索引的选择是需要遵守一个规则的.
- 表中定义了主键,那么主键就会成为聚集索引。
- 为什么主键成为聚集索引,因为主键可以很好的保证唯一性与非空性。
- 没用定义主键,则会选择一个唯一索引的列当作聚集索引列。
- 也没用唯一索引列的话,则InnoDB会生成一个隐藏的聚集索引。它的唯一性由 InnoDB 自动生成的内部标识符来保证。
通常来说我们经常需要通过主键去查询,但是如果这个主键是隐藏的,那么就不太友好了。所有MySQL推荐InnoDB表中必须有一个主键,这个主键直接成为聚集索引。
非聚集索引(二级索引)
可以没有,也可以存在多个。
索引与数据是分开的:该索引中存储的数据是当前行主键索引的值。
查询数据的过程(当一个查询需要查到除索引值之外的数据):通过非聚集索引定位到数据后,拿到当前索引存储的值(聚集索引的值),再通过这个聚集索引去表中定位到是那一行,再去取其他列的数据。(这个操作也称之为回表)。
回表
回表
是指在使用索引进行查询时,数据库发现它不能直接从索引中获取所有需要的信息,于是它需要返回到表中去检索其他的数据。
回表
会产生额外的 IO 操作,因此在设计索引时,可以考虑创建覆盖索引
来减少回表的发生,特别是在需要查询的列上创建索引。
覆盖索引
当一次查询只用到了索引列,或者联合索引中用到的字段时,则无需再次回表(即访问实际的数据行)。
正确的使用覆盖索引可以提交查询性能,因为它可以减少回表操作,从而减少IO操作和内存消耗。
最左前缀原则
最左前缀原则指:在使用复合索引的时候,必须使用该复合索引最左侧的索引列。否则会导致索引失效。
例:有一个包含这(col1,col2,col3)的复合索引
- 查询时同时这三个条件的时候索引生效。
- 查询时使用col1,col2 或者 col1,col3索引生效。
- 其他不包含col1这个最左侧列的查询时,均会导致索引失效。
只在复合索引中有这个原则。
为什么索引可以提升查询性能?
- 减少了数据检索的范围:通过在索引列上创建索引,数据库引擎可以直接跳转到索引中符合查询条件的位置,而不需要扫描整个表。这样可以显著减少需要检查的数据量。
- 减少了磁盘IO操作:索引使得数据库引擎能够更快地定位到需要的数据,从而减少了需要读取磁盘上的数据块的次数。这减少了IO操作,提高了查询性能。
- 提供了有序的数据访问路径:索引可以按照特定的顺序组织数据,这使得在某些情况下可以避免对整个表进行排序操作,从而减少了查询的耗时。
- 充当覆盖索引:在某些情况下,如果索引包含了所有查询所需的列,可以避免回表(访问实际的数据行),从而提高了查询性能。
需要注意的是,虽然索引可以显著提升查询性能,但过多或不合适的索引可能会导致性能下降,因此在设计索引时需要根据实际需求和查询模式进行合理的选择和优化。
最佳实践
- 选择合适的列:
- 选择经常用于查询的列,尤其是在
WHERE
、JOIN
、ORDER BY
和GROUP BY
子句中使用的列。 - 避免在很少用于查询的列上建立索引,因为它会增加索引维护的开销。
- 选择唯一性高的列:
- 对于具有高基数(唯一性高)的列,索引的效果通常更好。
- 如果一列数据中大多数都是相同的数据建立索引的意义不大。可以想象一颗B+树的叶子节点全是同样的索引值,通过
二分查询
,他的查询效率会多么“高”😀。
- 考虑多列索引:
- 当多个列一起经常用于查询时,可以考虑创建一个包含这些列的复合索引,这样可以提高查询性能。
- 避免过度索引:
- 避免在表中创建过多的索引,因为过多的索引会增加维护的成本,降低写操作的性能。
- 了解查询模式:
- 了解实际的查询模式和业务需求,根据具体情况来选择建立索引。
- 避免在小表上建立索引:
- 对于小型表,全表扫描可能比使用索引更快,因此在这种情况下可能不需要索引。
- 注意组合索引顺序:
- 对于复合索引,索引列的顺序很重要。应该将最常用于查询条件的列放在前面。
- 使用工具进行性能分析:
- 使用数据库性能分析工具(如
EXPLAIN
语句)来检查查询的执行计划,从而确定是否需要建立新的索引或者调整现有的索引。
- 考虑特定数据库引擎的特性:
- 不同的数据库引擎可能对索引的使用有不同的策略,因此需要根据实际使用的引擎进行相应的优化。
需要注意的是,虽然索引可以提升查询性能,但是过多不适合的索引可能会导致性能下降。
我通常直接就是找到对应操作表的Service,看看那些条件用的多,或者某一组条件用的多,直接加上索引。👍