连肝了多天学习MySQL索引与性能优化，详细总结一下索引的使用与数据库优化

文章目录

    • 索引是什么？
    • 索引的作用
    • 初步认识索引
    • 索引的类型
      • 按照数据结构分类
          • B+TREE索引
        • 哈希索引
      • 按功能逻辑进行分类
        • 唯一索引
        • 普通索引
        • 主键索引
        • 全文索引
      • 按照字段的个数进行划分
        • 单列索引
        • 多列（组合，联合）索引
  • 小结
  • 索引的设计原则
    • 数据准备
      • 1.创建数据库表
      • 2.创建模拟数据必需的存储函数
      • 3.创建插入模拟数据的存储过程
      • 4.调用函数
    • 哪些适合创建索引
      • 1.字段的数值有唯一性
      • 2.频繁作为WHERE查询条件的字段
      • 3. 经常 GROUP BY 和 ORDER BY 的列
      • 4. UPDATE、DELETE 的 WHERE 条件列
      • 5.DISTINCT 字段需要创建索引
      • 6.多表 JOIN 连接操作时，创建索引注意事项
      • 7.最左匹配原则
  • 总结

索引是什么？

 是存储引擎用于快速找到记录的一种数据结构。

索引的作用

 一个系统后期的性能调优大概就是数据库调优了，由于系统到了后期维护阶段，数据量庞大，索引对性能的影响愈发的重要。
 索引的优化应该是对查询优化最有效的的手段了。索引能够轻易的将 查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好几个数量级。创建一个真正“最优”的索引经常需要重写查询语句。

初步认识索引

 要了解MySQL中索引是如何工作的，最简单的方法就是去看看一本书的目录部分：如果想在一本书中找到某个特定主题，一般会先看书的“目录”部分，找到对应页码。索引在MySQL中使用，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

mysql> SELECT * FROM product p WHERE p.id = "000000280051064738303630"

 如果在id中有建立检索，则MySQL将使用该索引找到对应的值为000000280051064738303630的id的数据行，也就是说 ，MySQL先在索引进行查找，然后返回所有包含该值的数据行。
 索引可以包含一个或者多个列。如果索引包含多个列，那么这些列的顺序将很重要，因为MySQL只能高效地使用索引的最左缀列。创建一个含两个列和包含一个列的索引的索引的大小是不同的。

索引的类型

按照数据结构分类

B+TREE索引

 在数据结构中有对B+TREE进行详细的讲述。其主要的特性就是查询快，而且是进行顺序存储的，并且每一个叶子节点到根的距离是相同的，加了索引的查询不需要遍历一整张表。
在这里插入图片描述
  B+Tree索引能够加快访问速度，因为存储引擎不再需要进行全表扫描来获取数据行，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义 了子节点页中值的上限和下限。最终索引要么找到该值，要么该值不存在。叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的叶子节点。
 实际上很多存储引擎使用的是B+Tree，即每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历，B+Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

哈希索引

  是一种特殊的数据库索引类型，它利用哈希表（Hash Table）的数据结构来存储索引项。哈希表通过哈希函数（Hash Function）将索引列的值转化为一个固定长度的哈希码（Hash Code），然后用这个哈希码作为索引项在表中定位数据记录的位置。这种方式使得对于等值查询（例如 WHERE column = value）能够非常快速，理想情况下接近O(1)的时间复杂度。但是缺点就是不适合范围查找，涉及到范围查找需要使用B+Tree索引。哈希索引的数据结构如下图所示，相当于数组+链表的数据结构，每一个哈希值占用一个数组位，相同的哈希值下有对应的冲突元素：
在这里插入图片描述

按功能逻辑进行分类

唯一索引

  唯一索引要求所有索引列的值必须唯一，但允许空值存在。它用于确保数据的唯一性。同时也可以增加查找效率。

普通索引

  普通索引也称为辅助索引，是最基础的索引类型，没有唯一性和空值的限制，没有任何的约束，主要用于提高查询效率。

主键索引

  主键索引是一种特殊的唯一索引，不允许有空值。它通过主键字段建立，并自动创建。每个表只能有一个主键索引。

全文索引

  全文索引主要用于对大文本字段进行搜索，支持复杂的查询模式，如自然语言搜索。其中InnoDB是不支持全文索引的，而MySAMDB支持，但是不支持行锁，这里不做展开了。一般实际应用中会使用第三方索引中间件如ES，ES提供分词技术。
按照

按照字段的个数进行划分

  可分为单列索引和联合索引。

单列索引

  在表中的单个字段上创建索引。单列索引值根据该字段进行索引。单列索引可以是唯一索引，也可以是普通索引，也可以是全文索引。只要保证该索引对应一个字段即可。一个表可以建多个单列索引。

多列（组合，联合）索引

  是数据库中一种将多个列组合在一起创建的索引。它允许数据库系统通过一个单独的索引结构来快速定位满足多个条件的数据行。

小结

每个存储引擎都支持不同类型的索引
  InnoDB ：支持B-tree、全文索引，不支持Hash索引。
  MylSAM ：支持B-tree、全文索引，不支持Hash索引，不支持行锁。
  Memory：支持B+Tree和Hash，不支持全文索引。
  NDB：支持Hash索引，不支持B+Tree、全文索引等
  Archive：不支持B+Tree、全文、Hash等索引。

索引的设计原则

  为了使索引的使用效率更高，在创建索引时，必须考虑在哪字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好性能非常重要，设计索引时，应该考虑相应的准则。

数据准备

1.创建数据库表

#1.创建学生表和课程表
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL 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;

2.创建模拟数据必需的存储函数

#函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT)RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGINDECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET 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 ;

如果创建函数出现报错。

This function has none of DETERMINISTIC......

  由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。
  主从复制，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使 用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设置。
.查看mysql是否允许创建函数：

show variables like 'log_bin_trust_function_creators';

  命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

  mysqld重启，上述参数又会消失。永久方法：
  windows下：my.ini[mysqld]加上：

log_bin_trust_function_creators=1

  linux下：/etc/my.cnf下my.cnf[mysqld]加上：

log_bin_trust_function_creators=1

3.创建插入模拟数据的存储过程

# 存储过程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 ;

4.调用函数

CALL insert_course(100);
CALL insert_stu(1000000);

哪些适合创建索引

1.字段的数值有唯一性

  索引本身就是起到约束作用，比如唯一索引、逐渐主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如有某个字段的值具有唯一性，可以直接创建唯一索引，或者主键索引。唯一性可以是多个字段的组合，多个字段组合创建一个唯一索引也是可以的。

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

  某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在 数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
  比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。

3. 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立组合索引 。

4. UPDATE、DELETE 的 WHERE 条件列

  对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT 字段需要创建索引

  有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行 SQL 语句：

      SELECT DISTINCT(student_id) FROM `student_info`;

  运行结果（600637 条记录，运行时间 0.683s ）
如果我们对 student_id 创建索引，再执行 SQL

   SELECT DISTINCT(student_id) FROM `student_info`;

   语句：运行结果（600637 条记录，运行时间 0.010s ）
  你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照递增的顺序 进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

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

  首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增 长会非常快，严重影响查询的效率。
  其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。

  最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。这是因为索引在遇到聚合函数时会失效，MySQL会自动把varchar转为int，比如10 = ‘10’，这种情况MySQL会自动把字符varchar类型的‘10’转为10，导致了索引失效。
  举个例子，如果我们只对 student_id 创建索引，执行 SQL 语句：

		SELECT s.course_id, name, s.student_id, c.course_nameFROM student_info s JOIN course cON s.course_id = c.course_idWHERE name = '462eed7ac6e791292a79';运行结果（1 条数据，运行时间 0.189s ）

  其中course_id如果在student_info和course 表中数据类型不一样，那么就是即使在course_id上加索引也会失效。在开发中一定要注意这个问题。
  这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s 。

7.最左匹配原则

  MySQL的最左匹配原则（Leftmost Prefixing）是指在使用索引进行查询时，MySQL会从索引的最左边的列开始匹配，并且只能使用一个索引中的列进行查询。如果查询条件不从最左边的列开始，那么MySQL将不会使用这个索引。
举个例子，假设有一个组合索引（a, col2, col3），口语化的表述就是，第一列a是带头大哥，如果where没有用到a，索引将会失效。当执行查询时，哪些会索引失效，哪些会没失效，如下代码所示：

使用到索引
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;
`没能使用索引
SELECT * FROM table WHERE b = 2;
SELECT * FROM table WHERE b = 2 AND c = 3;
SELECT * FROM table WHERE c = 3;

总结

  介绍了索引，是什么，数据结构，有哪些分类，设计原则，后面还会持续更新。也是比较粗浅的，后面会逐渐加大深度。

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

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

K8s中pod的管理和优化

K8s中pod的管理和优化

一、k8s中的资源 1.1 资源管理介绍 在kubernetes中&#xff0c;所有的内容都抽象 资源&#xff0c;用户需要通过操作资源来管理kubernetes。kubernetes的本质上就是一个集群系统&#xff0c;用户可以在集群中部署各种服务所谓的部署服务&#xff0c;其实就是在kubernetes集群中…
阅读更多...
基于SpringBoot+Vue的非物质文化遗产保护与传播系统设计实现【原创】(地图组件)

基于SpringBoot+Vue的非物质文化遗产保护与传播系统设计实现【原创】(地图组件)

&#x1f388;系统亮点&#xff1a;地图组件&#xff1b; 一.系统开发工具与环境搭建 1.系统设计开发工具 后端使用Java编程语言的Spring boot框架 项目架构&#xff1a;B/S架构 运行环境&#xff1a;win10/win11、jdk17 前端&#xff1a; 技术&#xff1a;框架Vue.js&#x…
阅读更多...
软件项目必须进行验收测试吗?专业验收测试报告如何获取?

软件项目必须进行验收测试吗?专业验收测试报告如何获取?

软件项目验收测试是一种关键的质量保证活动&#xff0c;旨在确保软件产品符合用户需求和预期功能。它通常是在软件开发完成后&#xff0c;由客户或第三方测试机构进行的最终测试环节。验收测试的目的是确认软件的性能、功能、安全性和其他特性&#xff0c;以确保交付的产品能够…
阅读更多...
不是 PHP 不行了，而是 MySQL 数据库扛不住啊

不是 PHP 不行了，而是 MySQL 数据库扛不住啊

大多数的业务场景下 PHP 还没有达到性能瓶颈&#xff0c;然而 MySQL 数据库就先行驾崩了。但我们总是不分青红皂白&#xff0c;一股脑的把原因归结于是 PHP 语言不行了&#xff0c;每当遇到这种情形我就会感叹到 PHP 的命真苦啊。PHP 作为一门优秀的开源编程语言&#xff0c;在…
阅读更多...
CAS存在的问题及在Java中的解决方式

CAS存在的问题及在Java中的解决方式

CAS 介绍 CAS 可以保证对共享变量操作的原子性 CAS全称Compare And Swap&#xff0c;比较与交换&#xff0c;是乐观锁的主要实现方式。CAS在不使用锁的情况下实现多线程之间的变量同步。ReentrantLock内部的AQS和原子类内部都使用了CAS。 CAS算法涉及到三个操作数&#xff1a;需…
阅读更多...
深度学习500问——Chapter17：模型压缩及移动端部署（4）

深度学习500问——Chapter17：模型压缩及移动端部署（4）

文章目录 17.9 常用的轻量级网络有哪些 17.9.1 SequeezeNet 17.9.2 MobileNet 17.9.3 MobileNet-v2 17.9.4 Xception 17.9 常用的轻量级网络有哪些 17.9.1 SequeezeNet SqueezeNet出自 F.N.landola, S.Han等人发表的论文《SqueezeNet&#xff1a;ALexNet-level accuracy with…
阅读更多...
python文件,异常,模块,正则思维导图

python文件,异常,模块,正则思维导图

python文件操作 python异常处理 python模块和包 python正则表达式
阅读更多...
强大的PDF到Word转换工具

强大的PDF到Word转换工具

Solid Converter&#xff1a;强大的PDF到Word转换工具推荐 在日常工作和学习中&#xff0c;PDF是最常用的文件格式之一。然而&#xff0c;编辑PDF文档并不总是那么方便&#xff0c;尤其是当你需要将PDF文件转换为Word文档时。Solid Converter 是一款强大的工具&#xff0c;专为…
阅读更多...
高效美发店运营：SpringBoot管理系统详解

高效美发店运营：SpringBoot管理系统详解

1系统概述 1.1 研究背景 随着计算机技术的发展以及计算机网络的逐渐普及&#xff0c;互联网成为人们查找信息的重要场所&#xff0c;二十一世纪是信息的时代&#xff0c;所以信息的管理显得特别重要。因此&#xff0c;使用计算机来管理美发门店管理系统的相关信息成为必然。开发…
阅读更多...
鸿蒙开发（NEXT/API 12）【HTTP数据请求】网络篇

鸿蒙开发（NEXT/API 12）【HTTP数据请求】网络篇

场景介绍 应用通过HTTP发起一个数据请求&#xff0c;支持常见的GET、POST、OPTIONS、HEAD、PUT、DELETE、TRACE、CONNECT方法。 接口说明 HTTP数据请求功能主要由http模块提供。 使用该功能需要申请ohos.permission.INTERNET权限。 接口名描述createHttp()创建一个http请求…
阅读更多...
解决SqlServer自增主键使用MybatisPlus批量插入报错问题

解决SqlServer自增主键使用MybatisPlus批量插入报错问题

报错 SqlServer 表中主键设置为自增&#xff0c;会报以下错误。 org.springframework.jdbc.UncategorizedSQLException: Error getting generated key or setting result to parameter object. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须执行该语句才能获…
阅读更多...
LangChain: AI大语言模型的新篇章

LangChain: AI大语言模型的新篇章

本文介绍了LangChain框架&#xff0c;它能够将大型语言模型与其他计算或知识来源相结合&#xff0c;从而实现功能更加强大的应用。接着&#xff0c;对LangChain的关键概念进行了详细说明&#xff0c;并基于该框架进行了一些案例尝试&#xff0c;旨在帮助读者更轻松地理解LangCh…
阅读更多...
springboot查询全部部门流程

springboot查询全部部门流程

前端发送请求后&#xff0c;会请求DeptController的方法list()。 package com.intelligent_learning_aid_system.controller;import com.intelligent_learning_aid_system.pojo.Dept; import com.intelligent_learning_aid_system.pojo.Result; import com.intelligent_learni…
阅读更多...
SpringBoot美发门店管理系统：提升服务效率

SpringBoot美发门店管理系统：提升服务效率

2相关技术 2.1 MYSQL数据库 MySQL是一个真正的多用户、多线程SQL数据库服务器。 是基于SQL的客户/服务器模式的关系数据库管理系统&#xff0c;它的有点有有功能强大、使用简单、管理方便、安全可靠性高、运行速度快、多线程、跨平台性、完全网络化、稳定性等&#xff0c;非常…
阅读更多...
360 度评估的优缺点

360 度评估的优缺点

什么是 360 度评估&#xff1f; “360 度评估是一种人才培养工具&#xff0c;用于为接受者提供全方位的反馈&#xff0c;这通常包括来自他们直接下属的向上反馈、来自与他们密切合作的同事的反馈&#xff0c;以及来自他们经理的向下反馈。” 通常&#xff0c;360 度评估是匿名…
阅读更多...
渗透测试之 域AD渗透手法【密码喷洒技术】手法详解 以及相关示例

渗透测试之 域AD渗透手法【密码喷洒技术】手法详解 以及相关示例

说明: 域内密码喷洒工具: Kerbrute DomainPasswordSpray.ps1 原理抓个包分析一下&#xff1a; 域内用户枚举攻击防御&#xff1a; 流量检测&#xff1a; 说明: 域内密码喷洒&#xff08;Password Spraying&#xff09;一般和域内用户名枚举一起使用。 域内密码喷洒工具:…
阅读更多...
SQL 干货 | 使用 EXISTS 编写 SELECT 查询

SQL 干货 | 使用 EXISTS 编写 SELECT 查询

基于 SQL 中的 EXISTS 运算符为我们提供了一种基于其他数据是否存在&#xff08;或不存在&#xff09;来检索数据的简便方法。更具体地说&#xff0c;它是一个逻辑运算符&#xff0c;用于评估子查询的结果&#xff0c;并返回一个布尔值&#xff0c;该值指示是否返回了行。尽管 …
阅读更多...
影刀RPA实战：制作Excel工资条

影刀RPA实战：制作Excel工资条

1.实战目标 使用Excel制作工资条是一种常见的做法&#xff0c;每个公司几乎都有这样的需求&#xff0c;我们先介绍下使用excel手动制作工资条的方法&#xff0c;看看不足之处&#xff0c;使用影刀RPA又会给我们带来怎样的便利&#xff0c;让我们更倾向于选择影刀来处理。 工资…
阅读更多...
链表的回顾与总结（一）正序、逆序、有序、插入、修改、删除

链表的回顾与总结（一）正序、逆序、有序、插入、修改、删除

逆序链表 #include<iostream> using namespace std; struct node {int data;node* next; };//最好不要全局定义指针&#xff0c;很麻烦 void show(node* head) {while (head){cout << head->data << ;head head->next;} } int main() {int i, j, k…
阅读更多...
关于相机的一些零碎知识点

关于相机的一些零碎知识点

热成像&#xff0c;英文为Thermal Imaging&#xff0c;例如型号500T&#xff0c;其实指的就是热成像500分辨率。 相机的CMOS&#xff0c;英文为Complementary Metal Oxide Semiconductor&#xff0c;是数码相机的核心成像部件&#xff0c;是一种互补金属氧化物导体器件。 DPI…
阅读更多...
最新文章

Copyright @ 2022~2023