Mysql索引

Mysql索引

常闻索引，知其然，不知其所以然，遂微究之。

数据库查询数据的瓶颈在于磁盘的IO读取，所以数据库设计索引时，需要考虑尽量减少磁盘的IO读取次数。

Mysql底层数据结构

索引的作用是用来做数据的快速检索，而快速检索的实现的本质是数据结构，通过选择不同的数据结构，实现各种数据快速检索。

1. Hash

哈希算法：也叫散列算法，就是把任意值(key)通过哈希函数变换为固定长度的key地址，通过这个地址定位具体的数据结构。

举例:从用户表中查询id为7的用户信息,select * from user where id = 7

hash算法的执行过程是：计算存储 id=7 的数据的物理地址 addr=hash(7)=4231，而 4231 映射的物理地址是0x77，0x77 就是 id=7 存储的数据的物理地址。

弊端：

1. 碰撞问题，哈希函数，对不同的key，可能计算出相同的结果，导致不同的key映射到了相同的数据；
2. 不适用范围查找，select * from user where id >3,采用哈希结构会把数据一次性加载到内存，再筛选范围内数据；

2. 二叉树(BST)

​ 找到7这个值；

​ 适用于快速检索以及范围查找，但是，极端情况下会退化为链表，检索效率降低；

3. B树

​ 但是考虑到磁盘 IO 读一个数据和读 100 个数据消耗的时间基本一致,可以一次性读取多个数据

4. B+树

​ 与B树的区别：

​ B树的每个节点中存储的是数据，所以存储的数据量不多；B+树每个节点存储的是索引；

​ B+树的叶子节点，用链表按顺序串联，范围查找效率更高，Mysql索引就是用的B+树；

数据引擎

Myisam(非聚集索引) 和Innodb(聚集索引)

聚集索引建表语句：

非聚集索引建表语句：

执行结果：

执行上述两个建表语句（设置不同索引），系统出现了不同文件：

分析：

• Innodb 创建表后生成的文件有（聚集索引，索引与数据同一个文件）

• frm:创建表的语句

• idb:表里面的数据+索引文件

• Myisam 创建表后生成的文件有（非聚集索引，索引与数据，不同文件）

  • frm:创建表的语句

  • MYD:表里面的数据文件（myisam data）

  • MYI:表里面的索引文件（myisam index）

Myisam 非聚集索引

mysql建表，以主键作为key，建立主索引B+树，树的叶子节点包含主键关键字，存储了对应数据的物理地址；拿到物理地址后，再去数据文件中获取对应数据

Innodb非聚集索引

a.根据主键id作为key，建立主键索引B+树，树的叶子节点，包含主键关键字，存储了主键id对应的具体数据；

b. 如果再次为某个字段建立次级索引，该次级索引叶子节点，包含该字段关键字，存储

​ 的是对应的主键key；获取到主键key，再到主键索引树中，找到对应完整数据；

• 为什么次级所引述不和主键索引树一样存储所有数据？

  InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。

• 为什么要设置一个自增的主键？

  如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页，顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

  • 如果不设置？

    每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， mysql 在频繁的更新、删除操作，会产生碎片；含碎片比较大的表，查询效率会降低。

• Innodb如何选择一个聚集索引？

  1. 如果一个主键被定义了，那么这个主键就是作为聚集索引；
  2. 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引；
  3. 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

总结

1. Innodb中，一次级索引包含了两次查找。一次是查找次级索引自身。然后查找主键（聚集索引），相对而言，Myisam查询效率更高；

2. 较频繁的作为查询条件的字段应该创建索引；

3. 唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；

4. 更新非常频繁的字段不适合创建索引。