【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)

时间:2022-12-28 13:20:24
【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
MySQL索引的概念:
  • 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度
  • 索引分为:聚簇索引、非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引:能提高多行检索的速度,非聚簇索引:单行的检索很快。
  • 要注意的是,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。
索引类型:
  1. 普通索引
  2. 唯一性索引
  3. 全文索引
  4. 单列索引
  5. 多列索引
  6. 空间索引

 1. 在 ‘ 创建数据表时创建索引 ’ 的基本语法结构:[ 此文档解释所用的创建方式 ]

【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
2. ‘ 直接创建索引 ’ 的基本语法结构:
【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
3. ‘ 修改表结构的方式添加索引 ’ 的基本语法结构:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
 
删除索引:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
索引属性名的含义:
  • UNIQUE:(unique),可选参数,表示索引为唯一索引。
  • FULLTEXT:(fulltext) ,可选参数,表示索引为全文索引。
  • SPATIAL:(spatial) ,可选参数,表示索引为空间索引。
  • INDEX | KEY:(index | key), 必选参数,用于指定字段为索引的,用户在选择时,只需要二选一即可。
  • [索引名]:可选参数,其作用是给创建的索引取新名称。(起到方便使用的目的)
  • 被选定的字段名:必选参数,被用作索引的对应的字段名称,该字段必须被预先定义。
  • 长度:可选参数,其指索引的长度,必须是字符串类型才可以使用。(比如:电话号码)
  • [ASC | DESC]:(asc | desc),可选参数,ASC 表示升序排列,DESC 表示降序排列。
一、普通索引  创建
创建普通索引,不需要添加 [UNIQUE | FULLTEXT | SPATIAL ] 等任何参数进行约束。
  • 普通索引 (由关键字KEY或INDEX定义的索引) 的唯一任务是加快对数据的访问速度。
  • 只为那些最经常出现在‘查询条件’(WHERE column = ...) 或‘排序条件’(ORDER BY column)中的数据列,来创建索引。
  • 只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个int整数类型的数据列)来创建索引。
例:创建的表名为 score 的数据表,并在该表的 id 字段上建立名称为 score_id 的 ' 普通索引 ',SQL语句如下:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
实例详解:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
score表的结构:
【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
可以看出,id 字段上已经创建了一个名称为 score_id 的索引。
score表中id=1的数据的整个执行计划:
【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
possible_keys 和 key 的值都为 score_id,说明 score_id 索引已经存在,并且已经开始被使用了。
注意:若设定了主键 primary key
【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
 
score表的结构:
【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
可以看出,id 字段上已经创建了一个名称为 score_id 的索引和 PRIMARY 的主键。
score表中id=1的数据的整个执行计划:
【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
说明 score_id 索引已经存在,但已经开始被使用的是 PRIMARY 的主键。
二、唯一索引  创建
创建唯一索引时,使用 UNIQUE 参数对 INDEX | KEY 进行约束。
  • 与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。
  • 如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处:一是简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率;二是MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。也就是说,唯一索引可以保证数据记录的唯一性。
  • 事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。
  • 主索引:在前面已经反复多次强调过!必须为主键字段创建一个索引,这个索引就是所谓的"主索引"。
  • 主索引 与 唯一索引的唯一区别是:前者在定义时使用的关键字是PRIMARY而不是UNIQUE。 
例:创建的表名为 address 的数据表,并在该表的 id 字段上建立名称为 address_id 的 ' 唯一索引 ',SQL语句如下:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
三、全文索引  创建
全文索引只能作用在  CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用  FULLTEXT  参数进行约束。
  • MySQL从3.23.23版开始支持全文索引和全文检索,fulltext索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。
  • 对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
  • 文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字,普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现,这对MySQL来说很复杂,如果需要处理的数据量很大,响应时间就会很长。 
  • 这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时,MySQL将把在文本中出现的所有单词创建为一份清单,查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建,也可以等日后有必要时再使用命令添加
  • 有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法: 

   SELECT * FROM table_name 
   WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')

  • 上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。
例:创建的表名为 cards 的数据表,并在该表的 name 字段上建立名称为 cards_number 的 ' 全文索引 ',SQL语句如下:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
四、单列索引  创建
创建单列索引,即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数,用户在建立时只需要指定单列字段名,即可创建单列索引。
  •  多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。
例:创建的表名为 telephone 的数据表,并在该表的 tel 字段上建立名称为 tel_num 的单列索引,SQL 语句如下:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
五、多列索引  创建
创建多列索引,即在数据表的多个字段上创建索引。与上述单列索引类似,创建该类型索引不需要引入约束参数。
  •  多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。
例:创建的表名为 information 的数据表,并在该表的 name 和 sex 字段上建立名称为 info 的多列索引,SQL 语句如下:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
注意:在多列索引中,只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 name 字段),索引才会被使用。
触发多列索引的条件是用户必须使用索引的第一字段,如果没有用到第一字段,则索引不起任何作用,用户想要优化查询速度,可以应用该类索引形式。
例:组合(复合)索引 ---(‘最左前缀’原则)
针对 title 和 time 建立一个组合索引:alter table info add index `index_title_time` (`title`(50),`time`(10))。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:
    –title,time
    –title
为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示:
使用到上面的索引:
SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE title='测试';
–不使用上面的索引
SELECT * FROM article WHREE time=1234567890;
六、空间索引  创建
创建空间索引,需要添加 SPATIAL 参数进行约束。
同样,必须说明的是,只有 MyISAM 类型的表支持该类型 ‘ 空间索引 ’。而且,索引字段必须有非空约束。
创建的表名为 list 的数据表,并在该表的 goods 字段上建立名称为 listinfo 的空间索引,这里 goods 字段有非空约束,符合条件,SQL 语句如下:
 【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)
注意: goods 字段上已经建立名称为 listinfo 的空间索引,其中 goods 字段必须不能为空,且数据类型是 GEOMETRY,该类型是空间数据类型。
空间类型不能用其他类型代替,否则在生成空间素引时会产生错误且不能正常创建该类型索引。 
 
**外键索引**:如果为某个外键字段定义了一个外键约束条件,MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。 
 
另外:INDEX | KEY:(index | key), 必选参数,用于指定字段为索引的,用户在选择时,只需要二选一即可。
具体什么时候选 INDEX  ,什么时候选 KEY ?
参照两者区别:
1). key :是数据库的物理结构,它包含两层意义:一是约束(偏重于约束和规范数据库的结构完整性)、二是索引(辅助查询用的)。包括primary key, unique key, foreign key 等。
  primary key 有两个作用,一是约束作用(constraint),用来规范一个存储主键和唯一性,但同时也在此key上建立了一个index;
  unique key 也有两个作用,一是约束作用(constraint),规范数据的唯一性,但同时也在这个key上建立了一个index;
  foreign key也有两个作用,一是约束作用(constraint),规范数据的引用完整性,但同时也在这个key上建立了一个index;
2). index:是数据库的物理结构,它包含一层意义:它只是索引(辅助查询用的),它创建时会在另外的表空间(mysql中的innodb表空间)以一个类似目录的结构存储。
  因此,索引只是索引,它不会去约束索引的字段的行为(那是key要做的事情)。
3). 最后的释疑
    1. 我们说索引分类,分为主键索引、唯一索引、普通索引(这才是纯粹的index)等,也是基于是不是把index看作了key。
  比如 create table t(id int, unique index inx_tx_id  (id));  --- index当作了key使用,因为前面的 unique 赋予了 ‘约束’,使其具有和 key 一样的作用。
    2. 最重要的也就是,不管如何描述,理解index是纯粹的index,还是被当作key,当作key时则会有两种意义或起两种作用。
 
 
 
 
 

【详细解析】MySQL索引详解( 索引概念、6大索引类型、key 和 index 的区别、其他索引方式)的更多相关文章

  1. 一篇详细的 Mysql Explain 详解

    一.语法 explain < table_name > 例如: explain select * from t3 where id=3952602; 二.explain输出解释 +—-+— ...

  2. MySQL 联合索引详解

    MySQL 联合索引详解   联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c ...

  3. Oracle索引详解

    Oracle索引详解(二) --索引分类   Oracle 提供了大量索引选项.知道在给定条件下使用哪个选项对于一个程序的性能来说非常重要.一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程 ...

  4. lucene、lucene&period;NET详细使用与优化详解

    lucene.lucene.NET详细使用与优化详解 2010-02-01 13:51:11 分类: Linux 1 lucene简介1.1 什么是luceneLucene是一个全文搜索框架,而不是应 ...

  5. MySQL 操作详解

    MySQL 操作详解 一.实验简介 本节实验中学习并实践 MySQL 上创建数据库.创建表.查找信息等详细的语法及参数使用方法. 二.创建并使用数据库 1. 创建并选择数据库 使用SHOW语句找出服务 ...

  6. lucene&period;NET详细使用与优化详解

    lucene.NET详细使用与优化详解 http://www.cnblogs.com/qq4004229/archive/2010/05/21/1741025.html http://www.shan ...

  7. MySQL配置文件详解

    MYSQL 配置文件详解 “全局缓存”.“线程缓存”,全局缓存是所有线程共享,线程缓存是每个线程连接上数据时创建一个线程(如果没有设置线程池),假如有200连接.那就是200个线程,如果参数设定值是1 ...

  8. Mysql Explain 详解

    Mysql Explain 详解[强烈推荐] Mysql Explain 详解一.语法explain < table_name >例如: explain select * from t3 ...

  9. mysql数据库 详解 之 自学成才1

    一.学习目录 1.认识数据库和mysql 2.mysql连接 3.入门语句 4.详解列类型 5.增删改查 INSERT INTO  表名(列1,……  列n)  VALUES(值 1,……  值 n) ...

随机推荐

  1. leetcode一些常用函数

    6移位操作 “>> 右移,高位补符号位” 这里右移一位表示除2“>>> 无符号右移,高位补0”: 与>>类似“<< 左移” 左移一位表示乘2,二位 ...

  2. 自定义指令-directive &lpar;转&rpar;

    1.指令作用域中的@ 作用是把当前属性作为字符串传递. 前台代码: <div ng-controller="MyCtrl">       <drink water ...

  3. 是谁决定了走redis缓存?当然是mybatis啊

    1.是谁决定了走redis缓存?当然是mybatis啊 mybatis里默认实现数据的增删改查功能,这里要用到缓存啊 而且是mybatis这种orm框架采用缓存机制的,mybatis默认都有两层缓存了 ...

  4. hadoop filesystem 删除文件 复制文件 重命名文件

    private void moveFile(Configuration conf, String Path1, String Path2, String newname ) throws IOExce ...

  5. 【Netty】Netty传输

    一.前言 在简单学习了Netty中的组件后,接着学习Netty中数据的传输细节. 二.传输 2.1 传输示例 Netty中的数据传输都是使用的字节类型,下面通过一个实例进行说明,该实例中服务器接受请求 ...

  6. Eclipse导入项目常见问题----乱码问题03

    有时打开导入的项目文件时,会出现如下图情况: 解决方法 如下图步骤所示: 此时,我们可以看到文件正常了 jdk版本问题(有个红色感叹号)01:http://blog.csdn.net/baidu_37 ...

  7. 运维与自动化系列③自动化部署基础与shell脚本实现

    自动化部署基础与shell脚本实现 关于自动化的基础知识: 1.1:当前代码部署的实现方式: 运维纯手工scp到web服务器纯手工登录git服务器执行git pull或svn服务器执行svn upda ...

  8. 垃圾wps弹出,现在连关闭按钮都不给了

    垃圾wps弹出,现在连关闭按钮都不给了,有点起色就变得相当垃圾.

  9. 简化Getter 与 Setter 的插件 Lombok

    参考文档:https://www.jianshu.com/p/365ea41b3573 第一步:添加依赖 <dependency> <groupId>org.projectlo ...

  10. IOS 数据存储之 FMDB 详解

    FMDB是用于进行数据存储的第三方的框架,它与SQLite与Core Data相比较,存在很多优势. FMDB是面向对象的,它以OC的方式封装了SQLite的C语言API,使用起来更加的方便,不需要过 ...