MySQL索引与优化.docx
《MySQL索引与优化.docx》由会员分享，可在线阅读，更多相关《MySQL索引与优化.docx（14页珍藏版）》请在冰豆网上搜索。
MySQL索引与优化
MySQL索引与优化
目录
1为什么要使用索引1
1.1索引的优点2
1.2索引的缺点2
2MySQL索引类型3
2.1按索引的存储方式来划分3
2.1.1聚簇索引3
2.1.2非聚簇索引4
2.2按索引的创建关键字划分4
2.2.1主键索引5
2.2.2普通索引5
2.2.3唯一索引5
2.3按索引的结构方式划分5
2.3.1B-Tree索引5
2.3.2Hash索引6
2.3.3Full-text索引8
2.3.4R-Tree索引8
2.4按索引字段个数来划分9
2.4.1单列索引9
2.4.2多列索引（复合索引）9
2.5其他类型10
2.5.1前缀索引10
2.5.2覆盖索引12
3MySQL中索引的限制13
4是否应该创建索引13
4.1应该创建索引的情况13
4.2不应该创建索引的情况14
5MySQL索引失效实例14
1为什么要使用索引
关系数据库的世界是一个表与集合、表与集合上的运算占统治地位的世界。
数据库是一个表的集合，而表又是行和列的集合。
在发布一条SELECT查询从表中进行检索行时，得到另一个行和列的集合。
这些都是一些抽象的概念，对于数据库系统用来操纵表中数据的基本表示没有多少参考价值。
另一个抽象概念是，表上的运算都同时进行；查询是一种概念性的集合运算，并且集合论中没有时间概念。
当然，现实世界是相当不同的。
数据库管理系统实现了抽象的概念，但是在实际的硬件范围内要受到实际的物理约束。
结果是，查询要花时间，有时要花很长的时间。
而人类很容易不耐烦，不喜欢等待，因此我们丢下了集合上的那些瞬间的数学运算的抽象世界去寻求加速查询的方法。
幸运的是，有几种加速运算的技术，可对表进行索引使数据库服务器查找行更快。
可考虑怎样充分利用这些索引来编写查询。
可编写影响服务器调度机制的查询，使来自多个客户机的查询协作得更好。
我们思考基本硬件怎样运行，以便想出怎样克服其物理约束对性能进行改善的方法。
我们首先讨论索引，因为它是加快查询的最重要的工具。
还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。
在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。
但这样也并非总是有效，因为优化并非总是那样简单。
然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。
应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。
1.1索引的优点
1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5）通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
6）索引可以将随机I/O变为顺序I/O。
1.2索引的缺点
1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
2MySQL索引类型
声明：
以下的索引类型划分方式非来自官方文档的，仅是本人的总结，仅供参考。
2.1按索引的存储方式来划分
以在MySQL应用中较为多的InnoDB引擎为例说明
2.1.1聚簇索引
InnoDB的聚簇索引实际上在同一结构中保存了B-Tree索引和数据行。
当表有聚簇索引，它的数据行实际上存放在索引的叶子页中。
术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。
因为无法同时将数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
在InnoDB中，是通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。
如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
聚簇索引的优点：
1）可以把相关数据保存在一起。
例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。
如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O；
2）数据访问更快。
聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。
3）使用聚簇索引扫描的查询可以直接使用页节点中的主键值。
聚簇索引的缺点：
1）聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
2）插入速度严重依赖于插入顺序。
按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZETABLE命令重新组织一下表。
3）更新聚簇索引列的代价很高，因为会限制InnoDB将每个被更新的行移动到新的位置。
4）基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。
当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。
页分裂会导致表占用更多的磁盘空间。
5）聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
6）二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
2.1.2非聚簇索引
在MySQL中，非聚簇索引这个概念并不显著，不像sqlserver那样明确地创建非聚簇索引。
在MySQL中可以将二级索引理解为非聚簇索引，而一级索引为聚簇索引（主键索引）。
二级索引可以是唯一索引或者普通索引。
2.2按索引的创建关键字划分
例子：
CREATETABLEt1（
aINT,bINT,cINT,dINT,eINT,fINT,
PRIMARYKEYi0（a,b）,KEYi1（c,d）,UNIQUEKEYi2（e,f）
）ENGINE=INNODB;
2.2.1主键索引
它是一种特殊的唯一索引，不允许有空值。
一般是在建表的时候同时创建主键索引，一个表只有一个主键。
创建的时候使用关键词PRIMARY。
如例子中的i0
2.2.2普通索引
这是最基本的索引，它没有任何限制。
创建的时候使用关键词INDEX或KEY。
如例子中的i1
2.2.3唯一索引
它与前面的普通索引类似，不同的就是：
索引列的值必须唯一，但允许有空值。
如果是组合索引，则列值的组合必须唯一。
创建的时候使用关键词UNIQUE。
如例子中的i2
2.3按索引的结构方式划分
2.3.1B-Tree索引
B-Tree索引是MySQL数据库中使用最为频繁的索引类型，除了Archive存储引擎之外的其他所有的存储引擎都支持B-Tree索引。
不仅仅在MySQL中是如此，实际上在其他的很多数据库管理系统中B-Tree索引也同样是作为最主要的索引类型，这主要是因为B-Tree索引的存储结构在数据库的数据检索中有非常优异的表现。
一般来说，MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的结构来存储的，也就是所有实际需要的数据都存放于Tree的LeafNode，而且到任何一个LeafNode的最短路径的长度都是完全相同的，所以我们大家都称之为B-Tree索引当然，可能各种数据库（或MySQL的各种存储引擎）在存放自己的B-Tree索引的时候会对存储结构稍作改造。
如Innodb存储引擎的B-Tree索引实际使用的存储结构实际上是B+Tree，也就是在B-Tree数据结构的基础上做了很小的改造，在每一个LeafNode上面出了存放索引键的相关信息之外，还存储了指向与该LeafNode相邻的后一个LeafNode的指针信息，这主要是为了加快检索多个相邻LeafNode的效率考虑。
在Innodb存储引擎中，存在两种不同形式的索引，一种是Cluster形式的主键索引（PrimaryKey），另外一种则是和其他存储引擎（如MyISAM存储引擎）存放形式基本相同的普通B-Tree索引，这种索引在Innodb存储引擎中被称为SecondaryIndex。
2.3.2Hash索引
存储的时候会把key通过Hash函数计算，得到key的Hash值,再用这个Hash值做指针和数据库记录指针绑定在一起。
选定一个好的Hash函数很重要，好的Hash函数可以使计算出的Hash值分布均匀，降低冲突，只有冲突减小了，才会降低Hash表的查找时间。
Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。
所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。
然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。
在Memory存储引擎中，MySQL还支持非唯一的Hash索引。
可能很多人会比较惊讶，如果是非唯一的Hash索引，那相同的值该如何处理呢？
在Memory存储引擎的Hash索引中，如果遇到非唯一值，存储引擎会将他们链接到同一个hash键值下以一个链表的形式存在，然后在取得实际键值的时候时候再过滤不符合的键。
由于Hash索引结构的特殊性，其检索效率非常的高，索引的检索可以一次定位，而不需要像BTree索引需要从根节点再到枝节点最后才能访问到页节点这样多次IO访问，所以Hash索引的效率要远高于B-Tree索引。
可能很多人又会有疑问了，既然Hash索引的效率要比B-Tree高很多，为什么大家不都用Hash索引而还要使用B-Tree索引呢？
任何事物都是有两面性的，Hash索引也一样，虽然Hash索引检索效率非常之高，但是Hash索引本身由于其实的特殊性也带来了很多限制和弊端，主要有以下这些：
1.Hash索引仅仅只能满足“=”,“IN”和“<=>”查询（注意<>和<=>是不同的操作），不能使用范围查询；
由于Hash索引所比较的是进行Hash运算之后的Hash值，所以Hash索引只能用于等值的过滤，而不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证还和Hash运算之前完全一样。
2.Hash索引无法被利用来避免数据的排序操作；
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值的完全一样，所以数据库无法利用索引的数据来避免任何和排序运算；
3.Hash索引不能利用部分索引键查询；
对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并之后再一起计算Hash值，而不是单独计算Hash值，所以当我们通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用到；
4.Hash索引在任何时候都不能避免表扫描；
前面我们已经知道，Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中
升级会员 