3MySQL数据库------MySQL整体优化思路 - 全栈式开发35

• MySQL优化是针对几百万的数据的，少量的没必要优化

一、硬件优化—了解即可

（一）CPU优化

1.关闭 CPU 节能，设定为最大性能模式

• 原因：考虑到在高并发之前没有任何连接的情况，机器可能会处于节电模式、高并发场景来临时可能导致处理不过来新的请求。

2.配置合理的 CPU 核数和选择合适的 CPU 主频

• 原因：
  CPU 核数越多，支持的并发也越高；
  CPU 主频越高，处理任务的速度越快。

（二）内存优化

1.合理配置数据库服务器内存的大小。

• 内存对 MySQL 数据库影响是非常大的。InnoDB 使用 InnoDB buffer pool 缓存数据、索引等内容，从而加快访问速度。因此 MySQL 运行的物理机上，内存配置也是比较重要的。
• 在应用数据库实例前，应该预估活跃的数据大小，然后根据这个合理配置数据库服务器内存的大小。

（三）磁盘优化

1.提高磁盘 IO，使用 SSD(固态硬盘) 或者 Pcle SSD 设备

• 对于事务性操作（OLTP） 的数据库，一般场景是 IO 密集型的操作。因此，对于这类情况，应该把更多的注意力放在提高磁盘 IO 上。
• 使用 SSD(固态硬盘) 或者 Pcle SSD 设备

二、系统层面优化—了解即可

• 当硬件层优化的差不多以后，系统层部分配置也应该去做一些优化。这里介绍部分系统层面的优化方法

（一）调整 I/O 调度算法

1.I/O 调度算法建议使用：deadline/noop

• MySQL 运行的物理机上，I/O 调度算法建议使用：deadline/noop，尽量不使用 CFQ。

（二）文件系统选择

1.优先选用 xfs 或 ext4，坚决不用 ext3。

• 原因是： ext3 在 fsck 时需要耗费大量时间，文件越多，时间越长。

（三）调整内核参数

1.降低使用 swap 的概率，但是尽量不要设置为 0，可能引起 OOM。

• vm.swappiness ≤ 10

2.指定处理缓存脏页

• vm.dirty_ratio ≤ 5
• 这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如10%），系统不得不开始处理缓存脏页（因为此时脏页数量已经比较多，为了避免数据丢失需要将一定脏页刷入外存）；在此过程中很多应用进程可能会因为系统转而处理文件 IO 而阻塞。

3.指定缓存的脏页异步地刷入外存

• vm.dirty_background_ratio ≤ 10
• 避免因为 IO 压力瞬间飙升导致内核进程卡死，操作系统 hung 住。这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时，就会触发 pdflush/flush/kdmflush 等后台回写进程运行，将一定缓存的脏页异步地刷入外存

三、MySQL优化—掌握

性能影响原因

• 性能针对的是查询语句，其他插入、修改等语句不太会影响到服务器的性能
• 影响服务器性能的几个方面
  • 1.服务器硬件
  • 2.服务器的操作系统
  • 3.数据库存储引擎的选择
  • 4.数据库参数配置
  • 5.数据库结构设计和SQL语句
• SQL性能下降原因
  • 查询语句写的不好：索引优化可以改善
  • 索引失败
  • 关联查询大多join
  • 服务器调优及各个参数设置
  • 用select 用*选取全部的数据，这样很容易导致索引失效

SQL加载顺序

• 手写SQL的顺序，和机读顺序不同

  SELECT DISTINCT 
  	<SELECT _list>
  FROM
  	<left_table>
  JOIN  <right_table> ON <join_codition>
  WHERE
  	<where_condition>
  GROUP BY
  	<group_by_list>
  HAVING
  	<having_condition>
  ODER BY
  	<oder_by_condition>
  LIMIT <LIMIT number>
  

• 机读SQL顺序

  FROM  <left_table>
  ON <join_codition>
  <join_type> JOIN <right_table>
  WHERE <where_condition>
  GROUP BY <group_by_list>
  HAVING <HAVING_condition>
  SELECT 
  DISTINCT <select_list>
  ORDER BY <order_by_condition>
  LIMIT <limit_number>
  

MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘读书数据的时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈

什么查询是不值得优化的

• 一般该语句在响应时间所占没有超过%
• 优化的成本大于不优化的成本

（一）参数优化

• 在启动 MySQL 之前，一些参数合理的设置，可以大大提升 MySQL 的性能。这里就来介绍一部分相对比较重要的参数：

1.控制 InnoDB 缓存表和索引数据的内存区域大小

• innodb_buffer_pool_size=机器内存的 50-80%。
• 该参数控制 InnoDB 缓存表和索引数据的内存区域大小。对性能影响非常大，建议设置为机器内存的 50-80%。

2.InnoDB 的 redo 日志刷新方式

• innodb_flush_log_at_trx_commit
• 日志刷新方式，对 InnoDB 的影响会很大。

3.指定累积多少个事务后才将二进制日志 fsync 到磁盘

• sync_binlog

4.开启独立表空间。

• innodb_file_per_table

5.修改最大连接数

• max_connection
• 最大连接数。不能设置的过小，防止客户端连接失败；也不能设置的过大，防止数据库内存资源过多消耗。

6.设置慢查询时间阀值。

• long_query_time=10，默认10秒

7.建议这两个参数都设置为0。

• query_cache_type=0
  query_cache_size=0

（二）MySQL 设计优化

• 使用 InnoDB 存储引擎，不建议使用 MyISAM 存储引擎；
• 预估表数据量和访问量，如果数据量或者访问量比较大时，则需要提前考虑分库分表；
  • 主从复制—分库
  • 数据库的分区—分表
• 指定合适的数据库规范，在设计表、执行 SQL 语句时按照数据库规范来进行。

（三）SQL 语句优化

性能检测步骤总结

• 1.慢查询的开启并捕获
• 2.explain+慢SQL分析
• 3.show profile查询SQL在MySQL服务器里面的执行细节
• 4.SQL数据库服务器的参数调优

优化方向

• 索引优化
• join语句优化
• order by排序优化
• limit分页查询
• 用啥数据取啥数据select 字段1，字段2…比select * …好

总结

• 索引优化：

  • 第一：条件尽量使用主键索引，全值索引，遵循最左前缀，设置合理的索引、不要在字段上做计算
  • 第二：取什么要什么，最好带索引的字段
  • 第三：避免使用范围查询、模糊查询、!=、null、or
  • 第四：对于字符串字段，查询条件一定要带引号（否则会内部隐式转换，失去索引效果）

• join语句优化

  • 方法一：尽量保证小表是驱动表（扫描行数少），让被驱动的表有索引（添加正确索引，让 BNL变成 NLJ ，可以提高 join 的效率
    • left join：让左表是小表，并向右表添加索引
    • right join：让右表是小表，向左表添加索引
    • inner join：向大表建索引
  • 方法二：利用临时表
    • 创建临时表，然后在临时表中的关联字段上添加索引，然后通过临时表来做关联查询。

• order by排序优化

  • 优化策略调整MySQL参数
    • 增加sort_buffer_size参数设置
    • 增大max_lenght_for_sort_data参数的设置
  • 提高order by的速度
    • order by时select * 是一个大忌,只写需要的字段
    • order by 语句使用索引最左前列、或使用where子句与order by子句条件组合满足索引最左前列

• limit分页查询

  • 情况一： 根据自增且连续主键排序的分页查询
    • 将limit换成主键排序+limit步数
    • select a,b,c from t1 order by a limit 10
  • 情况二： 查询根据非主键字段排序的分页查询
    • 让排序和分页操作先查出主键，然后通过主键
    • 让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键
    • select a,b,c from t1 f inner join (select id from t1 order by a limit 99000,2) g on f.id = g.id;