MySQL优化
Tags: MySQL
- 数据库优化目的
- 由于数据库连接超时、慢查询1、阻塞造成的数据无法提交等产生的访问错误
- 增加数据库的稳定性
- 优化用户体验(提高页面访问速度等)
- 优化数据库的几个方面
- 硬件、系统配置、数据库表结构、SQL及索引
SQL语句优化
- 使用MySQL慢查询日志对有效率问题的SQL监控
- show variables like ‘slow_query_log’
- set global slow_query_log_file = ”
- set global log_queries_not_using_indexes=on;
- set global long_query_time=1
- 使用explain查询SQL的执行计划
- explain select * from customer \G;
- type 连接使用类型 const、eq_reg、ref、rang、index、ALL |rows 表扫描的行数
- Extra: usiong filesort | using temporary :查询需要优化
- Count()和Max()优化
- 添加索引
- count(*)包括空,而count(id)不包括
- 子查询的优化
- join(是否存在一对多) distinct 去重
- limit查询优化
- 使用有索引的列或主键进行Order by操作
- 记录上次返回的主键,在下次查询时使用主键过滤(连续))
SQL索引优化
- 在where从句,group by从句,order by从句,on从句中出现的列
- 索引字段越小越好
- 离散度大的列放到联合索引的前面
- 优化查询、影响写入效率,过多的索引也会影响查询效率
- 重复及冗余索引(pt-duplicate-key-checker)
pt-duplicate-key-checker -uroot -p ‘123’ -h 127.0.0.1 - 删除不用索引(慢查询日志配合pt-index-usage来进行索引使用情况分析)
数据库结构优化
- 选择合适的数据类型
- 选择最小的数据类型
- 使用简单的数据类型,int要比varchar类型在mysql处理上简单
- 尽可能使用not null定义字段
- 尽量少用text类型,费用不可时最好考虑分表
- 表的范式化和反范式化
- 第三范式:数据表中不存在非关键字段对任意候选关键字段的传递函数依赖
- 反范式:把原本符合第三范式的表适当增加冗余,以达到优化查询效率的目的(空间换时间)
- 表的垂直拆分:很多列的表拆分成多个表
- 把不常用的字段单独存放到一个表中
- 把大字段独立存放到一个表中
- 把经常一起使用的字段放到一起
- 表的水平拆分:
- 对主键进行hash运算,例:mod(id,5)取0-4个值
- 针对不同的hashID把数据存到不同的表中
- 跨分区表进行数据查询
- 统计及后台报表操作
- 前后台查询分开(后台使用汇总表)
系统配置优化
- 操作系统配置优化
- MySQL配置文件优化
- percona数据库配置向导
服务器硬件优化
- 慢查询:很难再一定的时间内过滤出所需要的数据 ↩