MySQL索引原理与使用原则

1.索引是什么

索引是一类数据结构，凭借数据结构的特性，使得在数据库中使用索引能够减少数据检索所需要的时间。

常见的索引有两种：聚集(簇)索引和非聚集(簇)索引。

• 聚集索引
  聚集索引又称为主键索引，顾名思义当创建表时指定了主键字段，主键索引是默认自动创建的。聚集索引的特定是不能为空，且值具有唯一性。
• 非聚集索引
  非聚集索引主要有三种，普通索引、唯一索引（唯一性）、full text索引（仅在MyISAM引擎中支持，解决大文本like效率低下）。

2.索引的底层数据结构

• 有序数组
  数组的值是顺序排列的，等值查询或者范围查询的速度特别快，由于插入数据需要移动数组里的元素，所以插入特别慢。
• 单链表
  单链表使用指针进行数据关联，插入数据时只需要从链表尾部插入即可，速度特别快，时间复杂度达到O(1)级别。
  检索数据需要进行全链表检索，速度特别慢，时间复杂度达到O(n)级别。
• 树
  • 二叉查找树
    最普通的二叉搜索树，左子树的值小于父节点，右子树的值大于父节点。
  • 平衡二叉树（AVL树）
    动态的二叉搜索树，左右子树的深度差绝对值不超过1。
    实现方式：插入过程中，如树条件不满足，平衡二叉树动态的进行左旋或右旋。
    缺点：一个子树消耗一个页的空间（默认16K），子树最大值为2，数据量大时，一次查询需要发生大量IO读取（假如磁盘是机械，还存在磁盘寻址时间，性能低下）。
    解决办法：一个子树存放尽量多的节点，减少IO读取次数，平衡二叉树（AVL树） —》 多路平衡查找树（B树）。
  • 多路平衡查找树（B树）
    索引Key的数量（关键字数）：N，子树的引用数量（度）：N + 1。
    原因：索引Key是平衡的，如Key数量为2，则存在的取值范围数为3。
    a<key1 , key1 <= a <= key2 , key2 < a
    特点：非最下层的叶子节点也可能存放完整的数据，一个子树消耗一个页的空间（默认16K），对比AVL树，大大减少IO读取次数。
  • 加强版多路平衡查找树（B+树）
    索引Key的数量（关键字数）：N，子树的引用数量（度）：N 。
    特点；只有最下层的叶子节点才会存放完整的数据，其他叶子节点只有key值和指针引用。最下层的子树之间（存放真实的表的列数据子树）使用了指针进行连接，极大的增强了范围查找的性能。
    优点：B+树 > B 树，扫库、扫表能力更强，磁盘读写能力更强，排序能力更强，效率更加稳定。

3.不同存储引擎中索引的落地方式

• MyISAM
  数据和索引以文件的方式分别存储的.MYD(数据文件)，.MYI(索引文件)。
  工作方式：主键索引寻找数据的逻辑和辅助索引一致。
  • 优势：使用前缀压缩技术使得索引更小
    • 先完全保存索引块中的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分,把这部分存储起来即可。
    • 索引块中的第一个值是"perform",第二个值是"performance",那么第二个值的前缀压缩后存储的是类似"7,ance"这样的形式。
    • 这个技术使得空间的消耗更小，但是也导致了检索的时间需要更多
• InnoDB
  数据和索引以文件的方式同时存放在一起，使用.idb作为后缀。
  InnoDB拥有一个自适应哈希索引的特性，当某些索引使用的非常频繁时，InnoDB会自动在基于B+树上创建一个哈希索引，使得这些热点索引的查找会变得非常之快（对用户是无感的，但是可以手动关闭该功能）。
  索引在存储引擎层进行实现而不是服务器层。
  • 工作方式
    • 主键索引：存储索引和数据
    • 辅助索引：存储索引和主键值
    • 为什么辅助索引存放的是主键值而不是主键的地址呢，当主键值很大的时间，岂不是浪费空间吗？
      答：因为mysql管理空间的最小单位是页，当存放主键的数据文件的页发送分裂时，主键的地址会发生改变，这时维护辅助索引的主键地址花销太大了，但是主键的值是不会发生改变的。
    • 如果创建表的时候，没有指定主键怎么办，辅助索引又要如何工作呢?
      答：首先会去找列中为 union索引的列，如果没有union key则会使用隐含的rowid作为主键索引。
  • InnoDB的逻辑存储结构
    默认每个区的大小为1M，一个区里默认有64页，默认每页的大小为16K。
    在Normal索引中，可能经常性的存在插入一行数据时，发生页分裂，导致页数据的重组。

4.索引的特性和使用原则

• 1.索引的特性

  • 1)表的选择性
    选择性计算公式：不重复的索引值 / 数据表的记录总数 （范围在1/表总数 到 1 之间）。表的选择性越接近1，则查询效率越高，性能是最好的。

    选择性低的表，建立索引的意义不大。因为InnoDB的B+树是顺序的，如果选择性低，则表示重复检率高，使得很多地方的检索看起来像是链表，几乎等于在链表中检索，约等于全表扫描。

  • 2)联合索引最左匹配
    使用索引时，查询的条件必须根据建立索引的条件顺序去逐一满足，不能够中断，不能够只写后面的索引，这样索引才会生效。

    假如使用联合索引创建了index(a,b,c)，相当于创建了index(a)，index(a,b)，index(a,b,c)，无需再单独创建a或b的索引。但是可以继续创建index(b,c)，因为index(b,c)并不属于index(a,b,c)的最左前缀索引。

    如果查询条件中某个列使用了范围查询，则其后续的索引都无法使用。

  • 3)索引覆盖

    1. 什么是回表
       使用辅助索引作为条件时，会先去查找辅助索引对应的B+树，并找到对应的主键值，根据主键值去主键索引对应的B+树种找到真正的数据，等于是一次查询，查找了两颗B+树，查找第二颗树的动作就叫做回表。

    2. 什么是覆盖索引？
       sql语句查询的结果的字段包含在当前满足where条件的索引里，结果中的数据可以直接在索引树中取得，使得无需回表查询主键索引树。

       为什么说查询什么数据就写什么字段，杜绝写select * 的原因？
       答：因为写select * 时，当where是辅助索引时，必须增加一次回表的数据访问，同时也增加了IO访问次数。

    3. 满足覆盖索引的索引类型
       Mysql中只有B+Tree所有支持覆盖索引。

       哈希索引、空间索引、全文索引都不存储索引列的值，也就不支持覆盖索引。

  • 4)索引条件下推
    应用条件：只适用于二级索引（辅助索引）。索引的过滤是在存储引擎层进行的，数据的过滤是在Server层进行的。

    • 场景：index(name1,name2) where name1 = ? and name2 like ?
      如果未开启索引条件下推，则在使用索引查找数据时，只会使用name1作为条件后即回表再过滤name2条件（在Server层过滤）。

      如果使用索引条件下推，则在使用索引查找数据时，使用name1和name2（使用like默认是不会进行条件过滤的，是索引条件下推起的作用）进行条件过滤后（在存储引擎层）才会回表。

      优点：减少IO操作

• 2.索引的创建原则

  1. 在用于where判断order排序和join的( on )字段上创建索引
  2. 索引的个数不要过多。
  3. 区分度低的字段,例如性别,不要建索引。
  4. 频繁更新的值,不要作为主键或者索引。
  5. 多列联合索引时，将选择性最高的列放到索引最前列。可以使用Count(索引字段)/Count(*)，来查看某个字段的选择性高低，选择性高的字段一般放在前面。
  6. 创建复合索引,而不是修改单列索引。
  7. 尽可能的做到覆盖索引（select的数据列尽可能都覆盖在索引中），减少查找了辅助索引后，根据主键值查找主键索引树，导致回表，出现二次查找
  • 为什么不建议用无序的值(例如身份证、UUID )作为索引?
    无序的值作为索引的话，写入的值是随机的，可能出现大量页的分裂，由于大量页分裂，导致页变的支离破碎，最终导致B+树出现大量碎片。
    如果使用顺序的主键插入，当出现并发插入时，会有两个问题。一：间隙锁竞争 二：自增主键的锁机制

  总结：索引虽好，但是表中的索引越多，则插入越慢。增加的新索引会导致insert,update,delete的操作速度会变慢。因为当数据产生了变更的时候，需要去维护对应字段所对应的索引树，树越多，则维护起来就越慢。

• 3.索引的使用误区

  • 独立的列
    如果查询的列不是独立的，则无法使用索引。
    eg: select user_id from user where user_id + 1 = 5
  • like条件中前面带%，或者前后都带%，无法使用索引。只有最后带%，可以使用索引
  • 索引列上使用函数（sum，count等等）（在输出列数据中使用count貌似能使用到索引）、表达式
  • 字符串不加引号，出现隐式转换
  • like条件中前面带%
  • 负向查询中可能能用到索引，可能用不到索引

• 4.分析sql语句的索引使用情况

  • 在sql语句前面加上关键字：explain