MySQL整理----索引

最新推荐文章于 2025-12-20 14:42:23 发布
原创 最新推荐文章于 2025-12-20 14:42:23 发布 · 2.5k 阅读 · 5 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #索引 #数据库 #sql

本文详细介绍了MySQL索引的原理及不同类型，包括单列索引、组合索引、唯一索引等，并提供了创建、修改和删除索引的具体方法。此外，还讨论了索引的优点和缺点。

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。占用资源 第二个弊端

普通索引

最基本的索引类型，没有唯一性之类的限制。

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

    该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

  • ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

  • **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)😗*该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

主键索引

简称为主索引，数据库表中一列或列组合（字段）的值唯一标识表中的每一行。该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键索引。

候选索引

与主索引一样要求字段值的唯一性，并决定了处理记录的顺序。在数据库和自由表中，可以为每个表建立多个候选索引。

聚集索引

也称为聚簇索引，在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引， 即如果存在聚集索引，就不能再指定CLUSTERED 关键字。

索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引更适用于对很少对基表进行增删改操作的情况。

如果在表中创建了主键约束，SQL Server将自动为其产生唯一性约束。在创建主键约束时，指定了CLUSTERED关键字或干脆没有制定该关键字，SQL Sever将会自动为表生成唯一聚集索引。

优点

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

SELECT语句的关键字执行顺序

1.FROM 确定查询的表

2.WHERE 对数据按条件筛选

3.GROUP BY 对筛选后的数据分组

4.HAVING 对分组后的数据再次进行筛选

5.SELECT 生成结果集

6.ORDER BY 对结果集进行排序

什么是 ACID ？

  1. 原子性 Atomic 事务必须是原子工作单元 ( 不可分割 ) ；对于其数据修改，要么全都执行，要么全都不执行。

  2. 一致性 Consistent 操作前的数据，操作后的数据保持一致。

  3. 隔离性 Insulation 保证多用户并发访问的数据安全性，并发事务所作的修改必须与任何其它并发事务所作的修改隔离。

  4. 持久性 Duration 事务操作的数据 持久化到数据库当中 , 对于系统的影响是永久性的。

    项目中是如何进行具体的sql优化的？

    （1）选择最有效率的表名顺序

    （2）当只需要一行数据时候使用limit1；

    （3）SELECT子句中避免使用‘*’

    （4）用Where子句替换HAVING子句

    （5）通过内部函数提高SQL效率

    （6）避免在索引列上使用计算。

    （7）提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。

Mysql整理-索引
不务正业的猿
03-01 443
MySQL中的索引是一种数据库对象，可以提高数据检索的速度，类似于书籍的目录。它们是对数据库表中一列或多列的值进行排序的数据结构，可以让数据库查询引擎快速找到所需的行。索引对于提高数据库查询的性能至关重要，特别是在处理大量数据时。过多的索引会增加存储空间的需求，降低更新表的性能。避免在数据变化频繁的列上创建索引，因为这会降低写操作的效率。只对值的一部分进行索引，可以节省空间，但可能降低一些效率。最常用的索引类型，适用于全键值、键值范围或键值前缀查找。索引的列顺序非常重要，影响索引的效果。
普通索引 唯一索引 主键索引 候选索引
斜阳雨陌
03-14 7250
普通索引 最基本的索引类型，没有唯一性之类的限制。普通索引可以通过以下几种方式创建： 创建索引，例如CREATE INDEX ON tablename (列的列表)； 修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表)； 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字
找工作之数据库基础知识总结
weixin_43215948的博客
07-01 802
1.索引 ①主索引：主索引是一种只能在数据库表中建立不能在自由表中建立的索引。在指定的字段或表达式中，主索引的关键字绝对不允许有重复值。 ②候选索引：和主索引类似，它的值也不允许在指定的字段或表达式中重复。一个表中可以有多个候选索引。 ③唯一索引：唯一索引允许关键字取重复的值。当有重复值出现时，索引文件只保存重复值的第1次出现。提供唯一索引主要是为了兼容早期的版本。 ④普通索引：普通索引允许关键字段有相同值。在一对多关系的多方，可以使用普通索引。 2.数据库的3中基本语言 DML（data manip
MYSQL的候选索引怎么建_Mysql_索引怎么建(inoodb)
weixin_35758982的博客
01-19 741
如何建索引索引太多，影响性能，每一次修改(增，删，改)都涉及到索引的改动。所以索引应该是在满足需求的情况下，尽可能少的建。然后利用好联合索引。首先看索引的概念密集索引、稀疏索引密集索引(唯一)(1)若一个主键被定义，该主键则作为密集索引；(2)若没有主键被定义，改表的第一个唯一非空索引则作为密集索引；(3)若不满足以上条件，InnoDB内部会生成一个隐藏主键(密集索引)针对大部分情况，我们的主键就...
【MySQL】索引总结归纳
m0_74282926的博客
11-24 621
索引设计场景总结 原则编号 原则描述 补充说明与示例 1 针对数据量较大且查询频繁的表建立索引。 - 小表数据量少时，全表扫描效率可能更高。- 当表超过 1000 条记录且频繁查询时，建议建立索引。 2 针对常作为查询条件 (WHERE)、排序 (ORDER BY)、分组 (GROUP BY) 的字段建立索引。 - 示例：查询 SELECT * FROM orders WHERE customer_id = 1，建议在 customer_id 上建索引。- 对于 ORDER BY cre
笔记整理-mysql索引
qq_42114504的博客
03-13 2014
MySQL的索引分类 索引分类 1.普通索引index :加速查找 2.唯一索引 主键索引：primary key ：加速查找+约束（不为空且唯一） 唯一索引：unique：加速查找+约束 （唯一） 3.联合索引 -primary key(id,name):联合主键索引 -unique(id,name):联合唯一索引 -index(id,name):联合普通索引 4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。 创建索引 Alter table ‘tablename’add prima
MySQL高级-索引优化（超详细）
SIESTA030的博客
02-14 7003
性能分析 MySQL Query Optimizer Mysql中由专门负责优化SELECT语句的优化器，主要功能就是通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的，但**不一定是DBA觉得最优的，这部分最耗时间**）。 当客户端向MySQL请求一条Query的时候，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MQO会对整条Query进行优化，处理掉一些常量表达式的预算，将其换算成常量值并对，Que
【Mysql 底层原理】MySQL 索引原理：B-Tree 和 B+Tree 的区别与应用
激流丶的博客
10-28 2561
博主从事应用安全和大数据领域，有8年研发经验，5年面试官经验，Java技术专家，WEB架构师，阿里云专家博主，华为云云享专家，51CTO 专家博主。
MySQL实战优化-整理版
04-29
- **存储格式**：MySQL数据文件通常采用特定格式存储在磁盘上，如InnoDB使用.ibd文件存储表数据和索引。 - **文件组织**：InnoDB通过B+树结构组织索引，每个索引对应一个或多个数据文件。 - **空间分配**：数据库...
mysql-5.5.21-win32.txt
01-21
虽然提供的链接和提取码无法直接用于下载，但我们可以基于这些信息来整理关于MySQL 5.5.21的相关知识点。 ### MySQL 5.5.21 版本概述 MySQL 5.5.21是MySQL 5.5系列中的一个版本，该版本发布于2011年，并且是MySQL ...
主索引 普通索引 候选索引 唯一索引
11-01 4160
1.普通索引 普通索引（由关键字KEY或INDEX定义的索引）唯一的任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引 2.唯一索引 在某个数据列只包含彼此不同的值，在为这个数据列创建索引时就应该用关键字UNIOUE把它定义为一个唯一的索引 这么做的好处：一是简化了MySQ
索引相关操作命令
qq_43496435的博客
07-26 1139
1、创建不同引擎的表 # 创建一个MyISAM引擎的表 create table my_test( id int primary key auto_increment, name varchar(20) ) engine = MyISAM; # 创建一个Innodb引擎的表 不写引擎类型默认innodb create table in_test( id int primary key auto_increment, name varchar(20)...
基于 MySQL Monitoring and Management 的性能保障实践
星哥玩云
12-17 960
摘要：本文探讨MySQL Monitoring and Management（MMM）工具在数据库性能保障中的实践应用。MMM作为官方推荐的轻量化监控方案，具有全栈指标覆盖、易部署和可视化告警等优势。重点分析了查询执行时间、连接数和缓存命中三大核心指标的监控逻辑与优化策略，并提供了从部署到日常监控的完整操作流程。通过电商平台案例展示了MMM在实际场景中的故障定位与性能优化价值，最后提出指标联动、动态阈值调整等优化建议，为构建高效的MySQL监控体系提供实用指南。
MySQL 无缝迁移到达梦：前置方法与全面评估分析指南
最新发布
数记
12-20 766
MySQL迁移至达梦数据库（DM）的兼容性评估与迁移策略摘要 本文系统分析了从MySQL迁移到达梦数据库的关键步骤： 兼容性评估：检查表结构、SQL语法、存储过程等对象的兼容性，对比事务隔离、字符集等特性差异，使用达梦工具（如DTS、SQL Checker）辅助分析。 工作量分级：根据适配难度分为低（直接兼容）、中（语法调整）、高（代码重写）和阻断项（需业务改造），示例评估显示存储过程改造占比最高（40%）。 迁移实施：分阶段进行试点验证、灰度同步（dmdrs工具）和全量切换，结合达梦DTS工具自动转换，配
Canal深度解析：MySQL增量数据订阅与消费实战
qq_41262225的博客
12-18 818
Canal实战指南：MySQL增量数据同步利器 Canal是阿里巴巴开源的MySQL增量数据订阅组件，基于MySQL主从复制协议实现毫秒级数据同步。核心原理是伪装成MySQL从库，通过解析binlog获取数据变更，支持Row格式的binlog解析。典型应用场景包括缓存同步、实时数据分析、数据异构迁移等。 Canal架构包含三个核心组件：EventParser负责连接MySQL并解析binlog；EventSink进行数据过滤和路由；EventStore存储事件数据。部署时需配置MySQL开启binlog并设
mysql数据库 - 查询
03-15
<think>好的，我现在需要帮助用户解决如何在MySQL中执行数据库查询的问题。首先，我需要回顾用户提供的引用内容，确保回答准确。用户提到了引用[3]中的内容，其中提到使用Python执行MySQL查询，使用%s作为占位符，以及游标的遍历方法。 接下来，我需要按照用户的要求，结构清晰地分步骤解释。首先应该提到连接到数据库，然后创建游标，接着执行查询语句，处理结果，最后关闭连接。同时，用户要求行内数学表达式用$...$，但这里可能用不上数学公式，但需要确保代码块正确。 需要注意的是，用户希望回答中使用中文，并且生成相关问题。引用的引用标记需要自然添加在相关段落末尾。例如，在讲执行查询时，引用[3]提到使用占位符和遍历游标，所以需要在对应部分加上[^3]。 可能还需要检查是否有其他引用相关的内容，比如引用[2]提到的执行计划，可能在优化查询性能时提到，但用户的问题主要是如何执行查询，所以可能不需要深入。但生成的相关问题中可以包括性能优化，这样可以利用引用[2]。 然后，生成相关问题时要考虑用户可能感兴趣的后续问题，例如如何优化查询、防止SQL注入、使用连接查询等。确保每个问题都与主题相关，并且有对应的引用支持。 最后，确保代码示例正确，使用Python和MySQL的连接方法，包括导入库、建立连接、创建游标、执行查询、获取结果和关闭连接。同时，提醒用户使用预处理语句来防止SQL注入，引用[3]中的内容支持这一点。 现在需要将这些思考整理成一个连贯的回答，分步骤说明，并附上代码示例和相关问题。同时，确保引用标记正确放置，符合用户的要求。</think>在MySQL中执行数据库查询需要遵循以下步骤，结合代码示例和最佳实践进行说明： ### 一、建立数据库连接 使用Python操作MySQL前，需要先安装mysql-connector-python库： ```bash pip install mysql-connector-python ``` 建立连接的代码实现： ```python import mysql.connector # 创建数据库连接 conn = mysql.connector.connect( host="localhost", user="your_username", password="your_password", database="your_database" ) ``` ### 二、创建游标对象 游标（Cursor）是执行查询和获取结果的核心工具： ```python cursor = conn.cursor() ``` ### 三、执行查询语句 #### 1. 基础查询 ```python query = "SELECT * FROM employees WHERE department = %s" params = ("IT",) cursor.execute(query, params) # 使用参数化查询防止SQL注入[^3] ``` #### 2. 获取查询结果 ```python # 获取单条记录 result = cursor.fetchone() # 获取前N条记录 results = cursor.fetchmany(size=5) # 获取全部记录 all_results = cursor.fetchall() # 直接遍历游标（适合大数据量） for (id, name, department) in cursor: print(f"员工ID：{id}, 姓名：{name}") ``` ### 四、处理查询结果 建议使用字典游标获取更易读的结果： ```python dict_cursor = conn.cursor(dictionary=True) dict_cursor.execute("SELECT * FROM products") for row in dict_cursor: print(row["product_name"], row["price"]) ``` ### 五、关闭连接 ```python cursor.close() conn.close() ``` ### 六、性能优化建议 1. 使用`EXPLAIN`分析执行计划[^2]： ```sql EXPLAIN SELECT * FROM orders WHERE total_amount > 1000; ``` 2. 合理使用索引 3. 限制返回字段，避免`SELECT *`
评论 8
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值