蒙国造博客

MySQL索引入门简述

Gary

概述

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

索引的本质

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

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

索引的存储分类

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

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 索引 支持 支持(5.6+) 不支持

B-TREE索引类型

索引的设置语法

设置索引

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

1．ALTER TABLE 来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);    # 创建通索引
ALTER TABLE table_name ADD UNIQUE (column_list);              # 创建UNIQUE唯一索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list);         # 创建PRIMARY KEY主键索引

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

CREATE INDEX index_name ON table_name (column_list);          # 普通索引
CREATE UNIQUE INDEX index_name ON table_name (column_list);   # UNIQUE唯一索引

删除索引

可利用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;

索引选择性

索引选择原则

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

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

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

索引选择原则细述

索引选择注意事项

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，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 子句中的条件全部放在索引中。

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

参考：

  1. MYSQL-索引
  2. InnoDB Table and Index Structures

 

退出移动版