一、背景
最近在网上看了很多关于SQL优化的文章,参考这些文章和《深入浅出MySQL》一书,做一个总结(结尾有图,大致总结出了五个方面)
后期会持续优化这篇文章
二、一些配置或者函数的解释
// 1.开启慢查询日志:
slow_query_log=1
// 2.
show status like 'Com_%';
Com_select:执行 select 操作的次数,一次查询只累加 1。
Com_insert: 执行 INSERT 操作的次数, 对于批量插入的 INSERT 操作, 只累加一次。
Com_update:执行 UPDATE 操作的次数。
Com_delete:执行 DELETE 操作的次数。
// 3.让mysql给出合适的字段长度(大致意思)
SELECT * FROM tbl_name PROCEDURE ANALYSE();
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);
// 4.分区
三、五个方面总结MySQL优化
(1)设计方面
- 在遵循三大范式的基础上适当的冗余:这里需要了解三大范式的概念
- 使用合适的数据类型,定义合适的长度;
- 尽量使用not null,因为null数值计算逻辑比较复杂;
(2)SQL语句方面
- 慢查询日志;
- 优化where后边的条件,避免全表扫描;like,>=,not in 等
- explain 查看sql执行情况;
- 批量导入的时候:
- 先禁用索引,导入后重新建索引;
- 批量插入,手动提交事务;
- 避免使用select *;
(3)索引层面
这里边大家可以自由发挥,哈哈
(4)mysql服务层面(配置调整)
- 选用合适的存储引擎,innodb和MyISAM;
- 读多写少用MyISAM;
- 读多写也多用Innodb;
- 查询缓存
- query_cache_type:
- 0或者off:关闭缓存
- 1或者on:开启缓存(使用SQL_NO_CACHE提示的SELECT语句除外)
- 2或者demand (只有显示指定SQL_CACHE的查询语句才会缓存)
- query_cache_size:单位是M;
- reset_query_cache:重置缓存;
- 对数据表的改动会导致基于该数据表的缓存失效;
- query_cache_type:
- key_buffer_size:设置索引块缓存大小,只用于MyISAM;
- table_cache:表示数据库用户打开表的最大数量;
- innodb_buffer_pool_size: 存储innodb引擎的表数据和索引数据的最大内存缓冲区大小;
- 最大连接数。
(5)服务器和应用层面
- 应用层面采用缓存,减少mysql查询频率;
- 主从复制:这里需要了解主从复制原理;
- 读写分离:
- WriteDatabase:写连接;
- ReadDatabase:读连接;
- 负载均衡搭建集群;
- 高可用:为单机服务提供一个冗余机。
- 心跳检测
- 虚IP
- 主从复制
- 分区?不太了解
- 水平分割和垂直分割。
以上就是我的总结,不太全,后期遇到一点补充一点吧,请各位大佬指正,加上配图(从网上找的):