MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！_哔哩哔哩_bilibili

索引为什么产生？

MySQL的数据在磁盘中顺序存储，如果想要检索一个数据，需要按顺序去检索，效率低，而索引相当于为存储的数据建立目录，？

索引是什么？

  1. 索引是一种方便数据库快速检索数据的数据结构，是排好序的数据结构，
  2. 索引是在存储引擎中实现的。

  3. 存储引擎是数据库底层的软件组织，相当于管理数据库的接口工具，管理员的身份（之前把它给忽略掉了，吐了，以为是数据直接放在磁盘上的），如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等的实现方法。关系型数据库中数据的存储是以表的形式存储

    哪些索引

    聚簇索引、非聚簇索引、普通索引、唯一索引、联合索引
    普通索引：最基本的索引，没有任何重复性限制。
    唯一索引：在普通索引的上面加上，索引的列必须唯一的条件

    聚簇索引举例

  4. 针对主键建立的索引，叶节点存储的是一个个完整的数据记录，非页节点存储的是所有孩子节点中的索引数据的最小值，向上构建直到根节点

image.png

非聚簇索引举例

image.png

联合索引举例

联合索引在B+树上的存储结构及数据查找方式 - 掘金
image.png

聚簇索引+非聚簇索引的区别

  1. 聚簇索引就是对递增的主键进行索引，索引中存储的是完整的数据记录，
  2. 非聚簇索引是对数据记录中的非主键进行索引，存储非主键数据的位置（非主键+主键，通过主键进行二次检索定位）
  3. 聚簇索引和非聚簇索引的区别就在于叶子节点中存储的是完整的数据记录还是某个值或者地址。
  1. 聚簇索引的叶子节点存储的是数据记录，非聚簇索引的叶子节点存储的是数据位置，非聚簇索引不会影响数据表的物理存储顺序；
  2. 一个表只能有一个聚簇索引，也即只能有一种排序存储的方式，但可以有多个非聚簇索引，
  3. 使用聚簇索引，数据的查询效率高，但是对数据进行插入、删除、更新等操作，效率则比非聚簇索引低。

最左前缀原则

  1. 应用于联合索引，如果index Table (a,b,c)，则会按照建立联合索引时的顺序，在查询时，可以有(a),(a,b),(a,b,c)
  2. 优化器会对sql语句的索引顺序进行优化，比如 where b=1 and a=2，也会走索引
  3. 为什么一旦遇到范围查找，后面的就不走索引了？比如where a=3 and b>3 and c=3，这时会走(a,b)的索引，c的索引会失效，因为 对于索引条(3,4,2) (3,4,4) (3,5,1,)(3,5,2,)(3,5,4) 所有b>3的部分都会被取到，这时再去走c的索引，也没法判断哪个对，可以通过修改索引顺序index Table (a,c,b)来实现

MySQL最左匹配原则，道儿上兄弟都得知道的原则__陈哈哈的博客-CSDN博客_mysql的最左匹配原则

建立索引的原则

  1. 字段的数值有唯一性的限制（比如学生的学号ID），建立一个唯一索引
  2. 频繁在where查询中作为查询条件的字段，建立一个普通索引
  3. 频繁作为group by和order by中的条件字段，可以建立索引
  4. 既有group by，又有order by的，可以建立联合索引，先写group by后面的字段 再写order by后面的字段
  5. Update和delete中的where的查询也可以建立索引，如果一个字段经常更新，则不适合对该字段建立索引

  6. distinct的去重字段可以创建索引

    索引失效的几种情况

    索引失效的几种情况是什么？ - html中文网

  7. 筛选条件中有 or， 比如where a=1 or b=3 ，如果想使用or，同时想索引，则需要每列都加索引

  8. 联合索引中没有遵循最左前缀原则。
  9. like以%开头。比如查询where usr_name like ‘%w’。
  10. 需要类型转换。比如索引字段是usr_name=’123’可以走，但是usr_name=123就不会走。
  11. 索引列上有数学运算。 比如where id=id+1

  12. 索引列上有函数。比如where abs(id)=1

    B-树是什么

  13. 非叶节点也存储数据，本质上是平衡M叉搜索树，AVL树就是平衡二叉搜索树

image.png

B-Tree和B+Tree的区别

  1. B+Tree中叶子节点存放数据，非叶子节点只存放对下一层的索引，也即记录一个叶子节点中的最小值，

  2. B-Tree中叶子节点和非叶子节点都存放数据，本质是平衡M叉搜索树，

    InnoDB选用B+树而不是B树索引的原因

  3. B+树的查询效率更加稳定：无论查询什么数据，B+树的查询次数一定是3次相同的，而B树由于非叶子节点也存储数据，因此可能的查询次数是不等的。

  4. B+树的查询效率更高：B+树的非叶子节点存放的是下一层的索引，相较于B树的非叶子节点存放数据来说，非叶子节点能连接的下一层更多，叉路更多，因此整体结构B+树更加矮胖，高度更低

  5. B+树在范围查找上效率更高：B+树的叶子节点间都有链表连接，可以在叶子节点上，循着链表去找在范围内的数据记录，而B树则需要在叶子节点和非叶子节点上进行查找。

    MyISAM和InnoDB的索引方式的区别

    MySQL使用的是InnoDB
    两种不同的存储引擎，采用的索引方式也不一样：

  6. InnoDB由于不单独建立对于主键的索引而是和数据放在一起，其建立的针对主键的索引是聚簇索引，叶子节点中存储的是完整的数据记录，非聚簇索引是针对非主键的，不过仍要记录主键，方便通过主键对聚簇索引进行查找完整记录。

  7. MyISAM中索引和数据是分开存放的，索引的叶子节点中只存储完整数据记录的地址偏移量，因此针对主键的索引还是针对非主键的索引都是非聚簇索引，二级索引，每次查找都要进行一次回表操作。MyISAM回表速度很快，利用地址偏移量直接取数据

  8. InnoDB必须要有主键对于的聚簇索引，而MyISAM则不需要。

    其他

  9. 数据页是磁盘中数据的存放的逻辑概念，一个页中存放的是一条条数据记录；

  10. 记录在磁盘中的物理存储不是顺序存储的，而是通过数据记录的下一个指针来指向下一条记录。
  11. 数据页之间也不是连续存放的，只要保证页与页之间有双向链表