mysql 索引计划_mysql——索引及执行计划

一、什么是索引？

索引用来快速地寻找那些具有特定值的记录，他就如同我们字典的目录，我们可以快速找到我们想要的值。

所有MySQL索引都以B-树的形式保存。所以在索引中搜索值采用的二分查找法。为什么可以使用二分法呢?因为在索引表里，会对索引列进行排序

如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

假设我们创建了一个名为people的表： CREATE TABLE people ( peopleid SMALL  INT NOT NULL, name CHAR(50) NOT NULL );

然后，我们完全随机把1000个不同name值插入到people表。在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引，MySQL将在索引中排序name列,对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此，如果我们要查找name等于“Mike”记录的peopleid(SQL命令为“SELECT peopleid FROM people WHERE name='Mike';”)，MySQL能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid(999)。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。

二、索引的分类

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换

MEMORY/HEAP存储引擎：支持HASH和BTREE索引

索引我们分为四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

1.1、普通索引：

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

1.2、唯一索引：

索引列中的值必须是唯一的(就是没有重复的值)，但是允许为空值，

1.3、主键索引：

是一种特殊的唯一索引，不允许有空值。

2、组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿 首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

如：建立 姓名、年龄、性别的复合索引。

注意：在建立组合索引时，我们要考虑将来通过组合索引来查数据时，确定哪一列应该排在最前面。因为最左前缀原则，就导致了组合索引的第一列必须使用在查询语句中，整个组合索引才会生效。如上：在同一个索引中，seq_in_index的值就代表了组合索引中索引的顺序。1为最左的索引

请注意，创建复合索引应当包含少数几个列，并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值，其后果是内存溢出和性能降低。

复合索引对排序的优化：

复合索引只对和索引中排序相同或相反的order by 语句优化。

在创建复合索引时，每一列都定义了升序或者是降序。如定义一个复合索引：

CREATEINDEX idx_exampleON table1 (col1 ASC, col2 DESC, col3 ASC)

其中 有三列分别是：col1 升序，col2 降序， col3 升序。现在如果我们执行两个查询

1：Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC

和索引顺序相同

2：Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC

和索引顺序相反

查询1，2 都可以别复合索引优化。

如果查询为：

Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC

排序结果和索引完全不同时，此时的查询不会被复合索引优化。

查询优化器在在where查询中的作用：

如果一个多列索引存在于 列 Col1 和 Col2 上，则以下语句：Select * from table where col1=val1 AND col2=val2 查询优化器会试图通过决定哪个索引将找到更少的行。之后用得到的索引去取值。

1) 如果存在一个多列索引，最左面的索引总是能被优化器使用(即使只是只使用最左的索引也是可以的)。所以联合索引的顺序不同，影响索引的选择，尽量将重复率低的列放在前面。

如：一个多列索引为 (col1 ，col2， col3)

那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索会有作用。

SELECT * FROM tb WHERE col1 = val1

SELECT * FROM tb WHERE col1 = val1 and col2 = val2

SELECT * FROM tb WHERE col1 = val1 and col2 = val2 AND col3 = val3

2) 如果列不构成索引的最左面前缀，则建立的索引将不起作用。

SELECT * FROM tb WHERE col3 = val3

SELECT * FROM tb WHERE col2 = val2

SELECT * FROM tb WHERE col2 = val2 and col3=val3

3、全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。

4、空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。

在创建空间索引时，使用SPATIAL关键字。

要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

5.前缀索引

在 MySQL 中，可以仅仅使用某个字段的前面部分内容做为索引键索引该字段，以达到减小索引占用的存储空间和提高索引访问效率的目的。当然，前缀索引的功能仅仅适用于 字段前缀随机重复性很小的字段。如果须要索引的字段前缀内容有较多的重复，索引的过滤性自然也会随之降低，通过索引所访问的数据量就会增加，这时候前缀索 引虽然能够减少存储空间消耗，但是可能会造成 Query 访问效率的极大降低，得不偿失。

三、索引的创建

请自行百度

四、查看索引

查看表索引：show index from user_member;##查看user_member表中的所有索引

列名

解释

Table

表的名称。

Non_unique

如果索引不能包括重复词，则为0。如果可以，则为1。说白了：索引列的值如果不重复就是0，如果有重复的就是1

Key_name

索引的名字

Seq_in_index

索引中的列序列号，从1开始。

Column_name

列名称。 说白了：就是在该表中的哪一列上建的索引

Collation

列以什么方式存储在索引中。在MySQL中，有值‘A’(升序)或NULL(无分类)。体现了索引是有排序的。

Cardinality

不重复的记录的数目，对于索引来讲，Cardinality越大越好，最好接近真实的记录数，因为索引列的数据重复率就越低。如果Cardinality太小，则索引就失去意义了。该值不会自动更新，可通过运行ANALYZE TABLE或myisamchk -a更新(注意：该值只是估值)。

Sub_part

如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

Packed

指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null

如果列含有NULL，则含有YES。如果没有，则该列含有NO。

Index_type

用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

Comment

多种评注

五、索引的利弊

索引的利弊与如何判定，是否需要索引

相信读者都知道索引能够极大地提高数据检索的效率，让Query 执行得更快，但是可能并不是每一位朋友都清楚索引在极大提高检索效率的同时，也给数据库带来了一些负面的影响。下面就分别对 MySQL 中索引的利与弊做一个简单的分析。

1.索引的好处

索引带来的益处可能很多读者会认为只是"能够提高数据检索的效率，降低数据库的IO成本"。

确实，在数据库中表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件时可以极大地提高检索效率，加快检索时间，降低检索过程中须要读 取的数据量。但是索引带来的收益只是提高表数据的检索效率吗？当然不是，索引还有一个非常重要的用途，那就是降低数据的排序成本。

我们知道，每个索引中的数据都是按照索引键键值进行排序后存放的，所以，当Query 语句中包含排序分组操作时，如果排序字段和索引键字段刚好一致，MySQL Query Optimizer 就会告诉 mysqld 在取得数据后不用排序了，因为根据索引取得的数据已经满足客户的排序要求。

那如果是分组操作呢？分组操作没办法直接利用索引完成。但是分组操作是须要先进行排序然后分组的，所以当Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么mysqld 同样可以利用索引已经排好序的这个特性，省略掉分组中的排序操作。

排序分组操作主要消耗的是内存和 CPU 资源，如果能够在进行排序分组操作中利用好索引，将会极大地降低CPU资源的消耗。

2.索引的弊端

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

总结：

1)占用磁盘空间。

2) 增加了插入和删除的操作时间。一个表拥有的索引越多，插入和删除的速度越慢。如 要求快速录入的系统不宜建过多索引。

3.如何判定是否须要创建索引

在了解了索引的利与弊之后，那我们到底该如何来判断某个索引是否应该创建呢？

实际上，并没有一个非常明确的定律可以清晰地定义什么字段应该创建索引，什么字段不该创建索引。因为应用场景实在是太复杂，存在太多的差异。当然，还是仍然能够找到几点基本的判定策略来帮助分析的。

1. 较频繁的作为查询条件的字段应该创建索引

提高数据查询检索的效率最有效的办法就是减少须要访问的数据量，从上面索引的益处中我们知道，索引正是减少通过索引键字段作为查询条件的 Query 的IO量之最有效手段。所以一般来说应该为较为频繁的查询条件字段创建索引。

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

唯一性太差的字段主要是指哪些呢？如状态字段、类型字段等这些字段中存放的数据可能总共就是那么几个或几十个值然后在不断重复使用，每个值都会存在于成千上万 或更多的记录中。对于这类字段，完全没有必要创建单独的索引。因为即使创建了索引，MySQL Query Optimizer 大多数时候也不会去选择使用，如果什么时候 MySQL Query Optimizer选择了这种索引，那么非常遗憾地告诉你，这可能会带来极大的性能问题。由于索引字段中每个值都含有大量的记录，那么存储引擎在根据索引访问数据的时候会带来大量的随机IO，甚至有些时候还会出现大量的重复IO。

所以：在建立索引的时候，我们要从 Cardinality(SHOW INDEX FROM ...能看到此参数)的角度看是否合适的问题，Cardinality表示唯一值的个数，一般来说，如果唯一值个数在总行数中所占比例小于20%的话，则 可以认为Cardinality太小，此时索引除了拖慢insert/update/delete的速度之外，不会对select产生太大作用；

3. 更新非常频繁的字段不适合创建索引

上面在索引的弊端中已经分析过了，索引中的字段被更新的时候，不仅要更新表中的数据，还要更新索引数据，以确保索引信息是准确的。这个问题致使IO 访问量较大增加，不仅仅影响了更新 Query 的响应时间，还影响了整个存储系统的资源消耗，加大了整个存储系统的负载。

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

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

不会还有人会问为什么吧？自己也觉得这是废话了，哈哈！

5.索引列的字符集的考虑

建立索引的时候未考虑字符集的影响，比如说username字段，如果仅仅允许英文，下划线之类的符号，那么就不要用gbk，utf-8之类的字符 集，而应该使用latin1或者ascii这种简单的字符集，索引文件会小很多，速度自然就会快很多。

6. 单键索引还是组合索引

在很多时候，WHERE 子句中的过滤条件并不只是针对于单一的某个字段，经常会有多个字段一起作为查询过滤条件存在于 WHERE 子句中。在这种时候，就必须要判断是该仅仅为过滤性最好的字段建立索引，还是该在所有字段(过滤条件中的)上建立一个组合索引。

对于这种问题，很难有一个绝对的定论，须要从多方面来分析考虑，平衡两种方案各自的优劣，然后选择一种最佳的方案。因为从上面已了解到索引在提 高某些查询的性能同时，也会让某些更新的效率下降。而组合索引中因为有多个字段存在，理论上被更新的可能性肯定比单键索引要大很多，这样带来的附加成本也 就比单键索引要高。但是，当WHERE 子句中的查询条件含有多个字段时，通过这多个字段共同组成的组合索引的查询效率肯定比只用过滤条件中的某一个字段创建的索引要高。因为通过单键索引过滤的 数据并不完整，和组合索引相比，存储引擎须要访问更多的记录数，自然就会访问更多的数据量，也就是说需要更高的 IO 成本。

可能有朋友会说，那可以创建多个单键索引啊。确实可以将 WHERE 子句中的每一个字段都创建一个单键索引。但是这样真的有效吗？在这样的情况下，MySQL Query Optimizer 大多数时候都只会选择其中的一个索引，然后放弃其他的索引。即使他选择了同时利用两个或更多的索引通过 INDEX_MERGE 来优化查询，所收到的效果可能并不会比选择其中某一个单键索引更高效。因为如果选择通过 INDEX_MERGE 来优化查询，就须要访问多个索引，同时还要将几个索引进行 merge 操作，这带来的成本可能反而会比选择其中一个最有效的索引更高。

在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下能过滤90%以上的数据，而其他的过滤字段会频繁的更新，一般更倾向于创建组合索引， 尤其是在并发量较高的场景下。因为当并发量较高的时候，即使只为每个Query节省了很少的 IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。

当然，创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中，还应该尽量让一个索引被多个 Query 语句利用，尽量减少同一个表上的索引数量，减少因为数据更新带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。

对于具有2个用and连接条件的语句，且2个列之间的关联度较低的情况下，组合索引有一定优势。

对于具有2个用and连接条件的语句，且2个列之间的关联度较高的情况下，组合索引有很大优势。

对于具有2个用or连接条件的语句，单列索引有一定优势，因为这种情况下复合索引将会导致全表扫描，而前者可以用到index merge的优化。

7.数据库引擎对索引影响

比方说有一个文章表，我们要实现某个类别下按时间倒序列表显示功能：

SELECT * FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...

这样的查询很常见，基本上不管什么应用里都能找出一大把类似的SQL来，学院派的读者看到上面的SQL，可能会说SELECT *不好，应该仅仅查询需要的字段，那我们就索性彻底点，把SQL改成如下的形式：

SELECT id FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...

我们假设这里的id是主键，至于文章的具体内容，可以都保存到memcached之类的键值类型的缓存里，如此一来，学院派的读者们应该挑不出什么毛病来了，下面我们就按这条SQL来考虑如何建立索引：

不考虑数据分布之类的特殊情况，任何一个合格的WEB开发人员都知道类似这样的SQL，应该建立一个”category_id, created“复合索引，但这是最佳答案不？不见得，现在是回头看看标题的时候了：MySQL里建立索引应该考虑数据库引擎的类型！

如果我们的数据库引擎是InnoDB，那么建立”category_id, created“复合索引是最佳答案。让我们看看InnoDB的索引结构，在InnoDB里，索引结构有一个特殊的地方：非主键索引在其BTree的叶节 点上会额外保存对应主键的值，这样做一个最直接的好处就是Covering Index，不用再到数据文件里去取id的值，可以直接在索引里得到它。

如果我们的数据库引擎是MyISAM，那么建立"category_id, created"复合索引就不是最佳答案。因为MyISAM的索引结构里，非主键索引并没有额外保存对应主键的值，此时如果想利用上Covering Index，应该建立"category_id, created, id"复合索引。

六、索引失效

1． 如果一个 Like 语句的查询条件以通配符为起始则索引失效。

如：%车 或 %车% 索引将会失效。

车% 索引生效。

2、使用不等于操作符(!=)

下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描

select * from dept where staff_num !=1000;

但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？

有！

通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。

select * from dept where staff_num >1000 or staff_num <1000;

3、使用 is null 或 is not null

使用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引(除非索引是一个位图索引，关于位图 索引，会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。

解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)

3、使用函数

如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引：

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

select * from staff where birthdate

4、比较不匹配的数据类型

比较不匹配的数据类型也是难于发现的性能问题之一。

下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。

把SQL语句改为如下形式就可以使用索引

select * from dept where dept_id = '900198';