《高性能MySQL》阅读-高性能索引策略

【四非凡人】：才德兼备是圣人，无才有德是贤人，
有才无德是小人，才德俱失是庸人。

高性能的索引策略

1. 独立的列
   ① 是指索引列不能是表达式的一部分，也不能是函数的参数（MySQL8.0版本之后可以有函数索引了）
2. 前缀索引和索引的选择
   ① 有时索引列字符非常的长，这会让索引变得大且慢
   ② 通常可以选择使用索引的部分字符，这样可以大大节约索引空间，这样同样也降低了索引的选择性
   **索引的选择性：**不重复的索引值（也称为基数，cardinality）和数据表记录总数（#T）的比值，范围从1/（#T）到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行，唯一索引的选择性是1 ，这是最好的索引选择性，性能也是最好的
   ③ 一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT、或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度
   ④ 要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”
3. 多列索引
   1. 在多个列行建立独立的单列索引，大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种“合并索引” 的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。
   2. 合并索引的变种：OR条件的联合，AND条件的相交，组合前两种情况的联合及相交。
   3. 糟糕的索引：
      ① 当出现服务器对多个索引 做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
      ② 当服务器需要对多个索引联合操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上
4. 选择合适的索引列顺序
   1. 在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等
   2. 选择性最高的列放到索引最前列。
5. 聚簇索引
   1. 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。 具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行
   2. 当表有聚簇索引时，它的数据行实际上存储在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。
   3. 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引
   4. 因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引
   5. InnoDB通过主键聚集数据，如果没有主键，InnoDB会选择一个唯一的非空索引代替。如果这样的索引也没有，InnoDB会隐式定义一个主键来作为聚簇索引；
   6. InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相聚很远
   7. 聚集数据的优点：
      ① 可以把数据保存在一起
      ② 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找的要快
      ③ 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
   8. 聚簇索引的缺点
      ① 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没有什么优势了
      ② 插入速度严重依赖于插入顺序。
      ③ 更新聚簇索引列的代价很高。因为会枪支罪InnoDB将每个被更新的行移动到新的位置
      ④ 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间
      ⑤ 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
      ⑥ 二级索引（非聚簇索引）可能比想象的要更大，因为二级索引的叶子节点包含了引用列的主键列
      ⑦ 二级索引查找需要两次索引查找，而不是一次