Mysql索引理解mysql索引本文为《高性能mysql》索引部分的阅读笔记。索引是存储引擎层的东西，所以不同的存储引

概述

本文为《高性能mysql》索引部分的阅读笔记。索引是存储引擎层的东西，所以不同的存储引擎对于索引的实现都不太相同。

innodb会监控被频繁访问的二级索引，如果二级索引被频繁访问，那么innodb会为二级索引建立hash索引（在内存中），这样会提高二级索引的访问性能。

但是hash索引只能用于等值查询，并且会消耗一定的buffer pool。

对于长数据的搜索，可以加一列，对长数据进行hash搜索，会提高性能。

SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com”) AND url="http://www.mysql.com

对于比较长的数据，可以采用前缀索引，但是前缀的特异性要强，这样才能充分发挥索引的性能。使用前缀所以就无法使用group by，以及order by操作。

对于多列的单独索引，做where查询的时候，其实性能往往不是很好，对于此类查询，mysql会进行索引合并，合并操作会消耗对应的cpu资源。但优化器并不会将这部分时间和资源计算在查询成本内，甚至有时候不如进行全表查询。

如果在explain 语句的时候发现index_merge，需要去分析是否需要优化。

多列索引的顺序也比较重要，也就是选择哪一列作为索引前缀。

单个表进行多索引查询的时候，mysql会进行索引合并的优化，对多个索引分别进行条件扫描，然后对扫描结果进行合并。

数据和索引聚簇在一起。

优缺点：

1.适用于io密集型应用，如果数据全部存储在内存，聚簇索引毫无用处。

2.插入速度依赖插入顺序，最好按照主键插入数据。

3.更新聚簇索引列（主键）的代价比较高，因为需要移动整个数据到新的位置，甚至出现页分裂。

4.可能会导致全表扫描速度变慢。

5.二级索引叶需要存储主键列，并且二级索引需要两次查找

如果一个索引包含查询的所有字段（查询字段以及条件字段），称为覆盖索引。

好处：不需要回表查询，速度快。因为索引比较小，内存缓存后性能会更好。

覆盖索引一般采用B-tree实现，因为需要存储索引列的值。

覆盖索引理论上讲是可以不回表查询对应字段，但有些场景我们可以结合覆盖索引来减少数据行的访问，也就是延迟关联。（高版本的mysql可能不需要这个复杂的技巧操作了）

举例：

EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY’ AND title like '%APOLLO%’

这行数据不能使用覆盖索引，因为查询了所有字段。就算我们建立一个(actor,title)的多列索引，其查询逻辑仍然是先根据索引查actor='SEAN CARREY’，然后回表再做like操作。

理论上可以查出索引数据，然后在索引过滤like操作再回表查询数据返回。但需要注意的是MySQL不能在索引中执行LIKE操作。

优化办法：扩展索引(actor，title，prod_id）

EXPLAIN SELECT * FROM products JOIN (SELECT prod_id FROM products WHERE actor='SEAN CARREY' AND title LIKE ) AS t1 ON (t1.prod_id=products.prod_id)

该操作就是延迟关联。可以做到过滤完之后再回表查询。

出现这些问题主要是低版本的mysql的api不支持将查询条件传到存储引擎，都是从存储引擎拉数据然后在mysql进行数据过滤。5.6支持了索引条件推送（ICP）将优化这个方面。

如果where条件的一部分能够通过使用索引中的字段进行评估，那么mysql server把这部分where条件下推到storage engine（存储引擎层）。存储引擎通过索引元组的索引列数据过滤不满足下推索引条件的数据行。只能用于二级索引。

SELECT * FROM products WHERE actor='SEAN CARREY’ AND title like '%APOLLO%’  index(actor,title)

对于这行数据，存储引擎会查询满足actor的索引条目，然后根据查询到的索引条目在对like进行过滤。最后将符合条件的数据从base table查询并返回给mysql server层。

条件：

索引可以让查询锁定更少的行。减少行锁的数量，增加并发性。避免碎片化可以加快查询。