MySQL 索引知识汇总

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

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

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

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

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

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

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

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

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

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

如果是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;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

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;

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name; \G
........

以上就是MySQL 索引知识汇总的详细内容，更多关于MySQL 索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL全文索引、联合索引、like查询、json查询速度哪个快

    查询背景 有一个表tmp_test_course大概有10万条记录,然后有个json字段叫outline,存了一对多关系(保存了多个编码,例如jy1577683381775) 我们需要在这10万条数据中检索特定类型的数据,目标总数据量:2931条 SELECT COUNT(*) FROM tmp_test_course WHERE `type`=5 AND del=2 AND is_leaf=1 我们在限定为上面类型的同时,还得包含下面任意一个编码(也就是OR查询) jy157768338177

  • MySQL数据库优化之索引实现原理与用法分析

    MySQL数据库优化之索引实现原理与用法分析

    本文实例讲述了MySQL数据库优化之索引实现原理与用法.分享给大家供大家参考,具体如下: 索引 什么是索引 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍.

  • Mysql索引类型与基本用法实例分析

    本文实例讲述了Mysql索引类型与基本用法.分享给大家供大家参考,具体如下: 索引 MySQL目前主要有以下几种索引类型: 普通索引 唯一索引 主键索引 组合索引 全文索引 - 普通索引 是最基本的索引,它没有任何限制. CREATE INDEX IndexName ON `TableName`(`字段名`(length)) - 唯一索引 与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一. CREATE UNIQUE INDEX index

  • mysql索引原理与用法实例分析

    本文实例讲述了mysql索引原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是索引 创建索引 普通索引 唯一索引 全文索引 单列索引 多列索引 查看索引 删除索引 首发日期:2018-04-14 什么是索引: 索引可以帮助快速查找数据 而基本上索引都要求唯一(有些不是),所以某种程度上也约束了数据的唯一性. 索引创建在数据表对象上,由一个或多个字段组成,这若干个字段组成"键"存储到数据结构中(B树或者哈希表).[可以根据数据结构分类成B树索引(innodb\myisam引

  • MySql 存储引擎和索引相关知识总结

    存储引擎 什么是数据库存储引擎? 数据库引擎是数据库底层软件组件,不同的存储引擎提供不同的存储机制,索引技巧,锁定水平等功能,使用不同的数据库引擎,可以获得特定的功能 如何查看引擎? --如何查看数据库支持的引擎 show engines; ​ --查看当前数据的引擎: show create table 表名\G ​ --查看当前库所有表的引擎: show table status\G 建表时指定引擎 create table yingqin (id int,name varchar(20))

  • MySQL8新特性之降序索引底层实现详解

    什么是降序索引 大家可能对索引比较熟悉,而对降序索引比较陌生,事实上降序索引是索引的子集. 我们通常使用下面的语句来创建一个索引: create index idx_t1_bcd on t1(b,c,d); 上面sql的意思是在t1表中,针对b,c,d三个字段创建一个联合索引. 但是大家不知道的是,上面这个sql实际上和下面的这个sql是等价的: create index idx_t1_bcd on t1(b asc,c asc,d asc); asc表示的是升序,使用这种语法创建出来的索引叫做

  • MySQL 的覆盖索引与回表的使用方法

    两大类索引 使用的存储引擎:MySQL5.7 InnoDB 聚簇索引 * 如果表设置了主键,则主键就是聚簇索引 * 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引 * 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引 InnoDB的聚簇索引的叶子节点存储的是行记录(其实是页结构,一个页包含多行数据),InnoDB必须要有至少一个聚簇索引. 由此可见,使用聚簇索引查询会很快,因为可以直接定位到行记录. 普通索引 普通索引也叫二级索引,除聚簇索引外的

  • MySQL 索引知识汇总

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度. 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车. 拿汉语字典的目录页(索引)打比方,我们可以按拼音.笔画.偏旁部首等排序的目录(索引)快速查找到需要的字. 索引分单列索引和组合索引.单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引.组合索引,即一个索引包含多个列. 创建索引时,你需要确保该索引是应用在

  • Mysql事务索引知识汇总

    目录 一.事务 1.事务特性 2.事务并发时出现的问题 二.索引 1.索引使用 2.索引类型 3.索引注意原则 三.SQL 1.SQL优点 2.SQL分类 3.SQL语法及关键字 四.数据库设计 1.数据库三大范式 2.数据库规范设计 一.事务 mysql事务是用于处理操作量大.复杂性高的数据 1. 事务特性 原子性:保证每个事务所有操作要么全部完成或全部不完成,不可能停滞在中间环节:如事务在执行过程中出现错误,则会回滚到一致性:事务开始结束后保证数据库的完整性没有被破坏,数据都是按照数据库要求

  • Mysql索引常见问题汇总

    Q1:数据库有哪些索引?优缺点是什么? 1.B树索引:大多数数据库采用的索引(innoDB采用的是b+树).能够加快访问数据的速度,尤其是范围数据的查找非常快.缺点是只能从索引的最左列开始查找,也不能跳过索引中的列,如果查询中有某个列用到了范围查询,则右边所有列都无法使用索引优化查找. 2.哈希索引:基于哈希表实现.在MySQL中,只有Memory引擎显式的支持哈希搜索.哈希查找的速度非常快,但哈希索引只包含哈希值和行指针,不存储字段值,所以不能用索引中的值来避免读取行,也不能进行排序.由于哈希

  • MySQL索引知识的一些小妙招总结

    MySQL索引知识的一些小妙招总结

    一.索引基本知识 1.1 索引的优点 大大减少了服务器需要扫描的数据量,加快数据库的检索速度 帮助服务器避免排序和临时表 将随机io变成顺序io 1.2 索引的用处 速查找匹配WHERE子句的行 从consideration中消除行,如果可以在多个索引之间进行选择,mysql通常会使用找到最少行的索引 如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行 当有表连接的时候,从其他表检索行数据 查找特定索引列的min或max值 如果排序或分组时在可用索引的最左前缀上完成的,则对表进行排序

  • MySQL索引失效的几种情况汇总

    一.索引不存储null值 更准确的说,单列索引不存储null值,复合索引不存储全为null的值.索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描. 为什么索引列不能存Null值? 将索引列值进行建树,其中必然涉及到诸多的比较操作.Null值的特殊性就在于参与的运算大多取值为null. 这样的话,null值实际上是不能参与进建索引的过程.也就是说,null值不会像其他取值一样出现在索引树的叶子节点上. 二.不适合键值较少的列(重复

  • MySQL日期时间函数知识汇总

    MySQL日期时间函数知识汇总

    目录 1.获取时间 2.日期与时间戳的转换 3.获取月份.星期.星期数.天数等函数 4.日期的操作函数 5.时钟和秒钟转换的函数 6.计算日期和时间的函数 6.1计算时间间隔 6.2时间计算 7.日期的格式化与解析 1. 获取时间 函数 功能 返回格式 CURDATE(), CURRENT_DATE() 返回当前日期,只包含年.月.日 2022-03-08 CURTIME(), CURRENT_TIME() 返回当前时间,只包含时.分.秒 19:35:46 NOW(), SYSDATE(), C

  • 分享15个Mysql索引失效的场景

    分享15个Mysql索引失效的场景

    目录 背景 数据库及索引准备 创建表结构 初始化数据 数据库版本及执行计划 1 联合索引不满足最左匹配原则 2 使用了select * 3 索引列参与运算 4 索引列参使用了函数 5 错误的Like使用 6 类型隐式转换 7.使用OR操作 8 两列做比较 9 不等于比较 10 is not null 11 not in和not exists 12 order by导致索引失效 13 参数不同导致索引失效 14 其他 小结 背景 无论你是技术大佬,还是刚入行的小白,时不时都会踩到Mysql数据库不

  • mongodb索引知识_动力节点Java学院整理

    mongodb索引知识_动力节点Java学院整理

    我们日常做开发都避免不了要对程序进行性能优化,而程序的操作无非就是CURD,通常我们又会花费50%的时间在R上面,因为Read操作对用户来说是非常敏感的,处理不好就会被人唾弃. 从算法上来说有5种经典的查找,具体的可以参见我的算法速成系列,这其中就包括我们今天所说的"索引查找",如果大家对mysql比较了解的话,相信索引查找能给我们带来什么样的性能提升吧. 我们首先插入10w数据,上图说话: 一:性能分析函数(explain) 好了,数据已经插入成功,既然我们要做分析,肯定要有分析的工

  • Java基础知识汇总

    Java基础知识 1.Java语言的优点: 1)Java是纯面向对象语言 2)与平台无关性,一次编译到处运行 3)Java提供了狠多内置类库 4)提供了对web应用的支持 5)具有较好的安全性(数组边界检测.Bytecode检测)和健壮性(强制型机制.垃圾回收器.异常处理) 6)去除c++难以理解的一些特性(头文件 指针 运算符重载 多重继承) 2.java与c++的异同: 1)Java为解释型语言,c++为编译型语言,java会慢但是跨平台 2)Jave为纯面向对象,c++既面向对象又能面向过

  • MySQL 索引分析和优化

    MySQL 索引分析和优化

    一.什么是索引? 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍. 假设我们创建了一个名为people的表: CREATE TABLE people ( p

随机推荐