MySQL索引

目录

  • 一、索引的概述
  • 二、索引的作用
    • 2.1 索引是如何实现？
    • 2.2 使用索引的副作用
  • 三、创建索引的原则依据
  • 四、MySQL的优化哪些字段/场景适合创建索引？
  • 五、索引的分类及创建
    • 5.1 普通索引
      • 直接创建索引
      • 修改表方式创建
      • 创建表的时候指定索引
    • 5.2 唯一索引
      • 直接创建唯一索引
      • 修改表方式创建唯一索引
      • 创建表的时候指定唯一索引
    • 5.3 主键索引
      • 创建表的时候指定主键索引
      • 修改表方式创建主键索引
    • 5.4 组合索引
    • 5.5 全文索引（FULLTEXT）
      • 直接创建索引
      • 修改表方式创建
      • 创建表的时候指定索引
      • 使用全文索引进行查询
  • 六、查看索引
  • 七、删除索引
    • 7.1 直接删除索引
    • 7.2 修改表方式删除
    • 7.3 删除主键索引
  • 总结

一、索引的概述

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

二、索引的作用

  • 加快表的查询速度，可以对字段排序
  • 可以降低数据库的I0成本，并且索引还可以降低数据库的排序成本。
  • 通过创建唯一性索引， 可以保证数据表中每一行数据的唯一性。
  • 可以加快表与表之间的连接。

2.1 索引是如何实现？

没有索引的情况，要查询某行数据，需要先扫描全表来定位某行数据。
有索引后会通过查找条件的字段找到其索引对应的行数据的物理地址，然后根据物理地址访问相应的数据。

2.2 使用索引的副作用

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

三、创建索引的原则依据

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

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

四、MySQL的优化哪些字段/场景适合创建索引？

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

五、索引的分类及创建

5.1 普通索引

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

直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);
 
#(列名(length))：length是可选项。
如果忽略 length 的值，则使用整个列的值作为索引。
如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾。
 
例：create index phone_index on member (phone);
select phone from member;
show create table member;

在这里插入图片描述
在这里插入图片描述

修改表方式创建

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

在这里插入图片描述

创建表的时候指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

在这里插入图片描述

5.2 唯一索引

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

直接创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

在这里插入图片描述

修改表方式创建唯一索引

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

在这里插入图片描述

创建表的时候指定唯一索引

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

在这里插入图片描述

5.3 主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。
一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

创建表的时候指定主键索引

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));
 
例：
create table boss1 (id int primary key,name varchar(20));
create table boss2 (id int,name varchar(20),primary key (id));
 
show create table boss1;
show create table boss2;

在这里插入图片描述

在这里插入图片描述

修改表方式创建主键索引

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

在这里插入图片描述
在这里插入图片描述

5.4 组合索引

可以是单列上创建的索引，也可以是在多列上创建的索引。
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';
 
例:create table boss4 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table boss4;
insert into boss4 values(1,'zhangsan','123456');
select * from boss where name='zhangsan' and id=1;

在这里插入图片描述
在这里插入图片描述
注：组合索引创建的字段顺序是其触发索引的查询顺序

--+
| CLASS | CREATE TABLE "member" (
  "id" int(11) NOT NULL,
  "name" varchar(50) DEFAULT NULL,
  "age" int(5) DEFAULT NULL,
  KEY "index_idname" ("id","name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
对以上的表进行select 
select id,name from member;			#会触发组合索引
而：
select name,id from member;			#按照索引从左到右检索的顺序，则不会触发组合索引
 
大量数据才能展示是否出发组合索引，未触发查询速度慢。

5.5 全文索引（FULLTEXT）

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。
在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持FULLTEXT 索引。
全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。

直接创建索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);
 
例：
select * from member;
create fulltext index remark_index on member (remark);

在这里插入图片描述
在这里插入图片描述

修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

在这里插入图片描述

创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));

在这里插入图片描述

使用全文索引进行查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');
 
例：
select * from ky19 where match(remark) against('this is dlaosipi');

在这里插入图片描述

六、查看索引

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开始
Column_name列名称
Collation列以上面方式存储在索引中。在Mysql中，有值‘A’（升序）或NULL（无分类）
Cardinality索引中唯一值数目的估计值
Sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL
Packed指示关键字如何被压缩。如果没有被压缩，则为NULL
Null如果列含有NULL，则含有YES。如果没有，则该列还含有NO
Index_type用过的索引方法（BTREE,FULLTEXT,HASH,RTREE）
Comment备注

七、删除索引

7.1 直接删除索引

DROP INDEX 索引名 ON 表名;
 
例：drop index name_index on ky27;

在这里插入图片描述

7.2 修改表方式删除

ALTER TABLE 表名 DROP INDEX 索引名;
 
例：alter table ky19 drop id_index;
show index from member;

在这里插入图片描述

7.3 删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

总结

1、索引分类
① 普通索引 ：针对所有字段，没有特殊的需求/规则
② 唯一索引 : 针对唯一性的字段，仅允许出现一次空值
③ 组合索引 (多列/多字段组合形式的索引)
④ 全文索引（varchar char text）
⑤ 主键索引 ：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引

2、索引创建
① 在创建表的时候，直接指定index
② alter修改表结构的时候，进行add 添加index
③ 直接创建索引index
PS:主键索引——》直接创建主键即可

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

2023 年第三届长三角高校数学建模 A 题 快递包裹装箱优化问题

2023 年第三届长三角高校数学建模 A 题 快递包裹装箱优化问题

2022 年，中国一年的包裹已经超过 1000 亿件，占据了全球快递事务量的一 半以上。近几年，中国每年新增包裹数量相当于美国整个国家一年的包裹数量， 十年前中国还是物流成本最昂贵的国家，当前中国已经建立起全世界最强大、…
阅读更多...
Linux基础学习---2、系统管理、帮助命令、文件目录类命令

Linux基础学习---2、系统管理、帮助命令、文件目录类命令

1、系统管理 1.1 Linux中的进程和服务 计算机中，一个正在执行的程序或命令。被叫做“进程”（Process）。 启动之后一直存在、常驻内存的进程，一般称做“服务”（Service）。1.2 systemctl（CentOS…
阅读更多...
关于如何对VS的C++项目进行完全重命名

关于如何对VS的C++项目进行完全重命名

很多人一个开始在VS编写C项目的时候，第一个项目名称都是系统默认名称或者HelloWorld这类的名字，一看就比较小白。 一段时间以后，项目已经进行了一段时间了，这时候想要对项目名称进行重命名。但是，偏偏VS的重命名功能做…
阅读更多...
【笔试强训选择题】Day10.习题（错题）解析

【笔试强训选择题】Day10.习题（错题）解析

作者简介：大家好，我是未央； 博客首页：未央.303 系列专栏：笔试强训选择题 每日一句：人的一生，可以有所作为的时机只有一次，那就是现在！！！ 文章目录…
阅读更多...
React项目总结：上一步的终点，下一步的起点

React项目总结：上一步的终点，下一步的起点

项目简介 本人利用 react18.2 json-server 做了一个后台管理系统。 包含： 用户管理权限管理站内信审核管理站内信发布管理 等内容。 其中涉及到react-router V6.0的使用以及一些权限控制等内容。 更多精彩内容，请微信搜索“前端爱好者“&#xff…
阅读更多...
分享两款好用的软件

分享两款好用的软件

软件一：去水印神器——Inpaint Inpaint是一款功能强大的图像处理软件，它的主要功能是去除图片中的水印。除此之外，它还可以帮助用户修复照片中的缺陷，例如划痕、斑点、红眼等，删除照片中的不必要的元素，例…
阅读更多...
名称空间(namespaces)与作用域

名称空间(namespaces)与作用域

引入 在python解释器中运行一行代码import this就可以看到“传说”中的python之禅，它体现了使用python进行开发的规范，而最后一句 - Namespaces are one honking great idea -- lets do more of those!就是本文的主角。 名称空间(Namespaces) 名称空间…
阅读更多...
Protobuf: 高效数据传输的秘密武器

Protobuf: 高效数据传输的秘密武器

当涉及到网络通信和数据存储时，数据序列化一直都是一个重要的话题；特别是现在很多公司都在推行微服务，数据序列化更是重中之重，通常会选择使用 JSON 作为数据交换格式，且 JSON 已经成为业界的主流。但是 Google 这么大…
阅读更多...
聊聊并发编程的12种业务场景

聊聊并发编程的12种业务场景

前言 并发编程是一项非常重要的技术，无论在面试，还是工作中出现的频率非常高。 并发编程说白了就是多线程编程，但多线程一定比单线程效率更高？ 答：不一定，要看具体业务场景。 毕竟如果使用了多线程&…
阅读更多...
fbx sdk的使用介绍

fbx sdk的使用介绍

我们平时需要围绕fbx写一些小工具，虽说使用ascii格式的fbx可以直接进行字符串解析，并且网上也有一些基于ascii解析的开源库，但在制作一些通用的工具时，使用fbx sdk进行编写肯定是最好的。 1.下载fbx sdk和cmake 要用cmake生成vi…
阅读更多...
bash简单常见用法

bash简单常见用法

bash新建自定义数组 myArray() for ((i 0 ; i < 5 ; i )) do myArray[$i]"AAAA{$i}DD" done echo ${myArray[]} #输出结果是AAAA{0}DD AAAA{1}DD AAAA{2}DD AAAA{3}DD AAAA{4}DD 提取文件名成功 projects"D:/Project/Program/IDEAWorkspace/myauto/automati…
阅读更多...
Python程序员辞职后，如何踏出自由职业的第一步，聊聊我自己的看法

Python程序员辞职后，如何踏出自由职业的第一步，聊聊我自己的看法

大家好&#xff0c;我是兴哥。有个广州的朋友说他辞职了&#xff0c;想要自由职业该怎么开始第一步呢&#xff1f;我问他你之前的收入月薪是多少&#xff0c;他说2万出头。我不得不说&#xff0c;对于写项目的自由职业程序员&#xff0c;2万是一个极高的门槛。但既然他已经辞职…
阅读更多...
淘宝拍立淘多码识别方案总结

淘宝拍立淘多码识别方案总结

本文通过拆解原始问题、发散思路优化等方式&#xff0c;记录了扫一扫从单码到多码识别的技术框架改造及多码识别率优化方案。其中涉及解码SDK的能力、码处理技术链路、码转换算法、降低漏检率策略等设计与实现。 背景与挑战 多码即在同一个界面中同时存在多个条码或二维码&…
阅读更多...
Node.js 与 WebAssembly

Node.js 与 WebAssembly

目录 1、简介 2、关键概念 3、生成WebAssembly模块 4、如何使用它 5、与操作系统交互 1、简介 首先&#xff0c;让我们了解为什么WebAssembly是一个很棒的工具&#xff0c;并学会自己使用它。 WebAssembly是一种类似汇编的高性能语言&#xff0c;可以从各种语言编译&…
阅读更多...
从零开始的强化学习入门学习路线

从零开始的强化学习入门学习路线

强化学习是机器学习领域中的一个分支&#xff0c;它是指智能体通过与环境的交互来学习如何采取最佳行动以最大化奖励信号的过程。强化学习在许多领域都有广泛的应用&#xff0c;如游戏、自动驾驶和机器人控制等。如果你对强化学习感兴趣&#xff0c;下面是一个入门强化学习的学…
阅读更多...
【分布式锁】Redisson分布式锁的使用（推荐使用）

【分布式锁】Redisson分布式锁的使用（推荐使用）

文章目录 前言一、常见分布式锁方案对比二、分布式锁需满足四个条件三、什么是Redisson?官网和官方文档Redisson使用 四、Redisson 分布式重入锁用法Redisson 支持单点模式、主从模式、哨兵模式、集群模式自己先思考下,如果要手写一个分布式锁组件&#xff0c;怎么做&#xff…
阅读更多...
数据备份系列：Rsync 备份实战记录（二）

数据备份系列：Rsync 备份实战记录（二）

一、Rsync Cron 场景使用 在对数据备份要求实时性不高的情况下&#xff0c;可优先考虑该场景&#xff0c;选择一个合适的时间&#xff0c;对数据进行定时远程增量同步。 在《数据备份系列&#xff1a;Rsync 备份详解&#xff08;一&#xff09;》中我们已经对服务搭建以及远程…
阅读更多...
鸿蒙学习总结

鸿蒙学习总结

控件 button 源码所在路径&#xff0c;小编也只是猜测&#xff0c;还没搞懂鸿蒙上层app到底层的玩法&#xff0c;网上也没相关资料&#xff0c;找源码真是费劲(不是简单的下载个源码的压缩包&#xff0c;而是找到里面的控件比如Button&#xff0c;或者UIAbility实现的源码&…
阅读更多...
基于python语言dlib库和opencv库的视频眨眼检测

基于python语言dlib库和opencv库的视频眨眼检测

功能说明&#xff1a; 基于python编程语言&#xff0c;使用dlib 和opencv开发的视频眨眼检测。 环境&#xff1a; * python 3.6.8 * opencv 3.4.2.16 * dlib 19.7.0 原理&#xff1a; 1.使用opencv-python读取处理视频图像 2.使用线程机制处理人脸检测关键点 3.根…
阅读更多...
elk生命周期删除日志

elk生命周期删除日志

elk版本&#xff1a;7.14 一、简介 ELK日志我们一般都是按天存储&#xff0c;例如索引名为"prodlog-2023-05-08"&#xff0c;因为日志量所占的存储是非常大的&#xff0c;我们不能一直保存&#xff0c;而是要定期清理旧的&#xff0c;这里就以保留7天日志为例。 自…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站