mysql 索引问题整理

1. 索引是什么，是为了解决什么问题：
   答：索引的出现是为了提高数据查询的效率，就像书的目录一样

2. 索引的常见模型：
   答：主要有哈希表、有序数组、搜索树

3. 哈希表、有序数组、搜索树的特点：
   答：
   哈希表：适用于都是等值查询的这种
   有序数组：在等值查询和范围查询场景中的性能都非常优秀，但是在更新场景中，需要成本就很大，所以这个类型只适用于静态储存引擎，存一些不会变动的数据
   搜索树：等值查询和范围查询和更新操作性能都很稳定

4. 索引是在哪一层实现的：
   答：索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

5. InnoDB的索引模型：
   答：在InnoDB中，表都是根据主键顺序以索引的形式进行发放的，数据都是存储在B+树，这种存储的方式被称为索引组织表。每个索引在InnoDB中对应一颗B+树。

6. 什么是聚簇索引和非聚簇索引：
   答：聚簇索引的叶子节点存的是整行数据，非聚簇索引的叶子节点存的是主键索引的值。聚簇索引又被称为主键索引，非聚簇索引又被称为二级索引。

7. 主键索引和普通索引有什么区别：
   答：主键索引的查询只要搜索主键ID这棵B+树索引就好了，普通索引是先在普通索引的B+树上找到主键的值，然后拿到主键的值再去主键ID这棵B+索引树进行搜索。 这种查询数据的过程称为回表。

8. 什么是页分裂和页合并：
   答：新插入一个值，当前的页刚好满了需要申请一个新的页，然后把部分数据挪过去，被称为页分裂
   当相邻的两个页由于删除了数据，利用率很低之后，会将页合并，这个过程被称为页合并。

9. 为什么要用自增主键：
   答：性能方面：自增主键在不指定ID的时候，默认会取最大的ID+1，每次插入一条新纪录，都是追加操作，都不涉及到挪到其他记录，也不会触发页分裂。
   存储方面：因为非主键索引的叶子结点上都是主键的值，那么整型做主键的话只要4个字节长整形8个字节，如果身份证号这种做主键的话需要20个字节，显然，主键长度越小，普通索引的叶子结点就越小，普通索引占用的空间也就越小。

10. 什么是覆盖索引：
    答：要查询的字段在普通索引中已经包含了的话，就不需要进行回表查询，被称为覆盖索引。但是需要注意的是，索引的维护是总是有代价的，需要建立冗余索引时就需要考虑的多一点。

11. 什么是最左前缀原则：
    答：顾名思义就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

12. 什么是最左匹配原则：
    答：mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a=1 and b=2 and c>3 and d=4 如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，a,b,d的顺序可以任意调整。
    =和in可以乱序，比如a=1 and b=2 and c=3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

13. 什么是索引下推：
    答：可以在索引遍历过程中，对索引包含的字段先做判断，直接过滤掉不满足的条件纪录，减少回表次数。

14. 怎么重建索引：
    答：alter table T engine=InnoDB命令，普通删除索引的话，不会释放空间。

15. 为什么表数据删掉一半，表文件大小不变？
    答：因为delete命令其实只是把记录的位置，或者数据页标记为“可复用”，但是磁盘文件的大小是不会变的，也可以认为是一种逻辑删除，所以物理空间没有实际释放，只是标记为可复用，表文件的大小当然也不会变。

16. 表的数据信息存哪里
    答：表数据信息可能较小也可能巨大无比，它可以存储在共享表空间，也可以单独存储在一个以.ibd为后缀的文件里，由参数innodb_file_per_table来控制，建议总是作为一个单独的文件来存储，这样非常容易管理，并且在不需要的时候，使用drop table命令也能直接把对应的文件删除，如果存储在共享空间之中即使表删除了空间也不会释放。

17. 表结构的信息存在哪里？
    答：首先表结构定义占有的存储空间比较小，在Mysql8.0之前，表结构的定义信息存在以.frm为后缀的文件中，在mysql8.0之后，则允许把表结构的定义信息存在系统数据表中

18. 如何才能删除表数据后，表文件大小就变小？
    答：重建表，消除表因为进行大量的增删改查操作而产生的空洞，使用如下命令：
    alter table t engine = Innodb （recreate）
    analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁。
    optimize table t（等于 recreate+analyze）
    truncate table t（等于drop+create）

19. 空洞是啥？咋产生的？
    答：空洞就是那些被标记可以复用但是还没被使用的存储空间。使用delete命令删除数据会产生空洞，标记为可复用插入新的数据可能产生页分裂，也可能产生空洞修改操作，有时是一种先删除后插入的动作，也可能产生空洞。

20. Mysql是怎么选择索引的？
    答：选择索引是优化器的工作，优化器会结合扫描行数、是否使用临时表、是否排序、是否回表等因素来进行综合判断。其中扫描行数的判断逻辑为：通过基数来判断，一个索引上不同的值的个数称为基数。

21. mysql是怎样得到索引的基数的？
    答：mysql是通过采样统计得到基数的，采样统计的时候，InnoDB默认选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数。

22. 索引的基数统计信息什么时候变更？
    答：索引统计在当变更数据行数超过1/M的时候，会自动触发重新做一次索引统计。在mysql中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择，设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

23. 统计信息不准确的情况下怎解决：
    答：analyze table

24. 索引选择异常如何处理：
    答：采用force index 强行选择一个索引
    修改语句，引导mysql使用我们期望的索引，比如加上order by
    新建一个更合适的索引或者删除误用的索引
    analyze table重新统计信息

25. Order by 是走内存排序还是外部排序：
    答：首先对于需要排序的情况下来说，mysql会给每个线程分配一块内存用于排序，称为 sort_buffer，是否走内存排序，取决于排序所需的内存和参数 sort_buffer_size（开辟的内存的发小），如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。如果排序量太大，内存放不下，就需要利用磁盘临时文件辅助排序。

26. 什么是全字段排序？
    答：sort_buffer中存储的待排序数据，包括需要返回的所有字段，可以做到回表次数减少。

27. 什么是rowid排序？
    答：sort_buffer中存储的待排序数据，只包括待排序字段和对应行的主键ID，等到排序完毕以后，需要回表查询出来需要返回的其他字段数据。

28. 什么时候选择全字段排序？什么时候选择rowId排序？
    答：如果内存足够大，会优先选择全字段排序，反之就选择rowid排序。rowid排序会把全字段排序多一次回表操作。

29. 哪些操作会导致不走索引？
    答：条件字段函数操作
    隐士类型转换，如订单号查询到时候没有引号包裹
    隐式字符编码转换