Mysql5.7 性能优化

简介

MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如：

• 通过优化文件系统提高磁盘I/O的读写速度；
• 通过优化操作系统调度策略提高MySQL在高负荷情况下的负载能力；
• 优化表结构、索引、查询语句等使查询响应更快。

在MySQL中，可以使用SHOW STATUS语句查询一些MySQL数据库的性能参数。

优化查询

分析查询语句

• MySQL中提供了EXPLAIN语句和DESCRIBE语句用来分析查询语句。

使用索引查询

索引失效的情况

• 使用LIKE关键字的查询语句
  • 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用
• 使用多列索引的查询语句
  • MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中第1个字段时，索引才会被使用。
• 使用OR关键字的查询语句
  • 查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

优化子查询

• 子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。
• 可以使用连接（JOIN）查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能会更好。

优化数据库结构

将字段很多的表分解成多个表

• 对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢

增加中间表

增加冗余字段

• 表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多

如果要查询一个员工所在部门的名称，就必须从staff表中查找员工所在部门的编号（department_id），然后根据这个编号去department表查找部门的名称。如果经常需要进行这个操作，连接查询会浪费很多时间。可以在staff表中增加一个冗余字段department_name，该字段用来存储员工所在部门的名称，这样就不用每次都进行连接操作了。

• 冗余字段会导致一些问题。比如，冗余字段的值在一个表中被修改了，就要想办法在其他表中更新该字段。否则会使原本一致的数据变得不一致。否通过增加冗余来提高数据库性能要根据实际需求综合分析。

优化插入记录的速度

MyISAM引擎的表

• 禁用索引
  • 可以在插入记录之前禁用索引，数据插入完毕后再开启索引
• 禁用唯一性检查
  • 可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启
• 使用批量插入
  • 多条insert语句合并成一条
• 使用LOAD DATA INFILE批量导入

InnoDB引擎的表

• 禁用唯一性检查
• 禁用外键检查
• 禁止自动提交

分析表、检查表和优化表

分析表主要是分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新造成的空间浪费

分析表

• MySQL中提供了ANALYZE TABLE语句分析表
• 使用ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁

检查表

• 使用CHECK TABLE语句来检查表,能够检查InnoDB和MyISAM类型的表是否存在错误

优化表

• 使用OPTIMIZE TABLE语句来优化表
• OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段
• 通过OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片。OPTIMIZETABLE语句在执行过程中也会给表加上只读锁。

• 一个表使用了TEXT或者BLOB这样的数据类型，若已经删除了表的一大部分，或者已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间
• 在多数的设置中，根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量的更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定的表运行。

优化MySQL服务器

优化MySQL服务器主要从两方面来优化，一方面是对硬件进行优化；另一方面是对MySQL服务的参数进行优化。这部分内容需要较全面的知识，一般只有专业的数据库管理员才能进行这一类的优化。

优化服务器硬件

• 配置较大的内存
• 配置高速磁盘系统
• 合理分布磁盘I/O
• 配置多处理器

优化MySQL的参数

MySQL服务的配置参数都在my.cnf或者my.ini文件的［mysqld］组中

• ·key_buffer_size：表示索引缓冲区的大小
  • 如果这个值太大，就会导致操作系统频繁换页，也会降低系统性能。
• ·query_cache_size：表示查询缓冲区的大小
• … …

Mysql 5.7的新特性

临时表性能优化

• MySQL 5.7新增了CREATE TABLESPACE语句来创建一个通用表空间。这个功能可以让用户自由地选择表和表空间之间的映射。
• MySQL 5.7使用了独立的临时表空间来存储临时表数据，但不能是压缩表。临时表空间在实例启动的时候进行创建，shutdown的时候进行删除，即所有非压缩的innodb临时表提供一个独立的表空间
• 在MySQL 5.7中，临时表在连接断开或者数据库实例关闭的时候，会进行删除

服务器语句超时处理

• 在MySQL 5.7中可以设置服务器语句超时的限制，单位可以达到毫秒级别。
• 当中断的执行语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。
• 默认情况下，MAX_EXECUTION_TIME的值为0，代表没有时间限制。通过上面的设置后，如果SELECT语句执行超过2000毫秒，语句将会被终止。

支持创建全局通用表空间

• MySQL 5.7支持创建全局通用表空间，全局表空间可以被所有的数据库的表共享，而且相比于独享表空间，使用手动创建共享表空间可以节约元数据方面的内存

来源

MySQL 5.7从入门到精通 读书笔记