MySql数据库之索引优化

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。

索引的数据结构

B-Tree索引？

维基百科对B树的定义为“在计算机科学中，B树（B-tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同，B-树为系统最优化大块数据的读和写操作。B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度。普遍运用在数据库和文件系统。”

目前大多数的存储引擎使用B-Tree索引，严格来说是 B+树。相比B树，二叉树，Hash，它有哪些优势呢?

相对于二叉树，明显的优势是避免树的深度过大而造成磁盘I/O读写过于频繁；相对于Hash，见下面Hash索引限制描述；相比较B树来说，B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历；详情参考这篇文章。

适用场景：等值匹配，全值匹配，匹配最左前缀，匹配列前缀，范围匹配，只访问索引的查询，如覆盖索引。

Hash 索引？

哈希索引基于哈希表实现，只有精确匹配索引所有列时才有效。对于每一行数据，存储引擎都会根据索引列计算一个哈希值，哈希索引将所有的hash值存储在索引中，同时在哈希表中保存指向每个数据行的指针。

MySQL中只有Memory引擎显式支持哈希索引。这也是Memory引擎的默认存储引擎，Memory引擎同时也支持B-Tree索引，哈希索引解决碰撞的方式是使用链表。

Hash索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；

哈希索引数据并不是按照索引列的值顺序存储的，所以也就无法用于排序；

哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引的全部列值内容来计算哈希值的。如：数据列（a,b）上建立哈希索引，如果只查询数据列a，则无法使用该索引；

哈希索引只支持等值比较查询，如：=，in()， <=>，不支持任何范围查询；

访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行；

如果哈希冲突很多的话，一些索引维护操作的代价也很高，如：如果在某个选择性很低的列上建立哈希索引（即很多重复值的列），那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应的引用，冲突越多，代价越大；

适用场景：只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。

MyISAM和InnoDB对B-Tree索引实现

MyISAM索引文件和数据文件是分离的，索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行，对于二级（辅助）索引，与主索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，可见MyISAM索引是“非聚合的”。

InnoDB的主索引是采用“聚集索引”的数据存储方式，所谓“聚集”，就是指数据行和键值紧凑地存储在一起（InnoDB 只能聚集一个叶子页（16K）的记录），因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形；对于二级（辅助）索引，InnoDB采用的方式是在叶子页中保存主键值，通过这个主键值来回表查询到一条完整记录，因此按辅助索引检索实际上进行了二次查询，效率肯定是没有按照主键检索高的。由于每个辅助索引都包含主键索引，因此，为了减小辅助索引所占空间，我们通常希望 InnoDB 表中的主键索引尽量定义得小一些（值得一提的是，MySIAM会使用前缀压缩技术使得索引变小，而InnoDB按照原数据格式进行存储），并且希望InnoDB的主键是自增长的，因为如果主键并非自增长，插入时，由于写入时乱序的，会使得插入效率变低。

索引的优点

最常见的B-Tree索引，按照顺序存储数据，索引可以做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也会将相关的列存储在一起，因为索引中存储了实际的列值，所以某些查询只是用索引就能够完成全部查询（覆盖索引，索引包含所有满足查询需要的数据的索引，也就是平时所说的不需要回表操作）：索引大大减少了服务器需要扫描的数据量；索引可以帮助服务器避免排序和临时表；索引可以将随机IO变为顺序IO；

索引优化策略

独立的列

索引列不能是表达式的一部分，也不能是函数的参数，例如

1	select * from table where id + 1 = 5;

这个就不对了。此外对于列的类型也要注意一些优化：字段类型优先级：整型 > date, time > enum, char>varchar > blob；够用就行,不要慷慨 (如smallint,varchar(N))，原因是大的字段浪费内存,影响速度；尽量避免用NULL()，原因是NULL不利于索引,要用特殊的字节来标注；

前缀索引和索引的选择性

有时候需要索引很长的字符列，这会让索引变得很大且慢，除了模拟hash值存储的方式外，还可以索引开始部分的字符，这样可以大大节约索引空间，从而提高索引效率，但这样会降低索引的选择性（不重复的索引值(也称为基数)和数据表的记录总数(#T)的比值，范围从1/#T到1之间）。要注意以下几点：

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的；

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度；

计算合适的前缀长度的一个方法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性；

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY , 也无法使用前缀索引做覆盖扫描；

有时候后缀索引也有用途(例如，找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。

多列索引

合理使用联合索引，不要在where后面用到的每一列都加索引。

选择合适的索引列顺序

正确的索引顺序依赖于使用该索引的查询，并同时满足需要考虑如何更好地满足排序和分组的需要。

聚簇索引

InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。它的数据实际上存储在索引的叶子页中。”聚簇”表示把数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

聚簇索引的优点:可以把相关的数据保存在一起;数据访问更快;使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

聚簇索引的缺点：更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置；可能会导致页分裂；导致全表扫描变慢，尤其是行比较稀疏；二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列；二级索引访问需要两次索引查询，而不是一次。这是因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复工作：两次B-Tree查找而不是一次。

索引覆盖

索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据。这种查询速度非常快,称为”索引覆盖”。

索引与排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列的值为”index”,则说明MySQL使用了索引来做排序(不要和Extra列的 “Using index”搞混淆了)。

压缩(前缀压缩)索引

MyISAM使用前缀索引压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认值压缩字符串，但通过参数设置可以对整数压缩。InnoDB按照原数据格式进行存储。

冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意还是无意的。MySQL需要单独维护重复的索引，并且优化器在优化查询的是时候也需要逐个地进行考虑，这会影响性能。重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。冗余索引，如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。

未使用的索引

对于服务器上一些永远不用的索引，完全是累赘，建议考虑删除。

索引和锁

InnoDB只有在访问行的时候才会对其进行加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。

如何排查SQL语句？

show processlist

explain

参数值	含义
id	表示SELECT语句的编号；
select_type	表示SELECT语句的类型。该参数有几个常用的取值：SIMPLE：表示简单查询，其中不包括连接查询和子查询；PRIMARY：表示主查询，或者是最外层的查询语句；UNION：表示连接查询的第二个或后面的查询语句；
table	表示查询的表；
type	表示表的连接类型。该参数有几个常用的取值：const：表示表中有多条记录，但只从表中查询一条记录；eqref ：表示多表连接时，后面的表使用了UNIQUE或者PRIMARY KEY；ref：表示多表查询时，后面的表使用了普通索引；unique subquery：表示子查询中使用了UNIQUE或者PRIMARY KEY；index_ subquery：表示子查询中使用了普通索引； range ：表示查询语句中给出了查询范围；index ：表示对表中的索引进行了完整的扫描;all ：表示此次查询进行了全表扫描； ———– 该条SQL需要优化；
possible_keys	表示查询中可能使用的索引；如果备选的数量大于3那说明已经太多了，因为太多会导致选择索引而损耗性能，所以建表时字段最好精简，同时也要建立联合索引，避免无效的单列索引；
key	表示查询使用到的索引；
key_len	表示索引字段的一长度；
ref	表示使用哪个列或常数与索引一起来查询记录；
rows	表示查询的行数;试图分析所有存在于累计结果集中的行数，虽然只是一个估值，却也足以反映出SQL执行所需要扫描的行数，因此这个值越小越好；
Extra	表示查询过程的附件信息；