MySQL 优化闲扯

最新推荐文章于 2025-09-06 22:56:13 发布

weixin_30572613

最新推荐文章于 2025-09-06 22:56:13 发布

阅读量77

点赞数

CC 4.0 BY-SA版权

文章标签：数据库操作系统运维

原文链接：http://www.cnblogs.com/sumon/p/4756561.html

本文详细阐述了MySQL优化的全过程，包括数据库设计规范、服务器硬件和操作系统调节、MySQL配置、查询优化和高级优化等内容。通过遵循一系列最佳实践，可显著提升数据库性能与查询效率。

参阅 -- 淘宝商品库MySQL优化实践的学习.txt

架构 -- 基于MySQL百万在线架构.pdf

MySQL 特点

1.  binlog、redo log、undo log主要顺序IO
2.  datafile是随机IO和顺序IO都有
3.  OLTP业务以随机IO为主，建议加大内存，尽量合并随机IO为顺序IO
4.  OLAP业务以顺序IO为主，极大内存的同时增加硬盘数量提高顺序IO性能
5.  MyISAM是堆组织表（HOT），InnoDB是索引组织表（IOT）
6.  InnoDB相比MyISAM更消耗磁盘空间

MySQL 优化

优化思路:
        确认问题->确认瓶颈->制定方案->测试方案->实施方案->回顾反馈
命令和工具：
        top vmstat sar iotop dstat oprofile
        slow log,show global status,show processlist,show engine innodb status,pt-ioprofile

一. 数据库设计

命名规范
1.库名、表名、字段名必须使用小写字母，并采用下划线分割。
2.库名、表名、字段名禁止超过32个字符。须见名知意。
3.库名、表名、字段名禁止使MySQL保留字。
4.临时库、表名必须以tmp为前缀，并以日期为后缀。
5.备份库、表必须以bak为前缀，并以日期为后缀。

基础规范：
1.使用INNODB存储引擎
2.表字符集使用UTF8
3.所有表.库.字段,都需要添加注释
4.单表数据量建议控制在5000W以内 ---旧数据归档
5.不在数据库中存储图片,文件等大对象数据

字段设计：
1.尽可能不使用TEXT、BLOB类型,可以做垂直拆分，或者转成MyISAM表
2.用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
3.Simple is good
4.尽可能的使用整型
5.将字符转化为数字
6.使用TINYINT来代替ENUM类型
7.使用UNSIGNED存储非负整数
8.INT类型固定占用4字节存储
9.使用timestamp存储时间
10.使用INT UNSIGNED存储IPV4
11.使用VARBINARY存储大小写写敏感的变⻓字符串
12.禁止在数据库中存储明文密码
13.表字段必须有两个时间列,create_time,update_time,且类型如下
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建日期',
14.IPV4适用INT UNSIGNED存储




索引规范
主键准则
    表必须有主键
    不使用更新频繁的列
    尽量不选择字符串列
    尽量不使UUID MD5 HASH
    默认使用非空唯一键
    建议选择自增或发号器

二. 服务器硬件和操作系统调节

1.  分配足够多的物理内存给MySQL(70%+)   ---- 拥有足够的物理内存来把整个InnoDB文件加载到内存中，在内存中访问文件时的速度要比在硬盘中访问时快的多
2.  尽量避免使用交换区swap，如可能就禁用掉. ---- 交换时是从硬盘读取的，它的速度很慢
3.  阵列卡配备
4.  及BBU模块，提高IOPS,使用电池供电的RAM                     ---- 电源保护器
4.  尽量使用RAID10，而不是RAID5，电池支持的高速缓存RAID控制器----数据校检代价高
5.  将操作系统和数据分区分开，不仅仅是逻辑上，还包括物理上 –---操作系统的读写操作会影响数据库的性能
6.  把MySQL临时空间和复制日志与数据放到不同的分区   –--- 当数据库后台从磁盘进行读写操作时会影响数据库的性能
7.  如果可以的话，使用noatime 和 nodirtime挂载文件系统 –--- 没有理由更新访问数据库文件的修改时间
8.  使用 XFS 文件系统 – 一种比ext3更快、更小的文件系统，并且有许多日志选项 ---- 而且ext3 已被证实与MySQL有双缓冲问题
9.  调整 XFS 文件系统日志和缓冲变量 –---为了最高性能标准
10. 在 Linux 系统中, 使用 NOOP 或者 DEADLINE IO 定时调度程序 –--- 同NOOP 和 DEADLINE定时调度程序相比，这个 CFQ 和 ANTICIPATORY 定时调度程序 显得非常慢。echo deadline >/sys/block/sda/queue/scheduler，我们也可以直接在/etc/grub.conf的kernel行最后添加elevator=deadline来永久生效
11. 使用64位操作系统 -- 现在应该是默认的了
12. 删除服务器上未使用的安装包和守护进程  –--- 更少的资源占用
13. 把使用MySQL的host和你的MySQL host放到一个hosts文件中 – 没有DNS查找 ----怀疑DNS解析有问题时尝试
14. 切勿强制杀死一个MySQL进程 –--- 你会损坏数据库和正在运行备份的程序。
15. MySQL独占整个Server，避免资源争用。

16. net.ipv4.tcp_fin_timeout=30 ---- TIME_WAIT超时时间，默认是60s
17. net.ipv4.tcp_tw_reuse=1 ---- #1表示开启复用，允许TIME_WAITsocket重新用于新的TCP连接，0表示关闭
18. net.ipv4.tcp_tw_recycle=1 ---- #1表示开启TIME_WAITsocket快速回收，0表示关
19. net.ipv4.tcp_max_tw_buckets=4096 ---- #系统保持TIME_WAITsocket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息
20. net.ipv4.tcp_max_syn_backlog=4096 ---- #进入SYN队列最大长度，加大队列长度可容纳更多的等待连接
21. vi/etc/security/limits.conf，* soft nofile 65535，* hard nofile 65535，ulimit -SHn 65535 #立刻生效
22. 内存 关闭NUMA可以从BIOS，操作系统，启动进程时临时关闭这个特性；关闭swap，vm.swappiness = 0
23. CPU 关闭电源保护模式，采用最大性能模式
24. 设置IO写策略为WB，或者FORCE WB，禁用WT策略
25. 关闭预读，没必要预读，那点宝贵的CACHE用来做写缓存
26. 关闭物理磁盘cache策略，防止丢数据
27. 使用高转速硬盘，不使用低转速盘
28. 使用SSD或者PCIe-SSD盘

三.MySQL 配置

1.  innodb_flush_method=O_DIRECT ----来避免双缓冲
2.  innodb_buffer_pool_size=server内存*70%+，innodb_log_buffer_size : 如果没在大事务，控制在8M-16M即可
3.  innodb_log_file_size设置的合理大小 ----可以依据日志产生量 show engine innodb status\G select sleep(60); show engine innodb status\G
4.  innodb_log_file_size : 这个可以配置256M以上，建议有两个以前的日志文件(innodb_log_files_in_group). 如果对系统非常大写的情况下，也可以考虑用这个参数提高一下性能，把文件设的大一点，减少checkpiont的发生。 最大可以设制成：innodb_log_files_in_group * innodb_log_file_size < 512G(percona, MySQL 5.6) 建议设制成: 256M -> innodb_buffer_pool_size/innodb_log_file_in_group 即可。
5.  max_connections,max_connection_errors（10w）----合理的最大连接数，依据你的硬件和实际使用

6.  thread_cache 在4-16，依据硬件，防止打开连接时缓慢
7.  skip-name-resolve 代开 – 去掉 DNS 查找
8.  query_cache_size=0 一般数据经常变化的，禁用查询缓存
9.  temp_table_size，max_heap_table_size，sort_buffer_size,read_buffer_size,join_buffer_size,read_rnd_buffer_size 都是针对session的设置，注意大小
10. innodb_flush_log_at_trx_commit=1默认，为了数据安全，sync_binlog 默认即可： 0
11. innodb_io_capacity: 这个参数据控制Innodb checkpoint时的IO能力，一般可以按一块SAS 15000转的磁盘200个计算，6块盘的SAS做的Raid10这个值可以配到600即可。如果是普通的SATA一块盘只能按100算。(innodb-plugin, Percona有这个参数)
12. innodb_max_dirty_pages_pct : 这个参数据控制脏页的比例如果是innodb_plugin或是MySQL5.5以上的版本，建议这个参数可以设制到75%-90%都行。如果是大量写入，而且写入的数据不是太活跃，可以考虑把这个值设的低一点。 如果写入或是更新的数据也就是热数据就可以考虑把这个值设为：95%
13. innodb_adaptive_flushing 默认即可
14. innodb_change_buffer_max_size 如果是日值类服务，可以考虑把这个增值调到 50
15. innodb_change_buffering 默认即可
16. innodb_flush_neighors 默认是开的， 这个一定要开着，充分利用顺序IO去写数据
17. innodb_lru_scan_depth: 默认即可 这个参数比较专业
18. innodb_max_purge_lag 默认没启用，如果写入和读取都量大，可以保证读取优先，可以考虑使用这个功能
19. innodb_random_read_ahead 默认没开启，属于一个比较活跃的参数，如果要用一定要多测试一下。 对用passport类应用可以考虑使用
20. innodb_read_ahead_threshold 默认开启：56 预读机制可以根据业务处理，如果是passprot可以考虑关闭。如果使用innodb_random_read_ahead,建议关闭这个功能
21. innodb_read_io_threads 默认为：4 可以考虑8 ，innodb_write_io_threads 默认为：4 可以考虑8
22. innodb_rollback_segments 默认即可: 128，undo log也可以独立配置了，建议单独配置出来，放在独立的存储上
23. max_allowed_packet=32~64M
24. innodb_file_per_table 打开
25. innodb_data_file_path = ibdata1:2048M:autoextend，这个大小是每次增加的量，可以设置大写
26. innodb_log_files_in_group = 3
27. innodb_additional_mem_pool_size = 64M 根据meta量设置
28. open_file_limit,innodb_open_files,table_open_cache,table_definition_cache可以数倍max_connections
29. sysdate-is-now

四.查询优化

1.  使用慢查询日志去发现慢查询。
2.  使用执行计划去判断查询是否正常运行
3.  总是去测试你的查询看看是否他们运行在最佳状态下–久而久之性能总会变化。
4.  避免在整个表上使用count(*),它可能锁住整张表。
5.  使查询保持一致以便后续相似的查询可以使用查询缓存。
6.  在适当的情形下使用GROUP BY而不是DISTINCT。
7.  在WHERE, GROUP BY和ORDER BY子句中使用有索引的列。
8.  保持索引简单,不在多个索引中包含同一个列。
9.  有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX。
10. 检查使用SQL_MODE=STRICT的问题。
11. 对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR.
12. 为了避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现。
13. 不要使用MAX,使用索引字段和ORDER BY子句。
14. 避免使用ORDER BY RAND().
15. LIMIT M，N实际上可以减缓查询在某些情况下，有节制地使用。
16. 在WHERE子句中使用UNION代替子查询。
17. 对于UPDATES（更新），使用SHARE MODE（共享模式），以防止独占锁。
18. 在重新启动的MySQL，记得来温暖你的数据库，以确保您的数据在内存和查询速度快。
19. 使用DROP TABLE，CREATE TABLE DELETE FROM从表中删除所有数据。
20. 最小化的数据在查询你需要的数据，使用*消耗大量的时间。
21. 考虑持久连接，而不是多个连接，以减少开销。
22. 基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询。
23. 当负载增加您的服务器上，使用SHOW PROCESSLIST查看慢的和有问题的查询。
24. 在开发环境中产生的镜像数据中测试的所有可疑的查询
25. Batch INSERT and REPLACE，Use LOAD DATA instead of INSERT
26. Delete small amounts at a time if you can
27. pt-query-digest + Box Anemometer/Query-Digest-UI

五.高级优化

1.  如果使用percona或者mariadb的话，开启thread pool ---- 高并发的情况下，性能不会下降的明显
2.  如果对数据占用空间比较敏感的，考虑用toku引擎
3.  BIOS设置优化,System Profile（系统配置）选择Performance Per Watt Optimized(DAPC)，发挥最大功耗性能,Memory Frequency（内存频率）选择Maximum Performance（最佳性能）,C1E，允许在处理器处于闲置状态时启用或禁用处理器切换至最低性能状态，建议关闭（默认启用）,C States（C状态），允许启用或禁用处理器在所有可用电源状态下运行，建议关闭（默认启用）
4.  To get rid of a runaway rollback once it occurs, increase the buffer pool so that the rollback becomes

CPU-bound and runs fast, or kill the server and restart with innodb_force_recovery=3,

六.备份优化

1.  从二级复制服务器上进行备份。
2.  在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致。
3.  彻底停止MySQL，从数据库文件进行备份。
4.  如果使用MySQL dump进行备份，请同时备份二进制日志文件– 确保复制没有中断。
5.  不要信任LVM 快照 – 这很可能产生数据不一致，将来会给你带来麻烦。
6.  为了更容易进行单表恢复，以表为单位导出数据 – 如果数据是与其他表隔离的。
7.  当使用mysqldump时请使用–opt。
8.  在备份之前检查和优化表。
9.  为了更快的进行导入，在导入时临时禁用外键约束。
10. 为了更快的进行导入，在导入时临时禁用唯一性检测。
11. 在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长。
12. 通过自动调度脚本监控复制实例的错误和延迟。
13. 定期执行备份。
14. 定期测试你的备份

转载于:https://www.cnblogs.com/sumon/p/4756561.html