Day05-数据库服务索引应用

于 2024-10-09 08:00:00 发布
阅读量374 收藏 7
点赞数 17
分类专栏： # Linux85-MySQL 文章标签： 数据库 mysql dba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dws123654/article/details/142745313
版权

Day-05-数据库索引知识说明


01.数据库索引概述介绍
02.数据库索引分类介绍
03.数据库索引应用配置
04.数据库执行计划概述
05.数据库执行计划获取
06.数据库索引引用类型
07.数据库索引覆盖长度
08.数据库联合索引应用
09.数据库索引扩展信息
10.数据库索引应用总结

作业题目:数据库连表查询练习
链接: https://www.cnblogs.com/oldboy-heqing/articles/16868031.html

01 查询平均成绩大于60分的同学的学号和平均成绩

mysql> select student.sno as "学号",avg(sc.score) as "成绩平均分" from student   join sc on studeent.sno=sc.sno group by student.sno having avg(sc.score)>60;
+--------+-----------------+
| 学号   | 成绩平均分      |
+--------+-----------------+
|      1 |         69.5000 |
|      2 |         95.0000 |
|      3 |         69.5000 |
|      4 |         79.6667 |
|      6 |         83.0000 |
|      7 |         74.5000 |
|      8 |         70.0000 |
|      9 |         80.0000 |
|     10 |         96.0000 |
+--------+-----------------+
9 rows in set (0.00 sec)

02 查询所有同学的学号，姓名。选课数，总成绩

mysql> select student.sno,student.sname,count(course.cno),sum(sc.score) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno;
+-----+---------+-------------------+---------------+
| sno | sname   | count(course.cno) | sum(sc.score) |
+-----+---------+-------------------+---------------+
|   1 | zhang3  |                 2 |           139 |
|   2 | zhang4  |                 2 |           190 |
|   3 | li4     |                 2 |           139 |
|   4 | wang5   |                 3 |           239 |
|   5 | zh4     |                 1 |            40 |
|   6 | zhao4   |                 2 |           166 |
|   7 | ma6     |                 2 |           149 |
|   8 | oldboy  |                 1 |            70 |
|   9 | oldgirl |                 1 |            80 |
|  10 | oldp    |                 1 |            96 |
+-----+---------+-------------------+---------------+
10 rows in set (0.00 sec)

03 查询各科成绩最高和最低的分，以如下形式显示:课程ID，最高分，最低分

mysql> select course.cno,max(sc.score),min(sc.score) from course join sc on course.cno=sc.cno group by course.cno;
+------+---------------+---------------+
| cno  | max(sc.score) | min(sc.score) |
+------+---------------+---------------+
| 1001 |            99 |            67 |
| 1002 |            90 |            59 |
| 1003 |           100 |            40 |
+------+---------------+---------------+
3 rows in set (0.00 sec)

04 统计各位老师，所教课程的及格率

mysql> select teacher.tname,course.cname,concat(floor(count(case when sc.score>=60 then 1 end)/count(*)*100),"%") from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tno,course.cno;
+--------+--------+--------------------------------------------------------------------------+
| tname  | cname  | concat(floor(count(case when sc.score>=60 then 1 end)/count(*)*100),"%") |
+--------+--------+--------------------------------------------------------------------------+
| oldboy | linux  | 100%                                                                     |
| xiaoQ  | python | 66%                                                                      |
| xiaoA  | mysql  | 75%                                                                      |
+--------+--------+--------------------------------------------------------------------------+
3 rows in set (0.05 sec)

05 查询每门课程被选修的学生数

mysql> select course.cname,count(*) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by course.cno;
+--------+----------+
| cname  | count(*) |
+--------+----------+
| linux  |        6 |
| python |        3 |
| mysql  |        8 |
+--------+----------+
3 rows in set (0.01 sec)

06 查询出只选修了一门课程的全部学生的学号和姓名

mysql> select student.sno,student.sname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno having count(course.cno)=1;
+-----+---------+
| sno | sname   |
+-----+---------+
|   5 | zh4     |
|   8 | oldboy  |
|   9 | oldgirl |
|  10 | oldp    |
+-----+---------+
4 rows in set (0.00 sec)

07 选修课程门数超过1门的学生信息

mysql> select student.sno,student.sname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno having count(course.cno)>1;
+-----+--------+
| sno | sname  |
+-----+--------+
|   1 | zhang3 |
|   2 | zhang4 |
|   3 | li4    |
|   4 | wang5  |
|   6 | zhao4  |
|   7 | ma6    |
+-----+--------+
6 rows in set (0.00 sec)

08 统计每门课程:优秀(85分以上)，良好(70-85)，一般(60-70)，不及格(小于60)的学生列表

mysql> select course.cname as 课程名, group_concat(case when sc.score>85 then student.sname end) as 优秀, group_concat(case when sc.score>=70 and sc.score<85 then student.sname end) as 良好, group_concat(case when sc.score>=60 and sc.score<70 then student.sname end) as 一般, group_concat(case when sc.score<60 then student.sname end) as 不及格 from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by course.cno;
+-----------+-------------------+---------------------+--------+-----------+
| 课程名    | 优秀              | 良好                | 一般   | 不及格    |
+-----------+-------------------+---------------------+--------+-----------+
| linux     | li4,zhao4         | zhang3,wang5,oldboy | ma6    | NULL      |
| python    | zhang4            | NULL                | wang5  | zhang3    |
| mysql     | oldp,wang5,zhang4 | oldgirl,ma6,zhao4   | NULL   | zh4,li4   |
+-----------+-------------------+---------------------+--------+-----------+
3 rows in set (0.00 sec)

09 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

mysql> select student.sno,student.sname,avg(sc.score) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno having avg(sc.score)>85;
+-----+--------+---------------+
| sno | sname  | avg(sc.score) |
+-----+--------+---------------+
|   2 | zhang4 |       95.0000 |
|  10 | oldp   |       96.0000 |
+-----+--------+---------------+
2 rows in set (0.00 sec)

1、数据库索引概述介绍

  • 索引概念介绍：

索引是数据库中用来提高数据读取性能的常用工具（select update delete）；

提高查询数据的性能，主要是减少对IO CPU 内存的消耗；

PS：应用索引主要在大表上（百万 千万 亿万级别）

可以简单理解：数据库索引相当于书的目录，可以借助索引有针对的查看相应数据的信息，避免了全盘检索带来的工作量；

2、数据库索引分类介绍

在MySQL数据库服务中，是有很多种索引类型的，但是比较常用的索引类型主要有：

序号类型说明
类型01B+Tree默认类型索引
类型02Hash算法类型索引
类型03R+Tree空间类型索引
类型04Fulltext全文类型索引

主要关注：B+Tree类型索引（底层应用原理）

B+Tree索引应用原理：

索引是如何演变？

遍历 – 二叉树 – Btree – B+tree

形象比喻：

节假日，抽奖互动

100个箱子，将iPhone手机放置在了其中一个箱子中：制定方案，快速找到有手机的箱子

xiaoA：学生问箱子里有没有奖品，老师如实回答 – 遍历

xiaoB：学生说箱子编号，老师根据正确箱子编号回答大小 – 二叉树 （消耗的IO不平衡）

xiaoC：

image-20240809151248003

对于B++tree算法的底层算法逻辑理解：

利用Btree算法还是快速锁定100个盒子中，有代金券的盒子编号，如下图所示：

  • 将需要存储的数据信息，均匀分配保存到对应页当中，最终数据信息的均匀存储（落盘）

  • 根据页节点存储的数据信息，取出页节节点最小数据信息，并将每个叶节点最小数据信息进行汇总整合，生成相应内部节点数据；

    实质上存储的是下层页节点的区间范围，以及与之对应的指针信息，最后构建出内部节点信息；

  • 根据内部节点存储的数据信息，取出内部节点最小数据信息，并将每个内部节点最小值信息进行汇总整合，生成相应根节点数据；

    根节点只能有占用一个页区域，如果一个页区域空间不够，需要进行内部节点层次扩展，但是尽量要保证层次越少越好；

    实质上存储的是下层内部节点的区域范围，以及与之对应的指针信息，最后构建出独立且唯一的根节点信息；

  • 整个树形结构，越向上节点存储数据的范围越大，然后依次再分发数据到下面的小范围，最终形成多叉树；

    由于出现了多叉树，就表示全部数据分布在多个链表上，避免了单条链表存储数据，同时可以实现并发的访问数据

  • 对于加号表示增强，其中增强表示在整个链表上，增加了同级相邻节点之间的双向指针，从而实现相邻节点相互跳转

根据以上B+Tree的结构说明，假设现在需要查找54这个数据值信息所在的数据页：等值查询

  • 根据定义查找的数值信息，首先在根节点中获取数值所在的区间范围和相应指针信息，从而找到下层对应的内部节点信息；
  • 根据定义查找的数据信息，其次在枝节点中获取数值所在的区域范围和相应指针信息，从而找到下层对应的叶子节点信息；
  • 根据定义查找的数据信息，最后在叶子节点中获取最终的数据信息，结果结合上图经历三步完成了数据查找（3*16=48kB）；

在利用BTree查找数据信息时，会结合树形层次结构，来决定查询数据的步骤过程，并且理论上每个数据查找过程步骤相同；

总结：B代表的平衡含义就是，每次查找数据消耗的IO数量是一致的，并且读取的页数量也是一致的，查找时间复杂度是一致的；

根据以上B+Tree的结构说明，假设现在需要查找大于90这个数据值信息所在的数据页：不等值查询

  • 根据定义查找的数值信息，首先在根节点中获取首个大于指定数值的区间范围和相应指针信息，从而找到下层对应的内部节点信息；
  • 根据定义查找的数据信息，其次在枝节点中获取数值所在的区域范围和相应指针信息，并且结合双向指针进行预读；
  • 根据定义查找的数据信息，最后在叶子节点中获取最终的数据信息，并且结合双向指针进行预读，查询其余大于90的数值；

在利用BTree查找数据信息时，由于存在双向指针概念，可以避免重复从根查找问题，减少IO消耗，结合预读快速调取数据到内存中

总结：在BTree中的双向链接增强特性和预读功能，可以根据簇（64page）读取数据，可以使数据信息的范围查找变得更加方便高效

3、数据库索引应用配置

方式一：聚簇索引（主键索引/聚合索引 --desc 表名 – PRI ）

概述：

聚簇索引主要是：将多个簇（区-64个数据页-1M）聚集在一起就构成了所谓聚簇索引，也可以称之为主键索引；

聚簇索引作用是：用来组织存储表的数据行信息的，也可以理解为数据行信息都是按照聚簇索引结构进行存储的，即按区分配空间的；

聚簇索引的存储：聚簇是多个簇，簇是多个连续数据页(64个)，页是多个连续数据块(4个)，块是多个连续扇区(8个)；

总结：利用聚簇索引可以实现从物理上或逻辑上，都能满足数据存储的连续性关系，方便进行数据查找的有序性IO；（IOT组织表）

image-20240809151317871

以上图信息为例，若显示创建ID列为pk自增列：

① 按照ID逻辑顺序，在同一个区中连续的数据页上，有序存储数据行；

② 数据行所在的数据页，作为聚簇索引的叶子节点（叶子节点就是所有数据行）；

③ 叶子节点构建完后，可以构建no-left（支节点），用于保存的是leaf节点中的ID范围和指针信息；

④ 支节点构建完后，可以构建root（根节点），用于保存的是no-leaf节点中的ID范围和指针信息；

⑤ 并且leaf节点和no-leaf相邻数据页之间都具有双向指针，从而加速数据的范围查找；

补充：

聚簇索引的构建方式

  • 数据表创建时，显示的构建了主键信息（pk），主键（pk）就是聚簇索引；
  • 数据表创建时，没有显示的构建主键信息时，会将第一个不为空的UK的列做为聚簇索引；
  • 数据表创建时，以上条件都不符合时，生成一个6字节的隐藏列作为聚簇索引；

方式二：；辅助索引（一般索引 – MUL）

概述：

辅助索引主要是：主要用于辅助聚簇索引查询的索引，一般按照业务查找条件，建立合理的索引信息，也可以称之为一般索引；

辅助索引作用是：主要是将需要查询的列信息可以和聚合索引信息建立有效的关联，从而使数据查询过程更高效，节省IO和CPU消耗

辅助索引的存储：调取需要建立的辅助索引列信息，并加上相应主键列的所有信息，存储在特定的数据页中；

总结：利用辅助索引与聚合索引建立的关联，先经过辅助索引的查询获取对应聚簇索引，在经过聚簇索引回表查询获取详细数据；
在这里插入图片描述

补充：

辅助索引的构建方式：

  • 数据表创建时，显示的构建了一般索引信息（mul），一般索引信息（mul）就是辅助索引；
  • 数据表创建时，没有显示的构建一般索引信息时，在查询检索指定数据信息，会进行全表扫描查找数据；

以上图信息为例，若显示创建name列为mul查询列：

① 调取需要建立的辅助索引列信息，并加上相应主键列的所有信息，存储在特定的内存区域中；

② 根据调取的辅助索引列信息，进行字符的顺序排序，便于形成范围查询的区间，并将排序后的数据信息存储在特定数据页中；

③ 叶子节点构建完后，可以构建no-left（支节点），用于保存的是leaf节点中的字符范围和指针信息；

④ 支节点构建完后，可以构建root（根节点），用于保存的是no-leaf节点中的字符范围和指针信息；

⑤ 找到相应辅助索引的数据信息后，在根据辅助索引与聚簇索引的对应关系，获取到相应的主键信息，从而获取相应其他数据信息在利用聚簇索引获取其他数据信息的过程，也可以称之为回表查询过程；

有了索引信息需要考虑的问题：

问题一：辅助索引检索数据产生回表问题分析：（回表次数越少越高）

什么情况回表次数会增加：

1）有些信息走了辅助索引，有些信息没有走辅助索引

解决：可以建立联合索引，调整查询条件，使辅助索引过滤出更精细主键ID信息，从而减少回表查询的次数；

2）查询的数据信息越多，越会回表查询

解决：可以在查询数据时，尽量指定显示数据的列（辅助索引列或联合索引列）

产生问题：

① 在回表过程中，有可能会出现多次的回表，从而造成磁盘IOPS的升高；（因为是随机IO操作过程）

② 在回表过程中，有可能会出现多次的回表，从而造成磁盘IO量的增加；

解决方法：

① 可以建立联合索引，调整查询条件，使辅助索引过滤出更精细主键ID信息，从而减少回表查询的次数；

② 可以控制查询信息，实现覆盖索引，辅助索引完全覆盖查询结果；

③ 优化器算法做调整？？？(MRR-多路读功能 ICP-索引下推功能 )

问题二：构建索引树高度问题分析：（索引树高度越低越好）

影响索引树高度因素：

① 数据行数量会对高度产生影响；（3层BTREE – 可以实现一般2000万行数据索引的存储-20~30列表）

​ 解决方法：可以拆分表 拆分库 或者实现分布式存储；

② 索引字段长度过大会对高度产生影响；

​ 解决方法：利用前缀索引解决问题

③ 数据类型设定会对高度产生影响；

​ 解决方法：列定义时，选择简短合适的数据类型；

索引信息创建：

  • 单列索引创建：

主键索引：

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )
 
mysql> alter table student add primary key (sno);
mysql> desc student;
mysql> show index from student;

辅助索引：

alter table 表名 add index idx_name(列名);

mysql> alter table student add index idx_sname(sname);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| sno   | int           | NO   | PRI | NULL    |       |
| sname | varchar(20)   | YES  | MUL | NULL    |       |
| sage  | tinyint       | YES  |     | NULL    |       |
| ssex  | enum('f','m') | YES  |     | m       |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY   |            1 | sno         | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_sname |            1 | sname       | A         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

唯一索引：

ALTER TABLE 表名 ADD UNIQUE (列名)；

mysql> alter table student add unique(sid);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| sno   | int           | NO   | PRI | NULL    |       |
| sname | varchar(20)   | YES  | MUL | NULL    |       |
| sage  | tinyint       | YES  |     | NULL    |       |
| ssex  | enum('f','m') | YES  |     | m       |       |
| sid   | int           | YES  | UNI | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY   |            1 | sno         | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          0 | sid       |            1 | sid         | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_sname |            1 | sname       | A         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

前缀索引：

alter table 表名 add index ix_n(列名(10));

mysql> alter table student add index idx_sid(sid(8))
mysql> show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+------
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_p
+---------+------------+-----------+--------------+-------------+-----------+-------------+------
| student |          0 | PRIMARY   |            1 | sno         | A         |          10 |     N
| student |          0 | sid       |            1 | sid         | A         |           1 |     N
| student |          1 | idx_sname |            1 | sname       | A         |          10 |     N
| student |          1 | idx_na_id |            1 | sname       | A         |          10 |     N
| student |          1 | idx_na_id |            2 | sid         | A         |          10 |     N
| student |          1 | idx_sid   |            1 | sid         | A         |           1 |      
+---------+------------+-----------+--------------+-------------+-----------+-------------+------
6 rows in set (0.00 sec)
  • 多列索引创建：
alter table 表名 add index idx_na_po(列名01,列名02,...);


mysql> alter table student add index idx_na_id(sname,sid);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| sno   | int           | NO   | PRI | NULL    |       |
| sname | varchar(20)   | YES  | MUL | NULL    |       |
| sage  | tinyint       | YES  |     | NULL    |       |
| ssex  | enum('f','m') | YES  |     | m       |       |
| sid   | int           | YES  | UNI | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY   |            1 | sno         | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          0 | sid       |            1 | sid         | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_sname |            1 | sname       | A         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_na_id |            1 | sname       | A         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_na_id |            2 | sid         | A         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.02 sec)

索引信息查看：

desc 表名;
show index from 表名;

索引信息删除：

# 删除索引信息（一般索引）
alter table 表名 drop index 索引名;  
-- 删除辅助索引

alter table 表名 drop index 索引名;    
-- 删除联合索引

alter table 表名 drop index 列名;        
-- 删除唯一索引

# 删除索引信息（聚簇索引）
alter table 表名 drop primary key;
-- 删除聚簇索引


mysql> alter table student drop primary key;
mysql> alter table student drop index sid;
mysql> alter table student drop index idx_sname;
mysql> alter table student drop index idx_na_id;
mysql> alter table student drop index idx_sid;

彩蛋：索引创建后的效果检验；

数据库压力测试方法：

# 进行数据库程序服务压测：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000 -uroot -p123456 -h192.168.30.101 -verbose

-- concurrency=100  模拟同时100会话连接；
-- iterations=1 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
-- create-schema='test'  指定操作的数据库信息；
-- query="select * from test.100w where k2='780P'"  指定压测过程具体执行了什么语句操作
-- number-of-queries=2000 指定一共做了多少次查询，总的测试查询次数(并发客户数×每客户查询次数)

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000 -uroot -p123456 -h10.0.0.51 -verbose

设置索引情况：

mysql> alter table oldboy.t100w add PRIMARY KEY(id);
mysql> alter table oldboy.t100w add index idx_k2(k2);

4、数据库执行计划概述

  • 执行计划介绍：

在介绍数据库服务程序运行逻辑时，在SQL层处理SQL语句时，会根据解析器生成解析树（多种处理方案）；

然后在利用优化器生成最终的执行计划，然后在根据最优的执行计划进行执行SQL语句；

作为管理员，可以在某个语句执行前，将语句对应的执行计划提取出来进行分析，便可大体判断语句的执行行为，从而了解执行效果；

可以简单理解：执行计划就是最优的一种执行SQL语句的方案，表示相应SQL语句是如何完成的数据查询与过滤，以及获取；

PS：通过查看执行计划，可以优化SQL语句；

5、数据库执行计划获取

可以利用命令进行获取执行计划信息：explain/desc

explain select * from oldboy.t100w where k2='VWlm';
或者
desc select * from oldboy.t100w where k2='VWlm';

输出的信息每一列有什么含义：

序号字段解释说明
01列ID表示语句执行顺序，单表查询就是一行执行计划，多表查询就会多行执行计划；
02列select_type表示语句查询类型，sipmle表示简单(普通)查询
03列table表示语句针对的表，单表查询就是一张表，多表查询显示多张表；
05列type***表示索引应用类型，通过类型可以判断有没有用索引，其次判断有没有更好的使用索引
06列possible_keys表示可能使用到的索引信息，因为列信息是可以属于多个索引的
07列key表示确认使用到的索引信息
08列key_len***表示索引覆盖长度，对联合索引是否都应用做判断
10列rows表示查询扫描的数据行数（尽量越少越好），尽量和结果集行数匹配，从而使查询代价降低
11列fltered表示查询的匹配度
12列Extra***表示额外的情况或额外的信息

索引应用情况：

利用类型信息，来判断确认索引的扫描方式，常见的索引扫描方式类型：

序号类型解释说明
01ALL - ok表示全表扫描方式，没用利用索引扫描类型；
02index表示全索引扫描方式，需要将索引树全部遍历，才能获取查询的信息（主键index=全表扫描）
03range表示范围索引方式，按照索引的区域范围扫描数据，获取查询的数据信息；
04ref表示辅助索引等值（常量）查询，精准定义辅助索引的查询条件
05eq_ref表示多表连接查询时，被驱动表的连接条件是主键或者唯一键时，获取的数据信息过程；
06const/system表示主键或者唯一键等值（常量）查询，精准定义索引的查询条件

索引应用情况：

情况一：ALL

表示全表扫描方式，没用利用索引扫描类型；

01 此类型出现原因：查找条件没有索引；

image-20240812145740045

02 利用了模糊查询方式（like）

此类型出现原因：查询条件不符合查询规律（like %%-只针对辅助索引，不影响主键索引-range）；

image-20240812145906876

03 查询条件使用了排除法

此类型出现原因：查询条件使用了排除法（!=/not in-只针对辅助索引，不影响主键索引）；

image-20240812150138066

情况二：index

表示全索引扫描方式，需要将索引树全部遍历，才能获取查询的信息（主键index=全表扫描）

此类型出现原因：扫描查询列设置了辅助索引信息，但是没有基于索引列设置查询条件

01 查询列信息设置了辅助索引，但是没有在条件中应用辅助索引

image-20240812151116102

情况三：range

表示范围索引方式，按照索引的区域范围扫描数据，获取查询的数据信息；

此类型出现原因：查找条件是范围信息（> < >= <= between and in or）

01 查找条件是范围信息（> < >= <= between and in or）

image-20240812151806761

特殊说明：在利用in查询数据信息时，查询效果和逻辑语句or的查询效果是一致；

02 此类型出现原因：查找条件是模糊信息（like）

image-20240812152613966

情况四：ref

表示辅助索引等值（常量）查询，精准定义辅助索引的查询条件

image-20240812153339945

情况五：eq_ref

表示多表连接查询时，被驱动表的连接条件是主键或者唯一键时，获取的数据信息过程；

此类型出现原因：被驱动表的链表条件是主键或唯一键时

image-20240812153520484

知识点补充：如何影响联表查询驱动关系

  • 当连接查询没有where条件时：

    • 左连接查询时，前面的表是驱动表，后面的表是被驱动表，右连接查询时相反；
    • 内连接查询时，哪张表的数据较少，哪张表就是驱动表

  • 当连接查询有where条件时

    • 带where条件的表是驱动表，否则是被驱动表

image-20240812154512175

说明：在没有设置比较合理索引情况下，默认选择结果集小的作为驱动表，即小表驱动大表；

但是，此时如果给city表中的population加上索引信息，查找数据的执行计划才是最优的，对应获取数据的性能是最好的；

image-20240812154814748

MySQL驱动表和被驱动表说明：https://www.cnblogs.com/oldboy666/p/16892774.html

情况六：const/system

表示主键或者唯一键等值（常量）查询，精准定义索引的查询条件

此类型出现原因：查询的数据条件是主键或唯一键，并且是精确等值查询；

image-20240812154953242

7、数据库索引覆盖长度

在执行计划列中，key_len主要用来判断联合索引覆盖长度（字节），当覆盖长度越长，就表示匹配度更高，回表查询的次数越少；

到底联合索引被覆盖了多少，是可以通过key_len计算出来；

# 联合索引设置
alter table t1 add index id_a_b_c(a列,b列,c列);

# 联合索引应用
select * from t1 where a=xx and b=xx and c=xx
100行 -- 回表100
50行  -- 回表50
10行  -- 回表10

如果全部覆盖到了：长度=a+b+c 即三个列最大预留长度的总和

最大预留长度影响因素？

  • 数据类型：
  • 字符集（GBK：中文每个字符占用2个字节，英文1个字节 /UTF-8：中文每个字符占用3个字节，英文1个字节）
  • not null 是否可以为空 name

最大预留长度计算结果：不同的数据类型

最大预留长度计算方法：

  • 数据类型：
  • 字符集（GBK：中文每个字符占用2个字节，英文1个字节 /UTF-8：中文每个字符占用3个字节，英文1个字节）
  • not null 是否可以为空

计算方式案例：

字段数据类型字符集计算结果
namechar(10)utf8mb4最大预留长度=4*10=40 10
utf8最大预留长度=3*10=30
varcher(10)utf8mb4最大预留长度=4*10=40 + 2字节 =42 (1-2字节存储字符长度信息)
utf8最大预留长度=3*10=30 + 2字节 =32 (1-2字节存储字符长度信息)
tinyintN/A最大预留长度=1（大约3位数） 2的8次方=256
intN/A最大预留长度=4（大约10位数） 2的32次方=4294967296
bigintN/A最大预留长度=8（大约20位数) 2的64次方=18446744073709551616
not nullN/A在没有设置not null时，在以上情况计算结果再+1

实例操作练习：理解key_len索引覆盖长度

创建一个测试数据表：

mysql> create database test;
Query OK, 1 row affected (0.01 sec)
# 常见测试数据表
mysql> use test;
Database changed

create table keylen (
    id int not null primary key auto_increment,
    k1 int not null,    
    k2 char(20),         
    k3 varchar(30) not null,
    k4 varchar(10)     
) charset=utf8mb4;

# 设置表中列索引信息
alter table keylen add index idx(k1,k2,k3,k4);

mysql> insert into keylen values(null,10,'xiaoQ','oldboy','shahe');
Query OK, 1 row affected (0.01 sec)

mysql> insert into keylen values(null,20,'xiaoA','oldgirl','beijing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from keylen where k1=10 and k2='xiaoQ' and k3='oldboy' and k4='shahe';
+----+----+-------+--------+-------+
| id | k1 | k2    | k3     | k4    |
+----+----+-------+--------+-------+
|  1 | 10 | xiaoQ | oldboy | shahe |
+----+----+-------+--------+-------+
1 row in set (0.00 sec)

mysql> desc select * from keylen where k1=10 and k2='xiaoQ' and k3='oldboy' and k4='shahe';
+----+-------------+--------+------------+------+---------------+------+---------+-------------------------+------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref                     | rows | filtered | Extra                    |
+----+-------------+--------+------------+------+---------------+------+---------+-------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | keylen | NULL       | ref  | idx           | idx  | 250     | const,const,const,const |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+------+---------------+------+---------+-------------------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

当四个索引信息全部覆盖，key_len数值计算结果：

# key_len计算思路
k1 = 4
k2 = 4 * 20 +1 = 81
k3 = 4 * 30 +2 = 122
k4 = 4 * 10 +2 + 1 = 43
sum = 4 + 81 + 122 + 43 = 250

# 进行校验结果
desc select * from keylen where k1=1 and k2='a' and k3='a' and k4='a';

说明：根据key_len长度数值，理想上是和联合索引的最大预留长度越匹配越好，表示索引都用上了，回表次数自然会少；

8、数据库联合索引应用

知识补充：

联合索引应用要遵循最左原则：（以索引讲解表格进行说明最左原则）

  • 建立索引的时候，最左列使用选择度高（cardinality-重复值少的列/唯一值多的列）的列
  • 执行查询的时候，一定包含索引的最左条件；

联合索引应用情况：

  • 联合索引全部覆盖
    • 需要满足最左原则；（尽量）
    • 需要定义条件信息时，将所有联合索引条件都引用；（必要）

进行实战测试环境练习，属于联合索引全覆盖情况：

实战测试01-步骤一：删除默认索引

mysql> use oldboy;
mysql> show index from t100w;
mysql> alter table t100w drop index idx_k2;
mysql> show index from t100w;
mysql> desc t100w;
-- 删除原有表中所有索引信息；

在这里插入图片描述

实战测试01-步骤二：创建测试环境

# 在不满足最左原则创建联合索引
mysql> alter table t100w add index idx(num,k1,k2);
-- 此时key_len的最大预留长度：4+1 + 2*4+1 + 4*4+1 = 31

联合索引创建情况：
在这里插入图片描述

验证索引全覆盖最大预留长度

desc select * from t100w where num=913759 and k1='ej' and k2='EFfg';

最大预留长度验证结果：

在这里插入图片描述

说明：进行联合索引全覆盖时，索引条件的应用顺序是无关的，因为优化器会自动优化索引查询条件应用顺序；

实战测试02-步骤一：获取重复数据信息

mysql> select num,count(*) from t100w group by num having count(*)>1 order by count(*) desc limit 3;
+--------+----------+
| num    | count(*) |
+--------+----------+
| 339934 |       14    |
| 614847 |       12    |
|  65003 |       12     |
+--------+----------+
3 rows in set (0.54 sec)

mysql> select * from t100w where num='339934';
+---------+--------+------+------+---------------------+
| id      | num    | k1   | k2   | dt                  |
+---------+--------+------+------+---------------------+
|  959036 | 339934 | 7X   | jkwx | 2019-08-12 11:52:47 |
|    4277 | 339934 | Ba   | NOpq | 2019-08-12 11:41:21 |
|  185265 | 339934 | BO   | 78Z0 | 2019-08-12 11:43:21 |
|  965745 | 339934 | eL   | Z0wx | 2019-08-12 11:52:52 |
|  987825 | 339934 | fs   | nomn | 2019-08-12 11:53:07 |
|  308385 | 339934 | g1   | deRS | 2019-08-12 11:44:44 |
|  223157 | 339934 | ku   | mn89 | 2019-08-12 11:43:46 |
|  138236 | 339934 | or   | UV45 | 2019-08-12 11:42:51 |
|  765105 | 339934 | rJ   | 89qr | 2019-08-12 11:50:26 |
|  478517 | 339934 | t8   | abef | 2019-08-12 11:46:49 |
|  107745 | 339934 | tZ   | noKL | 2019-08-12 11:42:31 |
|  503036 | 339934 | v3   | BCGH | 2019-08-12 11:47:07 |
|  596385 | 339934 | Yb   | PQqr | 2019-08-12 11:48:17 |
| 1000001 | 339934 | yb   | pqqs | 2022-11-12 12:41:59 |
+---------+--------+------+------+---------------------+

实战测试02-步骤二：插入新的测试数据

mysql> insert into t100w values(1000001,339934,'yb','pqqs',now());
mysql> select * from t100w where num='339934';

实战测试02-步骤三：进行范围索引全覆盖查询

mysql> select * from t100w where num=339934 and k1='yb' and k2 > 'PQqr';
mysql> desc  select * from t100w where num=339934 and k1='yb' and k2 > 'PQqr';

查询的结果信息：
在这里插入图片描述

说明：在进行联合索引全覆盖查询时，**最后一列**不是精确匹配查询，而是采取区间范围查询，也可以实现索引全覆盖查询效果；

– 联合索引每一列信息，都要精准匹配，可以实现全覆盖
– 联合索引最后一列信息，进行范围查询时，也可以实现全覆盖

  • 联合索引部分覆盖
  • 联合索引全不覆盖
我心中有一片海
关注
专栏目录
数据库原理及应用--sql server
weixin_51182789的博客
06-05 1958
菜鸟学习数据库 目录 梗概 关系数据库基础 sql se
MYSQL数据库管理与应用
qq_62197145的博客
03-14 2378
MYSQL数据库管理与应用知识
Day04-数据库服务查询语句
苦难带不走梦想
10-08 1698
课程知识内容概述:01 数据库操作数据语句 （insert update delete）02 数据库查询语句基础 （select）03 数据库查询语句进阶 （分组查询 连表查询 子查询）04 数据库获取元数据信息 （批量管理 服务监控）05 数据库索引概述介绍 （索引构建-原理）06 数据库索引分类介绍 （主键索引 辅助索引 联合索引 前缀索引 唯一键索引）
【数据库学习笔记】Day02 - 数据库系统结构
oo00Z00oo的博客
03-02 2005
【数据库学习】Day02 - 数据库系统结构 一、数据和数据模型：           现实世界的数据散乱无章，不利于人们对其进行管理和处理，因此，必须把现实世界的数据按照一定格式组织起来，方便对数据的操作和使用。在用数据库技术管理数据时，数据被按一定格式组织起来，常见的有二维表结构（即关系模型的结构），使得数据能被高效地管理和处理。 1.1 数据与信息 描述事物的符号记录称为数据。 将从数据中获得的有意义
Day01-postgresql数据库基础入门培训
苦难带不走梦想
10-03 2129
PostgreSQL【简称：PG】是加州大学伯克利分校计算机系开发的，一个开源免费的关系式数据库管理系统，经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内相对高的声誉。PostgreSQL可以运行在所有主流操作系统上，包括Linux、AIX、HP-UX、Solaris和Windows等34种平台。PostgreSQL是完全的事务安全性数据库，完整地支持外键、联合、视图、触发器和存储过程(并支持多种语言开发存储过程)。
达梦数据库DCP-索引管理
达梦数据库技术贴
08-01 1920
千里之行始于足下，梦想不付之行动，终究是纸上谈兵 经过一段时间的达梦DCP培训让学习了好多支持点。让我对达梦的理解更加深刻。 今天为大家说一说达梦数据的索引管理 索引概念 /*索引是与表相关的一种结构，它能使对应于表的SQL语句执行的更快，因为索引能快速定位数据库。 DM7索引能提供访问表的数据的更快路径，可以不用重写任何查询二使用索引，其查询结果与不使用索引是一样的，但速度更快。 达梦数据库提供了几种最常见类型的索引，对不同场景有不同的功能。 1、聚集索引：每个普通表有且只有一个聚集索引。注意：在建表的时
MXY---数据库
weixin_45836811的博客
02-04 1408
数据库 数据库：数据库就是管理数据的仓库 数据库分类： 1）关系型数据库：Orcale、MYSQL 2）非关系型数据库：Redis 关系、非关系型数据库： 1）关系型数据库，建立在紧密关系基础上，也成为传统数据库。以行和列的表形式存储数据，可以简单理解为二维表之间的数据结合。 2）非关系型数据库，建立在松散关系基础之上。 Mysql： 1）服务端：处理具体数据维护 保护磁盘 2）客户端：CRUD（增删改查） Mysql存放位置：my.ini进行默认配置 SQL语句：结构化查询语言简称SQL，是一种特殊目的的
Java面试题-数据库索引存储过程游标视图等等
慕白Lee的博客
02-19 3365
Java面试题-数据库-索引存储过程游标视图等等 Java面试题-数据库-索引存储过程游标视图等等
数据库MySQL详解
热门推荐
卓越无关环境，保持空杯心态——靡不有初，鲜克有终
07-24 18万+
全网最详细MySQL教程，2023持续更新中
day06-MySQL
qq_63763836的博客
08-31 1722
在我们讲解SpringBootWeb基础知识(请求响应案例)的时候，我们讲到在web开发中，为了应用程序职责单一，方便维护，我们一般将web应用程序分为三层，即：Controller、Service、Dao。之前我们的案例中，是这样子的请求流程：浏览器发起请求，先请求Controller；Controller接收到请求之后，调用Service进行业务逻辑处理；Service再调用Dao，Dao再解析user.xml中所存储的数据。
day05-Elasticsearch01day05-Elasticsearch01
10-25
在“day05-Elasticsearch01day05-Elasticsearch01”这个主题中，我们可以深入探讨Elasticsearch的基础概念、核心特性以及常见应用场景。 一、Elasticsearch基础 1. 数据模型：Elasticsearch基于Lucene构建，使用倒...
day05-Elasticsearch01
12-13
- **索引(Index)**：类似于数据库中的表，用于存储相似类型的数据。 - **文档(Document)**：索引中的基本存储单元，以JSON格式表示。 - **类型(Type)**：在7.x版本后被废弃，但在早期版本中，索引内部可以有多个...
10组-杨立伦-数据库设计1
08-08
综上所述，这个数据库设计涵盖了学生、班级、专业、教师、迟到和补签等核心要素，但实际应用中可能还需进一步完善，比如添加更多的业务规则和细节字段，以满足更复杂的需求。同时，考虑到数据库的性能和安全，还需要...
鲁班到家上门安装维修系统源码开发之结构功能解析
weixin_43744973的博客
10-08 912
鲁班到家上门安装维修系统的源码开发是一个复杂但富有成效的过程。通过详细的需求分析、技术选型、功能模块设计以及前后端分离开发，该系统不仅提升了服务效率，还通过数字化管理优化了用户体验。未来，该系统有望在智能家居售后服务领域发挥更加重要的作用，引领行业向更加高效、专业、透明的方向发展。
MySQL之复合查询与内外连接
zdlynj的博客
09-30 1398
前面我们讲解的mysql表的查询都是对一张表进行查询，即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中，我们往往还需要对多个表同时进行查询。我们这里使用的测试表，为雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。
ATAM需求说明-系统架构师（七十六）
ke1ying的博客
10-08 793
1体系结构权衡分析法ATAM(Architecture Trade Off Analyzer Method)是一种常见的结构权衡分析法，该框架主要关注系统的（），针对性能、安全性、可用性和可修改性，在系统开发前进行分析、评价和这种。A共享数据库的集成方式通常将应用程序的数据存储在一个共享数据库中，通过制定统一的数据库模式来处理不同应用集成需求。场景和需求的收集，架构视图和场景的实现，属性模型的构造和分析，架构评价和折中。7企业信息资源集成管理的前提是对企业（）的集成，其核心是对企业（）的集成。
World of Warcraft [CLASSIC][80][Grandel] Call to Arms: Victory in Wintergrasp
qq 117791303
10-02 350
Wintergrasp 冬拥湖 120 VS 120
同城O2O系统源码与跑腿配送平台的架构设计与开发方案详解
最新发布
meihusdk的博客
10-08 407
同城O2O系统与跑腿配送平台的开发，既是技术的挑战，也是商业机会的探索。通过合理的架构设计与清晰的开发方案，开发者可以构建出高效、稳定且易于扩展的平台，为用户提供更为便捷的服务。
思迅商云开启货号修改语句
xushugang007的博客
10-03 240
insert into t_sys_system (sys_var_id,sys_var_name,sys_var_value,is_changed,sys_ver_flag) values('itemnoisedit','货号、供应商、客户编码修改','1','否','1')use hbposv10 --分店数据库名改成： hbposv10_branch。--开启 货号、供应商、客户编码修改。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值