mysql 优化

最新推荐文章于 2022-09-26 08:56:54 发布
最新推荐文章于 2022-09-26 08:56:54 发布
阅读量190 收藏
点赞数
分类专栏： km

mysql query optimizer

mysql中有专门负责优化 select 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为窗户端请求的query提供它认为最优的执行计划（不一定是DBA认为最优的，这部分最耗费时间）

当客户端向mysql发送一条query，命令解析器模块完成请求分类，区别出是select并转发给 mysql query optimizer时，mysql query optimizer首先会对整条query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对query中的查询条件进行简化和调整，如去掉一些无用或显而易见的条件、结构调整等。然后分析query中的hint信息（如果有），看显示hint信息是否可以完全确定该query的执行计划。如果没有hint或hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据query进行写相应的计算分析，然后再得出最后的执行计划。

mysql 常见瓶颈

  1. CPU : CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
  2. IO : 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件的性能瓶颈 : top,free,iostat和vmstat来查看系统的性能状态

explain

explain可以模拟优化器执行sql查询语句，从而知道mysql是如何处理sql语句的，分析查询或表结构的性能瓶颈

可以分析：

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

测试sql

-- 用户表
CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);

-- 收货地址表
create table user_address(
	id bigint(20) not null auto_increment,
	user_id bigint(20),
	address varchar(50) not null default '',
	default_add int default 0,
	primary key (id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into user_address (user_id,address,default_add) values(1,'上海',1);
insert into user_address (user_id,address,default_add) values(1,'北京',0);
insert into user_address (user_id,address,default_add) values(1,'杭州',0);
insert into user_address (user_id,address,default_add) values(2,'上海',1);
insert into user_address (user_id,address,default_add) values(2,'郑州',0);

-- 商品表
create table goods(
	id bigint(20) not null auto_increment,
	price int not null default 0,
	gname varchar(50) not null default '',
	primary key (id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into goods(price,gname) values(23,'钢笔');
insert into goods(price,gname) values(14,'笔记本');
insert into goods(price,gname) values(2300,'电子琴');
insert into goods(price,gname) values(350,'足球');
insert into goods(price,gname) values(135,'电饭锅');

-- 订单表
CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  goods_id bigint(20) default null,
	address_id bigint(20) default null,
  PRIMARY KEY (`id`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into order_info(user_id,goods_id,address_id) values(1,1,1);
insert into order_info(user_id,goods_id,address_id) values(1,2,2);
insert into order_info(user_id,goods_id,address_id) values(1,2,3);
insert into order_info(user_id,goods_id,address_id) values(2,4,4);
insert into order_info(user_id,goods_id,address_id) values(2,4,4);
insert into order_info(user_id,goods_id,address_id) values(2,5,5);

使用方法

explain 后面跟需要分析的 sql 语句

mysql> select * from user_info;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xys  |   20 |
|  2 | a    |   21 |
|  3 | b    |   23 |
|  4 | c    |   50 |
|  5 | d    |   15 |
|  6 | e    |   20 |
|  7 | f    |   21 |
|  8 | g    |   23 |
|  9 | h    |   50 |
| 10 | i    |   15 |
+----+------+------+
10 rows in set (0.00 sec)

mysql> explain select * from user_info;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user_info | ALL  | NULL          | NULL | NULL    | NULL |   10 | NULL  |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql>

id

select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况

  1. id相同，执行顺序由上至下
explain
select
	u. name as '用户名',
	ua.address as '收货地址',
	g.gname as '商品名称'
from
	user_info u
left join user_address ua on u.id = ua.user_id
left join order_info o on ua.id = o.address_id
left join goods g on o.goods_id = g.id
where
	u.id = 1;

+--------+----------+----------+
| 用户名 | 收货地址 | 商品名称 |
+--------+----------+----------+
| xys    | 上海     | 钢笔     |
| xys    | 北京     | 笔记本   |
| xys    | 杭州     | 笔记本   |
+--------+----------+----------+

+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | const  | PRIMARY       | PRIMARY | 8       | const            |    1 |      100 | NULL                                               |
|  1 | SIMPLE      | ua    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             |    5 |      100 | Using where                                        |
|  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             |    6 |      100 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | g     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | etest.o.goods_id |    1 |      100 | NULL                                               |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------------+
  1. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    在这里插入图片描述
  2. id相同不同，同时存在

id如果相同，可以认为是一组，从上往下顺序执行
在所有主中，id只越大，优先级越高，越先执行

在这里插入图片描述

select_type

参考：https://blog.csdn.net/guangzixin/article/details/8200296
主要用于区分查询的类型，如：普通查询、联合查询、子查询等复杂查询

  1. SIMPLE
    简单的SELECT，不实用UNION或者子查询
explain select * from user where uid=1;

在这里插入图片描述

  1. PRIMARY
    表示主查询，或者是最外面的查询语句
explain select * from (select * from user where uid=1)b

在这里插入图片描述
3. UNION
第二层，在SELECT之后使用了UNION

explain select * from user where uid=1 union select * from user where uid=2

在这里插入图片描述
4. DEPENDEN UNION
UNION语句中的第二个SELECT，依赖于外部子查询

explain select * from user x where uid in (select uid from user y union select uid from user z where uid<5)

在这里插入图片描述
5. UNION RESULT
UNION的结果

explain select * from user where uid=1 union select * from user where uid=2

在这里插入图片描述
6. SUBQUERY
子查询中的第一个SELECT

explain select * from groups where gid =(select gid from user where uid=1)

在这里插入图片描述
7. DEPENDENT SUBQUERY
子查询中的第一个SELECT，取决于外面的查询

explain select * fromuser where uid in (select uid from user where uid<4)

在这里插入图片描述
8. DERIVED
导出表的SELECT（FROM子句的子查询）

explain select * from (select * from user where uid=1)b

在这里插入图片描述

table

表名

type

参考：https://blog.csdn.net/it_0101/article/details/77868939
表示表的连接类型
八种情况【5.7有14种】?：
在这里插入图片描述
从最好到最差依次是：
system>const>eq_ref>ref>range>index>All

  1. system
    表示只有一行记录（等于系统表），这是const类型的特例，平时不会出现，也可以忽略不计

  2. const
    如sql语句“WHERE user_id>=125 and sid = 377 and compact_state >= 1”当where条件中只要某一单一条件使用了主键（默认唯一索引）或者自建立的唯一索引并且参数在索引中有值（如sid=111,表中必须有sid为111的记录，若没有这么一条记录，Extra列会出现“no matching row in const table”，type为null表示为找到记录，如下图），则type为const.
    在这里插入图片描述
    优化点：若字段是数字 int bigint double 等数据类型，参数尽量不要加引号，而若是char varchar 等字符串类型，最好加上引号，如下两图对比。

其中compact_code为varchar类型，且建立了一个unique类型索引，图1可以看出，正常加上引号后，type为const(常量，效率最佳)，并且用到了建立的unique索引idx_code，rows为1（代表预估算需要扫描多少条记录，这个估算并不准确）；

图2，去掉引号后，type为all（全表扫描，效率最差），且并未用到索引，rows为135（代表估算需要扫描135条记录）
在这里插入图片描述
3. eq_ref
如图：其中a.sid为a表的主键，b表为a表的附表，compact_id对应a.sid，这种连表查询时，即先以主键a.sid查找a表记录，找到索引树上第一个值后，再去扫描b表全部，再接着第二遍…,所以a表为eq_reg(比const差一点（const是去直接找到那一条），比ref好一点（ref为普通索引，找到第一条后还要继续找）)，b表为all(全表扫描)。
在这里插入图片描述

  1. ref
    使用到普通索引（非unique）查询非范围时出现，如compact_code = “123”,compact_code为普通索引。
    在这里插入图片描述
  2. range
    如sql语句“where 字段1 >= “a” and 字段2 >= 281 and 字段3 >= “1””，只要其中一个字段使用了索引（不管是unique、normal、full text）或者主键，查询某一范围的数据时出现，（当然，这里排除了const的情况，即满足不了const出现的条件）
    在这里插入图片描述
  3. index
    情况1、只查询索引列时出现（因为索引是已经排序好的，第二图已说明，实际数据并未排序好），不管索引是什么索引
    在这里插入图片描述
    情况2、order by 主键（只能是主键，即使是其他unique索引也不行，第二图已说明）
    在这里插入图片描述
  4. All
    全表扫描，从头到尾扫描整张表查找行，效率最差

possible_keys

key

key_len

ref

rows

Extra

done

小代码2016
关注
专栏目录
Mysql优化之Zabbix分区优化
09-09
MySQL优化是提升数据库性能的关键，尤其对于Zabbix这样的监控系统来说，其性能直接影响到监控数据的收集和告警的及时性。Zabbix的数据主要存储在`history`和`trends`两个表中，随着时间的推移，这两个表会变得非常...
mysql优化笔记+资料
11-18
以下是一份详细的MySQL优化笔记，涵盖了多个方面： 一、查询优化 1. 使用索引：为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型，适用于不同的查询场景。 2. 避免全表扫描：尽量使用...
Mysql优化高级篇
m0_62866676的博客
09-19 306
数据库优化
mysql分析需要优化的语句
weixin_30649859的博客
08-26 62
要分析的sql语句是执行速度比较慢的。查找执行速度比较慢的sql语句。找到后，具体分析。 一、慢查询日志 慢查询日志是一种mysql提供的日志，记录所有执行时间超过某个时间界限的sql的语句。这个时间界限，我们可以指定。在mysql中默认没有开启慢查询，即使开启了，只会记录执行的sql语句超过10秒的语句。 1.如何开启慢查询日志 打开mysql的配置文件，window系统下是my.in...
深入MySQL优化-性能分析
追梦的小狼狗
02-26 259
MySQL Query Optimizer MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划。 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Que...
mysql优化-sql语句优化注意事项
hyy
11-17 316
1、避免SELECT * 从数据库里读出越多的数据，那么查询就会变得越慢。并且如果你的数据库服务器和 WEB 服务器是两台独立的服务器的话，这还会增加网络传输的负载。 select * from person where lname='x8RJWmQX'; select id from person where lname='x8RJWmQX'; 2、避免在where 子句中使用 != 或 ...
MySQL高级
qq_44796093的博客
09-26 462
索引（INDEX）是帮助MYSQL高效获取数据的数据结构。）本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存在磁盘上。
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL优化
iiiiiiiiiooooo的博客
03-19 1万+
MySQL优化方向： 在设计上：字段类型，存储引擎，范式 在功能上：索引，缓存，分库分表 在架构上：集群，主从复制，负载均衡，读写分离 SQL优化 1.插入优化 大量数据采用批量插入形式 事务设置手动提交，MySQL默认是自动提交，意味着每写一个SQL事务就自动提交，可能会频繁的涉及事务开始和提交，所以建议手动提交 2.order by优化 Using filesort：通过表的索引或者全表扫描，读取到满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序，所以返回的数据
MySQL优化方案参考
12-15
MySQL优化是一个涵盖多个层面的复杂任务，涉及到硬件配置、操作系统设置以及数据库本身的调整。这篇文章将主要探讨硬件层、系统层和MySQL层的优化方案。 在硬件层，优化首先从CPU开始。为了最大化CPU性能，应调整...
mysql中select优化_MySQL Select 优化
weixin_34790851的博客
01-19 671
准备：create table t(x int primary key,y int unique,z int);insert into t(x,y,z) values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7),(8,8,8),(9,9,9);情况1：select 没有用到索引explain select z from t;type...
vue3+springboot高校学生评教系统[编号：CS_82037](1)源码数据库.zip
09-23
本文介绍了使用SpringBoot作为后端框架，Vue作为前端框架，MyBatis-Plus进行持久层开 。详细描述了系统测试的目的、功能测试案例，包括登录验证和用户管理，以及数据库设计。 前端：vue3 开发工具：IDEA 或者eclipse都支持 编程语言: java 框架：springboot/ssm都支持 jdk版本:jdk1.8以上均可 数据库: mysql 版本不限 数据库工具：Navicat/SQLyog都可以
ASP源码：大家帮网精品装修招标门户网站程序网站源码，网站系统模板源码73.rar
最新发布
09-23
项目工程资源经过严格测试可直接运行成功且功能正常的情况才上传，可轻松copy复刻，拿到资料包后可轻松复现出一样的项目，本人系统开发经验充足（随意编程），有任何使用问题欢迎随时与我联系，我会及时为您解惑，提供帮助 【资源内容】：项目具体内容可查看/点击本页面下方的*资源详情*，包含完整源码+工程文件+说明（若有）等。【若无VIP，此资源可私信获取】 【本人专注IT领域】：有任何使用问题欢迎随时与我联系，我会及时解答，第一时间为您提供帮助 【附带帮助】：若还需要相关开发工具、学习资料等，我会提供帮助，提供资料，鼓励学习进步 【适合场景】：相关项目设计中，皆可应用在项目开发、毕业设计、课程设计、期末/期中/大作业、工程实训、大创等学科竞赛比赛、初期项目立项、学习/练手等方面中 可借鉴此优质项目实现复刻，也可基于此项目来扩展开发出更多功能 #注 1. 本资源仅用于开源学习和技术交流。不可商用等，一切后果由使用者承担 2. 部分字体及插图等来自网络，若是侵权请联系删除，本人不对所涉及的版权问题或内容负法律责任。收取的费用仅用于整理和收集资料耗费时间的酬劳 3. 积分资源不提供使用问题指导/解答
变压器变频器配电柜电路控制原理图CAD施工图纸设备控制图消防泵,喷淋泵电控原理图
09-23
变压器变频器配电柜电路控制原理图CAD施工图纸设备控制图消防泵,喷淋泵电控原理图
【数字信号去噪】基于matlab飞蛾扑火算法MFO-ICEEMDAN信号去躁【含Matlab源码 7598期】.zip
09-23
CSDN海神之光上传的代码均可运行，亲测可用，直接替换数据即可，适合小白； 1、代码压缩包内容 主函数：main.m； 调用函数：其他m文件；无需运行 运行结果效果图； 2、代码运行版本 Matlab 2019b或2023b；若运行有误，根据提示修改；若不会，私信博主； 3、运行操作步骤 步骤一：将所有文件放到Matlab的当前文件夹中； 步骤二：双击打开main.m文件； 步骤三：点击运行，等程序运行完得到结果； 4、仿真咨询 如需其他服务，可私信博主或扫描博客文章底部QQ名片； 4.1 博客或资源的完整代码提供 4.2 期刊或参考文献复现 4.3 Matlab程序定制 4.4 科研合作 功率谱估计： 故障诊断分析： 雷达通信：雷达LFM、MIMO、成像、定位、干扰、检测、信号分析、脉冲压缩 滤波估计：SOC估计 目标定位：WSN定位、滤波跟踪、目标定位 生物电信号：肌电信号EMG、脑电信号EEG、心电信号ECG 通信系统：DOA估计、编码译码、变分模态分解、管道泄漏、滤波器、数字信号处理+传输+分析+去噪（CEEMDAN）、数字信号调制、误码率、信号估计、DTMF、信号检测识别融合、LEACH协议、信号检测、水声通信
电影网站模板系统.rar
09-23
项目工程资源经过严格测试可直接运行成功且功能正常的情况才上传，可轻松copy复刻，拿到资料包后可轻松复现出一样的项目，本人系统开发经验充足（随意编程），有任何使用问题欢迎随时与我联系，我会及时为您解惑，提供帮助 【资源内容】：项目具体内容可查看/点击本页面下方的*资源详情*，包含完整源码+工程文件+说明（若有）等。【若无VIP，此资源可私信获取】 【本人专注IT领域】：有任何使用问题欢迎随时与我联系，我会及时解答，第一时间为您提供帮助 【附带帮助】：若还需要相关开发工具、学习资料等，我会提供帮助，提供资料，鼓励学习进步 【适合场景】：相关项目设计中，皆可应用在项目开发、毕业设计、课程设计、期末/期中/大作业、工程实训、大创等学科竞赛比赛、初期项目立项、学习/练手等方面中 可借鉴此优质项目实现复刻，也可基于此项目来扩展开发出更多功能 #注 1. 本资源仅用于开源学习和技术交流。不可商用等，一切后果由使用者承担 2. 部分字体及插图等来自网络，若是侵权请联系删除，本人不对所涉及的版权问题或内容负法律责任。收取的费用仅用于整理和收集资料耗费时间的酬劳 3. 积分资源不提供使用问题指导/解答
mariadb-11.5.2-winx64.msi
09-23
mariadb-11.5.2-winx64.msi
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值