MySQL性能优化

性能优化

性能优化的目的是为了使MySQL数据库运行速度更快、占用的磁盘空间更小。

性能优化有很多方面：
- 优化查询速度
- 优化更新速度
- 优化MySQL服务器等
- 优化数据库结构

优化简介

如果MySQL数据库中需要进行大量的查询操作，那么就需要对查询语句进行优化。对于耗费时间的查询语句进行优化，可以提高整体的查询速度。

如果连接MySQL数据库的用户很多，那么就需要对MySQL服务器进行优化。否则，大量的用户同时连接MySQL数据库，可能会造成数据库系统崩溃。

查询MySQL性能

通过查询的结果分析MySQL数据库的性能，然后根据相应的性能优化。

数据库管理员可以使用show status语句查询MySQL数据库的性能

mysql> show status like 'value';

value取值:
- connections: 连接MySQL服务器的次数
- uptime: MySQL服务器的上线时间
- slow_queries: 慢查询的次数
- com_select: 查询操作的次数
- com_insert: 插入操作的次数
- com_update： 更新操作的次数
- com_delete： 删除操作的次数

对于InnoDB表，有一些专门的value参数：
- innodb_rows_read: select查询的次数
- innodb_rows_inserted: insert语句插入的记录数
- innodb_rows_updated: update语句更新的记录数
- innodb_rows_deleted: delete语句删除的记录数

优化查询

查询是数据库中最频繁的操作，提高了查询速度可以有效的提高MySQL数据库的性能。

分析查询语句

MySQL中可以使用explain和describe语句来分析查询语句

explain select 语句

mysql> explain select * from student where id > 200;

输出对select语句的分析，各个字段的具体含义查看https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

describe语句的使用方法与explain语句是一样的，这两者的分析结果也是一样的。

但是 describe 更多用于查看表结构。

索引对查询速度的影响

生成索引

create index index_name on table(name)

NOTE
使用索引查询记录时，一定要注意索引的使用情况，如下三个例子：
- LIKE关键字配置的字符串不能以“%”开头
- 使用多列索引时，查询条件必须要使用这个索引的第一个字段
- 使用OR关键字时，OR关键字连接的所有条件都必须使用索引

优化子查询

很多查询中需要使用子查询。
子查询可以使查询语句很灵活，但子查询的执行效率不高。

子查询时，MySQL为内层查询语句的查询结果建立一个临时表，然后外查询在临时表中查询，查询完毕MySQL撤销这些临时表。因此，子查询的速度会受一定影响。

在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快。

优化数据库结构

数据库结构是否合理，需要考虑
是否存在冗余；
对表的查询和更新的速度、表中字段的数据类型是否合理等多方面的内容；

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

适用：字段特别多且有些字段的使用频率很低的表，可以将其分解成多个表。

增加中间表

有时需要经常查询某两个表中的几个字段。

经常进行联表查询，会降低MySQL数据库的查询速度。

对于这种情况，可以建立中间表来提高查询速度。

方法：
先分析经常需要同时查询哪几个表中的哪些字段。然后将这些字段建立一个中间表，并将原来那几个表的数据插入到中间表中，之后就可以使用中间表来进行查询和统计了。

增加冗余字段

设计数据库表时尽量让表 达到三范式。

但是，有时候为了提高查询速度，可以有意识地在表中增加冗余字段。

分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。
从数据库性能来看，增加少量的冗余来提高数据库的查询速度是可以接受的。
是否通过增加冗余来提高数据库的性能，这要根据MySQL服务器的具体要求来定，如果磁盘空间很大，可以考虑牺牲一点磁盘空间。

优化插入记录的速度

插入记录时，索引、唯一性校验都会影响到插入记录的速度。
一次插入多条记录和多次插入记录所耗费的时间是不一样的

禁用索引

插入记录时，MySQL会根据表的索引对插入的记录进行排序。

插入大量数据时，排序会降低插入的速度。

在插入前禁用索引，插入完毕后开启索引。

ALTER TABLE 表名 DISABLE KEYS；

ALTER TABLE 表名 ENABLE KEYS;

对于先创建的表，可以先不创建索引，等到记录都导入以后，再创建索引

禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种校验会降低插入记录的速度。

可以在插入记录前禁用唯一性检查，插入完毕后再开启。

SET UNIQUE_CHECKS=0;

SET UNIQUE_CHECKS=1;

优化INSERT语句

插入多条记录时，可以采用两种INSERT语句的方式

• INSERT插入多条记录

• INSERT插入一条记录，执行多次

执行多次会增加与数据库连接的次数，从而降低插入速度。

当插入大量数据时，最好使用一个INSERT语句。
如果能用load data infile语句导入更好。

分析表、检查表和优化表

分析表： 分析关键字的分布

analyze table 表名1 [,表名2...];

结果显示四列信息
- Table： 表示表名
- Op: 表示操作，有三个值（analyze check optimize）
- Msg_type: 表示信息类型，有四个值（status note warning error）??
- Msg_text: 显示信息

检查表： 检查表是否存在错误

check table 表名1 [,表名2...] [option];

check会有一个可选的option选项，分别是quick、fast、changed、medium、extended。这5个参数的执行效率依次降低。option只对MyISAM类型的表有效，对InnoDB类型表无效。

优化表： 消除删除或者更新造成的空间浪费

optimize table 表名1 [,表名2...];

优化对InnoDB和MyISAM类型的表都有效。但是只能优化表中的varchar、blob或text类型的字段
（如果一个表有以上三种类型的字段，那么更新、删除等操作就会造成磁盘空间的浪费。因为更新和删除操作后，以前分配的磁盘空间不会自动回收，使用optimize table语句就可以将这些磁盘碎片整理出来，以便回收再利用）

分析、检查、优化三条命令都会给表加上只读锁。

优化MySQL服务器

两方面理解

• 从硬件方面来进行优化

• 从MySQL服务的参数进行优化

优化服务器硬件

• 增加内存

• 提高硬盘的读写速度

• 在内存中为MySQL设置更多缓冲区

• 提供多块磁盘来存储数据。
  因为可以从多个磁盘上并行读取数据。 通过镜像机制可以将不同计算机上的MySQL服务器进行同步，这些MySQL服务器中的数据都是一样的。通过不同的MySQL服务器来提供数据库服务，这样可以降低单个服务器的压力，从而提供MySQL的性能

优化MySQL的参数

内存中会为MySQL保留部分缓冲区，这些缓存区可以提供MySQL数据库的处理速度

在MySQL配置文件中设置
/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

key_buffer_zie: 索引缓冲区的大小

table_cache: 表示同时打开的表的个数

query_cache_size: 查询缓冲区的大小。 只有在select中使用SQL_CACHE关键字，查询缓冲区才会使用，如 select SQL_CACHE * from student; 适合操作少且经常执行的查询操作中。  可以使用flush query cache 语句来清理查询缓冲区中的碎片

query_cache_type: 表示查询缓冲区的开启状态。 0 关闭， 1 开启， 2 按要求使用查询缓冲区

max_connections: 数据库的最大连接数

sort_buffer_size: 排序缓冲区的大小，越大越好

read_buffer_size: 表示为每个线程保留的缓冲区的大小。当线程需要从表中连续读取记录时需要用到这个缓冲区。set session read_buffer_size=n 可临时设置该值

read_rnd_buffer_size: 表示为每个线程保留的缓冲区的大小。但主要用于存储按特定顺序读取出来的记录。用set session read_rnd_buffer_size=n 临时设置该值

innodb_buffer_pool_size: 表示InnoDB类型的表和索引的最大缓冲

innodb_flush_log_at_trx_commit: 表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。 有三个值： 0，表示每隔1秒写一次（日志&&磁盘）；1，表示每次提交事务时写一次（日志&&磁盘）；2表示每次提交事务时将数据写入日志，每隔1秒将日志写入磁盘。 1 最合理。

合理的配置这些参数可以提供MySQL服务器的性能。配置完成后，需要重启MySQL服务才会生效。