mysql 索引相关

引言：

MYSQL由于其免费和开源的性质，在项目中用处广泛。大家都知道，一个MySQL数据库能够储存大量的数据，如果要在大量的数据中查找某一个数据，如果使用全表检索的话，即费时间又费力气，这时，就需要一种手段来减少这种消耗，这时候，索引就出现了！这里就简要介绍一下MYSQL的索引相关的内容。

索引是啥球东西：

索引的作用就像是相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索方式是全表搜索，将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。值得一提的是，索引是一个单独的、物理的数据库结构。所以，若索引太多，一方面索引占据了硬盘的空间，更重要的是，每次修改数据纪录，索引就必须刷新一次，严重影响数据库的性能。而索引太少，对查询性能又会产生影响。所以需要找到一个平衡点，这是DBA需要针对不同业务需要操心的事，在本文不表。

MYSQL InnoDB引擎的索引：

其实在MYSQL中，索引是跟存储引擎关联的。这里主要是对InnoDB这个MYSQL 5.5之后默认的存储引擎支持的索引做一个概述，补充讲解一下联合索引的概念和使用规则，给女神扫盲一下。

InnoDB存储引擎支持以下的索引

• 哈希索引
• 全文索引
• B+树索引

哈希索引：自适应的，InnoDB会根据表的使用情况自动为表生成哈希索引，不能人为干预，也就是你不用管它是干嘛的。。

全文索引：不知道是什么。。。。

B+树索引：传统意义的索引，关于B+树的概念，本文也不表，详情见july的博客。B+树索引的本质是B+树在数据库中的实现。需要注意的是：B+树索引并不能找到一个键值对应的具体行。B+树索引只能查到被查找数据行所在的页，然后数据库通过把页读入内存，再在内存中查找，最后得到结果。

数据库中的B+树索引又可以分为两类：

• 聚集索引
• 辅助索引（非聚集索引）

这两种索引的共同点是内部都是B+树，叶节点存放着所有数据。不同点是叶节点是否存放着一整行数据。

聚集索引：按每张表的主键构造一颗B+树。并且叶节点存放整张表的行记录数据。每张表只能有一个聚集索引。聚集索引的另一个好处是它对于主键的排序查找和范围的速度非常快。叶节点的数据就是我们要找的数据。

辅助索引：叶级别不包含行的全部数据，叶级别除了包含行的键值以外，每个索引行还包含了一个书签（bookmark），该书签告诉innodb存储引擎，哪里可以找到与索引对应的数据。辅助索引的存在并不影响数据再聚集索引中的组织，因此一个表可以有多个辅助索引。当通过辅助索引查找数据时，innodb会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键。然后再通过主键索引找到一行完整的数据。

什么时候使用B+索引？

当查询表中很少一部分数据时，B+索引才有意义。对于性别，地区类型字段，他们取值范围很小，即低选择性。这时加B+索引是没有必要的。相反，某个字段取值范围很广，如姓名，几乎没有重复，即高选择性，则使用B+索引是比较合适的。因此。当访问高选择性字段并取出很少一部分数据时，该字段加B+索引是非常有效的。但是当取出的数据行占表中大部分数据时，数据库就不会使用B+索引了。

很多时候，我们在MYSQL中创建了索引，但是某些查询还是很慢，因为这些查询根本就没有使用到索引！一般来说，可能是某些字段没有创建索引，或者是组合索引中字段的顺序与查询语句中字段的顺序不符，或者你取出的是大部分数据，MYSQL引擎会不使用索引。

联合索引：

接下来解释一下联合索引。初衷来自与百度的一道面试题。大概的意思就是：一个表叫tb，里面有a,b,c三个字段。写sql 查询 a>10 b>10 c>10. select * from tb where a > 10 and b > 10 and c > 10，问怎么建索引查询快。

我的回答是对 a b c建立联合索引，是不是最优的，有没有别的方法不清楚。。。

联合索引顾名思义指针对表上的多个列做索引。说白了，联合索引的实现还是一个B+树，不同的是联合索引键值的数量不是1，而是大于等于2。

联合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。所以，对于联合索引来说，MYSQL会从左到右的使用索引中的字段。所以创建联合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，联合索引非常有用；仅对后面的任意列执行搜索时，联合索引则没有用处。这样说来也许有些抽象，下面举例说明。

create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);
首先联合索引的最左优先原则：联合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。
如商标所示：有一个联合索引(a,b,c)

下面的where语句都会用到这个索引（）：
where a = "some value";
where a = "some value" and b = "some value";
where a = "some value" and b = "some value" and c = "some value";
where b = "some value" and a = "some value" and c = "some value";
对于最后一条语句，MYSQL会自动优化成第三条的样子
下面的情况就不会用到索引：
where b = "aaaaaa";
where b = "aaaa" and c = "cccccc";

更多例子如下：
优: select * from test order by a
差: select * from test order by b
差: select * from test order by c

优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c

优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c

 

关于具体的性能的对比数据，等这短时间忙完了再补上！

 

参考：

http://leyteris.iteye.com/blog/825799

MYSQL创建索引，查看以及删除：http://leyteris.iteye.com/blog/825799

MYSQL索引详解：http://database.51cto.com/art/201103/252461.htm

理解MySQL——索引与优化：http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html