Mysql优化切入点
- 物理层面:
- 硬件升级
- 读写分离
- 使用Mysql集群
- 分库分表
- 运维层面:
- 定时清理无效数据以及磁盘碎片
- 选择合适的备份策略
- 配置优化
- 设计层面:
- 表设计规范
- 列类型选择
- 使用索引
- 使用合适的存储引擎
- SQL优化
- 定位慢查询
- 使用explain分析你的SQL
- 常见的SQL优化点
- 让SQL生效索引
- 使用存储过程
硬件升级
升级物理服务器是最简单最有效的优化方式之一,可适当增强内存、CPU的配置,提高MySQL的性能。
当内存超过4g,使用64位操作系统,使用64位mysql
配置读写分离和集群
请查看: Mysql主从配置和读写分离配置
分库分表
当业务与数据很庞大时,单台Mysql或者几台Mysql集群已经无法满足需求时,可以采用分库分表:MySql分库分表解决方案
定时清理无效数据以及磁盘碎片
删除mysql数据时,必然会在数据文件中造成不连续的空白空间。如果一段时间内的大量删除操作,会使得空白空间变得比存储内容所占的空间更大。这会导致存储位置不连续,物理存储顺序与理论存储顺序不相同。
所以,需要运维人员定时清理无效数据以及磁盘碎片。
备份策略
根据业务选择合适的MySql备份策略,不合适的备份策略可能造成MySql性能受损或者数据丢失,具体请查看: Mysql备份策略
配置优化
修改my.cnf文件,在linux下,默认在/var/mysql/conf.d/
路径下。
最大连接数
-- 并发2000左右差不多到极限了
max_connections=1000
缓存大小
适当增加查询缓存大小,可以极大的提高mysql的查询性能:
query_cache_size=15M
-- innodb:
innodb_additional_mem_pool_size=64M
innodb_buffer_pool_size=1G
-- myisam
key_buffer_size
表和列的设计
良好的表结构与字段类型不仅可以让你的业务实现起来更加方便,更重要的是可以减少数据库的负担:MYSQL中表和列的设计
使用索引
在合适的情况下使用索引,索引信息存储在内存中,速度极快,具体查看:Mysql的索引
定位慢查询
进行SQL语句优化前,首先要找到有问题的SQL语句,我们把那些执行速度很慢的查询SQL称作为慢查询SQL。在MySql中,可以开启慢查询日志,来定位慢查询语句。
开启Mysql的慢查询日志
如下配置代表当select语句查询时间超过1s钟时,那么则这个查询语句就是一个慢查询语句,他会被记录到remotejob-01-slow.log
中:
# my.cnf
# 开启慢查询日志
slow_query_log=ON
# 指定日志路径
slow_query_log_file=/var/lib/mysql/remotejob-01-slow.log
# 指定慢查询时间
long_query_time=1
查看Mysql的运行状态,找到日志中的慢查询
查询mysql数据库的运行状态,比如mysql的运行时间、执行次数、当前连接状况…
show status;
# 展示当前会话的/所有会话的执行情况 (启动到现在的所有情况,而不是当前敞口的,就使用global)
show [session/global] status like ...;
-- 需要了解的几种指令
# mysql启动时间 单位秒
show status like 'uptime';
# 当前执行了多少select,delete,update,insert语句,确认语句的分布情况
show status like 'com_select';
show status like 'com_delete';
show status like 'com_update';
show status like 'com_insert';
# 展示当前的连接数
show status like 'connection';
# 展示慢查询次数
show status like 'slow_queries';
# 定位慢查询,默认情况下, mysql认为10s才是一个慢查询
# 修改mysql慢查询为一秒种(修改系统变量)
show variables like 'long_query_time'; #查询为10s
set long_query_time = 1; # 修改慢查询时间为1s
模拟慢查询
模拟慢查询环境:使用存储过程构建大表
delimiter //
create procedure slow_table_build ()
begin
declare n int default 400000;
drop table if exists slow_table;
create table slow_table (
id int auto_increment primary key ,
name varchar(32),
age tinyint unsigned
) ;
while n > 0 do
insert into slow_table (name, age) values (concat('name', n), mod(n, 120));
set n = n - 1;
end while ;
end //
delimiter ;
call slow_table_build();
如果此时执行sql语句超过1s,就会被统计到。