性能优化
14.1 优化简介
优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面，需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能的节省系统资源，以便系统可以提供更大负荷的服务。
show status语句查询一些mysql数据库的性能参数
show status like 'values' # 记得打引号
常用的性能参数，也就是上述查询语句中的values值
Connections:连接mysql服务器的次数
uptime:mysql服务器本次启动到现在的时间；单位秒
slow_queries:慢查询的次数
Com_select:查询操作的次数
Com_insert:插入操作的次数
Com_update:更新操作的次数
Com_delete:删除操作的次数
14.2 优化查询
14.2.1 分析查询语句
通过对查询语句的分析，可以了解查询语句执行情况，找出查询语句执行的瓶颈，从而优化查询语句。
EXPLAIN [EXTENDED] SELECT select_options
实例
使用explain语句分析一个查询语句
explain select * from tb_student ;
语句2
DESCRIBE SELECT * from tb_student ;
返回的结果
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tb_student | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL
14.2.2 索引对查询速度的影响
MySQL中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此，索引对查询的速度有着至关重要的影响。
不使用索引查询就需要从头开始查，而使用索引就直接从索引存储的位置中提取索引，再提取索引对应的数据。
14.2.3 使用索引查询
索引可以提高查询的速度。但并不是使用带有索引的字段查询时，索引都会起作用。
介绍索引查询并不能优化查询的语句
1．使用LIKE关键字的查询语句
2．使用多列索引的查询语句
3．使用OR关键字的查询语句
14.2.4 优化子查询
在MySQL中可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好。连接之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成查询工作。
14.3 优化数据库结构
数据库的设计需要考虑到数据冗余，查询和更新的速度，字段的数据类型是否合理等多方面的内容。
14.3.1 将字段很多的表分解成多个表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
就是将表中相关联的表数据分出来形成一个表，要查询他们完整的信息时候可以采用联合查询(对分开的表)
14.3.2 增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。
就是对于经常使用查询表的相关字段把他分离出来，将这些分离出来的字段作为中间表，将原来需要联合查询的表的数据插入中间表中，最后，使用中间表查询(说白了还是分表，但你要清楚分哪些数据)
14.3.3 增加冗余字段
冗余字段就是需要在有几个表关联查询的时候，我们通常查询已经知晓的表，但该表的某个字段又与其他表关联，我们需要通过改字段查到其他关联表的数据，这时就需要在经常查询的表上增加冗余字段
合理地加入冗余字段可以提高查询速度
上述三种优化数据库结构的注意点
冗余字段:冗余字段一旦在一个表中被修改了，就要想办法在其他表中更新该字段，否则就会使原本一直的数据变得不一致。分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。
从数据库性能的角度看，浪费一定的磁盘空间来提高查询速度是大部分时候是可以接受的。
14.3.4 优化插入记录的速度
影响插入速度的主要是索引、唯一性校验和一次插入记录条数等。根据这些情况，可以分别进行优化。
1．禁用索引
对于非空表，插入记录时，mysql会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引就会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。
禁用索引语句
alter table table_name disable keys
开启索引语句
alter table table_name enable keys
2．禁用唯一性检查
插入数据时，会对插入数据的唯一性进行校验
禁用唯一性语句检查
set unique_checks=0;
开启唯一性语句检查
set unique_checks=1;
3．使用批量插入
使用Insert插入多条记录
4．使用LOAD DATA INFILE 批量导入
LOAD DATA INFILE语句导入数据的速度与insert快
上述4种是对MyISAM引擎，下面是对InnoDB引擎
禁用唯一性检查
禁用外键检查
禁止自动提交
14.3.5 分析表、检查表和优化表
MySQL提供了分析表、检查表和优化表的语句。
1．分析表
分析表主要是分析**关键字**的分布。那这是什么关键字呢
analyze table语句分析表
实例
analyze table table_name;
2．检查表
主要是检查表是否存在错误。
check table语句
实例
check table table_name;
3．优化表
主要是消除删除或者更新造成的空间浪费。
optimize table语句
实例
optimize table table_name;
14.4 优化MySQL服务器
14.4.1 优化服务器硬件
服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。
14.4.2 优化MySQL的参数
通过优化MySQL的参数，可以提高资源利用率，从而达到提高MySQL服务器性能的目的。
key_buffer_size、table_cache、query_cache_size # MySQL的参数
14.5 临时表性能优化
在MySQL 8.0中，用户可以把数据库和表归组到逻辑和物理表空间中，这样做可以提高资源的利用率。
MySQL 8.0使用 CREATE TABLESPACE 语句来创建一个通用表空间。这个功能可以让用户自由地选择表和表空间之间的映射。例如，创建表空间和设置这个表空间应该含有什么样的表。这也让在同一个表空间的用户对所有的表分组，因此在文件系统的一个单独的文件内持有他们所有的数据。同时为通用表空间实现了元数据锁。
14.6 服务器语句超时处理
在MySQL 8.0中可以设置服务器语句超时的限制，单位可以达到毫秒为级别。
当中断的执行语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。
设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现。
SET GLOBAL MAX_EXECUTION_TIME=2000;
14.7 创建全局通用表空间
下面创建名为dxy的共享表空间，SQL语句如下：
mysql> CREATE TABLESPACE dxy ADD datafile 'dxy.ibd' file_block_size=14k;
指定表空间，SQL语句如下：
mysql> CREATE TABLE t1(id int,name varchar(10))engine = innodb default charset utf8mb4 tablespace dxy;
14.8 MySQL 8.0的新特性1——支持不可见索引
不可见索引的特性对于性能调试非常有用。在MySQL 8.0中，索引可以被“隐藏”和“显示”。当一个索引被隐藏时，它不会被查询优化器所使用。也就是说，管理员可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以删掉了。
14.9 MySQL 8.0的新特性2——增加资源组
MySQL 8.0新增了一个资源组功能，用于调控线程优先级以及绑定CPU。MySQL用户需要有 RESOURCE_GROUP_ADMIN权限才能创建、修改、删除资源组。
MySQL 8.0默认提供两个资源组，分别是USR_default和SYS_default。下面来讲述关于资源组的常用操作。
创建名称为my_resouce_group的资源组，执行语句如下：
mysql> CREATE RESOURCE GROUP my_resouce_group type=USER vcpu=0,1 thread_priority=5;
14.10 综合案例——全面优化MySQL服务器
本章详细介绍了MySQL性能优化的各个方面，主要包括：查询语句优化、数据结构优化和MySQL服务器优化。本章的综合案例将帮助读者加深理解MySQL优化的方法，以及建立执行这些优化操作的能力。
解答疑惑
是不是索引建立越多越好
合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，MySQL要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上只有查询用的字段才建立索引。
为什么查询语句中的索引没有起作用
在一些情况下，查询语句中使用了带索引的字段，但索引并没有起作用。例如，WHERE条件的LIKE关键字匹配的字符串以"%"开头，这种情况下索引不会起作用。又如,WHERE条件中使用OR关键字连接查询条件，如果又1个字段没有使用索引，那么其他的索引也不会起作用。如果使用多列索引，但没有使用多列索引中的第一个字段，那么多索引也不会起作用。
如何使用查询缓冲区
查询缓冲区可以提高查询的速度，但是这种方式只适合查询语句比较多、更新语句比较少的情况。默认情况下，查询缓冲区的大小为0，也就是不可用。可以修改query_cache_size以调整查询缓存区的大小，修改query_cache_type以调整查询缓冲区的类型。在my.ini中修改query_cache_size和query_cache_type的如下
[mysqid]
query_cache_size=512M
query_cache_type=1
query_cache_type=1表示开启查询缓冲区。只有在查询语句中包含SQL_NO_CACHE关键字时，才不会使用查询缓冲区。可以使用FLUSH QUERY CACHE语句来刷新缓冲区，清理查询缓冲区中的碎片。
