最近由于公司交易量越积越多,数据查询变得缓慢,严重影响系统运行效率,需要对数据库和sql进行优化整改,在网上查了一遍mysql常用的优化的方案,把sql和数据库优化的常用的几个方面列到一起,以方便以后分析解决和快速入手。
1.查询分析
手段:慢查询日志分析,EXPLAIN分析查询
慢查询日志开启:MySQL配置文件(my.ini或者my.cnf文件)中配置log_slow_queries(日志记录路径)和long_query_time(慢查询的时间秒值)
2.数据库事务 (禁止自动提交)
禁用:SET autocommit=0;
开启:SET autocommit=1;
3.表结构优化
1.表字段尽量设定为not null;
2.包含特定类型的字段,可以使用enum,set等符合数据类型;
3.数值型字段笔字符串效率高,字段类型尽量使用最小类型;
4.varchar的长度尽量只分配需要的长度;
5,尽量使用timestamp而非datetime;
6.单表字段尽量在20以内;
4.表拆分
垂直拆分:按字段进行拆分;(适合字段比较多的表)
水平拆分:按照行进行拆分;(适合数据量大的表)
5.分区(大数据后的产物 )
mysql主要支持4中模式的分区:range分区,list预定义列表分区,hash分区,key键值分区;
6.读写分离(读多写少)
采用数据库集群的方案,其中一个是主库,负责写入数据,其他都是从库,负责读取数据。
7.数据库集群
例如MyCat搭建Mysql集群,由MyCat负责维护集群节点的使用;
8.硬件优化
1.内存优化;
2.磁盘I/O相关(读写越快越高越好)(使用SSD或者PCleSSD设备)
3.配置CPU相关:
*** 选择Perfromance Per Watt Optimized(DAPC)模式,发挥CPU最大性能;
***关闭CIE和C States等选项,提升CPU效率;
***Memory Frequency(内存频率)选择Maximum Performance;
9.MySql缓存
数据库打开Query Cache(QC) 功能