MYSQL-索引

转载 2016年08月29日 18:39:04

概述

用来加快查询的技术很多,其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引,MYSQL必须从第一条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。但也不全是这样。本文讨论索引是什么以及如何使用索引来改善性能,以及索引可能降低性能的情况。

索引的本质

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

索引的存储分类

索引是在MYSQL的存储引擎层中实现的,而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。MYSQL目前提供了一下4种索引。

  • B-Tree 索引:最常见的索引类型,大部分引擎都支持B树索引。
  • HASH 索引:只有Memory引擎支持,使用场景简单。
  • R-Tree 索引(空间索引):空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型。
  • Full-text (全文索引):全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。

Mysql目前不支持函数索引,但是能对列的前面某一部分进行索引,例如标题title字段,可以只取title的前10个字符进行索引,这个特性可以大大缩小索引文件的大小,但前缀索引也有缺点,在排序Order By和分组Group By 操作的时候无法使用。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。
语法:create index idx_title on film (title(10))

MyISAM、InnoDB引擎、Memory三个常用引擎类型比较

索引 MyISAM引擎 InnoDB引擎 Memory引擎
B-Tree 索引 支持 支持 支持
HASH 索引 不支持 不支持 支持
R-Tree 索引 支持 不支持 不支持
Full-text 索引 不支持 暂不支持 不支持

B-TREE索引类型

  • 普通索引
    这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
    (1)创建索引: CREATE INDEX 索引名 ON 表名(列名1,列名2,...);
    (2)修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1,列名2,...);
    (3)创建表时指定索引:CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1,列名 2,...) );
  • UNIQUE索引
    表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique:
    (1)创建索引:CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
    (2)修改表:ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
    (3)创建表时指定索引:CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );
  • 主键:PRIMARY KEY索引
    主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。
    (1)主键一般在创建表的时候指定:“CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) ); ”。
    (2)但是,我们也可以通过修改表的方式加入主键:“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。
    每个表只能有一个主键。 (主键相当于聚合索引,是查找最快的索引)
    注:不能用CREATE INDEX语句创建PRIMARY KEY索引

索引的设置语法

一 设置索引

在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1.ALTER TABLE - ALTER TABLE用来创建普通索引UNIQUE索引PRIMARY KEY索引

  • ALTER TABLE table_name ADD INDEX index_name (column_list)
  • ALTER TABLE table_name ADD UNIQUE (column_list)
  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)

2.CREATE INDEX - CREATE INDEX可对表增加普通索引或UNIQUE索引。

  • CREATE INDEX index_name ON table_name (column_list)
  • CREATE UNIQUE INDEX index_name ON table_name (column_list)

二 删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

  • DROP INDEX index_name ON talbe_name
  • ALTER TABLE table_name DROP INDEX index_name
  • ALTER TABLE table_name DROP PRIMARY KEY

其中,前两条语句是等价的,删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

三 查看索引

mysql> show index from tblname;
mysql> show keys from tblname;
  • Table:表的名称
  • Non_unique:如果索引不能包括重复词,则为0。如果可以,则为1
  • Key_name:索引的名称
  • Seq_in_index:索引中的列序列号,从1开始
  • Column_name:列名称
  • Collation:列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。
  • Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。
  • Sub_part:如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
  • Packed:指示关键字如何被压缩。如果没有被压缩,则为NULL。
  • Null:如果列含有NULL,则含有YES。如果没有,则该列含有NO。
  • Index_type:用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。
  • Comment:更多评注。

索引选择性

一 索引选择原则

1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引

当然,并不是存在更新的字段就适合创建索引,从判定策略的用语上也可以看出,是"非常频繁"的字段。到底什么样的更新频率应该算是"非常频繁"呢?每秒?每分钟?还是每小时呢?说实话,还真难定义。很多时候是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断的,如果通过该字段的查询并不是很多,可能几个小时或是更长才会执行一次,更新反而比查询更频繁,那这样的字段肯定不适合创建索引。反之,如果我们通过该字段的查询比较频繁,但更新并不是特别多,比如查询几十次或更多才可能会产生一次更新,那我个人觉得更新所带来的附加成本也是可以接受的。

4. 不会出现在 WHERE 子句中的字段不该创建索引

二 索引选择原则细述

  • 性能优化过程中,选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有 两种类型的列:在where子句中出现的列在join子句中出现的列,而不是在SELECT关键字后选择列表的列;
  • 索引列的基数越大,索引的效果越好。例如,存放出生日期的列具有不同的值,很容易区分行,而用来记录性别的列,只有"M"和"F",则对此进行索引没有多大用处,因此不管搜索哪个值,都会得出大约一半的行,( 见索引选择性注意事项对选择性解释;)
  • 使用短索引,如果对字符串列进行索引,应该指定一个前缀长度,可节省大量索引空间,提升查询速度;

    例如,有一个CHAR(200)列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。对前10个或者20个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘IO较少,较短的值比较起来更快。更为重要的是,对于较短的键值,所以高速缓存中的快能容纳更多的键值,因此,MYSQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多快的可能性。

  • 利用最左前缀

三 索引选择注意事项

既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。

一般两种情况下不建议建索引:

  1. 表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了;

    至于多少条记录才算多,这个个人有个人的看法,我个人的经验是以2000作为分界线,记录数不超过 2000可以考虑不建索引,超过2000条可以酌情考虑索引。

  2. 索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:
    Index Selectivity = Cardinality / #T
    显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。例如,上文用到的employees.titles表,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:

    SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

    +-------------+
    | Selectivity |
    +-------------+
    |      0.0000 |
    +-------------+
    

    title的选择性不足0.0001(精确值为0.00001579),所以实在没有什么必要为其单独建索引。

  3. MySQL只对一下操作符才使用索引:<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)

  4. 不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。 在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。

四 索引的弊端

索引的益处已经清楚了,但是我们不能只看到这些益处,并认为索引是解决查询优化的圣经,只要发现 查询运行不够快就将 WHERE 子句中的条件全部放在索引中。

确实,索引能够极大地提高数据检索效率,也能够改善排序分组操作的性能,但有不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假设在Table ta 中的Column ca 创建了索引 idx_ta_ca,那么任何更新 Column ca 的操作,MySQL在更新表中 Column ca的同时,都须要更新Column ca 的索引数据,调整因为更新带来键值变化的索引信息。而如果没有对 Column ca 进行索引,MySQL要做的仅仅是更新表中 Column ca 的信息。这样,最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外,Column ca 的索引idx_ta_ca须要占用存储空间,而且随着 Table ta 数据量的增加,idx_ta_ca 所占用的空间也会不断增加,所以索引还会带来存储空间资源消耗的增加。

引用

mysql-索引

单列索引与多列索引   索引可以是单列索引,也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表: CREATE TABLE people ( pe...
  • abc19900828
  • abc19900828
  • 2014年09月22日 17:17
  • 389

MySQL-索引

MySQL-索引内容整理根据看《MySQL技术内幕》时的笔记,扩展了一些总结了一下。加快对数据表内容的访问速度的基本手段,尤其是涉及多个数据表关联查询中。优点 没有索引的数据表相当于无序的数据行的集合...
  • zjiang1994
  • zjiang1994
  • 2017年04月20日 14:48
  • 385

每天一点MySQL-索引

索引(Index)是帮助MySQL高效获取数据的数据结构。它对于高性能非常关键,建立索引是现实中性能问题的首要原因。理解索引如何工作的最简单的方式就是把索引看成一本书,为了找到书中一个特定的话题,需要...
  • u011391629
  • u011391629
  • 2017年07月26日 18:40
  • 356

MySQL-索引和优化

索引与优化 1、选择索引的数据类型 MySQL支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响。通常来说,可以遵循以下一些指导原则: (1)越小的数据类型通常更好:越小的数据类型通常在磁...
  • qq_36577291
  • qq_36577291
  • 2017年12月21日 18:31
  • 51

Mysql-索引数据结构

昨晚北京迎来了2016入冬的第一场雪呀,爽歪歪。11.20号,mark一下 一.前言: 在我们的生活中,导出可以看到索引效果的应用,如在火车站观看的车次表、字典的目录等。它们的作用就是索引的作...
  • ty_hf
  • ty_hf
  • 2016年12月11日 19:33
  • 1392

Mysql-索引覆盖

索引是一个利于查找的数据结构,而且还缓存在内存中,用索引查找出物理行地址非常快,但是从物理行中提取出(回行)需要的字段值,就会慢一点了。 查询索引是快的,利用索引从物理磁盘中找到要的所有数据(回行)...
  • ty_hf
  • ty_hf
  • 2016年12月11日 20:34
  • 992

Mysql-索引排序行

上一篇刚刚通俗化的说明了B-TREE的几个结果与存储方式,其实跟索引感觉上还是没有关联起来,那么本篇,就通过实际的一个数据行的例子,说明一下。 一.模拟创建原始数据 左边是自己方便说明,模拟...
  • ty_hf
  • ty_hf
  • 2016年12月08日 23:43
  • 981

MySQL-索引03

MySQL-索引03 主键索引 普通索引 唯一索引 联合(组合)索引 联合主键索引 联合普通索引 联合唯一索引 ------------------- 主键索引 crea...
  • maidou931019
  • maidou931019
  • 2017年06月12日 22:00
  • 957

MySQL-索引与优化

理解MySQL——索引与优化 写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小...
  • u014534643
  • u014534643
  • 2017年11月07日 15:05
  • 81

MySQL- 索引详解

索引(键(key)),是存储引擎用于快速找到记录的一种数据结构。 如果没有索引,数据库就必须从第一条记录开始进行全表扫描,直到找出相关的行。索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时...
  • qq_29168493
  • qq_29168493
  • 2018年01月16日 18:53
  • 17
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MYSQL-索引
举报原因:
原因补充:

(最多只允许输入30个字)