mysql实战45讲学习笔记--11

11 怎么给字符串字段加索引

        前缀索引的优势，选取字段前几个作为索引，占用的空间更小
        使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
        建立索引的时候关注的是区分度，区分度越高越好，区分度越高，意味着重复的键值越少，因此，可以通过统计索引上有多少不同的值来判断使用多长前缀。
        mysql> select count(distinct email) as L from SUser;
        然后选取不同长度的前缀来看这个值，
        mysql> select
         count(distinct left(email,4)）as L4,
         count(distinct left(email,5)）as L5,
         count(distinct left(email,6)）as L6,
         count(distinct left(email,7)）as L7,
        from SUser;
        使用前缀索引可能会损失区分度。
        使用前缀索引的时候就用不上覆盖索引对查询性能的优化，这也是选择是否使用前缀索引需要的考虑因素。
        实际情况可以根据区分度来看使用什么存储，
        倒序存储和使用hash字段的异同点
        从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗和额外这个hash字段差不多抵消。
        CPU消耗方面，倒序方式读和写的时候，需要额外调用一次reverse函数，而hash字段的方式需要额外调用一次crc32()函数，如果从两个函数的计算复杂度来看，reverse函数额外消耗的CPU资源会更小些。
        查询效率来看，使用hash字段方式的查询性能相对稳定一些。因为crc32算出来的值虽然有冲突的概率，但概率非常小，可以认为每次查询的平均扫描行数接近1。

小结

        使用字符串创建索引的场景
        1.直接创建完整索引，这样可能比较占用空间，
        2.创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
        3.倒序存储，在创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。
        4.创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，都不支持范围扫描。