第9章 MySQL数据库性能优化9.1优化简介数据库原理与应用2MySQL数据库性能优化是数据库管理员和数据库开发人员的必备技能性能优化的目
的是为了使MySQL数据库运行速度更快、占用的磁盘空间更小。例如，通过优化文件系统提高磁盘I/O的读写速度，通过优化操作系统调度策
略提高MySQL在高负荷情况下的负载能力，优化表结构、索引、查询语句等使查询响应更快。SHOW STATUS LIKE ''v
alue'';查询MySQL数据库的性能参数【说明】value是要查询的参数值① Connections：连接MySQL服务器的次数
。② Uptime：MySQL服务器的上线时间。③ Slow_queries：慢查询的次数。④ Com_select：查询操作的次
数。⑤ Com_insert：插入操作的次数。⑥ Com_update：更新操作的次数。⑦ Com_delete：删除操作的次数。
【例9-1】查询MySQL服务器的慢查询次数。SHOW STATUS LIKE ''slow_queries''; 慢查询次数
参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。9.2优化查询数据库原理与应用69.2.1
分析查询语句的执行计划执行计划是SQL语句调优的一个重要依据。查看SQL语句的查询执行计划（QEP） 使用EXPLAI
N语句和DESCRIBE语句，通过此语句的输出结果能够了解到MySQL优化器是如何执行SQL语句的，提供重要的信息来帮助做出调优决
策。MySQL5.6.3及之后的版本对SELECT、DELETE、INSERT和UPDATE语句都可以生成执行计划。EXPLAIN
SELECT语句;【例9-2】使用EXPLAIN语句分析简单的查询语句。1.EXPLAIN语句USE fruitsales;EX
PLAIN SELECT FROM suppliers WHERE s_id IN(101,102,103);【说明】（1
）id：SELECT识别符，是SELECT查询序列号。（2）select_type：表示SELECT语句的类型，它的常用取值：①
SIMPLE表示简单查询，其中不包括连接查询和子查询。② PRIMARY表示主查询，或者是最外层的查询语句。③ UNION表示连接
查询的第二个或后面的查询语句。④ UNION RESULT表示一系列定义在UNION语句中表的返回结果，其对应的table列的值为
，表示匹配的id行是这个集合的一部分。【例9-3】使用EXPLAIN语句分析带有UNION的查询语句。EXP
LAIN SELECT FROM suppliers WHERE s_call=11111 UNION SELECT
FROM suppliers WHERE s_call=22222; （3）table：表示查询的表。（4）partitions
：表示分区表的分区情况，非分区表该列值为NULL（5）type：表示MySQL在表中找到所需行的方式，下面按照性能由最差到最好的顺
序给出常见类型。① ALL：全表扫描，MySQL将进行全表扫描。② index：索引全扫描，MySQL将遍历整个索引来查询匹配的行
，index与ALL的区别为index类型只遍历索引树。③ range：只检索给定范围的行，使用一个索引来选择行。④ index_
subquery：表示子查询中使用了普通索引。⑤ unique_subquery：表示子查询中使用了UNIQUE或者PRIMARY
KEY。⑥ ref：表示多表查询时，后面的表使用了普通索引。⑦ eq_ref：表示多表连接时，后面的表使用了UNION或者PRI
MARY KEY。⑧ const：表示表中有多条记录，但只从表中查询一条记录。⑨ system：该表是仅有一行的系统表。（6）po
ssible_keys：表示查询中可能使用的索引。（7）key：表示查询使用到的索引。（8）key_len：表示索引字段的长度。（
9）ref：表示使用哪个列或常数与索引一起来查询记录。（10）rows：表示查询的行数。（11）filtered：表示针对表里符合
条件的记录数的百分比。（12）Extra：表示MySQL在处理查询时的详细信息。【例9-4】使用EXPLAIN语句分析带有子查询的
查询语句。CREATE UNIQUE INDEX name_idx ON suppliers(s_name);EXPLAIN SE
LECT FROM suppliers WHERE s_id=(SELECT s_id FROM suppliers
WHERE s_name=''ACME'');DESCRIBE | DESC SELECT语句;【例9-5】使用DESCIBE
语句分析一个查询语句。2.DESCRIBE语句DESCRIBE SELECT FROM fruits WHERE f_name
=''apple''; DESCRIBE语句的使用方法与EXPLAIN语句是一样的，分析结果也是一样。9.2.2 优化查询基本原则尽
可能对每一条运行在数据库中的SQL语句进行EXPLAIN。尽量少使用JOIN。MySQL的优势在于简单，但是在某些方面其实也是劣势
。对于复杂的多表JOIN，一方面由于优化器受限，另一方面JOIN的性能表现距离其它的关系数据库（例如Oracle）有一定的差距。尽
量少排序。排序操作会消耗较多的CPU资源。尽量避免使用SELECT 查询。大多数情况下，SELECT子句中的字段的多少并不会影响
到读取的数据。但是当存在ORDER BY操作时，SELECT子句中的字段多少在很大的程度上就影响到了排序效率。尽量用JOIN代替子
查询。虽然JOIN的性能并不是特别好，但是和MySQL的子查询相比，它还是具有非常大的优势的。尽量少使用OR关键字。当WHERE子
句中存在多个条件以“或”并存时，MySQL的优化器并没有很好地解决其执行计划优化问题，这时使用UNION ALL或UNION的方式
来代替OR会得到更好的效果。尽量使用UNION ALL代替UNION。UNION和UNION ALL的差异主要是前者需要将两个或多
个查询结果集合并后再进行唯一性过滤操作，这会涉及排序增加大量的CPU运算，加大资源消耗和延迟。所以当确认结果集中不可能出现重复结果
或不在乎重复结果时，应尽量使用UNION ALL而不是UNION。尽量早过滤。使用这一原则优化一些JOIN的SQL语句，这样可以尽
可能多地减少不必要的I/O操作，大大节省I/O操作所消耗的时间。避免“类型转换”。这里的“类型转换”是指WHERE子句中出现的字段
类型和所给的值类型不一致时发生的类型转换。优先优化高并发的SQL语句。从破坏性的角度来说，高并发的SQL语句总会比使用频率低的SQ
L语句破坏性大，因为高并发的SQL语句一旦出现问题，甚至不给用户任何喘息的机会就会将系统压跨。而对于一些需要消耗大量I/O且响应很
慢的SQL语句，由于频率低，即使遇到大多情况下就是使整个系统响应慢一点，但至少会给用户喘息的机会。从全局出发优化，而不是片面调整。
SQL优化不能是单独针对某一个进行，而是应该充分考虑系统中所有的SQL语句，尤其是在通过调整索引优化SQL的执行计划时，千万不能顾
此失彼，因小失大。9.2.3 索引对查询速度的影响 ※※※MySQL中提高性能的一个最有效的方式就是对数据表设计合理的索引。【
例9-6】查询语句中不使用索引和使用索引的对比EXPLAIN SELECT from fruits WHERE f_name=
''apple'';CREATE INDEX fname_idx ON fruits(f_name);EXPLAIN SELECT
from fruits WHERE f_name=''apple'';1. 使用LIKE关键字的查询语句【例9-7】查询语句中使用L
IKE关键字，并且匹配的字符串中包含有“%”的两种查询情况比较。EXPLAIN SELECT from fruits WHER
E f_name LIKE ''%e'';EXPLAIN SELECT from fruits WHERE f_name LIKE
''a%'';如果匹配字符串的第一个字符为“%”，索引不会起作用。2. 使用多列索引的查询语句【例9-8】下面在fruits表的s_
id和f_name两个字段上创建多列索引，然后验证多列索引的使用情况。DROP INDEX fname_idx ON FRUITS
; ##删除例9-6创建的索引 CREATE INDEX sid_fname_idx ON fruits(s_id,f_
name);EXPLAIN SELECT FROM fruits WHERE s_id=107;只有查询条件中使用了索引字段中
的第一个字段时，索引才会被使用。EXPLAIN SELECT from fruits WHERE f_name=''apple''
;3. 使用OR关键字的查询语句【例9-9】查询语句中使用OR关键字示例。EXPLAIN SELECT FROM fruits
WHERE s_id=101 OR f_id=''a1'';查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时
，查询中才使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。EXPLAIN SELECT FROM fru
its WHERE s_id=101 OR f_name=''apple'';9.2.4 优化执行语句1. 优化INSERT语
句ALTER TABLE 表名 DISABLE KEYS;ALTER TABLE 表名 ENABLE KEYS;1）禁用索引
开启索引SET UNIQUE_CHECKS=0;SET UNIQUE_CHECKS=1;2）禁用唯一性检查开启唯一性检查3）使用
批量插入，减少与数据库间的连接等操作INSERT INTO orders VALUES (50001,''2022-01-01'',1
0002), (50002,''2022-01-01'',10003);4）使用LOAD DATA INFILE批量导入 LOAD
DATA INFILE语句导入数据的速度比INSERT语句速度快。5）对于InnoDB引擎的表，常见的优化方法禁用唯一性检查禁用
外键检查 SET FOREIGN_KEY_CHECKS=0|1;禁止自动提交 SET AUTOCOMMIT=0|1
;2. 优化UPDATE语句更新时写的速度依赖于更新的数据大小和更新的索引的数量。更新时锁定表可以加速执行UPDATE的操作，同时
做多个更新，比一次更新一条记录要快得多。3. 优化DELETE语句删除一条记录的时间与索引数量成正比。删除一个表的所有行，使用TR
UNCATE TABLE而不要使用DELETE。9.3优化数据库结构数据库原理与应用269.3.1 表的优化1. 将字段很多的表
分解成多个表对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。表的优化主要是切分表、增加中间表和增加中
间字段 。【例9-10】假设customers表中有很多字段，其中“c_email”字段存储客户的电子邮箱信息。假设“c_emai
l”信息很少使用，可以对customers表进行分解。将customers表分解成两个表info表和email表，info为客户基
本信息表，email为客户电子邮箱信息表。email表中存储两个字段，分别为c_id和c_email。如果需要查询某个客户的电子邮
箱信息，可以使用c_id查询。如果需要同时查询客户基本信息与电子邮箱信息，可以将info表和email表进行连接查询SELECT
c_name,c_email FROM info i,email e WHERE i.c_id=e.c_id;2. 增加中间
表有时需要经常查询多个表中的几个字段，如果经常进行多表的连接查询，会降低查询速度。对于这种情况，可以建立中间表，通过对中间表的查询
提高查询效率。【例9-11】在fruitsales数据库中有fruits表和orderitems表，实际中经常要查询每种水果销售的
总数量及总金额。可以通过增加中间表，提高查询速度。CREATE TABLE temp_sale( 编号 CHAR(10), 名称
CHAR(10), 总数量 int, 总金额 decimal(8,2));INSERT INTO temp_sale SELECT
f.f_id,f_name,sum(quantity),sum(quantityitem_price) FROM or
deritems o,fruits f WHERE o.f_id=f.f_id GROUP BY f.f_id; SE
LECT FROM temp_sale;3. 增加冗余字段设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段。但是，有
时候为了提高查询速度，可以有意识的在表中增加冗余字段。【例】经常要查看每位客户所下订单的总金额，需要通过c_id字段将orders
表和orderitems表连接起来。SELECT c_id,sum(quantityitem_price) FROM orde
ritems oi,orders o WHERE oi.o_num=o.o_num GROUP BY c_id;ALTER T
ABLE orderitems ADD c_id INT;SET sql_safe_updates=0;UPDATE orderi
tems oi SET c_id=(SELECT c_id FROM orders o WHERE oi.o_num=o.o_n
um);SELECT FROM orderitems;如果要查询一个客户订单总金额，也需要将这两个表进行连接查询，而连接查询会
降低查询速度。那么，则可以在orderitems表中增加一个冗余字段c_id，该字段用来存储客户的id值，这样就不用每次都进行多表
连接操作了。9.3.2 分析表、检查表和优化表1. 分析表ANALYZE用于收集优化器统计信息，分析和存储表的关键字分布，分析的
结果可以使数据库系统获得准确的统计信息，使得SQL语句能生成正确的执行计划。ANALYZE [LOCAL|NO_WRITE_TO
_BINLOG] TABLE 表名[,…]; LOCAL关键字和NO_WRITE_TO_BINLOG作用相同，都是执行过程不写
二进制日志。【例9-12】使用ANALYZE TABLE分析fruits表。ANALYZE TABLE fruitsales.
fruits;Table列表示分析的表的名称Op列表示执行的操作，analyze表示进行分析操作Msg_type表示信息类型，其值
通常是状态（status）、信息（info）、注意（note）、警告（warning）和错误（error）之一。2. 检查表CHE
CK主要作用是检查表是否存在错误，CHECK也可以检查视图是否有错误，比如在视图定义中视图引用的表已不存在CHECK TABLE
表名[,表名,……];【例9-13】使用CHECK TABLE检查fruits表。CHECK TABLE fruitsales.f
ruits;3. 优化表OPTIMIZE可以回收空间、减少碎片、提高I/O。OPTIMIZE [LOCAL|NO_WRITE_TO
_BINLOG] TABLE 表名[,…];【例9-14】使用OPTIMIZE TABLE优化fruits表。OPTIMIZE T
ABLE fruitsales.fruits;对特定的表不需要经常优化，每周一次或每月一次即可。【注意】ANALYZE、CHECK
、OPTIMIZE执行期间会对表进行锁定，应在数据库不繁忙的时候执行相关的操作。9.3.3 优化字段1. 数值类型尽量不要使用D
OUBLE类型，存在存储长度和精确问题。不建议使用DECIMAL固定精度小数整数要区分开TINYINT、INT和BIGINT的选择
，如果能确定字段不使用负数，添加UNSIGNED定义。2. 字符类型尽量不要使用TEXT类型定长字段使用CHAR类型尽量不要使用T
IMESTAMP类型不定长字段使用VARCHAR类型，且仅设置适当的最大长度3. 时间类型只需要精确到某一天的数据，可以使用DAT
E类型，因为它的存储空间只需要3个字节4. ENUM和SET状态字段可以尝试使用ENUM来存放如果是存放可预先定义的属性数据可以尝
试使用SET类型 通过对不同表不同字段使用不同的数据类型减少数据存储量，降低I/O操作次数，提高缓存命中率。9.4优化MyS
QL服务器数据库原理与应用40优化MySQL服务器的原则内存中的数据要比磁盘上的数据访问的快。让数据尽可能长时间地留在内存里能减少
磁盘读写活动的工作量。让索引信息留在内存里要比让数据记录的内容留在内存里更加重要。9.4.1 优化服务器硬件加大内存，数据库服务
器可以把更多的数据保存在缓存区，可大大减少磁盘I/O，从而提升数据库的整体性能。配置高速磁盘系统，以减少读盘的等待时间，提高响应速
度。合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。配置多处理器，MySQL是多线程的数据库，
多处理器可同时执行多个线程。9.4.2 优化MySQL的参数key_buffer_size：表示索引缓存的大小，这个值越大，使用索引进行查询的速度就越快。max_connection：表示数据库的最大连接数。table_cache：表示同时打开的表的个数。thread_cache_size：表示可以复用的线程的数量。query_cache_size：表示查询缓冲区的大小。通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。MySQL服务的配置参数都在my.ini或者my.cnf文件的[MySQLd]组中。sort_buffer_size：表示排序缓冲区的大小。read_buffer_size：表示为每个线程连续扫描时为扫描的每个表分配的缓冲区的大小。read_rnd_buffer_size：表示为每个线程保留的缓冲区的大小，与read_buffer_size相似，但主要用于存储按特定顺序读取出来的记录。innodb_buffer_pool_size：表示InnoDB类型的表和索引的最大缓存，这个值越大，查询的速度就会越快。。Thank You ! |
|
