mysql - 索引及优化（全）

注音：理解mysql存储原理对于合理建立索引至关重要

1. 基本概念

1. 聚簇索引，给主键加的索引，数据存放在索引的叶子节点中，所以数据即索引，索引即数据。每个表只能又一个主键，一个聚簇索引。
　　innodb一定有一个聚簇索引，如果自己没指定，系统会给个默认的
2. 非聚簇索引，不是聚簇索引的索引。非聚簇索引的叶子结点会保存主键的值，为了命中索引后，再去查主键
3. 主键的插入顺序应该是有序插入，不然会出现叶分裂，严重影响性能，所以主键一般是自增，不重复，而且不可更新
4. 当一个非聚簇索引需要查询没有索引的字段时，它会用主键去进行二次查找聚簇索引，找到需要的字段值，这个过程称为回表
5. myisam索引存的是物理地址，innodb存的是数据或者主键

理一下逻辑：
　　mysql中一个数据页的大小为16kb，当数据满了，需要重新开一个新的数据页，在每个数据页中，
　　　　主键都应该是增加的（其他页的主键不能出现在本页的最大值和最小值之间，如果不是这样，innodb会自动调整，导致页分裂，从而影响性能）。
　　然后新开一个数据页，用来记录每个数据页的最小主键
　　以此类推

 

 

下面的视频讲的非常清楚
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. 不要定义冗余或重复的索引