MySQL数据库的优化技术：
对数据库的优化是一个综合性的技术，主要包括： 1.表的设计是否合理（符合三范式，3NF） 2.添加适当索引（常见索引有四种：普通索引，主键索引，唯一索引，全文索引，（空间索引，复合索引） 3.分表技术（水平分割，垂直分割） 4.读写分离 5.存储过程（SQL语句每次执行都需要编译，存储过程只编译一次，模块化编程） 6.对MySQL的配置优化（配置最大并发数 my.ini，调整缓存大小） 7.MySQL服务器的硬件升级 8.定时清除不需要的数据，定时进行碎片整理 9.SQL语句优化 l 需求：为什么要调优
l 分析：怎么找到软肋
l 实战：如何调优
l 总结
为什么要调优
§ 老板要求
§ 客户投诉
§ 机器发飙
§ 自己主动
机器发飙
- 网站或客户端打开非常慢，而webserver负载低，或打开静态页面很快，那就很可能是数据库的问题
- load average >= 5
- Iowait >= 10
- vmstat procs(r b) 值较高
- top中CPU的idle很小，sys或wait较高
- 服务器的swap严重
- mysql的内存命中率很低，例如 myisam_key_read_hit_ratio或innodb_buffer_hit_ratio较低
瓶颈定位
- vmstat,iostat,top等系统级别的工具
- explain
- slow query
- show status/show processlist/show engine innodb status
- 其他，如mysqlreport，profiling
瓶颈定位 – vmstat/iostat
瓶颈定位 - Explain
Explain 都能提供什么信息呢？
- 表的读取顺序
- 每个表都是如何读取的
- 可能用到哪些索引，实际使用了哪些索引
- 表是如何引用的
- 查询优化器从每个表中预计读取的记录数
- 其他额外信息，例如是否使用了内存表，是否引发排序等
瓶颈定位 – 续
- 将 LONG_QUERY_TIME 设为最小值；建议打补丁，这样单位可以设成微秒，并可查看详细执行计划
- 执行 SHOW [GLOBAL] STATUS/PROCESSLIST 查看当前运行状态，从结果中发现可能的问题
- 执行 SHOW ENGINE INNODB STATUS 查看 INNODB 的状态
- 另外,要定期检查多余的索引以及没有使用索引的慢查询
- 利用 mysqlreport 产生可读性更强的报告
- 利用 Profiling 剖析一次查询瓶颈所在
- 其他工具，包括监控工具，linux自带工具等
MySQL调优的几种途径
- 硬件、网络、软件
- MySQL参数设置
- 应用程序、架构优化
- 查询优化、索引
硬件、网络、软件
- 通常硬件是优化的最佳入口，主要是CPU、内存、磁盘、网络
- 客户端和服务器在一个高速的局域网内
- 通常，新版本的效率不如旧版本，但是可以利用新版本的新功能来从另一方面得到性能上的提升
- 编译优化，采用静态编译等
- 使用更稳定高效的内核
- 使用合适的文件系统，推荐使用xfs()
MySQL参数设置
| 参数名 | 说明 |
| Key Buffer | MyISAM索引缓冲 |
| Query Cache | 查询结果缓存 |
| Sort Buffer | 排序缓冲 |
| Read Buffer | 全表扫描缓冲 |
| Join Buffer | 连接查询缓冲 |
| Slow Query | 设置慢查询,打上msl补丁 |
| Tmp Table | 内存表，还需要注意max_heap_table_size |
| Innodb Buffer | InnoDB最重要的设置,包括日志缓冲 |
应用程序、架构优化
- 垂直/水平切分服务器/数据库、表
- 开启MySQL复制，实现读、写分离
- 在复制的基础上，增加负载均衡
- 采用集群+复制(MySQL 6.0+)
- 频繁更新的表，可以分离成父表和子表(内存表)
- 用统计表保存定时统计结果，而不是在大表上直接统计
- 编写存储过程/函数来代替大量的外部应用程序交互
查询优化、索引
- 确保索引合理利用，尽量使用联合索引
- 适当加大查询缓存(query cache)
- 尽量减少交互次数
- 尽量使用固定格式的SQL语句，查询语句中少用运算或函数
- 缩短每个事务
- 使用适当的字段类型；适当的长度，有需要的时候再扩充
- 分解复杂查询为多个小查询
- 字符型字段采用前缀索引
- 其他 ……
调优方法
一、选择合适的引擎
MyISAM
这个是默认类型,基于传统的ISAM类型，它是存储记录和文件的标准方法。与其他存储引擎比较，MyISAM具有检查和修复表格的大多数工具。MyISAM表格可以被压缩,而且它们支持全文搜索。它们不是事务安全的，而且也不支持外键。
InnoDB
ACID、外键、日志修复。InnoDB表格速度很快。如果需要一个事务安全的存储引擎或者是需要大量并发的INSERT或UPDATE，应该使用InnoDB表。
NDB
支持事务，用于cluster，实现高可用，但性能仍欠佳。
调优方法 – 续(其他)
- 不直接执行 COUNT(*) – innodb
- 多个操作放在一起提交，但要注意事务不能太大
- 日志文件并非越大越好，需要考虑恢复和检查点
- 左连接时把数据量小的表放在前面
- innodb_flush_log_at_trx_commit 可以尝试设置为 2，甚至是 0
- 导入数据时关闭 AUTOCOMMIT 以及 UNIQUE_CHECKS、FOREIGN_KEY_CHECKS
- 复杂的查询总是先用EXPLAIN来分析一下
- 定期执行OPTIMIZE TABLE整理碎片
- 用char来代替varchar，MyISAM是这样，InnoDB则相反