数据库--索引

目录

• 索引
  • 什么是索引
  • 为什么需要索引
  • 索引的影响
    • 什么样的数据应该添加索引
    • 磁盘IO
    • 索引数据结构
  • 最左匹配原则
  • 聚集索引
  • 辅助索引
  • 覆盖查询
  • 回表查询
• 语法：
• 结论：
  • 补充(查询计划，慢查询优化)

索引

什么是索引

• 可以理解为：搜索引导，索引是一个特殊的数据结构，其存储的是数据的关键信息与详细信息 的位置对应关系
• 例如：书本的目录

为什么需要索引

• 加速查询，当数据量非常大的时候，查询某一个数据是非常慢的

索引的影响

1. 不是有了索引就能加速，得看你的查询语句有没有正确使用索引

2. 索引也需要占用额外的数据空间(问题不大)

3. 添加索引后，将导致增加，删除，修改数据时变慢 (写入数据时索引也需要修改)

什么样的数据应该添加索引

本质上索引的原理是尽可能的减小搜索范围

• 查询操作较多，写入较少，并且数据量很大的数据
• 查询与写入的占比，10：1或者更大

磁盘IO

• 平均查找一个数据需要花费至少9ms，这段时间CPU会切换到其他程序，这时你的程序效率就会变低，我们要加速查询，必须要减少IO操作的次数。

索引数据结构

• b+树

  • 树根和树枝都是索引，叶子节点才是真正储存数据的，叶子的数量越多，树的层级越高，导致IO次数增加
  • 要避免这个问题，在叶子节点中尽可能储存更多的数据，应该将数据量小的字段作为索引

最左匹配原则

索引在查找时，是按照从左往右依次进行比较。如果查询语句中没有出现最左边的索引，将无法加速，而是遍历查找

聚集索引

聚集索引中包含了所有字段的值，如果你指定了主键，那么主键就是聚集索引，如果没有，那么会找一个非空且唯一的字段作为聚集索引，如果也找不着，就会自动生成一个字段作为聚集索引

聚集索引中存储了所有的数据

辅助索引

除了聚集索引以外的，其他的都是第二索引(辅助索引)

第二索引的结构不仅包含了自己的数据，还包含了主键的值，因为我们要查询的数据可能不仅仅是这个数据，还可能有别的字段，所以需要根据主键值，回到聚集索引中查询。

覆盖查询

在当前索引中包含了所有需要的字段数据时，称之为覆盖查询，如果使用的是聚集索引来查询那么一定是覆盖查询，速度快

回表查询

在当前索引找不到要的数据时，需要通过id去聚集索引中查询，速度慢于聚集索引

语法：

删除主键：

alter table 表名 drop primary key();

创建索引：

create index 索引名称 on 表名(字段名);

删除索引：

drop index 索引名称 on 表名

联合索引：

create index 索引名称 on 表名(字段名，字段名);

create index union_index on usr(id, email, name, gender);

结论：

1. 使用占用空间最小的字段来作为索引，让叶子中能够储存更多的信息，减少叶子数量，降低层级，加快速度
2. 不要在一行中储存太多的数据，例如小说，视频，如果字段太多，可以分表
3. 尽量的使用覆盖查询
4. 如果字段的重复度比较高，区分度比较低，不推荐给字段添加索引，因为树会一直往中间建立，层级很高，反而会因为数据量增加减缓速度的查找
5. 模糊匹配中，百分号尽量不要写前面
6. 不要在等号的左边做运算
   • 例如：select count(*) from usr where id * 3 = 6;也会遍历所有的记录
7. and语句会自动找一个具备索引的字段优先执行，然后在找完这个字段后再找别的字段，所以我们应该在and语句中至少包含一个具备索引的字段
8. or语句要避免使用，因为or语句会把每一个条件都进行查询，不论先后，如果要用则需保证所有字段都有索引才能加速。
9. 联合索引中，顺序应该将区分度最高的放在左边，依次排开
10. 联合索引查询时，必须保证最左边的索引出现在语句中

另外需要注意：如果要查询的数据量非常大，那么索引无法加速

不是添加了索引就能提速，需要考虑索引添加的是否合理，sql语句是否正确使用了索引

补充(查询计划，慢查询优化)

查询计划：可以明确的显示mysql将要进行的操作，可以检查出语句不合理的地方。

慢查询优化：可以设置一个最大执行时间，如果某条语句执行时间超过这个最大值，就自动记录这一条语句