高性能MySQL笔记

最新推荐文章于 2024-10-13 21:08:42 发布

shaobohong

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量531

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_17613195/article/details/121958137

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

第7章分区表

7.1分区表

将数据按照一个较粗的粒度分在不同表适用于：表太大无法全部放内存、只有部分热点数据其他都是历史数据、对分区单独操作

7.1.1分区表的原理

分区表由多个底层表实现，从存储引擎角度看，它们和普通表没有区别 select：判断是否可以过滤分区再执行 insert：判断由哪个分区接收再执行 delete：判断在哪个分区再执行 update：判断数据在哪个分区，取出更新，判断更新后的数据应放哪个分区再执行

7.1.2分区表的类型

根据范围、时间间隔、键值、哈希分区

7.1.3如何使用分区表

7.1.4什么情况下会出问题

null值使分区过滤失效、插入大量数据导致选择分区成本过高、打开并锁住所有底层表成本过高、重组分区成本过高

7.1.5查询优化

7.1.6合并表

7.12查询缓存

7.12.1MySQL如何判断缓存命中

存放在一个引用表，通过哈希值引用额外消耗：查询前判断、可缓存且没在缓存中则存入缓存、更新表要设置缓存失效

7.12.2查询缓存如何使用内存

先申请一个大的数据块用于存储结果，选择一个尽可能小的数据块存储，当数据块用完还有数据未存，再选择一个数据块…完成后释放剩余空间并发操作时，会在两个查询间产生碎片

7.12.3什么情况下查询缓存能发挥作用

命中率缓存未命中：查询语句含不确定函数或结果太大、未被缓存过、内存不足缓存被逐出、查询结果还没缓存起来碎片、内存不足、数据修改都会造成缓存失效

7.12.4如何配置和维护查询缓存

减少碎片、提高查询缓存的使用率

7.12.5InnoDB和查询缓存

7.12.6通用查询缓存优化

用多个小表替代大表，使失效策略范围小点批量写入替代单条写入，这样只做一次缓存失效不在数据库或表级别控制缓存，通过sql_cache和sql_no_cache控制某个select语句是否缓存写密集的系统关闭缓存

第8章优化服务器设置

8.1 MySQL配置的工作原理

配置文件一般在/etc/my.cnf或者/etc/mysql/my.cnf

查找配置文件:

$ which mysqld
/usr/sbin/mysqld
$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /user/etc/my.cnf

8.1.1 语法、作用域和动态性

语法：配置项设置都使用小写，单词之间用下划线或横线隔开

作用域：服务器级（全局作用域）、连接级（会话作用域）、对象级

8.1.2 设置变量的副作用

8.1.3 入门

8.1.4 通过基准测试迭代优化

8.2 什么不该做

不要根据一些“比率”来调优，如命中率过低，则应增加缓存的大小。命中率跟缓存大小没关系

不要使用调优脚本

不要相信MySQL奔溃时自身输出的内存消耗公式

8.3 创建MySQL配置文件

8.3.1 检查MySQL服务器状态变量

SHOW GLOBAL STATUS查看状态

mysqladmin -u root extended-status -ri60 每隔60秒查看状态变量的增量变化，root为账号

8.4 配置内存使用

确定内存上限

确定每个连接MySQL需要多少内存，例如排序缓冲和临时表

确定操作系统需要多少内存

把剩下的全部给MySQL缓存

8.4.1 MySQL可以使用多少内存

考虑操作系统或架构的限制

8.4.2 每个连接需要的内存

8.4.3 为操作系统保留内存

8.4.4 为缓存分配内存

8.4.5 InnoDB缓冲池（Buffer Pool）

缓存：索引、行数据、自适应哈希索引、插入缓冲、锁、其他内部数据结构

8.4.6 MyISAM建缓存（Key Caches）

只缓存索引

查询键缓存

show global variables like '%key_buffer_size%'

查询索引存储占用的空间

select sum(index_length) from information_schema.tables where engine='MYISAM'

UNIX系统，使用一下命令

du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`

8.4.7 线程缓存

保存空闲的线程

thread_cache_size 可缓存线程数

8.4.8 表缓存

8.4.9 InnoDB数据字典

InnoDB的表缓存

8.5 配置MySQL的I/O行为

8.5.1 InnoDB I/O配置（重点查看）

InnoDB使用日志来减少提交事务时的开销，无须在每次事务提交就把缓冲池的脏块刷新到磁盘中

把随机I/O变成顺序I/O

8.5.2 MyISAM的I/O配置