
SHOW STATUS LIKE 'param'
 
/*
param:
· Connections # 连接 MySQL 服务器的次数
· Uptime # MySQL 服务器的上线时间
· Slow_queries # 慢查询的次数
· Com_select # 查询操作的次数
· Com_insert # 插入操作的次数
· Com_update # 更新操作的次数
· Com_dalete # 删除操作的次数
*/

2 优化查询

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

2.2.1 分析查询语句

通过对查询语句分析，可以了解查询语句执行情况，找出查询语句的瓶颈，从而优化查询语句。MySQL 中提供了 EXPLAIN 语句和 DESCRIBE 语句，用来分析查询语句。

EXPLAIN selection_statement


mysql> EXPLAIN SELECT * FROM day_expenditure \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: day_expenditure
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 23
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
 
ERROR:
No query specified
 
mysql> EXPLAIN SELECT * FROM day_expenditure WHERE id = 666 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: day_expenditure
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.02 sec)
 
ERROR:
No query specified
 
 
/*
select_type: 表示查询语句的类型，有多种取值：
SEMPLE 表示简单查询，其中不包括连接查询和子查询；
PRIMARY 表示主查询，或是最外层的查询语句；
UNION 表示连接查询的第一个查询语句；
DEPENDENT UNION 表示连接查询中第二个及往后的查询语句，取决于 UNION 查询；
SUBQURY 表示子查询中最外层的查询语句；
DEPENDENT SUBQURY 表示子查询中内层的查询语句。
type: 表示表的连接类型，有多种取值：
system 表示只有一行的系统表，是 const 的一个特例；
const 表示数据表最多只有一行匹配，将在查询开始时被读取，并在余下的查询优化中作为常量对待，const 用于使用常数比较主键或唯一索引的场合；
eq_ref 用于使用等号比较带索引的列；
ref 用于等号或不等号；
ref_or_null 解决子查询中经常使用该类型优化连接；
index_merge 该连接类型表示使用了索引合并优化方法；
range 表示范围查询，之间所给定范围的行；
index 表示只扫描索引树；
ALL 表示查询的是所有数据。
possible_keys: 指出可能使用哪些索引在该表中找数据。
key: 表示查询实际使用到的索引。想要强制 MySQL 使用或忽略 possible_keys 中的索引，可以在查询中使用 FORCE INDEX index_name 、USE INDEX index_name 或 IGNORE INDEX [index_name]。
key_len: 实际使用的索引的字节长，可以通过该值确定实际使用一个多列索引中的几个字段。
rows: 实际扫描的行数。
Extra: 查询时一些额外的信息。
*/


DESCRIBE select_statement
 
# DESC select_statement
 
# 该语句效果和 EXPLAIN 一样，都可以用来查看查询语句的执行细节

2.2.2 索引对查询速度的影响

MySQL 中提高性能的一个有效方法是对数据表设计合理的索引。索引提供了高效访问数据的方法，可以加快查询速度，因此，索引对查询的速度有着至关重要的影响。使用索引可以快速定位表中的某条记录，从而提高数据库查询的速度、提高数据库的性能。

如果查询时没有使用索引，查询语句将全表扫描所有数据。在数据量大的情况下，这样查询的速度太慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少扫描的行数，达到提高查询速度的目的。

2.2.3 使用索引查询

索引可以提高查询的速度，但并不是使用带有索引的字段查询时索引都会起作用，有几种特殊情况，在这些情况下有可能使带有索引的字段查询时索引并没有起到作用：

（1）使用模糊查询并且不满足索引最左前缀原则

例如：在使用 LIKE 关键字进行查询的语句中，匹配字符串的第一个字符为 “%”；或在使用 REGEXP 关键字查询的语句中，使用 “$” 查询以某个字符结尾的字符串时，索引不会起作用。

（2）使用多列索引的查询语句且不满足索引最左前缀原则

MySQL 可以为多个字段创建索引。一个索引可以包括 16 个字段。对于多列索引，只有查询条件满足了最左前缀时，索引才会生效。

（3）使用 OR 关键字的查询

查询语句的查询条件有 OR 关键字，则 OR 前后的条件中的列都是索引列时，查询才使用索引。

2.2.4 优化子查询

MySQL 从 4.1 版本开始支持子查询，使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果可以作为另一个自 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要很多个步骤才能完成的 SQL 操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时， MySQL 需要为内层查询语句的查询结果建立一个临时表。然后外层查询从临时表中查询记录。查询完毕后再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

在 MySQL 中，可以使用连接查询来代替子查询，如果查询中使用索引，性能会更好。

3 优化数据库结构

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

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

3.2 增加中间表

对于需要经常使用联合查询的表，可以建立中间表，以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，将原来的联合查询改为对中间表的查询，以提高查询效率。

3.3 增加冗余字段

设计数据表时应该尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询效率。

表的规范程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。例如，员工的信息存储在staff 表中，部门信息存储在 department 表中。通过 staff 表中的 department_id 字段与 department 表建立关联关系。如果要查询一个员工所在部门的名称，必须从 staff 表中查找员工所在部门的编号（department_id），然后根据这个编号去 department 表中查找部门的名称。如果经常需要进行这个操作，连接查询会浪费很多时间，可以在 staff 表中增加一个 department_name ，该字段用来存储员工所在部门的名称，这样就不用每次都进行连接操作了。

冗余字段会导致一些问题。比如，冗余字段的值在一个表中被修改了，就要想办法在其它表中更新该字段，否则就会导致数据不一致。分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。从数据库性能来看，为了提高查询速度而增加少量的冗余大部分时候都是可以接受的。是否通过增加冗余来提高数据库性能，这要根据实际需求综合分析。

3.4 优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录的条数等。根据不同情况，可以分别进行优化。

对于 MyISAM 引擎的表，常见的优化方法如下：

（1）禁用索引

对于非空表，插入记录时，MySQL 会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。

ALTER TABLE tbname DISABLE KEYS

ALTER TABLE tbname ENABLE KEYS

（2）禁用唯一性检查

插入数据时，MySQL 会对插入的数据进行唯一性校验，这种唯一性校验也会降低插入记录的速度。为了降低这种情况对插入速度的影响，可以在插入之前禁用唯一性校验，插入之后再开启。

SET UNIQUE_CHECKS = 0

SET UNIQUE_KEYS = 1

（3）如果可以，使用 LOAD DATA INFILE 批量导入数据，否则，使用一条 INSERT 语句批量插入数据而不是多条 INSERT 语句分别插入数据

对于 Innodb 引擎的表，常见的优化方法如下：

（1）禁用唯一性检查

SET UNIQUE_CHECKS = 0

SET UNIQUE_KEYS = 1

（2）禁用外键检查

SET FOREIGN_KEY_CHECKS  = 0

SET FOREIGN_KEY_CHECKS = 1

（3）禁止自动提交

SET AUTOCOMMIT = 0

SET AUTOCOMMIT = 1

3.5 分析表、检查表和优化表

MySQL 提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布；检查表主要是检查表中是否存在错误；优化表主要是消除删除或更新造成的空间浪费。

（1）分析表

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbname [tbname2, ..., tbnamen]

LOCAL 是 NO_WRITE_TO_BINLOG 的别名。二者都是执行过程中不写入二进制日志，使用 ANALYZE 分析表时，数据库会自动对表增加一个只读锁。


mysql> ANALYZE LOCAL TABLE day_expenditure;
+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| totest.day_expenditure | analyze | status   | OK       |
+------------------------+---------+----------+----------+
1 row in set (0.02 sec)

（2）检查表


CHECK TABLE tbname [tbname2, ..., tbnamen] option [option2, ..., option5]
 
option 参数有五个取值：
QUICK: 不扫描行，不检查错误的连接。
FAST: 只检查没有被正确关闭的表。
CHANGED 只检查上次检查后被更改的表和没有被正确关闭的表。
MEDIUM: 扫描行，以验证被删除的连接是有效的
EXTENDED: 对每行的所有关键字进行一个全面的关键字查找。

option 只对 MyISAM 表有用，对 Innodb 表没用，该语句同样会给表加只读锁。

（3）优化表

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbnaem [tbname2, ..., tbnamen]

通过该语句，可以删除表中因存在 VARCHAR、BLOB 或 TEXT 等可变长类型的字段且进行了大量的更新或删除操作之后造成的空间碎片。该语句同样会加只读锁。

4 优化 MySQL 服务器

4.1 优化服务器硬件

服务器的硬件性能直接决定着 MySQL 数据库的性能。硬件的性能瓶颈直接决定 MySQL 数据库的运行速度和效率。针对性能瓶颈，提高硬件配置，可以提高 MySQL 数据库查询、更新的速度。

（1）配置较大的内存。足够大的内存是提高 MySQL 数据库性能的方法之一，内存的速度比磁盘 I/O 快很多，可以通过增加系统缓冲区容量使数据在内存中停留时间更长，以减少磁盘 I/O 。

（2）配置高速磁盘系统，以减少读写盘的等待时间，提高响应速度。

（3）合理分布磁盘 I/O，把磁盘 I/O 分散在多个设备上，以减少资源竞争，提高并行操作能力。

（4）配置多处理器。MySQL 是多线程数据库，多处理器可以并行执行多个线程。

4.2 优化 MySQL 参数

通过优化 MySQL 参数可以提高资源利用率，从而达到提高 MySQL 服务器性能的目的。MySQL 服务器的配置参数都在 my.cnf 或 my.ini 文件的 [MySQLd] 组中。

key_buffer_size: 表示索引缓冲区的大小。索引缓冲区所有的线程共享。增加索引缓冲区可以得到更好的处理的索引（对所有读和多重写）。当然这个值也不是越大越好，它的大小取决于内存的大小，如果这个值过大，导致操作系统频繁换页，也会降低系统性能。
table_cache: 表示同时打开的表的个数。这个值越大，能够同时打开的表越多。但也不是越大越好，因为同时打开的表太多会影响操作系统的性能。
query_cache_size: 表示查询缓冲区的大小。该参数需要和 query_cache_type 配合使用，当 query_cache_type 值是 0 的时候，所有的查询都不使用查询缓冲区，但这并不会导致 MySQL 释放 query_cache_size 所配置的缓冲区内存。当 query_cache_type 值为 1 时，表示所有的查询都使用查询缓冲区，除非在查询语句中指定 SQL_NO_CACHE 。当 query_cache_type 值为 2 时，只有在查询语句中使用了 SQL_CACHE 关键字，查询才会使用查询缓冲区。使用缓冲区可以提高查询速度，但只在修改操作少且经常执行相同的查询操作时才会有效。
sort_buffer_size: 表示排序缓冲区的大小。这个值越大，进行排序的速度越快。
read_buffer_size: 表示每个线程连续扫描时为每个表分配的缓冲区的大小（字节）。当线程从表中连续读取数据时需要用到这个缓冲区。SET SESSION read_buffer_size = n 可以临时设置该参数的值。
read_rnd_buffer_size: 表示为每个线程保留的缓冲区大小，与上一个参数类似。但主要用于存储按特定顺序读取出来的数据。
innodb_buffer_pool_size: 表示 Innodb 类型的表和索引的最大缓存。这个值越大，查询的速度就会越快，但是这个值太大的话会影响操作系统的性能。
max_connections：表示数据库的最大连接数。这个连接数不是越大越好，因为这些连接会浪费内存资源。过多的连接可能会导致 MySQL 僵死。
innodb_flush_log_at_trx_commit: 表示何时将缓冲区得到数据写入日志文件，并将日志文件写入磁盘。该参数有三个值，分别为0、1 和 2 。值为 0 时表示每隔 1 秒将数据文件写入日志文件并将日志文件写入磁盘；值为 1 时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；值为 2 时表示每次提交事务时将数据写入日志文件，每隔 1 秒将日志文件写入磁盘。该参数默认值为 1 。默认值为 1 安全性最高，但是每次事务提交或事务外的指令都需要把日志写入磁盘，比较费时；0 值更快一点但是安全性比较差；2 值日志每秒仍会写入磁盘，所以即使出现故障，一般也不会丢失超过 1 ~ 2 秒的更新。
back_log: 表示 MySQL 暂时停止回答请求之前的短时间内，多少个请求可以被存在堆栈中。换句话说，该值表示 TCP/IP 连接的侦听队列的大小。只有期望在短时间内有很多连接时才需要增加该参数的值。操作系统在这个队列上也有限制，设定 back_log 高于操作系统的限制将是无效的。

5 服务器语句超时处理

在 MySQL 8.0 中可以设置服务器语句超时的限制，单位可以达到毫秒级别。当语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。


SET {GLOBAL | PERSIST} max_execution_time = n
 
# n 的单位为毫秒（ms）

默认情况下，该全局变量的值为 0 ，代表没有限制。

也可以设置该限制到会话里，即超时语句的会话会被直接关闭：

SET SESSION max_execution_time = n

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】