Mysql性能优化之逐级优化，开发人员必备技巧

作为一名软件开发人员，尤其是服务端开发，数据库已经是一个必备的技能了，也是这些年使用最广泛的数据库之一，小编平时也是开发工作中也是基于mysql数据库的，经常跟mysql打交道。使用数据库的人都会碰到性能问题，随着业务的发展，对于数据库的性能要求也会越来越高，那么平时遇到性能问题我们应该怎么优化，或者说怎么预防数据库出现性能问题以及在出现性能问题时如何更快速的发现问题和解决问题，今天咱们一起来来看看关于Mysql的性能优化过程。

mysql

数据库优化的目的

1、提高系统的稳定性

低效的sql查询会导致系统经常处于卡顿甚至崩溃状态

阻塞造成数据提交失败

2、增强用户体验

系统访问的流畅度

稳定且良好的系统功能体验

数据库优化的层级

1、sql语句及索引优化

2、数据库表即数据表结构

3、数据库系统配置

4、服务器硬件配置

这个数据库优化层级，自上而下，成本越来越高，然而效果越来越不明显，也就是说，sql语句及索引的优化成本最低产生的效果最明显，也是开发人员使用最多的优化手段。接下来咱们就从上到下来看看怎么优化。

一、sql语句及索引的优化

1、mysql慢查询日志及未使用索引查询日志开启

show variables like '%log%'; ——查看log相关的变量列表及状态，忘记变量名可以查看

show variables like 'slow_query_log'; ——查看是否开启sql慢查询日志

set global slow_query_log=on/off; ——开启或关闭sql慢查询日志

show variables like 'log_queries_not_using_indexes'; ——查看是否开启未使用索引查询日志

set global log_queries_not_using_indexes=on/off; ——开启未使用索引查询日志

还要设置慢查询的时间，查看变量long_query_time的值，show variables like 'long_query_time'; set global long_query_time=1; 单位s，如果设置成0的话，那所有查询都会被记录。

show variables like 'slow_query_log_file'; ——查看慢查询日志位置，查到日志位置后就可以去查看记录的慢查询，如下图：

配置sql慢查询日志

2、慢查询日志分析工具——mysqldumpslow

安装了mysql，这个工具也会随之安装进来，所以可以直接使用，使用时可以mysqldumpslow -h查看命令参数。

mysqldumpslow工具

-s 按照那种方式排序

c：访问计数
l：锁定时间
r:返回记录
a:查询时间
al：平均锁定时间
ar：平均访问记录数
at：平均查询时间

-t 是top n的意思，返回多少条数据。
-g 可以跟上正则匹配模式，大小写不敏感。

大家可以自行摸索一下，可以帮助大家更好的分析慢查询的sql语句的问题出在哪里。

3、explain分析sql的执行计划

explain分析执行计划

select_type：查询的类型（SIMPLE简单查询，PRIMARY最外层的查询等）
table：数据表，显示这行数据是哪张表
type：连接类型，从最好到最差(const，eq_ref，ref，range，index，all)，这个很重要
possible_keys：可能用到的索引，如果为null则无可用索引
key：实际使用到的索引，如果为null则没有使用索引
key_len：使用到索引的长度，短点较好
ref：索引的哪一列被使用了，显示常数最好
rows：用来返回请求结果需要遍历到的行数，越少越好
extra：扩展列，其他信息use index表示覆盖索引扫描，using filesort使用文件排序，需要优化，排序有问题，using temporary使用临时表，也需要优化了，一般对不同列集合进行order by排序。

4、索引的优化

这个小节的内容在小编往期的文章中有详细介绍，请移步往期文章《Mysql查询优化器，再也不会因为该什么时候建立"索引"发愁了》https://www.toutiao.com/i6759772160329253379/

二、数据表结构的优化

1、选择合适的数据类型，如下：

使用可以满足使用条件的最小数据类型
使用简单的数据类型，比如int比varchar在mysql上的处理上简单，时间戳，IP地址等
定义字段时使用not null
尽量不要使用Text、MediumText等text数据类型，可以考虑分表存储数据

2、表的垂直拆分

把一个很多列的表拆分为多个表，有效解决表宽度问题

不常用的字段单独存放到一个表
经常用的字段单独存放到一个表
把较大字段单独存放到一个表，比如产品的描述信息不建议放在产品主表

三、系统配置优化

mysql服务器是基于操作系统的，因此操作系统的配置也会影响到mysql数据系统，下面列出一些比较常见的系统配置项。

系统

1、服务器网络方向，修改/etc/sysctl.conf文件

net.ip4.tcp_max_syn_backlog=65535 ——增加tcp支持的队列数
net.ip4.tcp_max_tw_buckets=10000
net.ip4.tcp_tw_reuse=1
net.ip4.tcp_tw_recycle=1
net.ip4.tcp_fin_timeout=10 ——上面这四项，断开连接时减少资源回收

2、服务器打开文件限制数

可以使用ulimit -a查看目录限制，修改/etc/security/limits.conf增加下面内容来修改打开文件数量的限制。

soft nofile 65535 ——单个用户可用的最大进程数量(软限制)
hard nofile 65535 ——单个用户可用的最大进程数量(硬限制)

3、mysql配置

mysql配置文件一般位于/etc/my.conf或/etc/mysql.conf

/etc/mysql/my.conf

innodb_buffer_pool_size ——配置innodb的缓冲池，如果只有Innodb引擎的表，建议设置为总的75%左右，如果还有myIsam表，那可以减小一点。
innodb_buffer_pool_instances ——5.5后新增参数，设置缓冲池的个数，默认一个缓冲池
innodb_log_buffer_size ——innodb的log缓冲的大小，一遍设置小点即可
innodb_flush_log_at_trx_commit ——对innodb的I/O效率影响很大，默认1，可以设置【0,1,2】，建议设置2，如果对数据安全性要求很高设置默认1。
innodb_read_io_threads ——读的I/O进程数，默认4，5.5可根据cpu核数及系统负载调整读的并发数
innodb_write_io_threads ——写的I/O进程数，默认4，5.5可根据cpu核数及系统负载调整写的并发数
innodb_file_per_table ——控制每个表使用独立的空间，默认off，建议设置on，增加并发的读写效率