2022-2-08 MySql高级篇-索引以及sql优化

目录

一、索引概述以及优缺点

1、概述

2、优点

3、缺点

二、设计索引

1. 一个简单的索引设计方案

 2、InnoDB索引设计方案

1、迭代一次，目录项记录的页

 2、迭代两次，多个目录项纪录的页

​3、迭代三次 ，目录项记录页的目录页

 3、B+Tree

 三、常见索引的概念

1、聚簇索引

2、二级索引（辅助索引、非聚簇索引）

 3、联合索引

4、InnoDB中B+Tree的注意事项

1、根页面位置万年不动

 2. 内节点中目录项记录的唯一性

​

3. 一个页面最少存储2条记录

 四、索引的声明与使用

1、索引的创建

1、创建表时创建索引

 2、在已经存在的表上创建索引

2、索引的删除

3、索引的设计原则

1、适合创建索引的11种情况

2、限制索引的数目

 3、不适合创建索引的7中情况

五、索引的优化与查询优化

 1、索引失效的案例

---

一、索引概述以及优缺点

1、概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法 。

2、优点

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主 要的原因。

（2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。

（3）在实现数据的 参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时， 可以提高查询速度。

（4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时 间 ，降低了CPU的消耗。

3、缺点

增加索引也有许多不利的方面，主要表现在如下几个方面：

（1）创建索引和维护索引要 耗费时间 ，并 且随着数据量的增加，所耗费的时间也会增加。 （2）索引需要占 磁盘空间 ，除了数据表占数据空间之 外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文 件更快达到最大文件尺寸。

（3）虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表 中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。 因此，选择使用索引时，需要综合考虑索引的优点和缺点。

二、设计索引

1. 一个简单的索引设计方案

我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果 我们 想快速的定位到需要查找的记录在哪些数据页中该咋办？我们可以为快速定位记录所在的数据页而 建 立一个目录 ，建这个目录必须完成下边这些事：

• 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
• 给所有的页建立一个目录项。

这里用到了一个概念：行格式 

 2、InnoDB索引设计方案

1、迭代一次，目录项记录的页

我们把前边使用到的目录项放到数据页中的样子就是这样：

 2、迭代两次，多个目录项纪录的页

3、迭代三次 ，目录项记录页的目录页

 

 

 3、B+Tree

 三、常见索引的概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集 索引称为二级索引或者辅助索引。

1、聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式（所有用户记录都存储在了叶子结点），也就是所谓的索引即数据，数据即索引，术语“聚簇”表示数据行和相邻的键值聚簇的存储在一起。

 限制：

2、二级索引（辅助索引、非聚簇索引）

概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根 据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就 是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！ 

如果将所有用户数据都存储在叶子节点，那么将会占用大量空间，每次新建一个B+树都需要拷贝数据。

小结：

 3、联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按 照 c2和c3列 的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序。
• 在记录的c2列相同的情况下，采用c3列进行排序

如图所示

• 每条目录项记录都由c2、c3、页号组成，各条记录先按照c2的值顺序排序，如果记录的c2列相同，按照c3的列排序
• B+树的叶子节点处的用户记录由c2、c3和主键c1列组成

注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意 思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

• 建立 联合索引 只会建立如上图一样的1棵B+树。
• 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

4、InnoDB中B+Tree的注意事项

1、根页面位置万年不动

 2. 内节点中目录项记录的唯一性

 如下表：

 如果二级索引中目录项记录的内容只是索引列＋页号的搭配的话，那么为c2列建立索引后的B+树应该长这样:

问题：

如果我们想新插入一行记录，其中c1、c2、c3的值分别是:9、1、 'c'，那么在修改这个为c2列建立的二级索引对应的B+树时便碰到了个大问题:由于页3中存储的目录项记录是由c2列＋页号的值构成的，页3中的两条目录项记录对应的c2值都是1，这时我们要插入的值的c2列也是1，此时我们就不知道应该插入页4还是页5中

 将主键加入二级索引

3. 一个页面最少存储2条记录

 四、索引的声明与使用

MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。

1、索引的创建

1、创建表时创建索引

如果在显示创建表时创建索引，语法如下：

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]

 2、在已经存在的表上创建索引

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

1. 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

2. 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中， CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

2、索引的删除

1. 使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

2. 使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

提示 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成 索引的所有列都被删除，则整个索引将被删除。

3、索引的设计原则

1、适合创建索引的11种情况

1.字段的数值有唯一性的限制

索引本身可以起到约束作用，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速的通过该索引来确定某条记录。例如，学生表中学号是唯一的，而姓名不是唯一的，通过学号做索引可以更快确定某个学生信息。

2.频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在 数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。

3.经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者 使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多个，或者同时出现GROUP BY和ORDER BY那么可以在这些列上建立 组合索引 。

4.UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就 能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或 删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更 新不需要对索引进行维护。

5.DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。 比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行 SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（600637 条记录，运行时间 0.683s ）：

如果我们对 student_id 创建索引，再执行 SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（600637 条记录，运行时间 0.010s ）：

你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因 为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

6. 多表 JOIN 连接操作时，创建索引注意事项

首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增 长会非常快，严重影响查询的效率。

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

7. 使用列的类型小的创建索引

8. 使用字符串前缀创建索引

假如有一个商户表，地址字段比较长，在地址字段建立前缀索引。问题是截取多长。截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字 段的散列度(选择性)会降低。

 

 引申问题：

 9.区分度高(散列性高)的列适合作为索引

例如性别字段，基数为2，只有男和女，因此，区分度比较低，不适合作为索引，但是例如id字段，可能每个值都不一样，区分度高，适合做索引

 10. 使用最频繁的列放到联合索引的左侧

例如做如下查询：

SELECT * FROM student_info
WHERE student_id = ? AND `name` = ?

student_id字段可能会被频繁使用，这样可以较少地建立一些索引。同时，由于“最左前缀原则”，可以增加联合索引的使用率。

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

在一个有GROUP BY和ORDER BY条件的sql语句中，对于单独建立两个索引，只会用到第一个，不如创建联合索引。

2、限制索引的数目

 3、不适合创建索引的7中情况

1. 在where中使用不到的字段，不要设置索引

2. 数据量小的表最好不要使用索引 

在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。设置查询时间比遍历索引的时间还要短，索引可能不会产生优化效果。

3. 有大量重复数据的列上不要建立索引

举例1：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先 访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

结论：当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引。

4.避免对经常更新的表创建过多的索引

 5.不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字 符串等。

6. 删除不再使用或者很少使用的索引

 7. 不要定义冗余或重复的索引

① 冗余索引

 例如

 ② 重复索引

五、索引的优化与查询优化

 1、索引失效的案例

1.全值匹配我最爱

对于大量数据的查询，将条件中所有字段创建联合索引，将会提高查询的效率。

2.最佳左前缀法则

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

 例如下列查询，先使用age条件，然后使用name条件

 对于下列我们建立的索引

 第三条使用不到，因为B+Tree的构建顺序是age，classId，name

 3.主键插入顺序

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录 移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量 避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入。

4.计算、函数、类型转换(自动或手动)导致索引失效 

 当没有使用函数时，会从我们构建的B+Tree中查找，但是当使用了函数，它并不作用在索引，使用explain查看时，显示作用的type为all，并且没有使用索引。你能看到如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢很多。

 5.类型转换导致索引失效

 未用到索引的情况是因为进行了一个类型转换，name字段为varchar类型。

6.范围条件右边的列索引失效

创建如下索引

执行如下的查询 结果如下

 根据字段长度发现并没有用到name字段的索引，也就是说，范围条件右边的索引，都会失效。

 结论：

 7.不等于(!= 或者<>)索引失效

当查询语句中使用不等于符号时，索引失效。

8.is null可以使用索引，is not null无法使用索引

 9.like以通配符%开头索引失效

当通配符以%开头，要进行全表扫描，无法使用B+TREE查询 

10.OR 前后存在非索引的列，索引失效

一个条件需要使用索引，而另一个不需要索引，可能查询时间大于不用索引，索引也会失效。 11.数据库和表的字符集统一使用utf8mb4

 统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不 同的 字符集 进行比较前需要进行 转换，那么就可能使用到转换的函数，会造成索引失效。

建议：

六、sql优化

1、插入优化（insert）

插入操作时主键顺序插入效率高于乱序插入

1.手动提交事务

 不一行一行插入而是手动提交事务

2.load指令

 2、主键优化

1、页分裂

如果主键是乱序插入，那么新插入的50并不会插入到页3，而是从要插入的数据页，从右往左截取50%处的一部分数据放到新的页上，然后将50插入，并改变链表指针，将新的页放在中间。

2、页合并

 3、主键优化

例如二级索引可以有多个，如果主键长度过长，会占据存储空间，且搜索效率低，因为二级索引的叶子节点存储的是主键值。

 如果乱序插入可能会出现页分裂的现象

无序，长度长，检索时消耗磁盘io

修改主键还需要变动索引结构 

 3、order by优化

mysql中有两种排序方式

 没有创建索引时都是根据第一种方式进行排序

 默认创建索引都是按照升序进行排列，如果是二级索引，对于年龄升序排，对电话降序排，那么会使用Using filesort和Using index两种。因此要对不同的sql定制不同的索引。

1、order by优化

 对于第二条，如果是select * 查询一般使用不到覆盖索引，它会失效。

4、group by优化

如果不遵循最左前缀，或者不用索引 

extra会出现Using temporary，即用到了临时表，效率低，而使用索引则是Using index，效率高 

 1、group by优化

尽量满足最左前缀法则