MySQL索引

发布于：2024-03-22 ⋅ 阅读:(73) ⋅ 点赞:(0)

文章目录

MySQL索引

1、索引的概念

索引就是一种帮助系统更快的查找相应的数据

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

优点：快速筛选数据

缺点：需要额外的磁盘空间

2、索引的作用

  • 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因
  • 当表中数据很多或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度
  • 可以降低数据库的IO成本，并且还可以降低数据库的排序成本
  • 通过创建唯一(键)性索引，可以保证数据表中每一行数据的唯一性
  • 可以加快表与表之间的连接
  • 在使用分组和排序时，可大大减少分组和排序时间

3、索引的副作用

  • 索引需要占用额外的磁盘空间

  • 在插入和修改数据时要花费更多的时间，因为索引也要随之变动

对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。而InnoDB 引擎的表数据文件本身就是索引文件。当表很大或查询涉及到多个表时，可以成干上万倍地提高查询速度。

4、创建的原则（应用场景）

  • 表的主键、外键必须有索引

  • 记录数超过500+行的表应该有索引

  • 经常与其他表进行连接的表，在连接字段上应该建立索引

  • 经常出现在where子句中的字段，特别是大表的字段
    应该建立索引

  • 索引应该建在选择性高的字段上

  • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

  • 唯一性太差的字段不适合建立索引

  • 更新太频繁的字段不适合创建索引

适合创建索引的场景

  • 小字段
  • 唯一性强的字段
  • 更新不频繁，但查询率很高的字段
  • 表的记录超过300+行
  • 主键、外键、唯一键

5、索引的分类和创建

  • 先部署需要的数据库和数据表
mysql -uroot -p123456
#登录数据库

create database bohe;
#创建数据库

use bohe;
#切换到数据库

create table ly01(id int not null,name char(10) not null,cardid varchar(18) not null,phone varchar(11) not null,address char(60) not null,remark text);
#创建数据表

desc ly01;
#查看数据表结构

insert into ly01 values(1,'wang',111111,18245613,'nanjing','aaa');
insert into ly01 values(2,'yang',222222,17542699,'qingdao','bb');
insert into ly01 values(3,'zhao',333333,17169542,'yantai','cc');
insert into ly01 values(4,'wu',444444,18246697,'weihai','dd');
insert into ly01 values(5,'qi',555555,18145789,'lanzhou','ee');
insert into ly01 values(6,'li',666666,17356245,'yunnan','ff');
insert into ly01 values(7,'yuan',777777,1795846,'jiaxing','gg');
insert into ly01 values(8,'yi',888888,182412357,'huaihua','xx');
#表中插入记录

select * from ly01;
#查看生成的数据表

image-20240321141812095

image-20240321141925603

image-20240321143130926

image-20240321143256903

5.1 普通索引
  • 最基本的索引类型，没有唯一性之类的限制
5.1.1 直接创建索引
  • 格式
create index 索引名 on 表名(列名);

create index address_index on ly01(address);
#直接创建索引

select address from ly01;
#实现方式，使创建的索引生效

show create table ly01;
#查看生成的索引

image-20240321143552720

5.1.2 修改表方式创建索引
  • 格式
alter table 表名 add index 索引名 (列名);

alter table ly01 add index phone_index (phone);
#修改表的方式创建索引

select phone from ly01;
#实现方式，使创建的索引生效

show create table ly01;
#查看生成的索引

image-20240321143958031

5.1.3 创建表的时候指定索引
  • 格式
create table 表名(字段1 数据类型,字段2 数据类型,...,index 索引名 (列名));

create table ly02(id int(3) not null,name varchar(10) not null,cardid char(18) not null,index name_index(name));
#创建表的时候指定索引

insert into ly02 values(1,'wang',111111);
insert into ly02 values(2,'yang',222222);
insert into ly02 values(3,'zhao',333333);
#表中插入内容

select name from ly02;
#使创建的索引生效

show create table ly02;
#查看生成的索引

image-20240321145117576

5.2 唯一索引
  • 与普通索引类似，但区别是唯一索引列的每个值都唯一。
  • 唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
5.2.1 直接创建唯一索引
  • 格式
create unique index 索引名 on 表名(列名);
#直接创建唯一索引

create unique index cardid_index on ly01(cardid);
#直接创建唯一索引cardid_index

select cardid from ly01;
#实现方式，使创建的索引生效

show create table ly01;
#查看生成的索引

image-20240321145738199

5.2.2 修改表方式创建唯一索引
  • 格式
alter table 表名 add unique 索引名(列名);

alter table ly01 add unique id_index (id);
#使用修改表的方式创建唯一索引id_ index

show create table ly01;
#查看生成的索引

image-20240321151152608

5.2.3 创建表的时候指定唯一索引
  • 格式
create table 表名 (字段1 数据类型,字段2 数据类型[,...],unique 索引名 (列名));

create table ly03(id int not null,name varchar(10) not null,cardid char(18) not null,unique name_index(name));
#创建表时指定唯一索引

insert into ly03 values(1,'yi',888888);
#表中插入数据

show create table ly03;
#查看ly03表中的索引信息

image-20240321152107115

5.3 主键索引
  • 是一种特殊的唯一索引，必须指定为“primary key”。
  • 一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。
5.3.1 创建表时指定主键索引
  • 格式
create table 表名 ([...],primary key (列名));

create table ly04(id int(3),name char(10) not null,cardid varchar(18) not null,primary key (cardid));
#创建表时指定主键索引

insert into ly04 values(1,'yun',112886699);
#表中插入数据

show create table ly04;
#查看ly04表中生成的主键索引

image-20240321152801796

5.3.2 修改表的方式创建主键索引
  • 格式
alter table 表名 add primary key (列名)

alter table ly01 add primary key (address);
#使用修改表的方式创建主键索引

show create table ly01;
#查看ly01表中的索引信息

image-20240321153238613

5.4 组合索引

可以是单列上创建的索引，也可以是在多列上创建的索引。

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

组合索引创建的字段顺序是其触发索引的查询顺序

  • 格式
create table 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

create table ly05(id int(6) not null,name varchar(10) not null,cardid char(18) not null,index index_ly05(name,id,cardid));
#创建组合索引

insert into ly05 values(1,'xi',12580);
#在表ly05中插入数据

show create table ly05;
#查看ly05表中的组合索引信息

select id,name,cardid from ly05;
#该组合索引不会生效，因为select语句查询顺序是（id,name,cardid）与where语句定义顺序(name,id,cardid)不一致

image-20240321160720323

image-20240321175759552

5.5 全文索引

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。

在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。

  • 全文索引可以在 char、varchar 或者 text 类型的列上创建。每个表只允许有一个全文索引。
5.5.1 直接创建全文索引
  • 格式
create fulltext index 索引名 on 表名（列名）;

create fulltext index name_index on ly01(name);
#创建全文索引

show create table ly01;
#查看生成的全文索引

select * from ly01 where name='yang';
#查看全文索引

image-20240321161240289

5.5.2 修改表方式创建全文索引
  • 格式
alter table 表名 add fulltext 索引名 (列名);

alter table ly01 add fulltext address_index(address);
#使用修改表的方式创建全文索引

show create table ly01;
#查看ly01表中索引信息

image-20240321162815645

5.2.3 创建表的时候指定全文索引
  • 格式
create table 表名 (字段1 数据类型[,...],fulltext 索引名 (列名)); 
#创建表的时候指定全文索引

select * from 表名 where match(列名) against('查询内容');
#使用全文索引查询

create table ly06(id int not null,name varchar(8) not null,cardid char(18) not null,fulltext name_index (name));
#创建表的时候指定全文索引

insert into ly06 values(1,'qi',1122685);
#在ly06表中插入数据

show create table ly06;
#查看ly06表中的索引信息

image-20240321164315775

  • 使用全文索引查询信息
select * from ly01 where match(name) against('zhao');
#查找ly01表中name=zhao的数据

image-20240321171717649

5.6 总结
5.6.1 索引分类
  • 普通索引：针对所有字段，没有特殊的需求/规则
  • 唯一索引：针对唯一性的字段，仅允许出现一次空值
  • 组合索引：(多列/多字段组合形式的索引)
  • 全文索引：(varchar char text)MySQL为了优化对文本内容搜索的一种机制
  • 主键索引：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引
5.6.2 创建索引
  • 在创建表的时候，直接指定index
  • alter修改表结构的时候，进行add添加index
  • 直接创建索引index
  • 主键索引直接创建主键即可

6、索引的管理

6.1 查看索引

  • 格式
show index from 表名;
show index from 表名\G;
show keys from 表名;
show keys from 表名\G;
#以上都可以查看索引，加\G表示纵向显示

show create table 表名;
#查看表的信息

image-20240321164917483

image-20240321165002233

image-20240321165051702

image-20240321165121932

6.2 删除索引

6.2.1 直接删除
  • 格式
drop index 索引名 on 表名;
#直接删除索引

drop index phone_index on ly01;
#删除ly01表中的索引phone_index

drop index address_index on ly01;
#删除ly01表中的索引address_index

image-20240321150132762

6.2.2 修改表方式删除索引
  • 格式
alter table 表名 drop index 索引名;
#修改表的方式删除索引

show create table ly02;
#查看ly02表中的索引信息

alter table ly02 drop index name_index;
#使用修改表方式删除ly02表中的索引name_index

show create table ly02;
#查看ly02表中的索引信息

image-20240321150715701

image-20240321162450202

6.2.3 删除主键索引
  • 格式
alter table 表名 drop primary key;
#删除主键索引

alter table ly01 drop primary key;
#删除主键索引

show create table ly01;
#查看ly01表中的索引信息

image-20240321161726643

本文含有隐藏内容，请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布