MySQL 5.7（八）【性能优化】

最新推荐文章于 2024-04-24 23:02:45 发布

Convallaria@GJH

最新推荐文章于 2024-04-24 23:02:45 发布

阅读量716

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/Nigori_gjh/article/details/106845516

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

第八篇

- 性能优化

性能优化

一、优化简介

查询数据库性能参数：

show status like 'value';

性能参数（value）：
●Connections：连接MySQL服务器的次数。
●Uptime：MySQL服务器的上线时间。
●Slow_queries：慢查询的次数。
●Com_select：查询操作的次数。
●Com_insert：插入操作的次数。
●Com_update：更新操作的次数。
●Com_delete：删除操作的次数。

二、优化查询

语法结构：

explain [extended] select select_options;

extended：执行结果将产生附加信息。
select_options：select 语句的查询选项，包括 from，where 。

@example：

mysql> explain select * from fruits;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | fruits | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

参数解析：
● id: select 的查询序列号（识别符）。
● select type: select 语句的类型，取值如下:
    ➢ simple 简单查询，其中不包括连接查询和子查询;
    ➢ primary 主查询，或者是最外层的查询语句;
    ➢ union 连接查询的第2个或后面的查询语句;
    ➢ dependent union 连接查询中的第2个或后面的 select 语句，取决于外面的查询;
    ➢ union result 连接查询的结果；
    ➢ subquery 子查询中的第1个 SELECT 语句；
    ➢ dependent subquery 子查询中的第1个 select，取决于外面的查询;
    ➢ derived 导出表的 select (from子句的子查询)。
● table: 表示查询的表。
● type：表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型:
    ➢system
    该表是仅有一行的系统表。这是const连接类型的一个特例。
    ➢const
    数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对待。const 表只读取一次，查询速度快。const 用于使用常数值比较 primarykey 或 unique 索引的所有部分的场合。
。。。

（一）使用索引查询特例

1、使用 like 关键字的查询语句

匹配字符串时，只有“ % ”不在第一个位置，索引才会生效。

2、使用多列索引的查询语句

一个索引可以包括16个字段。只有查询条件中使用了这些字段中第1个字段时，索引才会生效。

3、使用OR关键字的查询语句

查询语句的查询条件中只有 or 关键字，且 or 前后的两个条件中的列都是索引时，查询中索引生效。否则索引不生效。

（二）优化子查询

使用连接 (JOIN) 查询来替代子查询。连接查询不需要建立临时表，如果查询中使用索引的话，性能会更好。连接的高效率，是因为不需要在内存中创建临时表来完成查询工作。

三、优化数据库结构

（一）将字段很多的表分解成多个表

某些使用频率较低的字段分理出来形成新表，在查询详细信息是，使用 join 连接查询。

（二）增加中间表

把需要经常联合查询的数据插入到中间表中，然后对中间表进行查询。

（三）适量增加冗余字段

根据实际项目可以适当增加冗余字段，以增加少量磁盘消耗换取查询速度的加快。但是要注意对其他表中冗余数据的同步更新。

（四）优化插入记录的速度

对于 MyISAM 引擎的表，优化方法：

1、禁用索引

对于 非空表，插入记录之前禁用索引，插入结束后开启索引。

禁用索引

alter table table_name disable keys;

开启索引

alter table table_name enable keys;

对空表批量导入数据，不需要进行此操作，因为 MyISAM 引擎的表是在导入数据之后才建立索引的。

2、禁用唯一性检查

插入数据时，会进行唯一性校验，降低了插入速度。插入记录之前禁用检查，插入结束后开启检查。

禁用唯一性检查

set unique_checks=0;

开启唯一性检查

set unique_checks=1;

3、使用批量插入（不赘述）

4、使用 load data infile 批量导入数据

load data infile 批量导入数据要快于 insert。

对于 InnoDB 引擎的表，优化方法：

1、禁用唯一性检查（与MyISAM 用法一样）

2、禁用外键检查

禁用外键检查

set foreign_key_checks=0;

恢复外键检查

set foreign_key_checks=1;

3、禁用自动提交

插入数据之前禁用事务的自动提交，数据导入完成后恢复自动提交操作。

禁用自动提交

set autocommit=0;

恢复自动提交

set autocommit=1;

（五）分析表、检查表、优化表

1. 分析表（分析关键字的分布）

analyze [local | no_write_to_binlog] table table_name[,table_name] ...

local 关键字是 no_write_to_binlog 关键字的别名，在执行过程中均不写入二进制日志。
analyze table 分析过程中，数据库自动对表加只读锁，其语句能够分析 InnoDB 、BDB 和 MyISAM 类型的表。

mysql> analyze table student;
+-----------------------+---------+----------+----------+
| Table                 | Op      | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| db_mysql_test.student | analyze | status   | OK       |
+-----------------------+---------+----------+----------+
1 row in set (0.60 sec)

● Table：表名称
● Op：执行的操作，analyze 表示进行分析操作
● Msg_ type：信息类型，其值通常是状态(status)、信息(info)、注意(note) 、警告( warning)和错误(error) 之一
● Msg_ text：显示信息

2. 检查表（检查表是否存在错误）

check table 可以检查 InnoDB 和 MyISAM 类型的表是否存在错误，对 MyISAM 型表，其语句会更新关键字统计数据，而且其语句还可以检查视图是否有错误。

check table table_name [,table_name] ... [option] ...
option = {quick | fast | medium | extended | changed}

option 参数取值 QUICK、FAST、 MEDIUM、EXTENDED 和 CHANGED
● QUICK：不扫描行，不检查错误的连接。
● FAST：只检查没有被正确关闭的表。
● CHANGED：只检查上次检查后被更改的表和没有被正确关闭的表。
● MEDIUM：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点。
● EXTENDED：对每行的所有关键字进行一个全面的关键字查找。确保表是100%一致的，但是花的时间较长。

option只对MyISAM类型的表有效，check table 执行过程中也会给表加上只读锁。

3. 优化表（消除删除/更新造成的空间浪费）

optimize table语句对 InnoDB 和 MyISAM 型表有效。但是，其语句只能优化表中的VARCHAR、BLOB 或 TEXT 类型的字段。

optimize [local | no_write_to_binlog] table table_name[,table_name] ...

通过optimize table语句可以消除删除/更新造成的文件碎片。其语句在执行过程中也会给表加上只读锁。
注意：
一个表使用了 TEXT 或 BLOB 这样的数据类型，如果已经删除了表的一大部分，或已经对含有可变长度行的表（含有VARCHAR，BLOB或TEXT列的表）进行了很多更新，则应使用optimize table来重新利用未使用的空间，并整理数据文件的碎片。在多数的设置中，根本不需要运行optimize table。即使对可变长度的行进行了大量的更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定的表运行。

四、优化MySQL服务器

（一）优化服务器硬件

(1) 配置较大的内存。可以通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘I/O。
(2) 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。
(3) 合理分布磁盘I/O, 把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。
(4) 配置多处理器，MySQL是多线程的数据库，多处理器可同时执行多个线程。

（二）优化MySQL参数

MySQL服务的配置参数都在 my.cnf或 my.ini 文件的 [MySQLd] 组中。（参考）

● key_ buffer size：表示索引缓冲区的大小。索引缓冲区所有的线程共享。增加索引缓冲区可以得到更好处理的索引(对所有读和多重写)。当然，这个值也不是越大越好，它的大小取决于内存的大小。如果这个值太大，导致操作系统频繁换页，也会降低系统性能。
● table cache：表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。
● query_cache_size：表示查询缓冲区的大小。该参数需要和query_cache_ type 配合使用。当query_cache_type值是0时，所有的查询都不使用查询缓冲区。但是query_cache_type=0 并不会导致MySQL释放query_ cache_ size 所配置的缓冲区内存。当query_ cache_ type=1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定SQL_ NO_ CACHE，如SELECT SQL_NO_CACHE＊ FROM tbl_ name。当query_ cache_ type=2 时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓冲区。使用查询缓冲区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。
● sort_buffer_ size：表示排序缓存区的大小。这个值越大，进行排序的速度越快。
● read_ buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_ buffer_ size=n 可以临时设置该参数的值。
● read_rnd_buffer_size：表示为每个线程保留的缓冲区的大小，与read_ buffer_ size 相似。但主要用于存储按特定顺序读取出来的记录。也可以用SET SESSION read_rnd_buffer_size=n 来临时设置该参数的值。如果频繁进行多次连续扫描，可以增加该值。
● innodb_ buffer_ pool_ size：表示InnoDB 类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。
● max_ connections：表示数据库的最大连接数。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
● innodb_ flush_ log_ at_ trx_ commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。值为0时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘;值为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘;值为2时表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该参数的默认值为1。默认值1安全性最高，但是每次事务提交或事务外的指令都需要把日志写入( flush)硬盘，是比较费时的; 0值更快一点，但安全方面比较差; 2值日志仍然会每秒写入到硬盘，所以即使出现故障，一般也不会丢失超过1~2秒的更新。
● back_ log：表示在MySQL暂时停止回答新请求之前的短时间内，多少个请求可以被存在堆栈中。换句话说，该值表示对到来的TCP/IP连接的侦听队列的大小。只有期望在一个短时间内有很多连接，才需要增加该参数的值。操作系统在这个队列大小上也有限制。设定back_log高于操作系统的限制将是无效的。
● interactive_ timeout：表示服务器在关闭连接前等待行动的秒数。
● sort_ buffer_ size：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2097144 (2MB)。
● thread_ cache_ size：表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。
● wait_timeout：表示服务器在关闭一个连接时等待行动的秒数。默认数值是28800。
除上述参数以外，还有innodb_ log_buffer_ size、 innodb_ log_ file_ size 等参数。配置完参数以后，需要重新启动MySQL服务才会生效。

为什么查询语句中的索引|没有起作用?
在WHERE条件的LIKE关键字匹配的字符串以“%”开头，这种情况下索引不会起作用。又如，WHERE条件中使用OR关键字连接查询条件，如果有1个字段没有使用索引，那么其他的索引也不会起作用。如果使用多列索引，但没有使用多列索引中的第1个字段，那么多列索引也不会起作用。

Convallaria@GJH

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 5.7（八）【性能优化】

第八篇性能优化一、优化简介二、优化查询性能优化一、优化简介查询数据库性能参数：show status like 'value';性能参数（value）：●Connections：连接MySQL服务器的次数。●Uptime：MySQL服务器的上线时间。●Slow_queries：慢查询的次数。●Com_select：查询操作的次数。●Com_insert：插入操作的次数。●Com_update：更新操作的次数。●Com_delete：删除操作的次数。二、优化查询语法结构：exp
复制链接

扫一扫

专栏目录