MySQL|【MySQL】创建高性能的索引架构|数据库|mysql

目录
索引基础
索引类型
索引的优点
高性能的索引策略
维护索引和表

索引基础
MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。
索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引最左前缀列。创建一个包含两个列的索引，和创建两个包含一列的索引是大不相同的。
索引类型
索引是在存储引擎层而不是服务器层实现的。不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
B-Tree索引
大多数MySQL引擎都支持这种索引。不同的存储引擎也可能使用不同的结构存储，NDB内部实际上使用T-Tree，InnoDB则使用的是B+Tree。
存储引擎以不同的方式使用B-Tree索引。例如：MyISAM使用前缀压缩技术使索引更小，InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
B-Tree通常意味着所有的值都按顺序存储，且每一个叶子页到根的距离相同。

文章图片

图B+树

B-Tree能快速访问数据，因为存储引擎不需要进行全表扫描来获取需要的数据，取而代之是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层节点
B-Tree对索引基于顺序存储，所以很适合查找范围数据。
B-Tree索引的限制:

如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

B+ 树与 B 树差异的点，主要是以下这几点：

叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
非叶子节点中有多少个子节点，就有多少个索引

哈希索引
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行指针。只有Memory引擎显式支持哈希索引。
优缺点：索引结构紧凑，查询快，但是索引只包含哈希值和行指针，不能和BTree一样使用索引中的值来避免读取行。自然也无法用于排序。不支持部分索引列匹配查找。只支持等值比较查询。哈希冲突时必须遍历。
自适应哈希索引：InnoDB注意到某些索引值引用频繁时，会在内存中基于BTree索引之上再创建一个哈希查找（完全自动的，内部的行为）
创建自定义哈希索引：依然使用B-Tree查找，但是它使用哈希值而不是键本身进行索引查找
空间数据索引 R-Tree
MyISAM表支持，无须前缀索引，而是从所有维度来索引数据。查询时可以有效使用任意维度来组合查询。
全文索引
查找的是文本中的关键词，而非索引中的值。
其他索引
如TokuDB使用分形树索引。
索引的优点

索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变为顺序I/O。

缺点
缺点：

使用索引会影响插入数据和修改数据的性能。
有时坏的索引会诱导优化器进行很差的索引查询，甚至比全表查询还要慢。
一个索引是否适合某个查询的“三星系统”。

索引将相关的记录放到一起则获得一星；
如果索引中的数据顺序和查找中的排列顺序一致则获得二星；
如果索引中的列包含了查询中需要的全部列则获得三星
高性能的索引策略
独立的列
如果查询中的列不是独立的，则MySQL就不会使用索引。”独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数，如下：

文章图片

文章图片

前缀索引和索引选择性
可以通过索引开始的部分字符，这样可以大大节约索引空间，从而提升索引效率。但这样会降低索引选择性，索引选择性是指，不重复的索引值和数据表的记录总数的比值，范围从1/T#到1之间。索引选择性越高则查询效率越高。
多列索引
注意不是为每个列创建独立的索引，或者按照错误的顺序创建多列索引
索引合并：一定程度缓解”在多个列上建立独立的单独索引“对性能的拖垮。对于下列查询条件（注意actor_id和film_id是分别建立的主键）

文章图片

古老版本中会全表查询，除非改成用UNION来连接两个查询的结果。5.0以后版本会同时使用这两个单列索引进行查询，并将结果进行合并。这种算法有三种情况OR条件的联合，AND条件的相交，组合前两种情况的联合及相交。
不过索引合并策略是一种优化的结果，实际上更多时候说明了表上的索引建得很糟糕。在EXPLAIN中的Extrea可以看到是否引用了其他单独的索引。如果有的话，应该好好检查一下查询和表的结构。

当出现服务器对多个索引做相交操作时（通常有多个AND条件），意味着需要一个包含这几个查询列的多列索引
当对多个索引做联合操作时（通常有多个OR条件），当其中有些索引选择性不高的时候，需要耗费大量CPU和内存资源在算法的缓存、排序和合并上。
优化器不会把这些计算到查询成本中，它只关系随机页面读取。这会导致成本被低估。
用OR的时候，可能根本不需要使用索引，就算索引了有些数据还是会丢掉，那么该执行计划还不如直接走全表扫描。

选择合适的索引列顺序（只适用于B-tree索引）
经验法则：将选择性最高的列放到索引的最前列（效果分场景而异。如果不考虑排序和分组的时候，只进行查询，通常是很好的。但是当左边的索引值基数比较大时，就会造成虽然使用了索引但还是很慢的现象）
聚簇索引
当表有聚簇索引时，它的数据行实际存放在索引的叶子页中。叶子页包含了行的全部数据，但是节点页只包含了索引列。

文章图片

优点：

可以把相关数据保存在一起。如实现电子邮箱时可以根据用户id聚合数据，只需要从磁盘读取少量的数据页就能获得数据。否则每次都需要一次磁盘I/O
数据访问更快。因为保存在一个B-Tree中，因此聚簇索引获取数据比非聚簇索引快一些。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

聚簇数据最大限度提高了I/O密集型应用的性能，但如果数据都放在内存中，则访问的顺序页也没那么重要，也没什么优势了。
插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB最快的方式。如果不是按照主键顺序加载数据，那么结束以后最好使用OPTIMIZE TABLE命令重新组织表。
更新聚簇索引列的代价很高，会强制将每个被更新的行移动到新的位置。
插入新行，或者主键更新是需要移动行的时候，可能面临**“页分裂”（当行的主键必须插入到某个已满页面时，存储引擎会将该页分裂成两个来容纳该行**）页分裂会导致表占用更多的磁盘空间。
可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
二级索引（非聚簇索引）可能比想象更大，因为在二级索引的叶子节点包含了引用行的主键列
二级索引访问需要两次索引查找。

InnoDB和MyISAM的区别
MyISAM：按照数据插入顺序存储在磁盘上（如果定长可以直接通过跳过所需字节数找到需要的行）

文章图片

InnoDB：聚簇索引就是“表”，因为存储了所有数据

文章图片

每个叶子节点都包含主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。
如果主键是一个列的前缀索引，InnoDB也会包含完整的主键列和其他剩下的其他列。而二级索引是索引到主键列，而不是指向行的指针。

在InnoDB中应该按主键顺序插入行
如果没什么数据需要聚集，可以定义一个代理键作为主键（可使用AUTO_INCREMENT自增列，保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好）尽量避免随机聚簇索引。
【MySQL|【MySQL】创建高性能的索引】

文章图片

因为主键值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改)，下一条记录就会写入到新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满。
当使用UUID作为主键插入时，随机字符串可能造成很大的问题。

文章图片

无法确定排列顺序，故不能直接插入到最后，而是要寻找合适的位置。万一该页已满，还要分裂页。
缺点：

写入的目标页可能已经插入到磁盘上了，并已经从缓存中移除了，这就导致存储引擎必须要先进行查找磁盘上的内容。这将导致大量的随机磁盘I/O
因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
由于频繁的页分裂，页会变得稀疏并被不规则地填充，会有很多碎片，影响查询性能。

所以把这些随机值填充以后，最好做一次OPTIMIZE TABLE来重建表并优化页的填充。

自增的主键也会有一些问题：

导入旧数据时，可能会ID重复或ID变化，导致导入失败。
分布式架构，分库分表，无法实现全局ID唯一。
增长从1开始，无法统一为统一的格式。
超高并发工作负载下，按顺序插入可能会造成明显的争用。主键的上一条会成为热点。
AUTO_INCREMENT锁机制会变成热点

覆盖索引
如果一个索引包含所有需要查询的字段的值，就称为”覆盖索引”，简单来说，就是要查询的字段刚好就是你的索引。
好处：

索引条目通常远小于数据行大小，只需要读取索引就会极大减少数据访问量。
索引是按照列值顺序储存的，所以随机I/O会少一些，都是顺序读取。
一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存。
InnoDB的聚簇索引很有用。如果二级索引主键能够直接覆盖查询，则可避免对主键索引的二次查询

当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到“Using index”的信息。假设索引覆盖了where条件中的字段，但不是整个差个查询涉及的字段，则依然要回表查询，即使并不需要多余信息。
SELECT * FROM products WHERE actor = 'SEAN CARREY' AND title LIKE '%APO%';
上面的查询无法覆盖，一方面是因为SELECT * 选择了全部内容，不可能被索引全部覆盖，另一个原因是MySQL不能在索引中执行LIKE操作，只能做最左前缀的LIKE比较。重写：

文章图片

我们把这种方式叫做延迟关联，延迟了对列的访问。在查询的第一阶段可以使用覆盖索引，然后根据这个id去匹配外面的值，虽然无法使用索引覆盖整个查询，但总比什么都不用来的好。不过这样的优化仍然来自于WHERE匹配的行数。假如匹配actor = 'SEAN CARREY'的数据已经很少的时候，子查询带来的成本反而比从表中直接提取完整行更高。
使用索引扫描来排序

如果索引不能覆盖需要查询的所有列，那就不得不每扫描一条索引记录就回表查询一次（随机I/O）

只有当索引的列顺序和ORDER BY字句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，才能使用索引来对结果做排序。如果查询需要关联多个表，则只有order by子句引用的字段全部为第一个表时才能使用。
有一种情况下ORDERY BY子句可以不满足这个要求，就是当前面的索引列指定为常量的时候，就是WHERE 索引列1 = 固定值 ORDER BY 索引列2,索引列3.这样的情况下就可以。

压缩（前缀压缩）索引
MyISAM使用前缀压缩（减小索引长度）默认只压缩字符串，但通过参数设置也可以对整数做压缩。
先完全保存索引块的第一个值，然后将其他值和第一个值（不是前一个）比较后得到相同前缀字节数和剩余不同后缀部分，把这部分存起来。例第一个perform，第二个performance，则压缩为7,ance
代价是某些操作可能很慢，无法在索引块中使用二分查找而只能从头开始扫描。正序速度还好，但是倒序速度就会很慢。可以在 CREATE TABLE 语句中指定 PACK_ KEYS 参数来控制索引压缩的方式。
冗余和重复索引
重复索引

文章图片

MySQL的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建3个重复索引。
冗余索引
如果创建了索引(A,B),再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。但如果再创建索引(B,A),则不是冗余索引，索引(B)也不是，因为B不是索引(A,B)的最左前缀列。
不过如果扩展已有的索引会导致其变得太大，从而影响其他使用该索引得查询的性能，那么就应该冗余。比如整数列上的索引要加一个很长VARCHAR列的索引，那性能可能会急剧下降。
还有一种情况，当创建了索引（A）的时候，如果像WHERE A=5 ORDER BY ID 这样的查询就很有用，但是假如改成索引(A,B)以后那么就会变成（A，B，ID），上面的查询就无法使用索引做排序了。
未使用的索引
可以使用Percona或者MariaDB帮助定位未使用的索引
索引和锁
索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。

文章图片

这个查询会返回2-4行之间的数据，实际上获得了1-4行的排他锁。但假如使用索引不访问第一行，那么第1行就不会被加锁。换句话说，底层存储引擎的操作是”从索引的开头开始获取满足条件actor_id<5的记录”，服务器并没有告诉InnoDB可以过率第1行的where条件。注意到EXPLAIN的Extra列出现了”Using where”，这表示MySQL服务器将存储引擎返回以后再应用WHERE。
InnoDB在二级索引上使用共享(读)锁，但访问主键索引需要排他(写)锁。
维护索引和表
找到并修复损坏的表
对于MyISAM存储引擎,表损坏通常是系统崩溃导致的。可以尝试运行CHECK TABLE来检查是否发生了表损坏，可以使用REPAIR TABLE命令来修复损坏的表。
如果InnoDB引擎的表出现了损坏，那一定时发生严重的错误。常见的类似错误通常是由于尝试使用rsync备份InnoDB导致的，不存在查询能够让InnoDB表损坏。
更新索引统计信息
MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息
records_in_range()：通过向存储引擎传入两个边界值获取这个范围大概有多少条
info()：该接口返回各种类型的数据，包括索引的基数（每个键值有多少记录）
如果返回的信息是不准确的数据，或者执行计划太复杂无法准确地获取各个阶段匹配的行数，优化器就会使用索引统计信息来估算扫码行数。如果表没有统计信息，或者统计信息不准确，优化器可能做出错误的决定。可以通过ANALYZE TABLE来重新生成统计信息解决这个问题。

ANALYZE TABLE的运行成本：

Memory引擎根本不存储索引统计信息
MyISAM将索引统计信息存储在磁盘，需要进行一次全索引扫描来计算索引基数。整个过程需要锁表
直到MySQL5.5，InnoDB也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中。
可以使用SHOW INDEX FROM 表来查看索引的基数

减少索引和数据的碎片
B-Tree索引可能会碎片化，降低查询效率。表的数据存储也可能碎片化。

行碎片：数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
行间碎片：逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作本来是顺序读取的
剩余空间碎片：数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，造成浪费。

对于MyISAM表，这三类都有可能发生。但InnoDB不会出现短小的行碎片；InnoDB会移动过短小的行并重写到一个片段中。

可以通过执行OPTIMIZE TABLE 或导出再导入的方式来重新整理数据。对于那些不支持的，可以通过一个不做任何操作的ALTER TABLE来重建表。
总结

单行访问是很慢的。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引位置引用以提升效率
按顺序访问范围数据是很快的。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且FROUP BY查询也无需再做排序和将行按组进行聚合计算。
索引覆盖查询很快。如果一个索引包含了查询所需要的所有列，那么存储引擎就不要再进行回表查找行。

总的来说，编写查询语句时应使用合适的索引以避免单行查找。