MySQL 索引，聚簇索引、覆盖索引

编写查询语句时尽可能选择合适的索引以避免单行查找，尽可能使用数据原生顺序从而避免额外的排序，并尽可能使用覆盖索引查询。
在新的《高性能 MySQL》中已经彻底去除了对其他引擎的讨论，只专注在了 InnoDB上，所以我们可以只看 InnoDB 的原理和特性。

创建高性能的索引

当表越大，索引对表的影响也越大。索引优化是对查询性能优化最有效的手段，索引可以轻易的将查询性能提高几个数量级。

1.1 索引

索引就和一本书的目录一样，先找索引中的记录，再根据记录查找对应的数据行。

索引有很多种类型，可以为不同的场景提供更好的性能。MySQL 中，索引是在存储引擎层而不是服务器层实现的。所以不同的引擎有不同的索引。MySQL 支持的索引类型：B+ Tree 、哈希索引、全文索引等。

B+Tree 索引：引擎不需要进行全表扫描获取需要的数据，从树的根节点开始搜索，时间复杂度为 O(logN),B+Tree 索引列是顺序存储的，很适合查找范围数据，也因此可以 GROUP BY 和 ORDER BY。索引对多个值进行排序依据是 CREATE TABLE 语句中定义索引时列的顺序。

B+ Tree 限制：①必须从最左列索引开始查找②不能跳过中间的列去查下一列③如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。

哈希索引：只有精确匹配索引所有列的查询才有效。存储引擎会根据索引列计算一个哈希码，对应的哈希表存储数据。M有SQL 中只有 Memory 显式支持哈希索引。如果多个列的哈希值相同，索引会以链表的方式存储多个记录。

InnoDB 引擎有一个特殊的功能叫“自适应哈希索引”。当某些查询频繁时，会创建一个哈希索引。

1.2 索引优点

主要是让服务器快速定位到表的指定位置。

因为索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询。

索引优点：

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

索引对于中到大型的表非常有效，对于小和特大型的表效果就会很差。

1.3 高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。

1. 独立的列：不能是表达式或函数的一部分；

2. 前缀索引和索引选择性：选择性：不重复的索引值和数据表记录总数的比值。比值越高查询效率越高（越高越好）。一般使用前缀索引时用来计算该取多长的长度。如：select count(distinct left(city,3))/count(*) as sel3,count(distinct left(city,4))/count(*) as sel4 from city;和select count(dictict city)/count(*) from citys;选择出最接近的数值。创建的索引就是：ALTER TABLE citys ADD KEY(city(4));前缀索引无法 order by 和 group by；

3. 多列索引：为每个列创建独立索引是很错误的，顺序错误也是不好的。建立多列索引的情况：①当出现服务器对多个索引做相交操作时（通常有多个 AND 操作）就意味着需要一个包含所有相关列的多列索引，而不是多个单列索引；②当服务器对多个索引做联合操作时（通常有多个 OR 操作），通常会耗费大量的 CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据时。更重要的是，优化器不会把这些计算到“查询成本中”，所以当查询慢的时候很难知道原因，所以有时 UNION。也是一种选择（OR 用 UNION ALL 替代）

4. 选择合适的索引列顺序：这是我们经常困惑的地方，什么才是正确的顺序：正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。有一个经验法则：将选择性最高的列放到索引最前列。在不需要考虑排序和分组时，这样会很快。

   SELECT
   	COUNT( DISTINCT id )/ COUNT(*) AS id_selecticity,
   	COUNT( DISTINCT `password` )/ COUNT(*) AS pwd_selectivity,
   	COUNT(*) 
   FROM
   	users
   	
   # 对应的输出为 1.0000	0.3571	14，id选择性更高，索引最左列应为id
   

1.4 聚簇索引

也叫主键索引，并不是一种单独的索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行（B+Tree）。聚簇表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方所以一个表只能有一个聚簇索引。

聚簇索引节点页只包含索引列，叶子页包含了行的全部数据。

聚集数据的优点：

• 可以把相关的数据保存在一起，如获取电子邮件，根据用户 ID 就可以获取到某个用户的全部邮件；
• 数据访问更快；
• 使用覆盖索引扫描的查询可以直接使用节点中的主键值。

缺点：

• 最大限度地提高了IO 密集型应用的性能，如果数据全部放在内存中，访问顺序也就不重要了；
• 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载到 InnoDB 中速度最快的方式；
• 更新聚簇索引的代价很高，因为会强制 InnoDB 将每个呗更新的行移动到新的位置；
• 二级索引可能很大，二级节点包行列应用行的主键列；

二级索引需要两次查找：查找时存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中找到对应的行。对于 InnoDB 自适应哈希索引能够减少这样的重复工作。

1.5 覆盖索引

通常大家都会根据查询的 WHERE 条件来创建合适的索引，不过这只是索引优化的一个方面。优秀的索引应该考虑到整个查询，而不单是 WHERE 条件部分（应该是将过滤条件和 select 部分的字段都放入索引）。索引是一种查找数据的高效方式，但 MySQL 也可以使用索引来直接获取列的数据，而不需读取行。如果一个索引包含（或覆盖）所有需要查询的字段的值，我们就称其为“覆盖索引”。

覆盖索引的好处：

• 索引条目通常远小于数据行大小，如果只需读取索引，MySQL 会极大减少数据的访问量。这对缓存的负载很重要，因为时间大部分花费在数据拷贝上。
• 因为索引是按照列值顺序存储的，所以对于 I/O 密集型的查询比随机访问要快得多；
• 由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用，如果二级主键能覆盖查询，则可避免对主键（聚簇）索引的二次查询；

不是所有的索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，所以 MySQL 只能使用 B-Tree。

# 执行查询前，我们建立了覆盖索引(s_id,f_id),通过 explain 后的 Extra 可以看到查询走了索引
explain select s_id,f_id
				from intentory
				
# 很多方式会导致不走索引，这个我们在优化中总结过了

1.6 使用索引扫描做排序

MySQL 有两种方式可以生成有序的结果：①通过排序操作；②按索引顺序扫描。如果 EXPLAIN 出的 type 列的值为 index 则使用了索引。MySQL 可以使用同一个索引既满足排序又满足查找行。

但是使用排序是有条件的：只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时才能使用索引排序。如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段都为第一张表的时候，才能使用索引排序。ORDER BY 子句和查找型查询限制一样，需满足索引最左前缀的要求或前导列为常量。WHERE 和 ORDER BY 中的列值组合为索引最左前缀就可以使用索引。

不要创建冗余索引和重复索引。

1.7 总结

索引是一个很复杂的话题。MySQL 中，大多数情况下选择 B+Tree 索引，其他的只适用于特殊目的。在选择索引和编写利用这些索引的查询时，有如下三个原则始终需铭记：

• 当行查询很慢，如果服务器从存储中获取一个数据只是为了获取其中一行，就浪费了很多工作，这时候索引就可以提升效率；
• 按顺序访问范围数据时是很快的；
• 索引覆盖查询是很快的，因为不需要回表查找行。

总的来说，编写查询语句时尽可能选择合适的索引以避免单行查找，尽可能使用数据原生顺序从而避免额外的排序，并尽可能使用覆盖索引查询。