《高性能MySQL》第五章创建高性能索引

索引的优点

除了快速查找，索引还有一些其他附带功能。
比如BTree索引，由于它具有顺序性，所以可以使用ORDER BY进行排序和GROUP BY进行分组。

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

对于中大型的表索引都是比较好用的，但是对于小型表或者特大型表就不尽然，小型表可能全表扫描的整体效率会高于使用索引，因为维护索引本身也是需要成本的。特大型的表，维护成本也必然很大，这种可采用分区的技术。

高性能的索引策略

有一个索引建立的评判标准，被称为三星系统：

• 索引将相关的记录放到一起（⭐）
• 索引中的数据顺序和查找中的排列顺序一致（⭐⭐）
• 如果索引中的列包含了查询需要的全部（⭐⭐⭐）

独立的列

索引不能是表达式的一部分。因为MySQL并不能直接解析这些表达式。
如下所示：

所以为了索引能成功用上，只将索引单独置于表达式的左侧

前缀索引和索引选择性

如果索引字段较长那么可以采用之前提过的模拟哈希索引的方式，这种就可以选择索引字段的前缀部分作为索引，不过这样可能会降低索引的选择性(不重复的索引值(基数)/记录总数，在1/T~1之间)。字段选取的越长，选择性一般越好，不过则就会相对比较耗费空间。一般来说基数得接近完整列的基数。
实验例子：
我瞎找了些歪果仁的名字

首先全表统计（如果数据量比较庞大可抽取部分数据），基数如下：




如上所示所以可将前缀为3置为索引。
还有一种方法可计算完整列的选择性。不过这种方式反应的是平均选择性，如果数据分布均匀时，可能结果不太精确。

然后使用ALTER TABLE testpre ADD KEY(name(3)); 对实验内容创建索引。
缺陷是无法基于MySQL做ORDER BY和GROUP BY，也无法做覆盖扫描。
也可使用后缀索引，比如某个域名的所有电子邮件地址等，将字符串反转然后检索前x个

多列索引

在5.0之后版本中对于多个独立的索引使用OR,AND,组合前两种情况的联合及相交都是使用了两个索引扫描的联合。

而在之前版本中会使用全表扫描。
不过这种情况的出现还是说明表的索引建得比较糟糕

• 相交操作时，意味着应该使用一个包含相关列的多列索引而不是多个单独的
• 联合操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据时
• 优化器不会吧这些计算到查询成本中，这倒是甚至不如全表扫描

IGNOR INDEX(key) 可用于强制不使用哪个索引。
FORCE INDEX(key) 可用于强制使用哪个索引。

选择合适的索引列顺序

仅在使用BTree索引时有用，因为只有它存在排序等操作。
应该正确依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组。
一个多列索引，顺序是按照从左到右的索引进行排序的。
当不要考虑排序与分组时，可将选择性最高的那一列作为最左列，然后依次排下来。
根据选择性的高低，应该选取customer_id作为最左列，staff_id作为次左列。

但是有时候需要根据值的分布决定，就是哪个值的使用频率更高。还有看哪些值与排序有关。

聚簇索引

这是一种索引具体的实现方式，InnoDB中
就是采用的这种方式实现，它保存了BTree索引和数据行。一个表只能有一个聚簇索引。（覆盖索引可以模拟多个聚簇索引）
在InnoDB中是选择主键聚集数据，如果没有主键则选取一个唯一的非空索引，如果还不存在，则隐式的创建一个主键。
好处

• 减少磁盘IO的占用
• 数据访问更快。
• 使用聚簇索引扫描的查询可以直接使用页节点中的主键值。

缺点

• 聚簇数据最大限度地提高了I/O密集型应用的性能，如果整表都在内存中，则效果就不大
• 插入速度严重依赖于插入的顺序，如果不是使用主键顺序加载数据最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引的代价较高
• 当又新行插入或主键被更改时，可能出现页分裂的情况，一页装不下了，将一页分裂成两个页面来容纳改行。
• 聚簇索引可能导致全表扫描变慢，尤其是行较稀疏或者页分裂导致数据不连续的时候。
• 二级索引（非聚簇索引）可能比想象中大，二级索引包含了引用行的主键列。而且使用二级索引会带来二次查询。

MyISAM 和InnoDB的数据存储对比

图来自原书：

InnoDB主键最好顺序插入（自增就是个不错的选择）
不过如果在高并发的场景下，主键顺序插入必然会引发竞争，还有就是AUTO_INCREMENT锁机制，可尝试修改innodb_autoinc_lock_mode(0:任何insert语句都会得到一个auto_inc锁，1：默认模式，一次生成几个连续的值用于insert语句，无需语句执行结束，只要得到对应的值就提前释放锁，2：无锁，不过可能出现anto_increment值不连续)

覆盖索引

通常根据WHERE条件来创建合适的索引。
如果索引包含所需查找的字段就叫做覆盖索引（聚簇索引可以说是全列的覆盖索引），这种情况下就不用回表再查一次，能够节省时间。
只有BTree索引支持覆盖索引。因为只有它保存了索引了原字段。
好处

• 索引包含的列数一般小于总列数，而且不用回表时间空间双重节省，索引较小，更容易放入内存，进而节省IO。（MyIASM更是如此，它支持压缩索引）
• 索引按照顺序存储的，IO密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。甚至可以通过OPTIMIZE命令使得索引完全顺序排列。
• 像MyIASM调用数据会依赖系统调用，如果有覆盖索引就可以避免这种情况
• InnoDB可避免二次查询。

可通过查看Explan 命令查看Type中如果是Using Index则是使用的覆盖索引。
如果索引无法完全覆盖查询的数据，那么就不会完全使用覆盖索引，当然能够用覆盖索引肯定要能够用索引，比如索引应该位于表达式的左端，且只有索引，而且LIKE操作仅支持前缀匹配，所以通配符尽量不要放到前面。
如果实在没办法，只能将通配符放前面可以采用延迟关联，它延迟了对列的访问。传出外层查询需要的值，然后使用索引，这无法使用索引覆盖整个查询，不过总比没有的好。
比如SELECT * FROM product WHERE actor =‘SEAN CRREY’ AND title LIKE ‘%APOLLO%’
可以优化为SELECT * FROM products JOIN(SELECT prod_id FROM products WHERE actor ='SEAN CARREY' AND title LIKE '%APOLLO%') AS t1 ON (t1.prod_id = products.prod_id)

如果内层查询的数据远小于外层数据，这时优化效果就会比较明显。
InnoDB由于索引包含主键，所以相当于可以覆盖索引和主键。

如果有索引下推（ICP，5.6之后）的话，就可以将判断提前，比如从一个索引检索出一些复合条件的数据，如果一个一个回表再将不符合另外一个条件的过滤的话，就会比检索出来后先过滤再回表，来得耗时，因为回表次数多了，有了这个操作，刚才延迟关联就可以不用了。

使用索引扫描来排序

由于如果只基于索引排序，或者通过排序操作，自然第一种会快得多，不过第一种如果索引不能覆盖查询的所有列的话，就会一条一条的回表查询。这可能导致按索引的速度比全表查询还慢，尤其是 I/O密集型任务。
所以尽可能设计索引既满足排序又可用于查找行。
只有当索引与Order By子句顺序是一样的时才能MySQL才能够使用索引来对结果做排序。还是哪个说法使用索引排序首先得成功用上索引，最左原则，不过如果左边的索引固定为常数了，也是可以走索引的。
如果需要关联多张表时，则只有当ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。

压缩(前缀压缩)索引

采用最大公共前缀，然后将这部分压缩，只记录后面不同的部分。不过压缩了空间的代价就是一些操作会变慢。

冗余和重复索引

不同类型的索引不算冗余。
不符合最左前缀原则的不算冗余。
不过有时空间允许的条件下，可插入数据不多的情况下，创建冗余索引以提升某些操作的速度也是允许的

未使用的索引

理应删除，不过一些索引是为了标识数据的唯一性

索引和锁

索引可以锁定更少的行。被检索时被扫到的行都会被上锁，被过滤掉的行的锁会被提前释放，而索引下推能够在检索后得到的数据过滤一次，只在必要的数据上加锁。在完成对应是检索后就直接释放而不用等事务结束（早期是一直要等待事务结束）

InnoDB在二级索引上使用共享锁，而访问主键时使用排他锁，这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE 比LOCK INSHARE MODE或非锁定查询要慢很多。