多维度mysql性能优化手段实践

数据库优化维度有四个：硬件升级、系统配置、表结构设计、SQL语句及索引。

优化选择：

• 优化成本：硬件升级>系统配置>表结构设计>SQL语句及索引。

• 优化效果：硬件升级<系统配置<表结构设计<SQL语句及索引。

系统配置优化

保证从内存中读取数据

MySQL会在内存中保存一定的数据，通过LRU算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。扩大innodb_buffer_pool_size，能够全然从内存中读取数据。最大限度降低磁盘操作。

确定innodb_buffer_pool_size 足够大的方法：

mysql>  show global status like 'innodb_buffer_pool_page%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 7163   |
| Innodb_buffer_pool_pages_dirty   | 0      |
| Innodb_buffer_pool_pages_flushed | 848994 |
| Innodb_buffer_pool_pages_free    | 1024   |  0则表示已用光
| Innodb_buffer_pool_pages_misc    | 4      |
| Innodb_buffer_pool_pages_total   | 8191   |
+----------------------------------+--------+

nnodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。

修改 my.cnf

innodb_buffer_pool_size = 750M

如果是专用的MySQL Server可以禁用SWAP

#查看swap
cat /proc/swaps
#关闭所有交换设备和文件.
swapoff -a

数据预热

默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。数据预热能够提高读取速度。

降低磁盘写入次数

• 增大redolog，减少落盘次数

  innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

• 通用查询日志、慢查询日志可以不开，bin-log开

  生产中不开通用查询日志，遇到性能问题开慢查询日志

• 写redolog策略 innodb_flush_log_at_trx_commit设置为0或2

  如果不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者事务都非常小，都能够用 0或者 2 来减少磁盘操作。

提高磁盘读写性能

使用SSD或者内存磁盘。

表结构设计与优化

设计中间表

设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）。

设计冗余字段

为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）。

拆表

对于字段太多的大表，考虑拆表（比如一个表有100多个字段）

对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表

主键优化

每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下雪花算法）。

字段的设计

数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

能用数字的用数值类型如：sex 1 0

SQL语句及索引优化

设计一个表：tbiguser

create database mytest;
use mytest;
create table tbiguser(id int primary key auto_increment,nickname varchar(255),loginname varchar(255),age int,sex char(1),status int, address varchar(255));

插入1千万的数据

CREATE PROCEDURE test_insert()
BEGIN DECLARE i INT DEFAULT 1;
WHILE i<=10000000
DO
insert into tbiguser VALUES(null,concat('zy',i),concat('zhaoyun',i),23,'1',1,'beijing');
SET i=i+1;
END WHILE;
commit;
END;

慢日志查询开启（获取慢查询）

使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句3秒-5秒

mysql> show variables like '%slow_query%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /opt/lib/mysql/dbserver0-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)
​
# 默认10秒
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
#命令设置开启
set global slow_query_log='ON';
#测试生成慢查询日志
select sleep(10);
#设置慢查询时间阈值
set global long_query_time=2;
flush privileges;

配置文件配置开启慢查询

[mysqld]
slow_query_log = ON
slow_query_log_file =/opt/lib/mysql/dbserver0-slow.log
long_query_time = 10

使用explain查看有问题的SQL的执行计划，重点查看索引使用情况

EXPLAIN查看索引使用情况

参考文档：

MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

使用explain查看有问题的SQL的执行计划，重点查看索引使用情况

mysql> explain select * from tbiguser where loginname='zhaoyun1' and nickname='zy1';
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | tbiguser | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10407544 |     1.00 | Using where |
+----+-----