Mysql性能优化
2023-05-19 15:19:02 3 举报AI智能生成
Mysql性能优化
作者其他创作
大纲/内容
应用层优化
增加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补全索引列的条件
避免多个范围条件
优化排序
覆盖索引+延迟关联
0 条评论
下一页
