Mysql性能优化-索引

索引

索引简介

MySQL中，主要有四种类型的索引，分别为：B-Tree索引，Hash索引，Fulltext索引和R-Tree索引。

B-Tree

一般来说，MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的。 MySQL 的各种存储引
擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个Leaf Node 上面除了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。

在 Innodb 存储引擎中，存在两种不同形式的索引，一种是 Cluster 形式的主键索引（Primary Key），另外一种则是和其他存储引擎（如 MyISAM 存储引擎）存放形式基本相同的普通 B-Tree 索引，这种索引在 Innodb 存储引擎中被称为 Secondary Index。

图示中左边为 Clustered 形式存放的 Primary Key，右侧则为普通的 B-Tree 索引。两种索引在Root Node 和 Branch Nodes 方面都还是完全一样的。而 Leaf Nodes 就出现差异了。在 Primary Key中，Leaf Nodes 存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数据以主键值有序的排列。而 Secondary Index 则和其他普通的 B-Tree 索引没有太大的差异，只是在Leaf Nodes 除了存放索引键的相关信息外，还存放了 Innodb 的主键值。

所以，在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过 Secondary Index 来访问数据的话，Innodb 首先通过 Secondary Index 的相关信息，通过相应的索引键检索到 Leaf Node之后，需要再通过 Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。

MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的 Secondary Index 的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在 Leaf Nodes 上面出了存放索引键信息之外，再存放能直接定位到MyISAM 数据文件中相应的数据行的信息（如 Row Number），但并不会存放主键的键值信息。

Hash 索引

Hash 索引在 MySQL 中使用的并不是很多，目前主要是 Memory 存储引擎使用，而且在 Memory 存储引擎中将 Hash 索引作为默认的索引类型。所谓 Hash 索引，实际上就是通过一定的 Hash 算法，将需要索引的键值进行 Hash 运算，然后将得到的 Hash 值存入一个 Hash 表中。然后每次需要检索的时候，都会将检索条件进行相同算法的 Hash 运算，然后再和 Hash 表中的 Hash 值进行比较并得出相应的信息。

由于 Hash 索引结构的特殊性，其检索效率非常的高，索引的检索可以一次定位，而不需要像 BTree索引需要从根节点再到枝节点最后才能访问到页节点这样多次 IO 访问，所以 Hash 索引的效率要远高于 B-Tree 索引。

既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，，Hash 索引也一样，虽然 Hash 索引检索效率非常之高，但是 Hash 索引本身由于其实的特殊性也带来了很多限制和弊端，主要有以下这些：

• Hash 索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
• Hash 索引无法被利用来避免数据的排序操作；
• Hash 索引不能利用部分索引键查询；
• Hash 索引在任何时候都不能避免表扫面；
• Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 B-Tree 索引高；

Full-text 索引

Full-text 索引也就是我们常说的全文索引，目前在 MySQL 中仅有 MyISAM 存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有 CHAR，VARCHAR 和 TEXT 这三种数据类型的列可以建 Full-text 索引。

一般来说，Fulltext 索引主要用来替代效率低下的 LIKE ‘%*%’ 操作。实际上，Full-text 索引并不只是能简单的替代传统的全模糊 LIKE 操作，而且能通过多字段组合的 Full-text 索引一次全模糊匹配多个字段。

MySQL 目前的 Full-text 索引在中文支持方面还不太好，需要借
助第三方的补丁或者插件来完成。而且 Full-text 的创建所消耗的资源也是比较大的，所以在应用于实际生产环境之前还是尽量做好评估。

R-Tree 索引

R-Tree 索引可能是我们在其他数据库中很少见到的一种索引类型，主要用来解决空间数据检索的问题。

在 MySQL 中采用了具有二次分裂特性的 R-Tree 来索引空间数据信息，然后通过几何对象（MRB）信息来创建索引。

虽然仅仅只有 MyISAM 存储引擎支持空间索引（R-Tree Index），但是如果我们是精确的等值匹配，创建在空间数据上面的 B-Tree 索引同样可以起到优化检索的效果，空间索引的主要优势在于当我们使用范围查找的时候，可以利用到 R-Tree 索引，而这时候，B-Tree 索引就无能为力了。

索引所给我们带来的收益只是提高表数据的检索效率吗？当然不是，索引还有一个非常重要的用途，那就是降低数据的排序成本。

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

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

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

如何判定是否需要创建索引

• 频繁作为查询条件的字段应该创建索引；
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件：
  如状态字段，类型字段这些字段中存放数据可能就那么几种，每个值都会存放于成千上万或是更多的记录中，对于这类字段，完全没有必要创建索引。因为即使创建了索引，MySQL Query Optimizer大多数也会不去选择使用，由于索引字段中每个值都含有大量记录，那么存储引擎在根据索引访问数据的时候会带来大量随机IO，甚至有些时候可能会出现大量的重复IO。
• 更新非常频繁的字段不适合创建索引。索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的，这时带来IO访问量的较大增加。
• 不会出现在WHERE子句中的字段不该创建索引。

如何选择合适的列建立索引

• 在where 从句，group by 从句，order by 从句，on 从句中出现的列。
• 索引字段越小越好
• 离散度大的列放到联合索引的前面

维护及优化

重复及冗余索引

• 重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中的primary key和ID列上的索引就是重复索引。

create table test(    id int not null primary key,    name varchar(10) not null,    title varcahr(50) not null,    unique(id)) engine = innodb;

• 冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。

查找重复及冗余的索引

使用pt-duplicate-key-checker工具检查重复及冗余索引：

pt-duplicate-key-checker \-uroot \-p '' \-h 127.0.0.1 

删除不用的索引

目前MySQL中还没有记录索引的使用情况，但是在PerconMySQL 和 MariaDB中可以通过INDEX)STATISTICS表来查看那些索引未使用，但是MySQL中目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析。

pt_index-usage \-uroot -p '' \mysql-slow.log

索引使用

• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
• 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

• MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录，但由于MySQL在查询时最多只能使用一个索引。因此，如果 WHERE条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度。

  SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;

  在以上的SQL的WHERE子句中已经使用了NAME字段上的索引，因此，在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题，我们可以对SALES表建立复合索引：

索引对DML的影响

• insert 不会提高，insert 用不到索引，只会增加维护索引的时间。
• update，更新索引列不会提高，少量更新非索引列，会有提高 ； 更新索引列，索引要重新维护，更新非索引列，倒是没什么影响 。
• delete，这个要看删除的条件的是怎么写的，如果条件用到索引了，会提高，没有用到，会全表扫描。 也不会提高。