MySQL数据库中的索引知识

2024-06-22 22:36

本文主要是介绍MySQL数据库中的索引知识,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

MySQL数据库中索引的作用是用来加快数据的查询速度。

索引  index(表的层面)

在数据库中使用select来查询数据的时候会一条一条得去查询符合要求的数据,而索引就相当于在这张表中依据某一个字段的数值给这张表的数据创建了一个目录。目录帮助我们快速的去定位数据。

在表中创建索引的时候要挑选合适的字段来创建索引,如果为这张表创建的索引挑选的字段不合适的话,此时依据索引查询数据的速度不增反降。

如上图所示,在实际应用场景中我们需要依据一段时间之内用户的行为来将合适的字段创建索引。

如果说在一段时间以内,用户常常根据商品的价格来搜索商品,此时我们就需要将这张表的索引创建在价格从字段上。此时数据库会自动按照价格的数值来进行相应的排序,然后生成一个目录。

如果用户的搜索行为发生改变的话,此时我们就需要将原来表中的索引删除,重新创建一个索引。

在创建索引的时候不适合将索引创建在数据经常需要发生变动的字段上。

注意点:1.不适合在经常变动的数据上建立索引。

2.建立在合适的字段上{根据前端业务的用户行为}

MySQL数据库默认会在主键字段上自动创建索引,

查看表的索引

如上图所示,在MySQL数据库中使用SQL语句show index from 表的名称;

来查看这张表的索引,此时MySQL数据库软件会给出一个表,在这张表中有一行内容就代表有一个索引,一般在MySQL数据库中都会有这样一个默认行为即自动为拥有主键属性的字段去创建索引。

创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

如上图所示,是使用help create index;在MySQL数据库上去查询如何创建索引的SQL语句。

即如果想去在一张表的某一个字段上去创建索引,就需要使用语句

create index 自己起的索引名称

on 表的名称(表的字段名称,表的字段名称);

如上图所示,在使用show index from 表名称;的表格中的key_name是索引的名称。

如上图所示,使用SQL语句create index 创建的索引名称 on 表的名称 (字段名称);

可以在表中创建索引,使用show index from 表的名称;

在日常工作中,MySQL数据库工作人员常常使用字段_key的方式来为创建的索引命名。

删除索引

如上图所示,使用命令drop index 索引名称 on 表名称

验证索引对加快搜索数据的帮助

在MySQL内部有一个查询分析器的东西。这个查询分析器可以帮助我们分析select语句是如何帮助获取到数据的。

explain就是MySQL中的查询分析器。

如上图所示,key的结果可以说明在使用select语句查询表中信息的时候是否使用到了索引,当为null的时候表示没有使用到索引。而rows表示使用select语句调查表中的信息的时候调查了表的多少行数据。

如上图所示,当在表中使用语句create index 索引名称 on 表(字段名称);

使用explain select * from 表名称 where 条件;

可以查看到select语句的执行过程。

什么情况下的select语句是不能触发字段的索引的?

当你写的select语句触发了数据类型转换的时候,就不能触发索引。

如上图所示,当使用select语句中使用了比较查询的时候,或者在等值查询的时候使用了“5000”这种将字符串=数字,的数据类型转换的时候同样不能触发索引。

在SQL语句中使用select语句去查询数据的时候什么情况是用不到索引的?

在使用SQL的SELECT语句查询数据时,以下情况可能不会使用索引,从而可能影响查询性能:

  1. 全表扫描更快: 当表中的数据量非常小,全表扫描比使用索引进行查找更快时,数据库可能选择不使用索引。

  2. 使用函数或表达式: 在查询条件中,如果对索引列使用了函数、表达式或计算(如DATE(create_time)age - 1),这通常会导致索引失效。

  3. 不等于(!=)和NOT IN操作符: 使用!=NOT IN操作符可能导致索引不被使用,特别是当这些操作符与其他条件结合使用时。

  4. 类型不匹配: 如果查询条件中的数据类型与索引列的数据类型不一致,可能导致索引失效。

  5. LIKE操作符以通配符开头: 如果LIKE子句以通配符(如'%abc')开头,索引通常不会被利用,因为这要求对列的每一项做全值比较。

  6. OR条件: 当查询条件中包含多个通过OR连接的条件时,除非每个条件的列都有索引且优化器选择使用这些索引,否则索引可能不会被利用。

  7. 多列索引未使用第一列: 对于复合索引(多列索引),如果查询条件没有首先使用索引的第一列,索引可能不会被使用。

  8. 索引列参与计算: 在条件中,如果对索引列进行了数学运算(+,-,*,/,!)或其他计算,索引可能不会被使用。

  9. 字段编码不一致: 在关联查询中,如果关联字段的字符编码不一致,可能导致索引失效。

  10. 索引覆盖未满足: 如果查询需要从表中检索大量数据,而不仅仅是索引包含的列(即没有实现索引覆盖),数据库可能决定放弃使用索引而进行全表扫描。

了解这些情况有助于在设计查询和索引策略时做出更优化的选择,以提高查询效率。

这篇关于MySQL数据库中的索引知识的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1085601

相关文章

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更