您现在的位置是:首页 > 正文

0202性能分析-索引-MySQL

2024-02-01 06:47:06阅读 3

1 索引语法

  • 创建索引

    CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_column_name,...);
    
    • Index_name:规范为idx_表名_字段名...
  • 查看索引

    SHOW INDEX FROM table_name;
    
  • 删除索引

    DROP INDEX index_name ON table_name;
    

按照下列要求,创建索引:

  1. name字段为姓名字段,该字段值可能重复,为该字段创建索引;
  2. phone手机号字段值,要求非空且唯一,为该字段创建唯一索引;
  3. 为profession、age、status创建联合索引;
  4. 为email索引建立合适的索引来提升查询效率。

首先查看下表tb_user1当前索引,如下图1-2所示:

在这里插入图片描述

name字段建立常规索引,sql如下:

CREATE INDEX idx_tb_user1_name ON tb_user1(name);

给字段phone创建唯一索引,sql如下:

CREATE UNIQUE INDEX idx_tb_user1_phone  ON tb_user1(phone);

为profession、age、status创建联合索引,sql如下:

CREATE INDEX idx_tb_user1_pro_age_sta ON tb_user1(profession, age, phone);
  • 联合索引字段顺序由讲究
  • seq_in_index:该索引(联合索引)字段顺序

为提高查询效率,为email字段建立常规索引,sql如下:

CREATE INDEX idx_tb_user1_email ON tb_user1(email);

在此查看tb_user1表中的索引如下图1-3所示:

在这里插入图片描述

删除idx_tb_user1_email索引,sql如下:

DROP INDEX idx_tb_user1_email ON tb_user1;

2 性能分析

2.1 查看执行频次

通过如下命令,可以查看当前数据库INSERT,UPDATE,DELETE,SELECT的访问频次

SHOW GLOBAL|SESSION STATUS LIKE 'Com_______'

如下图2.1-1所示:

在这里插入图片描述

  • Com后面跟7个下划线

  • 通过该指令确认当前数据库是查询为主还是增、删或者改为主,然后针对不同类型做相应的优化。

2.2 慢查询日志

MySQL慢查询日志是MySQL数据库的一项功能,用于记录执行时间超过预设阈值的查询语句。慢查询日志可以帮助你识别数据库性能瓶颈和优化查询语句。

要启用MySQL慢查询日志,你可以按照以下步骤进行操作:

  1. 打开MySQL配置文件(通常是my.cnf或my.ini)。你可以在MySQL的安装目录中找到该文件。

  2. 在配置文件中找到[mysqld]部分,如果不存在,请添加该部分。

  3. [mysqld]部分下添加或修改以下行,以启用慢查询日志:

    slow_query_log = 1  // 启用慢查询日志
    slow_query_log_file = /path/to/slow-query.log  // 慢查询日志文件的路径和名称
    long_query_time = 1  // 查询执行时间超过多少秒将被记录到慢查询日志中
    

    注意,你需要根据实际情况设置适当的路径和时间阈值。

  4. 保存并关闭配置文件。

  5. 重启MySQL服务器,以使配置更改生效。

现在,MySQL将开始记录执行时间超过指定阈值的查询语句到慢查询日志文件中。你可以使用任何文本编辑器打开日志文件以查看其中的查询语句和执行时间。

另外,你也可以使用MySQL提供的工具来分析慢查询日志,例如mysqldumpslow和pt-query-digest。这些工具可以帮助你解析慢查询日志文件并生成汇总报告,以便更好地理解数据库性能问题。

需要注意的是,启用慢查询日志会对系统性能产生一定的影响,因为它需要记录大量查询信息。因此,在生产环境中,你可能需要谨慎使用慢查询日志功能,并根据需要进行开关控制。

示例:

Time                 Id Command    Argument
# Time: 2023-06-12T00:28:49.903565Z
# User@Host: root[root] @  [172.17.0.1]  Id:     8
# Query_time: 2.961605  Lock_time: 0.000026 Rows_sent: 1  Rows_examined: 0
use gaogzhen;
SET timestamp=1686529726;
select count(*) from tb_sku;
  • 记录当前时间、登录用户、主机、查询用时、加锁时间、查询那个数据库、时间、执行语句等
  • 慢查询日志一般在开发测试环境中使用,生成环境慎用。

2.3 profile

MySQL的profile是一种功能,用于分析查询的性能和资源消耗情况。通过启用profile,你可以获得关于每个查询的详细信息,包括执行时间、扫描的行数、使用的临时表等等。这对于优化查询和发现潜在的性能问题非常有用。

要使用MySQL的profile功能,你可以按照以下步骤进行操作:

  1. 打开MySQL客户端,以管理员或具有适当权限的用户身份登录到MySQL服务器。

  2. 在执行查询之前,使用以下命令启用profile功能:

    SET profiling = 1;
    

    这将启用profile功能,并将性能信息记录到MySQL服务器的内存中。

  3. 执行你想要分析的查询语句。

  4. 当查询完成后,使用以下命令查看profile结果:

    SHOW PROFILES;
    

    这将显示所有执行过的查询的列表,包括每个查询的标识符和执行时间。

    示例截图如下图2.3-1所示:

    在这里插入图片描述

  5. 选择你想要查看详细信息的查询,使用以下命令查看该查询的profile结果:

    SHOW PROFILE FOR QUERY <query_id>;
    

    \<query_id>替换为你要查看的查询的标识符。

    在这里插入图片描述

  6. 这将显示该查询的详细profile结果,包括每个阶段的耗时、扫描的行数、使用的临时表等。

注意,使用完profile功能后,应使用以下命令禁用profile功能,以避免对性能产生额外的开销:

SET profiling = 0;

MySQL的profile功能对于优化查询和发现性能问题非常有用,但在生产环境中使用时应谨慎,以避免对系统性能造成过大的影响。

2.4 explain

2.4.1 概述

EXPLAIN是MySQL提供的一个关键字,用于分析查询语句的执行计划。通过EXPLAIN,你可以获取关于查询语句的详细信息,包括查询的表、使用的索引、连接类型、扫描行数等等。这些信息对于优化查询和理解查询性能非常有帮助。

要使用EXPLAIN,你可以按照以下步骤进行操作:

  1. 打开MySQL客户端,以管理员或具有适当权限的用户身份登录到MySQL服务器。

  2. 在客户端中,使用以下语法来执行EXPLAIN并分析查询语句:

    EXPLAIN your_query;
    

    将"your_query"替换为你要分析的查询语句。

  3. 执行上述命令后,MySQL将返回一个关于查询执行计划的结果集,包含多列的信息,如下所示:

    • id: 查询的唯一标识符,用于区分不同的查询。
    • select_type: 查询类型,包括简单查询、联接查询、子查询等。
    • table: 查询涉及的表名。
    • partitions: 查询涉及的分区。
    • type: 表访问的类型,如全表扫描、索引扫描等。
    • possible_keys: 可能使用的索引。
    • key: 实际使用的索引。
    • key_len: 使用的索引长度。
    • ref: 列与索引之间的关联。
    • rows: 预计扫描的行数。
    • filtered: 通过条件过滤的行占比。
    • Extra: 其他额外的信息,如是否使用了临时表、使用的排序方式等。

    这些列提供了关于查询执行计划的详细信息,你可以根据这些信息来优化查询语句,例如选择更合适的索引、优化连接方式等。

通过使用EXPLAIN,你可以更好地理解查询语句的执行方式,并进行性能优化。这对于大型数据库和复杂查询尤为重要。

示例有student,course,student_course三张表,学生表与课程表直接通过学生选课表多对多关联。

2.4.2 重点解析
  • id :select 查询的序列号,表示查询中执行select子句或者操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行);

示例:

  1. 查看所有学生选课情况执行计划

    • sql语句
    explain select s.*, c.* from student s, course c, student_course sc where s.id = sc.studentid and sc.courseid = c.id;
    
    • 查询结果
    • id select_type table partitions type possible_keys key key_len ref rows filtered Extra
      1 SIMPLE s ALL PRIMARY 4 100.00
      1 SIMPLE sc ALL fk_courseid,fk_studentid 6 33.33 Using where; Using join buffer (hash join)
      1 SIMPLE c eq_ref PRIMARY PRIMARY 4 gaogzhen.sc.courseid 1 100.00
  2. 查询选修了MYSQL课程的学生信息(子查询)

    • explain select * from student s where s.id in (
      	select studentid from student_course sc where sc.courseid = (
      		select id FROM course c where c.NAME = 'MYSQL'
      	)
      );
      
    • id select_type table

      1 PRIMARY
      1 PRIMARY s
      2 MATERIALIZED sc
      3 SUBQUERY c

  • type: 表示连接类型,性能有好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。

  • 优化原则尽量向前优化;

  • NULL:不访问任何表,比如select 1;

  • system:使用系统表;

  • const:使用主键或者唯一索引;

    在这里插入图片描述

  • ref:使用非唯一索引;

    在这里插入图片描述

  • All:全表扫描,性能很低。

结语

如果小伙伴什么问题或者指教,欢迎交流。

❓QQ:806797785

参考链接:

[1]MySQL数据库视频[CP/OL].2020-04-16.p74-78.

网站文章

  • 傅里叶变换,其物理意义是什么?(转)

    1、为什么要进行傅里叶变换,其物理意义是什么?傅立叶变换是数字信号处理领域一种很重要的算法。要知道傅立叶变换算法的意义,首先要了解傅立叶原理的意义。傅立叶原理表明:任何连续测量的时序或信号,都可以表示为不同频率的正弦波信号的无限叠加。而根据该原理创立的傅立叶变换算法利用直接测量到的原始信号,以累加方式来计算该信号中不同正弦波信号的频率、振幅和相位。和傅立叶变换算法对应的是反傅立

    2024-02-01 06:46:58
  • (python)诗词排版(横排)

    完成了数据输入,其中poem存储了一首诗(无标点),n可能是5或者7,代表poem是五言诗或者七言诗。此代码可以帮助新手更加直观地认识python的作用。ju 代表的是本诗一共有多少句。利用for循环进行排版。

    2024-02-01 06:46:51
  • 轻松模拟数据库

    测试驱动的开发真是太好了! 在组织中建立它之后,您将开始: 极大地提高您的质量(事情不常发生) 大大改善您的流程(事情可以更轻松地更改) 极大地改善您的开发人员氛围(事情更有趣) 进行正确的测试驱动开发的重要性是找到要覆盖的代码的良好比例。 通过自动单元测试 通过自动集成测试 通过手动“烟雾测试” 通过手动的“验收测试” 一点也不 找到该比例可...

    2024-02-01 06:46:20
  • Ubuntu20.04 安装 ROS2

    官方文档

    2024-02-01 06:46:13
  • 计算机断网后显示配置0%,电脑断网后自动报警提醒怎么设置

    很多小伙伴经常开着电脑在互联网上下载各种资料,大型软件、高清电影等等。但是这年头有些网络运营商经常各种不靠谱,带宽偷工减料不说,还时不时断网。于是经常出现这样一幕场景:1、打开电脑附件的命令提示符窗口...

    2024-02-01 06:46:06
  • 【Leetcode刷题】哈希

    【Leetcode刷题】哈希

    Leetcode1.两数之和、Leetcode290.单词规律、Leetcode594.最长和谐子序列、Leetcode17.电话号码的字母组合、Leetcode49.字母异位词分组、Leetcode...

    2024-02-01 06:45:36
  • chatgpt赋能Python-python_crc校验

    本文由chatgpt生成,文章没有在chatgpt生成的基础上进行任何的修改。以上只是chatgpt能力的冰山一角。作为通用的Aigc大模型,只是展现它原本的实力。对于颠覆工作方式的ChatGPT,应...

    2024-02-01 06:45:28
  • 修改docker_gwbridge的网段解决和内网ip冲突的问题

    修改docker_gwbridge的网段解决和内网ip冲突的问题

    1、要删除docker_gwbridge这个网络,但是一般是无法直接删除的,因为被很多容器使用,可以通过docker network inspect docker_gwbridge查看被哪些容器占用。...

    2024-02-01 06:45:20
  • mybatis pageHelpler分页不生效解决办法

    只配置当前这一个依赖 PageHelper不生效。<!-- mybatis pager --><dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.10</version></dependency>还需要把这个依.

    2024-02-01 06:44:53
  • 两面翻转的盒子

    两面翻转的盒子

    html代码: 天官赐福 百无禁忌 一直都很喜欢这种花里胡哨的东西,这次先尝试了下文字,下次可以试试图片。 css代码:

    2024-02-01 06:44:47