mysql笔记：11. 性能优化
文章目录
- 概览
- 查询速度优化
- 1. 分析查询语句
- 1.1 EXPLAIN
- 1.2 DESCRIBE
- 2. 使用索引优化查询
- 3. 优化子查询
- 数据库结构优化
- 1. 分解表
- 2. 建立中间表
- 3. 增加冗余字段
- 4. 优化插入速度
- 4.1. MyISAM引擎表
- 4.2. InnoDB引擎表
- 5. 分析表、检查表和优化表
- 5.1. 分析表
- 5.2. 检查表
- 5.3. 优化表
- MySQL服务器的优化
- 1. 服务器硬件优化
- 2. MySQL参���优化
性能优化是通过合理安排资源，调整系统参数使MySQL运行更快、更节省资源。主要包括查询速度优化、更新速度优化、MySQL服务器优化等。
概览
MySQL数据库优化是多方面的，原则上是减少系统的瓶颈和资源的占用、增加系统的反应速度。
（）MySQL常用的性能参数值如下表：
参数名 功能 connections 连接服务器的次数 uptime 服务器的上线时间 slow_queries 慢查询的次数 com_select 查询操作的次数 com_insert 插入操作的次数 com_update 更新操作的次数 com_delete 删除操作的次数 查询命令语法：
（）mysql> show status like 'NAME';
查询速度优化
在数据库中，一般对数据的查询操作是最频繁的，提高数据的查询速度可以有效提升数据库的性能。
1. 分析查询语句
通过分析查询语句，可以了解查询语句的执行情况，找出查询语句的不足之处，从而优化查询语句。
分析查询语句有两种方式：EXPLAIN和DESCRIBE。
1.1 EXPLAIN
基本语法：
EXPLAIN [EXTENDED] SELECT select_options
参数select_type：SELECT类型，可以为以下任何一种：
值 含义 SIMPLE 简单SELECT(不使用UNION或子查询) PRIMARY 最外面的SELECT UNION UNION中的第二个或后面的SELECT语句 DEPENDENT UNION UNION中的第二个或后面的SELECT语句，取决于外面的查询 UNION RESULT UNION的结果。 SUBQUERY 子查询中的第一个SELECT DEPENDENT SUBQUERY 子查询中的第一个SELECT，取决于外面的查询 DERIVED 导出表的SELECT(FROM子句的子查询) 1.2 DESCRIBE
基本语法：
DESCRIBE SELECT select_options
2. 使用索引优化查询
索引可以快速定位表中的某条记录，使用索引可以提高数据库的查询速度，从而提高数据库的性能。如果不使用索引，查询语句将扫描表中全部记录，速度会很慢；使用索引，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。
一般情况下，索引可以提高查询速度，但并不是所有使用带有索引的字段查询都会起作用。例如下面几种特殊情况：
- 使用LIKE关键字的查询语句
- 使用多列索引的查询语句
- 使用OR关键字的查询语句
3. 优化子查询
子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个的条件。它可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
其优点是查询语句很灵活，但是其执行效率不高。
执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响也随之增大。
在MySQL中，可以使用连接（JOIN）查询来替代子查询。它不需要建立临时表，速度比子查询要快。如果查询中使用索引的话，性能会更好。
数据库结构优化
合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。设计时，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面内容。
1. 分解表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新的表。因为当一个表的数据量很大时，会由于使用频率低的字段而变慢。
2. 建立中间表
对于需要经常联合查询的表，可以建立中间表提高查询效率。
3. 增加冗余字段
设计数据库表时应该尽量遵循数据库范式理论的规约，尽可能减少冗余字段，让数据库看起来更加精致。但是，合理地加入冗余字段可以提高查询速度。
表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。
不过冗余字段会导致一些新问题。如，一个表中的冗余字段值修改了，就要同步更新其他表中的该字段值，否则就会使原本一致的数据变得不一致。
4. 优化插入速度
插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。
4.1. MyISAM引擎表
- 禁用索引
对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。可以在插入记录前禁用索引，数据插入完毕后再开启索引。
# 禁用索引 ALTER TABLE table_name DISABLE KEYS; # 启用索引 ALTER TABLE table_name ENABLE KEYS;
空表批量导入数据时不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。
- 禁用唯一性检查
插入数据时，MySQL会对插入的记录进行唯一性校验。这种校验也会降低插入速度。可以先禁用，插入数据后再开启。
# 禁用唯一性校验 SET UNIQUE_CHECKS=0; # 启用唯一性校验 SET UNIQUE_CHECKS=1;
-
使用批量插入
-
使用LOAD DATA INFILE批量导入
4.2. InnoDB引擎表
- 禁用唯一性检查
和MyISAM引擎的使用一样。
- 禁用外键检查
# 禁用外键检查 SET foreign_key_checks=0; # 启用外键检查 SET foreign_key_checks=1;
- 禁止自动提交
# 禁用自动提交 SET autocommit=0; # 启用自动提交 SET autocommit=1;
5. 分析表、检查表和优化表
MySQL提供了分析表、检查表和优化表的语句。
5.1. 分析表
分析表主要是分析关键字的分布，语法：
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 [, table_name2, ...]
5.2. 检查表
MySQL数据库中可以使用CHECK TABLE检查表，主要是检查InnoDB和MyISAM类型的表是否存在错误，语法：
CHECK TABLE table_name1 [, table_name2, ...] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}5.3. 优化表
优化表主要是消除删除或者更新造成的空间浪费。该语句对InnoDB和MyISAM类型的表都有效。主要是优化表中的VARCHAR、BLOB或TEXT类型的字段。语法：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 [, table_name2, ...]
MySQL服务器的优化
1. 服务器硬件优化
服务器的硬件性能直接决定着MySQL数据库的性能。优化方向：
- 配置较大的内存
- 配置调整磁盘系统
- 合理分布磁盘I/O
- 配置多处理器
2. MySQL参数优化
通过优化MySQL参数也可以在不提升硬件的前提下提高资源利用率，从而达到提高服务器性能的目的。配置参数都在my.cnf或者my.ini文件中。
- 禁止自动提交
- 禁用唯一性检查
-
- 禁用唯一性检查
- 禁用索引