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 |
　　　　+-----------------+-------+

　　　　max_connections=1024

　　修改环境变量

　　　　innodb_buffer_pool_size=2048M

　　　　InnoDB引擎相关参数介绍

　　　　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 独立表空间

　　参数优化工具

　　　　变量优化工具：percona-toolkit

　　SQL优化

　　　　MySQL如何选择合适的字符集

　　　　如果存储的是各种各样的语言文字，则可以选择UTF8，这是目前国内应用最广泛的字符集，没有之一

　　　　如果只需要支持中文，并且数据量很大，此外，还包含了大量的运算，则可以选择GBK，理论上可以获取更高的性能，但不推荐使用

　　　　对于新兴的互联网以及移动互联网的混合业务，推荐使用utf8mb4字符集替代UTF8字符集。总之，如果没有极特别的需求，请选择UTF8或utf8mb4作为数据库的字符集

　　　　如果使用开源程序，则可以根据上述说明进行选择，如果是公司开发人员自己开发产品，那么选择权就在开发人员手里，DBA只能提供建议

　　打开慢查询日志

　　使用其它分析sql工具