MYSQL-索引

MYSQL-索引

概述

用来加快查询的技术很多,其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引,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 indexfromtblname;mysql> show keysfromtblname;

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在运行时也要消耗资源维护索引,因此索引并不是越多越好。

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

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

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

索引的选择性较低。所谓索引的选择性(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),所以实在没有什么必要为其单独建索引。

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

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

四 索引的弊端

索引的益处已经清楚了,但是我们不能只看到这些益处,并认为索引是解决查询优化的圣经,只要发现 查询运行不够快就将 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 所占用的空间也会不断增加,所以索引还会带来存储空间资源消耗的增加

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,287评论 6 498
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,346评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 162,277评论 0 353
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,132评论 1 292
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,147评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,106评论 1 295
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,019评论 3 417
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,862评论 0 274
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,301评论 1 310
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,521评论 2 332
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,682评论 1 348
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,405评论 5 343
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,996评论 3 325
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,651评论 0 22
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,803评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,674评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,563评论 2 352

推荐阅读更多精彩内容

  • 命名规则:表名_字段名 1、需要加索引的字段,要在where条件中 2、数据量少的字段不需要加索引 3、如果whe...
    Dg_fc58阅读 704评论 0 50
  • 1.索引作用 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别...
    kang_KK阅读 21,830评论 1 51
  • 索引是用来快速检索出具有特定值的记录。如果没有索引,数据库就必须从第一条记录开始进行全表扫描,直到找出相关的行。数...
    咕泡学院蓉蓉老师阅读 454评论 0 2
  • 转载:http://blog.codinglabs.org/articles/theory-of-mysql-in...
    qf1007阅读 1,291评论 0 0
  • MySQL 索引 MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 ...
    SkTj阅读 377评论 0 0