MYSQL之高性能的mysql（五）--索引
创建高性能的索引
索引的优点
高性能的索引策略
独立的列
前缀索引和索引选择性
如果需要索引很长的字符串，可以索引开始的部分字符，可以大大节约索引空间从而提高索引效率，但是降低了索引的选择性（不重复的索引值和数据表的记录总数）。为了决定前缀的合适长度，第一种方法是需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。第二种方法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性：
创建前缀索引：ALTER TABLE sakila.city_demo ADD KEY (city(7));
优点：使索引更小更快的有效办法；
缺点：无法使用前缀索引做覆盖扫描，无法使用前缀索引做ORDER BY，GROUP BY；
多列索引
在多个列上建立单独的索引大部分情况下不能提高mysql的查询性能，在老版本的mysql中，只能使用其中某一个单列索引，然而这种情况下没有哪一个单例索引是非常有效的。MySQL5.0和更新版本中引入了“索引合并”，能够同时使用多个单例索引进行扫描，并将结果进行合并，包括：OR条件的联合（union），AND条件的相交（intersection），组合这两种情况的联合及相交。 索引合并策略有时候是一种优化的结果，但实际上说明了表上的索引建的很糟糕：
当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引；
当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量的CPU资源和内存资源在算法的缓存，排序和合并操作上。
优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取，使得查询的成本被低估导致该执行计划还不如走全表扫描。
如果在explain中看到索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的，也可以通过参数optimizer_switch来关闭索引合并功能。
选择合适的索引顺序
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的，这是后索引的作用只是用于优化where条件的查询，对于在where子句中使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关（值得分布）。
聚簇索引（数据行存放在索引的叶子页）
优点：
把相关的数据保存在一起减少磁盘IO；
聚簇索引将索引和数据页保存在同一个B-Tree中，数据访问更快，不用回表查询；
使用覆盖索引扫描得查询可以直接使用叶结点中得主键值；
缺点：
如果数据全都放在内存中，则访问顺序没那么重要了，聚簇索引就没优势；
插入数据的速度依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式，如果不按主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表；
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
基于聚簇索引列的表在插入新行，或者主键被更新导致需要移动行的时候，可能会面临“页分裂”的问题，页分裂会导致表占用更多的磁盘空间；
聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于分页导致数据存储不连续的时候；
二级索引（非聚簇）可能比想象中的更大，因为在二级索引的叶子节点包含了引用行的主键列；且需要两次索引查找；
覆盖索引
（一个索引包含所有要查询的字段的值，查询只需扫描索引而无需回表）
优点：
索引的条目通常要小于数据行的大小，如果只需要读索引，MySQL就会极大地减少数据访问量；
索引是按列值顺序存储的（至少在一个单页内），所以对于I/O密集型的范围查询回比从磁盘读取每一行数据的I/O要少的多；
一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此访问数据需要一次系统调用；
由于InnoDB的聚簇索引，覆盖索引对InnDB表特别有用，InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，可以避免对主键索引的二级查询；
使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果：通过排序操作或者按照索引顺序扫描，如果explain出来的type列的值为index，则说明mysql使用了索引扫描来做排序；
扫描索引本身虽然很快，但如果索引不能覆盖查询所需的全部列，就不得不每扫描一条索引记录就回表查询一次对应得行，这基本都是随机I/O操作，所以按索引顺序读取数据得速度通常要比顺序地全表扫描还要慢；
只有当索引得列顺序和order by子句得顺序完全一致，并且所有列得排序方向都一样时，MySQL才能够使用索引来对结果做排序；如果查询需要关联多张表，则只有当order by子句引用得字段全部为第一个表时才能使用索引做排序。
冗余和重复索引
重复索引是指在相同得列上按照相同得顺序创建相同类型得索引（如创建主键，加上唯一限制后，又加索引会创建三个重复二等索引），应该避免，并及时删除；可以通过写一些复杂得访问INFORMATION_SCHEMA表得查询来找，或者使用common_schema中得一些视图来定位。
未使用得索引
定位未使用得索引：在Percona Server或MariaDB中先打开userstates服务器变量（默认是关闭的），然后让服务器运行一段时间再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引得使用频率；
