MySQL进阶-----索引的语法与SQL性能分析

目录

前言

一、索引语法

1.SQL语法

2.案例演示

二、SQL性能分析

三、慢查询日志

1.开启日志

 2.测试样例

 四、profile详情

1.开启profile

2.profile测试SQL语句

五、explain详情

1.语法结构

 2.执行顺序示例(id)

3.执行性能示例(type)


前言

        本期就要来去讲解SQL索引的相关语法,已经我们之前所用的SQL语句对其进行性能分析和执行效率来去判断如何来选择合适的SQL语句。下面看正文。(上一期链接:MySQL进阶-----索引的结构与分类-CSDN博客)

一、索引语法

1.SQL语法

1.创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

 2. 查看索引

SHOW INDEX FROM table_name ;

 3. 删除索引

DROP INDEX index_name ON table_name ;

2.案例演示

先来创建一张表 tb_user ,并且查询测试数据。
create table tb_user(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1',
'6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,
'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1',
'2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,
'1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23,
'2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2',
'0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24,
'2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
'1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43,
'1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1',
'0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价',
44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43,
'1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40,
'2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,
'2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35,
'2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
'1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易',
30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51,
'2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52,
'1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19,
'1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20,
'1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29,
'1', '4', '2003-05-26 00:00:00');

这里我们先去查看这个表的索引:

show index  from tb_user;

这里可以看到索引的名字,以及索引的类型是B+tree 

数据准备好了之后,接下来,我们就来完成如下需求:
A. name 字段为姓名字段,该字段的值可能会重复,为该字段创建索引。
create index index_name on tb_user(name);

再次查看:

B. phone 手机号字段的值,是非空,且唯一的,为该字段创建唯一索引。
create unique index index_phone on tb_user(phone);

C. profession age status 创建联合索引。
create index pro_age_sta on tb_user(profession,age,status);

二、SQL性能分析

MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信
息。通过如下指令,可以查看当前数据库的 INSERT UPDATE DELETE SELECT 的访问频次:
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

Com_delete: 删除次数
Com_insert: 插入次数 
Com_select: 查询次数 
Com_update: 更新次数 
我们可以在当前数据库再执行几次查询操作,然后再次查看执行频次,看看 Com_select 参数会不会变化。

通过上述指令,我们可以查看到当前数据库到底是以查询为主,还是以增删改为主,从而为数据库优化提供参考依据。 如果是以增删改为主,我们可以考虑不对其进行索引的优化。 如果是以查询为主,那么就要考虑对数据库的索引进行优化了。
那么通过查询 SQL 的执行频次,我们就能够知道当前数据库到底是增删改为主,还是查询为主。 那假如说是以查询为主,我们又该如何定位针对于那些查询语句进行优化呢? 次数我们可以借助于慢查询日志。
接下来,我们就来介绍一下 MySQL 中的慢查询日志。

三、慢查询日志

1.开启日志

慢查询日志记录了所有执行时间超过指定参数( long_query_time ,单位:秒,默认 10 秒)的所有
SQL 语句的日志。
MySQL 的慢查询日志默认没有开启,我们可以查看一下系统变量 slow_query_log
show variables like 'slow_query_log';

如果要开启慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2

这里我们先通过vi /etc/my.cnf 指令进入到mysql里面进行对配置文件的编辑

配置完毕之后,通过以下指令重新启动 MySQL 服务器进行测试,查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log
systemctl restart mysqld

 然后,再次查看开关情况,慢查询日志就已经打开了。

 2.测试样例

A. 执行如下 SQL 语句 :
select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时
13.35sec

B. 检查慢查询日志 :
最终我们发现,在慢查询日志中,只会记录执行时间超多我们预设时间( 2s )的 SQL ,执行较快的 SQL是不会记录的。

 

这样,通过慢查询日志,就可以定位出执行效率比较低的 SQL ,从而有针对性的进行优化。

 四、profile详情

1.开启profile

show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling
参数,能够看到当前 MySQL 是否支持 profile 操作:
SELECT @@have_profiling ;

可以看到,当前 MySQL 是支持 profile 操作的,但是开关是关闭的。可以通过 set 语句在
session/global 级别开启 profiling
查看profile是否开启:
select @@profiling;

如果显示0就表示未开启,那就要去开启:

SET profiling = 1;

2.profile测试SQL语句

开关已经打开了,接下来,我们所执行的 SQL 语句,都会被 MySQL 记录,并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL 语句:
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
执行一系列的业务 SQL 的操作,然后通过如下指令查看指令的执行耗时:
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看每一条 SQL 的耗时情况 :

查看指定 SQL各个阶段的耗时情况 :

五、explain详情

1.语法结构

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序

 语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

Explain 执行计划中各个字段的含义: 

字段

含义

id

select查询的序列号,表示查询中执行select子句或者是操作表的顺序

(id相同,执行顺序从上到下;id不同,值越大,越先执行)。

select_type

表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、

UNION(UNION 中的第二个或者后面的查询语句)、

SUBQUERY(SELECT/WHERE之后包含了子查询)等

type

表示连接类型,性能由好到差的连接类型为NULL、system、const、

eq_ref、ref、range、 index、all 。

possible_key

显示可能应用在这张表上的索引,一个或多个。

key

实际使用的索引,如果为NULL,则没有使用索引。

key_len

表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 。

rows

MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的。

filtered

表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好。

 2.执行顺序示例(id)

数据准备:

# 创建学生信息表
create table students
(id   int auto_incrementprimary key,name varchar(10) null,num  varchar(20) null,constraint students_num_uindexunique (num)
);
#创建中间连接表 
create table stu_cour
(stu_id  int null,cour_id int null,constraint stu_cour_courses_id_fkforeign key (cour_id) references courses (id),constraint stu_cour_students_id_fkforeign key (stu_id) references students (id)
);
# 创建课程表
create table courses
(id     int         not nullprimary key,c_name varchar(10) null
);# 数据插入
insert into students values (1,'韩信','123451'),(2,'李白','123452'),(3,'公孙离','123453'),(4,'司马懿','123454');insert into courses values (1,'mysql'),(2,'java'),(3,'python');insert into stu_cour values (1,2),(1,3),(2,1),(2,3),(3,3);

 关系如图所示:

explain语句结果中select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)。

 (1)id相同案例:

explain select s.*,c.* from students s,stu_cour sc,courses c where s.id=sc.stu_id and c.id=sc.cour_id;

(2)id不同时案例:

explain select * from students where id=(select stu_id from stu_cour where cour_id=(select id from courses where c_name='mysql'));

3.执行性能示例(type)

(1)如果查询全部的话那就是全部扫描,结果为all

explain select * from tb_user;

(2)如果查询指定条件的话,但没有建立索引,结果如下:

explain select * from tb_user where name='韩信';

(3)如果查询有建立索引的条件的话,结果如下:

explain select * from tb_user where id=2;

 以上就是本期的全部内容,我们下次见!

分享一张壁纸:

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/290110.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

fastadmin学习04-一键crud

FastAdmin 默认内置一个 test 表,可根据表字段名、字段类型和字段注释通过一键 CRUD 自动生成。 create table fa_test (id int unsigned auto_increment comment ID primary key,user_id int(10) default 0 null…

蓝桥杯物联网遇见的重大BUG及其产生原因和解决方法

BUG列表 1、ADC的RP2显示一直为0:2、LORX_Tx发送数据乱码:3、strcmp比较char a[2] {1, 2}与“12”字符串是否相等板子会死机:4、LORA_Tx和LORA_Rx放一起会接收不到数据:5、RTC获取到静止时间:6、ADC获取RP1和RP2模拟量…

Vue 03 组件通信

Vue学习 Vue 0301 浏览器本地存储localStorageSessionStorage案例 todolist的完善 02 组件自定义事件Custom Events基本使用解绑自定义事件注意事项①② 总结案例 todolist的完善 03 全局事件总线GlobalEventBus案例 todolist的完善 04 消息的订阅与发布案例 todolist的完善 05…

网络原理-传输层-UDP报文结构

本文介绍UDP报文 有很多友友搞不清楚UDP报文的详细结构还有TCP的详细结构,所以专门分开来讲 以免弄混. 首先我们先看一下整个UDP结构,让大家有一个全方面的认识 下面我们来详细解释UDP报 16位源端口号(本机):就是2字节大小,16个二进制位. 16位目的端口号(目的机):也是2字节…

SQL Server 数据库常见提权总结

前面总结了linux和Windows的提权方式以及Mysql提权,这篇文章讲讲SQL Server数据库的提权。 目录 基础知识 权限判定 系统数据库 存储过程 常见系统存储过程 常见扩展存储过程 xp_cmdshell扩展存储过程提权 xp_dirtree写入文件提权 sp_oacreate提权 xp_re…

Linux安装redis(基于CentOS系统,Ubuntu也可参考)

前言:本文内容为实操记录,仅供参考! 一、下载并解压Redis 1、执行下面的命令下载redis:wget https://download.redis.io/releases/redis-6.2.6.tar.gz 2、解压redis:tar xzf redis-6.2.6.tar.gz 3、移动redis目录&a…

和数集团董事长唐毅一行参加香港加密峰会

2024年3月26日-27日,和数集团董事长唐毅、UM Company CEO Rubens、QUANTIX CAPITAL风险投资基金CEO Jake一行,参加了亚太地区重要的Web3会议“WOW Summit HongKong 2024”。 UM Company CEO Rubens、和数集团董事长唐毅、QUANTIX CAPITAL风险投资基金 CE…

【Java八股面试系列】中间件-Redis

目录 Redis 什么是Redis Redis解决了什么问题 Redis的实现原理 数据结构 String 常用命令 应用场景 List(列表) 常用命令 应用场景 Hash(哈希) 常用命令 应用场景 set(集合) 常见命令​编辑 应用场景 Sorted Set(有序集合) 常见命令​编辑 应用场景 数据持…

Day46:WEB攻防-注入工具SQLMAPTamper编写指纹修改高权限操作目录架构

目录 数据猜解-库表列数据&字典 权限操作-文件&命令&交互式 提交方法-POST&HEAD&JSON 绕过模块-Tamper脚本-使用&开发 分析拓展-代理&调试&指纹&风险&等级 知识点: 1、注入工具-SQLMAP-常规猜解&字典配置 2、注入…

助力低碳出行 | 基于ACM32 MCU的电动滑板车方案

前言 随着智能科技的快速发展,电动滑板车的驱动系统也得到了长足的发展。国内外的电动滑板车用电机驱动系统分为传统刷式电机和无刷电机两种类型。其中,传统的刷式电机已经逐渐被无刷电机所取代,无刷电机的性能和寿命都更出色,已成…

pandas数据保存与加载

安装操作Excel模拟数据写入编辑读取切片操作 统计 安装 pip install pandas pip install numpyExcel环境安装 pip install xlrd pip install xlwt pip install openpyxi操作Excel import pandas as pd 模拟数据 写入 import pandas as pd# 模拟需要写入的数据 dic{name:[…

【计算机网络篇】数据链路层(4.2)可靠传输的实现机制

文章目录 🍔可靠传输的实现机制⭐停止 - 等待协议🗒️注意 🔎停止 - 等待协议的信道利用率🗃️练习题 ⭐回退N帧协议🎈回退N帧协议的基本工作流程🔎无传输差错的情况🔎超时重传的情况&#x1f5…

大话设计模式之迪米特法则

迪米特法则,也称为最少知识原则(Law of Demeter),是面向对象设计中的一个重要原则,其核心思想是降低耦合度、减少对象之间的依赖关系,从而使系统更加灵活、易于维护和扩展。 根据迪米特法则,一…

【考研数学】线代跟张宇,还是李永乐?

干吗要做选择,你可以全部都要的嘛! 基础阶段选张宇,强化阶段选李永乐,这样搭配可以说是最佳了。 先来说下两位老师各自的特点,张宇老师,讲线代通俗易懂,技巧运用得溜溜的。他的课,…

samba实现linux共享文件夹

一、samba安装 sudo apt install samba 二、配置Samba 编辑Samba配置文件sudo vi /etc/samba/smb.conf 在文件末尾添加以下内容,设置一个简单的共享目录(替换path_to_share为实际的共享目录路径): [Share] path /path_to_sha…

Linux:详解TCP协议段格式

文章目录 认识TCPTCP协议段格式 本篇主要总结的是TCP协议的一些字段 认识TCP TCP协议全称是传输控制协议,也就是说是要对于数据的传输进行一个控制 以上所示的是对于TCP协议进行数据传输的一个理解过程 全双工 至此就可以对于TCP协议是全双工的来进行理解了&…

【计算机图形学】3D Implicit Transporter for Temporally Consistent Keypoint Discovery

对3D Implicit Transporter for Temporally Consistent Keypoint Discovery的简单理解 文章目录 1. 现有方法限制和文章改进2. 方法2.1 寻找时间上一致的3D特征点2.1.1 3D特征Transporter2.1.2 几何隐式解码器2.1.3 损失函数 2.2 使用一致特征点的操纵 1. 现有方法限制和文章改…

[2023] 14届

1.日期统计 题意 1.日期统计 - 蓝桥云课 (lanqiao.cn) 思路 用dfs扫 对每一个位进行限制 花了一个小时 注意把答案枚举出来 对应一下看到底对不对 code #include<iostream> #include<cstdio> #include<stack> #include<vector> #include<al…

VSCode 如何同步显示网页在手机或者平板上

首先要确保 ①电脑上安装了VsCode ②VsCode安装插件LiveServer 安装成功之后 连续按住 Alt L 、Alt O 会跳转到对应的html页面上 http://127.0.0.1:5500/....... 是这个开头的 然后打开网络 如果桌面有网上邻居的可以直接点桌面的网上邻居 进来找到WLAN这个…

为什么我的微信小程序 窗口背景色backgroundColor设置参数 无效的问题处理记录!

当我们在微信小程序 json 中设置 backgroundColor 时&#xff0c;实际在电脑的模拟器中根本看不到效果。 这是因为 backgroundColor 指的窗体背景颜色&#xff0c;而不是页面的背景颜色&#xff0c;即窗体下拉刷新或上拉加载时露出的背景。在电脑的模拟器中是看不到这个动作的…