mysql性能调优

2021-11-30 02:59:06

1. 减少不必要的商业需求

例如：实时数据

2. 系统架构设计的影响

造成应用层设计不合理的主要原因还是面向对象的思维太过深入，以及为了减少自己代码的开发逻辑和对程序接口的过度依赖。

(1) 不适合在数据库中存放的数据：

  • 二进制多媒体数据

  • 流水队列数据（经常insert，update和delete）

  • 超大文本数据

(2) 合理利用应用层的cache

访问频繁但很少更改的数据适合用cache

(3) 精简数据层的实现

  • 减少对循环嵌套的依赖，合理利用sql语句

  • 减少对sql语句的过度依赖（减少IO操作）

  • 减少sql语句的重复执行

3. Query语句的调优

(1) sql语句在Mysql中执行的大致过程：

当Mysql Server的连接线程接收到Client端发送过来的sql请求后，会经过一系列的分解Parse，进行相应的分析。然后，Mysql会通过查询优化器模块（Optimizer）根据该sql所涉及的数据表的相关统计信息进行计算分析，然后在得出一个Mysql认为最合理最优化的数据访问方式，也就是我们常说的执行计划，然后再根据所得到的执行计划通过调用存储引擎接口来获取相应数据。然后再将存储引擎返回的数据进行相关处理，并以Client端所要求的格式作为结果集返回给Client端的应用程序。

(2) 基本优化思路

  1. 优化更需要优化的Query（高并发低消耗的查询）

  2. 定位优化对象的性能瓶颈（IO，CPU消耗分别对应数据访问，数据运算）

  3. 明确的优化目标

  4. 从explain入手（了解执行计划）

(3) 基本原则

  1. 多使用profile

  2. 永远用小结果集驱动大的结果集

  3. 尽可能在索引中完成排序

  4. 只取出自己需要的cloumns

  5. 仅仅使用最有效的过滤手段

  6. 尽可能避免复杂的join和子查询

(4) 通过开启慢查日志发现有问题的sql语句

开启慢查日志

set global slow_query_log=on;

设置日志存储路径

set global slow_query_log_file='path';

设置记录不用到索引的查询

set global log_queries_not_using_indexes=on;

设置超过时间的查询

set global long_query_time=1;

通过自带的mysqldumpslow分析日志 
通过功能更加丰富的pt-query-digest分析日志

  • 查询次数多且每次查询占用时间长的SQL,通常为pt-query-digest分析的前几个查询

  • IO大的SQL,注意pt-query-digest分析中的Rows examine项

  • 未命中索引的SQL,注意pt-query-digest分析中Rows examine和Rows send的对比

pt-query-digest 日志文件

(5) 内置函数的优化

  • 像max(),min()等需要排序的函数使用索引进行优化

  • count(*) == count(value or null)

(6) join语句的优化

Mysql只使用一种连接算法：Nested Loop Join 
1. 尽可能减少join语句中nested loop的循环总次数，即用小结果集驱动大结果集。 
2. 优先优化循环的内层操作。 
3. 保证join语句中被驱动表上join条件字段已经被索引。 
4. 当无法保证第三点时，不要吝惜join buffer的设置。

(7) order by的优化

① 在排序字段上建立有序索引。

② 当无法满足第一点时，会使用排序算法

  1. Extra: Using filesort 
    当explain中只出现Using filesort时表示使用了第一种算法。该算法在取得第一个表之后，先根据排序条件将该字段与行指针放入Sort Buffer中进行排序。然后再利用排序后的数据根据行指针返回第一个表的数据，作为驱动结果集来连接到第二个表。可以看出多了一步IO操作去连接回第一张表。

  2. Extra: Using temporary; Using filesort 
    当我们的排序操作在join连接之后时，就会出现使用Using temporary; Using filesort，Mysql会先对表进行连接操作，然后将结果集放入临时表，再进行filesort，最后得到有序的结果集。

显而意见，第二种算法更加高效，用空间换取时间，减少了IO操作。

③ 使用配置

  1. 加大max_length_for_sort_data 
    当我们返回字段的最大长度小于这个参数时，Mysql就会选择第二种算法。

  2. 加大sort_buffer_size 
    让Mysql在排序过程中对需要排序的数据进行分段。

(8) group by的优化

① 有索引的情况下

  1. 使用松散（loose）索引扫描实现group by，即Mysql完全利用索引扫描来实现group by 
    Extra: Using where, Using index for group-by 
    需要的条件： 

    1. group by条件字段必须在同一个索引中最前面的连续位置。

    2. 在适用group by的同时，只能适用max()和min()两个聚集函数。

    3. 如果引用到了该索引中group by条件之外的字段条件时，必须以常量形式存在（不能是范围）。

  2. 使用紧凑（tight）索引扫描实现group，当group by不是以索引中的第一个开始，但是有条件where以常量形式引用到了索引中的第一个。通过该常量来引用缺失的索引键。 
    Extra: Using where, Using index

② 没有索引的情况下

  1. 使用临时表 
    Extra: Using where, Using index, Using temporary, Using filesort 
    从执行计划可以很明显的看出来：通过索引找到我们需要的数据，然后创建了临时表，又进行了排序操作，最终才得到我们需要的group by结果。

③ 优化思路

  1. 尽量利用索引

  2. 提供足够大的sort_buffer_size

(9) 合理利用索引

① B-Tree索引

  • 在innodb中通过主键来访问数据效率是非常高的。

Hash索引

  • 优点：能通过hash算法得到存储位置，效率很高。

  • 缺点：由于自身限制同时存在很多弊端。 

    • 不能满足范围查询（因为hash算法转换后的值的大小与原值不对应）

    • 不同用来避免数据的排序

    • 不同利用部分索引键查询

    • 由于冲突，必须每次都对表扫描

    • 当有大量hash值相等时效率低

② FullText索引

主要用来代替效率低下的like'%%'操作。

③ R-Tree索引

主要用来解决空间数据检索的问题。在Mysql中，支持一种用来存放空间信息的数据类型GEOMETRY。

④ 索引的利弊

  • 提高检索速率，降低IO成本。

  • 降低数据排序成本（B-Tree的有序性）

  • 提高更新操作的IO成本。

  • 浪费空间。

⑤ 什么时候应该用索引

  • 较频繁的查询字段

  • 唯一性较强的字段（太差的话就算频繁查询也不适合）

  • 更新频繁的字段不适合做索引

⑥ 单键索引还是组合索引

做到尽量让一个索引被多个query语句所利用,尽量减少同一个表上索引的数量。

⑦ 索引相关sql语句

创建索引：

create index 索引名 on 表名(字段名列表);

强制使用索引：

select * from 表 force index(索引名) where ...;

⑧ 索引优化

  1. 如何选择组合索引：离散程度大的列在前

  2. 主键已经默认是唯一索引了，所以primay key的主键不用再设置unique唯一索引了

  3. 冗余索引，是指多个索引的前缀列相同，或者在联合索引中包含了主键的索引，因为innodb会在每个索引后面自动加上主键。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

  4. 不使用NOT IN和<>操作,NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

  5. mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

  6. 查询重复，冗余索引的工具pt-duplicate-key-checker

pt-duplicate-key-checker -u root -p '密码' -h ip地址

4. 数据库结构优化

(1) 选择合适的数据类型

  1. 使用可存下数据的最小的数据类型

  2. 使用简单地数据类型，Int在mysql的处理比varchar简单

  3. 尽可能使用not null定义字段

  4. 尽量少用text，非用不可最好分表

① 用int来存储日期时间以节省空间：

  1. 插入时使用UNIX_TIMESTAMP()将datetime转成int

  2. 查询时使用FROM_UNIXTIME()将int转成datetime

② 用bigint来存储ip地址：

  1. 插入时使用INET_ATON()来将ip地址转成bigint

  2. 查询时使用INET_NTOA()来将bigint转成ip地址

(2) 表的范式化

优化到第三范式以上。

(3) 反范式化

为了优化查询效率，以空间换时间。

(4) 表的垂直拆分

① 把原来有很多列的表拆分成多个表，原则是：

  1. 把不常用的字段单独存放到一个表中

  2. 把大字段独立存放在一个表中

  3. 把经常使用的字段放在一起

(5) 表的水平拆分

① 为了解决单表数据量过大的问题，每个水平拆分表的结构完全一致

方法: 
1. 对id进行hash运算，可以取mod 
挑战: 
1. 跨分区进行数据查询 
2. 统计及后台报表操作

5. 系统配置优化

(1) 操作系统的优化

  1. 网络方面，修改/etc/sysctl.conf文件，增加tcp支持的队列数，减少断开连接时，资源的回收。

  2. 打开文件数的限制。修改/etc/security/limits.conf文件，增加一下内容以修改打开文件数量的限制。

  3. 关闭iptables，selinux等防火墙软件。使用硬件防火墙。

(2) Mysql配置文件

Mysql可以通过启动时指定参数和使用配置文件两种方法进行配置。大多数情况下默认的配置文件位于/etc/my.cnf或/etc/mysql/my.cnf，查找配置文件的顺序可以用该命令获取：

usr/sbin/mysqld --verbose --help | grep -A 1 'Defaulto options'

① 重要参数：

  1. innodb_buffer_pool_size配置innodb的缓冲池，如果数据库中只有innodb表，则推荐配置为总内存的75%。

  2. innodb_flush_log_at_trx_commit(0|1|2)决定数据库事务刷新到磁盘的时间，0是每秒刷新一次。1是每次提交刷新一次，安全性最高。2是先提交到缓冲区，再每秒刷新一次，效率最高。

  3. innodb_file_per_table控制innodb每一个表使用独立的表空间，默认是OFF，造成IO瓶颈。推荐设置ON。

② 常用参数：

  1. innodb_buffer_pool_instances配置缓冲池的个数，默认是一个。

  2. innodb_log_buffer_size配置日志缓冲区的大小，一般不用太大。

  3. innodb_read_io_threads(默认是4) 
    innodb_write_io_threads(默认是4)决定innodb读写的IO进程数。

  4. innodb_stats_on_metadata配置mysql在什么情况下刷新innodb表的统计信息。











本文转自 知止内明 51CTO博客，原文链接：http://blog.51cto.com/357712148/1962354，如需转载请自行联系原作者
上一篇：IntelAMT 固件密码绕过登录漏洞分析与实战


下一篇：less 学习-变量