数据库(MySQL)的调优

MySQL 调优金字塔理论

如上图所示:

数据库优化维度有四个:
硬件、系统配置、数据库表结构、SQL及索引
优化成本:
硬件>系统配置>数据库表结构>SQL及索引
优化效果:
硬件<系统配置<数据库表结构<SQL及索引

1. SQL及索引的优化

1.1 优化步骤

 1)  查看本机MySQL Server 慢查询是否打开

show variables like 'slow%'; 

慢查询打开的情况如下所示：

 2）若慢查询未打开则通过如下脚本设置慢查询

set global slow_query_log = on;
即
set global [上图中选项] = [你要设置的参数值]
注意 slow_query_log_file 路径要加单引号，因为路径varchar  类型的。

3) 定位分析慢查询mysqldumpslow

mysqldumpslow 是MySQL自带的分析数据库慢查询的原生利器，使用方法如下：

mysqldumpslow -t 3 /data/mysql/log/mysql_slow_query.log | more \G;
-t  3 显示前3条慢查询。

通过explain查看执行计划

explain select * from tablename;

1.2 具体优化点（基本原则）

a. sql的优化重要在于减少扫描表的次数（避免全表扫描），需要建立必要的索引

b. 使用order by 是避免使用到filesort(额外的文件排序)，排序字段应该属于索引字段

c.在使用复合索引时要符合最左原则

d.避免类型转换，否则可能索引失效

e. <> 、!、is null、not in、like(可以前缀匹配) 等操作符慎用,or(如果要利用索引,则OR之间的每个条件列都必须要用到索引，否则使用union 代替)

f. 对于join联合查询，要用小表驱动大表，排序字段尽量在驱动表上

g.覆盖索引(查询的列与where字句的条件列都能在索引中找到)，覆盖索引可以避免或者减少回表

h.分页limit优化，对于偏移量offset较大的语句可以通过倒叙反向查询

i.尽量不使用select * ，去除没有使用到的字段，只查需要的字段

j.少用count(*)

k.尽早过滤原则

2. 表结构优化

参考

3.系统参数配置优化

参考

4.硬件优化

cpu、内存、IO

激光打印