【宋红康 MySQL数据库 】【高级篇】【11】索引的设计原则

最新推荐文章于 2024-04-29 23:47:34 发布
最新推荐文章于 2024-04-29 23:47:34 发布
阅读量885 收藏 5
点赞数 2
分类专栏： 数据库 文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44018671/article/details/121388700
版权

持续学习&持续更新中…

学习态度：守破离

【宋红康 MySQL数据库 】【高级篇】【11】索引的设计原则

数据准备

在这里插入图片描述

CREATE DATABASE atguigudb1;

USE atguigudb1;

#1.创建学生表和课程表
CREATE TABLE `student_info` (
 `id` INT(11) AUTO_INCREMENT,
 `student_id` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `course_id` INT NOT NULL ,
 `class_id` INT(11) DEFAULT NULL,
 `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`course_id` INT NOT NULL ,
`course_name` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


#函数1：创建随机产生字符串函数

SELECT @@log_bin_trust_function_creators;

SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //
CREATE FUNCTION rand_string(n INT) 
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN 
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO 
       SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
       SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;


#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END //
DELIMITER ;


# 存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE  insert_course( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    #设置手动提交事务
 REPEAT  #循环
 SET i = i + 1;  #赋值
 INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  #提交事务
END //
DELIMITER ;


# 存储过程2：创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE  insert_stu( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    #设置手动提交事务
 REPEAT  #循环
 SET i = i + 1;  #赋值
 INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  #提交事务
END //
DELIMITER ;

#调用存储过程：
CALL insert_course(100);

SELECT COUNT(*) FROM course;

CALL insert_stu(1000000);

SELECT COUNT(*) FROM student_info;

在这里插入图片描述

哪些情况适合创建索引

字段的数值有唯一性的限制

在这里插入图片描述

频繁作为 WHERE 查询条件的字段

在这里插入图片描述

#查看当前stduent_info表中的索引
SHOW INDEX FROM student_info;
#student_id字段上没有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #276ms
#给student_id字段添加索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
#student_id字段上有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #43ms

经常 GROUP BY 和 ORDER BY 的列

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

#student_id字段上有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #41ms

#删除idx_sid索引
DROP INDEX idx_sid ON student_info;

SHOW INDEX FROM student_info;

#student_id字段上没有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #866ms

在这里插入图片描述

SHOW INDEX FROM student_info;

#先修改sql_mode

SELECT @@sql_mode;
SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

#才能这样子测试

ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);

#EXPLAIN #发现没有使用到create_time字段的索引【因为group by语句先于order by语句执行】
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #5.212s

在这里插入图片描述

# 添加联合索引：
ALTER TABLE student_info
ADD INDEX idx_sid_cre_time(student_id,create_time DESC);

#EXPLAIN
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #0.257s

在这里插入图片描述

DROP INDEX idx_sid_cre_time ON student_info;

ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC,student_id);

#EXPLAIN #发现使用的还是group by语句的字段（student_id）的索引，并没有使用到idx_cre_time_sid这个联合索引
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;

UPDATE、DELETE 的 WHERE 条件列

在这里插入图片描述

在这里插入图片描述

SHOW INDEX FROM student_info;

UPDATE student_info SET student_id = 10002 
WHERE NAME = '462eed7ac6e791292a79';  #0.633s

#添加索引
ALTER TABLE student_info
ADD INDEX idx_name(NAME);


UPDATE student_info SET student_id = 10001 
WHERE NAME = '462eed7ac6e791292a79'; #0.001s

DISTINCT 字段需要创建索引

在这里插入图片描述

在这里插入图片描述

多表 JOIN 连接操作时，创建索引注意事项

在这里插入图片描述

PS：数据类型一旦转换就会用到函数，一旦用到函数索引就失效了，故数据类型必须一致。

使用列的类型小的创建索引

在这里插入图片描述

使用字符串前缀创建索引

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

PS：查询出来的值越接近于1越好一些。都接近于1那么截取的越少越好。

在这里插入图片描述

区分度高(散列性高)的列适合作为索引

在这里插入图片描述

比如：

  • gender字段只有’男’、'女’两个值，所以gender字段就不适合创建索引
  • id字段适合创建索引

使用最频繁的列放到联合索引的左侧

在这里插入图片描述

注意

  • 在多个字段都要创建索引的情况下，联合索引优于单值索引

哪些情况不适合创建索引

在where中使用不到的字段，不要设置索引

在这里插入图片描述

数据量小的表最好不要使用索引

在这里插入图片描述

在这里插入图片描述

有大量重复数据的列上不要建立索引

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

避免对经常更新的表创建过多的索引

在这里插入图片描述

不建议用无序的值作为索引

在这里插入图片描述

删除不再使用或者很少使用的索引

在这里插入图片描述

不要定义冗余或重复的索引

在这里插入图片描述

在这里插入图片描述

总结

小结

在这里插入图片描述

限制索引的数目

在这里插入图片描述

参考

尚硅谷宋红康: MySQL数据库（入门到高级，菜鸟到大牛）.

本文完，感谢您的关注支持！

lpruoyu
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL入门到高级-宋红康版.zip
08-15
MySQL入门到高级-宋红康版 ----- 毕业设计，课程设计，项目源码均经过助教老师测试，运行无误，欢迎下载交流 ----- 下载后请首先打开README.md文件（如有），某些链接可能需要魔法打开。 ----- 毕业设计，课程设计，...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
MySQL-索引优化与查询优化1
cheng1877的博客
08-31 316
MySQL索引
导航【mysql高级】【java提高】
小心星的博客
08-14 1398
导航【mysql高级】【java提高】
mysql为什么不能建很多索引_为什么重复值高的字段不能建索引（+聚集索引和非聚集索引)...
weixin_35786759的博客
01-19 2255
一、原因：-非聚簇索引存储了对主键的引用，如果select字段不在非聚簇索引内，就需要跳到主键索引。-如果非聚簇索引值重复率高，那么查询时就会大量出现上图中从右边跳到左边的情况，导致整个流程很慢原因就是上面的。数据库中聚集索引只有一个，默认主键。其他用户创建的索引都是非聚集索引。非聚集索引存储了对主键的引用，即通过索引确定叶子节点之后，还需要再次根据主键去查询数据。(所以会查询两次)如果非...
索引的创建与设计原则--MySQL
Littewood的博客
02-28 694
索引的创建方式与设计原则
MYSQL中为什么索引不宜建在重复数据多的列上
热门推荐
Sunmeok的博客
10-08 1万+
昨天想了一天这个问题 首先我们先粗略的说一说： 加入有一个查询语句要查询性别为男生的数据，因为这样的数据很多，我们要扫描很多次索引，然后再去取这个性别为男的数据。 那么分为两部分，先扫描索引，然后去取这个符合要求的数据 如果我们不建立索引，那么去扫描整个表。 不建立索引需要的时间=T扫描整个表                            建立索引需要的时间= T去索引中取+T取...
创建索引的注意事项
qq_63815371的博客
04-21 9247
一.11种适合创建索引的常见情况 1. 字段的数值有唯一性的限制 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba） 说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。 2. 频繁作为 WHERE 查询条件的字段 某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。 比如学生表数据表...
为什么重复值高的字段不能建索引（比如性别字段等)
weixin_34244102的博客
06-28 4922
   结论（以innodb为例） a、非聚簇索引存储了对主键的引用，如果select字段不在非聚簇索引内，就需要跳到主键索引（上图中从右边的索引树跳到左边的索引树），再获取select字段值 b、如果非聚簇索引值重复率高，那么查询时就会大量出现上图中从右边跳到左边的情况，导致整个流程很慢 c、如果where值重复率高的字段，select用了limit，只查较少数据，也就是跳的次数很少的...
MySQL数据库管理高级篇(二)
06-14
MySQL是一个关系型数据库管理系统，目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一。 MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL采用了双授权策略，分为社区版和商业版，...
通俗易懂的MySQL高级教程（含配套资料）
06-14
本教程为授权出品 MySQL是目前流行的关系型数据库管理系统，在WEB应用方面 MySQL 也是目前好的 RDBMS 应用软件之一。随着淘宝去IOE(去除IBM小型机、Oracle数据库及EMC存储设备)化的推进，MySQL 数据库在当前的互联网应用中变得越来越重要，本教程主要讲授针对 Java 开发所需的 MySQL 高级知识，课程中会让大家快速掌握索引，如何避免索引失效，索引的优化策略，了解innodb和myisam存储引擎，熟悉MySQL锁机制，能熟练配置MySQL主从复制，熟练掌握explain、show profile、慢查询日志等日常SQL诊断和性能分析策略。
mysql高级参考课件（供实操参考）.zip
01-18
mysql进阶资料
尚硅谷MySQL全套教程包含MySql核心技术篇和MySQL高级篇
08-10
MYSQL教程 MYSQL教程MYSQL教程 MYSQL教程尚硅谷MySQL全套教程包含MySql核心技术篇和MySQL高级篇。数据库视频教程，全套视频教程。
mysql数据库哪些情况不适合使用索引
qq_39746820的博客
03-28 4453
mysql数据库哪些情况不适合使用索引
【课堂练习】
最新发布
JacksonLeo的博客
04-29 280
虽然现代JVM的垃圾回收机制已经非常高效，但是在性能敏感的场合，仍然需要注意对象的创建频率。例如，在SysLogininforMapper中的deleteLogininforByIds方法，如果处理大量ID时，应考虑使用批处理技术，而不是循环单条处理。例如，在SysMenuMapper中的查询方法，如果涉及到复杂的查询条件或者大量数据的查询，应确保SQL语句的优化和索引的使用。异常处理：代码中应该有更明确的异常处理逻辑，比如使用try-catch块捕获可能的异常，并进行适当的处理，如记录日志、回滚事务等。
探秘MySQL主从复制的多种实现方式
todoitbo的博客
04-26 1029
本文将深入探讨MySQL主从复制的多种实现方式，包括基于语句、基于行和混合模式等。无论您是初学者还是有经验的数据库管理员，都能通过本文全面了解MySQL主从复制技术的多样化选择，从而提高数据库的可用性和性能。
SpringCloudAlibaba：2.1nacos
m0_63040701的博客
04-26 913
Nacos是阿里巴巴开源的服务注册中心以及配置中心Nacos=注册中心Eureka + 服务配置Config + 服务总线Bus。
SpringBoot JPA使用
gochenguowei的博客
04-25 983
是 Spring 框架提供的一个模块，用于简化与关系型数据库的交互和数据访问。它基于JPA（Java Persistence API）标准，并提供了一组易于使用的API和工具，帮助开发人员更轻松地进行数据库操作。通过Spring Data JPA，开发人员可以通过编写简洁的代码来执行常见的 CRUD 操作，同时还支持高级查询、分页、事务管理等功能。它的目标是提供一种更简单、更高效的方式来处理数据库操作，减少开发人员的工作量，并提高应用程序的可维护性和可扩展性。
superset部署与实践
m0_60125201的博客
04-29 602
本博客简要介绍了superset这个BI工具，使用两种方法来部署superset，并简单介绍了superset的使用。
宋红康 mysql数据库
08-15
您好！对于MySQL数据库，我可以提供一些基本的信息。MySQL是一个开源的关系型数据库管理系统，广泛应用于各种网站和应用程序中。它具有高性能、稳定可靠以及丰富的功能。 MySQL支持多种操作系统，并提供了多种编程语言的接口，如Java、Python、PHP等。它使用标准的SQL语言进行数据管理和查询，可以处理大规模的数据集。 MySQL的特点包括： 1. 可扩展性：可以在单个服务器上运行多个数据库实例，支持主从复制和分布式数据库架构。 2. 安全性：提供了各种安全特性，如用户认证、数据加密和访问控制等。 3. 高性能：通过索引、查询优化和缓存等技术，提升数据库的查询和写入性能。 4. 数据完整性：支持事务处理和数据约束，保证数据的一致性和完整性。 5. 可靠性：提供了数据备份、恢复和故障转移等机制，确保数据的可靠性和可用性。 除了基本的数据库操作外，MySQL还支持存储过程、触发器、视图等高级功能，可以实现复杂的业务逻辑和数据处理需求。 希望这些信息对您有所帮助！如果您有更具体的问题，可以继续提问。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值