Mysql性能优化
2023-05-19 15:19:02 3 举报AI智能生成
登录查看完整内容
Mysql性能优化
作者其他创作
大纲/内容
增加redis或者memcached缓存
寻找mysql替代品
其他
应用层优化
cpu
固态硬盘
内存交换区
操作系统和硬件优化
每个连接需要的内存
为操作系统分配内存
innodb_buffer_pool
太大的话mysql关闭比较慢，可以改小innodb_max_dirty_pages_pct加速关闭
关闭的时候需要刷新脏页到磁盘
bufferpool
线程缓存
表缓存
内存配置
innodb用日志把随机io变为顺序io
调大日志文件大小可以显著提供写性能
innodb_log_buffer_size日志缓冲区调大可以减少io
IO配置
一次多少线程进入内核
并发配置
不能为它们创建内存临时表（不管这个字段有多小）
通过subtring函数转化为string
把它们放在基于内存的文件系统（比如linux上是tmpfs）
优化
优化blob和text
超过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
字符串最大长度比平均长度大很多
列的更新很少，碎片不是问题
使用了像UTF-8复杂的字符集，每个字符都使用不同的字节存储（中文1个字符3个字节，英文1个字符1个字节，1个数字1个字节）
innodb，一个表所有的varchar字段长度不能超过65535
适合存储很短的字符串
经常变更的数据
长度固定的，比如：MD5
varchar，char
字符方式存储
有字符集和排序规则
text
二进制方式存储
没有字符集和排序规则
blob
长度超过max_heap_table_size或tmp_table_size会使用磁盘临时表，所以尽量避免使用
用substring转化为字符串
blob，text
字符串
8字节存储空间
1001年到9999年，精度为秒
与时区无关
datetime
4字节存储空间
1970年到2038年
1970年1月1日到目前的时间戳，和unix时间戳类似
默认datetime格式化显示timestamp
默认插入的时候，没有指定第一个列的值为当前时间戳
timestamp
如果没有多个时区操作数据，建议选择timestamp
日期与时间
优化数据类型
冗余少
更新快
表小，放入内存，执行快
优点
通常需要关联查询，效率低
缺点
范式
避免关联，效率高
数据冗余
更新慢
反范式
冗余重要数据，避免关联
利与排序
缓存衍生值
混用范式与反范式
范式与反范式
实时计算
定期重建，影子表
统计数据
缓存表与汇总表
数据类型优化
全值匹配
匹配最左前缀
匹配列前缀
匹配范围值
精确匹配某一列，范围匹配另一列
只访问索引的查询
适用
左缀匹配
不能跳过索引中的列
范围查询后面的列无法适用索引
限制
B+Tree
只有Memery引擎支持
只能精确匹配
哈希索引只包含哈希值和行指针
无法排序
不支持部分索引列匹配
只支持等值比较
访问速度非常快，除非哈希冲突很多
哈希索引
空间索引
全文索引
索引类型
索引基础
索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变为顺序IO
索引的优点
索引列不能是表达式的一部分或者函数的参数
独立的列
前缀索引和索引的选择性
尽量扩展索引
index_merge（索引合并）要注意是不是可以建多列索引
多列索引
选择性高的列放到最前面，但不要忽略特殊的查询的基数
要考虑order by group by distinct
选择合适的索引列顺序
索引和数据存储在一起，一个表只能有一个
适用覆盖索引扫描可以直接使用叶子节点的主键值
数据访问更快
插入速度严重依赖于插入顺序
更新聚簇索引的代价很高
插入或者更新主键的时候可能会产生“页分裂”
行稀疏
由于页分裂导致数据存储不连续
可能导致全表扫描变慢
聚簇索引
索引条目通常小于数据行大小，减少数据访问量，更容易缓存
顺序io
只能用B+Tree做覆盖索引
覆盖索引
按索引顺序读取的速度通常比全表扫描快
排序的顺序与索引的顺序一致才可以使用索引排序
关联查询，order by的列全部引用的第一个表时，才可以利用索引排序
适用索引扫描排序
压缩索引
冗余和重复索引
未适用的索引
innodb行锁效率高，占用更少内存
索引可以让查询锁定更少的行
索引和锁
高性能索引策略
repair table
alter table
找到并修复损坏的表
更新索引统计信息
alter tabe重建表
optimize table
消除碎片
碎片会影响查询效率
行碎片
行间碎片
剩余空间碎片
分类
减少索引和数据的碎片
维护索引和表
可以用and in补全索引列的条件
支持多种过滤条件
避免多个范围条件
覆盖索引+延迟关联
索引优化
Mysql性能优化
0 条评论
回复 删除
下一页
