MySQL 8.0——性能优化

MySQL性能优化就是通过合理安排资源，调整系统参数使MySQL运行更快、更节省资源。MySQL性能优化包括查询速度优化、数据库结构优化、MySQL服务器优化等。

1、优化简介

优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化，一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。

MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如，通过优化文件系统，提高磁盘I\O的读写速度；通过优化操作系统调度策略，提高MySQL在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

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

show status like 'value';

其中，value是要查询的参数值，一些常用的性能参数如下：

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

查询MySQL服务器的连接次数，可以执行如下语句：

SHOW STATUS LIKE 'Connections';

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Connections   | 268405 |
+---------------+--------+

查询MySQL服务器的慢查询次数，可以执行如下语句：

SHOW STATUS LIKE 'Slow_queries';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+

查询其他参数的方法和两个参数的查询方法相同。慢查询次数参数可以结合慢查询日志，找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

2、优化查询

2.1、分析查询语句

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

EXPLAIN语句的基本语法如下：

EXPLAIN [EXTENDED] SELECT select_options

使用EXTENED关键字，EXPLAIN语句将产生附加信息。select_options是SELECT语句的查询选项，包括FROMWHERE子句等。

执行该语句，可以分析EXPLAIN后面SELECT语句的执行情况，并且能够分析出所查询表的一些特征。

使用EXPLAIN语句来分析一个查询语句，可执行如下语句：

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> |   16 |    100.0 | <null> |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+--------+

• id：SELECT识别符。这是SELECT的查询序列号。
• select_type：表示SELECT语句的类型。它可以是以下几种取值：
  • SIMPLE表示简单查询，其中不包括连接查询和子查询；
  • PRIMARY表示主查询，或者是最外层的查询语句；
  • UNION表示连接查询的第2个或后面的查询语句；
  • DEPENDENT UNION，连接查询中的第2个或后面的SELECT语句，取决于外面的查询；
  • UNIONRESULT，连接查询的结果；
  • SUBQUERY，子查询中的第1个SELECT语句；
  • DEPENDENTSUBQUERY，子查询中的第1个SELECT，取决于外面的查询；
  • DERIVED，导出表的SELECT（FROM子句的子查询）​。
• table：表示查询的表。
• type：表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型。

1. system

该表是仅有一行的系统表。这是const连接类型的一个特例。

2. const

数据表最多只有一个匹配行，将在查询开始时被读取，并在余下的查询优化中作为常量对待。

const表查询速度很快，因为它们只读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。

在下面的查询中，tbl_name可用于const表：

select * from tbl_name 
where primary_key = 1;

select * from tbl_name
where primary_key_key_part1 = 1 and primary_key_part2 = 2;

CREATE TABLE user (
  id INT PRIMARY KEY,
  id_card VARCHAR(18) NOT NULL,
  name VARCHAR(50),
  age INT,
  UNIQUE KEY uk_id_card (id_card),
  KEY idx_name (name)
) ENGINE=InnoDB;

INSERT INTO user VALUES
(1, '110101199901011234', 'Tom', 25),
(2, '110101199902022345', 'Jerry', 30);

EXPLAIN SELECT * FROM user WHERE id = 1;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
|  1 | SIMPLE      | user  | <null>     | const | PRIMARY       | PRIMARY | 4       | const |    1 |    100.0 | <null> |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------+

3. eq_ref

对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UNIQUE或PRIMARY KEY时，即可使用这种类型。

eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的表的列的表达式。

出现在 多表 JOIN
被驱动表使用的是 PRIMARY KEY 或 UNIQUE NOT NULL 索引
JOIN 条件是 等值（=）
对驱动表的 每一行，被驱动表 最多返回 1 行

在下面的例子中，MySQL可以使用eq_ref连接来处理ref_tables：

select * from ref_table, other_table
where ref_table.key_column = other_table.column;

select * from ref_table, other_table
where ref_table.key_column_part1 = other_table.column and ref_table.key_column_part2 = 1;

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT NOT NULL,
  order_no VARCHAR(50),
  KEY idx_user_id (user_id)
) ENGINE=InnoDB;

INSERT INTO user VALUES
(1, 'Tom', 'tom@test.com'),
(2, 'Jerry', 'jerry@test.com');

INSERT INTO orders VALUES
(1001, 1, 'ORD001'),
(1002, 1, 'ORD002'),
(1003, 2, 'ORD003');

EXPLAIN
SELECT *
FROM orders o
JOIN user u ON u.id = o.user_id;

+----+-------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref       | rows | filtered | Extra  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+--------+
|  1 | SIMPLE      | u     | <null>     | ALL  | PRIMARY       | <null>      | <null>  | <null>    |    2 |    100.0 | <null> |
|  1 | SIMPLE      | o     | <null>     | ref  | idx_user_id   | idx_user_id | 4       | test.u.id |    1 |    100.0 | <null> |
+----+-------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+--------+

4. ref

对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。

这种类型用于索引既不是UNIQUE也不是PRIMARY KEY的情况，或者查询中使用了索引列的左子集，即索引中左边的部分列组合。ref可以用于使用=或<=>操作符带索引的列。

用的是 普通索引（非唯一）
查询条件是 等值（=）
可能匹配 0 / 1 / N 行
MySQL 通过索引树 一次性定位到起始位置，然后顺序扫描
用普通索引查等值，扫一小段索引，可能多条结果”

在下面的例子中，MySQL可以使用ref连接来处理ref_tables：

select * from ref_table
where key_column = expr;

select * from ref_table, other_table
where ref_table.key_column = other_table.column;

select * from ref_table, other_table
where ref_table.key_column_part1 = other_table.column and ref_table.key_column_part2 = 1;

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  status TINYINT,
  email VARCHAR(100),
  KEY idx_name (name),
  KEY idx_status (status),
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB;

INSERT INTO user VALUES
(1, 'Tom',   25, 1, 'tom@test.com'),
(2, 'Tom',   30, 1, 'tom2@test.com'),
(3, 'Jerry', 28, 0, 'jerry@test.com'),
(4, 'Tom',   35, 1, 'tom3@test.com');

EXPLAIN
SELECT * FROM user WHERE name = 'Tom';

+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------+
|  1 | SIMPLE      | user  | <null>     | ref  | idx_name      | idx_name | 153     | const |    3 |    100.0 | <null> |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------+

5. re_or_null

该连接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该连接类型的优化。

在下面的例子中，MySQL可以使用ref_or_null连接来处理ref_tables：

select * from ref_table
where key_column - expr or key_column is null;

6. index_merge

该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用索引的最长关键元素。

index_merge= MySQL 对同一张表同时使用多个索引，分别扫描后再合并结果。它不是“更慢”，也不是“更快”，而是一种 “在没有更优单列索引时的折中方案”。

一个 SQL
一个表
WHERE 条件里有 多个独立索引字段
MySQL 会：
分别走多个索引
拿到多组 rowid
按条件做 交集 / 并集 / 排序去重
再去回表取数据

7. unique_subquery

unique_subquery= IN (SELECT …)子查询中，子查询命中了「主键 / 唯一非空索引」，优化器用“半连接 + 去重”方式执行

该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

8. index_subquery

该连接类型类似于unique_subquery，可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

9. range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。

当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符用常量比较关键字列时，类型为range。

range= 使用索引，但只扫描“一个范围内的索引条目”，不是精确匹配一行，也不是全表扫描它是 “能用索引，但不够精确”​ 的典型代表。

• 用了 B+Tree 索引
• 查询条件是 范围条件
• MySQL 在索引树上：
  • 找到起点
  • 沿着叶子节点顺序扫描
  • 直到终点

下面介绍几种检索指定行情况：

select * from tb1_name
where key_column = 10;

select * from tb1_name
where key_column between 10 and 20;

select * from tb1_name
where key_column in (10, 20, 30);

select * from tbl_name
where key_part1 = 10 and key_part2 in (10, 20, 30);

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  score INT,
  create_time DATETIME,
  KEY idx_age (age),
  KEY idx_score (score),
  KEY idx_name (name),
  KEY idx_create_time (create_time)
) ENGINE=InnoDB;

INSERT INTO user VALUES
(1, 'Tom',   25, 80, '2026-01-01 10:00:00'),
(2, 'Tom',   30, 85, '2026-01-02 11:00:00'),
(3, 'Jerry', 28, 90, '2026-01-03 12:00:00'),
(4, 'Tom',   35, 95, '2026-01-04 13:00:00'),
(5, 'Tom',   40, 70, '2026-01-05 14:00:00');

EXPLAIN
SELECT * FROM user WHERE age > 30;

EXPLAIN
SELECT * FROM user WHERE age BETWEEN 25 AND 35;

EXPLAIN
SELECT * FROM user WHERE age BETWEEN 25 AND 35;

EXPLAIN
SELECT * FROM user
WHERE create_time >= '2026-01-02'
  AND create_time < '2026-01-04';

EXPLAIN
SELECT * FROM user WHERE name LIKE 'Tom%';

10. index

该连接类型与ALL相同，除了只扫描索引树。这通常比ALL快，因为索引文件通常比数据文件小。

index= 全索引扫描（Full Index Scan）​

• 扫描整个索引树，不扫描数据行（或少回表）​
• 比 ALL（全表扫描）好一点，但仍然是“扫全量”

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  KEY idx_age (age),
  KEY idx_city (city)
) ENGINE=InnoDB;

INSERT INTO user VALUES
(1, 'Tom',   25, 'Beijing'),
(2, 'Jerry', 30, 'Shanghai'),
(3, 'Tom',   28, 'Beijing'),
(4, 'Mike',  35, 'Beijing'),
(5, 'Lucy',  40, 'Shanghai');

-- 覆盖索引 + 无 WHERE（最典型）
EXPLAIN
SELECT age FROM user;

-- ORDER BY 索引列（无过滤）
EXPLAIN
SELECT age FROM user ORDER BY age;

-- GROUP BY 索引列
EXPLAIN
SELECT age, COUNT(*) FROM user GROUP BY age;

-- 索引比表小，优化器选 index
EXPLAIN
SELECT age FROM user WHERE age > 0;

11. all

ALL= 全表扫描（Full Table Scan）​

• 从第一条数据开始，一行一行扫完整张表

• 不走索引，或索引用不上

• 没有合适的索引

• 或索引失效

• MySQL 只能：

  • 从聚簇索引（InnoDB）
  • 或堆表（MyISAM）
  • 按顺序读取所有数据页

• possible_keys：指出MySQL能使用哪个索引在该表中找到行。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看它是否引用某些列或适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能。

• key：表示查询实际使用到的索引，如果没有选择索引，该列的值是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。参见SELECT语法。

• key_len：表示MySQL选择的索引字段按字节计算的长度，如果键是NULL，则长度为NULL。注意通过key_len值可以确定MySQL将实际使用一个多列索引中的几个字段。

• ref：表示使用哪个列或常数与索引一起来查询记录。

• rows：显示MySQL在表中进行查询时必须检查的行数。

• Extra：表示MySQL在处理查询时的详细信息。

DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。DESCRIBE语句的语法形式如下：

DESCRIBE SELECT select_options

DESCRIBE可以缩写成DESC。

2.2、索引对查询速度的影响

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

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

下面是查询语句中不使用索引和使用索引的对比。首先，分析未使用索引时的查询情况，EXPLAIN语句执行如下：

explain select * from fruits
where f_name = 'apple';

+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref    | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
|  1 | SIMPLE      | fruits | <null>     | ALL  | <null>        | <null> | <null>  | <null> |   16 |     10.0 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+

可以看到，rows列的值是16，说明“SELECT * FROMfruits WHERE f_name=‘apple’;”这个查询语句扫描了表中的16条记录。

然后，在fruits表的f_name字段上加上索引。执行添加索引的语句及结果如下：

create index index_name on fruits(f_name);

现在，再分析上面的查询语句。执行的EXPLAIN语句及结果如下：

explain select * from fruits
where f_name = 'apple';

+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+--------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra  |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+--------+
|  1 | SIMPLE      | fruits | <null>     | ref  | index_name    | index_name | 765     | const |    1 |    100.0 | <null> |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+--------+

结果显示，rows列的值为1，表示这个查询语句只扫描了表中的一条记录，其查询速度自然比扫描15条记录快；而且possible_keys和key的值都是index_name，说明查询时使用了index_name索引。

2.3、使用索引查询

索引可以提高查询的速度，但并不是使用带有索引的字段查询时索引都会起作用。

使用索引有几种特殊情况，在这些情况下有可能使用带有索引的字段查询时索引并没有起作用。下面重点介绍这几种特殊情况。

2.3.1、使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”​，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。下面将举例说明。

查询语句中使用LIKE关键字，并且匹配的字符串中含有“%”字符，EXPLAIN语句执行如下：

explain select * from fruits 
where f_name like '%x';

+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref    | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
|  1 | SIMPLE      | fruits | <null>     | ALL  | <null>        | <null> | <null>  | <null> |   16 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+


explain select * from fruits 
where f_name like 'x%';

+----+-------------+--------+------------+-------+---------------+------------+---------+--------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key        | key_len | ref    | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+------------+---------+--------+------+----------+-----------------------+
|  1 | SIMPLE      | fruits | <null>     | range | index_name    | index_name | 765     | <null> |    4 |    100.0 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+--------+------+----------+-----------------------+

已知f_name字段上有索引index_name。第1个查询语句执行后，rows列的值为16，表示这次查询过程中扫描了表中所有的16条记录；第2个查询语句执行后，rows列的值为4，表示这次查询过程扫描了4条记录。第1个查询语句中的索引没有起作用，因为第1个查询语句中LIKE关键字后的字符串以“%”开头，而第2个查询语句使用了索引index_name。

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

MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。

在表fruits中f_id、f_price字段上创建多列索引，验证多列索引的使用情况。

create index index_id_price 
on fruits(f_id, f_price);

explain select * from fruits
where f_id = 'a1';

+----+-------------+--------+------------+-------+------------------------+---------+---------+-------+------+----------+--------+
| id | select_type | table  | partitions | type  | possible_keys          | key     | key_len | ref   | rows | filtered | Extra  |
+----+-------------+--------+------------+-------+------------------------+---------+---------+-------+------+----------+--------+
|  1 | SIMPLE      | fruits | <null>     | const | PRIMARY,index_id_price | PRIMARY | 30      | const |    1 |    100.0 | <null> |
+----+-------------+--------+------------+-------+------------------------+---------+---------+-------+------+----------+--------+


explain select * from fruits
where f_price = 5.2;

+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref    | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
|  1 | SIMPLE      | fruits | <null>     | ALL  | <null>        | <null> | <null>  | <null> |   16 |     10.0 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+

从第1条语句查询结果可以看出，​“f_id= ‘a1’”的记录有1条。第1条语句共扫描了1条记录，并且使用了索引index_id_price。从第2条语句查询结果可以看出，rows列的值是16，说明查询语句共扫描了16条记录，并且key列值为NULL，说明“SELECT * FROM fruits WHEREf_price=5.2;”语句并没有使用索引。因为f_price字段是多列索引的第2个字段，只有查询条件中使用了f_id字段才会使index_id_price索引起作用。

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

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引；否则，查询将不使用索引。

查询语句使用OR关键字的情况：

explain select * from fruits
where f_name = 'apple' or s_id = 101;

+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref    | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
|  1 | SIMPLE      | fruits | <null>     | ALL  | index_name    | <null> | <null>  | <null> |   16 |     19.0 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+------+----------+-------------+

explain select * from fruits
where f_name = 'apple' or f_id = 'a1';


16 rows in set
+----+-------------+--------+------------+-------------+-----------------------------------+--------------------+---------+--------+------+----------+----------------------------------------------+
| id | select_type | table  | partitions | type        | possible_keys                     | key                | key_len | ref    | rows | filtered | Extra                                        |
+----+-------------+--------+------------+-------------+-----------------------------------+--------------------+---------+--------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | fruits | <null>     | index_merge | PRIMARY,index_name,index_id_price | index_name,PRIMARY | 765,30  | <null> |    2 |    100.0 | Using union(index_name,PRIMARY); Using where |
+----+-------------+--------+------------+-------------+-----------------------------------+--------------------+---------+--------+------+----------+----------------------------------------------+

因为s_id字段上没有索引，所以第1条查询语句没有使用索引，总共查询了16条记录；第2条查询语句使用了f_name和f_id这两个索引，因为id字段和name字段上都有索引，所以查询的记录数为2条。

2.4、优化子查询

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活，但执行效率不高。

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。

查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，性能会更好。连接之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成查询工作。

3、优化数据库结构

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

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

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

假设会员表存储会员登录认证信息，该表中有很多字段，如id、姓名、密码、地址、电话、个人描述字段。其中，地址、电话、个人描述等字段并不常用。可以将这些不常用字段分解出另外一个表。将这个表取名叫members_detail。表中有member_id、address、telephone、description等字段。其中，member_id是会员编号，address字段存储地址信息，telephone字段存储电话信息，description字段存储会员个人描述信息。这样就把会员表分成两个表，分别为members表和members_detail表。

创建这两个表的SQL语句如下：

create table members
(
	id int(11) not null auto_increment,
	username varchar(255) default null,
	password varchar(255) default null,
	last_login_time datetime default null,
	last_login_ip varchar(255) default null,
	primary key(id)
);

create table members_detail
(
	member_id int(11) not null default 0,
	address varchar(255) default null,
	telephone varchar(16) default null,
	description text
);

如果需要查询会员的详细信息，可以用会员的id来查询。如果需要将会员的基本信息和详细信息同时显示，可以将members表和members_detail表进行联合查询，查询语句如下：

select * from members
left join members_detail
on members.id = members_detail.member_id;

通过这种分解，可以提高表的查询效率。对于字段很多且有些字段使用不频繁的表，可以通过这种分解的方式来优化数据库的性能。

3.2、增加中间表

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

首先，分析经常联合查询表中的字段。然后，使用这些字段建立一个中间表，并将原来联合查询的表的数据插入到中间表中。最后，使用中间表进行查询。

会员信息表和会员组信息表的SQL语句如下：

create table vip
(
	id int(11) not null auto_increment,
	username varchar(255) default null,
	password varchar(255) default null,
	groupId int(11) default 0,
	primary key(id)
);

create table vip_group
(
	id int(11) not null auto_increment,
	name varchar(255) default null,
	remark varchar(255) default null,
	primary key(id)
);

已知现在有一个模块需要经常查询带有会员组名称、会员组备注（remark）​、会员用户名信息的会员信息。根据这种情况可以创建一个temp_vip表。temp_vip表中存储用户名（user_name）​、会员组名称（group_name）和会员组备注（group_remark）信息。创建表的语句如下：

create table temp_vip
(
	id int(11) not null auto_increment,
	user_name varchar(255) default null,
	group_name varchar(255) default null,
	group_remark varchar(255) default null,
	primary key(id)
);

接下来，从会员信息表和会员组表中查询相关信息存储到临时表中：

insert into temp_vip(user_name, group_name, group_remark)
select v.username, g.name, g.remark
from vip as v, vip_group as g
where v.groupid = g.id;

以后，可以直接从temp_vip表中查询会员名、会员组名称和会员组备注，而不用每次都进行联合查询。这样可以提高数据库的查询速度。

3.3、增加冗余字段

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

表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。例如，员工的信息存储在staff表中，部门信息存储在department表中。通过staff表中的department_id字段与department表建立关联关系。

如果要查询一个员工所在部门的名称，必须从staff表中查找员工所在部门的编号（department_id）​，然后根据这个编号去department表查找部门的名称。如果经常需要进行这个操作，连接查询会浪费很多时间。

可以在staff表中增加一个冗余字段department_name，该字段用来存储员工所在部门的名称，这样就不用每次都进行连接操作了。

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

3.4、优化插入记录的速度

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

3.4.1、MyISAM引擎

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

1、禁用索引

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

ALTER TABLE table_name DISABLE KEYS;

其中，table_name是禁用索引的表的表名。

重新开启索引的语句如下：

ALTER TABLE table_name ENABLE KEYS;

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

2、禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。禁用唯一性检查的语句如下：

SET UNIQUE_CHECKS=0;

开启唯一性检查的语句如下：

SET UNIQUE_CHECKS=1;

3、使用批量插入

插入多条记录时，可以使用一条INSERT语句插入一条记录；也可以使用一条INSERT语句插入多条记录。插入一条记录的INSERT语句情形如下：

INSERT INTO fruits VALUES('x1', '101', 'mongo2', '5.7');
INSERT INTO fruits VALUES('x2', '101', 'mongo3', '5.7')
INSERT INTO fruits VALUES('x3', '101', 'mongo4', '5.7')

使用一条INSERT语句插入多条记录的情形如下：

INSERT INTO fruits VALUES
('x1', '101', 'mongo2', '5.7'),
('x2', '101', 'mongo3', '5.7'),
('x3', '101', 'mongo4', '5.7');

第2种情形的插入速度要比第1种情形快。

4、使用LOAD DATA INFILE批量导入

当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快。

3.4.2、InnoDB引擎

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

1、 禁用唯一性检查

插入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行setunique_checks=1。这个和MyISAM引擎的使用方法一样。

2、禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。禁用外键检查的语句如下：

SET foreign_key_checks=0;

恢复对外键的检查语句如下：

SET foreign_key_checks=1;

3、 禁止自动提交

插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交的语句如下：

set autocommit=0;

恢复自动提交的语句如下：

set autocommit=1;

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

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

3.5.1、分析表

MySQL中提供了ANALYZE TABLE语句来分析表。ANALYZE TABLE语句的基本语法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

• LOCAL关键字是NO_WRITE_TO_BINLOG关键字的别名，二者都是执行过程不写入二进制日志，
• tbl_name为分析表的表名，可以有一个或多个。

使用ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB、BDB和MyISAM类型的表。

使用ANALYZE TABLE来分析message表，执行的语句及结果如下：

analyze table members;

+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| test_db.members | analyze | status   | OK       |
+-----------------+---------+----------+----------+

• Table：表示分析的表的名称。
• Op：表示执行的操作。analyze表示进行分析操作。
• Msg_type：表示信息类型，其值通常是状态（status）​、信息（info）​、注意（note）​、警告（warning）和错误（error）之一。
• Msg_text：显示信息。

3.5.2、检查表

MySQL中可以使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。

对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据。而且，CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。该语句的基本语法如下：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

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

option只对MyISAM类型的表有效，对InnoDB类型的表无效。CHECK TABLE语句在执行过程中也会给表加上只读锁。

check table members;

+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| test_db.members | check | status   | OK       |
+-----------------+-------+----------+----------+

3.5.3、优化表

MySQL中使用OPTIMIZE TABLE语句来优化表。该语句对InnoDB和MyISAM类型的表都有效。但是，OPTILMIZETABLE语句只能优化表中VARCHAR、BLOB或TEXT类型的字段。OPTILMIZE TABLE语句的基本语法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

• LOCAL | NO_WRITE_TO_BINLOG关键字的意义和分析表相同，都是指定不写入二进制日志；
• tbl_name是表名。

通过OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片。OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。

optimize table members;

+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| test_db.members | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test_db.members | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+

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

4、优化MySQL服务器

优化MySQL服务器主要从两方面来优化：

• 一方面是对硬件进行优化；
• 另一方面是对MySQL服务的参数进行优化。这部分的内容需要较全面的知识，一般只有专业的数据库管理员才能进行这一类的优化。对于可以定制参数的操作系统，也可以针对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时，所有的查询都不使用查询缓冲区。但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓冲区内存。当query_cache_type=1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定SQL_NO_CACHE，如SELECTSQL_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。

合理地配置这些参数可以提高MySQL服务器的性能。除上述参数以外，还有innodb_log_ buffer_size、innodb_log_file_size等参数。配置完参数以后，需要重新启动MySQL服务才会生效。

5、临时表性能优化

在MySQL 8.0中，用户可以把数据库和表归组到逻辑和物理表空间中，这样做可以提高资源的利用率。

MySQL 8.0使用CREATE TABLESPACE语句来创建一个通用表空间。这个功能可以让用户自由地选择表和表空间之间的映射。例如，创建表空间和设置这个表空间应该含有什么样的表。这也让在同一个表空间的用户对所有的表分组，因此在文件系统一个单独的文件内持有他们所有的数据，同时为通用表空间实现了元数据锁。

优化普通SQL临时表性能是MySQL 8.0的目标之一。首先，通过优化临时表在磁盘中的不必要步骤，使得临时表的创建和移除成为一个轻量级的操作。将临时表移动到一个单独的表空间中，恢复临时表的过程就变得非常简单，就是在启动时重新创建临时表的单一过程。

MySQL 8.0去掉了临时表中不必要的持久化。临时表仅仅在连接和会话内被创建，然后通过服务的生命周期绑定它们。通过移除不必要的UNDO和REDO日志，改变缓冲和锁，从而为临时表做了优化操作。

MySQL 8.0增加了UNDO日志一个额外的类型，这个类型的日志被保存在一个单独的临时表空间中，在恢复期间不会被调用，而是在回滚操作中才会被调用。

MySQL 8.0为临时表设定了一个特别类型，称之为“内在临时表”​。内在临时表和普通临时表很像，只是内在临时表使用宽松的ACID和MVCC语义。

MYSQL 8.0为了提高临时表相关的性能，对临时表相关的部分进行了大幅修改，包括引入新的临时表空间（ibtmp1）​；对于临时表的DDL，不持久化相关表定义；对于临时表的DML，不写redo、关闭change buffer等。

InnoDB临时表元数据不再存储于InnoDB系统表，而是存储在INNODB_TEMP_TABLE_INFO中，包含所有用户和系统创建的临时表信息。该表在第一次运行select时被创建，下面举例说明。

MySQL 8.0使用了独立的临时表空间来存储临时表数据，但不能是压缩表。临时表空间在实例启动的时候进行创建、shutdown的时候进行删除，即为所有非压缩的innodb临时表提供一个独立的表空间。默认的临时表空间文件为ibtmp1，位于数据目录中。通过innodb_temp_data_file_path参数可指定临时表空间的路径和大小，默认为12MB。只有重启实例才能回收临时表空间文件ibtmp1的大小。create temporary table和usingtemporary table将共用这个临时表空间。

在MySQL 8.0中，临时表在连接断开或者数据库实例关闭的时候会进行删除，从而提高了性能。只有临时表的元数据使用了redo保护，保护元数据的完整性，以便异常启动后进行清理工作。

临时表的元数据在MySQL 8.0之后使用了一个独立的表（innodb_temp_table_info）进行保存，不用使用redo保护，元数据也只保存在内存中。但这有一个前提，即必须使用共享的临时表空间，如果使用file-per-table，仍然需要持久化元数据，以便异常恢复清理。临时表需要undolog，用于MySQL运行时的回滚。

在MySQL 8.0中，新增一个系统选项internal_tmp_disk_storage_engine，可定义磁盘临时表的引擎类型，默认为InnoDB，可选MyISAM。在这以前，只能使用MyISAM。在MySQL 5.6.3以后新增的参数default_tmp_storage_engine是控制create temporary table创建的临时表存储引擎，在以前默认是MEMORY。查看结果如下：

6、服务器语句超时处理

在MySQL 8.0中可以设置服务器语句超时的限制，单位可以达到毫秒级别。

当中断的执行语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。

设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现。

SET GLOBAL MAX_EXECUTION_TIME=2000;

默认情况下，MAX_EXECUTION_TIME的值为0，代表没有时间限制。通过上述设置后，如果SELECT语句执行超过2000毫秒，语句将会被终止。

设置服务器语句超时的限制，也可以通过设置系统变量max_execution_time来实现。该变量用于设置SELECT语句运行在一个特定的会话里，指定该会话的超时时间。

SET SESSION MAX_EXECUTION_TIME=2000;

通过上述设置后，如果SELECT语句执行超过2000毫秒，会话将会被终止。

7、创建全局通用表空间

MySQL 8.0支持创建全局通用表空间，全局表空间可以被所有数据库的表共享，而且相比于独享表空间，手动创建共享表空间可以节约元数据方面的内存。可以在创建表的时候指定属于哪个表空间，也可以对已有表进行表空间修改，具体的信息可以查看官方文档。

下面创建名为dxy的共享表空间，SQL语句如下：

create tablespace dxy 
add datafile 'axy.ibd' file_block_size = 16k;

指定表空间，SQL语句如下：

create table t1
(
	id int,
	name varchar(10)
) engine = innodb default charset utf8mb4 tablespace dxy;

也可以通过ALTER TABLE语句指定表空间，SQL语句如下：

alter table t1 tablespace dxy;

如何删除创建的共享表空间？因为是共享表空间，所以不能直接通过drop table tbname来删除，也不能回收空间。当确定共享表空间的数据都没用并且依赖该表空间的表均已经删除时，可以通过drop tablespace来删除共享表空间，以释放空间，如果依赖该共享表空间的表存在就会删除失败。

首先，删除依赖该表空间的数据表，SQL语句如下：

drop table t1;

最后，删除表空间，SQL语句如下：

drop tablespace dxy;

8、MySQL 8.0的新特性——支持不可见索引

不可见索引的特性对于性能调试非常有用。在MySQL 8.0中，索引可以被“隐藏”和“显示”​。当一个索引被隐藏时，它不会被查询优化器所使用。也就是说，管理员可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以删掉了。

下面通过一个案例来了解如何隐藏和显示索引。

创建不可见索引，执行语句如下：

create table test1
(
	a int,
	b int,
	index idx_a_b(a, b desc) invisible
);

查看索引idx_a_b的属性Visible的值，执行语句如下：

show index from test1;


***************************[ 1. row ]***************************
Table         | test1
Non_unique    | 1
Key_name      | idx_a_b
Seq_in_index  | 1
Column_name   | a
Collation     | A
Cardinality   | 0
Sub_part      | <null>
Packed        | <null>
Null          | YES
Index_type    | BTREE
Comment       |
Index_comment |
Visible       | NO
Expression    | <null>
***************************[ 2. row ]***************************
Table         | test1
Non_unique    | 1
Key_name      | idx_a_b
Seq_in_index  | 2
Column_name   | b
Collation     | D
Cardinality   | 0
Sub_part      | <null>
Packed        | <null>
Null          | YES
Index_type    | BTREE
Comment       |
Index_comment |
Visible       | NO
Expression    | <null>

从结果可以看出，Visible的属性值为NO。

显示不可见索引，执行语句如下：

alter table test1 alter index idx_a_b visible;

再次查看索引idx_a_b的属性Visible的值，Visible的属性值为YES。

当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

再次隐藏索引，执行语句如下：

alter table test1 alter index idx_a_b invisible;

数据表中的主键不能被设置为invisible。

9、MySQL 8.0的新特性——增加资源组

MySQL 8.0新增了一个资源组功能，用于调控线程优先级以及绑定CPU。MySQL用户需要有RESOURCE_GROUP_ADMIN权限才能创建、修改、删除资源组。

注意，在Linux环境下，MySQL进程需要有CAP_SYS_NICE权限才能使用资源组的完整功能。

　　[root@localhost~]# sudo setcap cap_sys_nice+ep /usr/local/mysql8.0/bin/mysqld
　　[root@localhost~]# getcap /usr/local/mysql8.0/bin/mysqld
　　/usr/local/mysql8.0/bin/mysqld = cap_sys_nice+ep

MySQL 8.0默认提供两个资源组，分别是USR_default和SYS_default。下面来讲述关于资源组的常用操作。

创建名称为my_resouce_group的资源组，执行语句如下：

create resource group my_resource_group type=USER vcpu=0,1 thread_priority=5;

将当前线程加入资源组：

set resource group my_resource_group;

查看资源组my_resouce_group中包含的线程，执行语句如下：

select * from Performance_Schema.threads 
where RESOURCE_GROUP='my_resouce_group';

资源组里有线程时，删除资源组会报错：

DROP RESOURCE group my_resouce_group;

ERROR 3656 (HY000): Resource group test_resouce_group is busy.

修改资源组的语句如下：

alter resource group my_resouce_group vcpu = 2,3 thread_priority = 8;

把资源组里的线程移出到默认资源组USR_default中，执行语句如下：

set resource group usr_default for 48;

删除资源组的语句如下：

drop resource group my_resouce_group;

如果需要指定线程的ID，可以使用以下语句：

set resource group my_resouce_group for thread_id;

例如，将ID为20的线程加入到资源组my_resouce_group中，语句如下：

set resource group test_resouce_group for 20;

10、常见问题

10.1、是不是索引建立得越多越好？

合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，MySQL要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段才建立索引。

10.2、为什么查询语句中的索引没有起作用？

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

10.3、如何使用查询缓冲区？

查询缓冲区可以提高查询的速度，但是这种方式只适合查询语句比较多、更新语句比较少的情况。默认情况下查询缓冲区的大小为0，也就是不可用。可以修改query_cache_size以调整查询缓冲区大小，修改query_cache_type以调整查询缓冲区的类型。在my.ini中修改query_cache_size和query_cache_type的值：

[mysqld]
query_cache_size=512M
query_cache_type=1

query_cache_type=1表示开启查询缓冲区。只有在查询语句中包含SQL_NO_CACHE关键字时，才不会使用查询缓冲区。可以使用FLUSH QUERY CACHE语句来刷新缓冲区，清理查询缓冲区中的碎片。