数据库索引

目录

概念：

结构：

优点：

缺点：

分类：

聚集索引（一级索引）：

概念：

Innodb中的聚集索引（一级索引）：

Innodb中的非聚集索引（二级索引）：

优点：

全文索引：

复合索引：

概念：

最左原则：

    创建索引的场景：

    不适合创建索引的场景：

        MySQL中key和index的区别：

mysql索引优化

---

概念：

索引是数据库中的一种数据结构，查询数据时可以通过索引来加快数据查询的速度。

结构：

• Innodb存储引擎使用B+树来实现。B树和B+树
• Memory存储引擎默认使用哈希索引来实现。

优点：

• 可以大大加快数据的查询速度：
  • 索引大大降低了服务器需要扫描的数据量。
  • 在使用分组和排序子句查询数据时，可以显著减少查询中分组和排序的时间。
  • ​mysql中的排序 -- order by​、mysql中的分组 -- group by
• 通过创建唯一性索引，可以保证表中每一行数据的唯一性

缺点：

• 增加了数据库的存储空间。
• 在修改、插入和删除数据时，索引也要动态维护，故在增删改数据时会花费较多的时间。

分类：

• 普通索引：
• 唯一索引：所有行的索引值均不相同。
• 主键索引：
  • 定义主键时数据库会自动创建主键索引，主键索引是唯一索引的特定类型。
  • 主键索引要求主键中的每个值都唯一。

聚集索引（一级索引）：

概念：

• 聚集索引的叶子节点存储着键值(一般为主键值)和数据库记录的物理地址。
• 在聚集索引中，表中行的物理顺序与键值(索引)的顺序相同。
• 一个表只能包含一个聚集索引，因为一个表的物理顺序只有一种情况。

Innodb中的聚集索引（一级索引）：

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

Innodb中的非聚集索引（二级索引）：

• 二级索引的叶子节点并不存储数据库记录的物理地址，而是存储的该条记录的主键值。
• 二级索引的访问需要两次查找：
  • 第一次根据二级索引的叶子节点获得对应记录的主键值。
  • 第二次根据主键值去聚集索引中查询对应记录的物理地址。 

优点：

• 与非聚集索引相比，聚集索引通常提供更快的数据访问速度。支持范围查询。

全文索引：

• 全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文索引不是简单的where条件匹配，它更类似于搜索引擎做的事情。
• 在相同的列上同时创建全文索引和基于值的B-tree索引不会有冲突，全文索引只适用于MATCH AGAINST 操作，而不适用于普通的where条件。

    

复合索引：

概念：

• 在多个列上建立的索引称为复合索引。

最左原则：

• 索引文件具有B-Tree的最左匹配特性：如果没有从复合索引的最左列开始(依次)查找，则无法使用索引，故建立复合索引时要特别注意多个列直接的顺序。
• 建立一个复合索引index_uid_name_age (bu_id, name, age) 相当于 建立了这3个索引：index_uid (bu_id)、index_uid_name (bu_id, name)、index_uid_name_age (bu_id, name, age) 
• 左模糊查询(like %str)和全模糊查询(like %str%)无法使用该列上的索引。

    
创建索引的场景：

• 需要经常查询的列上。
• 在经常需要排序(或根据范围进行查询)的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 在经常使用在WHERE子句中的列上创建索引，加快条件的判断速度。

    
不适合创建索引的场景：

• 在查询中很少使用到的列不应该创建索引。
• 取值很少的列不应该创建索引。eg：人员表中的性别列。
• text、image和bit数据类型的列不应该创建索引，因为这些列的数据量要么相当大，要么取值很少。

        
MySQL中key和index的区别：

• key有两个作用：约束的作用和索引的作用。
• index：没有约束的作用，只有一个索引的作用。
• key的约束作用举例：
  • primary key        唯一性约束
  • unique key        唯一性约束
  • foreign key        完整性约束

mysql索引优化