9 MySQL数据库性能优化ppt课件数据库原理与应用 .pptx

9 MySQL 数据库性能优化教学课件数据库原理与应用 数据库原理与应用9.1优化简介数据库原理与应用3数据库原理与应用 MySQL 数据库性能优化是数据库管理员和数据库开发人员的必备技能 性能优化的目的是为了使MySQL 数据库运行速度更快、占用的磁盘空间更小。例如，通过优化文件系统提高磁盘I/O 的读写速度，通过优化操作系统调度策略提高MySQL 在高负荷情况下的负载能力，优化表结构、索引、查询语句等使查询响应更快。数据库原理与应用SHOW STATUS LIKE value;查询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优化查询数据库原理与应用7数据库原理与应用9.2.1 分析查询语句的执行计划 执行计划是SQL 语句调优的一个重要依据。查看SQL 语句的查询执行计划（QEP）使用EXPLAIN 语句和DESCRIBE 语句，通过此语句的输出结果能够了解到MySQL 优化器是如何执行SQL 语句的，提供重要的信息来帮助做出调优决策。MySQL5.6.3 及之后的版本对SELECT、DELETE、INSERT和UPDATE 语句都可以生成执行计划。数据库原理与应用EXPLAIN SELECT 语句;【例9-2】使用EXPLAIN 语句分析简单的查询语句。1.EXPLAIN语句USE fruitsales;EXPLAIN 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 的查询语句。EXPLAIN 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 或者PRIMARY KEY。const：表示表中有多条记录，但只从表中查询一条记录。system：该表是仅有一行的系统表。数据库原理与应用（6）possible_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 SELECT*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语句总会比使用频率低的SQL 语句破坏性大，因为高并发的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】查询语句中使用LIKE 关键字，并且匹配的字符串中包含有“%”的两种查询情况比较。EXPLAIN SELECT*from fruits WHERE 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 fruits 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 V ALUES(50001,2022-01-01,10002),(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 语句 删除一条记录的时间与索引数量成正比。删除一个表的所有行，使用TRUNCATE TABLE 而不要使用DELETE。9.3优化数据库结构数据库原理与应用27数据库原理与应用9.3.1 表的优化1.将字段很多的表分解成多个表 对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。表的优化主要是切分表、增加中间表和增加中间字段。数据库原理与应用【例9-10】假设customers 表中有很多字段，其中“c_email”字段存储客户的电子邮箱信息。假设“c_email”信息很少使用，可以对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(quantity*item_price)FROM orderitems o,fruits f WHERE o.f_id=f.f_id GROUP BY f.f_id;SELECT*FROM temp_sale;数据库原理与应用3.增加冗余字段 设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段。但是，有时候为了提高查询速度，可以有意识的在表中增加冗余字段。【例】经常要查看每位客户所下订单的总金额，需要通过c_id 字段将orders 表和orderitems 表连接起来。SELECT c_id,sum(quantity*item_price)FROM orderitems oi,orders o WHERE oi.o_num=o.o_num GROUP BY c_id;数据库原理与应用ALTER TABLE orderitems ADD c_id INT;SET sql_safe_updates=0;UPDATE orderitems oi SET c_id=(SELECT c_id FROM orders o WHERE oi.o_num=o.o_num);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.检查表 CHECK 主要作用是检查表是否存在错误，CHECK 也可以检查视图是否有错误，比如在视图定义中视图引用的表已不存在CHECK TABLE 表名,表名,;【例9-13】使用CHECK TABLE 检查fruits 表。CHECK TABLE fruitsales.fruits;数据库原理与应用3.优化表 OPTIMIZE 可以回收空间、减少碎片、提高I/O。OPTIMIZE LOCAL|NO_WRITE_TO_BINLOG TABLE 表名,;【例9-14】使用OPTIMIZE TABLE 优化fruits 表。OPTIMIZE TABLE fruitsales.fruits;对特定的表不需要经常优化，每周一次或每月一次即可。【注意】ANAL YZE、CHECK、OPTIMIZE 执行期间会对表进行锁定，应在数据库不繁忙的时候执行相关的操作。数据库原理与应用9.3.3 优化字段1.数值类型 尽量不要使用DOUBLE 类型，存在存储长度和精确问题。不建议使用DECIMAL 固定精度小数 整数要区分开TINYINT、INT 和BIGINT 的选择，如果能确定字段不使用负数，添加UNSIGNED 定义。数据库原理与应用2.字符类型 尽量不要使用TEXT 类型 定长字段使用CHAR 类型 尽量不要使用TIMESTAMP 类型 不定长字段使用V ARCHAR 类型，且仅设置适当的最大长度3.时间类型 只需要精确到某一天的数据，可以使用DATE 类型，因为它的存储空间只需要3个字节数据库原理与应用4.ENUM 和SET 状态字段可以尝试使用ENUM 来存放 如果是存放可预先定义的属性数据可以尝试使用SET 类型 通过对不同表不同字段使用不同的数据类型减少数据存储量，降低I/O 操作次数，提高缓存命中率。9.4优化MySQL 服务器数据库原理与应用41数据库原理与应用优化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或者f 文件的MySQLd 组中。数据库原理与应用 sort_buffer_size：表示排序缓冲区的大小。read_buffer_size：表示为每个线程连续扫描时为扫描的每个表分配的缓冲区的大小。read_rnd_buffer_size：表示为每个线程保留的缓冲区的大小，与read_buffer_size 相似，但主要用于存储按特定顺序读取出来的记录。innodb_buffer_pool_size：表示InnoDB 类型的表和索引的最大缓存，这个值越大，查询的速度就会越快。数据库原理与应用