

首页  思维导图  详情



Mysql性能优化

2023-05-19 15:19:02   3  举报





AI智能生成

Mysql性能优化

mysql

Mysql性能优化

性能测试

MYSQL性能调优实战

sql优化

模版推荐

作者其他创作

大纲/内容

应用层优化

增加redis或者memcached缓存

寻找mysql替代品

其他

操作系统和硬件优化

cpu

固态硬盘

内存交换区

其他

优化服务器配置

内存配置

每个连接需要的内存

为操作系统分配内存

bufferpool

innodb_buffer_pool

关闭的时候需要刷新脏页到磁盘

太大的话mysql关闭比较慢，可以改小innodb_max_dirty_pages_pct加速关闭

线程缓存

表缓存

IO配置

innodb用日志把随机io变为顺序io

innodb_log_file_size,innodb_log_files_in_group

调大日志文件大小可以显著提供写性能

innodb_log_buffer_size日志缓冲区调大可以减少io

并发配置

一次多少线程进入内核

基于工作负载的配置

优化blob和text

不能为它们创建内存临时表（不管这个字段有多小）

优化

通过subtring函数转化为string

把它们放在基于内存的文件系统（比如linux上是tmpfs）

优化排序

超过max_length_for_sort_data使用two-pass算法，否则使用single-pass算法排序

优化

single-pass：新版本使用，一次读取整行，然后排序，缺点是占用空间大

two-pass：旧版本使用，先读取排序的字段，然后读取行数据

对应blob和text用subtring函数转化为string就可以使用single-pass算法

适当调大max_length_for_sort_data值

查询优化

基础

重构查询的方式

查询优化器的局限性

查询优化器hint

优化特定类型的查询

案例

性能优化前提

投入产出比

时间监控

执行时间/等待时间

性能优化工具

show profile

show status

show processlist

explain

慢查询日志

performation_schema

iostat/lsof/tcpdump/strace/oprofile

数据类型优化

优化数据类型

整数

无符号/有符号

tinyint，smallint，mediumint，int，bigint

怎么选择

根据数据范围做选择

实数

精确类型

decimal

不精确类型

float，double

怎么选择

decimal精确、占用空间大

double，float不精确、占用空间小

乘以倍数转化decimal为bigint

字符串

varchar，char

varchar

不定长

节省存储空间

临时表按最大长度分配，占用空间大

更新效率低，适合更新频率低

char

定长

更新不易产生碎片效率高

怎么选择

varchar

字符串最大长度比平均长度大很多

列的更新很少，碎片不是问题

使用了像UTF-8复杂的字符集，每个字符都使用不同的字节存储（中文1个字符3个字节，英文1个字符1个字节，1个数字1个字节）

innodb，一个表所有的varchar字段长度不能超过65535

char

适合存储很短的字符串

经常变更的数据

长度固定的，比如：MD5

blob，text

text

字符方式存储

有字符集和排序规则

blob

二进制方式存储

没有字符集和排序规则

怎么选择

长度超过max_heap_table_size或tmp_table_size会使用磁盘临时表，所以尽量避免使用

用substring转化为字符串

日期与时间

datetime

8字节存储空间

1001年到9999年，精度为秒

与时区无关

timestamp

4字节存储空间

1970年到2038年

1970年1月1日到目前的时间戳，和unix时间戳类似

默认datetime格式化显示timestamp

默认插入的时候，没有指定第一个列的值为当前时间戳

怎么选择

如果没有多个时区操作数据，建议选择timestamp

范式与反范式

范式

优点

冗余少

更新快

表小，放入内存，执行快

缺点

通常需要关联查询，效率低

反范式

优点

避免关联，效率高

缺点

数据冗余

更新慢

混用范式与反范式

冗余重要数据，避免关联

利与排序

缓存衍生值

缓存表与汇总表

统计数据

实时计算

定期重建，影子表

索引优化

索引基础

索引类型

B+Tree

适用

全值匹配

匹配最左前缀

匹配列前缀

匹配范围值

精确匹配某一列，范围匹配另一列

只访问索引的查询

限制

左缀匹配

不能跳过索引中的列

范围查询后面的列无法适用索引

哈希索引

只有Memery引擎支持

只能精确匹配

限制

哈希索引只包含哈希值和行指针

无法排序

不支持部分索引列匹配

只支持等值比较

访问速度非常快，除非哈希冲突很多

空间索引

全文索引

其他

索引的优点

索引大大减少了服务器需要扫描的数据量

索引可以帮助服务器避免排序和临时表

索引可以将随机IO变为顺序IO

高性能索引策略

独立的列

索引列不能是表达式的一部分或者函数的参数

前缀索引和索引的选择性

公式：count(distinct left(col1,n))/count(*)

多列索引

尽量扩展索引

index_merge（索引合并）要注意是不是可以建多列索引

选择合适的索引列顺序

选择性高的列放到最前面，但不要忽略特殊的查询的基数

要考虑order by group by distinct

聚簇索引

优点

索引和数据存储在一起，一个表只能有一个

适用覆盖索引扫描可以直接使用叶子节点的主键值

数据访问更快

缺点

插入速度严重依赖于插入顺序

更新聚簇索引的代价很高

插入或者更新主键的时候可能会产生“页分裂”

可能导致全表扫描变慢

行稀疏

由于页分裂导致数据存储不连续

覆盖索引

索引条目通常小于数据行大小，减少数据访问量，更容易缓存

顺序io

只能用B+Tree做覆盖索引

适用索引扫描排序

按索引顺序读取的速度通常比全表扫描快

排序的顺序与索引的顺序一致才可以使用索引排序

关联查询，order by的列全部引用的第一个表时，才可以利用索引排序

压缩索引

冗余和重复索引

未适用的索引

索引和锁

索引可以让查询锁定更少的行

innodb行锁效率高，占用更少内存

维护索引和表

找到并修复损坏的表

repair table

alter table

更新索引统计信息

减少索引和数据的碎片

碎片会影响查询效率

消除碎片

alter tabe重建表

optimize table

分类

行碎片

行间碎片

剩余空间碎片

案例

支持多种过滤条件

可以用and in补全索引列的条件

避免多个范围条件

优化排序

覆盖索引+延迟关联

 Collect

Get Started

mysql 性能优化

 Collect

Get Started

Mysql优化

 Collect

Get Started

MySQL性能优化

 Collect

Get Started

MYSQL_09从架构师角度全局理解Mysql性能优化





0 条评论

下一页