MySQL理论汇总大纲——索引相关

索引相关

1、索引的作用：

      提高数据查询效率

2、常见索引模型：

      哈希表、有序数组、搜索树

        哈希表：键 - 值(key - value)

•             哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置
•             哈希冲突的处理办法：链表
•             哈希表适用场景：只有等值查询的场景

        有序数组：

•             按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))
•             有序数组查询效率高，更新效率低
•             有序数组的适用场景：静态存储引擎。

        二叉搜索树：

•             每个节点的左儿子小于父节点，父节点又小于右儿子
•             查询时间复杂度O(log(N))，更新时间复杂度O(log(N))
•             数据库存储大多不适用二叉树，因为树高过高，会适用N叉树

        InnoDB中的索引模型：B+Tree
            一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
   

3、索引类型

        主键索引：

 

•             主键索引的叶子节点存的是整行的数据(聚簇索引)
•             主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据；普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)

            从性能和存储空间方面考量，自增主键往往是更合理的选择。

        非主键索引：

            非主键索引的叶子节点内容是主键的值(二级索引)。

4、 回表

        回到主键索引树搜索的过程，称为回表。

 5、覆盖索引

        覆盖索引就是要查询的字段就在索引树中，这样可以减少回表的次数。

6、最左前缀原则

        MYSQL做词法分析语法分析的时候是通过建立最左子树来建立语法树的，解析的过程也是从左到右所以遵循最左前缀的原则。
        例如索引是key index (a,b,c). 可以支持a a,b a,b,c 3种组合进行查找，但不支持 b,c进行查找。而且当已经有了 (a,b) 这个联合索引后，就不需要单独在 a 上建立索引了。
 

7、 索引下推

        在5.6之后，联合索引先找到最左的那个索引，然后根据联合查询的条件，过滤右边的索引，就减少了回表。
        例如：like 'hello%’and age >10 检索，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度。

8、普通索引和唯一索引，如何选择？

        查询：

 

•             唯一索引

                由于索引唯一性，查到第一个满足条件的记录后，停止检索。

•             普通索引

                查到满足条件的第一个记录后，继续查找下一个记录，直到第一个不满足条件的记录。

      由于InnoDB根据数据页来读写，两者的性能差距微乎其微。

        更新：

 

•             change buffer

         只有普通索引可以用change buffer；
         1> 当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中的与这个页有关的操作；
          2> change buffer是可以持久化的数据。在内存中有拷贝，也会被写入到磁盘上；
          3> change buffer用的是buffer pool里的内存，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。

•                 merge

          将change buffer中的操作应用到原数据页上，得到最新结果的过程，称为merge；
          访问这个数据页会触发merge，系统有后台线程定期merge，在数据库正常关闭的过程中，也会执行merge；
          将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。 change buffer 因为减少了随机磁盘访问，所以对更新性能的提升很明显。

•                 change buffer使用场景

            在一个数据页做merge之前，change buffer记录的变更越多，收益就越大。即适用于写多读少的业务,如：账单类、日志类的系统

9、如何给字符串字段加索引

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

    实际应用中，你要根据业务字段的特点选择使用哪种方式。

10、"order by"是怎么工作的

        1、MySQL为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size

•             如果排序的数据量小于sort_buffer_size，排序将会在内存中完成；
•             如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序；
•             在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件；

        2、mysql通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序

•             全字段排序

                1>.通过索引将所需的字段全部读取到sort_buffer中
                2>.按照排序字段进行排序
                3>.将结果集返回给客户端

  缺点：
   1.造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高。
   2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差。

  优点：

   MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作。

•             rowid排序

                1>.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
                2>.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
                3>.按照排序后的顺序，取id进行回表取出想要获取的数据
                4>.将结果集返回给客户端
       3、按照排序的结果返回客户所取行数

   优点：

    更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问。

   缺点：

    回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问。

 11、为什么SQL语句逻辑相同，性能却差异巨大？

          对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，从而导致性能问题。

 

                                                                                                                                                                                      ——整理自丁奇老师专栏《MySQL实战45讲》