⼏种常⽤索引以及使⽤策略
（本篇内容均出⾃《⾼性能MySQL》⼀书，是⼀篇粗略的笔记，有幸遇见建议直接观看书中第五章)
⼀、概述
索引是存储引擎⽤于快速查记录的⼀种数据结构，它对性能的影响⾮常关键，尤其是当表中数据量越来越⼤的时候。因此索引优化是提⾼查询性能的有效⼿段，查询和索引也需要配合使⽤。本⽂将介绍索引的种类，索引策略，以及⼀些注意事项。
⼆、索引类型
索引类型很多，在MYSQL中，索引是在存储引擎层⽽不是服务层实现，所以并没有统⼀的索引标准：不同存储引擎的索引的⼯作⽅式并不⼀样；不是所有的存储引擎都⽀持所有类型的索引；即使多个存储引擎⽀持同⼀种类型到的索引，底层实现也可能不同。
2.1 B+Tree索引
多数存储引擎都使⽤B+Tree(Balance Tree)索引，B+Tre结构e是B-Tree的变种，因此有必要先介绍两种数据结构。
2.1.1 选⽤B-Tree的⽬的
树结构很多，如⼆叉树、平衡⼆叉树、红⿊树，为什么要选⽤B-Tree呢？
内存和硬盘的速度相差太多了，并且硬盘是将所有信息分割成相等⼤⼩的页⾯，每次硬盘读写都是⼀个或多个完整的页⾯。因此为提⾼时间效率，应尽量减少I/O次数，并且每次从硬盘中读取到更多且合适点的数据量。
像⼆叉树类的结构，每个结点只存储了⼀个元素，如果要存储⼤量数据，就会形成⼀棵很“⾼”的树，每次查就会进⾏⼤量I/O，效率极低。⽽B-Tree对此进⾏了调整，使得每个结点可以存储多个元素，与硬盘存储的页⾯⼤⼩相匹配。
通过上图(图来源于⽹络)这种⽅式，每次I/O都能获得最⼤数量的数据，减少了必须访问结点和数据块的数量，可以说B-Tree是专门为内外存的数据交互准备的。
2.1.2 B-Tree与B+Tree的差异
对于树结构来说，都可以通过中序遍历来顺序查树中的元素，但这种遍历对于B-Tree意味着要在硬盘的不同页⾯之间进⾏多次访问，并且在分⽀结点上同时存储键值和数据，就减少了⼀个页⾯内存放的键值的数量。在这些问题基础上就产⽣了B+Tree(图来源于⽹络)。
在B-Tree中，每个元素在树中只出现了⼀次，有可能在叶⼦结点，也可能在分⽀结点上。但B+Tree中，出现在分⽀结点上的元素会在叶⼦结点中再次出现，叶⼦结点本⾝依据键值⼤⼩顺序链接。并且分⽀结点上的元素仅含键值，要对实际数据进⾏访问需要到达该键值的终端结点。
2.1.3 可⽤B+Tree索引的查询类型
B+Tree索引对全键值、键值范围和建前缀查有效：
全值匹配：和索引中所有列进⾏匹配
匹配最左前缀：只使⽤索引的第⼀列
匹配列前缀：使⽤索引到的第⼀列，匹配这⼀列值的开头部分
匹配范围值：使⽤索引到的第⼀列进⾏范围匹配
精确匹配某⼀列并范围匹配另外⼀列：对第⼀列使⽤全匹配，并对第⼆列进⾏范围匹配
只访问索引的查询：查询时只访问索引内容，不需要访问数据⾏
同时由于B+Tree是顺序组织存储的，该索引还适⽤于查询中的排序操作，⼀般order by⼦句满⾜上⾯
列出的这⼏种查询类型，则这个索引也可以满⾜对应的排序需求。
2.1.4 B+Tree索引的限制
假设表中存在索引key(name,age,gender)：
若不是按照索引的最左列开始查，则⽆法使⽤索引。如key索引⽆法⽤于查某个年龄或某个性别的⼈，同时也⽆法查name以某个字结尾的⼈
不能跳过索引中的列。如查询⽤到name，若不使⽤age，则索引不能继续⽤gender进⾏匹配，就只能使⽤name进⾏索引
如果查询中有某个列的范围查询，则其右边所有列都⽆法使⽤索引优化查。如查询语句name='Aidan' and age>10 and gender = 'm'，由于age是⼀个范围查询，则这个查询只能⽤到key索引的前⾯两列。
2.2 哈希索引
MySQL中只有Memory引擎⽀持哈希索引(也⽀持B+Tree索引)，是其默认的索引类型。Memory引擎⽀持⾮唯⼀哈希索引，采⽤链接法消除哈希冲突。
InnoDB引擎中有⼀个功能叫“⾃适应哈希索引”，当InnoDB注意到某些索引值被使⽤得很频繁时，它会在内存中基于B+Tree索引之上再创建⼀个哈希索引，让B+Tree也具有⼀些哈希索引的优点。但这是⼀个完全⾃动的⾏为，不受⽤户控制或配置，不过可以关闭该功能。
哈希索引基于哈希表实现，哈希表中保存着指向每个数据⾏的指针。对于每⼀⾏数据，存储引擎会对所有索引的列计算⼀个哈希码。
2.2.1 哈希索引的限制
哈希索引的检索效率⾮常⾼，不需要想B+Tree索引需要从根结点到⽀结点，但哈希索引的特殊性也带来了很多限制：
哈希索引是使⽤索引列的全部内容来计算哈希值的，所以哈希索引不⽀持部分索引列匹配查
哈希索引数据并不是按照索引值顺序存储的，所以⽆法⽤于排序
哈希索引只⽀持等值⽐较查询，包括=，IN( )，<=>。也不⽀持任何范围查询
哈希索引只包括哈希值和⾏指针，⽽不存储字段值，所以不能使⽤索引中的值来避免读取⾏
如果哈希冲突很多，则维护索引的代价也会很⾼
2.3 全⽂索引
全⽂索引作⽤是查⽂本中的关键词，⽽不是直接⽐较索引中的值。全⽂搜索和其他⼏类索引匹配⽅式完全不⼀样，它更类似于搜索引擎做的事，⽽不单单只是进⾏where的条件匹配。
三、索引策略
3.1 独⽴的列
索引列必须是独⽴的，不能是表达式的⼀部分或者是函数的参数，否则MySQL就不会使⽤索引。
select age from person where age+1 = 10;
MySQL不会⾃动解析age+1这个表达式，因此我们使⽤时需要对where进⾏简化
select …… where to_days(current_date) - to_days(date_col) <= 10;
3.2 前缀索引和索引选择性
对BLOB、TEXT或很长的VARCHAR类型进⾏索引，必须使⽤前缀索引，MySQL不允许索引这些列的完整长度。那么现在的关键在于选择多长的前缀。
这⾥需要提到⼀个概念，索引的选择性。它是指不重复的索引值和数据表的记录总数(#T)的⽐值，范围在1/#T到1之间。根据公式，选择性越⾼则查询效率越⾼，因此要保证前缀索引的选择性接近索引的整个列，但长度⼜不能太长。但在具体表中，不能仅看平均选择性，还要注意数据分布的平均性。
3.3 多列索引
多列索引不是为每个列创建独⽴的索引，事实上在多个列上创建单列索引在⼤多数情况下并不能提⾼查询性能。在早期版本，这种情况只会使⽤其中某⼀个单列索引或者根本不会使⽤索引。在MySQL5.0以及之后的版本，产⽣了索引合并策略，即可能会使⽤这些单列索引进⾏扫描，并对结果进⾏合并，但这并不能称之为⾏之有效。若在explain中看到索引合并，应检查表结构。也可通过optimizer_switch参数关闭索引合并功能，也可使⽤IGNORE INDEX提⽰让优化器忽略掉某些索引。
除了以上错误理解多列索引，还要注意索引列顺序问题。这⾥是指B+Tree索引，因为B+Tree索引是顺序组织存储的，适合排序，所以需要考虑如何更好地满⾜排序和分组的需要。
3.4 聚簇索引
聚簇索引并不是⼀种单独的索引类型，⽽是⼀种数据存储⽅式。在InnoDB中，表数据⽂件本⾝就是按B+Tree组织的⼀个索引结构。InnoDB 的聚簇索引实际是按照每张表的主键构造⼀棵B+树，同时在叶⼦节点中存放整张表到的⾏记录数据。
这个特性使得索引组织表中的数据也是索引的⼀部分，每张表只能拥有⼀个聚簇索引。InnoDB通过主键聚集数据，如果没有定义主
键，InnoDB会选择⾮空唯⼀索引替代，如果没有这样的索引，InnoDB会隐式定义⼀个主键来作为聚簇索引。InnoDB只聚集在同⼀个页⾯中的记录，包含相邻键值的页⾯可能会相距很远。
聚簇索引的优点：
数据访问更快。因为索引和数据保存在同⼀棵B+树，所以获取数据时，⽐⾮聚簇索引更快
使⽤覆盖索引扫描的查询可以直接使⽤页节点中的主键值，即对主键的排序查和范围查速度⾮常快
聚簇索引的缺点：
插⼊速度严重依赖插⼊顺序。按照主键的顺序插⼊是加在数据到InnoDB表中速度最快的⽅式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使⽤OPTIMIZE TABLE命令重新组织⼀下表
更新聚簇索引列代价很⾼，因为会强制InnoDB将每个被更新的⾏移动到新的位置
基于聚簇索引的表在插⼊新⾏，或者主键被更新导致需要移动⾏的时候，可能⾯临“页分裂”的问题。当主键值要求必须将这⼀⾏插⼊到
某个已满的页中时，存储引擎会将该页分裂成两个页⾯来容纳该⾏，这就是⼀次页分裂操作。页分裂会导致表占⽤更多的磁盘空间(与B+Tree结构的插⼊删除更新有关)
聚簇索引可能导致全表扫描变慢，尤其是⾏⽐较稀疏，或者页分裂导致数据存储不连续的时候
⼆级索引(辅助索引)可能⽐想象的要更⼤，因为⼆级索引的叶⼦节点包含了引⽤⾏的主键列
⼆级索引访问需要两次索引查，第⼀次记现在辅助索引的B+Tree中检索，到其叶⼦节点获取对应到的主键；第⼆次使⽤主键在主索引B+Tree中再执⾏⼀次检索，最终到叶⼦节点获取到整⾏数据
辅助索引：
上⾯提到了辅助索引，也叫⾮聚簇索引，是在聚簇索引纸上创建的索引。辅助索引存储的叶⼦节点不再是⾏的物理位置，⽽是⾏的主键值，因此利⽤辅助索引访问数据总是需要⼆次查。
3.4.1 InnoDB和MyISAM数据分布对⽐
这⾥我们实际探究聚簇索引和⾮聚簇索引的数据分布，以及主键索引和⼆级索引的数据分布的区别。通过这样⼀张表进⾏对⽐：
create table test(
col1 int not null,
col2 int not null,
primary key(col1),
key(col2)
);
MyISAM数据分布：MyISAM是按照是数据插⼊的顺序存储在磁盘上。⾏的旁边显⽰了⾏号，从0递增。因为⾏是定长的，所以MyISAM可以从表的开头跳过所需的字节到需要的⾏。
MyISAM⽀持B+Tree索引，但并不是聚簇，它的数据存储和主键索引是分开的。它的主键索引中，叶⼦结点仅存储着键值和⾏号，按索引列排序。
对于col2，⾮主键的索引，和主键索引⼀样
InnoDB的数据分布：
InnoDB⽀持聚簇索引，整个索引实际包含了整个表，所以不需要像MyISAM那样独⽴的⾏存储。
(如果主键是⼀个前缀索引，InnoDB也会包含完整的主键列和剩下的其他列)
与MyISAM的⼆级索引不同，InnoDB⼆级索引的叶⼦结点存储的不是“⾏指针”，⽽是主键值。这样做减少了当出现⾏移动或者数据页分裂时⼆级索引的维护⼯作，不过使⽤主键值会让⼆级索引占⽤更多空间。
整体对⽐：
3.4.2 在InnoDB表中按主键顺序插⼊
这⾥主要解释为什么尽量按顺序写⼊数据⾏。主键值是顺序的，则InnoDB把每⼀条记录都存储在上⼀条记录的后⾯，当达到页的最⼤填充因⼦时，下⼀条记录就会写⼊新页。但是对于⾮顺序插⼊，插⼊就会变得很随机，每次插⼊都需要为新的值合适的位置，因此需要把原来的值重新更改位置，分配空间。这样做就会有：
写⼊的⽬标也可能已经刷到磁盘上并从缓存中移除，或者还没有被加载到缓存中，InnoDB在插⼊之前不得不先到并从磁盘读取⽬标页到内存中，这就会产⽣⼤量随机I/O
写⼊的乱序使得InnoDB不得不频繁进⾏页分裂操作，以分配新的⾏空间。页分裂会移动⼤量数据，影响效率，并且页会变得稀疏，造成空间上的浪费
但对于⾼并发⼯作负载，InnoDB中按主键顺序插⼊可能会造成明显的争⽤。
3.5 覆盖索引
MySQL可以使⽤索引来直接获取列的数据，这样就避免了再去读取数据⾏。如果⼀个索引包含所有需要查询的字段的值，那么就称之为“覆盖索引”。
这样做的优点是：
索引条⽬通常远⼩于数据⾏的⼤⼩，只读取索引，就⼤⼤减少了数据访问量。⽽且索引⽐数据更⼩，对于I/O密集型应⽤，每次就能放更多到内存(MyISAM能压缩索引，这点对其很有帮助)
因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查会⽐随机读取每⼀⾏数据的I/O要少的多
对MyISAM来说，内存中只缓存索引，数据则依赖操作系统缓存，因此每⼀次访问数据就需要⼀次系统调⽤；对InnoDB来说，如果能够在⼆级索引上覆盖查询，则可以避免对聚簇索引的⼆次查询
覆盖索引的条件是必须要存储索引列的值，因此哈希索引、全⽂索引和空间索引都不能成为覆盖索引。同时也不是所有引擎都⽀持覆盖索引。
当发起⼀个被索引覆盖的查询时，在explain的extra列中可以看到“Using index”的信息，使⽤索引覆盖查询必须要注意：
索引⼀定是覆盖了查询的所有列
存储引擎在索引中执⾏的操作有限制。在MySQL5.5以及之前版本中，只允许在索引中做等于、不等于、⼤于等简单操作。MySQL能在索引中做最左前缀匹配的like匹配，但以通配符开头的like查询，就⽆法在索引中匹配，只能取数据⾏进⾏⽐较
如果查询中有部分可以被索引覆盖，则可以尝试改变sql语句，先进⾏在索引中查询，缩⼩范围，再提取数据⾏。
3.6 使⽤索引扫描来排序
MySQL有两种⽅式可以⽣成有序的结果：排序操作和按索引顺序扫描。如果explain出来的type列为“index”，则说明MySQL使⽤了索引扫描来排序。利⽤索引进⾏排序需要符合：
只有当索引的列顺序和order by⼦句的顺序完全⼀致，并且所有列的排序⽅向都⼀样时，MySQL才能使⽤索引来对结果做排序
如果查询需要关联多张表，则只有当order by ⼦句引⽤的字段全部为第⼀个表时，才能使⽤索引做排序
order by⼦句和查型查询的限制是⼀样的：需要满⾜索引的最左前缀的要求；否则MySQL都需要执⾏排序操作，⽽⽆法利⽤排序操作
以上最后⼀点，当前导列为常量的时候，order by⼦句可以不满⾜索引的最左前缀的要求。
例如有索引index(name,age)，若使⽤select name,age from person order by age，则不能⽤索引进⾏排序，但如果select name,age from person where name='Aidan' order by age，通过where条件指定了索引前⾯的列，形成了最左前缀条件，就能够使⽤。
⼀些不能使⽤索引做排序的查询(假设有index(rental_date,inventory_id,customer_id)索引)：
1、查询使⽤了两种不同的排序⽅向，但是索引列都是正序排序
……where rental_date='2020-09-01' order by inventory_id DESC, customer_id ASC;
2、查询的order by⼦句中引⽤了⼀个不在索引中的列
sql优化的几种方式
……where rental_date='2020-09-01' order by inventory_id, staff_id;
3、查询的where和order by中的列⽆法组合成索引的最左前缀
……where rental_date='2020-09-01' order by customer_id;
4、查询在索引列的第⼀列上是范围条件，所以MySQL⽆法使⽤索引的其余列
……where rental_date>'2020-09-01' order by inventory_id, customer_id;
5、查询在某⼀列⾏有多个等于条件
……where rental_date='2020-09-01' and inventory_id in(1,2) order by customer_id;
3.7 前缀压缩索引
MyISAM使⽤前缀压缩来减少索引的⼤⼩，从⽽让更多的索引可以放⼊内存中。默认只压缩字符串，
但通过参数设置也可以对整数做压缩。MyISAM压缩每个所以块的⽅法是，先完全保存索引块中的第⼀个值，然后将其他值和第⼀个值进⾏⽐较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来。例如，第⼀个值是perform，第⼆个值是performance，则第⼆个值通过前缀压缩后就是类似
于“7，ance”的形式。MyISAM对⾏指针也采⽤类似的前缀压缩⽅式。
压缩在某些情况下会提⾼性能，但某些操作可能更慢。因为每个值的压缩前缀都依赖前⾯的值，所以MyISAM查是⽆法在索引块使⽤⼆分查⽽只能从头开始扫描。如果倒序扫描就更糟糕了。因此对I/O密集型应⽤⽐较适⽤，⽽CPU密集型就⽐较慢了。可以在create table语句中指定PACK_KEYS参数控制索引压缩⽅式。
3.8 索引和锁
索引可以让查询锁定更少的⾏。如果你的查询从不访问那些不需要的⾏，那么就会锁定更少的⾏。虽然InnoDB的⾏锁效率很⾼，内存使⽤也很少，但是锁定⾏的时候仍然会带来额外开销；锁定超过需要的⾏会增加锁争⽤并减少并发性。
InnoDB只有在访问⾏的时候才会对其加锁，⽽索引能够减少InnoDB访问的⾏数，从⽽减少锁的数量。
但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的⾏时才有效。如果索引⽆法过滤掉⽆效的⾏，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应⽤WHERE⼦句。这时已经⽆法避免锁定⾏了：InnoDB已经锁住了这些⾏，到适当的时候才释放。在MySQL 5.1和更新的版本中，InnoDB 可以在服务器端过滤掉⾏后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。
例：有索引index(id)，在执⾏……where id<5 and id<>1时，最后会返回2~4⾏，但实际执⾏时会锁住1~4⾏。因为底层存储引擎的操作是“从索引的开头开始获取满⾜条件actor_id <5的记录”，服务器并没有告诉InnoDB可以过滤第1⾏的WHERE条件。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。