MySql调优概述

最新推荐文章于 2024-07-20 20:04:43 发布

王小燊oom

最新推荐文章于 2024-07-20 20:04:43 发布

阅读量169

点赞数

分类专栏： MySql 文章标签：数据库 mysql mysql优化

本文链接：https://blog.csdn.net/Ives_WangShen/article/details/109647308

版权

MySql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

本文仅作总结，复杂章节另作博文阐述，专栏文章将持续更新！

一. 性能监测

1. Profile

https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

set profiling=1
show profiles：查看所有执行语句的执行信息
show profile by query n：查看第n条语句的详细执行信息

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

all：显示所有性能信息 show profile all for query n
block io：显示块io操作的次数 show profile block io for query n
context switches：显示上下文切换次数，被动和主动 show profile context switches for query n
cpu：显示用户cpu时间、系统cpu时间 show profile cpu for query n
IPC：显示发送和接受的消息数量 show profile ipc for query n
Memory：暂未实现
page faults：显示页错误数量 show profile page faults for query n
source：显示源码中的函数名称与位置 show profile source for query n
swaps：显示swap的次数 show profile swaps for query n

2. Performance Schema

https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html
用于监控MySql Server在一个较低级别的运行环境的资源消耗、资源等待等情况
默认开启，可以通过配置文件/etc/mysql/my.cnf修改

3. 查看数据库连接

show processlist:查看mysql连接线程列表
在这里插入图片描述

二. 数据类型的优化

更小的通常更好，简单就好，尽量避免NULL

1. 整形

尽量使用满足需求的最小的数据类型

TINYINT: 8 bit
SMALLINT: 16bit
MEDIUMINT: 24bit
INT: 32bit
BIGINT: 64bit

2. 字符/字符串

https://www.cnblogs.com/Lance–blog/p/5193027.html

CHAR： CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。
VARCHAR： VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。VARCHAR类型的实际长度是它的值的实际长度+1。
TEXT
BLOB

3. datetime/timestamp

https://www.cnblogs.com/ivictor/p/5028368.html

datetime：

占用8byte
与时区无关,数据库底层时区配置对datetime无效
可以保存到毫秒
可保存时间范围大
不要使用字符串保存日期

timestamp：

4byte
时间范围1970-01-01 —— 2038-01-19
精确到秒
采用整形存储
依赖数据库设置的时区
自动更新timestamp列的值

date

3byte
可以使用日期时间函数进行日期之间的计算
范围：1000-01-01 —— 9999-12-31

4. 用枚举替代字符串

create  table enum_test (e enum('money','gun','women') not null);
insert into enum_test(e) values ('money'),('gun'),('women');

5. 特殊数据类型

a. IP

select inet_aton('1.1.1.1');
select inet_ntoa('16843009');

三. Schema优化

1. 范式和反范式合理使用

三范式：解决数据冗余

列不可拆分
不能存在传递依赖
表里的其他值必须唯一依赖主键

反范式：增加数据冗余

2. 主键的选择

代理主键：与业务无关，无意义的数字序列
自然主键：事物属性中的自然唯一标识
推荐使用代理主键：与业务解耦，容易维护；通用的键策略能够减少编写的源码数量，减少系统总体的拥有成本

3. 字符集的选择

纯latin1能表示的字符，没必要选择其他字符集，可以节省大量存储空间
如果确定不需要存放多种语言，就没必要非得使用UTF8或者其他Unicode字符集，会造成大量存储空间浪费
MySql的数据类型可以精确到字段，当需要大型数据库中存放多字节数据时，可以通过对不同表，不同字段使用不同数据类型来较大程度减少数据存储量，进而降低IO次数并提高缓存命中率

4. 存储引擎的选择

mysql默认:InnoDB
InnoDB vs MyISAM : https://blog.csdn.net/qq_35642036/article/details/82820178

	MyISAM	InnoDB
索引类型	非聚簇索引	聚簇索引
支持事务	否	是
支持表锁	是	是
支持行锁	否	是
支持外键	否	是
支持全文索引	是	是(5.6之后)
适合操作类型	大量select	大量insert、delete、update

5. 适当的数据冗余

被频繁引用且只能通过 join 多张表才能的到独立的小字段
1中的场景会造成大量不必要的IO，完全可以采用空间换时间的方式来优化，省去大量join时间。但是要保证冗余的同时，数据一致性不会被破坏，更新的同时冗余字段也会被更新

6. 适当的拆分

AKF：https://blog.csdn.net/Ives_WangShen/article/details/109139587

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

四. 执行计划

https://dev.mysql.com/doc/refman/8.0/en/explain.html
使用explain查看执行计划

explain select * from emp;

五. 通过索引优化

1. 三层B+ 树能存多少数据？

https://blog.csdn.net/qq_35590091/article/details/107361172

2. 索引基础+总结

基础
 总结

3. 专业术语

回表：普通索引先找到主键索引，再通过主键索引查找数据
覆盖索引：上述回表过程中，如果要查找的数据就是主键，那么无需回表（很多联合索引的建立，就是为了支持覆盖索引）
最左匹配原则：组合索引中，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配，案例如下：

创建了索引(a,b,c)

语句	索引是否发挥作用
where a=3	是，使用了a
where a=3 and b=5	是，使用了b
where a=3 and b=5 and c=4	是，使用了a，b，c
where a=3 or where c=4	否
where a=3 and c=4	是，使用了a
where a=3 and b>10 and c=7	是，使用了a，b
where a=3 and b like ‘%xx%’ and c=7	是，使用了a
where a=3 and b like ‘xx%’ and c=7	是，使用了a,b

索引合并：使用多个单列索引，然后将这些结果用“union或者and”来合并起来
索引下推/谓词下推：使用联合索引时，MySql Server会在存储引擎层面对相关索引依次进行筛选之后再返回 https://www.cnblogs.com/Chenjiabing/p/12600926.html
页分裂
页合并

4. 索引匹配方式

表结构如下

create table staffs(
id int primary key auto_increment,
name varchar(24) not 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 ‘员工记录表’;

创建联合索引结构如下

alter table staffs add index idx_nap(name, age, pos);

1. 全值匹配：全值匹配指的是和索引中的所有列进行匹配

explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';

2. 匹配最左前缀：只匹配前面的几列

explain select * from staffs where name = 'July' and age = '23';

explain select * from staffs where name = 'July';

3. 匹配列前缀：可以匹配某一列的值的开头部分

explain select * from staffs where name like 'J%';

注意：使用like模糊查询时，不要把%写在最前面，这样是不会使用索引的！

explain select * from staffs where name like '%y';

4. 匹配范围值：可以查找某一个范围的数据

explain select * from staffs where name > 'Mary';

5. 精确匹配某一列并范围匹配另外一列：可以查询第一列的全部和第二列的部分

explain select * from staffs where name = 'July' and age > 25;

6. 只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

5. 聚簇索引和非聚簇索引

a. 聚簇索引

不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起，eg：InnoDB
优点

可以把相关数据保存在一起
数据访问更快，因为索引和数据保存在同一个树中
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候