一文教你Mysql如何性能优化

news/2024/7/7 19:45:51

Mysql性能优化

Mysql性能优化

性能优化维度

在这里插入图片描述

数据库优化思路

应急调优的思路：

针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！

  1. show processlist（查看连接session状态）

  2. explain(分析查询计划)，show index from tableName（分析索引）

  3. show status like ‘%lock%’; # 查询锁状态

常规调优的思路：

针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。

  1. 开启慢查询日志，运行一天
  2. 查看slowlog，分析slowlog，分析出查询慢的语句。
  3. 按照一定优先级，进行一个一个的排查所有慢语句。
  4. 分析top sql，进行explain调试，查看语句执行时间。
  5. 调整索引或语句本身。

##优化实践

查询优化

查看sql语句执行计划Explain
作用

  1. 查看表的读取顺序
  2. 查看数据库读取操作的操作类型
  3. 查看哪些索引有可能被用到
  4. 查看哪些索引真正被用到
  5. 查看表之间的引用
  6. 查看表中有多少行记录被优化器查询

数据准备

create table t1(
  id int primary key,
  name varchar(20),
  col1 varchar(20),
  col2 varchar(20),
  col3 varchar(20)
);

create table t2(
  id int primary key,
  name varchar(20),
  col1 varchar(20),
  col2 varchar(20),
  col3 varchar(20)
);

create table t3(
  id int primary key,
  name varchar(20),
  col1 varchar(20),
  col2 varchar(20),
  col3 varchar(20)
);

insert into t1 values(1,'zs1','col1','col2','col3');
insert into t2 values(1,'zs2','col2','col2','col3');
insert into t3 values(1,'zs3','col3','col2','col3');

create index ind_t1_c1 on t1(col1);
create index ind_t2_c1 on t2(col1);
create index ind_t3_c1 on t3(col1);

create index ind_t1_c12 on t1(col1,col2);
create index ind_t2_c12 on t2(col1,col2);
create index ind_t3_c12 on t3(col1,col2);

查看执行计划

EXPLAIN SELECT * FROM t1;

类型全表扫描（效率低）
在这里插入图片描述

type:
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL

system：表(系统表)中只有一行记录, 这是const类型的特例, 基本上不会出现

const：通过索引一次查询就找到了，const用于比较primary key或者unique索引，该表最多有一个匹配行, 在查询开始时读取。由于只有一行, 因此该行中列的值可以被优化器的其余部分视为常量。const 表非常快, 因为它们只读一次。

explain select * from t1 where id=1

eq_ref：读取本表中和关联表表中的每行组合成的一行。除 了 system 和 const 类型之外, 这是最好的联接类型。当连接使用索引的所有部分时, 索引是主键或唯一非 NULL 索引时, 将使用该值。

explain select * from t1,t2 where t1.id = t2.id;

ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有符合条件的行。

explain select * from t1 where col1='zs1';

range: 只检索给定范围的行, 使用一个索引来选择行.key列显示的是真正使用了哪个索引,一般就是在where条件中使用between,>,<,in 等范围的条件,这种在索引范围内的扫描比全表扫描要好,因为它只在某个范围中扫描,不需要扫描全部的索引

explain select * from t1 where id between 1 and 10;

index: 扫描整个索引表, index 和all的区别为index类型只遍历索引树. 这通常比all快,因为索引文件通常比数据文件小,虽然index和all都是读全表,但是index是从索引中读取,而all是从硬盘中读取数据

explain select id from t1;

all: 全表扫描 ,将遍历全表以找到匹配的行

explain select * from t1;

select_type

- SIMPLE : 简单的select查询,查询中不包含子查询或者UNION

- PRIMARY: 查询中若包含复杂的子查询,最外层的查询则标记为PRIMARY

- SUBQUERY : 在SELECT或者WHERE列表中包含子查询

- DERIVED : 在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表中

- UNION: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层select被标记为:derived

- UNION RESULT: 从union表获取结果的select

索引优化

常见的索引类别

  • 普通索引
  • 唯一索引
  • 主键索引
  • 复合索引
  • 全文索引

索引存储结构

BTree索引

​ 在前面的例子中我们看见有USING BTREE，这个是什么呢？这个就是MySQL所使用的索引方案，MySQL中普遍使用B+Tree做索引，也就是BTREE。

为什么使用B+树：

​ B+树是一个多路平衡查找树，它和B树的主要区别在于:

  • B树中每个节点（叶子节点和非叶子节点）都存储真实数据。而B+树这种叶子节点存储值，非叶子节点存储键。
  • B树中一条记录只会出现一次，不会出现重复。而B+树的键可能出现重复。
  • B+树的叶子节点使用双向链表连接。

​ 基于上述特点，B+树具有如下优势：

  • 更少的IO次数：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B数多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。
  • 更适于范围查询：在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。
    -**更稳定的查询效率：**B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。

但是B+树也有其自身的缺点，因为键有可能出现重复，所以会占用更多的空间。但对于现代服务器对比性能来说，空间劣势基本都是可以接受的。

哈希索引

​ Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

特点：

  • Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
  • Hash索引无法被利用来避免数据的排序操作；
  • Hash索引不能利用部分索引键查询；
  • Hash索引在任何时候都不能避免表扫描；
  • Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+Tree索引高；

索引失效

数据准备

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
 

INSERT INTO staffs(name,age,pos,add_time) VALUES('zhangsan',18,'manager',NOW());
INSERT INTO staffs(name,age,pos,add_time) VALUES('lisi',19,'dev',NOW());
INSERT INTO staffs(name,age,pos,add_time) VALUES('wangwu',20,'dev',NOW());

SELECT * FROM staffs;

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);
  • 全值匹配 (索引idx_staffs_nameAgePos 建立索引时以 name，age，pos 的顺序建立的。全值匹配表示 按顺序匹配的
EXPLAIN SELECT * FROM staffs WHERE name = 'July';
EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND name = 'July' AND pos = 'dev';

EXPLAIN SELECT * FROM staffs WHERE age = 25;  
EXPLAIN SELECT add_time FROM staffs WHERE age = 25 AND pos = 'dev';
  • 最左前缀法则(如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。)
-- 【注意】
-- and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
-- 除开上述条件 才满足最左前缀法则。
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev'; -- 索引失效 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';-- 索引失效
  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），如果做的话，会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM staffs WHERE left(name,4) = 'July';
  • 存储引擎不能使用索引中范围条件(between、<、>、in等)右边的列(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)。
EXPLAIN SELECT * FROM staffs WHERE  name = 'July' AND age > 25 AND pos = 'dev';

  • 减少select *，使用哪些字段查哪些字段。

  • mysql5.7 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。但8.0不会。

  • mysql5.7 is not null 也无法使用索引，但是is null是可以使用索引的。但8.0不会

  • like以%开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

  • 字符串不加单引号索引失效 ( 底层进行转换使索引失效，使用了函数造成索引失效)（隐式类型转换）


http://lihuaxi.xjx100.cn/news/1280194.html

相关文章

uniapp导航栏点击切换特效 下边框跟随动态变化

uniapp导航栏点击切换特效 下边框跟随动态变化

前言 本人几率代码方便以后使用&#xff0c;导航栏下边框跟随特效 <template><view class"video"><view class"topbar"><scroll-view scroll-x"true"class"scroll-view"><view class"rel">…
阅读更多...
Three.js教程：gui.js库(可视化改变三维场景)

Three.js教程：gui.js库(可视化改变三维场景)

推荐&#xff1a;将 NSDT场景编辑器 加入你的3D工具链 其他系列工具&#xff1a; NSDT简石数字孪生 gui.js库(可视化改变三维场景) gui.js库(可视化改变三维场景) dat.gui.js说白了就是一个前端js库&#xff0c;对HTML、CSS和JavaScript进行了封装&#xff0c;学习开发的时候…
阅读更多...
【vue2】封装文字过长自动省略部分并且鼠标悬浮显示全部

【vue2】封装文字过长自动省略部分并且鼠标悬浮显示全部

技术&#xff1a;Ant design vue1.7.8 UI框架、vue2.X 需求:实现文字过长自动省略部分&#xff0c;鼠标悬浮显示全部 效果图&#xff1a; 图一&#xff1a; 图二&#xff1a; 1.封装组件代码&#xff1a; src/components/Ellipsis/index.js 文件下代码 import Ellipsis f…
阅读更多...
dsl语句查询elasticsearch集群节点分布和资源使用情况

dsl语句查询elasticsearch集群节点分布和资源使用情况

查询语句如下&#xff08;本文是直接在kibana里面执行的哦&#xff09; GET _cat/nodes?v执行结果 这样就可以很直观的看到&#xff0c;es部署在了哪些节点上&#xff0c;以及各节点资源分布使用
阅读更多...
【PostgreSQL-16新特性之普通用户的保留连接个数（reserved_connections）】

【PostgreSQL-16新特性之普通用户的保留连接个数（reserved_connections）】

PostgreSQL数据库为了保证在高并发&#xff0c;高连接数情况下某些用户能够正常连接到数据库里&#xff0c;设立了几个用户连接的保留个数。 本文介绍了PostgreSQL16版本前为超级用户保留的连接数&#xff08;superuser_reserved_connections&#xff09;以及PostgreSQL16版本…
阅读更多...
python3.10-一些有意思的语法

python3.10-一些有意思的语法

python3.10发行已经有一段时间了&#xff0c;但是时至今日才开始用上python3.10版本&#xff0c;说实话有点惭愧。下面来记录一下&#xff0c;在Python3.10版本中几个亮眼的语法变更&#xff1a; 带括号的上下文管理器 在以前&#xff0c;我们如果需要打开多个文件&#xff0…
阅读更多...
DALL-E2原理解读——大模型论文阅读笔记五

DALL-E2原理解读——大模型论文阅读笔记五

论文&#xff1a;https://cdn.openai.com/papers/dall-e-2.pdf 项目&#xff1a;https://openai.com/dall-e-2 一. 主要思想 利用CLIP提取的文本特征&#xff0c;级联式的生成图片。第一阶段通过prior将文本特征与图像特征进行对齐&#xff0c;第二阶段用扩散模型将视觉特征转…
阅读更多...
基于matlab多运动目标跟踪监测算法实现（附源码）

基于matlab多运动目标跟踪监测算法实现（附源码）

一、前言 此示例演示如何对来自固定摄像机的视频中的移动对象执行自动检测和基于运动的跟踪。 二、介绍 移动物体检测和基于运动的跟踪是许多计算机视觉应用的重要组成部分&#xff0c;包括活动识别、交通监控和汽车安全。基于运动的对象跟踪问题可以分为两部分&#xff1a; 检…
阅读更多...
最新文章

Copyright @ 2022~2023