MySql 数据库【索引】

article/2025/11/3 11:30:19

MySql 数据库【索引】

  • 1. 什么是索引？
  • 2. 索引的实现原理？
  • 3. 添加索引的条件？
  • 4. 索引的操作
    • 1. 创建索引
    • 2. 删除索引
    • 3. 查看一个sql语句是否使用了索引进行检索
  • 5. 索引的失效
  • 6. 索引的类型

1. 什么是索引？

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

索引相当于一本书的目录

通过索引查询的方式被称为索引查询。

在 mysql 数据库当索引也是需要排序的，并且这个索引的排序和 TreeSet 数据结构相同。TreeSet（TreeMap）底层是一个自平衡二叉树！在 mysql 当中索引是一个 B-Tree 数据结构。
遵循左小右大原则存放。采用中序遍历方式遍历取数据。

2. 索引的实现原理？

在这里插入图片描述

提醒：

  1. 在任何数据库中主键上都会自动添加索引对象，id 字段上自动有索引，因为 id 是主键。另外在 mysql 中，如果一个字段有 unique 约束的话，也会自动创建索引对象。
  2. 任何数据库中，然后一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
  3. 在 mysql 中，索引是一个单独的对象，在不同的存储引擎以不同的形式存在，在 MyISAM 存储引擎中，索引存储在一个 .MYI 文件中。在 InnoDB 存储引擎中，索引存储在一个逻辑名叫做 tablespace 的当中。在 MEMORY 存储引擎中，索引存储在内存中。不管索引存储在哪里，索引在 mysql 中都是一个树的形式存在。

在这里插入图片描述

3. 添加索引的条件？

  1. 数据量庞大（具体多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
  2. 该字段经常出现在 where 的后面，以条件的形式存在，也就是说这个字段总是被扫描。
  3. 该字段很少的 DML（insert、delete、update）操作。（因为 DML 之后，索引需要重新排序）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过 unique 约束的字段进行查询，效率是比较高的。

4. 索引的操作

1. 创建索引

给 emp 表的 ename 字段添加索引，起名：emp_ename_index

mysql> create index emp_ename_index on emp(ename);

在这里插入图片描述

2. 删除索引

将 emp 表上的 emp_ename_index 索引对象删除

mysql> drop index emp_ename_index on emp;

在这里插入图片描述

3. 查看一个sql语句是否使用了索引进行检索

mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

在这里插入图片描述
扫描14条记录：说明没有使用索引。type = ALL

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_ename_index | emp_ename_index | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在这里插入图片描述

5. 索引的失效

失效的第1种情况：

mysql> select * from emp where ename like '%T';

ename 上即使添加了索引，也不会走索引进行查询，为什么？
因为模糊查询匹配中以 “%” 开头了。
尽量避免模糊查询的时候以 “%” 开始。
这是一种优化的手段。

mysql> explain select * from emp where ename like '%T';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

在这里插入图片描述

失效的第2种情况：
使用 or 的时候会失效，如果使用 or 那么要求 or 两边的条件字段都要有索引，才会进行索引查询，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。索引这就是为什么不建议使用 or 的原因。

mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 |    16.43 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在这里插入图片描述

失效的第3种情况：
使用复合索引的时候，没有使用左侧的列查找，索引失效。

什么是复合索引？
两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

mysql> explain select * from emp where job = 'MANAGER';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_job_sal_index | emp_job_sal_index | 39      | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在这里插入图片描述

失效的第4种情况：
在 where 中索引列参加了运算，索引失效。

mysql> create index emp_sal_index on emp(sal);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from emp where sal + 1 = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

在这里插入图片描述

失效的第5种情况：
在 where 中索引列使用了函数

mysql> explain select * from emp where lower(ename) = 'smith';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

在这里插入图片描述

6. 索引的类型

  1. 单一索引：一个字段上添加索引
  2. 复合索引：两个或多个字段上添加索引
  3. 主键索引：主键上添加索引
  4. 唯一性索引：具有 unique 约束的字段上添加索引

注意： 唯一性比较弱的字段上添加索引用处不大。
相对来说，唯一性越高，效率越高。


http://chatgpt.dhexx.cn/article/aLXrlOiV.shtml

相关文章

【MySQL数据库】MySQL索引

【MySQL数据库】MySQL索引

MySQL索引、事务与存储引擎 一、索引1.1索引的概念1.2索引的作用1.3索引的工作原理1.4索引的副作用1.5索引的应用场景 二、索引的创建与分类2.1普通索引，最基本的索引类型，没有唯一性之类的限制。2.2唯一索引2.3 主键索引2.4组合索引2.5全文索引 三、删除…
阅读更多...
MySQL数据库索引机制

MySQL数据库索引机制

文章目录 一、MySQL缓冲区二、MySQL的page1.认识单个page2.认识多个页3.聚簇索引和非聚簇索引 三、索引的操作1.创建主键索引2.创建唯一索引3.创建普通索引4.查询索引的方法5.删除索引的方法6.索引创建原则 一、MySQL缓冲区 MySQL是一款有客户端和服务端的网络应用&#xff0c…
阅读更多...
MySQL数据库索引教程（超详细）

MySQL数据库索引教程（超详细）

索引初步 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 …
阅读更多...
【MySql】数据库索引

【MySql】数据库索引

数据库索引 索引索引的创建索引的查看索引的删除 聚簇索引 & 非聚簇索引聚簇索引非聚簇索引 索引创建原则 索引 可以简单理解为一本书的目录信息，是为了提升查找效率而建立的 索引的创建 1、在创建一个主键、唯一键、外键时候，数据库会自动地针对查…
阅读更多...
MySQL 数据库--索引

MySQL 数据库--索引

目录 前言 一、索引及分类 1.索引的概念 2.索引的作用 3.索引的副作用 4.索引的分类 4.1 普通索引 4.2 唯一性索引 4.3 主键索引 4.4 全文索引 4.5 组合索引（单列索引与多列索引） 5.创建索引的原则依据 二、创建及查看索引 1.准备 2.创建索…
阅读更多...
MYSQL数据库-索引

MYSQL数据库-索引

MYSQL数据库-索引 零、前言一、索引概念二、认识磁盘三、理解索引1、如何理解Page2、B vs B3、聚簇索引 VS 非聚簇索引4、普通索引5、总结 四、索引操作1、创建索引2、查询索引3、删除索引 零、前言 本章主要讲解MYSQL数据库中的索引这一重要知识点 一、索引概念 索引的价值&a…
阅读更多...
MySQL数据库：索引

MySQL数据库：索引

一、索引简介 1.概念 索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。 相当于是给数据库中的数据建立了一个目录，通过目录可以知道…
阅读更多...
MySQL数据库的索引

MySQL数据库的索引

文章目录 一、索引是什么？索引的作用 二、索引的使用查看索引创建索引删除索引 三、索引的底层 一、索引是什么？ 索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型&a…
阅读更多...
Mysql数据库索引详解

Mysql数据库索引详解

一、前言 随着我们业务量不断的增大，会发现查询数据库的时间变的越来越长，其实就是数据库到达了性能的瓶颈，这时候需要我们对其进行优化操作，可能我们第一时间就会想到：加索引。没错，这是优化的一个方向&a…
阅读更多...
MySQL 数据库--索引（理论详解及实例演示）

MySQL 数据库--索引（理论详解及实例演示）

文章目录 前言一、索引及分类1.索引的概念2.索引的作用3.索引的副作用4.索引的分类4.1 普通索引4.2 唯一性索引4.3 主键索引4.4 全文索引4.5 组合索引（单列索引与多列索引） 5.创建索引的原则依据 二、创建及查看索引1.准备2.创建索引的方法2.1 创建普通索…
阅读更多...
数据库-mysql之索引

数据库-mysql之索引

目录 1、索引简介 1.1 索引的含义和特点 1.2 索引的分类 1、普通索引 2、唯一性索引 3、全文索引 4、单列索引 5、多列索引 6、空间索引 1.3 索引的设计原则 1．选择唯一性索引 2．为经常需要排序、分组和联合操作的字段建立索引 3&#xff0…
阅读更多...
web前端页面开发工具的演进史

web前端页面开发工具的演进史

小编是一位有着6年前端开发经验的web前端开发工程师&#xff0c;今天小编从个人的视角来聊一聊web前端开发工具的演进史。 小编第一次接触 web前端开发是在2011年&#xff0c;那会还在上高中。当时正是大家都每天忙碌着装扮自己的QQ空间。那时的web前端布局还是以 <table&g…
阅读更多...
关于Webpack前端工程化构建，你必须要掌握这些核心知识点

关于Webpack前端工程化构建，你必须要掌握这些核心知识点

引言 在很久之前&#xff0c;模块化管理还没有出现&#xff0c;如果我们开发一个页面想要引入一些依赖的话&#xff0c;最常见的做法就是将依赖文件引入到.html文件中。比如&#xff0c;我们要使用JS的一些依赖库&#xff0c;就要在.html文件中使用<script>标签引用&…
阅读更多...
构建工具 Vite、Webpack、Rollup对比

构建工具 Vite、Webpack、Rollup对比

Webpack介绍 热更新方面&#xff1a;webpack支持HMR&#xff0c;但是webpack需要全部重新编译并更新&#xff0c;效率较低tree-shaking&#xff1a;webpack2开始支持且消除效果不好&#xff0c;但是webpack5有更好的tree-shaking&#xff08;去除未使用代码&#xff09;分包方…
阅读更多...
前端三大构建工具 Webpack、Vite、Rollup 优劣势及原理分析

前端三大构建工具 Webpack、Vite、Rollup 优劣势及原理分析

在刚刚结束的 VueConf2021 中&#xff0c;除了 Vue 3.0 以外&#xff0c;另外一个亮点就是下一代构建工具 Vite 了。 在尤雨溪分享的【 Vue 3 生态进展和计划】的演讲中&#xff0c;尤大神还特意提到 Vite 将成为 Vue 的现代标配。甚至最近新推出的 Petite Vue 从开发、编译、发…
阅读更多...
从Npm Script到Webpack，6种常见的前端构建工具对比

从Npm Script到Webpack，6种常见的前端构建工具对比

从Npm Script到Webpack&#xff0c;6种常见的前端构建工具对比 小编说&#xff1a;历史上先后出现了一系列构建工具&#xff0c;它们各有优缺点。由于前端工程师很熟悉JavaScript&#xff0c;Node.js又可以胜任所有构建需求&#xff0c;所以大多数构建工具都是用Node.js开发的。…
阅读更多...
前端开发入门：前端构建工具百度FIS

前端开发入门：前端构建工具百度FIS

FIS是什么&#xff1f; FIS全称为Front-end Integrated Solution&#xff0c;即前端集成解决方案。前端团队发展大多会经历规范设计、技术选型、系统拆分、性能优化等阶段&#xff0c;其中的技术需求可以总结为前端集成解决方案。FIS是专为解决前端开发中自动化工具、性能优化…
阅读更多...
前端工程化——构建工具选型

前端工程化——构建工具选型

一、什么是前端工程化 前端工程化是依据业务特点&#xff0c;将前端开发的规范、流程、技术、工具、经验等形成规范并建立成一种标准的体系。 二、为什么要前端工程化 实现前端工程化的目的简单来说就是通过流程规范、自动化工具来提升前端的开发效率、性能、质量、多人协作…
阅读更多...
前端构建工具与应用程序测试

前端构建工具与应用程序测试

1、前端构建工具 什么是前端构建&#xff1f; 什么是构建工具&#xff1f; 自动构建工具 Npm Scripts&#xff08;推荐&#xff09; Npm Scripts&#xff08;NPM脚本&#xff09;是一个任务执行者。NPM是安装Node时附带的一个包管理器&#xff0c;Npm Script 则是 NPM 内置…
阅读更多...
前端构建工具gulp的详细介绍以及使用

前端构建工具gulp的详细介绍以及使用

什么是 gulp为什么要用 gulp如何使用 gulp Installing Gulp Install the gulp commandInstall gulp in your devDependencies Create a gulpfileTest it out gulp API 什么是 gulp gulp 是一个前端构建工具&#xff0c;它能通过自动执行常见任务&#xff0c;比如编译预处理 CS…
阅读更多...
推荐文章

Copyright @ 2022~2023