简介
本文将全面讲解Mysql的性能优化，包括数据查询优化、数据库结构优化、插入数据优化、服务器优化等，一文学会MySQL全部常用的优化策略。
一.为什么要优化MySQL
为了合理分配和使用系统资源，让数据库运行更快，更节省资源。
所以优化的原则：减少资源占用，降低系统瓶颈，增加系统反应速度。
二.关键的数据库参数查询
①查看所有MySQL数据库的性能参数：
show status②查看MySQL上线时间
距离上次重启时间，查询的结果单位是秒。将结果/60/60/24即可换算成天，即表示距离上次重启，已经xxx天了。
show STATUS LIKE 'Uptime'③查看’慢查询’次数
show STATUS LIKE 'Slow_queries'③增删改查
show STATUS LIKE '%Com_create_%'
show STATUS LIKE '%Com_drop_%'
show STATUS LIKE '%Com_del_%'
show STATUS LIKE '%Com_alter_%'
show STATUS LIKE '%Com_select%'三.查询数据库哪些表的数据量比较大
1.连接到数据库
在命令行中执行下述命令。或者在Navicat中，直接在选择数据库的下拉框中选择information_schemause information_schema
2.执行以下SQL,查看数据量最大的30个表
select table_name , table_rows from tables order by table_rows desc LIMIT 30;四.查询优化
注：以下讲解以Mysql v5.7.33版本为例；查看数据库版本SQLselect version();
1.通过Explain语句分析查询慢的SQL
遇到查询慢的语句，在MySQL中可以通过EXPLAIN查看SQL执行计划，从而了解SQL执行慢的原因。
EXPLAIN SELECT * FROM `tbl_warning_log` where warning_status = 12
2.Explain语句查询结果解释
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
查询序号 | Select语句类型 | 表名 | 分区 | 表连接类型 | 可用索引字段 | 实际使用索引字段 | 实际使用索引长度 | 使用哪个列和常数查询 | 最少查询行数 | 结果占查询行数百分比 | 详细信息 |
①.id
查询行号，不重要。
②.select_type
select 语句类型。有以下值：
㈠.SIMPLE
表示简单查询，其中不包含连接查询和子查询。比如：
select * FROM tbl_user WHERE user_id = 372377213825024㈡.PRIMARY
表示主查询，或者是最外面的查询语句。
㈢.UNION
表示连接查询的第2个或后面的查询语句，仅有连接查询关系，并不互相依赖的SQL语句。
㈣.DEPENDENT UNION
依赖连接，UNION中第二个或内层的SELECT语句，取决于外面的查询。内层的查询与外层的查询有依赖关系。
㈤.UNION RESULT
表示连接查询的结果。
㈥.SUBQUERY
在带有子查询的SQL中，所有子查询中（如果有多个子查询）的第一个Select语句。
㈦.DEPENDENT SUBQUERY
子查询中的第一个Select,但他依赖于外层查询。
㈧.DERIVED
派生：子查询位于From语句中，如以下SQL：
select * FROM (SELECT * FROM tbl_user WHERE username = "五道口") a③.table
表示查询的表名。
④.partitions
分区。
⑤.type
表示表的连接类型。以下的连接类型的顺序是从最佳类型到最差类型：
1.system
表仅有一行，这是const类型的特列，平时不会出现，可以不用记忆。
2.const
数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查询，可理解为const是最优化的。
3.eq_ref
Mysql手册提出：“对于每个来自于前面的表的行组合，从该表中读取一行。除了const类型外，这可能是最好的联接类型。它用在一个索引的所有部分被联接使用并且索引是
UNIQUE或PRIMARY KEY”。
eq_ref可以用于使用=比较带索引的列。
4.ref
查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。
5.ref_or_null
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
注：上面这五种情况都是很理想的索引使用情况，一般不必再优化。
6.index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
7.unique_subquery
该类型替换了下面形式的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
只检索给定范围的行,使用一个索引来选择行。
10.index
该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
11.ALL
对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差,SQL需要优化的原因）
⑥.possible_keys
表示MySQL能使用哪个索引在该表中找到行(注意：是可能使用哪个索引，而不是真实使用)。
如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。
⑦.key
显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
可以强制使用索引或者忽略索引：
⑧.key_len
显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
注意：key_len是确定了MySQL将实际使用的索引长度。
⑨.ref
表示使用哪个列或常数与key一起从表中选择行。
⑩.rows
表示MySQL认为它执行查询时必须扫描的行数。
⑾.filtered
Filtered表示返回结果的行数占需读取行数的百分比。
在实际使用中，你会发现这个值经常是100%，只有在where条件中的列必须有索引，又要执行计划是全表扫描或覆盖索引扫描时，这个值才有变化。所以这个值基本用不到了。
⑿.Extra
该列包含MySQL解决查询的详细信息
-
Distinct：MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 -
Not exists：MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 -
range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 -
Using filesort：MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 -
Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 -
Using temporary：为了解决查询,MySQL需要创建一个临时表来容纳结果。 -
Using where：WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 -
Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描。 -
Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。
五.使用索引要注意的地方
并不是使用了带索引的字段，索引就会生效，就会提高查询速度，以下情况，索引可能不生效的，需要注意和优化！
①.使用LIKE关键字的查询
在使用LIKE关键字进行查询的查询语句中，如果用%匹配字符串的第一个字符时，索引无效。只有%不在第一个位置，索引才会生效。
②.使用联合索引的查询
MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。
比如下图中，在user_id和item_id上建立联合索引，但是只有在WHERE条件中，将user_id放到第一个位置时，索引才生效；反之，如果将item_id放到第一个位置时，索引不会生效。
③.使用OR关键字的查询
查询语句的查询条件中只使用OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。
六.子查询的优化
MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询为什么慢？
子查询虽然很灵活，但是执行效率并不高。原因是执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。
如何优化：
可以使用连接（JOIN）查询代替子查询，连接查询时不需要建立临时表，其速度比子查询快。
七.数据库结构优化
一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。
1.将字段很多的表分解成多个表
为什么要拆开字段多的表？
因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
怎么拆？
对于字段较多的表，可以将使用频率很低的表拆分成新的表，比如某某表的扩展表。
2.增加中间表
为什么增加中间表？
当需要频繁连表查询时，对于需要经常联合查询的表，可以建立中间表，从而避免了大量连表查询，提高了查询效率。
如何建立中间表？
通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
3.增加冗余字段
增加冗余字段的坏处
设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅，冗余数据越多，后期维护就越麻烦，冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。
适量冗余字段，可以优化查询速度
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。但是，合理的加入冗余字段，多个表中都含有此字段，就不需要连表查询了，可以提高查询速度。
八.插入数据优化（MyISAM和InnoDB两种情况）
什么影响插入数据速度？
插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。
插入数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有MyISAM和InnoDB，这里介绍关于这两种引擎的一些基本概念。
1.MyISAM和InnoDB简介：
MyISAM：
MyISAM是MySQL的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。每张MyISAM表存放在三个文件中：frm文件存放表格定义；数据文件是MYD (MYData)；索引文件是MYI (MYIndex)。
InnoDB：
InnoDB是事务型引擎，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定（InnoDB表的行锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，如like操作时的SQL语句），以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。
2.MyISAM和InnoDB主要区别：
-
MyISAM是非事务安全型的，而InnoDB是事务安全型的。 -
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。 -
MyISAM支持全文类型索引，而InnoDB不支持全文索引。 -
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。 -
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。 -
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。
3.MyISAM和InnoDB如何选型？
MyISAM用来管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
4.如何优化MyISAM引擎的数据表？
①禁用索引，优化写入速度
对于非空表，插入记录时，
MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。对于空表批量插入数据时，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。
如何通过禁用索引，优化写入速度？
可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。
禁用索引的语句：
ALTER TABLE table_name DISABLE KEYS开启索引语句：
ALTER TABLE table_name ENABLE KEYS②.禁用唯一性检查，优化写入速度
唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。
禁用唯一性检查的语句：
SET UNIQUE_CHECKS = 0;开启唯一性检查的语句：
SET UNIQUE_CHECKS = 1;③.批量插入数据
插入数据时，可以使用一条INSERT语句插入一条数据，也可以插入多条数据。如：
每次插入一条数据：
INSERT INTO tbl_ cart
VALUES
( '1', '苹果(APPLE) iPhone 12', 'http://image.taotao.com/images/2021/10/23/202110230452400280709.jpg', '2021-12-11 18:07:46', '2021-12-1216:52:55' );
INSERT INTO tbl_ cart
VALUES
( '2', '苹果(APPLE) iPhone 12', 'http://image.taotao.com/images/2021/10/23/202110230452400280709.jpg', '2021-12-11 18:07:46', '2021-12-1216:52:55' );上述SQL可以优化为，一次插入多条数据：
INSERT INTO tbl_ cart
VALUES
( '1', '苹果(APPLE) iPhone 12', 'http://image.taotao.com/images/2021/10/23/202110230452400280709.jpg', '2021-12-11 18:07:46', '2021-12-1216:52:55' ),
( '2', '苹果(APPLE) iPhone 12', 'http://image.taotao.com/images/2021/10/23/202110230452400280709.jpg', '2021-12-11 18:07:46', '2021-12-1216:52:55' );④使用LOAD DATA INFILE
当需要从文件中，批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。
将data.txt文件中的内容，导入到数据库，其语法如下：
load data infile '/tmp/data.txt' ignore into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n' (`name`,`age`,`description`);4.如何优化InnoDB引擎的数据表？
①.禁用唯一性检查，优化写入速度（同MyISAM）
唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。
禁用唯一性检查的语句：
SET UNIQUE_CHECKS = 0;开启唯一性检查的语句：
SET UNIQUE_CHECKS = 1;②.禁用外键检查，优化写入速度
插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。
禁用：
SET foreign_key_checks = 0;开启：
SET foreign_key_checks = 1;③.禁止自动提交
插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。
禁用：
SET autocommit = 0;开启：
SET autocommit = 1;九.服务器优化
1.优化服务器硬件
服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。
需要从以下几个方面考虑：
- ①.配置较大的内存。足够大的内存，是提高
MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。 - ②.配置高速磁盘，比如
SSD。 - ③.合理分配磁盘
IO，把磁盘IO分散到多个设备上，以减少资源的竞争，提高并行操作能力。 - ④.配置多核处理器，
MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。
2.优化MySQL的参数
通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。
MySQL的配置参数都在my.conf或者my.ini文件的[MySQLd]组中，常用的参数详解如下：
十.总结
本文是笔者多年使用MySQL的使用和优化经验，掌握了这些方法和要领后，在以后数据库使用中，定能码出高效。
