MySQL——索引管理

最新推荐文章于 2024-09-05 14:17:33 发布

不喜欢就睡大觉

最新推荐文章于 2024-09-05 14:17:33 发布

阅读量796

点赞数 30

文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/A6985HG/article/details/141719863

版权

一、索引的概述

1、索引的概念

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。
使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
索引是表中一列或者若干列值排序的方法。
建立索引的目的是加快对表中记录的查找或排序

2、索引的作用

加速查询：索引利用各种快速定位技术，如B树、哈希等，可以显著提高查询速度，尤其是在处理大型数据表或多表查询时，索引可以成千上万倍地提高查询速度。
降低IO成本：索引减少了必须从磁盘读取的数据量，从而降低了IO操作的次数和成本。
减少排序成本：在需要对数据进行排序的查询中，索引可以帮助数据库直接获取已经有序的数据，从而避免或减少排序操作。
保证数据唯一性：通过创建唯一性索引（唯一键索引），可以确保数据表中的每一行数据都是唯一的，这在维护数据完整性方面非常重要。
优化连接操作：索引可以加快表与表之间的连接操作，尤其是在使用外键进行连接时，索引可以显著提高连接查询的效率。
减少分组和排序时间：在需要对数据进行分组和排序的查询中，索引可以减少这些操作所需的时间，因为索引本身可能已经按照需要的顺序组织了数据。

3、索引的副作用

占用额外磁盘空间：索引需要存储额外的数据结构，这会占用数据库的磁盘空间。对于MyISAM引擎，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。而对于InnoDB引擎，表数据文件本身就是索引文件的一部分。
增加插入和修改时间：在插入和修改数据时，索引也需要随之更新。这会导致这些操作的时间增加，因为数据库系统不仅要更新数据记录，还要维护索引的一致性。
影响写操作性能：由于索引需要维护，频繁的插入、更新和删除操作可能会因为索引的更新而变得缓慢，尤其是在高并发的环境中。
索引维护成本：随着数据量的增加，索引的维护成本也会增加。数据库系统需要定期重建或优化索引，以保持其效率。
选择不当可能导致性能下降：如果索引选择不当，例如在不经常查询的列上创建索引，或者创建了过多的索引，可能会导致查询优化器选择不合适的索引，反而降低查询性能。

4、创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位
记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。
经常与其他表进行连接的表，在连接字段上应该建立索引。
唯一性太差的字段不适合建立索引。
更新太频繁地字段不适合创建索引。
经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
select name,score from ky19 where id=1
索引应该建在选择性高的字段上。
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

5、索引的分类

普通索引（Normal Index）：

这是最基本的索引类型，适用于所有字段，没有特殊的需求或规则。
可以创建在任何数据类型的字段上，用于加速查询，但不保证字段的唯一性。

唯一索引（Unique Index）：

针对具有唯一性要求的字段，确保字段的值在表中是唯一的。
允许出现一次空值（NULL），因为NULL在SQL标准中被认为是不同于任何其他值的特殊值。

组合索引（Composite Index 或 Multi-column Index）：

由多个字段组合而成的索引，适用于需要同时根据多个字段进行查询的场景。
组合索引的顺序很重要，查询时需要按照索引字段的顺序来使用索引，才能发挥其最大效能。

全文索引（Full-Text Index）：

专门为文本内容搜索设计的索引，适用于VARCHAR、CHAR、TEXT等文本类型的字段。
全文索引可以优化对文本内容的搜索，支持关键词查询，适用于需要进行全文检索的应用场景。

主键索引（Primary Key Index）：

针对具有唯一性且不可为空（NOT NULL）的字段，通常用于标识表中的每一条记录。
一张表中只能包含一个主键索引，主键索引隐含了唯一性和不可为空的约束。

二、索引的管理

1、查看索引

show create table cxc

show index from 表名;
show index from 表名\G；  //竖向显示表索引信息

show keys from 表名;
show keys from 表名\G;    //竖向显示表索引信息

解释：

字段名称	含义
Table	表的名称
Non_unique	如果索引内容唯一，则为 0；如果可以不唯一，则为 1。
Key_name	索引的名称。
Seq_in_index	索引中的列序号，从 1 开始。limit 2,3
Column_name	列名称。
Collation	列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。
Cardinality	索引中唯一值数目的估计值。
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
Packed	指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null	如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment	备注。

2、创建索引

2.1、创建普通索引

最基本的索引类型，没有唯一性之类的限制。

方法一：直接创建索引

create index 索引名 on 表名 (列名);

create index score_index on xiaomi (score);
create index name_index on damo (name);

方法二：修改表的方式创建

alter table 表名 add index 索引名 (列名);

alter table cxc add index index_id (id);

方法三：创建表的时候指定索引

create table jjg (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text,index index_carrdid(cardid));

2.2、创建唯一索引

唯一索引（Unique Index）在MySQL数据库中是一种确保数据列中每个值都是唯一的索引。通过创建唯一索引，你可以保证某一列或列组合中的数据值不会重复，这对于维护数据的完整性非常重要，如防止同一个电子邮件地址被注册多次

与普通索引类似，但区别是唯一索引列的每个值都唯一。
唯一索引允许有空值（注意和主键不同）。
如果是用组合索引创建，则列值的组合必须唯一。
添加唯一键将自动创建唯一索引。

方法一：直接创建唯一索引

create unique index 索引名 on 表名(列名);

create unique index name_index on AAA (name);

方法二：修改表结构方式添加唯一索引

alter table 表名 add unique 索引名 (列名);

alter table AAA add unique index score_index (score);

方法三：创建表时添加唯一索引

create table 表名 (字段1 数据类型,字段2 数据类型,...,unique 索引名 (列名));

create table BBB (id int,name char(10),score char(15),phone char(20),unique index name_index(name));

2.3、创建主键索引

主键索引是一种特殊的唯一索引，不仅确保索引列的数据唯一性，还能标识表中的每一行数据，必须指定为“primary key”。主键的特点包括：

每个表只能有一个主键
主键列不能有NULL值
主键自动创建唯一索引，确保列值的唯一性

方法一：创建表时指定主键索引

create table 表名 (字段1 数据类型,字段2 数据类型,...,primary key (列名));

create table CCC (id int,name char(10),score char(15),phone char(20),primary key(id));

方法二：修改表结构方式添加主键索引

alter table 表名 add primary key (列名);

mysql> alter table AAA add primary key(id);

注：

如果表中已经有数据，给列添加主键前需要确保该列的所有值都是唯一的，且没有NULL值。
考虑到性能和存储效率，主键通常设置为整型或UUID。
主键的选择对数据库性能有重要影响，应谨慎选择能唯一标识每行数据的列作为主键。

2.4、创建组合索引

组合索引（也称为复合索引或多列索引）是指基于表中两个或多个列创建的索引。组合索引可以提高查询性能，特别是在查询条件涉及多个列时。它按照索引中列的顺序存储数据，这使得数据库能够高效地利用索引来加速查询和排序操作

需要满足最左原则，因为select语句的 where条件是依次从左往右执行的，所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

方法一：创建表时指定组合索引

create table 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,……,index 索引名 (列名1,列名2,列名3,……));

create table DDD (id int,name char(10),score char(15),phone char(20),index index_bak(id,name,score));

方法二：修改表结构方式添加组合索引

alter table 表名 add index 索引名 (字段1,字段2,字段3,……);

alter table CCC add index index_bak(name,score,phone);

注：

索引顺序：在定义组合索引时，列的顺序非常重要。应根据查询中最常用的列来安排索引中的列顺序
性能影响：虽然索引可以提高查询性能，但过多的索引会增加插入、更新和删除操作的开销，因为数据库需要维护这些索引。因此，应根据实际需要创建索引
前导列优化：尽可能利用索引的前导列进行查询，以充分利用组合索引的优势。

2.5、创建全文索引

全文索引通过创建文本数据的索引来加速对文本的搜索查询，它会分析文本列中的内容，将文本分解成词元（tokens）或关键词，并建立一个搜索关键词的索引。这样，当进行文本搜索时，数据库可以直接利用这个索引来快速定位包含特定关键词的记录，而不是逐行扫描整个表。

全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。

方法一：直接创建全文索引

create fulltext index 索引名 on 表名 (列名);

create fulltext index score_index on BBB (score);

方法二：修改表结构方式添加全文索引

alter table 表名 add fulltext 索引名 (列名);

alter table BBB add fulltext index phone_index (phone);

方法三：创建表时指定全文索引

create table 表名 (字段1 数据类型,字段2 数据类型,……,fulltext 索引名 (列名));

create table EEE (id int,name char(10),score char(15),phone char(20),fulltext index name_index (name));