mysql - 索引及优化(全)
注音:理解mysql存储原理对于合理建立索引至关重要
1. 基本概念
1. 聚簇索引,给主键加的索引,数据存放在索引的叶子节点中,所以数据即索引,索引即数据。每个表只能又一个主键,一个聚簇索引。
innodb一定有一个聚簇索引,如果自己没指定,系统会给个默认的 2. 非聚簇索引,不是聚簇索引的索引。非聚簇索引的叶子结点会保存主键的值,为了命中索引后,再去查主键
3. 主键的插入顺序应该是有序插入,不然会出现叶分裂,严重影响性能,所以主键一般是自增,不重复,而且不可更新
4. 当一个非聚簇索引需要查询没有索引的字段时,它会用主键去进行二次查找聚簇索引,找到需要的字段值,这个过程称为回表
5. myisam索引存的是物理地址,innodb存的是数据或者主键
理一下逻辑:
mysql中一个数据页的大小为16kb,当数据满了,需要重新开一个新的数据页,在每个数据页中,
主键都应该是增加的(其他页的主键不能出现在本页的最大值和最小值之间,如果不是这样,innodb会自动调整,导致页分裂,从而影响性能)。
然后新开一个数据页,用来记录每个数据页的最小主键
以此类推
![](https://img2022.cnblogs.com/blog/1815864/202202/1815864-20220204173836591-1551625660.png)
下面的视频讲的非常清楚
https://www.bilibili.com/video/BV1iq4y1u7vj?p=116
https://www.bilibili.com/video/BV1iq4y1u7vj?p=117
https://www.bilibili.com/video/BV1iq4y1u7vj?p=118
2.
1. 每条记录会有多于的几个字段,其中一个是delete_mask,用来标识某条记录是否被删除。当需要删除一条数据时,innodb并不是真的把数据删了,
而是用这个字段来标识,这样保证了主键数据物理层面的连续性,而且,当我们重新插入此主键的值时,只需要把标识去掉,然后覆盖这条数据即可,而不用反复的进行页分裂
2. 行-业-区-段-表空间
3. 索引分类
从功能逻辑上说,索引主要有 4 种,分别是普通索引、唯一索引、主键索引、全文索引。 按照物理实现方式,索引可以分为 2 种:聚簇索引和非聚簇索引。 按照作用字段个数进行划分,分成单列索引和联合索引
4. 创建索引语法
CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC] UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数,分别表示唯一索引、全文索引和空间索引; INDEX 与 KEY 为同义词,两者的作用相同,用来指定创建索引; index_name 指定索引的名称,为可选参数,如果不指定,那么MySQL默认col_name为索引名; col_name 为需要创建索引的字段列,该列必须从数据表中定义的多个列中选择; length 为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度; ASC 或 DESC 指定升序或者降序的索引值存储。
5. 查看表索引
show index from 表名
6. 适合创建索引的场景(每次查询,大多数情况下只会走一个索引)
1. 字段的数值有唯一性的限制 2. 频繁作为 WHERE 查询条件的字段 3. 经常 GROUP BY 和 ORDER BY 的列 4. UPDATE、DELETE 的 WHERE 条件列 5.DISTINCT 字段需要创建索引 6. 多表 JOIN 连接操作时,创建索引注意事项 首先, 连接表的数量尽量不要超过 3 张 ,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重影响查询的效率。 其次, 对 WHERE 条件创建索引 ,因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下,没有 WHERE 条件过滤是非常可怕的。 最后, 对用于连接的字段创建索引 ,并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型,
而不能一个为 int 另一个为 varchar 类型
7. 使用列的类型小的创建索引
8. 使用字符串前缀创建索引(会对以此列进行排序时有影响,两条数据前缀一样,无法正确排序)
使用以下公式来判断取多长的前缀最为合适(一般前20个字已经能满足)
count(distinct left(列名, 索引长度))/count(*)
9. 区分度高(散列性高)的列适合作为索引
10. 使用最频繁的列放到联合索引的左侧
11. 在多个字段都要创建索引的情况下,联合索引优于单值索引
7. 不适合建立索引的场景
1. 在where中使用不到的字段,不要设置索引 2. 数据量小的表最好不要使用索引 3. 有大量重复数据的列上不要建立索引 4. 避免对经常更新的表创建过多的索引 5. 不建议用无序的值作为索引 6. 删除不再使用或者很少使用的索引 7. 不要定义冗余或重复的索引