mysql的表索引相关（mysql索引的用法）
wptr33 2025-05-03 16:58 15 浏览
索引的创建以及删除
1.alter table
2.create/drop index
mysql> create index idx_b on t (cls_id);
ERROR 1072 (42000): Key column 'cls_id' doesn't exist in table
desc方式查看
mysql> desc students;
+------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| created_at | datetime(3) | YES | | NULL | |
| updated_at | datetime(3) | YES | | NULL | |
| deleted_at | datetime(3) | YES | MUL | NULL | |
| sno | bigint | YES | | NULL | |
| pwd | varchar(32) | NO | | NULL | |
| tel | varchar(12) | NO | | NULL | |
| birth | datetime(3) | YES | | NULL | |
| cls_id | bigint | YES | MUL | NULL | |
+------------+-----------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
show create table 方式查看
mysql> show create table students;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`created_at` datetime(3) DEFAULT NULL,
`updated_at` datetime(3) DEFAULT NULL,
`deleted_at` datetime(3) DEFAULT NULL,
`sno` bigint DEFAULT NULL,
`pwd` varchar(32) NOT NULL,
`tel` varchar(12) NOT NULL,
`birth` datetime(3) DEFAULT NULL,
`cls_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_students_deleted_at` (`deleted_at`),
KEY `idx_b` (`cls_id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> show index from students
-> ;
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| students | 0 | PRIMARY | 1 | id | A | 45 | NULL | NULL | | BTREE | | | YES | NULL |
| students | 1 | idx_students_deleted_at | 1 | deleted_at | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| students | 1 | idx_b | 1 | cls_id | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.04 sec)索引核心是降低io的操作
explain select的查询序列号
1.数字越小，表示最外面
2.数字越大表示优先级最高（最里面的先执行）
3.数字相同表示是同一组的 同一组里的按顺序来从上到下执行
mysql> explain select * from su a ,(select c2 from su where id=10) b where a.c2=b.c2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+-------+----------+-------------+
| 1 | SIMPLE | su | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 87778 | 10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)4.当type 中出现了all 表示走了全表扫描 possible_keys （可能用到的索引） key（索引名称） rows （扫描的行数 一般超过5000行就有问题需要进行优化）
5.update 或者delete也会使用到对应的索引（select,update,delete都是能加速的）
索引分为单列索引和联合索引
创建方法： create index idx_c3_c4 on su(c3,c4)
查看索引的信息：
mysql> show index from su;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| su | 0 | PRIMARY | 1 | id | A | 472487 | NULL | NULL | | BTREE | | | YES | NULL |
| su | 1 | idx_c2 | 1 | c2 | A | 305177 | NULL | NULL | | BTREE | | | YES | NULL |
| su | 1 | idx_c3_c4 | 1 | c3 | A | 304103 | NULL | NULL | | BTREE | | | YES | NULL |
| su | 1 | idx_c3_c4 | 2 | c4 | A | 305613 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.03 sec)查询
mysql> explain select * from su where c3="222"; #用到索引了
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | su | NULL | ref | idx_c3_c4 | idx_c3_c4 | 4 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from su where c4="222"; #当使用c4查询时没有使用到对应的索引
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | su | NULL | ALL | NULL | NULL | NULL | NULL | 472487 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
explain select * from su where c4="222" order by c3;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | su | NULL | ALL | NULL | NULL | NULL | NULL | 472487 | 10.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from su where c3="222" order by c4;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | su | NULL | ref | idx_c3_c4 | idx_c3_c4 | 4 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#all 运算符没有使用到索引
mysql> explain select * from su where c3="222" or c4="123";
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | su | NULL | ALL | idx_c3_c4 | NULL | NULL | NULL | 472487 | 19.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)总结： 1.创建联合索引时 最左前缀原则 当单独使用c4时是无法使用索引的
2.在orderby 进行排序时也是遵循最左前缀原则的 走索引（c3必须在前面才行）
3.or 关键字在联合索引中不起作用（单列索引是可以） index merge(索引的合并)，只能用一个索引（同时使用到单列和联合索引） 转化为 合并索引
4.不支持中文索引，但是支持全文索引如何创建全文索引
5.索引中不会包含有null值的列，当使用count（*）时对null值不走，对于创建索引时我们建议创建非空字段 不建议在null上的字段简历索引
6.在进行select语句时不要进行函数运算 ，否则你创建索引了也用不到索引
mysql> create index idx_c4 on su(c4);
Query OK, 0 rows affected (3.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from su where c3="222" or c4="123";
+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+
| 1 | SIMPLE | su | NULL | index_merge | idx_c3_c4,idx_c4 | idx_c3_c4,idx_c4 | 4,4 | NULL | 3 | 100.00 | Using sort_union(idx_c3_c4,idx_c4); Using where |
+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+
1 row in set, 1 warning (0.01 sec)删除索引： alter table su drop index idx_c4;
mysql> ALTER TABLE su drop index idx_c4;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
尽量少使用or 否则就健单列索引
全文索引
create fulltext index idx_su on su(1);
覆盖索引
通过索引即可查到数据（就是覆盖素引） Extra 中使用 Usingindex
查询列就是索引列
mysql> explain select c2 from su where c2=123;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | su | NULL | ref | idx_c2 | idx_c2 | 4 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+当extra 中使用的是 filesort 时需要优化
函数运算索引
mysql> explain select * from su where YEAR(C5)<2007;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | su | NULL | ALL | NULL | NULL | NULL | NULL | 497738 | 100.00 | Using where |
+----+-------------+-------+---------
未使用函数时
mysql> explain select * from su where C5<"2007-12-12:14:00";
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | su | NULL | range | idx_c5 | idx_c5 | 4 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)1.提高排队效率
2.创建索引在非null的字段上进行创建，在有null的字段会扫描对应 的null数据
3.联合索引降低搜索的范围（单行索引和联合索引没有绝对的好与不好）
1.通过索引 扫描超过30%时走全表扫描
2.函数 以及模糊匹配时无法使用索引 like 语句
3.不综训最左前缀原则不走索引
4.两个单读索引 一个用排序 orderby 需要创建联合索引
相关推荐
- SpringBoot 3 + Flutter3 实战低代码运营管理-10章
-
获课》aixuetang.xyz/5075/三天构建运营管理系统：SpringBoot3+Flutter3高效开发方法论...
- SpringBoot探针实现:从零构建应用健康监控利器
-
SpringBoot探针实现：从零构建应用健康监控利器声明本文中的所有案例代码、配置仅供参考，如需使用请严格做好相关测试及评估，对于因参照本文内容进行操作而导致的任何直接或间接损失，作者概不负责。本文...
- Spring Batch中的JobRepository:批处理的“记忆大师”是如何工作
-
一、JobRepository是谁？——批处理的“档案馆”JobRepository是SpringBatch的“记忆中枢”，负责记录所有Job和Step的执行状态。它像一位严谨的档案管理员，把任务执...
- 还在为 Spring Boot3 技术整合发愁?一文解锁大厂都在用的实用方案
-
你在使用SpringBoot3开发后端项目时，是不是常常陷入这样的困境？想提升项目性能和功能，却不知道该整合哪些技术；好不容易选定技术，又在配置和使用上频频踩坑。其实，这是很多互联网大厂后端开发...
- 一文吃透!Spring Boot 项目请求日志记录，这几招你绝对不能错过!
-
在互联网应用开发的高速赛道上，系统的稳定性、可维护性以及安全性是每一位开发者都必须关注的核心要素。而请求日志记录，就如同系统的“黑匣子”，能够为我们提供排查故障、分析用户行为、优化系统性能等关键信息...
- spring-boot-starter-actuator简单介绍
-
SpringBootActuator是SpringBoot的一个功能强大的子项目，它提供了一些有用的监控和管理SpringBoot应用程序的端点。SpringBootActuat...
- 使用SpringBoot钩子或Actuator实现优雅停机
-
服务如何响应停机信号在java中我们可以直接利用通过Runtime...
- 28-自定义Spring Boot Actuator指标
-
上篇我们学习了《27-自定义SpringBootActuator健康指示器》，本篇我们学习自定义SpringBootActuator指标（Metric）。...
- 如何在Spring Boot中整合Spring Boot Actuator进行服务应用监控?
-
监控是确保系统稳定性和性能的关键组成部分，而在SpringBoot中就提供了默认的应用监控方案SpringBootActuator，通过SpringBootActuator提供了开箱即用的应...
- 「Spring Boot」 Actuator Endpoint
-
Actuator官网地址：https://docs.spring.io/spring-boot/docs/2.5.6/reference/html/actuator.html目的监控并管理应用程序...
- Spring Boot Actuator监控功能全面剖析
-
SpringBootActuator监控功能全面剖析在现代企业级Java开发中，SpringBoot以其轻量化、高效率的特性深受开发者青睐。而作为SpringBoot生态系统的重要组成部分，S...
- 1000字彻底搞懂SpringBootActuator组件!
-
SpringBootActuator组件SpringBootActuator通过HTTPendpoints或者JMX来管理和监控SpringBoot应用，如服务的审计、健康检查、指标统计和...
- JavaScript数据类型（javascript数据类型介绍）
-
基本数据类型BooleanNullNumberStringSymbolUndefined对象数据类型ObjectArray定义：JavaScript数组是内置的对象之一，它可以用一个变量来存储多个同种...
- 能运行，不代表它是对的:5 个潜伏在正常功能下的 JavaScript 错误
-
JavaScript的动态性和复杂性意味着，代码虽然表面上正常运行，但一些深层次、隐蔽的陷阱往往让人意想不到，梳理了几个JavaScript开发中难以发现的隐蔽错误，旨在帮助我们写出更健壮、更可...
- 一周热门
-
-
C# 13 和 .NET 9 全知道 :13 使用 ASP.NET Core 构建网站 (1)
-
因果推断Matching方式实现代码 因果推断模型
-
git pull命令使用实例 git pull--rebase
-
面试官:git pull是哪两个指令的组合?
-
git 执行pull错误如何撤销 git pull fail
-
git pull 和git fetch 命令分别有什么作用?二者有什么区别?
-
git fetch 和git pull 的异同 git中fetch和pull的区别
-
git pull 之后本地代码被覆盖 解决方案
-
还可以这样玩?Git基本原理及各种骚操作，涨知识了
-
git命令之pull git.pull
-
- 最近发表
-
- SpringBoot 3 + Flutter3 实战低代码运营管理-10章
- SpringBoot探针实现:从零构建应用健康监控利器
- Spring Batch中的JobRepository:批处理的“记忆大师”是如何工作
- Github霸榜的SpringBoot全套学习教程，从入门到实战，内容超详细
- 还在为 Spring Boot3 技术整合发愁?一文解锁大厂都在用的实用方案
- 一文吃透!Spring Boot 项目请求日志记录，这几招你绝对不能错过!
- spring-boot-starter-actuator简单介绍
- 使用SpringBoot钩子或Actuator实现优雅停机
- 28-自定义Spring Boot Actuator指标
- 如何在Spring Boot中整合Spring Boot Actuator进行服务应用监控?
- 标签列表
-
- git pull (33)
- git fetch (35)
- mysql insert (35)
- mysql distinct (37)
- concat_ws (36)
- java continue (36)
- jenkins官网 (37)
- mysql 子查询 (37)
- python元组 (33)
- mybatis 分页 (35)
- vba split (37)
- redis watch (34)
- python list sort (37)
- nvarchar2 (34)
- mysql not null (36)
- hmset (35)
- python telnet (35)
- python readlines() 方法 (36)
- munmap (35)
- docker network create (35)
- redis 集合 (37)
- python sftp (37)
- setpriority (34)
- c语言 switch (34)
- git commit (34)