7.1 MySQL优化

优化框架：

1.从硬件层面

　　cpu选型

检查工作负载是否是CPU密集型 ：
  可以通过检查 CPU利用率来判断是否是CPU密集型的工作负载，但是仅看CPU 整体的负载是不合理的，还需要看看 CPU 使用率和大多数重要的查询的 I/O 之间的平衡，并注意 CPU 负载是否分配均匀。
选择更快的CPU还是更多的CPU
   当遇到CPU密集型的工作时，通常可以从更快的CPU中收益
   多个CPU对并发性能比较好的数据库更有益

　　内存

   配置大量内存最大的原因共实不是因为可以在内存中保存大量数据 ∶最终母的是避免磁盘 I/O，因为磁盘 I/O 比在内存中访问数据要慢得多。关键是要平衡内存和磁盘的大小、速度、成本和其他因素，以便为工作负载提供高性能的表现。

　　硬盘

尽量使用顺序IO，避免随机IO
使用SSD固态硬盘，SAN等
使用raid10
存储容量，传输速度，访问时间，主轴转速，物理尺寸

　　网卡

光纤
多个网卡

 

2.从软件层面

操作系统

选择Linux Unix

文件系统

建议采用xfs
如果使用ext系统：
    文件系统是针对通用平台做了平衡，我们可以针对数据库做针对优化

 

网络优化

调整内核参数提升数据库网络IO性能

 

磁盘队列调度策略

在GNU /Linux上，队列调度决定了到块设备的请求实际上发送到底层设备的顺序。默认情况下使用cfq（Completely Fair Qucueing，完全公平排队）策略。随意使用的笔记本和台式机使用这个调度策略没有问题，并且有助于防止I/O饥饿，但是用于服务器则是有问题的。在MySQL的工作负载类型下，cfq会导致很差的响应时间，因为会在队列 中延迟一些不必要的请求。

[root@centos7-1 ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq

 

关闭NUMA

调整线程和内存交换分区
（比如设置物理内存使用百分比就使用交换分区）

 

3.从MySQL层面

修改连接数：

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec

max_connections=1024

修改环境变量

innodb_buffer_pool_size=2048M
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.00 sec)
默认KB为单位
mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           2.000000000000 |
+------------------------------------------+
1 row in set (0.00 sec)

InnoDB引擎相关参数介绍
InnoDB引擎相关参数多达120个，但是在实际工作中，我们需要修改的参数却是非常少的，下面我们介绍一些重要参数：
innodb_buffer_pool.size=2048M
InnoDB使用一个缓存池来保存索引和原始数据，缓存池设置的越大，理论上在存取表里面的数据时所需要的磁盘I/O就越少。官方建议设置为物理内存的50%-80%。实际配置大小应根据具体环境而定（根据经验）。
Innodb_data_file_path=ibdata1:12M:autoextend 
InnoDB 数据文件的路径，默认为12MB大小的iddata1的单独文件，默认以64MB为单位自增。
innodb_additional_mem_pool_size=16M
该参数是用来设置存储的数据目录信息和其它内部数据结构的内存池大小。应用程序里的表越多，就需要在其中分配越多的内存
对于一个稳定的系统这个参数的值一般比较稳定，不用设置太大，如果不够用，会从操作系统分配内存，并在错误日志记录警告信息
默认为1M，如果发现有错误信息，增加即可
innodb_file_io_threads=4
InnoDB 中文件IO线程，通常为4 如果是window则可以设置更大的值以提高磁盘IO
inondb_thread_concurrency=8 
服务器有几个cpu就设置为几，通常为8
innodb_flush_log_at_trx_commit=1 
如果设置为0，就相当于innodb_log_buffer_size队列满后再统一存储，默认值为1，也是最安全的设置
innodb_log_buffer_size=16M
默认为1M，通常设置8-16M就足够了
innodb_log_file_size 128M
日志文件大小，更大的值可以提高性能，但是也会增加数据库恢复的时间
innodb_log_files_in_group=3 为提高性能，mysql可以循环的将日志文件写入多个文件，推荐设置为3
innodb_max_dirty_pages_pct=90
Innodb主线程数安心缓存池中的数据
innodb_lock_wait_timeout=120
Innodb事务被回滚之前可以等待一个锁定的超市秒数。innodb在它自己的锁定表中自动检测事务死锁并回滚事务，默认值是50s
innodb_file_per_tables 独立表空间

参数优化工具

变量优化工具
wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar
tar 解压
安装
[root@localhost ~]# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock
[root@mysql ~]# pt-variable-advisor localhost -p123456 --socket /var/lib/mysql/mysql.sock

 

[root@mysql ~]# pt-variable-advisor localhost -p123456 --socket /var/lib/mysql/mysql.sock
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-variable-advisor line 4039.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-variable-advisor line 4039.

# A software update is available:
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# WARN key_buffer_size: The key buffer size is set to its default value, which is not good  for most production systems.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# WARN expire_logs_days: Binary logs are enabled, but automatic purging is not enabled.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.

# WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed.

 

SQL优化

MySQL如何选择合适的字符集
如果存储的是各种各样的语言文字，则可以选择UTF8，这是目前国内应用最广泛的字符集，没有之一
如果只需要支持中文，并且数据量很大，此外，还包含了大量的运算，则可以选择GBK，理论上可以获取更高的性能，但不推荐使用
对于新兴的互联网以及移动互联网的混合业务，推荐使用utf8mb4字符集替代UTF8字符集。总之，如果没有极特别的需求，请选择UTF8或utf8mb4作为数据库的字符集
如果使用开源程序，则可以根据上述说明进行选择，如果是公司开发人员自己开发产品，那么选择权就在开发人员手里，DBA只能提供建议

打开慢查询日志
使用其它分析sql工具

 https://imysql.cn/my-cnf-wizard.html