高性能MySQL读书笔记第5章-创建高性能的索引

1. 索引的优点
    1. 大大减少了服务器需要扫描的数据量；
    2. 帮助服务器避免排序和临时表；
    3. 将随机IO变为顺序IO
    《Relational Database Index Design and the Optimizers》的三星系统：索引将相关记录放到一起则获得一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含查询需要的全部列则获得三星（即索引覆盖）
    索引的缺点主要是使得插入、更新、删除变得复杂，并且占用更多的存储空间；
2. 高性能的索引策略
    1. 独立的列，指的是索引列不能是表达式的一部分，也不能是函数的参数
    2. 前缀索引和索引的选择性
        1. 前缀索引是指一个索引列的前几个字符，这样可以大大节约索引空间，提高索引效率，但会降低索引的选择性
        2. 索引的选择性是指，不重复的索引值和数据表的记录总数的比值，范围从1/count~1之间，索引的选择性越高则查询效率越高，因为可以过滤更多的行。简单理解就是区分度。
3. 多列索引，即联合索引
    -索引失效的情况：
    1. 使用范围查询（between and、like、>或<、in），后面的索引列均失效
    2. 索引列在表达式中或者作为函数参数
    3. 复合索引未先使用最左列字段
    4. like以%开头，则当前索引失效
    5. or只有一边使用索引，则索引失效，仅当or两边同时使用索引时，才会使用索引
    6. 当全表扫描速度比索引速度快时，会使用全表扫描，此时索引失败
    7. 当索引字段上使用<>或<=>或！=时，会进行全表扫描，此时索引失效
4. 选择合适的索引列顺序：首先按照第一列排序；其次按照第二列，等等

5. InnoDB和MyISAM存储引擎的实现差别
    1. InnoDB的主键索引即聚簇索引，叶子节点存储数据行，而MyISAM的主键索引称为非聚簇索引，叶子节点存储数据行的指针
    2. InnoDB的非主键索引即二级索引，存储了（索引列，主键），当查询字段包含于（索引列，主键）中时，称为索引覆盖，若查询字段多于该列时，则需要回表查询；而MyISAM的非主键索引和主键索引一样，都是叶子节点存储数据行的指针
6. 压缩索引：是为了节约存储空间而设计的索引，如perform和performance，第一条记录为perform，第二条记录为"7,ance"，由于存在对前面数据的依赖，故不能进行倒序扫描，只能顺序扫描，不能二分查找，因此查询效率低
7. 冗余索引和重复索引：重复索引如对主键设计了unique索引又设计了Index索引，事实上，主键索引和unique索引都是用index来实现的；冗余索引，如联合索引（A，B），再添加索引（A）就是冗余索引，因为（A，B）可以当A来使用，而如果添加B或（B，A）则是合理的，另外，索引（A）（非主键）隐含了就是（A，ID），因此再添加索引（A，ID）则是冗余的

8. 支持多种过滤条件，切勿滥用in，如where a in(1,2,3) and b in ('a','b','c') and c in ('ttt','fdsf'),则优化器会会转换为3*3*2=18种组合，执行计划需要检查where子句中的所有18种组合

9. 数据碎片：
    1. 行碎片：指的是数据行被存储为多个地方的多个片段中
    2. 行间碎片：指逻辑上顺序的页，或页在磁盘上不是顺序存储的
    3. 剩余空间碎片：指数据页中有大量的空余空间，这会导致服务器读取大量不需要的数据，从而造成浪费
    4. 对于MyISAM，三种碎片都可能存在，而InnoDB不会存在行碎片