百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

mysql的表索引相关（mysql索引的用法）

wptr33 2025-05-03 16:58 21 浏览

索引的创建以及删除

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 需要创建联合索引


相关推荐

高性能并发队列Disruptor使用详解

基本概念Disruptor是一个高性能的异步处理框架,是一个轻量的Java消息服务JMS,能够在无锁的情况下实现队列的并发操作Disruptor使用环形数组实现了类似队列的功能,并且是一个有界队列....

Disruptor一个高性能队列_java高性能队列

Disruptor一个高性能队列前言说到队列比较熟悉的可能是ArrayBlockingQueue、LinkedBlockingQueue这两个有界队列，大多应用在线程池中使用能保证线程安全，但其安全性...

谈谈防御性编程_防御性策略

防御性编程对于程序员来说是一种良好的代码习惯，是为了保护自己的程序在不可未知的异常下，避免带来更大的破坏性崩溃，使得程序在错误发生时，依然能够云淡风轻的处理，但很多程序员入行很多年，写出的代码依然都是...

有人敲门，开水开了，电话响了，孩子哭了，你先顾谁?

前言哎呀，这种情况你肯定遇到过吧！正在家里忙活着，突然——咚咚咚有人敲门，咕噜咕噜开水开了，铃铃铃电话响了，哇哇哇孩子又哭了...我去，四件事一起来，人都懵了！你说先搞哪个？其实这跟我们写Java多线...

面试官:线程池如何按照core、max、queue的执行顺序去执行?

前言这是一个真实的面试题。前几天一个朋友在群里分享了他刚刚面试候选者时问的问题："线程池如何按照core、max、queue的执行循序去执行？"。我们都知道线程池中代码执行顺序是：co...

深入剖析 Java 中线程池的多种实现方式

在当今高度并发的互联网软件开发领域，高效地管理和利用线程资源是提升程序性能的关键。Java作为一种广泛应用于后端开发的编程语言，为我们提供了丰富的线程池实现方式。今天，就让我们深入探讨Java中...

并发编程之《彻底搞懂Java线程》_java多线程并发解决方案详解

目录引言一、核心概念：线程是什么？...

Redis怎么实现延时消息_redis实现延时任务

一句话总结Redis可通过有序集合（ZSET）实现延时消息：将消息作为value，到期时间戳作为score存入ZSET。消费者轮询用ZRANGEBYSCORE获取到期消息，配合Lua脚本保证原子性获取...

CompletableFuture真的用对了吗?盘点它最容易被误用的5个场景

在Java并发编程中，CompletableFuture是处理异步任务的利器，但不少开发者在使用时踩过这些坑——线上服务突然雪崩、异常悄无声息消失、接口响应时间翻倍……本文结合真实案例，拆解5个最容易...

接口性能优化技巧，有点硬_接口性能瓶颈

背景我负责的系统到2021年初完成了功能上的建设，开始进入到推广阶段。随着推广的逐步深入，收到了很多好评的同时也收到了很多对性能的吐槽。刚刚收到吐槽的时候，我们的心情是这样的：...

禁止使用这5个Java类，每一个背后都有一段&quot;血泪史&quot;

某电商平台的支付系统突然报警：大量订单状态异常。排查日志发现，同一笔订单被重复支付了三次。事后复盘显示，罪魁祸首竟是一行看似无害的SimpleDateFormat代码。在Java开发中，这类因使用不安...

无锁队列Disruptor原理解析_无锁队列实现原理

队列比较队列...

Java并发队列与容器_java 并发队列

【前言：无论是大数据从业人员还是Java从业人员，掌握Java高并发和多线程是必备技能之一。本文主要阐述Java并发包下的阻塞队列和并发容器，其实研读过大数据相关技术如Spark、Storm等源码的，...

线程池工具及拒绝策略的使用_线程池处理策略

线程池的拒绝策略若线程池中的核心线程数被用完且阻塞队列已排满，则此时线程池的资源已耗尽，线程池将没有足够的线程资源执行新的任务。为了保证操作系统的安全，线程池将通过拒绝策略处理新添加的线程任务。...

【面试题精讲】ArrayBlockingQueue 和 LinkedBlockingQueue 区别?

有的时候博客内容会有变动，首发博客是最新的，其他博客地址可能会未同步,认准...

一周热门
最近发表
标签列表
赣ICP备2024040476号-12