《高性能mysql》读书笔记(第五章)

索引：存储引擎用于快速找到记录的一种数据结构，类似于书籍的目录。
作用：在大数据量的查询时提高查询效率。
一、索引基础
      使用索引去查询数据所在的位置而不是通过扫表一行一行的去查询数据所在的位置。可以有单个列或多个列组成的索引；对于多个列组成的索引，列的位置很重要，因为mysql只能高效的使用索引的最左前缀列。
1.1索引的类型
        索引存在于引擎层而不是服务器层，不同的引擎的索引的工作方式会不同，也不是所有的引擎支持所有类型的索引。
[1] B-Tree索引
         大多数mysql存储引擎都是用B-Tree数据结构来存储数据。
        MyISAM 使用前缀压缩技术使索引更小，通过数据的物理位置引用数据的行。
        InnoDB  使用原数据格式存储，根据主键引用被索引的行。
        B-Tree意味着所有的数据按顺序存储的，每一个叶子页到根的距离相同。
       使用B-Tree的存储引擎不需要全表扫描获取需要的数据，而是从索引的根节点开始搜索。根节点的槽中有指针，根据指针向下层查找，比较要查找的值和节点页的值可以找到合适的指针进入下层子节点，最终要么找到数据，要么记录不存在。
    对如下类型的索引的查询有效：
        全值匹配
        匹配最左前缀
        匹配列前缀
        匹配范围值
        精确匹配某一列并范围匹配另一列
        只访问索引的查询
    B-Tree通常支持查询只访问索引，不访问数据行。
    eg：多个列(A,B,C)组成的索引  A - B - C
    索引的限制：
        如果不是按照索引的最左列开始查找，则无法使用索引，不能用B或C查询，不是最左列。
        不能跳过索引中的列,不能跳过B用A和C去查询。
        查询中有某个列的范围查询，则右边所有列无法使用索引优化查询，例如where A=‘aa’ and
        B  LIKE  'b%' and C = 'man';只能使用索引的前两列
[2]哈希索引
    基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存在里面，同时哈希表中存在指向数据行的指针。
     在mysql中只有memory引擎显示支持哈希索引，也是memory引擎的默认索引类型。同事此引擎也支持B-Tree索引类型。
      注意：哈希索引支持非唯一哈希索引的，如果多个列的哈希值相同，则索引以链表的方式存放多个记录指针到同一个哈希索引中。
     eg：select name from user where age = 11;    
 查询时先计算11的哈希值，然后从索引中找到对应的指针，最后确保值时想要的行。
      哈希索引的限制：
              只包含哈希索引和指针；
              不是按照索引顺序来的，不能排序；
              不支持部分索引列匹配查找；
              只支持等值比较查询；
              访问哈希索引的数据很快，除非有很多哈希冲突，冲突出现时，存储引擎遍历链表的的行指针，逐行比较，得到结果；
               哈希冲突较多，索引维护的代价会很高。
       InnoDB上有一个特殊功能叫做“自适应哈希索引”，你当InnoDB注意到某些索引值被使用的很频繁，它会在内存中基于B-Tree索引之上再建一个哈希索引，B-Tree也具有哈希索引的功能，这是一个完全自动的内部的行为，用户无法控制，但可以关闭该功能。
       还可以创建自定义的哈希索引。提高查询性能，缺点是需要手动维护哈希值。
[3]空间数据索引
    MyISAM引擎的表支持空间索引，可以用于存储地理数据。这类索引无前缀查询。
[4]全文索引
    适用于MATCH AGAINST操作，不适用于不同的where操作。
[5]其他索引
    TokuDB使用形树索引，既有B-Tree的优点，避免了B-Tree的缺点。
二、索引的优点
      1.减少服务器需要扫描的数据量
      2.帮助服务器避免排序和临时表
      3.可以将随机I/O变为顺序I/O
三、高性能的索引策略
     3.1 独立的列
     3.2前缀索引和索引选择性
     3.3多列索引
          索引不是越多越好，根据业务实际需要设置合理的索引，索引越多耗费较多的CPU和内存资源在算法的缓存、排序和合并上。
     3.4选择合适的索引列顺序
     3.5聚簇索引
           不是单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引是在同一个结构中保存了B-Tree索引和数据行。
           优点：
                  把相关数据保存在一起。
                  数据访问更快。
                  使用覆盖索引扫描的查询可以直接使用页节点主键值。
           缺点：
                   提高了I/O密集型的应用性能，但如果数据放在内存中，那么就没有优势了。
                   更新聚簇索引代价高，强制InnoDB将每个被更新的行移动到新的位置。
                   插入新行货主键更新移动的时候面临页分裂的问题，占用更多的空间、
                   导致全表扫描变慢。
                   二级索引可能要更大，因为二级索引的叶子节点包含了引用行的主键列。
                   二级索引需要两次索引查找。
        3.6覆盖索引
优点：
         索引条目远小于数据行，减少数据访问量。
         是按照列顺序存储的(在单页中是这样)，对于I/O密集型的范围查询比随机读取一行数据的I/O要少。
         一些存储引擎像MyISAM在内存中只缓存索引，数据依赖操作系统来缓存，访问数据需要一次系统调用，导致严重的性能问题。
          由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。
         3.7使用索引扫描来排序
               mysql两种方式可以生成有序的结果：
                     通过排序操作
                     通过索引扫描
          3.8压缩索引(前缀压缩)
               MyISAM使用前缀压缩减少索引的大小，从而将更多地索引可以放在内存，在某些情况可以提高很大的性能。默认是字符压缩，也可通过配置对整数压缩。
               前缀压缩使用更少的空间，代价是某些操作可能更慢，因为每个压缩前缀都依赖前面的值，所以无法使用二分法查找只能从头扫描，正序扫描速度还可以，倒序就比较慢。
          3.9冗余和重复索引
              重复索引：mysql允许在同一列上按照相同的顺序创建相同类型的索引，但要避免此种情况，最明显的就是对主键列创建索引和唯一约束。     
              冗余索引：创建了索引(A,B)又创建了索引(A)，那么索引(A)就是冗余索引，因为前面的那个索引也可以当做索引(A)来使用。
              在不断的操作表的时候尽量操作现有的索引而不是一味的增加索引，同时也要考虑性能问题。
          3.10未使用的索引
                 如何定位并删除：
                a.在Percona Server 或 MariaDB中先打开userstates服务器变量(默认是关闭)，然后服务器运行一段时间，在通过查询 INFOMATION_SCHEMA.INDEX_STATISTICS得到每隔索引的使用频率。
                b.使用Percona Toolkit 中的 pt-index-usage
   3.11索引和锁
          InnoDB只会在访问的时候加锁，而索引能够减少InnoDB访问的行数，进而减少锁的数量，前提是当InnoDB在存储引擎层能过滤所有不需要的行才有效。否则可能会锁住较多的行。

四、索引案例学习  
       4.1支持多种过滤条件
       4.2避免多个范围条件
       4.3优化排序
五、维护索引和表
       5.1找到并修复损坏的表
            check table 可以检查大多数的表损坏。
             常用的InnoDB引擎的表如果出现损坏，一定发生了严重的错误，需要立刻调查，他的设计保证了并不容易损坏，如果发生损坏，一般是数据库的硬件问题(内存和磁盘)。
       5.2更新索引统计信息
             mysql的查询优化通过两个API来了解存储引擎的索引值的分布信息：
             第一个API:   records_in_range()
             第二个API：info()
       5.3减少索引和数据的碎片
            数据碎片类型：
                  行碎片
                  行间碎片
                  剩余空间碎片
            解决方案：
                   通过执行 OPTIMISE TABLE或者导出再导入的方式重新整理数据。
                   对于不支持OPTIMISE TABLE的存储引擎，可以通过不作任何操作的 alter table 操作来重建表，只需要将引擎改为当前的引擎即可。