mysql数据库的索引管理

目录

一、索引的概述

1、索引的概念

2、索引的作用 

3、索引的副作用

4、创建索引的原则依据

5、索引优化

6、索引的分类

7、数据文件与索引文件

二、管理数据库索引

1、查询索引

2、创建索引

2.1 创建普通索引

2.2 创建唯一索引

2.3 创建主键索引

2.4 创建组合索引

2.5 创建全文索引

3、删除索引

3.1 直接删除索引

3.2 修改表格式方式删除索引

3.3 删除主键索引

三、总结

1、索引类型

2、索引创建的三大方法

---

一、索引的概述

1、索引的概念

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

2、索引的作用 

• 加快数据检索速度：通过索引，数据库系统可以更快地定位到需要的数据，而不必扫描整个表能够大大加快查询速率。当表很大或查询涉及到多个表时，可以成干上万倍地提高查询速度
• 加速数据排序：索引可以帮助数据库系统快速排序数据，例如在ORDER BY子句中使用索引可以提高排序的效率
• 优化连接操作：对连接操作进行优化，特别是在多表连接时，索引可以显著提升查询性能
• 约束唯一性：可以使用索引来确保某些列或列组的数值在表中是唯一的，这种约束可以通过UNIQUE索引或主键索引来实现
• 降低数据库的IO成本：当没有索引可用时，数据库可能需要进行全表扫描以找到匹配的数据。而有了索引，数据库可以避免或减少全表扫描的情况，大大减少了IO操作的次数和数据量

 3、索引的副作用

• 占用存储空间：索引需要额外的存储空间。对于大型表来说，索引可能会占据相当可观的存储空间

• 降低写操作性能：当进行插入、更新和删除等写操作时，索引也需要进行维护，这可能导致写操作的性能下降。特别是对于频繁更新的列，索引维护成本较高

• 增加维护成本：随着数据的变化，索引的效率也会发生变化。因此，需要定期对索引进行优化和重建，这增加了维护成本

• 过多索引影响性能：如果表上存在过多或不必要的索引，会增加查询优化器的选择路径，可能导致性能下降

• 可能引起锁问题：在某些情况下，索引可能会引发锁问题，尤其是在并发环境中，需要谨慎处理索引以避免锁竞争

• 统计信息不准确：有时候索引的统计信息可能不准确，导致查询优化器做出不恰当的执行计划，从而影响性能

4、创建索引的原则依据

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

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

5、索引优化

• 经常用于查询条件的列：对于经常出现在 WHERE 子句中的列，特别是用作连接条件的列，创建索引可以提高查询性能

• 频繁被用来排序的列：如果某些列经常用于排序操作（例如在ORDER BY子句中），为这些列创建索引可以加快排序操作的速度

• 用作连接条件的列：在多表连接时，连接条件的列应该建立索引，以提高连接操作的效率

• 唯一性约束列：对于需要唯一性约束的列，如主键或UNIQUE约束的列，应当创建唯一索引

• 频繁被用于聚合函数的列：如果某些列经常用于聚合函数（如SUM、AVG等），为这些列创建索引可以提高聚合查询的性能

6、索引的分类

• B-tree索引：这是最常见的索引类型，适用于各种数据类型。B-tree索引通过对索引列的值进行排序，构建一个类似于树形结构的索引，从而加快数据的检索速度

• 哈希索引：哈希索引基于哈希算法构建，适用于等值查询，例如使用=或IN操作符的查询。相比B-tree索引，哈希索引在等值查询时具有更好的性能，但不支持范围查询和排序操作

• 全文索引：用于全文搜索的场景，例如对文本内容进行搜索。全文索引可以实现对文本内容的关键词搜索，并支持模糊匹配等操作

• 空间索引：适用于地理空间数据类型，如Point、LineString、Polygon等。空间索引可以加速地理位置相关的查询，例如查找某个区域内的所有点

• 组合索引：即将多个列组合起来创建的索引，可以同时提高多个列的查询效率，特别是在涉及多列的查询条件时

• 唯一索引：确保索引列的数值在表中是唯一的，通常用于约束某些列或列组的唯一性

• 主键索引：主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行数据

以上这些索引类型可以根据不同的场景和需求选择合适的索引策略，以提高数据库的查询性能

7、数据文件与索引文件

MySQL数据库的数据文件存放在/usr/local/mysql/data目录下，每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为三个文件，扩展名分别为“.frm”、“.MYD”和“.MYI”

“.MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起。

“.MYI”文件也是专属于 MyISAM 存储引擎的，主要存放 MyISAM 表的索引相关信息。对于 MyISAM 存储来说，可以被 cache 的内容主要就是来源于“.MYI”文件中。每一个MyISAM 表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。

MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件
（frm,myd,myi）。 每个表都有且仅有这样三个文件做为 MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个“.MYI”文件中。

还有“.ibd”和ibdata文件，这两种文件都是用来存放 Innodb 数据的，之所以有两种文件来存放 Innodb 的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储 方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和 MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用 ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件

二、管理数据库索引

1、查询索引

方法一：show create table 表名;方法二：
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	如果列只是被部分地编入索引，则为被编入索引的字符的数目(zhangsan)。如果整列被编入索引，则为 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 dianzi (score);
create index name_index on dianzi (name);

#使用索引实现查询
select score from dianzi;
select name from dianzi;
#查看索引信息
show create table dianzi;

方法二：修改表结构方式添加普通索引

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

alter table dianzi add index index_id (id);

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

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

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

#插入点数据，为普通索引查询
insert into tongxin values(1,'cxz',13938592221,14892283,'江苏南京','组长');
insert into tongxin values(2,'shd',29410875347,89422959,'江苏无锡','成员');
insert into tongxin values(3,'djs',14870235055,50964966,'江苏徐州','成员');
insert into tongxin values(4,'fhy',2950435883,98503856,'浙江杭州','组长');
insert into tongxin values(5,'tss',22857035003,285973503,'浙江宁波','成员');
insert into tongxin values(6,'xhd',24289592847,284973503,'浙江绍兴','成员');

注：

• 性能影响：虽然索引可以显著提高查询速度，但它们也会占用额外的磁盘空间，并可能降低插入、删除和更新数据的速度，因为数据库需要同时更新索引
• 选择索引列：选择哪些列作为索引应该基于查询的需要。频繁作为查询条件的列或经常出现在join、where、order by等子句中的列是创建索引的好候选
• 索引管理：随着时间的推移，表结构和查询模式可能会变化，因此定期审查和调整索引是很重要的，以确保数据库的性能最优化

2.2 创建唯一索引

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

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

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

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

create unique index index_name on tongxin (name);

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

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

alter table tongxin add unique index_phone (phone);

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

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

create table jisj (id int(3) not null,name varchar(10),cardid char(15),unique index_cardid (cardid));
#插入点数据，方便索引查询
insert into jisj values(1,'cxz',29857012035);
insert into jisj values(2,'xsf',29857012035);
insert into jisj values(2,'xsf',32894385901);
insert into jisj values(3,'sdh',29496291384);

方法四：创建表时添加唯一键，会自动创建唯一索引

create table wulian (id int(3) not null,name varchar(10) unique key,cardid char(15));
#unique key：表此字段唯一键约束，此字段数据不可重复，一个表中可有多个唯一键

#插入点数据，方便唯一索引查询
insert into wulian values(1,'cxz',29857012035);
insert into wulian values(2,'xsf',32894385901);
insert into wulian values(3,'sdh',29496291384);

注：

• 空值处理：唯一索引允许列中存在空值（NULL），但是如果索引是多列的，每列的组合必须是唯一的。对于唯一索引，MySQL视多个NULL值为不同的值，因此允许多个NULL值存在于唯一索引列中
• 性能影响：虽然唯一索引可以提高查询性能并保证数据的唯一性，但它也可能增加插入和更新操作的开销，因为每次这些操作发生时，MySQL都需要检查唯一性约束
• 使用场景：唯一索引非常适合用于需要强制数据唯一性的场景，如用户注册邮箱、身份证号码等

2.3 创建主键索引

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

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

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

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

create table class (id int(3) not null,name varchar(10) not null,cardid char(11) not null,primary key(id));

#插入数据，方便使用主键索引查询
insert into class values(1,'sjg',9432005023);
insert into class values(2,'sfh',2389420509);
insert into class values(2,'dkg',49848658802);
insert into class values(3,'jkd',32840437588);

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

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

alter table class add primary key (cardid);
alter table wulian add primary key (id);

注：

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

2.4 创建组合索引

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

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

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

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

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

#插入点数据，为组合索引查询
insert into bak values(1,'cxz',13938592221,14892283,'江苏南京','组长');
insert into bak values(2,'shd',29410875347,89422959,'江苏无锡','成员');
insert into bak values(3,'djs',14870235055,50964966,'江苏徐州','成员');

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

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

alter table wulian add index index_wulian (id,name);

注：

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

2.5 创建全文索引

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

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

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

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

create fulltext index index_address on tongxin (address);

select * from tongxin where address='浙江杭州';   #全文索引查询关键字“浙江杭州”

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

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

alter table bak add fulltext index_remark (remark);

 select * from bak where remark='组长';   #使用全文索引搜索，其中关键字是“组长”

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

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

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

#插入点数据，为全文索引查询
insert into zhizao values(1,'cxz',13938592221,14892283,'江苏南京','组长');
insert into zhizao values(2,'shd',29410875347,89422959,'江苏无锡','成员');
insert into zhizao values(3,'djs',14870235055,50964966,'江苏盐城','成员');

select * from zhizao where address='江苏南京';

注：

• 存储引擎限制：全文索引最初只支持MyISAM存储引擎。从MySQL 5.6版本开始，InnoDB存储引擎也支持全文索引
• 性能考虑：虽然全文索引可以显著提高文本搜索的速度，但它也会占用额外的磁盘空间，并可能增加文本数据更新操作的开销
• 查询语法：使用全文索引时，可以利用match() ... against()语法进行搜索查询，这允许执行包含布尔文本搜索和自然语言搜索的复杂查询

3、删除索引

3.1 直接删除索引

drop index 索引名 on 表名;

drop index index_address on zhizao;

3.2 修改表格式方式删除索引

alter table 表名 drop index 索引名;

alter table bak drop index index_bak;
alter table bak drop index index_remark;

3.3 删除主键索引

alter table 表名 drop primary key;

alter table class drop primary key;

三、总结

1、索引类型

索引名称	键标识	说明	一张表索引数量
普通索引	key	针对所有字段，没有特殊的需求/规则	可多个
唯一（键）索引	unique key	针对唯一性的字段，仅允许出现一次空值	可多个
主键索引	primary key	针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引	一个
组合索引	key	多列/多字段组合形式的索引	可多个
全文索引	fulltext key	（varchar char text）全文搜索内容	可多个

2、索引创建的三大方法

①直接创建索引

索引名称	格式
普通索引	create index 索引名 on 表名 (列名);
唯一（键）索引	create unique index 索引名 on 表名(列名);
主键索引	无
组合索引	无
全文索引	create fulltext index 索引名 on 表名 (列名);

②alter修改表结构的时候，进行add添加索引

索引名称	格式
普通索引	alter table 表名 add index 索引名 (列名);
唯一（键）索引	alter table 表名 add unique 索引名 (列名);
主键索引	alter table 表名 add primary key (列名);
组合索引	alter table 表名 add index 索引名 (字段1,字段2,字段3,……);
全文索引	alter table 表名 add fulltext 索引名 (列名);

③在创建表的时候，直接指定创建索引

索引名称	格式
普通索引	create table 表名 (字段1 数据类型,字段2 数据类型,...,index 索引名 (字段));
唯一（键）索引	create table 表名 (字段1 数据类型,字段2 数据类型,...,unique 索引名 (列名));
主键索引	create table 表名 (字段1 数据类型,字段2 数据类型,...,primary key (列名));
组合索引	create table 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,……,index 索引名 (列名1,列名2,列名3,……));
全文索引	create table 表名 (字段1 数据类型,字段2 数据类型,……,fulltext 索引名 (列名));