关系型数据库mysql(3)索引

目录

一.索引的概念

二.索引的作用 

三.创建索引的原则依据

四.索引的分类  

五.索引的创建

5.1 普通索引

5.1.1 直接创建索引

5.1.2 修改表方式创建

5.1.3 创建表的时候指定索引 

 5.2 唯一索引

5.2.1 直接创建唯一索引

5.2.2 修改表方式创建 

5.2.3 创建表的时候指定 

5.3 主键索引

5.3.1 创建表的时候指定 

5.3.2 修改表方式创建 

5.4 组合索引(单列索引与多列索引) 

​编辑5.5 全文索引(FULLTEXT)

5.5.1 直接创建索引 

 5.5.2 修改表方式创建

5.5.3 创建表的时候指定索引

5.5.4 使用全文索引查询

六.查看索引 

七.删除索引 

7.1直接删除索引

7.2修改表方式删除索引

7.3删除主键索引

 


一.索引的概念

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

二.索引的作用 

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

索引的副作用:
●索引需要占用额外的磁盘空间。
对于 MyISAM 引擎而言,索引文件和数据文件是分离的,索引文件用于保存数据记录的地址。而 InnoDB 引擎的表数据文件本身就是索引文件。
●在插入和修改数据时要花费更多的时间,因为索引也要随之变动。 

三.创建索引的原则依据

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

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

select name,score from ky19 where id=1 

  • 索引应该建在选择性高的字段上。
  • 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引。

MySQL 的优化 哪些字段/场景适合创建索引
1、小字段
2、唯一性强的字段
3、更新不频繁,但查询率很高的字段
4、表记录超过300+行
5、主键、外键、唯一键 

四.索引的分类  

从逻辑划分来看,索引的运用主要有五种,分别为:普通索引,唯一索引,主键索引,组合索引和全文索引(模糊索引)

  • 普通索引(Basic Index):没有特殊限制,单纯为了加速查询速度。
  • 唯一索引(Unique Index):索引的键值必须唯一,除了加速查询外还能保证数据的唯一性。
  • 主键索引(Primary Key Index):特殊的唯一索引,每个表只能有一个,不允许为空,并决定了聚集索引的顺序。
  • 组合索引(单列索引与多列索引)
  • 全文索引(Full-text Index):针对文本列的特殊索引,用于对文本内容进行全文搜索。

五.索引的创建

先创建一个数据表,供下面实验

模板:
mysql -uroot -p123456;create database itest;
use test;
create table student(id int(10),name varchar(10) not null,cardid varchar(18) not null,phone varchar(11),address varchar(50),remark text);
desc student;insert into student values(1,'zhangsan','123','111111','nanjing','this is vip');
insert into student values(2,'wangwu','12345','222222','benjing','this is normal'); 
insert into student values(3,'qianqi','1234567','333333','shanghai','this is vip');
insert into student values(4,'lisi','1234','444444','shanghai','this is vip');
insert into student values(5,'limei','123456','555555','huaian','this is vip');select * from student;

5.1 普通索引

  • 最基本的索引类型,没有唯一性之类的限制。
5.1.1 直接创建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);#(列名(length)):length是可选项。
#如果忽略 length 的值,则使用整个列的值作为索引。
#如果指定使用列前的 length 个字符来创建索引,这样有利于减小索引文件的大小。#索引名建议以“_index”结尾。示例:
create index phone_index on student (phone);
select phone from student;
show create table student;

 

5.1.2 修改表方式创建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);例:
alter table student add index id_index (id);
select id from student;
select id,name from student;

 

5.1.3 创建表的时候指定索引 
CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));例:create table test1(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));
show create table test1;

 5.2 唯一索引

  • 与普通索引类似,但区别是唯一索引列的每个值都唯一。
  • 唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建,则列值的组合必须唯一。
  • 添加唯一键将自动创建唯一索引。
5.2.1 直接创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);例:UNIQUE
create unique index address_index on student (address);--------
create unique index name_index on student (name);                        
show create table student;

5.2.2 修改表方式创建 
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);例:alter table student add unique cardid_index (cardid);

 

5.2.3 创建表的时候指定 
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));例:
create table test2 (id int,name varchar(20),unique id_index (id));
show creat table test2;

5.3 主键索引

  • 是一种特殊的唯一索引,必须指定为“PRIMARY KEY”。
  • 一个表只能有一个主键,不允许有空值。 添加主键将自动创建主键索引。
5.3.1 创建表的时候指定 
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));例:
create table test01 (id int primary key,name varchar(20));
create table test02 (id int,name varchar(20),primary key (id));

5.3.2 修改表方式创建 
ALTER TABLE 表名 ADD PRIMARY KEY (列名); 

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 amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table amd1;
insert into amd1 values(1,'zhangsan','123123');
insert into amd1 values(2,'lisi','255255');
select id,name from amd1;            #会触发组合索引
而:
select name,id from amd1;            #按照索引从左到右检索的顺序,则不会触发组合索引

5.5 全文索引(FULLTEXT)

  • 适合在进行模糊查询的时候使用,可用于在一篇文章中检索文本信息。
  • 在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎,在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。
5.5.1 直接创建索引 
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);例:select * from member;
create fulltext index remark_index on member (remark);

 5.5.2 修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
5.5.3 创建表的时候指定索引
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名)); #数据类型可以为 CHAR、VARCHAR 或者 TEXT
5.5.4 使用全文索引查询
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');例:
select * from member where match(remark) against('this is vip');
or select * from member where remark='this is vip';

六.查看索引 

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 备注

七.删除索引 

7.1直接删除索引

DROP INDEX 索引名 ON 表名;例:drop index name_index on student;

7.2修改表方式删除索引

ALTER TABLE 表名 DROP INDEX 索引名;例:alter table student drop index id_index;
show index from student;

 

7.3删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;
 

 

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/282004.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

ThreaTrace复现记录

1. 环境配置 服务器环境 需要10.2的cuda版本 conda环境 包的版本: python 3.6.13 pytorch 1.9.1 torch-cluster 1.5.9 torch-scatter 2.0.9 torch-sparse 0.6.12 torch-spline-conv 1.2.1 torch-geometric 1.4.3 环境bug 这里环境搭建好以后,就可以正…

osgEarth学习笔记2-第一个Osg QT程序

原文链接 上个帖子介绍了osgEarth开发环境的安装。本帖介绍我的第一个Osg QT程序。 下载 https://github.com/openscenegraph/osgQt 解压,建立build目录。 使用Cmake-GUI Configure 根据需要选择win32或者x64,这里我使用win32. 可以看到include和lib路…

jQuery 基础

文章目录 1. jQuery 概述1.1 JavaScript 库1.2 jQuery 概念1.3 jQuery 优点 2. jQuery 基本使用2.1 下载2.2 使用步骤2.3 jQuery 的入口函数2.4 jQuery 的顶级对象 $2.5 DOM 对象和 jQuery 对象DOM 对象和 jQuery 对象相互转换方法 1. jQuery 概述 1.1 JavaScript 库 1.2 jQue…

Cesium:按行列绘制3DTiles的等分线

作者:CSDN @ _乐多_ 本文将介绍如何使用 Cesium 引擎根据模型的中心坐标,半轴信息,绘制 3DTiles 对象的外包盒等分线。 外包盒是一个定向包围盒(Oriented Bounding Box),它由一个中心点(center)和一个包含半轴(halfAxes)组成。半轴由一个3x3的矩阵表示,这个矩阵是…

【毕设级项目】基于ESP8266的家庭灯光与火情智能监测系统——文末源码及PPT

目录 系统介绍 硬件配置 硬件连接图 系统分析与总体设计 系统硬件设计 ESP8266 WIFI开发板 人体红外传感器模块 光敏电阻传感器模块 火焰传感器模块 可燃气体传感器模块 温湿度传感器模块 OLED显示屏模块 系统软件设计 温湿度检测模块 报警模块 OLED显示模块 …

DashVector - 阿里云向量检索服务

DashVector 文章目录 DashVector一、关于 DashVector二、使用 DashVector 前提准备1、创建Cluster:2、获得API-KEY3、安装最新版SDK 三、快速使用 DashVector1. 创建Client2. 创建Collection3、插入Doc4、相似性检索5、删除Doc6. 查看Collection统计信息7. 删除Coll…

hcia datacom课程学习(3):http与https、FTP

1.超文本传输协议:http与https (1)用来访问www万维网。 wwwhttp+html+URLweb (2)它们提供了一种发布和接受html界面的方法:当在网页输入URL后,从服务器获取html文件来…

供应链投毒预警 | 恶意Py组件tohoku-tus-iot-automation开展窃密木马投毒攻击

概述 上周(2024年3月6号),悬镜供应链安全情报中心在Pypi官方仓库(https://pypi.org/)中捕获1起新的Py包投毒事件,Python组件tohoku-tus-iot-automation 从3月6号开始连续发布6个不同版本恶意包&#xff0c…

【nfs报错】rpc mount export: RPC: Unable to receive; errno = No route to host

NFS错误 问题现象解决方法 写在前面 这两天搭建几台服务器,需要使用nfs服务,于是六台选其一做服务端,其余做客户端,搭建过程写在centos7离线搭建NFS共享文件,但是访问共享时出现报错:rpc mount export: RPC…

嵌入式-4种经典继电器驱动电路-单片机IO端口/三极管/达林顿管/嵌套连接

文章目录 一:继电器原理二:单片机驱动电路三:经典继电器驱动电路方案3.1 继电器驱动电路方案一:I/O端口灌电流方式的直接连接3.1.1 方案一的继电器特性要求3.1.2 方案一可能会损坏I/O口 3.2 继电器驱动电路方案二:三极…

el-table树形数据序号排序处理

1&#xff0c;用下面这个代码可以实现基本表格的序号排序 <el-table-column label"序号" width"50px" align"center"><template slot-scope"scope">{{ scope.$index 1 }}</template></el-table-column>2&…

5G安全技术新突破!亚信安全5G安全迅龙引擎正式发布

5G专网应用飞速增长&#xff1a;2020年5G专网数量800个&#xff0c;2021年2300个&#xff0c;2022年5325个&#xff0c;2023年已经超过16000个&#xff0c;5G与垂直行业的融合快速加深&#xff0c;5G带来的变革正加速渗透至各行各业。 5G网络出现安全问题&#xff0c;将是异常严…

idea2023 运行多 springboot 实例

概要 1、修改idea运行多实例&#xff08;本地测试负载&#xff09; 你可能用到其他 1、改造项目缓存token 至redis 支持负载均衡部署 SpringSecurity6.0RedisJWTMP基于token认证功能开发&#xff08;源码级剖析可用于实际生产项目&#xff09;_springsecurity redis管理token…

Odoo17免费开源ERP开发技巧:如何在表单视图中调用JS类

文/Odoo亚太金牌服务开源智造 老杨 在Odoo最新V17新版中&#xff0c;其突出功能之一是能够构建个性化视图&#xff0c;允许用户以独特的方式与数据互动。本文深入探讨了如何使用 JavaScript 类来呈现表单视图来创建自定义视图。通过学习本教程&#xff0c;你将获得关于开发Odo…

雷池 WAF 社区版:下一代 Web 应用防火墙的革新

黑客的挑战 智能语义分析算法&#xff1a; 黑客们常利用复杂技术进行攻击&#xff0c;但雷池社区版的智能语义分析算法能深入解析攻击本质&#xff0c;即使是最复杂的攻击手法也难以逃脱。 0day攻击防御&#xff1a; 传统防火墙难以防御未知攻击&#xff0c;但雷池社区版能有效…

创建一个electron-vite项目

前置条件&#xff1a;非常重要&#xff01;&#xff01;&#xff01; npm: npm create quick-start/electronlatest yarn: yarn create quick-start/electron 然后进入目录&#xff0c;下载包文件&#xff0c;运行项目 到以上步骤&#xff0c;你已经成功运行起来一个 electr…

迷茫了!去大厂还是创业?

大家好&#xff0c;我是麦叔&#xff0c;最近我创建了一个 学习圈子 有球友在 星球 里提问。 大厂的layout岗位和小厂的硬件工程师岗位&#xff0c;该如何选择&#xff1f; 这个问题我曾经也纠结过&#xff0c;不过现在的我&#xff0c;I am awake&#xff01; 肯定是有大点大。…

【研发日记】Matlab/Simulink技能解锁(四)——在Simulink Debugger窗口调试

文章目录 前言 Block断点 分解Block步进 Watch Data Value 分析和应用 总结 前言 见《【研发日记】Matlab/Simulink技能解锁(一)——在Simulink编辑窗口Debug》 见《【研发日记】Matlab/Simulink技能解锁(二)——在Function编辑窗口Debug》 见《【研发日记】Matlab/Simul…

运用YOLOv5实时监测并预警行人社交距离违规情况

YOLO&#xff08;You Only Look Once&#xff09;作为一种先进的实时物体检测算法&#xff0c;在全球范围内因其高效的实时性能和较高的检测精度受到广泛关注。近年来&#xff0c;随着新冠疫情对社交距离管控的重要性日益凸显&#xff0c;研究人员开始将YOLO算法应用于社交距离…

Jenkins流水线将制品发布到Nexus存储库

1、安装jenkins&#xff08;建议别用docker安装&#xff0c;坑太多&#xff09; docker run -d -p 8089:8080 -p 10241:50000 -v /var/jenkins_workspace:/var/jenkins_home -v /etc/localtime:/etc/localtime --name my_jenkins --userroot jenkins/jenkins:2.449 坑1 打开x…