高性能的索引策略

1.前缀索引
有时候需要索引很长的字符列， 这会让索引变得大且慢。
通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。 但这样也会降低索引的选择性。
索引的选择性是指，不重复的索引值（也称为基数， cardinality）和数据表的记录总数（＃T）的比值，
范围从 0 到1之间。索引的选择性 越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行 比较
SELECT COUNT() AS cnt, city FROM sakila,city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
SELECT COUNT() AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

计算合适的前缀长度的另外一个办法就是计算完整列的选择性， 并使前缀的选择性接近于完整列的选择性。 下面显示如何计算完整列的选择性：
SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3 From sakila.city_demo
2.建立合适的复合索引和选择适当的索引列顺序：
在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。 所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER by、 GROUP BY和DISTINCT等子句的查询需求。
建立索引列顺序的原则：
1.选择性高的放在最前列：

SELECT COUNT(DISTINCT staff_id}/COUNT(*) AS staff_id_selectivity
 	COUNT(DISTINCT customer_id}/COUNT(*) AS customer_id_selectivity, 
 	COUNT(*) 
 	FROM payment

2.经常使用的，或单独使用概率大的放在最前列：
根据最左原则，复合索引的第一列的单独使用是可以用到此索引的，把很可能单独使用的列放在最前列，可以减少冗余索引的建立，减少索引数量，这样提高整个数据库服务器的性能。
3.聚簇索引：
InnoDB将通过主键聚集数据，称为聚簇索引。
聚集的数据有一些重要的优点：
1.可以把相关数据保存在一起。 例如实现电子邮箱时， 可以根据用户ID 来聚集数据， 这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。 如果没有使用聚簇索引， 则每封邮件都可能导致一次磁盘I/0.
2.数据访问更快。 聚簇索引将索引和数据保存在同一个B-Tree中， 因此从聚簇索引中 获取数据通常比在非聚簇索引中查找要快。
3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
如果在设计表和查询时能充分利用上面的优点， 那就能极大地提升性能。 同时， 聚簇索引也有一些缺点：
1.聚簇数据最大限度地提高了1/0密集型应用的性能， 但如果数据全部都放在内存中， 则访问的顺序就没那么重要了， 聚簇索引也就没什么优势了。
2.插入速度严重依赖于插入顺序。 按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。 但如果不是按照主键顺序加载数据， 那么在加载完成后最好使用OPTIMIZE TABLE 命令重新组织一下表。
3.更新聚簇索引列的代价很高， 因为会强制InnoDB将每个被更新的行移动到新的位置。
4.基于聚簇索引的表在插入新行， 或者主键被更新导致需要移动行的时候， 可能面临 “页分裂（page split）” 的问题。 当行的主键值要求必须将这一行插入到某个已存在的页中时， 存储引擎会将该页分裂成两个页面来容纳该行， 这就是一次页分裂操作。 页分裂会导致表占用更多的磁盘空间。
5.聚簇索引可能导致全表扫描变慢， 尤其是行比较稀疏， 或者由于页分裂导致数据存储不连续的时候。
6.二级索引（非聚簇索引） 可能比想象的要更大， 因为在二级索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找，而不是一次。
如果正在使用InnoDB表井且没有什么数据需要聚集， 那么可以定义一个代理键
(surrogate key）作为主键， 这种主键的数据应该和应用无关， 最简单的方曲是使用 AUTO_INCREMET自增列。这样可以保证数据行是按顺序写人， 对于根据主键做关联操作的性能也会更好。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引， 特别是对于1/0密集型的 应用。 例如， 从性能的角度考虑， 使用UUID来作为聚簇索引则会很糟糕，因为它使得聚簇 索引的插入变得完全随机， 这是最坏的情况， 使得数据没有任何聚集特性。
4.覆盖索引：
一个索引包含（或者说覆盖）所有需要查询的宇段的值。
索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。
因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于I/0密集型的 范围查询会比随机从磁盘读取每一行数据的1/0要少得多。
由于InnoDB的聚簇索引，覆盖索引对lnnoDB表特别有用。 InnoDB的二级索引在 叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主 键索引的二次查询。
1.只用到复合索引的第一列：

2.使用覆盖索引：

5.order by优化
只有当索引的列顺序和ORDER BY子句的顺序完全一致， 并且所有列的排序方向（倒序
或正序）都一样时，MySQL才能够使用索引来对结果做排序位14。如果查询需要关联多张表， 则只有当 ORDE R BY 子句引用的字段全部为第一个表时，才能使用索引做排序。 ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求s否则， MySQL都需要执行排序操作，而无法利用索引排序。
1.使用的filesort文档排序：

2.优化后使用索引排序：

3.复合索引第一列使用的范围查找，使用的是filesort

4.复合索引跳过中间列，使用filesort

5.复合索引只用前俩列，使用索引排序

6.复合索引第一列是常数，第二列是范围查找，使用的是filesort