看过来，最经典的MySQL调优之索引优化，双手奉上

最新推荐文章于 2023-06-20 09:54:01 发布

搬砖养女人

最新推荐文章于 2023-06-20 09:54:01 发布

阅读量878

点赞数

文章标签： mysql 服务器数据库

本文链接：https://blog.csdn.net/m0_68850571/article/details/124759339

版权

本文详细介绍了MySQL索引的基本知识，包括优点、分类、技术名词和匹配方式，强调了主键、唯一索引、普通索引、全文索引和组合索引的使用场景。还探讨了哈希索引的特点与限制，以及聚簇与非聚簇索引的优缺点。此外，文章深入讲解了覆盖索引的概念和优势，提供了索引优化的实践案例，包括如何避免全表扫描、使用前缀索引等技巧。最后，提到了索引监控的方法和索引优化的重要性。

摘要由CSDN通过智能技术生成

一、索引基本知识

1、索引的优点

1、减少了服务器需要扫描的数据量

2、帮助服务器避免排序和临时表

例子:

select * from emp orde by sal desc;

那么执行顺序:

所以有索引的话就不会创建零时表,临时表中,磁盘零时表比内存临时表更加的消耗性能。

3、将随机IO变成顺序IO

如何理解随机IO与顺序IO呢?如图:

这是一个磁盘,B是几何扇区(仅做标记,无特殊含义),A是磁道,C是扇区(sector,是物理读写的基本单位,通常是512字节),D由这样的多个扇区连在一起称为磁盘块(IO Block,是文件系统读写数据的最小单位，也叫磁盘簇)。扇区是磁盘最小的物理存储单元，操作系统将相邻的扇区组合在一起，形成一个块，对块进行管理。值得注意的是这是一个逻辑概念而不是物理概念。

大家都知道信息是存储在磁盘的扇区上,假如去吃旋转小火锅,上面的菜就是数据,旋转的旋转带就是磁道,夹在就是寻找菜的过程,如果旋转一圈需要四分钟,那么你吃一个菜,最短用时0min,最长4min,数学期望就是2min,如果你要吃8个菜并且不在一起(这就是随机IO),那么数学期望就是16min,如果要吃的8个菜紧挨着(顺序IO),那么数学期望就是2min,可见顺序IO能缩短寻址时间。

题外话:为什么存在磁盘块？（簇）

读取方便：由于扇区的容量比较小(512byte)，数目众多在寻址时比较困难，所以操作系统就将相邻的扇区组合在一起，形成一个块(通常4k)，再对块进行整体的操作。
分离对底层的依赖：操作系统忽略对底层物理存储结构的设计。通过虚拟出来磁盘块的概念，在系统中认为块是最小的单位。

我们平常所说的4K对齐也就是指的块大小，它表示操作系统读取磁盘时一次读取的数据大小。如果操作系统一次读取4K，但是块大小只有2K，就相当于一次IO要做2次磁盘寻址。而如果磁盘块大小刚好也是4K，那么一次IO就只需一次寻址。相对而言，磁盘寻址效率是很低的，多一次磁盘寻址肯定会更加导致IO效率低，因此对磁盘进行4K对齐也是提高了系统的IO性能。

读取数据磁盘上大概有三步:

2、索引的用处

1、快速查找匹配WHERE子句的行

2、从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引

3、如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行

4、当有表连接的时候，从其他表检索行数据

5、查找特定索引列的min或max值

6、如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组

7、在某些情况下，可以优化查询以检索值而无需查询数据行

3、索引的分类

3.1、主键索引

特殊的唯一索引,不允许有空值,例如表格中自增的id。

3.2、唯一索引

索引列值在表中唯一,在数据库表结构中对字段添加唯一索引后进行数据库进行存储操作时数据库会判断库中是否已经存在此数据，不存在此数据时才能进行插入操作。

唯一索引经常用在插入数据时,例如高并发下,如何避免插入两条同样单号的数据呢?当然是在存储数据的时候查一遍，那么怎样查找快呢？当然是创建索引，所以，在创建唯一约束的时候就创建了唯一索引。

注意的坑

添加唯一索引后有一种特殊情况，那就是如果该字段没有限制非空的话，存在插入NULL值的情况，此时，唯一索引并不起作用，也就是你可以插入n条该字段为null的数据。说白了,mysql中null != null 。

除此之外，如果插入空字符串的话，例如‘ ’ ‘ ’
不管中间是多少个空字符串在插入的时候都算作‘’ ，即空串不论多长，只能插入一条。

3.3、普通索引

其他的就不写了,提出个问题:普通索引和唯一索引哪个效率高?

3.4、全文索引

具体实现是正排索引和倒排索引,主要使用的是倒排索引,如果要在很多数据中搜查关键字,例如google搜索"huawei",如果底层用like"huawei'"的话,会非常慢,需要扫描整个数据库,为了提高搜素效率,可以建立关键字索引,属性值为文章id的数组,然后根据id确定文章位置,加权后将信息以列表形式返回。