MySql优化总结

Mysql优化切入点

1. 物理层面：

   1. 硬件升级

   2. 读写分离

   3. 使用Mysql集群

   4. 分库分表

2. 运维层面：

   1. 定时清理无效数据以及磁盘碎片

   2. 选择合适的备份策略

   3. 配置优化

3. 设计层面：

   1. 表设计规范

   2. 列类型选择

   3. 使用索引

   4. 使用合适的存储引擎

4. SQL优化

   1. 定位慢查询

   2. 使用explain分析你的SQL

   3. 常见的SQL优化点

   4. 让SQL生效索引

   5. 使用存储过程

硬件升级

升级物理服务器是最简单最有效的优化方式之一，可适当增强内存、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，就会被统计到。