MySQL索引

前言

• 索引是存储引擎用于快速找到记录的一种数据结构。
• 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。
• 索引优化应该是对查询性能优化最有效的手段了。

索引基础

要理解索引工作的原理，就好比你看一本书，你会先看书的目录，然后找到对应主题的页码，再根据页码翻到指定的页。

索引的类型

索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准。

聚簇索引

• 并不是一个单独的索引类型，而是一个数据存储方式
• innodb的聚簇索引在同一个数据结构中保存了B-Tree索引和数据行
• 聚簇表示数据行和相邻的键值紧凑地存储在一起
• 一些数据库服务器允许选择哪个索引作为聚簇索引，而mysql内建的存储引擎不支持这一点
• innodb通过主键索聚集数据，如果没有主键，innodb会选择一个非空的唯一索引代替，如果没有这样的索引，innodb会隐式定义一个主键作为聚簇索引。
• innodb只聚集同一个页面中的记录，包含相邻键值的页面可能会相距甚远

聚簇索引的优点

• 把相关的数据聚集在一起，例如实现电子邮箱时，可以根据用户id来聚集数据，这样只需要从磁盘读取少数的数据也就能获取某个用户的全部邮件，如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O
• 数据访问更快，无需回表。
• 使用覆盖索引扫描的查询可以直接使用页节点中的键值。

聚簇索引的缺点

• 聚簇索引最大限度地提高了I/O密集型应用的功能，但如果数据全部放入到内存中，则访问的顺序就没那么重要了，聚簇索引就没什么优势了。
• 插入速度严重依赖插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMEZE TABLE命令重新组织一下表。–为什么？
• 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 插入新的行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

Myisam和InnoDb的索引区别

Myisam

• 主键索引和非主键索引，叶子保存的是行号- 行指针
• 按照插入的顺序保存在磁盘，查找磁盘可能根据行号跳过一些行

InnoDB

• 二级索引存储的是主键索引，当主键行移动时，无需变动二级索引或者数据页分裂时二级索引的维护工作。
• 主键索引会让二级索引占用更多的空间。

在InnoDB表中按主键顺序插入行

• 自增主键，顺序插入，保证数据聚集
• UUID会导致随机插入，使得数据没有任何聚集特性，导致也分裂和碎片
  • 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到磁盘读取目标页到内存中。这将导致大量的随机I/O。
  • 因为写入是乱序，InnoDB不得不频繁地做页分裂操作，以便新的行分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
  • 由于频繁的页分裂，页会变得稀缺并被不规则地填充，所以最终数据会碎片化。

顺序的主键什么时候会造成更坏的结果？

• 高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。
• 另一个热点可能是auto_increment锁机制，所以设置innodb_autoinc_lock_mode参数

覆盖索引（B-Tree才有）

• 索引的叶子节点中已经包含要查询的字段的值，称之为覆盖索引
• 查询只需要扫描索引无需回表

优点

• 索引条目通常远小于数据行打小，所以如果只读取索引，那MySQL就会极大地减少数据访问量。
• 让简单的范围查询能使用完全顺序的索引访问。
• 避免回表查询

未来MySQL版本的改进

• 5.6以前 存储引擎API设计导致的，API设计不允许MySQL将过滤条件传到存储引擎层，而是把数据从存储引擎拉到服务器层，再根据查询条件过滤。
• 5.6 索引条件推送，大大改善现在的查询执行方式

使用索引扫描来做排序

• 按照索引顺序来读取行的速度通常要比全表扫描慢，因为索引需要回表查询，基本上都是随机I/O。
• 可以使用同一个索引既满足排序，又用于查找行。
• 只有当索引的列顺序和ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。
• 如果是关联查询，则只有当ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。
• ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。

压索（前缀压索）索引

• Myisam使用前缀压索索引来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下极大地提高性能。默认之压缩字符串，但通过参数设置页可以对证书做压缩。
• 方法：先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。
  • 例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压索后存储的类似“7，ance”这样的形式。
  • Myisam对行指针也采用类似的前缀压索方式。
• 只能从头开始搜索，不能使用二分法
• 倒序更慢

冗余和重复索引

• MySQL允许在相同列上创建多个索引。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。
• 以下创建了重复的索引

   create table test( 
   id int not null primary key,
   a int not null,
   b int not null
   unique(id),
   index(id)
)  engine=innodb

• 存在index(a,b)，在创建index(1) 属于冗余索引；(a,id)也属于冗余索引，因为id存在二级索引中，即叶子中。
• 表的索引越多，维护的成本越高。

未使用的索引

• 可能存在一些服务器永远不用的索引，这样的索引完全是累赘，建议删除。
  • 有的索引相当于唯一约束，虽然该索引一直没有被查询使用，却可能是用于避免产生重复数据的。
• 办法：在Percona Server 说着MariabDB中先打开userstates服务器变量(默认是关闭的）,然后让服务器运行一段时间，再通过查询INFORMATION_SHCEMA.INDEX_STATISTICS就能查询到每个索引的使用频率。-p 188

索引和锁

• 索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行。
  • 减少锁定行，减少开销
  • 提高并发性
• InnoDB只有在访问行的时候才会对其加锁

避免多个范围查询

• 范围查询后面索引全失效,ps: in 后面会走索引
• 如果未来版本的MySQL能够实现松散索引扫描，就能在一个索引上使用多个范围条件

优化排序

• 使用文件排序对小数据集很快，但如果一个查询匹配的结果有上百万行的话会很慢
• 查询同时有ORDER BY和LIMIT，没有索引会很慢，但如果翻页到很靠后也可能非常慢
  • 使用延迟关联优化,先使用覆盖索查询出主键，再用inner join关联

     select id from profiles where  x.sex='m' order by rating limit 1000000,10  
   )  as x using id
  

维护索引和表

• 修改损坏的表
• 维护准确的索引统计信息
• 减少碎片

找到并修复损坏的表

• 使用check table t 能够找出大多数表和索引的错误
• repair table 能够修复损坏的表
• alter table t engine=innodb 重建表

更新索引统计信息

• MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。
  • 第一个API是records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少记录。Myisam是返回精确值，而Innodb返回的是估算值。
  • 第二个API是info(),该接口返回各种类型的数据,包括索引的基数(每个键值有多少条记录)
• 如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以至无法精准获取各个阶段匹配的的行数，那么优化器会使用索引统计信息来估算扫描行数。 如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。
  • 通过analyze table 来重新生成统计信息解决这个问题
• 每个存储引擎实现索引统计信息的方式不同
  • Memory引擎根本不存储索引统计信息
  • Myisam将索引统计信息存储在磁盘中，analyze table需要进行一次全索引扫描来计算索引基数。整个过程需要锁表。
  • 知道MySQL 5.5版本，InnoDB也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估将其存储在内存中。
• 使用 show index from 表明 查看索引的基数(Cardinality)
• InnoDb引擎通过抽样的方式来计算统计信息，首先先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。老的InnoDB，样本的页面数量为8，可以通过设置innodb_stats_transient_sample_pages来设置更大的值，理论上来说可以帮助生成更准确的索引信息，特别是对于某些超大的数据表来说，但具体设置多大合适依赖于具体的环境。
• 通过设置innodb_stats_persistent将统计信息持久化到系统表

减少索引和数据的碎片

• B=Tree索引可能会碎片化，这会降低查询的效率
• B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询，索引覆盖扫描等操作来说，速度可能会降低很多倍。
• 表的数据存储可能碎片化
  • 行碎片，指的是数据行被存储为多个地方的多个片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降。-InnoDB不存在这种情况，它会移动短小的行并重写到一个片段中。
  • 行间碎片，指的是逻辑上顺序的页，或者行在磁盘上不是顺序存储。行间碎片对注入全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
  • 剩余空间碎片，值得是数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。
• 解决办法
  • optimize table 或者导出导入的方式来重新整理数据，这对多数存储引擎都是有效的。
  • 重建索引
  • alter table t engine=InnoDB

总结

• 在选择索引和编写利用索引查询时
  • 单行访问是很慢的，创建索引
  • 按照顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多。第二，如果服务器能够按需要顺序读取数据，那么久就不再需要额外的排序操作，并且GROUP BY 查询有无需再做排序和将行按组聚合计算了。
  • 索引覆盖查询是很快，无需回表。