性能优化
性能优化的目的是为了使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服务才会生效。