MySQL中索引的定义以及操作新手教程

目录
  • 索引的定义
  • 索引的类型
    • 普通、唯一、主键和全文索引
      • 普通索引（INDEX）
      • 唯一索引（UNIQUE INDEX）
      • 主键索引（PRIMARY KEY）
      • 全文索引（FULLTEXT）
    • 单列索引和组合索引
    • 聚集索引和非聚集索引
  • 索引的创建原则
  • 索引操作
    • 创建索引
    • 查看索引
    • 删除索引
  • 总结

索引的定义

数据库中的索引就像一本书的目录，可以据此快速定位数据库中相关数据的所在位置。

在数据库中，索引被定义为一种特殊的数据结构，由数据库中的一列或多列组合而成，可以用来快速查询数据表中某一特定值的记录。

索引是在表的字段的基础上建立的一种数据库对象，它由DBA或者表的拥有者创建或撤销，他是创建表与表之间关联关系的基础。

索引的类型

普通、唯一、主键和全文索引

普通索引（INDEX）

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入空值和重复值。

创建普通索引的几种方式：

# 1.直接创建索引
create index index_name on table(column(length));

# 2. 以修改表结构创建
alter table table_name add index index_name on column(length);

# 3. 创建表的同时创建索引
create table user(
	id CHAR(6) not null,
	name CHAR(255) not null,
	primary key(id),
	index user_name(name(length));
)

唯一索引（UNIQUE INDEX）

唯一索引指索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引的几种方式：

# 1.直接创建索引
create unique index index_name on table(column(length));

# 2. 以修改表结构创建
alter table table_name add unique index index_name on column(length);

# 3. 创建表的同时创建索引
create table user(
	id CHAR(6) not null,
	name CHAR(255) not null,
	primary key(id),
	unique index user_name(name(length));
)

主键索引（PRIMARY KEY）

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

全文索引（FULLTEXT）

全文索引的类型为FULLTEXT，表示在定义索引的列上支持值的全文查找，允许插入重复值或空值。

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

MySQL中只有MyISAM存储引擎支持全文索引。

全文索引可以在进行创建表、更新表和创建索引时使用，目前只支持CHAR、VARCHAR、TEXT列创建全文索引。

单列索引和组合索引

  • 单列索引

单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

  • 组合索引

组合索引是指组合表的多个字段创建的索引，，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。遵循最左前缀匹配原则

聚集索引和非聚集索引

  • 聚集索引

聚集索引是索引结构和数据一起存放的索引，索引结构的叶子节点上包含着该行的所有信息，找到索引也就找到了数据。

  • 非聚集索引

非聚集索引是索引结构和数据分开存放的索引，索引结构的叶子节点指向了数据的对应行，当需要访问数据时，在内存中先搜索索引，然后通过索引找到磁盘相应数据。

聚集索引 非聚集索引
一个表只能有一个 一个表可以有多个
物理连续 逻辑连续，物理不连续
查询快，插入慢（需要移动物理位置） 回表查询
索引的叶节点就是数据节点 索引的叶节点仍然是索引节点，通过一个指针指向对应的数据块。

索引的创建原则

1. 创建索引由专人完成

  • 索引由DBA或表的拥有者负责创建和撤销，其他用户不能随意操作。
  • 索引由系统自动选择，或由用户打开，用户可执行重建索引操作。

2.是否创建索引取决于表的数据量

  • 基本表中记录的数量越多，记录越长，越有必要创建索引。创建索引后，查询速度的提升效果会很明显。要避免对经常更新的表创建过多的索引，索引中的列也要尽可能少。
  • 数据量小的表最好不要使用索引。由于数据较少，查询花费的时间可能比遍历索引的时间还要短，因此，创建索引可能不会产生优化效果。对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
  • 索引要根据数据查询或数据处理的要求确定是否创建。对于查询频度高、实时性要求高的数据一定要建立索引。

3.索引数量要适度

  • 索引文件占用文件目录和存储空间，因此索引过多会加重系统负担。
  • 索引需要自身维护。当基本表的数据增加、删除或修改时，索引也会进行调整和更新，索引文件也要随之变化，以保持与基本表一致。
  • 索引过多会影响数据增、删、改的速度。索引并非越多越好，-张表中如果有大量的索引，不仅占用磁盘空间，而且还会影响INSERT、DELETE、UPDATE等操作的性能。

4.避免使用索引的情形

(1)包含太多重复值的字段。

(2)查询中很少被引用的字段。

(3)值特别长的字段。

(4)查询返回率很高的字段。

(5)具有很多NULL值的字段。

(6)需要经常增、删、改的字段。

(7)记录较少的基本表。

(8)需要频繁、大批量进行数据更新的基本表。

索引操作

创建索引

create table <table_name> [<fields_name> type]
[UNIQUE|FULLTEXT][INDEX|KEY]
<index_name> (<column_name > [length]) [ASC|DESC]

查看索引

show index from <table_name> [from <database_name>]

删除索引

1.删除索引

drop index <index_name> from <table_name>

2.通过修改表结构删除

alter table <table_name> drop index <index_name>

总结

到此这篇关于MySQL中索引的定义以及操作的文章就介绍到这了,更多相关MySQL索引定义及操作内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • mysql 添加索引 mysql 如何创建索引

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

  • MySQL索引类型总结和使用技巧以及注意事项

    在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

  • MySQL查看、创建和删除索引的方法

    本文实例讲述了MySQL查看.创建和删除索引的方法.分享给大家供大家参考.具体如下: 1．索引作用 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率.特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍. 例如,有3个未索引的表t1.t2.t3,分别只包含列c1.c2.c3,每个表分别含有1000行数据组成,指为1-1000的数值,查找对应值相等行的查询如下所示. SELECT c1,c2,c3 FROM t1,t2,t3

  • MYSQL索引无效和索引有效的详细介绍

    1.WHERE字句的查询条件里有不等于号(WHERE column!=...),MYSQL将无法使用索引2.类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=...),MYSQL将无法使用索引3.在JOIN操作中(需要从多个数据表提取数据时),MYSQL只有在主键和外键的数据类型相同时才能使用索引,否则即使建立了 索引也不会使用4.如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP,MYSQL只有在搜索模板的第一个字符不是通配符的情况下才

  • mysql性能优化之索引优化

    mysql性能优化之索引优化

    作为免费又高效的数据库,mysql基本是首选.良好的安全连接,自带查询解析.sql语句优化,使用读写锁(细化到行).事物隔离和多版本并发控制提高并发,完备的事务日志记录,强大的存储引擎提供高效查询(表记录可达百万级),如果是InnoDB,还可在崩溃后进行完整的恢复,优点非常多.即使有这么多优点,仍依赖人去做点优化,看书后写个总结巩固下,有错请指正. 完整的mysql优化需要很深的功底,大公司甚至有专门写mysql内核的,sql优化攻城狮,mysql服务器的优化,各种参数常量设定,查询语句优化,主

  • Mysql索引会失效的几种情况分析

    Mysql索引会失效的几种情况分析

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 3.like查询是以%开头 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况show status li

  • Mysql使用索引的正确方法及索引原理详解

    一 .介绍 为何要有索引? 一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重.说起加速查询,就不得不提到索引了. 什么是索引? 索引在MySQL中也叫做"键",是存储引擎用于快速找到记录的一种数据结构.索引对于良好的性能 非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要. 索引优化应该是对查询性能优化最有效的手段了.索引能

  • MySQL 索引分析和优化

    MySQL 索引分析和优化

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

  • MySQL中索引的定义以及操作新手教程

    目录 索引的定义 索引的类型 普通.唯一.主键和全文索引 普通索引(INDEX) 唯一索引(UNIQUE INDEX) 主键索引(PRIMARY KEY) 全文索引(FULLTEXT) 单列索引和组合索引 聚集索引和非聚集索引 索引的创建原则 索引操作 创建索引 查看索引 删除索引 总结 索引的定义 数据库中的索引就像一本书的目录,可以据此快速定位数据库中相关数据的所在位置. 在数据库中,索引被定义为一种特殊的数据结构,由数据库中的一列或多列组合而成,可以用来快速查询数据表中某一特定值的记录.

  • MySQL中索引与视图的用法与区别详解

    前言 本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 索引 一.概述 所有的Mysql列类型都可以被索引. mysql支持BTREE索引.HASH索引.前缀索引.全文本索引(FULLTEXT)[只有MyISAM引擎支持,且仅限于char,varchar,text列].空间列索引[只有MyISAM引擎支持,且索引的字段必须非空],但不支持函数索引. MyISAM和InnoDB存储引擎的表默认创建BTREE索引,

  • MySQL中索引失效的常见场景与规避方法

    前言 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时

  • 深入了解MySQL中索引优化器的工作原理

    深入了解MySQL中索引优化器的工作原理

    目录 本文导读 一.MySQL 优化器是如何选择索引的 1.MySQL数据库组成 2.MySQL数据库成本计算 二.MySQL查询成本 三.SELECT 执行过程 总结 本文导读 本文将解读MySQL数据库查询优化器(CBO)的工作原理.简单介绍了MySQL Server的组成,MySQL优化器选择索引额原理以及SQL成本分析,最后通过 select 查询总结整个查询过程. 一.MySQL 优化器是如何选择索引的 下面我们来看这张表,SUB_ODR_ID字段创建了相关的 2 个索引,根据我们前面

  • Mysql中的触发器定义与使用

    Mysql中的触发器定义与使用

    目录 一.触发器的介绍 二.触发器的语法 (1)insert触发器 (2)update触发器 (3)delete触发器 一.触发器的介绍 触发器是与表有关的数据库对象,指在insert/update/delete 之前或之后,触发并执行 触发器中定义的SQL语句集合.触发器的这种特性可以协助应用在数据库端确保数据的 完整性,日志记录,数据校验等操作. 使用别名old和new来引用触发器中发生变化的记录内容,这与其它的数据库是相似的. 现在触发器还只支持行级触发,不支持语句级触发. 触发器类型  

  • MySQL中实现插入或更新操作（类似Oracle的merge语句）

    如果需要在MySQL中实现记录不存在则insert,不存在则update操作.可以使用以下语句: 更新一个字段: INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE KEY UPDATE columnA=IF(columnB>0,1,columnA) 更新多个字段: INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE KE

  • MySQL中ROUND函数进行四舍五入操作陷阱分析

    本文实例讲述了MySQL中ROUND函数进行四舍五入操作陷阱.分享给大家供大家参考,具体如下: 在MySQL中, ROUND 函数用于对查询结果进行四舍五入,不过最近使用ROUND函数四舍五入时意外发现并没有预期的那样,本文将这一问题记录下来,以免大家跟我一样犯同样的错误. 问题描述 假如我们有如下一个数据表 test ,建表语句如下 CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT, field1 bigint(10) DEFAULT

  • MySQL中的binary类型使用操作

    MySQL中的binary类型使用操作

    本文主要向大家介绍了MySQL数据库之MySQL的binary类型操作,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助. 示例数据表: CREATE TABLE test_bin ( bin_id BINARY(16) NOT NULL ) Engine=InnoDB; 插入数据(内容是一个32位的UUID字符串值): INSERT INTO test_bin(bin_id) VALUES(UNHEX('FA34E10293CB42848573A4E39937F479'));

  • Mysql中索引和约束的示例语句

    外键 查询一个表的主键是哪些表的外键 SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'mydbname' AND REFERENCED_TABLE_NAME = '表名'; 导出所有外键语句 SELECT CONCAT('ALTER

  • MySQL中索引优化distinct语句及distinct的多字段操作

    MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB;

随机推荐