day64 数据库--索引

ゞ浴缸里的玫瑰 2023-10-09 11:31 17阅读 0赞

*  索引
    
     *  什么是索引
     *  为什么需要索引
     *  索引的影响
        
         *  什么样的数据应该添加索引
         *  磁盘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将要进行的操作，可以检查出语句不合理的地方。

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

转载于:https://www.cnblogs.com/lucky75/p/11209605.html