《高性能MySQL》（第三版）之四：MySQL高性能索引

《高性能MySQL》（第三版）之四：MySQL高性能索引

        这一节的内容以及下一节的查询性能优化篇，感觉还是放在一起比较合适，是目前日常工作中比较常用的内容，说到查询优化往往不能不谈索引，重要性可见一斑。但是限于篇幅，还是不得不拆开了。本期就先谈下索引的小秘密。内容比较多又有些零碎，尽量筛选精粹归纳，化整为零，便于理解记忆。

进入主题之前，了解一下今日歌曲推荐，嘿嘿！
《白月光与朱砂痣》

◎    索引基础：
        mysql先在索引上按值进行查找，然后返回所有包含该值的数据行。如果索引包含多个列，列的顺序很重要，mysql只能高效使用索引的最左前缀列。
        mysql 索引在存储引擎层进行实现。不同的存储引擎以不同的方式实现索引，例如：MyISAM使用前缀压缩技术使得索引更小，InnoDB按照原数据格式进行存储。

※    索引类型：
1、B-Tree索引
        所有的值按照顺序存储，每一个叶子页到根的距离相同。关于B-Tree 以及B+Tree ，之前有转过一篇文章，可以详细了解。https://blog.csdn.net/xiaoanzi123/article/details/106921356
        存储引擎无需再全表扫描，而是从索引根节点（根节点存有指向子节点的指针）开始搜索，根据这些指针向下层查找，进而比较节点页的值和要找的值。叶子节点特别的是，它们的指针指向被索引的数据，而不是其他节点页。树的深度和表的大小直接相关。
        B-Tree对索引列的顺序组织存储，很适合查找范围数据，还可用于查询中的order by操作。索引对多个值排序依据是创建表语句中定义索引时的 列 的顺序。
    
    适用于：
        全职匹配、匹配最左前缀、匹配列前缀、匹配范围值、只访问索引的查询、精确匹配某一列并范围匹配另一列
    限制：
        1、不按照索引最左列查询、
        2、跳过索引中的列、
        3、查询中有某个列的范围查询，其右边所有列都无法使用索引进行优化查询。所以一般建议将需要做范围查询的列放到索引的后面从而使优化器使用尽可能多的索引列。

综上： 顺序 相当重要！

2、Hash索引
        精确匹配索引所有列的查询才有效。
        每行数据，存储引擎都会对所有的索引列计算一个哈希码（值较小，不同键值的行计算出的哈希码也不同），哈希索引把所有的哈希码存在索引中，同时在哈希表中保存指向每个数据行的指针。
        索引自身只存储对应的哈希码，所以索引结构紧凑，查找速度很快。

限制：
        哈希索引数据不按索引值顺序存储，无法用于排序；
        不支持部分索引列匹配查找、因为哈希索引始终是使用索引列的全部内容来计算哈希码，例：数据列（A、B）上简历hash索引，若只查询列A，则无法使用该hash索引；
        只支持等值比较查询，不支持任何范围查询；
        hash冲突时，需存储引擎遍历链表中所有的行指针，逐行比较，直到找到所有的符合条件的行，也就是说，hash冲突时很慢，其他情况下非常快；另外hash冲突越多，索引维护代价越大；

综上：hash索引只适用某些特定场合

3、空间数据索引（R-Tree）
        跳过，别问为啥，知道有这个东西概念即可

4、    全文索引
        查找的文本中的关键词，而不是直接比较索引中的值，后续再详谈。先提一嘴，在相同的列上同时创建全文索引和基于B-Tree的索引不会冲突，因为全文索引是 MATCH AGAINST 的匹配操作，不是普通的where条件操作。

5、    其他类型索引，略过。

◎    索引优点：
        1、    减少服务器需要扫描的数据量
        2、    帮助服务器避免排序和临时表
        3、    将随机I/O变成顺序I/O

        一星索引：索引能将相关记录放到一起
        二星索引：还能 索引中的数据顺序和查找中的排列顺序一致
        三星索引：还能 索引中的列包含了查询中需要的全部列

◎    高性能索引策略：！！！
        比较零碎，突出重点，强化理解！

1、    列独立
        意思是：索引列不能是表达式的一部分或者函数的参数。eg: where sm_id + 1 = 4 
        ps; 养成简化where条件的习惯，始终将索引列单独放到比较符号的一侧。

2、    前缀索引 与 索引的选择性
        索引的选择性：不重复的索引值（也叫基数），和数据表的记录总数（#T）的比值，范围是 1/#T 到 1 之间，其值越高，查询效率越大（因为在查询时可以过滤掉更多的行，降低扫描数据量）。 唯一索引的索引选择性是1，性能也最好。

        如果是很长的varchar、BLOB、TEXT，必须用前缀索引，不然完整长度作为索引的话，那索引得有多大啊，浪费空间，但是前缀索引又要足够长，不然索引的选择性就无法保证，得让索引选择性接近于索引整个列。

        前缀索引能使索引更小更快，但是无法做order by 和 group by,也无法做覆盖扫描。
3、    多列索引
        其实经常会有一些浅显认识生成的结论影响着我们，比如where查询的所有条件列都加上索引，这不扯淡吗？这里请允许我狗头保命…其实还是对底层一知半解导致的。
        这里要说的就是多列联合索引。适用于如下情形，对优化有显著效果：
        ①    、对多个索引做相交操作（通常有多个and条件），不要再去创建多个单列索引！！！
        ②    、对多个索引做联合操作（通常有多个or条件），会耗费大量cpu和内存用于算法的缓存、排序、合并的操作上。而且这些成本不会被优化器统计到查询成本中去

        如果explain 中看到有索引合并，应该好好检查查询和表结。

4、    索引列顺序要合适
        这个顺序的重要性，在前面的B-Tree索引的内容中已经特别强调过了！至关重要！

        如果将选择性最高的列放到索引最前列，会有用，比如不考虑分组和排序时，这样很好，但这点不是绝对的，比如它就没有避免随机I/O和排序 重要。有时候可能违反这个原则，从而保证相关列的索引选择性提高。

5、聚簇索引
        聚簇索引只是一种数据存储方式，不是一种单独的索引类型。
        关键点是如何理解这个聚簇二字?即 在同一个结构中保存了B-Tee索引和数据行，数据行和相邻的键值紧凑的存储在一起。存放在索引的叶子页中。因为存储引擎负责实现索引，并不是所有的存储引擎都支持聚簇索引。主要讨论常用的InnoDB。InnoDB通过主键聚集数据，如果没主键，则选择一个唯一的非空索引代替。如果还没有，则隐式定义一个主键来作为聚簇索引。

    优点：
            1、可把相关数据保存在一起，减少数据页读取，降低磁盘I/O、
            2、数据访问更快、
            3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值
    缺点：
            1、插入速度严重依赖插入顺序（按照主键顺序插入是加载数据到InnoDB表中最快的方式，如果不是按照主键顺序加载数据，加载完成后执行命令 optimize table 对表重新组织一下）
            2、更新聚簇索引代价高
            3、插入新行或者主键被更新导致需要移动行时，页分裂问题，导致占用更多磁盘空间
            4、可能导致全表扫描变慢
            5、二级索引（非聚簇索引）可能更大（二级索引叶子节点包含了引用行的主键列）
            6、二级索引的访问需要两次索引查找，不是一次（原因是二级索引保存的行指针实质上是行的主键值，而不是指向行的物理位置）

    可见，使用聚簇索引，在    InnoDB表中按照主键顺序插入行的重要性，尤其是I/O密集型的应用。 InnoDB关于页分裂的默认最大填充因子是15/16，达到后会创建新页，数据是依次按顺序追加的。

    所以不要使用uuid这种数据作为主键，它会导致聚簇索引的插入变得随机不连续。而且导致大量随机I/O、频繁页分裂，导致大量碎片。

6、覆盖索引
        如果一个索引包含了所有需要查询的字段值，称之为覆盖索引。 无需再回表查询。0

    好处：
        1、    减少数据访问量、
        2、    索引是顺序存储的，范围查询时能减少I/O
        3、    由于二级索引在叶子节点中保存了行的主键值，二级主键能够覆盖查询的话可以避免主键索引的二次查询

    覆盖索引必须存储索引列的值，只能覆盖那些只访问索引中部分列的查询。如何理解呢？其实mysql查询优化器会判断一个条件，即 索引覆盖了where条件的字段，但不是整个查询涉及的字段，这个条件不满足的时候，照样回表查询。（mysql5.5以及之前版本）.原因是没有任何索引能覆盖这个查询。解决思路，推荐扩展索引至所有数据列，再用延迟关联的方式。

7、使用索引扫描做排序
        如果索引不能覆盖查询所需的全部列，不得不每扫描一条索引就回表查询一次对应的行，基本都是随机I/O，所以按索引顺序读取数据通常比顺序全表扫描还慢。！！！
        ps： 补充一句，这个是经常会遇到的，明明创建了索引，但是explain发现还是走的全表扫描，索引没生效，可能就是这个原因。

        当索引的列顺序和order by 子句顺序完全一致，并且所有的列的排序方向都一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by 子句引用的字段全部为第一个表的时候，才能使用索引做排序。order by 子句也需要满足索引的最左前缀要求，否则无法利用索引排序，除非索引的第一列被指定为常数。
        PS: 上面这一段要求条件的描述挺长，最好网上找个例子实践一番更加有助于理解。本书中的例子参见P177.

8、压缩（前缀压缩）索引
        MyISAM 使用前缀压缩减少索引的大小以便于更多的索引放入内存中，从而提升性能。默认只压缩字符串。

9、冗余和重复索引
        如果有重复索引，需要mysql单独维护，在查询优化的时候也要逐一考虑，影响性能。
        已有索引（a,b），再创建索引（a）就是冗余索引。大多数情况下都不需要冗余索引。
        尽量扩展已有索引，而不是创建新索引。索引越多，dml操作会变慢。

10、未使用的索引
    一个字，删

11、索引和锁
        InnoDB只有在访问行的时候才会对其加锁，索引能减少访问数据量（行数），从而减少锁的的数量。
        InnoDB在二级索引上使用共享（读）锁，但访问主键索引使用排他（写）锁。

◎    小结
        三原则：
                1、    单行访问很慢，最好读取的块中能包含尽可能多所需要的行，使索引可以创建位置引用以提升效率。
                2、    按顺序访问范围数据很快（原因有二，1、顺序I/O不需要多次磁盘寻道。2、服务器无需再做额外的排序工作）
                3、    索引覆盖查询很快，主要是因为无需回表查找行，避免了大量单行访问。

        这一章的内容多回味多理解，必定受益多多。下一节，我们迈入第二步，窥探查询性能的优化提升。下期不见不散。

 

下期预告：《高性能MySQL》（第三版）之五：查询性能优化

 

 

码字不易，感谢支持！ 点关注，下次相遇不迷路！

欢迎关注微信公众号：独行侠的守望
      - - 无论如何都要坚定的前行，去拼搏，去奋斗。爱生活，爱自己！码农一枚，主要分享生活日常、兴趣爱好，记录成长。欢迎一起交流！        
                                                                                                     
本文同步发布于个人网站：https://www.yuanhuiying.com    进入主站点击“愚默博客“标签页进行访问。