MySQL性能优化-索引

索引

1、索引类型

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 资源的消耗。

2、索引的使用

聚簇索引和非聚簇索引

mysql中普遍使用B+Tree做索引，但在实现上又根据聚簇索引和非聚簇索引而不同。
聚簇索引：
实际存储的循序结构与数据存储的物理机构是一致的，所以通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引，当然有人说我不想拿主键作为聚簇索引，我需要用其他字段作为索引，当然这也是可以的，这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键。

非聚簇索引：
非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

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

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

索引使用总结

● 在where 从句，group by 从句，order by 从句，on 从句中出现的列。
● 索引字段越小越好
● 离散度大的列放到联合索引的前面
● 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
● 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
● MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录，但由于MySQL在查询时最多只能使用一个索引。因此，如果 WHERE条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度，所以可以将where条件的参数和排序中的参数合并成一个复合索引。

当然，数据查询只能用到一个索引，这个表达不精确，只限于单表的查询，而联表查询实际上每个表都可以有其独立的索引被用到。

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

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

应该尽量让MySql使用索引进行排序。当不能使用索引生成排序结果的时候，MySql需要自己进行排序。如果数据量小于“排序缓冲区”的大小，则MySql使用内存进行“快速排序”操作。如果数据量太大超过“排序缓冲区”的大小，那么MySql只能采用文件排序，而文件排序的算法非常复杂，会消耗很多资源。

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序。所以让MySql根据索引构造排序结果非常的重要。

3、对索引的理解

索引是优化查询的算法

一般数据库的索引大概是btree，b+tree，类似这样的结构，那么现在非关系型数据库特别流行，也就是所谓的key-value数据库，最求极端效率，通常是 hash结构的数据索引。

数据索引提供了一种有序，在有序的情况下，进行检索，二分法效率最高，n条记录中定位查询开销是 log2(N)（hash索引效率更高，但不提供关系型查询，应用场景比较受局限）。

那么所谓的btree结构也好，或其他的类似结构也好，把握一个原则，接近二分法的查询效率，因为如果做一个完全有序的队列，那么插入，删除，修改需要做的操作开销太大了，所以才会有人设计树形结构，兼顾查询和更新操作。

查询效率的关键是有序，二分，反过来理解就是，无需遍历所有数据，即可实现快速的定位。

如何理解索引的使用和效率呢？
你就把索引当作是一个有序数列放在脑子里，然后思考这个SQL，
这个条件子句和排序子句，能否在这个索引的连续范围内精确命中结果，
也就是所谓索引命中率高，这个查询就效率高，
如果无法在索引这个有序数列连续范围内精确命中，查询效率就不高。
那有人说了，索引并不是真的有序数列啊，我说的是一种模拟的思考方式，
这样思考效率最高。

举例

比如一个社区，我希望用户进来，就能看到本地的用户，当然，是最新在线的，否则都是死用户就无法交流了。

SQL: select * from user where area='$area' order by lastlogin desc limit 30;

(这个 limit 特别重要)

● 正确的索引是area+lastlogin 的有序复合索引。
● 如果只把area当作索引会怎样，数据库会把符合这个area的所有结果拿出来，然后按照lastlogin排好序给你，这样就要遍历所有符合这个area的用户记录；
● 如果只把lastlogin作为索引会如何，我们想象，lastlogin是一个有序的数列，数据库会从最后一条开始往前挨条遍历，每条都去比对area是不是符合查询条件，直到数出30条，遍历结束，请注意，不是全部遍历，在这里，如果area 是个热门城市，比如上海，北京，可能遍历200次左右就出结果了，效率很快，但如果是个冷门城市，可能要遍历几千条几万条结果，甚至全部数据表遍历都凑不出符合条件的30条。这样效率就要命了。 所以用lastlogin为索引，效率存在风险。
● 那么两个都建立索引呢？这个mysql只会选择一个索引.
● 那么如果把lastlogin+area建立索引呢？你们设想一下，两个字段拼在一起，作为有序数列，然后数据库去查询的时候，lastlogin+area,这时候area是没用的后缀，在排序中根本体现不出他存在的意义，和单独lastlogin索引是完全一样的。

就是索引中遍历的记录越少，效率越高，遍历的记录越多，效率越差。 在慢查询日志或者explain分析中，一个重要的指标是 affected rows。

执行开销与影响结果集线性正相关

一条查询语句，其执行开销，在大多数情况下，与影响结果集，也就是索引扫描行数，呈线性相关。

经典案例1，大翻页问题
按最新更新的板块第一页帖子

select * from post where boardid=$id order by lastupd desc limit    0,30;

按最新更新的板块第100页帖子

select * from post where boardid=$id order by lastupd desc limit 3000,30;

这两个SQL 看上去只有limit有区别，索引都是boardid+lastupd (不要搞错顺序，理解一下)
但第一条SQL索引只扫描30行；第二条SQL索引扫描了3030行，其效率是第一条SQL的1/100.

搜索引擎的蜘蛛抓取 大翻页就是 这样把论坛搞死的。

经典案例2，积分排行问题

select count(*) from gamescore where gameid=$gameid and score>'$score' ;

gameid+score复合索引，顺序不能错。
看结果，如果你游戏成绩特别好，前几名，前几十名，你的结果就是索引扫描行数。
如果你的游戏成绩很烂，几万名，几十万名，那么索引扫描了几万条，几十万条，就效率非常低了，如果有一批人同时在提交成绩，又都是这种几万名，几十万名的用户，数据库非崩溃不可，你再多服务器也白搭。
所以，常见的解决方法是，积分排行只针对最靠前的用户提供，后面只给估算或区间了。

当然，这里有个终极方案，用redis的有序数组结构，一劳永逸的解决这个问题。

4、 维护及优化

重复及冗余索引

● 重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中的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

5、索引对DML的影响

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

6、B-tree和B-tree+

B-tree，B是balance，一般用于数据库的索引。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。而B+tree是B-tree的一个变种，大名鼎鼎的MySQL就普遍使用B+tree实现其索引结构。

那数据库为什么使用这种结构？

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

为了达到这个目的，磁盘按需读取，要求每次都会预读的长度一般为页的整数倍。而且数据库系统将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。并把B-tree中的m值设的非常大，就会让树的高度降低，有利于一次完全载入。

my-way查找树

首先介绍一下m-way查找树，顾名思义就是一棵树的每个节点的度(子节点)小于等于m。

故，它的性质如下：
●每个节点的键值数小于m
●每个节点的度小于等于m
●键值按顺序排列
●子树的键值要完全小于或大于或介于父节点之间的键值

B-tree

B-tree是一种平衡的m-way查找树。

B-tree利用多个分支（称为子树）的结点，减少获取记录时所经历的结点数，从而达到节省存取时间的目的。

一棵度为m的B-tree应满足的性质：
●每个结点的子结点个数≤m；
●根结点若不是叶子结点，它至少有两个子结点
●除根和叶子结点外，每个结点的子结点个数≥ [m/2]
●所有的叶子结点都出现在同一层，而且不带有信息
●非叶子结点若具有j+1个子结点，那么它包含j个关键字(其中，j≤m-1)

B-Tree查找：
在给定的m阶B-树中查找一个给定值v相等的关键字，必须从根结点开始进行查找，一般采用二分查找
B-Tree插入：
●插入的节点少于M-1个键值，则直接插入。
●插入的节点的键值已等于m-1，则将此节点分为二，因为一棵m的B-tree，最多只能有m-1个键值

B-tree+

B+树是B-树的变体。

有几点不同的地方：
●非叶子结点的子树指针与关键字个数相同
●为所有叶子结点增加一个链指针
●所有关键字都在叶子结点出现