Mysql优化系列之索引性能

　　索引是sql优化的核心部分，在《高性能Mysql》中单独抽出一章讲，也印证了其重要性。这一篇也会讲的很细致。

　　以下所讲，除少数的如全文索引之外，均以Innodb存储引擎为基本

一、索引是什么

　　索引，在Mysql中也叫做"键（key）"，是存储引擎用于快速找到记录的一种数据结构。

　　这里我们注意到：索引是一种数据结构，节点是有序的，有大小，有时候一张表的索引甚至会有几个G的大小

　　另外，索引是在存储引擎层实现的，不同的存储引擎层实现也不同

二、有什么好处

　　这个问题，一般可能觉得没啥好回答的，但是我会告诉你，好的sql能使查询效率提高几倍几十倍，但是有索引和没索引，

查询的性能可能相隔几个数量级，不相信的读者可以亲自试一试。

三、有哪些类型

　　Mysql支持的索引类型有很多，我简单的列出几种

　　B-Tree索引：B-Tree读作B树，不是B减树，一种平衡多路查找树，这种结构的查找效率很高

　　B+Tree索引：现在大多数版本使用B+Tree索引，可以分为聚集索引（clustered index）和二级索引（secondary index）。

聚集索引的叶子节点存放的是整张表的行记录数据。二级索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的

聚集索引键，即主键。当通过二级索引来查询数据时，InnoDB存储引擎会遍历二级索引找到主键，然后再通过主键在聚集索引

中找到完整的行记录数据。

　　哈希索引：基于哈希表实现，这个用的比较少，支持的存储引擎也有限，不讲

　　全文索引：全文索引适用于查找文本中的关键词，而不是简单的where条件操作，这里也不讲，基本没用到

　　我们用的最多的B-Tree大致分为普通索引normal，唯一索引unique，联合索引union，另外主键Primary Key也是索引

四、B-Tree索引适用的有效查询情况(这里是重点，写sql一定注意)

• 全值匹配

　　即为等值匹配 where a = 1。失效情形：where a != 1

• 列前缀匹配

　　where name like "mike%"，失效情形：where name like "%mike"

• 最左前缀匹配

　　　　这里最常见的是联合索引的情况。考虑列a,b,c,建立联合索引（a,b,c），请问哪些查询可以用到这个索引？

　　　　有效情形：查a,查a,b,查a,b,c(注意顺序)。

　　　　失效情形：查b,查c,查b,c；查a=1 and b>1 and c=1,此时只能用到索引a，中间不能有范围查询

• 索引不仅适用于where，也使用于order by子句
• 索引必须是独立的，不能是表达式的一部分。失效：where a+1 = 5，无法使用索引列a
• 一些巧妙使用索引的sql语句优化，我会放在下一篇sql语句查询优化讲

五、高性能的索引策略

　　我会以例子做引子来讲，这样比较清晰

• 前缀和后缀索引。用户表中的电子邮箱字段，如何查邮箱是163类型的记录？

　　这里我们可以使用"后缀索引",因为163邮箱的后缀字符都是以@163.com结尾，那么我们存储时其实可以将邮箱反转后存储，并以

邮箱字段的前若干位建一个前缀索引，add index idx_name((email(8)),这种情况遇到的也不多

• 多列索引

　　不要粗略的把where子句中的字段都建上索引。要懂得合并索引，前面的索引适用的例子已经讲了，这里不再重复。另外补充一点

将OR条件的2个列单独建索引，然后union后合并结果是一种更好的选择

• 索引列的顺序

　　多列索引的列顺序很重要，这一点在where子句中也会讲到。将能筛选掉最多（筛选后最少）的列优先作为前缀列，这只是一个

经验的做法，有很多具体的数据特征需要去分析

• 聚簇索引

　　当表有聚簇索引时，数据行实际上存放在索引的叶子页中。聚簇，意即数据行和相邻的key值紧凑的存储在一起。

• 覆盖索引

　　一种比较特殊的索引情况。select的列被所建的索引覆盖。那么查询时不必读取数据行，直接从索引中获取。

• 使用自增列主键

　　记住，不要使用非常随机的UUID值作为索引列，这种做法很不负责任，无论是插入时更新索引，还是查询时遍历索引都非常烂。

• 冗余索引，重复索引，以及不会使用的索引，出于我们的强迫症属性，大胆的删除掉吧

　　最后，关于索引可以讲很多很多，不论是理论的东西还是实际的分析，真的很多，笔者省去了4种常见索引的情景，有一些其实

根据索引类型的名称即可知道怎么用，比如唯一索引，就是保证这个索引下的列值必须唯一，比如不能重复的订单号，优惠券兑换码

之类的。其他的一些东西还是得多用客户端Navicat去explain，去试才能更好的选择和使用。

　　PS：格式还是没花时间排，感觉很乱*********

Mysql优化系列之索引性能

标签：文本   表达式   失效   str   个数   必须   clu   数据   适用于