MySQL——索引

为什么要使用索引？

• 索引大大减少了存储引擎需要扫描的数据量。以 InnoDB 来说，发生一次 IO，最小的存储单位是以页为单位的，所以一页内存储的信息越多，那么读取效率也就越快，默认情况下，InnoDB 一页的大小为 16k，由于索引的大小比数据要小的多，所以一页内可以存储更多的索引，因此通过索引查找所需要读取的页非常少，减少了存储引擎需要扫描数据的数据量，加快了查找速度；
• 数据行的物理地址通常是随机分布的，采用索引进行查找，可以把随机I/O变为顺序I/O，可以更加充分的发挥磁盘的 I/O 性能。
• 索引帮助服务器避免排序和临时表。
• 每次查找数据时把磁盘IO次数控制在一个很小的数量级

磁盘IO与预读

• 磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。
• 每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO

BTree索引和Hash索引

通常索引的数据结构是 B+ 树，除此之外还有 Hash 结构。

BTree索引

MyISAM和InnDB的BTree索引对有何不同？

MyISAM使用前缀压缩技术使得索引更小，InnoDB按照原数据格式进行存储。

MyIASM索引通过数据的物理位置引用被索引的行（查询更快），InnoDB根据主键引用被索引的行

索引有哪些限制？

例如：last_name（姓）、first_name（名）、dob顺序加索引

• 如果不是按照索引的最左列开始查找，则用不到索引。如无法查找名字为bill，也无法查询某个特定生日的人
• 不能跳过索引中的列。如无法查找姓为Smith在某个特定生日的人。
• 如果查询中有某个列的范围查询，则右边所有列都无法使用索引。如where last_name=‘Smith’ and first_name like ‘J%’ and dob=‘1993-03-04’，这个查询只能用到索引的前2列，因为like是一个范围查询

Hash索引

InnoDB 除了支持 BTree 索引，还支持 Hash 索引。Hash 索引性能理论上要高于 BTree 索引。

下面看一下 Hash 索引的特点：

• Hash 索引是基于 Hash 表实现的，只有查询条件精确匹配 Hash 索引中的所有列时，才能够使用到 Hash 索引，也就是说 Hash 索引只能用到等值查询中，范围查询和模糊查询就不能使用 Hash 索引；
• 对于 Hash 索引中的所有列，存储引擎都会为每一行计算一个 Hash 码，Hash 索引中存储的就是 Hash 码，同时在 Hash 索引的表中还保存了每一个 Hash 索引所代表数据行的指针，由于 Hash 索引只存储了键值和 Hash 码以及对应行的指针，索引中并没有保存字段的值，所以 Hash 索引的存储结构是十分紧凑的，使得 Hash 索引找到数据的速度非常快。

Hash 索引的限制？

• 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引避免读取行
• 哈希索引数据不是按照索引值顺序存储的，所以不能用于排序
• 哈希索引只支持等值比较查询
• 访问哈希索引的速度非常快，除非有很多哈希冲突。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针进行比较找到符合条件的行
• 如果哈希冲突很多的话，索引维护操作的代价会很大。比如，在某个选择性很低的列建立哈希索引，当从表中删除一行数据时，存储引擎需要遍历哈希值冲突链表的每一行

建索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

索引优化策略

索引上不能使用表达式或函数

例如对索引上的日期列进行计算：

select ... from order where to_days(out_date)-to_days(current_date)<=30;

前缀索引和索引的选择性

索引很长的字符串会让索引变的大而且慢，可以索引开始的部分字符，这样节约索引空间，提高索引效率，但这样会降低索引的选择性。

索引的选择性是指，不重复的索引值和数据表的记录总数的比值，索引的选择性越高可以过滤掉更多的行，索引效率更高。

联合索引

联合索引中索引列的顺序尤为重要，那么如何选择索引列的顺序呢？

• 不需要排序和分组的时，将选择性高的列放在前面通常是很好的。

聚簇索引

聚簇索引是叶子节点中存储了索引和完整的用户数据

优点：

• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中。不用做回表避免了磁盘IO
• 使用覆盖索引扫描的查询可以直接使用叶子节点中主键值。

缺点：

• 如果数据全部加载进内存，聚簇索引就没有优势了。
• 基于聚簇索引的表在插入新行或者主键需要更新导致移动行的时候，有页分裂的问题。页分裂会导致占用更多的磁盘空间。

覆盖索引

覆盖索引是 select 的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

优点：

• 由于 B-tree 索引是按照索引的键值进行存储的，所以对于 IO 密集型的范围查找来说，可以减少随机 IO，变随机 IO 操作变为顺序 IO 操作；
• 对于 InnoDB 存储引擎，可以避免对 InnoDB 主键索引的二次查询。通常情况下，通过索引查找到相应键值后，还需要通过主键索引二次查询才能获取到数据行，而在覆盖索引中，可以通过索引获取全部的数据，避免二次查询，减少相关 IO 操作；

索引和锁

锁定行会带来额外开销，锁定超过需要的行会增加锁争用并且减少并发性，而索引可以让查询锁定更少的行。

InnoDB只有在访问行的时候才会对其加锁，而索引可以减少InnoDB访问的行数，从而减少锁的数量。InnoDB在存储引擎层过滤掉不需要的行。

为什么性别不适合建索引？

因为你访问索引需要付出额外的IO开销，你从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。