MySQL索引概念及七种索引类型分享介绍

目录
  • 1 索引的概念
  • 2 索引的类型

1 索引的概念

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构，这也是索引最基本的功能。

索引对于良好的性能非常关键。数据量越大时，索引对性能的影响也越重要，好的索引可以将查询性能提高几个数量级。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但是在数据量逐渐增大时，糟糕的索引会使MySQL的性能急剧的下降。

索引优化是查询性能优化最有效的手段。

如果想要在一本书中找到某个特定主题，一般会先看书的目录，找到对应的页码，然后直接翻到对应的页码即可查看。在MySQL中，存储引擎用类似的方法使用索引，首先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。简单的说，数据库索引类似于书前面的目录，能加快数据库的查询速度。

如下查询：

select name from user where user_id = 5

如果user_id列上建有索引，则MySQL将使用该索引找到user_id 为 5的行，即MySQL现在索引上按值进行查找，然后返回包含该值的数据行。

索引可以一个或多个列的值，如果索引包含多个列，那么列的顺序也很重要，因为MySQL只能高效地使用最左前缀列。

2 索引的类型

按照功能逻辑区分，MySQL目前主要有以下索引类型：

主键索引 ：

  • 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
  • ALTER TABLE table_name ADD PRIMARY KEY (column);

普通索引：

  • MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和NULL值。一个表允许多个列创建普通索引。
  • ALTER TABLE table_name ADD INDEX index_name (column);

唯一索引：

  • 索引列中的值必须是唯一的，但是允许NULL值。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。一个表允许多个列创建唯一索引。
  • ALTER TABLE table_name ADD UNIQUE (column);

全文索引：

  • 主要是为了快速检索大文本数据中的关键字的信息。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引，基于倒排索引，类似于搜索引擎。MyISAM存储引擎支持全文索引，InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
  • ALTER TABLE table_name ADD FULLTEXT (column);

前缀索引：

  • 在文本类型如BLOB、TEXT或者很长的VARCHAR列上创建索引时，可以使用前缀索引，数据量相比普通索引更小，可以指定索引列的长度，但是数值类型不能指定。
  • ALTER TABLE table_name ADD KEY(column_name(prefix_length));

组合索引：

  • 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。
  • 主键索引、普通索引、唯一索引等都可以使用多个字段形成组合索引。例如，ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 );

空间索引 ：

  • MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

到此这篇关于MySQL索引概念及七种索引类型分享介绍的文章就介绍到这了,更多相关MySQL索引 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 简单介绍下MYSQL的索引类型

    一.介绍一下索引的类型 Mysql常见索引有:主键索引.唯一索引.普通索引.全文索引.组合索引 PRIMARY KEY(主键索引) ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`) INDEX(普通索引)      ALTER TABLE `table_name` ADD INDEX index_name ( `colu

  • Mysql 数据库结构及索引类型

    Mysql 数据库结构及索引类型

    目录 前言 数据库索引的数据结构 聚集索引 辅助索引 索引管理 联合索引 覆盖索引 总结 前言 数据库索引是mysql数据库中重要的组成部分,是数据库查询数据速度提升的关键,本文将介绍数据库索引的一些内容. 数据库索引的数据结构 在数据库中的索引方法中,有TREE和HASH两种方法,HASH是经常使用的,本文中主要介绍TREE的数据结构.B+Tree 的高度一般是2-4层,也就是说查找一条数据记录,最多使用 2-4 次 IO,当前一般的机械磁盘每秒至少可以做 100 次 IO, 2-4 词的 I

  • Mysql索引的类型和优缺点详解

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针. 注: [1]索引不是万能的!索引可以加快数据检索操作,但会使数据修改操作变慢.每修改数据记录,索引就必须刷新一次.为了在某种程序上弥补这一缺陷,许 多SQL命令都有一个DELAY_KEY_WRITE项.这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进 行刷新,对索引的刷新将等到全部记录插入/修改完毕之后再进行.在需要把许多新记录插入某个数据

  • MySQL索引的各种类型

    什么是索引? 索引是数据库存储引擎用于快速查找到指定数据的一种数据结构. 可以用新华字典做类比:如果新华字典中对每个字的详细解释是数据库中表的记录,那么按部首或拼音等排序的目录就是索引,使用它可以让我们快速查找的某一个字详细解释的位置. 在MySQL中,存储引擎也是用了类似的方法,先在索引中找到对应的值,然后再根据匹配的索引值找到对应表中记录的位置. 面试中为什么问索引? 之所以在索引在面试中经常被问到,就是因为:索引是数据库的良好性能表现的关键,也是对查询能优化最有效的手段.索引能够轻易地把查

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

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

  • MySQL隐式类型转换导致索引失效的解决

    目录 问题 复现 隐式转换 总结 参考 问题 在工作中发现,有一个接口只执行一条SQL查询语句,并且SQL明明使用了主键列,但是速度很慢. 在MySQL中EXPLAINN后发现,执行时并没有使用主键索引,而是进行了全表扫描. 复现 数据表DDL如下,使用 user_id 作为主键索引: CREATE TABLE `user_message` ( `user_id` varchar(50) NOT NULL COMMENT '用户ID', `msg_id` int(11) NOT NULL COM

  • 详解mysql索引总结----mysql索引类型以及创建

    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车.对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢.还是以WordPress来说,其多个数据表都会对经常被查询的字段添加索引,比如wp_comments表中针对5个字段设计了BTREE索引. 一个简单的对比测试 以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万条

  • MySQL索引类型Normal、Unique和Full Text的讲解

    MySQL的索引类型有普通索引(normal),唯一索引(unique)和全文索引(full text),合理使用索引可大大提升数据库的查询效率,下面是三种类型的索引的介绍 normal:这是最基本的索引,它没有任何限制,MyIASM中默认的BTREE类型的索引,是我们大多数情况下用到的索引. unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复.例如身份证号用作索引时,可设置为unique. full text : 表示全文搜索的索引,仅可用于 MyISAM 表. FULLT

  • 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索引概念及七种索引类型分享介绍

    目录 1 索引的概念 2 索引的类型 1 索引的概念 索引(在MySQL中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构,这也是索引最基本的功能. 索引对于良好的性能非常关键.数据量越大时,索引对性能的影响也越重要,好的索引可以将查询性能提高几个数量级.在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但是在数据量逐渐增大时,糟糕的索引会使MySQL的性能急剧的下降. 索引优化是查询性能优化最有效的手段. 如果想要在一本书中找到某个特定主题,一般会先看书的目录,找

  • MySQL查询性能优化七种方式索引潜水

    MySQL查询性能优化七种方式索引潜水

    目录 前言: 有读者可能会一脸懵? 啥是索引潜水? 你给起的名字的吗?有没有索引蛙泳? 这个名字还真不是我起的,今天要讲的知识点就叫索引潜水(Index dive) . 先要从一件怪事说起: 我先造点数据复现一下问题,创建一张用户表: CREATE TABLE `user` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `name` varchar(100) NOT NULL DEFAULT '' COM

  • MySQL七种JOIN类型小结

    MySQL七种JOIN类型小结

    在开始之前,我们创建两个表用于演示将要介绍的其中JOIN类型. 建表 CREATE TABLE `tbl_dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `locAdd` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREAT

  • MySQL触发器概念、原理与用法详解

    本文实例讲述了MySQL触发器概念.原理与用法.分享给大家供大家参考,具体如下: 1.触发器的概念 触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete, update)时就会激活它执行.--百度百科 上面是百度给的触发器的概念,我理解的触发器的概念,就是你执行一条sql语句,这条sql语句的执行会自动去触发执行其他的s

  • mysql外键的三种关系实例详解

    mysql外键的三种关系实例详解

    本文实例讲述了mysql外键的三种关系.分享给大家供大家参考,具体如下: 因为有foreign key的约束,使得两张表形成了三种了关系: 多对一 多对多 一对一 一对多或多对一 多对一 create table press( id int primary key auto_increment, name varchar(20) ); create table book( id int primary key auto_increment, name varchar(20), press_id

  • Mysql表的七种类型详细介绍

    学习Mysql数据库,Mysql表类型都有哪些是一定需要知道的,下面就为您介绍七种Mysql表类型,希望能对您学习Mysql表类型有所帮助. MySQL作为当前最为流行的免费数据库服务引擎,已经风靡了很长一段时间,不过也许也有人对于MySQL的内部环境不很了解,尤其那些针对并发性处理的机制.今天,我们先了解一下Mysql表类型,以及它们的一些简单性质. 截至目前,MySQL一共向用户提供了包括DBD.HEAP.ISAM.MERGE.MyIAS.InnoDB以及Gemeni这7种Mysql表类型.

  • MySQL索引优化之不适合构建索引及索引失效的几种情况详解

    MySQL索引优化之不适合构建索引及索引失效的几种情况详解

    目录 结论 不建议建立索引的场景 索引失效的场景 小结 结论 具体案例下文有详尽描述 不适合建立索引的场景: 数据量比较小的表不建议建立索引 有大量重复数据的字段上不建议建立索引(类似:性别字段) 需要进行频繁更新的表不建议建立索引 where.group by.order by后面的没有使用到的字段不建立索引 不要定义冗余索引 索引失效的场景: 过滤条件使用不等于(!=.<>) 过滤条件使用is not null 在索引字段上使用函数或进行计算 在使用联合索引的时候,需要满足“最佳左前缀法则

  • MySQL索引优化之适合构建索引的几种情况详解

    MySQL索引优化之适合构建索引的几种情况详解

    目录 结论 建立索引的场景 小结 结论 在where后面的过滤字段上建立索引(select/update/delete后面的where都是适用的),使用索引加快过滤效率,不用进行全表扫描 在具有唯一要求的字段上添加唯一索引,加快查询效率,查到即可直接返回 group by或者order by后面的字段添加索引,由于索引是排好序的,所以建立索引就等同于在查询之前已经是排好序了(这里需要注意建立的联合索引建立中字段的顺序,可以结合具体案例场景7进行学习) 在DISTINCT(去重字段)后面的字段添加

  • mysql索引失效的五种情况分析

    mysql索引失效的五种情况分析

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

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

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

随机推荐